| Bug #18513 | mysqlcheck hangs on a InnoDB server | ||
|---|---|---|---|
| Submitted: | 26 Mar 2006 7:32 | Modified: | 6 Apr 2006 6:49 |
| Reporter: | Jim Michaels | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
| Version: | 5.0.18 | OS: | Windows (Windows XP Pro SP2 MP) |
| Assigned to: | CPU Architecture: | Any | |
[26 Mar 2006 7:32]
Jim Michaels
[26 Mar 2006 16:16]
Heikki Tuuri
Jim, how big is the table? Maybe it just takes many hours to run the CHECK? Regards, Heikki
[26 Mar 2006 16:17]
Heikki Tuuri
Jim, what does SHOW INNODB STATUS\G say? Regards, Heikki
[27 Mar 2006 8:24]
Jim Michaels
innodb datafiles are 44MB. tables are very small or empty. both processes have been running for 2 days now. result of show innodb status\g (why can't I do this from the query browser?) Status ===================================== 060327 0:20:02 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 0 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 2, signal count 2 Mutex spin waits 1, rounds 3, OS waits 0 RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 0 ------------ TRANSACTIONS ------------ Trx id counter 0 99584 Purge done for trx's n:o < 0 98689 undo n:o < 0 0 History list length 16 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 1740 MySQL thread id 9, query id 1080 localhost 127.0.0.1 root show innodb status ---TRANSACTION 0 0, not started, OS thread id 1680 MySQL thread id 3, query id 1079 localhost 127.0.0.1 root -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (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 141 OS file reads, 3 OS file writes, 3 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 74821, used cells 0, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 38360472 Log flushed up to 0 38360472 Last checkpoint at 0 38360472 0 pending log writes, 0 pending chkp writes 8 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 25289904; in additional pool allocated 273664 Buffer pool size 1152 Free buffers 1021 Database pages 131 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 131, created 0, written 0 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 1 read views open inside InnoDB Main thread id 3848, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
[27 Mar 2006 8:29]
Jim Michaels
sql dump is 7MB. 99% of it is a GEO-IP table with country names.
[27 Mar 2006 8:34]
Jim Michaels
the manual states that mysqlcheck only works for MyISAM tables. this is an InnoDB database. it shouldn't be doing anything at all, should it? It appears from the manual that this is is not a useful program for an InnoDB database. (?) Is it? If it it can be used for optimizing or something, someone needs to fix the manual (in addition to the program).
[27 Mar 2006 12:17]
Heikki Tuuri
Jim, ok, I understand that when you call in the shell mysqlcheck, it runs forever if the table happens to be InnoDB. From the status I see that it is not doing anything inside InnoDB. Thus, the process is hanging or looping somewhere else. I am putting this bug to category 'Command-line clients'. Regards, Heikki
[27 Mar 2006 16:45]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.19, and inform about the results.
[28 Mar 2006 6:21]
Jim Michaels
see bug id 18556 about 5.0.19. won't connect, apps hang. http://bugs.mysql.com/?id=18556&edit=2
[28 Mar 2006 10:38]
Valeriy Kravchuk
Bug #18556 is a different one. Please, check mysqlcheck behaviour with 5.0.19 when you'll be able to install it successfully and inform about the results in this bug report.
[28 Mar 2006 23:18]
Jim Michaels
5.0.19 won't install correctly. all mysqlcheck.exe and mysql.exe does is hang, and I can't kill them. I have to wither turn off the box or repeatly shutdown the machine. usually turn off the box. the mysql service won't connect. I don't know how to fix it. I am using 5.0.19 GA community edition. anything better I can use?
[29 Mar 2006 2:43]
Jim Michaels
Got 5.0.19 to install. had to delete old directory completely before install. C:\prj>mysqlcheck --user=myusername --password=mypassword -A brendas.config OK brendas.counter OK brendas.block_ips OK brendas.imgs OK brendas.links OK brendas.requests OK geoff-blog.messages OK geoff-blog.posts OK geoff-blog.replies OK jmichae3.available_languages OK jmichae3.cal0_evt OK jmichae3.cal0_evt_calendars OK jmichae3.calendar1 OK jmichae3.calendar1_calendars OK jmichae3.categories OK jmichae3.categoryroots OK jmichae3.geoip OK mysql.columns_priv OK mysql.db OK mysql.func OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.proc OK mysql.procs_priv OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK C:\prj> however, there are other problems. server disconnects itself when I try to execute a 7MB restore SQL script full of small inserts, and then disallows access to clients.
[5 Apr 2006 11:33]
Valeriy Kravchuk
So, does mysqlcheck hang on InnoDB tables on 5.0.19 for you or not? Please, clarify. Other problems you mentioned are already reported.
[6 Apr 2006 1:10]
Jim Michaels
doesn't hang anymore. all is fine.
[6 Apr 2006 6:49]
Valeriy Kravchuk
Looks like the problem is not repeatable in 5.0.19 when properly installed.
