Database inaccessible cannot be USEd or dumped

Fri, Oct 11, 2019

mariadb how to tell if table is locked
mariadb how to tell if database is locked
mysql database frozen
mysql table hangs, never comes up with “USE” (i keep typing table when i mean database)
mysql database hangs, never comes up with “USE”
unusable database what to do mariadb
mariadb database locked
mariadb fix broken database
Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2 “No such file or directory”)’

Entertainingly written for documentation, but no answers here:

https://mariadb.com/kb/en/library/mysqldump/

Not my problem:

https://stackoverflow.com/questions/28400488/mysqldump-hangs-with-routines

But it mentions mysql.proc being able to get a lock which could be the issue?

OK but all that’s wild speculation and getting ahead of proper first steps.

The proper first step is to use mysqlcheck.

Where my database is named “example”:

mysqlcheck example
example.access                                      OK
example.actions                                     OK
example.actions_aid                                 OK
example.activitystream                              OK
example.activitystream_accounts                     OK
example.advanced_help_index                         OK
example.authmap                                     OK
example.batch                                       OK
example.better_formats_defaults                     OK
example.blocks                                      OK
example.blocks_roles                                OK
example.boxes                                       OK

And now mysqlcheck is hanging, right there. I’ll bet anything it’s the Drupal cache table is so supernaturally corrupted that it’s freezing all these standard commands that i’m sure are supposed to work, or at least give some sort of reasonable error message, no matter what.

mysqlcheck freezes before table

mysql
use mysql
show processlist
+-------+--------+-----------+--------+------------+--------+---------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id    | User   | Host      | db     | Command    | Time   | State                           | Info                                                                                                 | Progress |
+-------+--------+-----------+--------+------------+--------+---------------------------------+------------------------------------------------------------------------------------------------------+----------+
|  4868 | root   | localhost | anjali | Query      | 126507 | closing tables                  | show fields from `webform_submissions`                                                               |    0.000 |
|  4916 | anjali | localhost | anjali | Query      | 126355 | Waiting for table level lock    | UPDATE variable SET value = 'b:1;' WHERE name = 'drupal_http_request_fails'                          |    0.000 |
|  5210 | anjali | localhost | anjali | Query      | 125990 | Waiting for table level lock    | UPDATE variable SET value = 'b:1;' WHERE name = 'drupal_http_request_fails'                          |    0.000 |
|  5264 | anjali | localhost | anjali | Query      | 125899 | Waiting for table level lock    | UPDATE sessions SET uid = 0, cache = 0, hostname = '162.158.93.196', session = '', timestamp = 15707 |    0.000 |
|  5285 | anjali | localhost | anjali | Query      | 125849 | Waiting for table level lock    | UPDATE sessions SET uid = 0, cache = 0, hostname = '162.158.92.75', session = '', timestamp = 157078 |    0.000 |
|  5310 | anjali | localhost | anjali | Query      | 125799 | Waiting for table level lock    | UPDATE sessions SET uid = 0, cache = 0, hostname = '162.158.89.140', session = '', timestamp = 15707 |    0.000 |
|  5359 | anjali | localhost | anjali | Query      | 125622 | Waiting for table level lock    | UPDATE variable SET value = 'b:1;' WHERE name = 'drupal_http_request_fails'                          |    0.000 |
|  5449 | anjali | localhost | anjali | Query      | 125257 | Waiting for table level lock    | UPDATE variable SET value = 'b:1;' WHERE name = 'drupal_http_request_fails'                          |    0.000 |
|  5541 | anjali | localhost | anjali | Query      | 125026 | Waiting for table level lock    | DELETE FROM cache WHERE cid = 'variables'                                                            |    0.000 |
|  5717 | anjali | localhost | anjali | Query      | 124720 | Waiting for table level lock    | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = '62e1u6a8f52qs16kv |    0.000 |
|  5739 | anjali | localhost | anjali | Query      | 124619 | Waiting for table level lock    | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = '62e1u6a8f52qs16kv |    0.000 |

and it just goes on and on

There were several problems on sojourner after the reboot the night before. One site stopped even being able to do a database dump, and looking at the mysql processlist it had a ton of outstanding processes, one with state ‘closing tables’ and almost all the rest with ‘Waiting for table level lock’. Figured i’d restart mysql rather than to try killing all of those individually, but the service mysql stop command is hanging too, so at some point i’ll probably commence freaking out.

never mind it finished! … and an error starting up

mysqld_safe Can’t log to error log and syslog at the same time.

… is the only thing in the systemctl status mysql.service that even means anything
… i think that might be the actual error? That logging is misconfigured?

no this looks more likely:
mysqld_safe A mysqld process already exists
OK here’s the error:
sojourner /etc/init.d/mysql[14774]: 1 processes alive and ‘/usr/bin/mysqladmin –defaults-file=/etc/mysql/debian.cnf ping’ resulted in 15:17 Oct 12 15:16:09 sojourner /etc/init.d/mysql[14774]: [61B blob data] 15:17 Oct 12 15:16:09 sojourner /etc/init.d/mysql[14774]: error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2 “No such file or directory”)’ 15:17 Oct 12 15:16:09 sojourner /etc/init.d/mysql[14774]: Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists! 15:25 and the real error behind that is this: 15:25 Oct 12 15:22:32 sojourner /etc/init.d/mysql[16862]: 191012 15:22:32 mysqld_safe Can’t log to error log and syslog at the same time. Remove all –log-error configuration options for –syslog to take effect. 15:25 Oct 12 15:22:32 sojourner /etc/init.d/mysql[16862]: 191012 15:22:32 mysqld_safe A mysqld process already exists 15:26 the mysqld process already exists one. I think. 15:43 Well it ultimately took resorting to kill -9 on otherwise-indestructible mysqld_safe and mysqld processes, but that did it, all databases back up and working

ps aux | grep mysqld

Couldn’t find any service other than mysql to stop to end these processes.

kill didn’t work.

Had to kill -9 on the processes identified by the ps aux | grep mysqld command did it.

Things found in the meantime, some of which said to do the above as a last (or in some cases, first) resort: