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

[29 Apr 2009 17:47] Andrew Torry
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.
[29 Apr 2009 18:23] Valeriy Kravchuk
Please, send the results of:

show create table `redried_production`\G

I want ot check if this is table is partitioned.
[29 Apr 2009 20:30] Sveta Smirnova
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`.
[29 Apr 2009 23:17] Andrew Torry
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.
[30 Apr 2009 0:39] Andrew Torry
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.
[30 Apr 2009 0:42] Andrew Torry
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)
[30 Apr 2009 7:24] Sveta Smirnova
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?
[30 Apr 2009 14:15] Andrew Torry
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.
[30 Apr 2009 16:26] Sveta Smirnova
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.