Bug #61700 MySQL goes to improper state after starting when already running
Submitted: 29 Jun 2011 18:50 Modified: 9 Aug 2011 19:36
Reporter: Veerabahu Subramanian Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.5_10 OS:Linux
Assigned to: CPU Architecture:Any
Tags: mysql start

[29 Jun 2011 18:50] Veerabahu Subramanian
Description:
Giving /etc/init.d/mysql start when mysql is already started ends up in mysql going to some improper state. 
Following log statements are observed in error.log
{
{ 
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: 11 
InnoDB: Check that you do not already have another mysqld process 
InnoDB: using the same InnoDB data or log files. 
110624 23:56:49 InnoDB: Unable to open the first data file 
InnoDB: Error in opening ./ibdata1 
110624 23:56:49 InnoDB: Operating system error number 11 in a file operation. 
InnoDB: Error number 11 means 'Resource temporarily unavailable'. 
InnoDB: Some operating system error numbers are described at 
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html 
110624 23:56:49 InnoDB: Could not open or create data files. 
110624 23:56:49 InnoDB: If you tried to add new data files, and it failed here, 
110624 23:56:49 InnoDB: you should now edit innodb_data_file_path in my.cnf back 
110624 23:56:49 InnoDB: to what it was, and remove the new ibdata files InnoDB created 
110624 23:56:49 InnoDB: in this failed attempt. InnoDB only wrote those files full of 
110624 23:56:49 InnoDB: zeros, but did not yet use them in any way. But be careful: do not 
110624 23:56:49 InnoDB: remove old data files which contain your precious data! 
110624 23:56:49 [ERROR] Plugin 'InnoDB' init function returned error. 
110624 23:56:49 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 
110624 23:56:49 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 
110624 23:56:49 [ERROR] Do you already have another mysqld server running on port: 3306 ? 
110624 23:56:49 [ERROR] Aborting 

110624 23:56:49 [Note] /usr/sbin/mysqld: Shutdown complete 

110624 23:56:49 mysqld_safe mysqld from pid file /var/lib/mysql/Veerabahu.pid ended 

} 
Though the error message seems obvious. Confusion is why MySQL is trying to start when it is already started and goes to such a state ? 
} 

Environment tested :- 
  RHEL 5 update 4 (64 bit) and RHEL 4 update 5 (32 bit) 
  5.5.10-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) 
and corresponding 32 bit version

How to repeat:
Start MySQL :-
[veechand@Veerabahu ~]$ sudo /etc/init.d/mysql start 
Starting MySQL.. [ OK ] 
See MySQL status :-
[veechand@Veerabahu ~]$ sudo /etc/init.d/mysql status 
MySQL running (16322) [ OK ] 
Start MySQL again :-
[veechand@Veerabahu ~]$ sudo /etc/init.d/mysql start 
Starting MySQL [ OK ] 
Now see MySQL status :-
[veechand@Veerabahu ~]$ sudo /etc/init.d/mysql status 
/etc/init.d/mysql: line 363: test: 16948: binary operator expected 
MySQL is running but PID file could not be found [FAILED] 

Suggested fix:
To avoid this, can MySQL startup script (mysql) be changed to check for status before starting.
[4 Jul 2011 9:58] Veerabahu Subramanian
There is no way to stop user of our application to do "/etc/init.d/mysql start". So when user does the same, mysql goes to improper state and thereby our application too. After this development need to get involved to restore the state of MySQL and our application back. As this is more often seen, request of for a quick fix / atleast workaround.
[5 Jul 2011 17:03] Valeriy Kravchuk
Check Bug #23790. Looks like a duplicate/closely related bug.
[5 Aug 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[6 Aug 2011 5:34] Veerabahu Subramanian
Yes the issues seems to be related to #23790. However, the issue #23790 is filed in Version 5.0 but I am observing the issue in 5.5. Can you clarify is the issue resolved in 5.5 also.
[9 Aug 2011 19:36] Sveta Smirnova
Thank you for the feedback.

Bug #23790 in "Verified" state. This means bug was confirmed internally and will be fixed in future versions. currently it is not fixed. So closing this bug as duplicate of bug #23790