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:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5,0,51b/5.1/6.0 OS:Microsoft Windows (Linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: crash
Triage: D2 (Serious) / R2 (Low) / E2 (Low)

[10 Jun 2008 14:29] Dmitrii Shevchenko
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;
[10 Jun 2008 14:50] Miguel Solorzano
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] Miguel Solorzano
[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] Miguel Solorzano
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.