Skip to main content

MySQL keeps failing

drp
Hi, The sites on my server keep going down and I have to restart the server. It seems to be related to a problem with MySQL. The error message that visitors see is: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown storage engine 'InnoDB"> I don't know what triggers it. Any ideas? It's happened enough now that I need to do something about it. I'm currently running MySQL 5.5 so could upgrade to 5.6? Thanks, Clive

Comments

9 comments

Date Votes
  • madmanmachines
    Hi, Have you checked your MySQL error log at '/var/lib/mysql/' ? Could you paste your MySQL configuration?:
    cat /etc/my.cnf
    You may try simply moving the MySQL configuration out of the way and using an empty configuration:
    mv /etc/my.cnf /etc/my.cnf.bak touch /etc/my.cnf /scripts/restartsrv_mysql
    Thanks,
    0
  • drp
    Hi, Have you checked your MySQL error log at '/var/lib/mysql/' ? Could you paste your MySQL configuration?:
    cat /etc/my.cnf
    You may try simply moving the MySQL configuration out of the way and using an empty configuration:
    mv /etc/my.cnf /etc/my.cnf.bak touch /etc/my.cnf /scripts/restartsrv_mysql
    Thanks,

    Here's the config:
    [mysqld] default-storage-engine=MyISAM innodb_file_per_table=1 max_allowed_packet=268435456 open_files_limit=10000
    And here's the contents of the error file:
    141031 10:27:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 141031 10:27:50 [Note] Plugin 'FEDERATED' is disabled. 141031 10:27:50 InnoDB: The InnoDB memory heap is disabled 141031 10:27:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins 141031 10:27:50 InnoDB: Compressed tables use zlib 1.2.3 141031 10:27:50 InnoDB: Using Linux native AIO 141031 10:27:50 InnoDB: Initializing buffer pool, size = 128.0M 141031 10:27:50 InnoDB: Completed initialization of buffer pool 141031 10:27:50 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 141031 10:27:50 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 141031 10:27:51 InnoDB: Waiting for the background threads to start 141031 10:27:52 InnoDB: 5.5.37 started; log sequence number 27839648 141031 10:27:52 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 141031 10:27:52 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 141031 10:27:52 [Note] Server socket created on IP: '0.0.0.0'. 141031 10:27:52 [Note] Event Scheduler: Loaded 0 events "#######################.err" 1174L, 86063C
    Though, the dates look wrong to me.
    0
  • cPanelMichael
    Hello, Have you tried reviewing the last few lines of the MySQL error log? EX:
    tail -500 /var/lib/mysql/$hostname.err
    Thank you.
    0
  • drp
    Hello, Have you tried reviewing the last few lines of the MySQL error log? EX:
    tail -500 /var/lib/mysql/$hostname.err
    Thank you.

    I see. Thanks. Here's everything from that day:
    150413 10:36:34 mysqld_safe Number of processes running now: 0 150413 10:36:34 mysqld_safe mysqld restarted 150413 10:36:36 [Note] Plugin 'FEDERATED' is disabled. 150413 10:36:39 InnoDB: The InnoDB memory heap is disabled 150413 10:36:39 InnoDB: Mutexes and rw_locks use GCC atomic builtins 150413 10:36:39 InnoDB: Compressed tables use zlib 1.2.3 150413 10:36:39 InnoDB: Using Linux native AIO 150413 10:36:41 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 150413 10:36:41 InnoDB: Completed initialization of buffer pool 150413 10:36:41 InnoDB: Fatal error: cannot allocate memory for the buffer pool 150413 10:36:41 [ERROR] Plugin 'InnoDB' init function returned error. 150413 10:36:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 150413 10:36:41 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 150413 10:36:41 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 150413 10:36:41 [Note] Server socket created on IP: '0.0.0.0'. 150413 10:36:42 [Note] Event Scheduler: Loaded 0 events 150413 10:36:42 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.42-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 150413 10:43:45 [Note] /usr/sbin/mysqld: Normal shutdown 150413 10:43:45 [Note] Event Scheduler: Purging the queue. 0 events 150413 10:43:45 [Note] /usr/sbin/mysqld: Shutdown complete 150413 10:43:45 mysqld_safe mysqld from pid file /var/lib/mysql/#########.#########.###.pid ended 150413 10:44:04 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 150413 10:44:04 [Note] Plugin 'FEDERATED' is disabled. 150413 10:44:04 InnoDB: The InnoDB memory heap is disabled 150413 10:44:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins 150413 10:44:04 InnoDB: Compressed tables use zlib 1.2.3 150413 10:44:04 InnoDB: Using Linux native AIO 150413 10:44:04 InnoDB: Initializing buffer pool, size = 128.0M 150413 10:44:04 InnoDB: Completed initialization of buffer pool 150413 10:44:04 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 150413 10:44:04 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 150413 10:44:05 InnoDB: Waiting for the background threads to start 150413 10:44:06 InnoDB: 5.5.42 started; log sequence number 111673446 150413 10:44:06 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 150413 10:44:06 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 150413 10:44:06 [Note] Server socket created on IP: '0.0.0.0'. 150413 10:44:06 [Note] Event Scheduler: Loaded 0 events 150413 10:44:06 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.42-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 150413 11:07:30 [Warning] IP address '############' could not be resolved: Name or service not known 150413 12:26:54 [Warning] IP address '############' could not be resolved: Name or service not known 150413 20:12:06 [Warning] IP address '############' could not be resolved: Name or service not know
    0
  • 24x7server
    Hello, I think your are getting this issues due to insufficient memory installed on your server. I will suggest Please check /var/log/messages and monitor your memory usages.
    0
  • drp
    Thanks. That makes sense now. I think I've found a solution here (option 2):
    0
  • cPanelMichael
    Hello, You are welcome to try that solution before adding more memory to your system to see if it works. Thank you.
    0
  • gadalf
    Hello, Have you tried reviewing the last few lines of the MySQL error log? EX:
    tail -500 /var/lib/mysql/$hostname.err
    Thank you.

    I get no such file or directory when run this comand
    0
  • cPanelMichael
    I get no such file or directory when run this comand

    You must replace "$hostname" with the actual hostname of your server. Thank you.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post