Re: Problems creating my.cnf / options file

[Jon Swenson]

Hi Rick,

Thank you for the suggestions.
However, I did not make any tables with FULLTEXT indexes yet.
Plus, my problem was that I could not start the database. So,
it was impossible to turn on MySQL and alter talbes etc.
Below is a summary of the solution to this problem.
Regards.

A solution found:

I have been working on this problem for almost a month.
I had given up and made a stop gap by setting
up two linux boxes with PHP5 MySQL and Apache and began developing
on those instead. Then, after several weeks and alot of work a
solution was found. Below is an outline of the troubleshooting steps
I used to get MAC 10.5.8 working with a custom my.cnf file.

I hope this helps someone avoid the stress I just went through
to get this DB working again. ;-)

When you want to use my.cnf to customize your MySQL options/configurations
on OSX MAC you will notice several things: 1. There is no my.cnf file.
2. You have to create a my.cnf file. And, when you create it,
it will make your MySQL inoperable. You cannot turn MySQL on after creating
this file. So, if you desire to have a my.cnf file set up on your MAC,
get ready to troubleshoot. The main file used for trouble shooting
is your mysql error log. The error log is located in the file below:
/usr/local/mysql/data/macbook-2.local.err
Use the sudo option to acces it.
sudo vi /usr/local/mysql/data/macbook-2.local.err

ONE
Create the my.cnf file. Sample my.cnf files are located in your MySQL install at:
/usr/local/mysql-5.1.36-osx10.5-x86_64/support-files/ .
Just copy the file you want to your /etc directory.
sudo cp /usr/local/mysql-5.1.36-osx10.5-x86_64/support-files/my-medium.cnf /etc/my.cnf
After I did this, MySQL could not be turned on.

Before I changed the permissions on the my.cnf file I was getting the below error message.
110826 15:20:04 mysqld_safe mysqld from pid file /usr/local/mysql/data/macbook-2.local.pid ended
110826 15:20:12 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
Warning: World-writable config file '/etc/my.cnf' is ignored
110826 15:20:12 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
110826 15:20:12 [Note] Plugin 'FEDERATED' is disabled.
^G/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
110826 15:20:12 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110826 15:20:12 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

The main issue in this error message to be solved is:
""Warning: World-writable config file '/etc/my.cnf' is ignored""
This told me my.cnf that I created was not being used.
Changed permission levels to 770:
sudo chmod 770 my.cnf


TWO
Change settings in the my.cfn file.
Make sure the socket is set correctly:
socket = /tmp/mysql.sock

## Note:
Originally, i tried the below path because unix command locate mysql.sock
returns /var/mysql/mysql.sock
socket = /var/mysql/mysql.sock
But, this breaks systems preferences MySQL panel functionality
and causes other problems. I fixed this at the end of the troubeshooting
process. Possibly, having it set correctly here will
avoid some of the below errors.
Anyhow, for my situation, the next error was the main problem and a beast to fix.

THREE
Error message related to the ./mysql-bin.index was now displayed when I
tried to turn on the database.
Below is the error message:
110826 15:53:59 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
110826 15:54:00 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
^G/usr/local/mysql/bin/mysqld: File './mysql-bin.index' not found (Errcode: 13)
110826 15:54:00 [ERROR] Aborting
110826 15:54:00 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
110826 15:54:00 mysqld_safe mysqld from pid file /usr/local/mysql/data/macbook-2.local.pid ended

The ./mysql-bin.index is not found.
Based on the above error message, file "'./mysql-bin.index' not found (Errcode: 13)""
and the below web post Post.
http://www.linuxquestions.org/questions/linux-software-2/mysqld-var-log-mysql-mysql-bin-index-not-found-480676/
I realized I needed to do two things:
1.) Change the log-bin settings in the my.cnf file.
The my.cnf file has login-bin settings in two places. I changed
both to the below value. I do not know if both have to be changed but that is
what I did and it helped solve the issue.
log-bin=/usr/local/mysql-5.1.36-osx10.5-x86_64/bin

2.) Make the ./mysql-bin.index file.
Based on the above web post, I created a script called ./mysql-bin.index
I put the below values in it:

./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000008
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000016

Then, I saved the file and changed the permissions to it.
Save at:
/usr/local/mysql-5.1.36-osx10.5-x86_64/bin/mysql-bin.index
sudo chmod 770 ./mysql-bin.index
Note: I think I created it using.
touch ./mysql-bin.index. Then, I opened it in vi and put the
above values in it.
(ls -A to view all the hidden files in a folder)

FOUR
Change the ownership files of every file in the
mysql directory. On MAC the MySQL directory is:
/usr/local/mysql-5.1.36-osx10.5-x86_64/
The below is the UNIX command to change the ownership values:
chown -R mysql:mysql /usr/local/mysql-5.1.36-osx10.5-x86_64/
Some posts said this command needed to be run in order to allow
MySQL to create the files/directories it needed to make.
I am not sure if this is true but I did it.


FIVE
After this I was getting the below types of error messages everytime I tried to turn on the database.
110826 16:56:22 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
110826 16:56:22 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
110826 16:56:22 [Note] Plugin 'FEDERATED' is disabled.
^G/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
110826 16:56:22 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110826 16:56:22 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

