I just suddenly ran into a big issue of MySQL crashing every couple of minutes on my Lightsail Linux instance.
A quick look at the log (found in /var/log/mysql/error.log revealed:
2019-01-31T16:20:05.372726Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-01-31T16:20:05.372745Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-01-31T16:20:05.372760Z 0 [Note] InnoDB: Uses event mutexes
2019-01-31T16:20:05.372764Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-01-31T16:20:05.372767Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-01-31T16:20:05.372771Z 0 [Note] InnoDB: Using Linux native AIO
2019-01-31T16:20:05.372995Z 0 [Note] InnoDB: Number of pools: 1
2019-01-31T16:20:05.373094Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-01-31T16:20:05.375782Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-01-31T16:20:05.375813Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2019-01-31T16:20:05.375820Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-01-31T16:20:05.375825Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-01-31T16:20:05.375830Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.
2019-01-31T16:20:05.375834Z 0 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
2019-01-31T16:20:05.375837Z 0 [ERROR] Failed to initialize builtin plugins.
2019-01-31T16:20:05.375840Z 0 [ERROR] Aborting
The error basically states that InnoDB is tryign to use 128M of memory, and it is unable to get the memory it needs. So a solution to this is to lower the memory used by InnoDB and hope that the issue resolves itself.
Locate and the /etc/mysql/mysql.conf.d/mysqld.cnf file using one of the following commands:
- sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
OR
- sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
Scroll to the bottom of the file and find the # * InnoDB heading.
Paste the following line – or if it already exists: change it’s value.
Save, and exit the editor.
Now, for this change to take effect, you must restart MySQL with the following:
- sudo /etc/init.d/mysql restart
Make sure you monitor the system to make sure that this has fixed your issue – and check your error log periodically!
If that did not resolve your issue…
There can be a couple of other reasons as to why your InnoDB keeps crashing:
Not enough memory on your server.
It is possible that the reason your system is crashing is due to lack of RAM. To check how much RAM your system is using, run the following command:
- top
Here, you will be able to see how much memory your system is using. If you see a fairly low number under KiB Mem free, you might want to consider upgrading your system.
Creating a swap file might also solve this issue. Learn how to do that here.
MySQL has a pending update, or your update was not installed correctly.
Run the following command to check if your MySQL server needs an update:
- sudo mysql_upgrade -u root -p
Enter your root user MySQL password when prompted.
The system will prompt you if you need to install any updates.
If you see a message stating that your system is up to date, we will force an update just to make sure everything is correct:
- sudo mysql_upgrade –force -u root -p
Enter your password again.
Your server needs an update
Run the following commands to get all the latest package updates for your server:
- sudo apt-get update
- sudo apt-get upgrade
Leave a Reply