Mysql – Mariadb processes stuck after aborted dump restore. What to do

mariadbmysqldump

I tried to restore a database dump on my local server.
I have created the database locally myself. The dump creates the tables and insert all the data.

I used 'source "C:\my_dump_file.sql"' to start the restore.
Everything goes well until nothing happens at all. No disk activity, no CPU activity, mysql is stuck waiting for something.
I killed the process (I tried CTRL-D and after that, CTRL-C), but now my mariadb installation is in an awful state (see below). It refuses to drop that database. It starts well (all my other databases work) but won't stop.

MariaDB [(none)]> show processlist;
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------+----------+
| Id | User        | Host            | db   | Command | Time | State                    | Info                   | Progress |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------+----------+
|  1 | system user |                 | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL                   |    0.000 |
|  2 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                   |    0.000 |
|  3 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                   |    0.000 |
|  4 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                   |    0.000 |
|  5 | system user |                 | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL                   |    0.000 |
| 10 | root        | localhost:50556 | NULL | Killed  |  677 | Unlocking tables         | DROP DATABASE failzzzz |    0.000 |
| 12 | root        | localhost:50576 | NULL | Query   |    0 | init                     | show processlist       |    0.000 |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------+----------+
7 rows in set (0.00 sec)

Also, here is what the event viewer tells me every time I try to stop the mysql service

InnoDB: Starting shutdown...

For more information, see Help and Support Center at http://www.mysql.com.  

InnoDB: Waiting for buf_dump_threadto exit

For more information, see Help and Support Center at http://www.mysql.com.  

Please note this is actually the second time I see this issue. I encountered it for another (smaller) database, and it wrecked my mariadb installation just the same. Luckily it was my only database at the time, so I ended up throwing mariadb out of the window and starting fresh, which was the quickest thing to do. After I set innodb_buffer_pool_size to 2G for the restore, I retried and everything eventually work. There should be a better way that to try and cross fingers that the value is high enough, right ?

I'm not so lucky this time, I cannot go through a restore process for every database my mariadb is currently running (too many databases, that would be too long).
I have a recent backup of my entire data folder (prior to my restore attempts), if needed.

So, what can I do ?

  • how can I fix my mariadb install ? Is it possible at all ?
  • if it's not fixable, can I use my data folder backup to start over
    fresh (WITHOUT the need to restore every database one by one, which is MUCH more time consuming). Information available online seems to indicate that it is possible (see https://dba.stackexchange.com/a/5933/147390)

Thanks

Best Answer

This is very likely the MariaDBs bug MDEV-15707, which is fixed in 10.2.15 and 10.3.7. Can happen when there is a large secondary index (non-unique), that does not fit into the buffer pool entirely.