Based on the above error message I was looking for ./mysql/plugin.frm.
I was using the unix command "locate" to find the plugin.frm
but it could not find it.
Many posts talked about the /data file permissions needing to be changed.
However, using the unix command line sudo chmod 777 command on the /data file was fruitless.
I could not get in the /usr/local/mysql-5.1.36-osx10.5-x86_64/data directory.
But, Mac's Finder did the trick.
You use the Go -> Go to Folder command in Finder's menu bar.
Put the path to MySQL in the Finder Go ->Go to Folder text box.
For me this was: /usr/local/mysql-5.1.36-osx10.5-x86_64/

Then, you change the permissions on:
/usr/local/mysql-5.1.36-osx10.5-x86_64/data
To find hidden files in finder you use:
/usr/local/mysql/data/. (note the "." at the end of the path)
I changed the permissions on these folders so the root MAC user could
have read access to them. Plus, mysql has ownership too.
After changing this setting the above error message went away.

SIX
When trying to turn on MySQL through System preference I would get
the error log message below:
"mysqld_safe A mysqld process already exists message"
This means MySQL is turned on. :-)
Wow, we are making progress!

Around this time I would also get a "cannot find
/tmp/mysql.sock" message when I tried to login using
/usr/local/mysql/bin/mysql -u root -p.
So, I made a symbolic link from ln -s /var/mysql/mysql.sock /tmp/mysql.sock
lrwxr-xr-x 1 root wheel 10 Aug 26 17:39 mysql.sock -> mysql.sock

I thought the symbolic link in the /tmp file would allow
MySQL to find the file in the directory it was looking in: /tmp/mysql.sock.
But, this issue may have been caused by my not having the correct socket setting
in my.cnf. If you did step TWO as above your should not have this problem.
In short, I am not sure if you need to do this step. But, I did it.

SEVEN
I was still not able to log in and the "mysqld_safe A mysqld process already exists"
message was still showing up in the error log. Then,
I found this post which partially solved the final step in getting my DB working again:
http://www.sitepoint.com/forums/mysql-182/mysqld-process-already-exists-very-green-newbie-292123.html
In short, the database is already on. But, system preferences does not show you that it is on.
However, I was able to access the database by using the below command:
sudo /usr/local/mysql-5.1.36-osx10.5-x86_64/bin/mysql -u root -p



However, it was still a strange start up.
Here is how I started it:
1.) Through system preferences turn it on.
The result is an error message. And, system prefences shows
the database is off. However, if you read the error message the
mysqld is ready for connections. But, you cannot log in normally.
A normal login would use:
/usr/local/mysql/bin/mysql -u root =p
This results in
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

But, if I use:
sudo /usr/local/mysql-5.1.36-osx10.5-x86_64/bin/mysql -u root -p
I get in.
Strange.

O, now found the solution for ERROR 2002 (HY000) :-)
In my.cnf the socket settings were incorrect.
SHOULD BE:
socket = /tmp/mysql.sock
NOT
socket = /var/mysql/mysql.sock
This solved the error.
Now, the system preference panel works again.
wheew, my MAC machine has been restored.

Last step:
Put in the minimum keywork length setting in [mysqld] section
in the my.cnf file. Wanting to do this is what caused all this trouble.
But, now it works fine :-)
ft_min_word_len = 3

YEAAHHH!!!!
I got my database back.
And, I can change my database settings in the my.cnf file. :-)
MySQL works fine now.

SHORT SUMMARY:
Why did creating a new my.cnf file cause so much havoc?? sigh.....
It looks like the main reason was permission settings, incorrect settings
in my.cnf script and not having a ./mysql-bin.index.

I am not a MySQL/Unix expert, so any advice to help better understand the above
issues would be greatly appreciated.
Cheers !!

QUESTIONS:
1.) What is a mysql.sock file? What does it do? This thing is a mystery.
Sometimes it was in the /private/tmp/mysql.sock and then other times
it was in /private/var/mysql.sock. Mysterious.....

2.) Why does MySQL have this file in /private/var/mysql.sock and also
in the /tmp file. Does it create a new .sock file in the ./tmp directory
everytime you turn it on?

3.) Was not having a ./mysql-bin.index file a result of mysql.sock not
working properly?

4.) Why did I have to change permissions of all MySQL files files
using chown -R mysql:mysql /usr/local/mysql-5.1.36-osx10.5-x86_64/ ?
Was this necessary? I read on some web posts that my.cnf has a
"user= " setting. You can set it to user=mysql and then MySQL fuctions
as the "mysql" etc. Is this important?


BELOW IS THE MY.CNF FILE CONTENTS I AM CURRENTLY USING
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
#socket = /var/mysql/mysql.sock
socket = /tmp/mysql.sock


# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
#socket = /var/mysql/mysql.sock
socket = /tmp/mysql.sock

skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# min word length setting for searches.
ft_min_word_len = 3

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
# Originally was log-bin=mysql-bin
log-bin=/usr/local/mysql-5.1.36-osx10.5-x86_64/bin


# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended

# Originally was log-bin=mysql-bin
log-bin=/usr/local/mysql-5.1.36-osx10.5-x86_64/bin


# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout