MySQL crashing with 'Too many connections' and InnoDB Errrors
Hi,
Suddenly my Mysql server showed an error message "Too many connections" and started to be about 60-70% unavailable, I mean, from 5 reloads of a page from the server arouns 2 were successfull.
This is the first part of the issue which concerns me as this server is genereally limited to a restricted number of custommers, which are perforiming a same load for long time so far. What I mean is - I can not imagine or reckon anything which had happened like something new on this server yesterday, so this crash can occur. MySQL max connections is set to 500. Genereally the server is showing around 10-50, after the restart. Shame I didnt look at this at the time of the emergency.
But lets explain to the end - after I got this message, I tried to restart the Mysql. I got a huge delay, after which MySQL said it can not be shutdown. I terminated the command with Ctrl+C (working from a SSH console), and retyped it again with. The second restart was "successfull" at least like a message I got in the console. However, when I tried to open a page from the site, I got an error: "Unknown table engine 'InnoDB'".
Then I rebooted the server. After this all started working perfectly, connections were showing the nice values in the range of 10-50, as I mentioned above, no crashes. However, I redirected all trafic to my second server till I solve this and I am still suspicious if this can be the reason for not having problems currently - the lack of any load.
I opened the logs and I saw the following:
Code:
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
[this message repeats a lot of times]
131104 14:57:44 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.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!
131104 14:57:44 [ERROR] Plugin 'InnoDB' init function returned error.
131104 14:57:44 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131104 14:57:44 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
131104 14:57:44 [ERROR] Do you already have another mysqld server running on port: 3306 ?
131104 14:57:44 [ERROR] Aborting
131104 14:57:44 [Note] /usr/libexec/mysqld: Shutdown complete
131104 14:57:44 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[again lot of times]:
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: Error in opening ./ibdata1
131104 14:58:46 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.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!
131104 14:58:46 [ERROR] Plugin 'InnoDB' init function returned error.
131104 14:58:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131104 14:58:46 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
131104 14:58:46 [ERROR] Do you already have another mysqld server running on port: 3306 ?
131104 14:58:46 [ERROR] Aborting
131104 14:58:46 [Note] /usr/libexec/mysqld: Shutdown complete
131104 14:58:46 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
131104 15:02:09 InnoDB: Starting shutdown...
131104 15:02:11 InnoDB: Shutdown completed; log sequence number 17 3155286261
131104 15:02:11 [Note] /usr/libexec/mysqld: Shutdown complete
131104 15:02:11 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
131104 15:02:45 [Note] /usr/libexec/mysqld: Normal shutdown
131104 15:02:45 [Note] Event Scheduler: Purging the queue. 0 events
131104 15:02:47 [Note] /usr/libexec/mysqld: Shutdown complete
131104 15:05:21 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
131104 15:05:21 InnoDB: Initializing buffer pool, size = 8.0M
131104 15:05:21 InnoDB: Completed initialization of buffer pool
131104 15:05:21 InnoDB: Started; log sequence number 17 3155286261
131104 15:05:21 [Note] Event Scheduler: Loaded 0 events
131104 15:05:21 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.69' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
131104 15:26:58 [Note] /usr/libexec/mysqld: Normal shutdown
131104 15:26:58 [Note] Event Scheduler: Purging the queue. 0 events
131104 15:27:00 InnoDB: Starting shutdown...
131104 15:27:03 InnoDB: Shutdown completed; log sequence number 17 3155286431
131104 15:27:03 [Note] /usr/libexec/mysqld: Shutdown complete
131104 15:27:03 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
131104 15:29:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
131104 15:29:39 InnoDB: Initializing buffer pool, size = 8.0M
131104 15:29:39 InnoDB: Completed initialization of buffer pool
131104 15:29:40 InnoDB: Started; log sequence number 17 3155286431
131104 15:29:40 [Note] Event Scheduler: Loaded 0 events
131104 15:29:40 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.69' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
Now I want to find out WHY this happened and resolve the reason in order to prevent it in future as I can not calm down even if now it is working OK, because I dont know if it will not repeat in future.
I just want to point out that in my mysql tables I am using nowhere InnoDB. If this is the reason for all troubles, is there any way to remove the InnoDB option from the whole MySQL server? However, if InnoDB is the reason, why I got the 'Too many connections' error before that?
