MySQL Bugs: #44548: Restoring a table dumped with 'mysqldump' is causing the server to hang

Bug #44548	Restoring a table dumped with 'mysqldump' is causing the server to hang
Submitted:	29 Apr 2009 17:47	Modified:	30 Apr 2009 16:26
Reporter:	Andrew Torry	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql-5.1.34-linux-x86_64-icc-glibc23	OS:	Linux (Ubuntu 8.04 LTS 2.6.24-23-server x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	hang, mysqldump, restore, server

Description:
I am restoring a table that I previously dumped with mysqldump using this command:-  "gzip -d -c rpnashapp.gz | mysql nashapp"

After approximately 30,000 rows the restore hangs the server.
If I turn the binary log off, then the restore works fine.
When the server hangs, a "SHOW PROCESSLIST" has the following:-

+----+------+-----------+---------+---------+------+---------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host      | db      | Command | Time | State         | Info                                                                                                 |
+----+------+-----------+---------+---------+------+---------------+------------------------------------------------------------------------------------------------------+
|  2 | root | localhost | nashapp | Query   | 2278 | freeing items | INSERT INTO `redried_production` VALUES ('1502398997','181','34','ZUSXDL','510','7','1000','PA','100 |
| 12 | root | localhost | NULL    | Query   |    0 | NULL          | show processlist                                                                                     |
+----+------+-----------+---------+---------+------+---------------+------------------------------------------------------------------------------------------------------+

As time passes, the ID being inserted to the table remains as '1502398997' and the only change is the "Time" column gets larger.

Nothing is reported to the MySQL error log.

If I abort the insert I get this message "Query aborted by Ctrl+C" but the shell prompt does not return unless I hit Ctrl+C again.

After this, if I kill the process from within MySQL as 'root', the "state" changes to "Killed" but the entry remains in the "SHOW PROCESSLIST" display.

Trying to shut down the MySQL server via the official shutdown script fails, and I have to "kill -9" the server before restarting.

After the restart, I discover that there's always around 27000-30000 rows in the redried_production table i.e. a number of rows managed to be restored & committed.

I have tried using different data sets restoring into different schemas in case I have "bad data", but the same hang occurs every time.

How to repeat:
Running this command repeats the problem every time:-

"gzip -d -c rpnashapp.gz | mysql nashapp"

Turn off the binary log in the "my.cnf" file, restart the server, and the problem disappears.

I have tried removing the "sync_binlog=1" entry in the my.cnf file, but I get the same problem. I also tried changing the replication binlog_format to "row" to no avail.

Suggested fix:
Allow this restore to happen with binary logging turned on.

Please, send the results of:

show create table `redried_production`\G

I want ot check if this is table is partitioned.

Thank you for the feedback.

Please also check if it is same as bug #42749. I.e. set binlog_format=STATEMENT. Try get backtrace also: attach gdb to hanging server, then run commands `bt` and `bt full`.

It sounds similar to bug 42749 as you suggest.
However when I try to specify "binlog_format=statement" then attempting to restart the MySQL server results in an error message:-

Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

Our application relies on the READ-COMMITTED isolation level.

Correction:-

With the binlog_format=statement set at the same time as the READ-COMMITTED isolation level in my.cnf, I get the error:-

Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

when attempting to restore my table via the "mysql" commandline, rather than when I restart the MySQL server as I previously stated in error.

As requested, here are the backtraces:-

(gdb) bt
#0  0x00007f222ba69db2 in select () from /lib/libc.so.6
#1  0x00000000005fc7a8 in handle_connections_sockets (arg=0x11) at mysqld.cc:4971
#2  0x00000000005fbb7d in main (argc=17, argv=0x7fff34ce4a30) at mysqld.cc:4470
(gdb) bt full
#0  0x00007f222ba69db2 in select () from /lib/libc.so.6
No symbol table info available.
#1  0x00000000005fc7a8 in handle_connections_sockets (arg=0x11) at mysqld.cc:4971
	sock = 15
	max_used_connection = 17
	readFDs = {fds_bits = {98304, 0 <repeats 15 times>}}
	clientFDs = {fds_bits = {98304, 0 <repeats 15 times>}}
	cAddr = {sin_family = 2, sin_port = 59813, sin_addr = {s_addr = 675025162}, sin_zero = "\000\000\000\000\000\000\000"}
	ip_flags = 113771472
#2  0x00000000005fbb7d in main (argc=17, argv=0x7fff34ce4a30) at mysqld.cc:4470
No locals.
(gdb)

Thank you for the feedback.

Could you please try to load this dump in test database using statement binary log format and less strict transaction isolation level, so we can be sure it is same as bug #42749?

With a STATEMENT-based binlog and the transaction isolation mode relaxed, the import runs fine. So it does appear to be the same as bug #42749.

Thank you for the feedback.

Marked as duplicate of bug #42749.

Regarding to "Need Triage" status: bug #42749 was triaged, but I don't agree with values, so asked to re-triage again with aim to have it fixed sooner.