| Bug #19213 | Table Locked after wrong Query | ||
|---|---|---|---|
| Submitted: | 20 Apr 2006 9:16 | Modified: | 20 May 2006 16:30 |
| Reporter: | Jan Kapellen | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.16 | OS: | Linux (linux x86_64) |
| Assigned to: | CPU Architecture: | Any | |
[20 Apr 2006 9:22]
Jan Kapellen
I have to add that our mysql-server has a average load of about 1000 queries per second for the last month
[20 Apr 2006 16:30]
Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the behaviour you described with single session and 4.1.19-BK:
openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.19
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `EVENT` (
-> `EVENT_ID` mediumint(9) unsigned NOT NULL auto_increment,
-> `EVENT_TYPE_ID` mediumint(9) unsigned NOT NULL default '0',
-> `QUALIFIER_CONSTRAINT_SET_ID` mediumint(9) unsigned default NULL,
-> `CONSTRAINT_SET_ID` mediumint(9) unsigned default NULL,
-> `RESPONSIBLE_PERSON_ID` bigint(20) unsigned default NULL,
-> `EVENT_DATE` datetime default NULL,
-> `DETAILS` varchar(255) default NULL,
-> `NEXT_EVENT_ID` mediumint(9) default NULL,
-> `STEP_ID` mediumint(9) default NULL,
-> `PARENT_ID` mediumint(9) default NULL,
-> `STATUS` char(1) default NULL,
-> `PRIORITY` smallint(6) default NULL,
-> `STARTED_AT` datetime default NULL,
-> `FINISHED_AT` datetime default NULL,
-> `RECYCLE_COUNT` tinyint(4) default NULL,
-> `OFFICIAL_ISSUE` tinyint(1) unsigned NOT NULL default '0',
-> `CPC_EVENT_ID` mediumint(9) unsigned default NULL,
-> `CMS_SOURCE_TYPE` varchar(6) default NULL,
-> `THREADS_NUMBER` tinyint(3) unsigned NOT NULL default '3',
-> `ISCLUSTERED` tinyint(3) unsigned NOT NULL default '0',
-> `CPC_STATUS` enum('Y','N') default 'N',
-> PRIMARY KEY (`EVENT_ID`),
-> UNIQUE KEY `IDX_PK_EVENT` (`EVENT_ID`),
-> UNIQUE KEY `IDX_QK_EVENT` (`EVENT_DATE`,`EVENT_TYPE_ID`,`PARENT_ID`),
-> KEY `IDX_EVENT_EVENT_TYPE` (`EVENT_TYPE_ID`),
-> KEY `IDX_EVENT_RESPONSIBLE_PERSON` (`RESPONSIBLE_PERSON_ID`),
-> KEY `IDX_EVENT_DATE_PRIORITY` (`EVENT_DATE`,`PRIORITY`),
-> KEY `IDX_EVENT_STATUS_PRIORITY` (`STATUS`,`PRIORITY`),
-> KEY `IDX_EVENT_PARENT_STATUS` (`PARENT_ID`,`STATUS`),
-> KEY `IDX_CPC_EVENT_ID` (`CPC_EVENT_ID`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO EVENT (EVENT_TYPE_ID,EVENT_DATE,STATUS,PRIORITY,RESPONSIBLE_PERSON_ID, STEP_ID,ISCLUSTERED,THREADS_NUMBER) VALUES (269151,NULL,'A',5,NULL,7, 0,'');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'THREADS_NUMBER' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
No table lock. So, please, check with a newer version, 4.1.18, and, in case of similar lock, please, send the SHOW PROCESSLIST results.
[20 May 2006 23:00]
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".
Description: We recognized, that our server parses a query as good, which was wrong. After the Query was substituted to the mysqld we was not able to remove the lock from the table and mysql was stacked. The query was run from our PHP-based ASP. We are running the mysqld (in safe mode) on a quad-opteron system with fedora core 4 (the kernel is recompiled: Linux 2.6.14.5 #6 SMP Mon Jan 2 14:29:46 CET 2006 x86_64 x86_64 x86_64 GNU/Linux) and the matching mysql-binaries provided by mysql. Here is the Query: INSERT INTO EVENT (EVENT_TYPE_ID,EVENT_DATE,STATUS,PRIORITY,RESPONSIBLE_PERSON_ID,STEP_ID,ISCLUSTERED,THREADS_NUMBER) VALUES (269151,NULL,'A',5,NULL,7,0,'') Here is the Table Layout: CREATE TABLE `EVENT` ( `EVENT_ID` mediumint(9) unsigned NOT NULL auto_increment, `EVENT_TYPE_ID` mediumint(9) unsigned NOT NULL default '0', `QUALIFIER_CONSTRAINT_SET_ID` mediumint(9) unsigned default NULL, `CONSTRAINT_SET_ID` mediumint(9) unsigned default NULL, `RESPONSIBLE_PERSON_ID` bigint(20) unsigned default NULL, `EVENT_DATE` datetime default NULL, `DETAILS` varchar(255) default NULL, `NEXT_EVENT_ID` mediumint(9) default NULL, `STEP_ID` mediumint(9) default NULL, `PARENT_ID` mediumint(9) default NULL, `STATUS` char(1) default NULL, `PRIORITY` smallint(6) default NULL, `STARTED_AT` datetime default NULL, `FINISHED_AT` datetime default NULL, `RECYCLE_COUNT` tinyint(4) default NULL, `OFFICIAL_ISSUE` tinyint(1) unsigned NOT NULL default '0', `CPC_EVENT_ID` mediumint(9) unsigned default NULL, `CMS_SOURCE_TYPE` varchar(6) default NULL, `THREADS_NUMBER` tinyint(3) unsigned NOT NULL default '3', `ISCLUSTERED` tinyint(3) unsigned NOT NULL default '0', `CPC_STATUS` enum('Y','N') default 'N', PRIMARY KEY (`EVENT_ID`), UNIQUE KEY `IDX_PK_EVENT` (`EVENT_ID`), UNIQUE KEY `IDX_QK_EVENT` (`EVENT_DATE`,`EVENT_TYPE_ID`,`PARENT_ID`), KEY `IDX_EVENT_EVENT_TYPE` (`EVENT_TYPE_ID`), KEY `IDX_EVENT_RESPONSIBLE_PERSON` (`RESPONSIBLE_PERSON_ID`), KEY `IDX_EVENT_DATE_PRIORITY` (`EVENT_DATE`,`PRIORITY`), KEY `IDX_EVENT_STATUS_PRIORITY` (`STATUS`,`PRIORITY`), KEY `IDX_EVENT_PARENT_STATUS` (`PARENT_ID`,`STATUS`), KEY `IDX_CPC_EVENT_ID` (`CPC_EVENT_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; How to repeat: Although we do not want this to happen again my suggestion is to write a small script which sends the above mentioned query to a mysql-server with version 4.16. Suggested fix: As I have already mentioned we was not able to unlock the table again, so the only solution by now is to just restart mysqld.