MySQL Bugs: #71086: Starting Mysql service on CentOS6.5

Bug #71086	Starting Mysql service on CentOS6.5
Submitted:	5 Dec 2013 7:40	Modified:	15 Jan 2014 11:07
Reporter:	Fannuel Wamambo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.1.71	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	MySQL

Description:
I upgraded my CentOS6.4 VM to 6.5 and discovered I can no longer run mysql server. I checked mysql logs and surprisingly, it was not logging anything. CentOS 6.5 is a fresh release as of the time of writing this bug(less than a 1 week old).

I tried to see if I could 're-create' the problem by running an upgrade on another VPS and a fresh CentOS 6.5 installation. On the fresh installation, i was able to run mysql until I did a restart and then i faced the same problem.

When I check on the service status, I get a Mysql stopped status and when i run a start or restart, it says: 

Another MySQL daemon already running with the same unix socket.
Starting mysqld:                                           [FAILED]

Does anyone know what could be happening here?

How to repeat:
Run a yum update -y on a centos 6.4, check the mysql service status and see if you can start it.
alternatively, install a fresh 6.5 and the mysql service, start it and then restart the server and check status

Please, send the output of:

ps aux | grep mysqld

the out of ps aux | grep mysqld is as follows:
root      6765  0.0  0.0   4356   728 pts/0    S+   17:35   0:00 grep mysqld

Normally, I would run ps -e | grep mysql and this is not returning anything.

I'm seeing the same thing on CentOS 6.5, which I did not see on 6.4 or prior. This is with MySQL 5.1.71.

Upon CentOS shutdown, the socket file from the running mysqld is not cleaned up and remains on reboot.

In my /etc/my.cnf I have:

socket=/var/lib/mysql/mysql.sock

Even though ps shows no mysqld running, the file /var/lib/mysql/mysql.sock exists.

My work-around is to remove that file as root, then I'm able to start mysql with

service mysqld start

However, the mysql.sock file does get cleaned up properly with

service mysqld stop

So it must have to do with the CentOS 6.5 shutdown or MySQL's response to it.

Kevin

A search in the log file /var/log/mysqld.log showed I had a permissions problem on /tmp in the CentOS 6.5 system affecting the mysqld startup. The /tmp problem was likely due to manual maintenance work done as part of the upgrade to CentOS 6.5, unrelated to MySQL and likely unrelated to CentOS.

131207 21:15:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
^G/usr/libexec/mysqld: Can't create/write to file '/tmp/ib0h0Wt3' (Errcode: 13)
131207 21:15:47  InnoDB: Error: unable to create temporary file; errno: 13
131207 21:15:47 [ERROR] Plugin 'InnoDB' init function returned error.
131207 21:15:47 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131207 21:15:47 [Note] Event Scheduler: Loaded 0 events
131207 21:15:47 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.71'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

My best guess is that mysqld started up, established /var/lib/mysql/mysql.sock, and then crashed as a secondary consequence of the previous error writing to my /tmp, and without cleaning up /var/lib/mysql/mysql.sock.

After resolving the permissions problem on my /tmp, reboot of the CentOS system now results in a properly running MySQL upon CentOS reboot.

Kevin

On the other hand, here is a scenario where MySQL 5.1.71 does not shut down gracefully under a CentOS 6.5 reboot, leaving the old /var/lib/mysql/mysql.sock such that mysqld will not start up.

I am consistently able to reproduce this error on a CentOS 6.5 KVM guest virtual system by rebooting the host CentOS 6.5 system. CentOS is supposed to gracefully shut down the guest systems, but this seems to be failing in the case of mysqld.

Since the reboot of the guest virtual system itself does not trigger the problem, I speculate it's probably more a CentOS 6.5 error than a MySQL error.

Thanks,
Kevin

Actually, MySQL requires to be shutdown properly and both Red Hat and CentOS do not do it correctly. 

You will always have problems with MySQL if user under whose uid MySQL is running does not have sufficient privileges on all of it's directories, including tmpdir.

Thanks guys. I was able to overcome this by deleting the /var/lib/mysql/mysql.sock file and changing /tmp permissions to 777 recursively. After a reboot, mysql was running properly.

Once again thanks.

Does anyone know a better way of dealing with this issue? I have had to delete the mysql.lock file, changed permissions for /tmp and restart a few times and this becomes a problem becomes I have to constantly check if the service is running. Its as if I'm operating with a chkconfig off.

I have the exact same problem as Fannuel described on Centos 6.5 (runing on a virtual server) and MySQL 5.1.71.

Can't wrap my head around it...

Hi,

I was finally able resolve this issue thanks to this RedHat 6.5 bug report:
https://bugzilla.redhat.com/show_bug.cgi?id=1037650
by following Honza Horak's solution (comment 6) and changing the /etc/init.d/mysqld file (so that it checks whether the socket is being used).

I don't know whether this is a completely "kosher" solution, but it works for me.

Hope this helps someone.

P.S.: I got to it through this page: http://kb.parallels.com/en/119334 which also describes other possible causes and solutions.