1

The database server of MySQL is doing some task around 12 hours now even though I haven't made any query during the time.

The CPU doesn't make any significant acitivity but the hard disk is in 100% usage all the time. So I guess the server is doing some work constructing index maybe, because it happened after I inserted around 3.5G of data.

Now, I want to shutdown my db server but I'm worrying if this will corrupt the database. Is there any safe way to shutdown the server? Or do I have to wait until the task is done?


I am adding more details for clarification and explain the new progress.

Before the problem happens, I was importing dump file which was around 5.5G in size. (I was executing the command from python script.)

But it took too long time so I stopped the python process before the import is completed. After that, the mysql server was doing the IO task for more than 12 hours and it is still doing.

With the advice from powerload79, I did query

   SHOW FULL PROCESSLIST; 

+----+-----------------+-----------------+--------+---------+--------+------------------------+-----------------------+
| Id | User            | Host            | db     | Command | Time   | State                  | Info                  |
+----+-----------------+-----------------+--------+---------+--------+------------------------+-----------------------+
|  4 | event_scheduler | localhost       | NULL   | Daemon  | 148754 | Waiting on empty queue | NULL                  |
| 28 | root            | localhost:29209 | bitmex | Sleep   |  57594 |                        | NULL                  |
| 30 | root            | localhost:51361 | bitmex | Query   |      0 | starting               | SHOW FULL PROCESSLIST |
+----+-----------------+-----------------+--------+---------+--------+------------------------+-----------------------+
3 rows in set (0.00 sec)

Full process query result in png file

It seemed there was no process doing IO task. So I tried to stop the server.

But the server didn't stop and went into a stop pending state. More than an hour was passed in the stop pending state. There is still a lot of activity in hard disk in the pending state. And the server is in offline and I cannot connect to the server from client anymore.

This is error logs generated when I tried to stop the server:

2020-03-15T03:15:00.384460Z 0 [System] [MY-013105] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Normal shutdown.

2020-03-15T03:15:02.386568Z 0 [Warning] [MY-010909] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Forcing close of thread 28  user: 'root'.

2020-03-15T03:15:02.387218Z 0 [Warning] [MY-010909] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Forcing close of thread 30  user: 'root'.

2 Answers 2

3

There is no such automated task built into MySQL, so you most definitely need to figure out what it is doing. Most likely somebody set up a backup to happen every 12 hours. You should be able to see a process running, both in the list of system processes and in MySQL using SHOW FULL PROCESSLIST in MySQL.

Hard-killing MySQL should be safe, provided that you are using only InnoDB engine, you don't have any safety features disabled in the MySQL config, write cache flushing is enabled on your disks, and you are positively certain that your disks aren't lying to you about those cache flushes. This is essentially not dissimilar from simulating a sudden power failure, which modern implementations of file systems and databases are reasonably robust against unless the hardware is lying (yes, it happens, especially with older consumer grade SSDs).

Even if everything is in the stack is honest there is a caveat - MySQL start after an unclean shutdown requires a full replay of the InnoDB transaction log to make sure the data is in a consistent state. Depending on the size of your transaction log and the speed of your disks, this can take a long time.

3
  • I checked the full process list and it seemed nothing was going on. And I tried to stop the mysql but it went to the stop pending state. More than an hour passed in stop pending state. Is it safe just to turn off the machine? I added extra description on what I did in the original post. Mar 15, 2020 at 5:03
  • I waited 5 more hours but no change. So I killed the mysqld process. And I restarted the mysql with innodb-force-recovery=1. And drop the table with the problem. And it seems things work fine. Mar 15, 2020 at 10:36
  • It sounds like you may have had disk corruption going on. This may be a good time to do a full set of SMART self-tests on your disks, and whatever other integrity checks you have available. Mar 16, 2020 at 13:41
0

I answer my question to explain how I handled this. It's not the solution as I had to drop a table but I could save the other tables.

I look at the process list of computer. There were two mysqld processes. I killed both of them. The server stopped finally.

When I tried to restart the mysql server, mysql made the following error logs:

2020-03-15T10:28:02.224966Z 1 [ERROR] [MY-012179] [InnoDB] Could not find any file associated with the tablespace ID: 21

2020-03-15T10:28:02.225782Z 1 [ERROR] [MY-012964] [InnoDB] Use --innodb-directories to find the tablespace files. If that fails then use --innodb-force-recovery=1 to ignore this and to permanently lose all changes to the missing tablespace(s)

I resrated with option innodb-force-recovery=1. The server started and I could connect to it. After log on to client, I dropped the table which made all the mess.

1
  • Kevin, Welcome to ServerFault. Good job. Mar 15, 2020 at 14:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .