www.webdeveloper.com
Results 1 to 3 of 3

Thread: MySQL crashing with 'Too many connections' and InnoDB Errrors

  1. #1
    Join Date
    Sep 2008
    Posts
    120

    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?

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,617
    I'm guessing the INNODB errors *might* be a disk space issue?

    As far as too many connections, it might be related to the above faults leaving "zombie" processes around. Another thing to check is if you are using persistent connections, e.g. using mysql_pconnect() instead of the non-persistent mysql_connect(). From what I've seen, the "P" version is more likely to create zombie processes if everything is not configured just the right way, so I usually avoid using pconnect.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Sep 2008
    Posts
    120
    Thanks. I totally removed InnoDB and will see how it goes now.
    I am not using pconnect as I am aware of this possible issue related to it.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles