| Bug #44548 | Restoring a table dumped with 'mysqldump' is causing the server to hang | ||
|---|---|---|---|
| Submitted: | 29 Apr 19:47 | Modified: | 30 Apr 18:26 |
| Reporter: | Andrew Torry | ||
| Status: | Duplicate | ||
| Category: | 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: | Target Version: | ||
| Tags: | mysqldump, restore, hang, server | ||
[29 Apr 20: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 22: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`.
[30 Apr 1: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 2: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 2: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 9: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 16: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 18: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.
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.