MySQL Bugs: #37752: Table crash for ROW

Bug #37752	Table crash for ROW_FORMAT=DYNAMIC on MyISAM
Submitted:	30 Jun 2008 21:36	Modified:	14 Jul 2008 11:22
Reporter:	Hans-Peter Klett	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.0.51b/5.1/6.0	OS:	Windows (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	myisam, ROW_FORMAT, table crash

Description:
I noticed that one of my development server tables was marked as crashed, so I grabbed the data from our live server.  After inserting the data, selecting all returned "table crashed", and a repair completely emptied all data from it.  This is 100% repeatable.  After recreating the table, we removed the ROW_FORMAT=DYNAMIC from the end of our query browser's create statements.  This is an effective workaround.

How to repeat:
CREATE TABLE  `permission_template_permissions` (
  `permission_template_id` int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 CHECKSUM=1 ROW_FORMAT=DYNAMIC;

INSERT INTO permission_template_permissions VALUES
(1, 91),
(1, 83),
(1, 94),
(1, 79),
(1, 80),
(1, 216),
(1, 82),
(1, 93),
(1, 78),
(1, 277),
(1, 81),
(1, 92),
(1, 77),
(1, 247),
(1, 5),
(1, 62),
(1, 58),
(1, 32),
(1, 214),
(1, 3),
(1, 2),
(1, 1),
(6, 1),
(2, 152),
(2, 144),
(2, 72),
(2, 26),
(2, 169),
(2, 23),
(2, 24),
(2, 61),
(2, 25),
(2, 138),
(2, 22),
(2, 21),
(2, 190),
(2, 19),
(2, 189),
(2, 18),
(2, 247),
(2, 220),
(2, 5),
(2, 62),
(2, 58),
(2, 32),
(2, 214),
(2, 3),
(2, 2),
(2, 1),
(4, 1),
(6, 134),
(3, 105),
(3, 72),
(3, 26),
(3, 169),
(3, 23),
(3, 24),
(3, 25),
(3, 138),
(3, 22),
(3, 21),
(3, 190),
(3, 19),
(3, 189),
(3, 18),
(3, 91),
(3, 83),
(3, 94),
(3, 79),
(3, 80),
(3, 216),
(3, 82),
(3, 93),
(3, 78),
(3, 277),
(3, 81),
(3, 92),
(3, 77),
(3, 247),
(3, 5),
(3, 62),
(3, 58),
(3, 32),
(3, 214),
(3, 3),
(3, 2),
(3, 1),
(4, 2),
(4, 3),
(4, 214),
(4, 58),
(4, 62),
(4, 5),
(4, 247),
(4, 79),
(4, 94),
(4, 83),
(4, 91),
(4, 18),
(4, 189),
(4, 19),
(4, 190),
(4, 21),
(4, 22),
(4, 138),
(4, 25),
(4, 24),
(4, 23),
(4, 169),
(4, 26),
(4, 144),
(4, 152),
(4, 188),
(5, 1),
(5, 2),
(5, 3),
(5, 214),
(5, 58),
(5, 62),
(5, 5),
(5, 220),
(5, 247),
(5, 80),
(5, 79),
(5, 18),
(5, 189),
(5, 19),
(5, 190),
(5, 278),
(5, 21),
(5, 22),
(5, 138),
(5, 51),
(5, 25),
(5, 61),
(5, 24),
(5, 23),
(5, 169),
(5, 26),
(5, 72),
(5, 144),
(5, 152),
(6, 122),
(6, 116),
(6, 115),
(3, 107),
(3, 144),
(3, 152),
(3, 188),
(1, 18),
(1, 189),
(1, 19),
(1, 190),
(1, 21),
(1, 22),
(1, 138),
(1, 25),
(1, 24),
(1, 23),
(1, 169),
(1, 26),
(1, 72),
(1, 105),
(1, 107),
(1, 144),
(1, 152),
(1, 188);

SELECT * FROM permission_template_permissions;

Upon further investigation, two other tables suffered the same fate today.  This is quite disconcerting.  Our client serves over 200,000 customers, and it is peak season.  We are crossing our fingers in hopes that this will not happen on our live setup.

Forgot to mention that it's 64-bit.  I would guess that's a more important variable than it being server 2008.

This is repeatable on Win32 too. I will test on Linux.

mysql 5.0 > SELECT * FROM permission_template_permissions;
ERROR 1194 (HY000): Table 'permission_template_permissions' is marked as crashed and should be repaired
mysql 5.0 > show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.64-nt-log       |
| version_comment         | Source distribution |
| version_compile_machine | ia32                |
| version_compile_os      | Win32               |
+-------------------------+---------------------+
5 rows in set (0.03 sec)

mysql 6.0 > SELECT * FROM permission_template_permissions;
ERROR 1194 (HY000): Table 'permission_template_permissions' is marked as crashed and should be repaired
mysql 6.0 > check table permission_template_permissions;
+--------------------------------------+-------+----------+---------------------------------+
| Table                                | Op    | Msg_type | Msg_text                        |
+--------------------------------------+-------+----------+---------------------------------+
| test.permission_template_permissions | check | error    | Found too long record (10) at 0 |
| test.permission_template_permissions | check | error    | Corrupt                         |
+--------------------------------------+-------+----------+---------------------------------+
2 rows in set (0.45 sec)

mysql 6.0 > repair table permission_template_permissions;
+--------------------------------------+--------+----------+------------------------------------------+
| Table                                | Op     | Msg_type | Msg_text                                 |
+--------------------------------------+--------+----------+------------------------------------------+
| test.permission_template_permissions | repair | info     | Wrong bytesec:   3-  0- 10 at 0; Skipped |
| test.permission_template_permissions | repair | warning  | Number of rows changed from 167 to 0     |
| test.permission_template_permissions | repair | status   | OK                                       |
+--------------------------------------+--------+----------+------------------------------------------+
3 rows in set (0.03 sec)

mysql 6.0 > SELECT * FROM permission_template_permissions;
Empty set (0.00 sec)

mysql 6.0 >

Thank you for the bug report. Verified on Linux also.

mysql> SELECT * FROM permission_template_permissions;
ERROR 1194 (HY000): Table 'permission_template_permissions' is marked as crashed and should be repaired
mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  | 
| version                 | 5.1.28-debug        | 
| version_comment         | Source distribution | 
| version_compile_machine | x86_64              | 
| version_compile_os      | unknown-linux-gnu   | 
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql>

a bit shorter testcase
----------------------

drop table if exists `t1`;
create table  `t1` (`a` int) checksum=1 engine=myisam row_format=dynamic;
insert into t1 values(1);
check table t1;

See bug #37310: 'on update CURRENT_TIMESTAMP' option crashes the table.