1

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

3 Answers 3

0

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.

1
  • I use MariaDB 10.2.14. I will try to confirm it later eventually but this does seem to be it. Nice catch !
    – Toto
    Jun 12, 2018 at 6:31
0

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.

1
  • I have killed the process before, however the dormant neverending tasks stay active whenever MariaDB is restarted, and they prevent Mariadb from shutting properly, forcing me to eventually have the process killed. You are right that it may be an actual bug worth reporting, I will see if I can do that (the table names and data would need to be anonymized first). PS. I have copied my data folder to a new mariadb instance. It does work, all my databases are usable. I still have no way to restore that dump without bricking MariaDB, though
    – Toto
    Mar 25, 2018 at 10:45
0

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.

1
  • An easy, idiot-proof way to report a hang is procdump -mp mysqld.exe, as I told. Often, do not need to share schema or data. If you feel like you can dump all thread callstacks yourself, with cdb, you can, this also will be much appreciated. People who report issues, and attach minidumps, usually have higher, sometimes very high chances to get the reported bugs fixed fast Apr 9, 2018 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.