petrovnn
By:
petrovnn

Drupal: MySQL dies and no longer works (reboot does not help) Ubuntu 12.04 512bm, Apache

October 9, 2014 4.4k views

For a while, Drupal running on the server is normal, but then there is this error:

PDOException: SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in lock_may_be_available() (line 167 of /var/www/whydrupal.ru/public_html/includes/lock.inc).

earlier helped restart a droplet from the admin DO, but now this reboot does nothing.

Command in console service mysql restart answers:

sop: Unknown instance: 
start: Job failed to start

Logs:

/var/log/mysql/error.log:

140129  9:52:09 [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.
140129  9:52:09 [Note] Plugin 'FEDERATED' is disabled.
140129  9:52:09 InnoDB: The InnoDB memory heap is disabled
140129  9:52:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140129  9:52:09 InnoDB: Compressed tables use zlib 1.2.3.4
140129  9:52:10 InnoDB: Initializing buffer pool, size = 64.0M
InnoDB: mmap(68681728 bytes) failed; errno 12
140129  9:52:10 InnoDB: Completed initialization of buffer pool
140129  9:52:10 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140129  9:52:10 [ERROR] Plugin 'InnoDB' init function returned error.
140129  9:52:10 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140129  9:52:10 [ERROR] Unknown/unsupported storage engine: InnoDB
140129  9:52:10 [ERROR] Aborting
140129  9:52:10 [Note] /usr/sbin/mysqld: Shutdown complete

I changed the parameters of many values ​​in /etc/mysql/my.cnf:

innodb_buffer_pool_size = 128М / 64М / 32M / 16М (does not help)
max_connections = 50    (500 default)
max_user_connections  = 10
innodb_buffer_pool_size = 32M     (128 default)
innodb_force_recovery = 4 (sometimes help but not permanently)

Full text my.cnf: http://pastebin.com/7bXWEtBu

tried to configure my-small.cnf (does not help)
rebooting server (does not help)
rebooting mysql (does not help)

more logs:

/var/log/syslog

Jan 30 07:15:36 localhost kernel: [165391.100711] select 27275 (apache2), adj 0, size 1306, to kill
Jan 30 07:15:36 localhost kernel: [165391.100713] select 21786 (apache2), adj 0, size 4084, to kill
Jan 30 07:15:36 localhost kernel: [165391.100714] select 21845 (apache2), adj 0, size 4454, to kill
Jan 30 07:15:36 localhost kernel: [165391.100715] select 21972 (apache2), adj 0, size 4586, to kill
Jan 30 07:15:36 localhost kernel: [165391.100729] select 1 (init), adj 0, size 365, to kill
Jan 30 07:15:36 localhost kernel: [165391.100732] select 817 (php5-fpm), adj 0, size 825, to kill

Jan 30 08:23:29 localhost kernel: [169464.172067] send sigkill to 24422 (apache2), adj 0, size 11829
Jan 30 08:23:29 localhost /etc/mysql/debian-start[24561]: Upgrading MySQL tables if necessary.
Jan 30 08:23:30 localhost /etc/mysql/debian-start[24564]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Jan 30 08:23:30 localhost /etc/mysql/debian-start[24564]: Looking for 'mysql' as: /usr/bin/mysql
Jan 30 08:23:30 localhost /etc/mysql/debian-start[24564]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jan 30 08:23:30 localhost /etc/mysql/debian-start[24564]: This installation of MySQL is already upgraded to 5.5.35, use --force if you still need to run mysql_upgrade
Jan 30 08:23:30 localhost /etc/mysql/debian-start[24575]: Checking for insecure root accounts.
Jan 30 08:23:30 localhost /etc/mysql/debian-start[24580]: Triggering myisam-recover for all MyISAM tables

in syslog incredible tons of errors that I find it hard to understand

/var/log/mysql/error.log:

140129 11:00:32 InnoDB: Initializing buffer pool, size = 16.0M
140129 11:00:32 InnoDB: Completed initialization of buffer pool
140129 11:00:32 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 71593559
140129 11:00:32  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 71593569
140129 11:00:32  InnoDB: Waiting for the background threads to start
140129 11:00:33 InnoDB: 5.5.35 started; log sequence number 71593569

140215 13:10:48 [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.
140215 13:10:48 [Note] Plugin 'FEDERATED' is disabled.
140215 13:10:48 InnoDB: The InnoDB memory heap is disabled
140215 13:10:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140215 13:10:48 InnoDB: Compressed tables use zlib 1.2.3.4
140215 13:10:48 InnoDB: Initializing buffer pool, size = 16.0M
140215 13:10:48 InnoDB: Completed initialization of buffer pool
InnoDB: Error: checksum mismatch in data file ./ibdata1
140215 13:10:48 InnoDB: Could not open or create data files.
140215 13:10:48 InnoDB: If you tried to add new data files, and it failed here,
140215 13:10:48 InnoDB: you should now edit innodb_data_file_path in my.cnf back
140215 13:10:48 InnoDB: to what it was, and remove the new ibdata files InnoDB created
140215 13:10:48 InnoDB: in this failed attempt. InnoDB only wrote those files full of
140215 13:10:48 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
140215 13:10:48 InnoDB: remove old data files which contain your precious data!
140215 13:10:48 [ERROR] Plugin 'InnoDB' init function returned error.
140215 13:10:48 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140215 13:10:48 [ERROR] Unknown/unsupported storage engine: InnoDB
140215 13:10:48 [ERROR] Aborting

140215 13:10:48 [Note] /usr/sbin/mysqld: Shutdown complete

Google does not help.
Similar unanswered question:
https://www.digitalocean.com/community/questions/mysql-keeping-crashings-on-my-drupal-site

Traffic in my all sites is very small: 200 users per day.
graph load of my server:

How to Raise MySQL?
How to make it work?

1 Answer

The initial error message would indicate that there was not enough memory available for MySQL. This is not uncommon on 512MB droplets. I would recommend enabling a swap file on your droplet to resolve this issue before adjusting your configuration too extensively.

https://www.digitalocean.com/community/tutorials/how-to-add-swap-on-ubuntu-12-04

The latest errors seem to indicate that since MySQL previous crashed there is some corruption of your database files and you may need to take steps to attempt to recover them if they contain important data. If they do not, or you have a backup you can try removing and re-installing MySQL on your droplet and re-importing your database.

by Etel Sverdlov
Linux swaps allow a system to harness more memory than was originally physically available. Here's how to set up a linux swap file on Ubuntu 12.04
  • thanks for the help!

    Set up a SWAP:

    sudo swapon -s
    Filename               Type        Size     Used    Priority
    /swapfile2             file        2097148  76868   -1
    

    hope this helps.

    Note

    The instruction given is 256mb, which is clearly not enough.

    Therefore, after applying the instructions had to make another file / swapfile2 size of 2GB

    This comment did not help. Command swapoff -a return

    swapoff: /swapfile2: swapoff failed: Cannot allocate memory
    

    But this article has helped: http://ericeastwood.com/blog/14/swap-file-guide-ubuntu

    Thank you!

    by Etel Sverdlov
    Linux swaps allow a system to harness more memory than was originally physically available. Here's how to set up a linux swap file on Ubuntu 12.04
Have another answer? Share your knowledge.