| Bug #444 | MySQL-4.1 fails to run on a production server : 'opening table' table status | ||
|---|---|---|---|
| Submitted: | 17 May 2003 13:04 | Modified: | 29 Aug 2003 6:32 |
| Reporter: | jocelyn fournier (Silver Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S1 (Critical) |
| Version: | 4.1.1 | OS: | Linux (Linux) |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
[19 May 2003 16:53]
jocelyn fournier
Hi, It seems the problem only occurs when the server is started under high load. I will try to investigate more thoroughly the problem. I change the status of this bug to "feedback" until I get more info about this problem...
[19 May 2003 17:30]
jocelyn fournier
Finally it's seems it's not linked with the load of the server.
After inverstigation, it's seems to always happen when this kind of queries are used :
INSERT INTO Hardwarefr.online (pseudo,date,cat,forum) VALUES ('Mseirco',1053388697,'1',1) ON DUPLICATE KEY UPDATE date=1053388697,cat='1',forum=1;
online is a HEAP table with the following definition :
CREATE TABLE `online` (
`pseudo` char(35) character set latin1 NOT NULL default '',
`date` int(10) unsigned NOT NULL default '0',
`cat` tinyint(4) unsigned NOT NULL default '0',
`forum` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`pseudo`),
KEY `forum` (`forum`),
KEY `date` (`date`)
) TYPE=HEAP MAX_ROWS=10000;
Here is a short part of the log :
211 Init DB forum_hardwarefr
211 Query REPLACE INTO Hardwarefr.guest (pseudo,date,forum) VALUES ('62.147.38.33',1053388696,1)
211 Query SELECT pseudo,email,modomp FROM Hardwarefr.inscrit WHERE status=3
211 Query SELECT haut,bas FROM forumpubhardwarefr WHERE cat=0
211 Query SELECT COUNT(*) FROM Hardwarefr.forumadmin
211 Query SELECT COUNT(*) FROM Hardwarefr.online
211 Query SELECT COUNT(*) FROM Hardwarefr.guest
211 Query SELECT connec FROM Hardwarefr.forumadmin
211 Query SELECT numero, nom, description, moderateur, password, auteur, visible FROM forumcathardwarefr ORDER BY ordre ASC
211 Query SELECT MAX(maxnumrep),MAX(date) FROM forumconthardwarefr1
030520 1:58:17 212 Connect mysql@localhost on
212 Query SELECT PostMax1,PostMax2,freemodo,positionPub,LOWER(forum),up FROM Hardwarefr.forumadmin WHERE defaut=1
212 Init DB forum_hardwarefr
212 Query SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='mseirco' AND password='FBuOdN4CcbFFM'
212 Query SELECT password,nom,moderateur,annonce,visible FROM forumcathardwarefr WHERE numero='1'
212 Query SELECT haut,bas FROM forumpubhardwarefr WHERE cat='1'
212 Query SELECT cookie FROM Hardwarefr.cookie WHERE id='74332' AND IDForum=1 AND cat='1'
212 Query INSERT INTO Hardwarefr.online (pseudo,date,cat,forum) VALUES ('Mseirco',1053388697,'1',1) ON DUPLICATE KEY UPDATE date=1053388697,cat='1',forum=1
17 Query show processlist
213 Connect mysql@localhost on
213 Query SELECT admin,nb_inscrit,PostMax2,da,LOWER(forum),positionPub,up,sondage,vsadmin,vadmin FROM Hardwarefr.forumadmin WHERE defaut=1
213 Init DB forum_hardwarefr
213 Query SELECT status,zlib,online,id FROM Hardwarefr.inscrit WHERE pseudo='babylonia' AND password='0100'
030520 1:58:18 17 Query show processlist
214 Connect mysql@localhost on
214 Query SELECT PostMax1,PostMax2,freemodo,positionPub,LOWER(forum),up FROM Hardwarefr.forumadmin WHERE defaut=1
<cut>
Hardwarefr.cookie is an innodb table.
Here is the show processlist when the lock begin to occur :
mysql> show processlist;
+-----+---------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+---------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+
| 17 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 53 | DELAYED | :2873 | forum_hardwarefr | Delayed_insert | 96 | Waiting for INSERT | |
| 54 | DELAYED | | forum_hardwarefr | Delayed_insert | 96 | Waiting for INSERT | |
| 97 | DELAYED | :31661 | forum_hardwarefr | Delayed_insert | 81 | Waiting for INSERT | |
| 98 | DELAYED | :7936 | forum_hardwarefr | Delayed_insert | 81 | Waiting for INSERT | |
| 101 | DELAYED | :13878 | forum_hardwarefr | Delayed_insert | 29 | Waiting for INSERT | |
| 102 | DELAYED | | forum_hardwarefr | Delayed_insert | 29 | Waiting for INSERT | |
| 156 | DELAYED | :26990 | forum_hardwarefr | Delayed_insert | 45 | Waiting for INSERT | |
| 157 | DELAYED | :22876 | forum_hardwarefr | Delayed_insert | 45 | Waiting for INSERT | |
| 211 | mysql | localhost | forum_hardwarefr | Query | 1 | Opening tables | SELECT MAX(maxnumrep),MAX(date) FROM forumconthardwarefr1 |
| 212 | mysql | localhost | forum_hardwarefr | Query | 0 | Opening tables | INSERT INTO Hardwarefr.online (pseudo,date,cat,forum) VALUES ('Mseirco',1053388697,'1',1) ON DUPLICA |
+-----+---------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)
Hope this could help to track down this nasty bug.
Tell me if you need more infos ! :)
(I will try to see if I can reproduce this bug in debug mode ASAP, so that I could send you a trace file)
Jocelyn
[20 May 2003 17:13]
jocelyn fournier
Ok, I've reproduce the bug and created a trace file with those parameters : mysqld_safe --debug=where:O,/home/mysql/mysqldebug1.trace --skip-safemalloc & You can find it in ftp://support.mysql.com/pub/mysql/secret/debug.tar.gz Note that 'opening table' problem begin to occur after : my_realpath: info: executing realpath open_table: info: inserting table 0x8ad5090 into the cache After that point, no more query are executed. (And not sure any more after seeing the trace that the 'ON DUPLICATE' query is the cause of the bug here).
[10 Jun 2003 15:58]
jocelyn fournier
Hi, I tried to played again to reproduce this bug. Using mysqld_safe --debug=d,info,error,query,general,where:t:i:o,/home/mysql/mysqldebug2.trace --skip-safemalloc & mysql crashes from time to time (which prevent me from reproducing the bug) with the following stack trace : 0x813c88b handle_segfault + 453 0x40034f54 _end + 934803380 0x833101a my_strcasecmp_utf8 + 34 0x816cea4 find_field_in_table(THD*, st_table*, char const*, unsigned, bool, bool) + 126 0x816d245 find_field_in_tables(THD*, Item_ident*, st_table_list*, st_table_list**, bool) + 725 0x80d54d1 Item_field::fix_fields(THD*, st_table_list*, Item**) + 81 0x816d776 setup_fields(THD*, Item**, st_table_list*, List<Item>&, bool, List<Item>*, bool) + 178 0x8172ae8 JOIN::prepare(Item***, st_table_list*, unsigned, Item*, unsigned, st_order*, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*, bool) + 368 0x8175e16 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 362 0x8172894 handle_select(THD*, st_lex*, select_result*) + 224 0x814d3be mysql_execute_command(THD*) + 1532 0x8152b05 mysql_parse(THD*, char*, unsigned) + 247 0x814be8a dispatch_command(enum_server_command, THD*, char*, unsigned) + 1908 0x814b70b do_command(THD*) + 507 0x814abec handle_one_connection + 580 0x400320ba _end + 934791450 0x40180d6a _end + 936162762 Crashing query is : thd->query at 0x8e04260 = SELECT threadhardwarefr1.id,icone,contenu,date,numreponse,signature,INET_NTOA(threadhardwarefr1.ip),INET_NTOA(realip),edit,pedit,online,pseudo,citation,image_forum,validation,icq,homepage,signature_forum,configuration,nbrpost_forum,certif FROM threadhardwarefr1 LEFT JOIN Hardwarefr.inscrit USING (id) WHERE numeropost='573512' ORDER BY numreponse ASC LIMIT 0,40 thd->thread_id=48 I also noticed that the "time" column is not always properly initialized : Indeed, sometimes show processlist displays : | 67 | unauthenticated user | localhost | NULL | Connect | -64279786 | login | NULL | Regards, Jocelyn
[11 Jun 2003 2:47]
Alexey Botchkov
Unfortunately i wasn't able to reproduce the bug on my computer. You said you can give me an access to the computer where i can debug this. I'd really appreciate this.
[17 Jun 2003 2:57]
Alexey Botchkov
pending a 4.0->4.1 merge by Monty
[21 Aug 2003 3:58]
jocelyn fournier
Hi, What is the status of this bug ? (Monty seems to have done some merges in the bk tree) Thanks, Jocelyn
[29 Aug 2003 6:32]
Alexey Botchkov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: Hi, I've just tried to launch MySQL-4.1.1 on my production server, with exactely the same configuration that the one used for MySQL-4.0.13. Things seem to works fine in the beginning, but after 1 mn of running, all the threads stay in 'opening tables' status, and a too many connections error occurs... (mutex problem ?). This has never happenned with 4.0.13. Here is the show processlist output : "mysql> show processlist; +-----+-------+-----------+------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-------+-----------+------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | 380 | mysql | localhost | forum_hardwarefr | Query | 188 | Opening tables | SELECT MAX(maxnumrep),MAX(date) FROM forumconthardwarefr4 | | 381 | mysql | localhost | forum_hardwarefr | Query | 188 | closing tables | UPDATE LOW_PRIORITY forumconthardwarefr1 SET vue=vue+1 WHERE numeropost='569710' | | 382 | mysql | localhost | NULL | Query | 188 | Opening tables | SELECT status,smilies,zlib,topicpp,smiliesperso,id FROM Hardwarefr.inscrit WHERE pseudo='cuspides' | | 383 | mysql | localhost | forum_hardwarefr | Query | 188 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='atigrou' AND password= | | 384 | mysql | localhost | forum_hardwarefr | Query | 188 | Opening tables | SELECT image,status,js,zlib,online,topicpp,oldquote,id FROM Hardwarefr.inscrit WHERE pseudo='thecoin | | 385 | mysql | localhost | forum_hardwarefr | Query | 187 | Opening tables | SELECT image,status,js,zlib,online,topicpp,oldquote,id FROM Hardwarefr.inscrit WHERE pseudo='phenos' | | 386 | mysql | localhost | forum_hardwarefr | Query | 187 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='jusco' AND password='2 | | 387 | mysql | localhost | forum_hardwarefr | Query | 187 | Opening tables | SELECT 1 FROM moderationhardwarefr WHERE victime='' AND cat IN ('8',0) AND ban='2' LIMIT 1 | | 388 | mysql | localhost | forum_hardwarefr | Query | 187 | Opening tables | SELECT image,status,js,zlib,online,topicpp,oldquote,id FROM Hardwarefr.inscrit WHERE pseudo='e-TE' A | | 389 | mysql | localhost | forum_hardwarefr | Query | 187 | Opening tables | SELECT image,status,js,zlib,online,topicpp,oldquote,id FROM Hardwarefr.inscrit WHERE pseudo='JayK' A | | 390 | mysql | localhost | NULL | Query | 186 | Opening tables | SELECT status,smilies,zlib,topicpp,smiliesperso,id FROM Hardwarefr.inscrit WHERE pseudo='janfy' | | 391 | mysql | localhost | forum_hardwarefr | Query | 186 | Opening tables | REPLACE INTO Hardwarefr.guest (pseudo,date,forum) VALUES ('217.128.21.150',1053200884,1) | | 392 | mysql | localhost | NULL | Query | 186 | Opening tables | SELECT status,smilies,zlib,topicpp,smiliesperso,id FROM Hardwarefr.inscrit WHERE pseudo='nicola04' | | 393 | mysql | localhost | forum_hardwarefr | Query | 186 | Opening tables | SELECT image,status,js,zlib,online,topicpp,oldquote,id FROM Hardwarefr.inscrit WHERE pseudo='Vague N | | 394 | mysql | localhost | forum_hardwarefr | Query | 185 | Opening tables | INSERT INTO Hardwarefr.guest (pseudo,date,forum) VALUES ('80.13.41.103',1053200885,1) ON DUPLICATE K | | 395 | mysql | localhost | forum_hardwarefr | Query | 185 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='patparis' AND password | | 396 | mysql | localhost | forum_hardwarefr | Query | 185 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='hugobios' AND password | | 397 | mysql | localhost | forum_hardwarefr | Query | 185 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='raistlin majere' AND p | | 398 | mysql | localhost | forum_hardwarefr | Query | 185 | Opening tables | SELECT 1 FROM Hardwarefr.inscrit WHERE pseudo='miton31' AND password='vNi8zg7bV7TDk' | | 399 | mysql | localhost | forum_hardwarefr | Query | 184 | Opening tables | SELECT image,status,js,zlib,online,topicpp,oldquote,id FROM Hardwarefr.inscrit WHERE pseudo='Negro' | | 400 | mysql | localhost | forum_hardwarefr | Query | 184 | Opening tables | SELECT id FROM Hardwarefr.inscrit WHERE pseudo='cronos' | | 401 | mysql | localhost | NULL | Query | 183 | Opening tables | SELECT status,smilies,zlib,topicpp,smiliesperso,id FROM Hardwarefr.inscrit WHERE pseudo='DaV-X' | | 402 | mysql | localhost | forum_hardwarefr | Query | 183 | Opening tables | SELECT password,nom,moderateur,annonce,visible FROM forumcathardwarefr WHERE numero='11' | | 403 | mysql | localhost | forum_hardwarefr | Query | 183 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='reset.smith' AND passw | | 404 | mysql | localhost | forum_hardwarefr | Query | 183 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='atigrou' AND password= | | 405 | mysql | localhost | forum_hardwarefr | Query | 182 | Opening tables | SELECT image,status,js,zlib,online,topicpp,oldquote,id FROM Hardwarefr.inscrit WHERE pseudo='Ricco' | | 406 | mysql | localhost | forum_hardwarefr | Query | 182 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='koulip31' AND password | | 407 | mysql | localhost | forum_hardwarefr | Query | 182 | Opening tables | SELECT image,status,js,zlib,online,topicpp,oldquote,id FROM Hardwarefr.inscrit WHERE pseudo='pti vam | | 408 | mysql | localhost | forum_hardwarefr | Query | 182 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='raph77' AND password=' | | 409 | mysql | localhost | forum_hardwarefr | Query | 182 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='menar' AND password='2 | <cut> | 699 | mysql | localhost | forum_hardwarefr | Query | 112 | Opening tables | INSERT INTO Hardwarefr.online (pseudo,date,cat,forum) VALUES ('koulip31',1053200958,0,1) ON DUPLICAT | | 700 | mysql | localhost | forum_hardwarefr | Query | 112 | Opening tables | SELECT status,js,zlib,online,topicpp,id FROM Hardwarefr.inscrit WHERE pseudo='essair' AND password=' | | 792 | root | localhost | NULL | Query | 0 | NULL | show processlist | +-----+-------+-----------+------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ 320 rows in set (0.00 sec)" How to repeat: Unfortunately, I don't know... I can provide you an access to the server and install 4.1.1 for debugging during the night.