MySQL problems on Snow Leopard

Question

I just erased and re-installed Snow Leopard. I installed MySQL 5.1.48 64-bit clean. I'm having issues with the MySQL server... when I boot the computer and log in, the server is not running. I tried to start it using the preference pane by clicking "Start MySQL Server", but all it did was highlight the button blue and think for like a minute, and then the button went back to normal and nothing happened.

I then tried starting it from the terminal:

    Hristo$ sudo /usr/local/mysql/support-files/mysql.server start
    Starting MySQL
    ....................................................................................................
    ERROR! Manager of pid-file quit without updating file.

so I checked the status:

Hristo$ sudo /usr/local/mysql/support-files/mysql.server status
Password:
/usr/local/mysql/support-files/mysql.server: line 418: pidof: command not found
 ERROR! MySQL is not running

I tried this:

Hristo$ /usr/local/mysql/bin/mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

So I'm not sure what to do. Earlier today, the server was working but I went to the preference pane to try and stop it… and the same thing happened… I clicked the "Stop MysQL Server" button, it did some thinking, then nothing. So I restarted the computer and now I'm having the above problems and apparently, the mysql.sock file is not in /tmp/ anymore… it was earlier today.

Any ideas?

Thanks, Hristo

UPDATE 2: error log file:

100701 13:27:03 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
100701 13:27:03 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
100701 13:27:03 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Unable to lock ./ibdata1, error: 35
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
100701 13:27:03  InnoDB: Retrying to lock the first data file
InnoDB: Unable to lock ./ibdata1, error: 35
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 35

This repeats for a while and then...

InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 35
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
100701 13:28:43  InnoDB: Unable to open the first data file
InnoDB: Error in opening ./ibdata1
100701 13:28:43  InnoDB: Operating system error number 35 in a file operation.
InnoDB: Error number 35 means 'Resource temporarily unavailable'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
100701 13:28:43 [ERROR] Plugin 'InnoDB' init function returned error.
100701 13:28:43 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
100701 13:28:43 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
100701 13:28:43 [ERROR] Do you already have another mysqld server running on port: 3306 ?
100701 13:28:43 [ERROR] Aborting

100701 13:28:43 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

100701 13:28:43 mysqld_safe mysqld from pid file /usr/local/mysql/data/pid_file.pid ended

UPDATE: after trying to start MySQL with sudo sh -x

Hristo$ sudo sh -x /usr/local/mysql/support-files/mysql.server start
+ basedir=
+ datadir=
+ service_startup_timeout=900
+ pid_file=
+ server_pid_file=
+ use_mysqld_safe=1
+ user=mysql
+ test -z ''
+ basedir=/usr/local/mysql
+ bindir=./bin
+ test -z ''
+ datadir=/usr/local/mysql/data
+ sbindir=./bin
+ libexecdir=./bin
+ datadir_set=
+ lsb_functions=/lib/lsb/init-functions
+ test -f /lib/lsb/init-functions
+ PATH=/sbin:/usr/sbin:/bin:/usr/bin:/usr/local/mysql/bin
+ export PATH
+ mode=start
+ shift
+ other_args=
+ case `echo "testing\c"`,`echo -n testing` in
++ echo 'testing\c'
++ echo -n testing
+ echo_n=
+ echo_c='\c'
+ test -x ./bin/my_print_defaults
+ print_defaults=./bin/my_print_defaults
+ extra_args=
+ test -r /usr/local/mysql/my.cnf
+ test -r /usr/local/mysql/data/my.cnf
++ ./bin/my_print_defaults mysqld server mysql_server mysql.server
+ parse_server_arguments
++ ./bin/my_print_defaults manager
+ parse_manager_arguments
+ test -z ''
++ /bin/hostname
+ pid_file=/usr/local/mysql/data/mysqlmanager-pid_file.pid
+ test -z ''
++ /bin/hostname
+ server_pid_file=/usr/local/mysql/data/pid_file.pid
+ case "$mode" in
+ cd /usr/local/mysql
+ manager=./bin/mysqlmanager
+ test -x ./bin/mysqlmanager
+ manager=./bin/mysqlmanager
+ echo 'Starting MySQL'
Starting MySQL
+ test -x ./bin/mysqlmanager -a 1 = 0
+ test -x ./bin/mysqld_safe
+ pid_file=/usr/local/mysql/data/pid_file.pid
+ wait_for_pid created 23653
+ verb=created
+ manager_pid=23653
+ i=0
+ avoid_race_condition='by checking again'
+ test 0 -ne 900
+ case "$verb" in
+ test -s /usr/local/mysql/data/pid_file.pid
+ test -n 23653
+ kill -0 23653
+ :
+ echo '.\c'
.+ ./bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/pid_file.pid
++ expr 0 + 1
+ i=1
+ sleep 1
+ test 1 -ne 900
+ case "$verb" in
+ test -s /usr/local/mysql/data/pid_file.pid
+ test -n 23653
+ kill -0 23653
+ :
+ echo '.\c'
.++ expr 1 + 1
+ i=2
+ sleep 1
+ test 2 -ne 900
+ case "$verb" in
+ test -s /usr/local/mysql/data/pid_file.pid
+ test -n 23653
+ kill -0 23653
+ :

