Hello monty,
After I got mysql/system crash I often see the following after I
bring mysql up again:
ruby:~ # w
9:37pm up 7:35, 1 user, load average: 0.01, 0.04, 0.06
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
root pts/0 mail.local 9:37pm 0.00s 0.07s 0.02s w
ruby:~ # mysqladmin processlist | grep -v Sleep
+------+-------+-------------+---------+---------+-------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
| |
+------+-------+-------------+---------+---------+-------+-------------------+------------------------------------------------------------------------------------------------------+
| 1 | titan | mars.local | counter | Query | 22606 | Waiting for table | INSERT
|INTO st45.g06hits
|(counter_id,visitor_id,page_id,referer_page_id,browser_id,display_id,option |
| 16 | titan | mail.local | counter | Query | 22857 | Waiting for table | select
|sum(lt.hits) as val, st31.g07pages.page as page, st31.g07pages.port as port,
|st31.g07pages.qu |
| 17 | titan | mail.local | counter | Query | 21685 | Waiting for table | SELECT
|visitors
FROM layers_st45.g08layers_stat_hits AS lsh,
layers |
| 21 | titan | mail.local | counter | Query | 21834 | Waiting for table | SELECT
|lsh.begin, sh.visitors AS visitors
FROM layers_st66.g03layers_stat_hits AS ls |
| 57 | root | localhost | counter | Query | 22758 | Waiting for table | SELECT
|user_agent_string, user_agent_id FROM st45.g07user_agents WHERE browser_id=20
| |
| 59 | titan | mail.local | counter | Query | 21883 | Waiting for table | select
|lt.id,sum(lt.visitors) as val from layers_st30.g07layers_stat_languages as
|l_lt,layers_st30.g |
| 80 | titan | ghost.local | | Query | 22627 | Waiting for table | SELECT
|HOUR(lsh.begin), sh.visitors AS visitors
FROM layers_st30.g07layers_stat_hits |
| 122 | titan | mail.local | counter | Query | 21761 | Waiting for table | SELECT
|visitors
FROM layers_st45.g01layers_stat_hits AS lsh,
layers |
| 282 | titan | mail.local | counter | Query | 17071 | Waiting for table | SELECT
st30.g00hits.visitor_id, st30.g00hits.id AS
id,st30.g00hits.ts,hex(st30.g00hits.ip) as ip |
| 1490 | root | localhost | | Query | 0 | | show
|processlist
| |
+------+-------+-------------+---------+---------+-------+-------------------+------------------------------------------------------------------------------------------------------+
--
ruby:/spylog/layers # tail -10000 /spylog/db/ruby.err | grep 45/g06hits
010122 15:20:46 Warning: Checking table: './st45/g06hits'
010122 15:20:46 Warning: Recovering table: './st45/g06hits'
mysqladmin debug reports no table locks active
I saw this many times on different machines and this sequince never
resolves itself ( the locks never disapears). Always the only way to
fix this is to kill one of the queries. I killed query #16 and
situation momentaly fixed:
ruby:/spylog/layers # mysqladmin processlist | grep -v Sleep
+------+-------+------------+---------+---------+-------+----------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
| |
+------+-------+------------+---------+---------+-------+----------------+------------------------------------------------------------------------------------------------------+
| 1 | titan | mars.local | counter | Query | 0 | update | INSERT INTO
|st45.g06hits
|(counter_id,visitor_id,page_id,referer_page_id,browser_id,display_id,option |
| 1405 | titan | mail.local | counter | Query | 0 | Checking table | select
|lt.id,sum(lt.hits) as val from layers_st66.g09layers_stat_enterpages as
|l_lt,layers_st66.g09s |
| 1512 | root | localhost | | Query | 0 | statistics | select ip
|from st66.g08domip where rt=0
| |
| 1515 | root | localhost | | Query | 0 | | show
|processlist
| |
+------+-------+------------+---------+---------+-------+----------------+------------------------------------------------------------------------------------------------------+
The log also shows the table was repaired long ago:
010122 15:16:35 Warning: Checking table: './st31/g07pages'
010122 15:16:36 Warning: Recovering table: './st31/g07pages'
Hope this will somehow point the problem :)
P.S You anyway write two messages to a log file then a table is
automatically repaired. I think there might be a good idea to add
third message indicating the table was fixed successfully.
Best regards,
Peter mailto:[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
