| Bug #37310 | 'on update CURRENT_TIMESTAMP' option crashes the table | ||
|---|---|---|---|
| Submitted: | 10 Jun 2008 14:29 | Modified: | 16 Sep 2008 4:56 |
| Reporter: | Dmitrii Shevchenko | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5,0,51b/5.1/6.0 | OS: | Windows (Linux) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
| Tags: | crash | ||
[10 Jun 2008 14:50]
MySQL Verification Team
Thank you for the bug report. Could you please provide your my.ini file.
I can't repeat with the how to repeat instructions:
c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3350 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.64-nt Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql 5.0 > use test
Database changed
mysql 5.0 > CREATE TABLE `gtl_assigns` (
-> `AssignID` int(10) unsigned NOT NULL auto_increment,
-> `ReviewerID` int(11) NOT NULL,
-> `RequestID` int(11) NOT NULL,
-> `Status` tinyint(2) NOT NULL default '0',
-> `LastChange` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-> PRIMARY KEY (`AssignID`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1
-> ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.08 sec)
mysql 5.0 > INSERT INTO gtl_assigns(ReviewerID, RequestID) VALUES('1', '1');
Query OK, 1 row affected (0.00 sec)
mysql 5.0 > UPDATE gtl_assigns SET Status='1' WHERE AssignID='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql 5.0 >
Thanks in advance.
[10 Jun 2008 15:19]
Dmitrii Shevchenko
I forgot to mention: I realize about crash only after I seelct something from that table. I.e. SELECT * FROM gtl_assigns; Here is my.ini file: [client] port=3306 [mysql] default-character-set=latin1 [mysqld] port=3306 max_allowed_packet = 500M basedir="C:/Program Files/MySQL/MySQL Server 5.0/" datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/" default-character-set=latin1 default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=100 query_cache_size=0 table_cache=256 tmp_table_size=9M thread_cache_size=8 myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=17M key_buffer_size=10M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=256K innodb_additional_mem_pool_size=2M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=1M innodb_buffer_pool_size=17M innodb_log_file_size=10M innodb_thread_concurrency=8
[10 Jun 2008 16:42]
MySQL Verification Team
[miguel@hegel dbs]$ 5.0/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 1
Server version: 5.0.64-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `gtl_assigns` (
-> `AssignID` int(10) unsigned NOT NULL auto_increment,
-> `ReviewerID` int(11) NOT NULL,
-> `RequestID` int(11) NOT NULL,
-> `Status` tinyint(2) NOT NULL default '0',
-> `LastChange` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-> PRIMARY KEY (`AssignID`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1
-> ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.39 sec)
mysql> INSERT INTO gtl_assigns(ReviewerID, RequestID) VALUES('1', '1');
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE gtl_assigns SET Status='1' WHERE AssignID='1';
Query OK, 1 row affected (0.37 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> check table gtl_assigns;
+------------------+-------+----------+---------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+---------------------------------+
| test.gtl_assigns | check | error | Found too long record (19) at 0 |
| test.gtl_assigns | check | error | Corrupt |
+------------------+-------+----------+---------------------------------+
2 rows in set (0.00 sec)
mysql>
[miguel@hegel dbs]$ 5.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 1
Server version: 5.1.26-rc-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `gtl_assigns` (
-> `AssignID` int(10) unsigned NOT NULL auto_increment,
-> `ReviewerID` int(11) NOT NULL,
-> `RequestID` int(11) NOT NULL,
-> `Status` tinyint(2) NOT NULL default '0',
-> `LastChange` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-> PRIMARY KEY (`AssignID`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1
-> ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO gtl_assigns(ReviewerID, RequestID) VALUES('1', '1');
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE gtl_assigns SET Status='1' WHERE AssignID='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> check table gtl_assigns;
+------------------+-------+----------+---------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+---------------------------------+
| test.gtl_assigns | check | error | Found too long record (19) at 0 |
| test.gtl_assigns | check | error | Corrupt |
+------------------+-------+----------+---------------------------------+
2 rows in set (0.79 sec)
[10 Jun 2008 16:43]
MySQL Verification Team
Thank you for the feedback.
[17 Jun 2008 12:41]
Ramil Kalimullin
http://lists.mysql.com/commits/47963
[18 Jun 2008 9:24]
Sergey Vojtovich
Ok to push.
[14 Jul 2008 17:55]
Hans-Peter Klett
I see this has been committed to 5.1, but will it be in 5.0?
[14 Jul 2008 19:04]
Ramil Kalimullin
Yes.
[26 Aug 2008 13:12]
Alexey Botchkov
ok to push
[26 Aug 2008 13:49]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/52560 2675 Ramil Kalimullin 2008-08-26 Fix for bug #37310: 'on update CURRENT_TIMESTAMP' option crashes the table Problem: data consistency check (maximum record length) for a correct MyISAM table with CHECKSUM=1 and ROW_FORMAT=DYNAMIC option may fail due to wrong inner MyISAM parameter. In result we may have the table marked as 'corrupted'. Fix: properly set MyISAM maximum record length parameter.
[26 Aug 2008 18:33]
Bugs System
Pushed into 5.0.70 (revid:ramil@mysql.com-20080826134850-1sjvdyegbgrrjlwv) (version source revid:davi.arnaut@sun.com-20080826182704-ikgad9sf3142e7x9) (pib:3)
[26 Aug 2008 19:11]
Bugs System
Pushed into 5.1.28 (revid:ramil@mysql.com-20080826134850-1sjvdyegbgrrjlwv) (version source revid:davi.arnaut@sun.com-20080826183817-r22ie0hgagpcn6su) (pib:3)
[27 Aug 2008 2:21]
Paul DuBois
Noted in 5.0.70, 5.1.28 changelogs. For a MyISAM table with CHECKSUM = 1 and ROW_FORMAT = DYNAMIC table options, a data consistency check (maximum record length) could fail and cause the table to be marked as corrupted. Setting report to NDI pending push into 6.0.x.
[8 Sep 2008 6:23]
Sveta Smirnova
Bug #39246 was marked as duplicate of this one.
[13 Sep 2008 20:57]
Bugs System
Pushed into 6.0.7-alpha (revid:ramil@mysql.com-20080826134850-1sjvdyegbgrrjlwv) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)
[16 Sep 2008 4:56]
Paul DuBois
Noted in 6.0.7 changelog.
Description: In certain circumstances, data updating in the table results in a crash. How to repeat: Create the following table: CREATE TABLE `gtl_assigns` ( `AssignID` int(10) unsigned NOT NULL auto_increment, `ReviewerID` int(11) NOT NULL, `RequestID` int(11) NOT NULL, `Status` tinyint(2) NOT NULL default '0', `LastChange` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`AssignID`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC; Then execute the following query: INSERT INTO gtl_assigns(ReviewerID, RequestID) VALUES('1', '1'); Status will be '0' and AssignID will be '1' for inserted row. Then the following update leads to table crash: UPDATE gtl_assigns SET Status='1' WHERE AssignID='1'; This crash does not issue when I don't use "on update CURRENT_TIMESTAMP" option or when another field is added after the "LastChange" field. For example: CREATE TABLE `gtl_assigns` ( `AssignID` int(10) unsigned NOT NULL auto_increment, `ReviewerID` int(11) NOT NULL, `RequestID` int(11) NOT NULL, `Status` tinyint(2) NOT NULL default '0', `LastChange` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `BugTest` tinyint(2) default NULL, PRIMARY KEY (`AssignID`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;