Jump to content
Larry Ullman's Book Forums

Recommended Posts

Greetings Larry and forum users :-)

 

Any help with the below problem would be greatly appreciated :-)

Please see my signature at the bottom of this message

for details on my OS, MySQL and PHP versions.

 

-- A short summary of the problem --

I have been building a commerce site based on Larry's books Effortless-Ecommerce, PHP & MySQL

(second edition), & PHP 5 advanced. All of them are great books!

After re-reading Larry's PHP & MySQL chapter on Advanced SQL and MySQL for the

30th time, I got the idea to change MySQL's minimum keyword length from four characters to three characters.

See page 201 for the reference to changing minimum keyword length. As a result, I did some research online and

decided to modify the ft_min_word_len setting. This way I can build apps that FULL TEXT

search for 3 character words instead of just words over 4 characters long.

I tried to change ft_min_word_len setting from 4 to 3 by creating a my.cnf file and changed the ft_min_word_len setting to 3.

I restarted my computer hoping the changes would take effect and boom

my database stopped working. I cannot even turn it on. aiya!

Please note my MySQL database server has been working fine for three years.

-- Details on how I created the problem --

1.) I chose a sample options file template that mysql includes in

/usr/local/mysql/support-files and saved it in the /etc/ directory as my.cnf.

I chose the my-large.cnf file as my template.

Here are the permissions for the new my.cnf file

-rw-r--r-- 1 root wheel 4775 Aug 1 15:17 /etc/my.cnf

Below are mysql docs that led me to do this:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

http://dev.mysql.com/doc/refman/5.1/en/option-files.html

http://dev.mysql.com/tech-resources/articles/mysql_intro.html

 

2.) I used vi to add the need setting that I desired and saved

the file as: /etc/my.cnf

I added the setting ft_min_word_len = 3

Now, the [mysqld] section of my.cnf file looks like this:

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

ft_min_word_len = 3

 

3.) The problem begins.

After making the above changes, I tried to restart MySQL and

it would not restart. So, I restarted my computer and MySQL

still would not restart.

 

If I try:

/usr/local/mysql/bin/mysql mysql -u root -p

I get the below error message:

ERROR 2002 (HY000): Can't connect to local MySQL server through

socket '/tmp/mysql.sock' (2)

 

MySQL documentation says if you get this error

"Make sure that the server is running. If it is not, clients cannot connect to it.

For example, if an attempt to connect to the server fails with a message such as one of those following, one cause might be that the server is not running:"

http://dev.mysql.com/doc/refman/5.1/en/access-denied.html

This is my problem. The server is not on and I cannot turn it on.

 

Many MySQL posts regarding ERROR 2002 recommend checking the

error log file for more details about the problem.

This was not easy to figure out how to do

so here is the command I used to get the error log.

sudo vi /usr/local/mysql/data/macbook-2.local.err

Hopefully, this will help someone.

 

When I try to turn on MySQL from the System Preference panel in MAC, the error log message created is:

110801 18:09:42 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

110801 18:09:42 [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)

110801 18:09:42 [ERROR] Aborting

110801 18:09:42 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

110801 18:09:42 mysqld_safe mysqld from pid file /usr/local/mysql/data/macbook-2.local.pid ended

 

Interestingly, if I try to log in from the command line using:

/usr/local/mysql/bin/mysql mysql -u root -p

It does not create an error message in the error log. Only when I try to log in from System Preference is

an error message created in the error log.

 

After doing the above trouble shooting, it looks like I have the following problems:

a.) According to ERROR 2002 there is a problem with /tmp/mysql.sock.

However, if I open the /tmp/ directory there is not mysql.sock file. However, if I run the command:

locate mysql.sock.

It returns: /private/tmp/mysql.sock as the location of the mysql.sock file.

So, it looks like mysql.sock exists but I cannot find it or modify it's permissions. Strange.

Please advise how to find this file. Are the permissions of mysql.sock needing to be

changed?

 

b.) I have a [Warning] and (Errcode: 13):

[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)

From this message I assume the major problem is with the ./mysql-bin.index file.

However, I cannot find this file either. Is there a permissions problem with this file?

Please advise how to find this file or solve this problem.

 

One other thing I tried to do was restart the DB server from the command line using:

/usr/local/mysql-5.1.36-osx10.5-x86_64/support-files/mysql.server restart

If I do this I get the following error:

ERROR! MySQL manager or server PID file could not be found!

Starting MySQL

. ERROR! Manager of pid-file quit without updating file.

 

My database was working great for 3 years.

However, I tried to create one my.cnf file and

I effectually broke MySQL. I cannot even turn it on or restart it.

Any advice to solve this issue would be much appreciated :-)

 

Regards.

Link to comment
Share on other sites

  • 3 weeks later...

Sorry for the delayed reply. Been a hectic summer! And thanks for the nice words on the books. Much appreciated. Kudos, too, for including lots of details.

 

Interestingly, if I try to log in from the command line using:

/usr/local/mysql/bin/mysql mysql -u root -p

It does not create an error message in the error log. Only when I try to log in from System Preference is

an error message created in the error log.

 

This is confusing. That command line step is how you execute the command-line mysql client. It is NOT how you start the database application. Conversely, the System Preferences can be used to start and stop the database application. But these are two different things.

 

After doing the above trouble shooting, it looks like I have the following problems:

a.) According to ERROR 2002 there is a problem with /tmp/mysql.sock.

However, if I open the /tmp/ directory there is not mysql.sock file. However, if I run the command:

locate mysql.sock.

It returns: /private/tmp/mysql.sock as the location of the mysql.sock file.

So, it looks like mysql.sock exists but I cannot find it or modify it's permissions. Strange.

Please advise how to find this file. Are the permissions of mysql.sock needing to be

changed?

 

On Mac OS X, /tmp and /private/tmp are the same. If you have a mysql.sock file, then MySQL is running.

 

 

Since it's been a while since the original post (apologies again), why don't you confirm where things stand now and I'll try to get you a fast reply.

Link to comment
Share on other sites

Hi Larry,

Thank you for the response to my post.

Below is a summary of my troubleshooting this problem.

I hope you are having a great summer :-)

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 directory 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 reasons were incorrect permission settings on certain files and

directories, 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

Link to comment
Share on other sites

Thank you for all the effort you put in to share what you learned. I really love seeing that!

 

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.....

 

A .sock file creates a socket, which is a port of communication. So, in complete layman's term, the mysql.sock file is the door that MySQL creates so that communications can get through.

 

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?

 

There should only be one, not two. Yes, MySQL creates a new .sock file when it starts.

 

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

working properly?

 

That I don't know.

 

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?

 

Yes, this is important and probably a big cause of your problems. I thought MySQL on Mac OS X ran by default as the "mysql" user, so that user needs to have access to everything it needs. If you change the user that MySQL is running as, then that user needs permission to access everything.

 

Thanks again for sharing!

Link to comment
Share on other sites

 Share

×
×
  • Create New...