SAGShow SAGShow - 4 months ago 16
SQL Question

MySQL unexpectedly crashes

I'm running Ubuntu 14.04 and I've got a few WordPress installations on my server.
Without warning, MySQL crashes every day on my server and I've yet to find the reason.

You can find the error.log below.

160721 9:25:44 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160721 9:25:44 [Note] Plugin 'FEDERATED' is disabled.
160721 9:25:44 InnoDB: The InnoDB memory heap is disabled
160721 9:25:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160721 9:25:44 InnoDB: Compressed tables use zlib 1.2.8
160721 9:25:44 InnoDB: Using Linux native AIO
160721 9:25:44 InnoDB: Initializing buffer pool, size = 128.0M
160721 9:25:44 InnoDB: Completed initialization of buffer pool
160721 9:25:44 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 40817296
160721 9:25:44 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...
InnoDB: Doing recovery: scanned up to log sequence number 41068976
160721 9:25:45 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
160721 9:25:45 InnoDB: Waiting for the background threads to start
160721 9:25:46 InnoDB: 5.5.47 started; log sequence number 41068976
160721 9:25:46 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
160721 9:25:46 [Note] - '127.0.0.1' resolves to '127.0.0.1';
160721 9:25:46 [Note] Server socket created on IP: '127.0.0.1'.
160721 9:25:46 [Note] Event Scheduler: Loaded 0 events
160721 9:25:46 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.47-0ubuntu0.14.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
160721 17:50:13 [Note] /usr/sbin/mysqld: Normal shutdown

160721 17:50:13 [Note] Event Scheduler: Purging the queue. 0 events
160721 17:50:13 InnoDB: Starting shutdown...
160721 17:50:14 InnoDB: Shutdown completed; log sequence number 41400934
160721 17:50:14 [Note] /usr/sbin/mysqld: Shutdown complete

160721 17:50:15 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160721 17:50:15 [Note] Plugin 'FEDERATED' is disabled.
160721 17:50:15 InnoDB: The InnoDB memory heap is disabled
160721 17:50:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160721 17:50:15 InnoDB: Compressed tables use zlib 1.2.8
160721 17:50:15 InnoDB: Using Linux native AIO
160721 17:50:15 InnoDB: Initializing buffer pool, size = 128.0M
160721 17:50:15 InnoDB: Completed initialization of buffer pool
160721 17:50:15 InnoDB: highest supported file format is Barracuda.
160721 17:50:15 InnoDB: Waiting for the background threads to start
160721 17:50:16 InnoDB: 5.5.47 started; log sequence number 41400934
160721 17:50:16 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
160721 17:50:16 [Note] - '127.0.0.1' resolves to '127.0.0.1';
160721 17:50:16 [Note] Server socket created on IP: '127.0.0.1'.
160721 17:50:16 [Note] Event Scheduler: Loaded 0 events
160721 17:50:16 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.47-0ubuntu0.14.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
160722 5:44:00 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160722 5:44:00 [Note] Plugin 'FEDERATED' is disabled.
160722 5:44:00 InnoDB: The InnoDB memory heap is disabled
160722 5:44:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160722 5:44:00 InnoDB: Compressed tables use zlib 1.2.8
160722 5:44:00 InnoDB: Using Linux native AIO
160722 5:44:00 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160722 5:44:00 InnoDB: Completed initialization of buffer pool
160722 5:44:00 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160722 5:44:00 [ERROR] Plugin 'InnoDB' init function returned error.
160722 5:44:00 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160722 5:44:00 [ERROR] Unknown/unsupported storage engine: InnoDB
160722 5:44:00 [ERROR] Aborting

160722 5:44:00 [Note] /usr/sbin/mysqld: Shutdown complete

160722 5:44:01 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160722 5:44:01 [Note] Plugin 'FEDERATED' is disabled.
160722 5:44:01 InnoDB: The InnoDB memory heap is disabled
160722 5:44:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160722 5:44:01 InnoDB: Compressed tables use zlib 1.2.8
160722 5:44:01 InnoDB: Using Linux native AIO
160722 5:44:01 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160722 5:44:01 InnoDB: Completed initialization of buffer pool
160722 5:44:01 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160722 5:44:01 [ERROR] Plugin 'InnoDB' init function returned error.
160722 5:44:01 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160722 5:44:01 [ERROR] Unknown/unsupported storage engine: InnoDB
160722 5:44:01 [ERROR] Aborting

160722 5:44:01 [Note] /usr/sbin/mysqld: Shutdown complete

160722 5:50:30 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160722 5:50:30 [Note] Plugin 'FEDERATED' is disabled.
160722 5:50:30 InnoDB: The InnoDB memory heap is disabled
160722 5:50:30 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160722 5:50:30 InnoDB: Compressed tables use zlib 1.2.8
160722 5:50:30 InnoDB: Using Linux native AIO
160722 5:50:30 InnoDB: Initializing buffer pool, size = 128.0M
160722 5:50:30 InnoDB: Completed initialization of buffer pool
160722 5:50:31 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 41616363
160722 5:50:31 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...
InnoDB: Doing recovery: scanned up to log sequence number 46858752
InnoDB: Doing recovery: scanned up to log sequence number 48741839
160722 5:50:31 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
160722 5:50:32 InnoDB: Waiting for the background threads to start
160722 5:50:33 InnoDB: 5.5.47 started; log sequence number 48741839
160722 5:50:33 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
160722 5:50:33 [Note] - '127.0.0.1' resolves to '127.0.0.1';
160722 5:50:33 [Note] Server socket created on IP: '127.0.0.1'.
160722 5:50:33 [Note] Event Scheduler: Loaded 0 events
160722 5:50:33 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.47-0ubuntu0.14.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306

Answer

InnoDB: mmap(137363456 bytes) failed; errno 12

Case 1: You have a tiny 1G server (or VM). If so, lower innodb_buffer_pool_size to, say, 32M.

Case 2: You have increased various other mysql settings to unreasonably high values. Undo.

Case 3: You have lots of applications in your small server; they are consuming most of RAM. Find and fix.

If you need further assistance, tell us how much RAM you have, what else is running and provide my.cnf.

Or you could get more RAM.