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

 

[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,

 

[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.

 

[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.

 

[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

 

[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.

 

[drp]

Thanks. That makes sense now. I think I've found a solution here (option 2):
http://www.webtrafficexchange.com/solved-mysql-crash-fatal-error-cannot-allocate-memory-buffer-pool

I'm also hosting with Digital Ocean. Do you think that will solve it? Or am I better off just switching to a server with more memory?

 

[cPanelMichael]

Hello,

You are welcome to try that solution before adding more memory to your system to see if it works.

Thank you.

 

[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

 

[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.