| Bug #7060 | mysqldump hangs | ||
|---|---|---|---|
| Submitted: | 7 Dec 2004 1:59 | Modified: | 13 Jan 2005 4:50 |
| Reporter: | Micke Moggia | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.7 | OS: | Linux (Red Hat Linux) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[7 Dec 2004 1:59]
Micke Moggia
[7 Dec 2004 6:57]
MySQL Verification Team
Hi, Thank you for the report, but I can't repeat it with my test tables. Please check if you get the same result with any InnoDB table or with particular table.
[7 Dec 2004 8:36]
Heikki Tuuri
Micke, during the hang, please run SHOW PROCESSLIST; and SHOW INNODB STATUS; and post the COMPLETE output of them. Regards, Heikki
[7 Dec 2004 20:17]
Micke Moggia
I prefer not to cause another hang on my production-server... My theory is that when I upgraded to 4.1 from 4.0 all the tables containing swedish letters was first converted to ascii-safe letters, all tables but one. And maybe that table still containing swedish letters was causing the trouble. But after a servercrash and a restart I tried to run the mysqldump again, but same thing happened. In SHOW PROCESSLIST I remember I saw that the process was stucked with "SHOW CREATE TABLE" and the status was "Waiting for table". When trying to kill the process it will alter state to "Killed" but will not go away. And the funny thing is that when I try to run "SHOW CREATE TABLE" in the MySQL-client it works with no problem. I can, if you will, try to run mysqldump again if you tell me how I can kill the process, if it goes wrong againg, without having to crash my server.
[7 Dec 2004 20:20]
Micke Moggia
With "tables containing swedish letters", I of course ment tablenames and columnnames...
[7 Dec 2004 21:54]
Heikki Tuuri
Micke, did you have Swedish letters, like å,ä,ö, in the table name of that problematic table? It is a known bug that mysqld-4.1 can go into an endless loop if a table was created with 4.0 and its name contains 'accent' characters. In an upgrade, you should first rename such tables. Regards, Heikki
[8 Dec 2004 0:49]
Heikki Tuuri
Hi! I am assigning this bug to Bar, because he knows about the bug fix status of UTF-8 problems. MySQL-4.1 stores table names in UTF-8. Old tables with ASCII characters >= 128 in their name cause problems then. Regards, Heikki
[13 Dec 2004 4:50]
Micke Moggia
New information: The problem first happened when a non-used table that contained high ascii-letters was hanging along to 4.1. I then dropped that table but the problem didn't go away. I have now found out that this is probarbly because when I renamed indexed column-names containing high ascii-letters their index did not change name! So the index-columns still contains high ascii-letters, they work, but I can't drop them cause MySQL complains that they don't exist. So here I am sitting with 25 tables that most of them have several indexes that have high ascii- letters in their columnnames. I can create new indexes but I can't delete the old ones. And when they still exist I can't do a database structure backup... Please advice!
[14 Feb 2005 22:54]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[27 Apr 2006 6:43]
Colin Leroy
Hi, I have the same problem. Our tape backup script starts with a mysqldump in order to have a clean dump, and runs: mysqldump --all-databases -u root --password='blah' > $BACKUPDIR/dump.sql restarting mysqld fixes it, but it usually blocks again the day after. Here is the output of show processes and show innodb status: mysql> SHOW PROCESSLIST; +-----+-------------+-------------------------+------------+---------+-------+--------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-------------+-------------------------+------------+---------+-------+--------+------------------------------------------------------------------------------------------------------+ | 17 | root | localhost | egroupware | Sleep | 61095 | | NULL | | 18 | root | localhost | egroupware | Sleep | 61627 | | NULL | | 19 | root | localhost | egroupware | Sleep | 220 | | NULL | | 20 | root | localhost | egroupware | Sleep | 220 | | NULL | | 21 | root | localhost | egroupware | Sleep | 80627 | | NULL | | 22 | root | localhost | egroupware | Sleep | 61161 | | NULL | | 23 | root | localhost | egroupware | Sleep | 77887 | | NULL | | 24 | root | localhost | egroupware | Sleep | 53894 | | NULL | | 26 | root | localhost | egroupware | Sleep | 80953 | | NULL | | 27 | root | localhost | egroupware | Sleep | 61245 | | NULL | | 31 | root | localhost | egroupware | Sleep | 61214 | | NULL | | 32 | root | localhost | egroupware | Sleep | 80645 | | NULL | | 37 | root | localhost | egroupware | Sleep | 219 | | NULL | | 240 | root | localhost | egroupware | Sleep | 61699 | | NULL | | 253 | root | localhost | egroupware | Sleep | 61219 | | NULL | | 254 | root | localhost | egroupware | Sleep | 61219 | | NULL | | 255 | root | localhost | egroupware | Sleep | 61047 | | NULL | | 354 | salome_user | caroline.toulouse:35498 | salome | Sleep | 45220 | | NULL | | 390 | root | localhost | salome | Query | 38298 | Locked | LOCK TABLES `ACTION_ATTACHEMENT` READ /*!32311 LOCAL */,`ACTION_PARAM_TEST` READ /*!32311 LOCAL */,` | | 394 | root | localhost | egroupware | Sleep | 219 | | NULL | | 395 | root | localhost | mysql | Query | 0 | NULL | SHOW PROCESSLIST | +-----+-------------+-------------------------+------------+---------+-------+--------+------------------------------------------------------------------------------------------------------+ 21 rows in set (0.00 sec) ===================================== 060427 8:38:56 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 27 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 17, signal count 17 Mutex spin waits 17, rounds 340, OS waits 0 RW-shared spins 32, OS waits 15; RW-excl spins 3, OS waits 2 ------------ TRANSACTIONS ------------ Trx id counter 0 12661 Purge done for trx's n:o < 0 12659 undo n:o < 0 0 History list length 32 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 4301, OS thread id 2981575600 MySQL thread id 395, query id 35108 localhost root SHOW INNODB STATUS ---TRANSACTION 0 0, not started, process no 4301, OS thread id 2984344496 mysql tables in use 37, locked 37 MySQL thread id 390, query id 34763 localhost root Table lock LOCK TABLES `ACTION_ATTACHEMENT` READ /*!32311 LOCAL */,`ACTION_PARAM_TEST` READ /*!32311 LOCAL */,`ACTION_TEST` READ /*!32311 LOCAL */,`ATTACHEMENT` READ /*!32311 LOCAL */,`CAMPAGNE_ATTACHEMENT` READ /*!32311 LOCAL */,`CAMPAGNE_CAS` READ /*!32311 LOCAL */,`CAMPAGNE_TEST` READ /*!32311 LOCAL */,`CAS ---TRANSACTION 0 12660, ACTIVE 52830 sec, process no 4301, OS thread id 2982214576 mysql tables in use 30, locked 30 30 lock struct(s), heap size 2496 MySQL thread id 354, query id 33247 caroline.toulouse 192.168.0.43 salome_user Trx read view will not see trx with id >= 0 12661, sees < 0 12661 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 218 OS file reads, 149 OS file writes, 94 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 0, seg size 2, is empty Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 398, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 6804129 Log flushed up to 0 6804129 Last checkpoint at 0 6804129 0 pending log writes, 0 pending chkp writes 51 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 18484100; in additional pool allocated 1048576 Buffer pool size 512 Free buffers 249 Database pages 261 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 231, created 30, written 122 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 4301, id 3001535408, state: waiting for server activity Number of rows inserted 70, updated 9, deleted 3, read 9683 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ Thanks, Colin