This repeats for a long time...

+ echo '.\c'
.++ expr 97 + 1
+ i=98
+ sleep 1
+ test 98 -ne 900
+ case "$verb" in
+ test -s /usr/local/mysql/data/pid_file.pid
+ test -n 23653
+ kill -0 23653
+ :
+ echo '.\c'
.++ expr 98 + 1
+ i=99
+ sleep 1
+ test 99 -ne 900
+ case "$verb" in
+ test -s /usr/local/mysql/data/pid_file.pid
+ test -n 23653
+ kill -0 23653
+ :
+ echo '.\c'
.++ expr 99 + 1
+ i=100
+ sleep 1
+ test 100 -ne 900
+ case "$verb" in
+ test -s /usr/local/mysql/data/pid_file.pid
+ test -n 23653
+ kill -0 23653
+ test -n 'by checking again'
+ avoid_race_condition=
+ continue
+ test 100 -ne 900
+ case "$verb" in
+ test -s /usr/local/mysql/data/pid_file.pid
+ test -n 23653
+ kill -0 23653
+ test -n ''
+ log_failure_msg 'Manager of pid-file quit without updating file.'
+ echo ' ERROR! Manager of pid-file quit without updating file.'
 ERROR! Manager of pid-file quit without updating file.
+ return 1
+ return_value=1
+ test -w /var/lock/subsys
+ exit 1

Answer 1

Did you check the permissions for the directory where the PID file is to be created (/var/run or what is it)?

It might also be something with wrong options in my.cnf. Try to launch MySQL without my.cnf (rename it) or comment out everything except for the absolutely necessary options.

Answer 2

There are several possible reasons, there's not nearly enough information in your question for us to determine the cause of your problem with any certainty.

In particular:

How did you install MySQL? Did you use a package? If so, which one? Or did you build from source?

Here are some suggestions:

If you change your start command to:

sudo sh -x /usr/local/mysql/support-files/mysql.server start

You'll see every command that the script executes and that will give you (us) help understanding where things are going wrong.

On my OSX 10.5 install, everything under /usr/local/mysql is owned root:wheel except /usr/local/mysql/data which is _mysql:wheel. This is probably the most common mysql startup problem.

The error log is likely to be informative and is probably to found at /usr/local/mysql/data/HOSTNAME.err.

That should be enough to enable you to fix things for yourself; if not, answer my question and post your log and we can go from there.

Answer 3

If you installed from the DMG, there's a startup script that comes with it, did you double click that?

ok, that script installs stuff in /Library/StartupItems/MySQLCOM

instead of doing the mysql.server try starting/stopping with that. I know all it's doing is calling mysql.server, but I'm wondering if everything is indeed installed properly or not. The following is copy pasted from the README on the DMG.

If you have installed the Startup Item, use this command:

 shell> sudo /Library/StartupItems/MySQLCOM/MySQLCOM start
 (ENTER YOUR PASSWORD, IF NECESSARY)
 (PRESS CONTROL-D OR ENTER "EXIT" TO EXIT THE SHELL)

If you don't use the Startup Item, enter the following command sequence:

 shell> cd /usr/local/mysql
 shell> sudo ./bin/mysqld_safe
 (ENTER YOUR PASSWORD, IF NECESSARY)
 (PRESS CONTROL-Z)
 shell> bg
 (PRESS CONTROL-D OR ENTER "EXIT" TO EXIT THE SHELL)