Mariadb processes stuck after aborted dump restore. What to do?

Question

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

Answer 1

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.

Answer 2

you can kill the mysqld.exe process

taskkill /im mysqld.exe /f

from the elevated command prompt. Then start service again. But before you do this, it would be nice of you to take a process dump (with procdump for example, e.g procdump -mp mysqld.exe) , and file a bug in mariadb bug tracker https://jira.mariadb.org , attaching the dump.

Answer 3

I ended up increasing innodb_buffer_pool_size from 2G to 4G, started a database restore (from scratch) and it worked. I will see if I can report the issue with the low innodb_buffer_pool_size parameter, but as the data and model is corporate information it would need obfuscation. Unless there is an easy, idiot-proof way to do that, I couldn't properly report the issue. The only thing particular with the dump is that one table contains 18M lines.

If anyone knows how to unbrick Mariadb if it fails due to innodb_buffer_pool_size too low, feel free to add it as an answer, I may accept it instead of this one.