Bug #17162 MyIsam table corrupted without a reason
Submitted: 6 Feb 2006 17:40 Modified: 9 Mar 2006 9:29
Reporter: Eider Oliveira Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.18 OS:MacOS (Macosx)
Assigned to: CPU Architecture:Any

[6 Feb 2006 17:40] Eider Oliveira
Description:
mysql> select count(*) from msg_life;
+----------+
| count(*) |
+----------+
| 52905556 |
+----------+
1 row in set (0.29 sec)

mysql> update msg_life, msg_life_b as orig set msg_life.atime = orig.atime where orig.atime > msg_life.atime and msg_life.user = orig.user and msg_life.msgid = orig.msgid;
ERROR 1030 (HY000): Got error 134 from storage engine

**** I've changed the from table and it worked:

mysql> update msg_life, msg_life_c as orig set msg_life.atime = orig.atime where orig.atime > msg_life.atime and msg_life.user = orig.user and msg_life.msgid = orig.msgid;
        Query OK, 292968 rows affected (23 hours 42 min 15.89 sec)
Rows matched: 292968  Changed: 292968  Warnings: 0

mysql> insert into msg_summary (user,msgid,ctime,rtime,atime,dtime) select m.user,m.msgid,m.rtime,m.ctime,m.atime,m.dtime from msg_life as m,msg_life_a as a, msg_life_b as b, msg_life_c as c where m.user = a.user and m.msgid = a.msgid and m.user = b.user and m.msgid = b.msgid and m.user = c.user and m.msgid = c.msgid;
Query OK, 318982 rows affected (6 hours 31 min 39.66 sec)
Records: 318982  Duplicates: 0  Warnings: 0

mysql> select count(*) from msg_life where dtime is null;
+----------+
| count(*) |
+----------+
| 41598372 |
+----------+
1 row in set (2 min 35.34 sec)

mysql> select count(*) from msg_life where dtime is not null;
ERROR 1194 (HY000): Table 'msg_life' is marked as crashed and should be repaired

How to repeat:
I coundn not reproduce it
[7 Feb 2006 11:03] Valeriy Kravchuk
Thank you for a problem report. Are that tables created in 5.0.18 or you created them in 4.x.y just upgraded to 5.0.18? Anyway, SHOW CREATE TABLE results for all the tables involved in that queries are needed.
[7 Feb 2006 13:34] Eider Oliveira
All tables were created with version 5.0.18. Follow the create table results and the repair table result:

mysql> select count(*) from msg_life where dtime is not null;
ERROR 145 (HY000): Table './statistic/msg_life' is marked as crashed and should be repaired
mysql> repair table msg_life;
+--------------------+--------+----------+----------------------------------------------------------------------------+
| Table              | Op     | Msg_type | Msg_text                                                                   |
+--------------------+--------+----------+----------------------------------------------------------------------------+
| statistic.msg_life | repair | info     | Duplicate key  1 for record at 2521359732 against new record at 1044471516 |
| statistic.msg_life | repair | warning  | Number of rows changed from 52905556 to 52905555                           |
| statistic.msg_life | repair | status   | OK                                                                         |
+--------------------+--------+----------+----------------------------------------------------------------------------+
3 rows in set (19 hours 41 min 9.02 sec)

mysql> select count(*) from msg_life where dtime is not null;
+----------+
| count(*) |
+----------+
| 11307183 |
+----------+
1 row in set (4 min 38.95 sec)
[7 Feb 2006 13:35] Eider Oliveira
mysql> show create table msg_life;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                        |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| msg_life | CREATE TABLE `msg_life` (
  `user` varchar(45) NOT NULL,
  `msgid` varchar(40) NOT NULL,
  `ctime` int(11) NOT NULL,
  `rtime` int(11) NOT NULL,
  `atime` int(11) NOT NULL,
  `dtime` int(11) default NULL,
  PRIMARY KEY  (`user`,`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.15 sec)

mysql> show create table msg_life_a;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                         |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| msg_life_a | CREATE TABLE `msg_life_a` (
  `user` varchar(45) NOT NULL,
  `msgid` varchar(40) NOT NULL,
  `ctime` int(11) NOT NULL,
  `rtime` int(11) NOT NULL,
  `atime` int(11) NOT NULL,
  `dtime` int(11) default NULL,
  PRIMARY KEY  (`user`,`msgid`),
  KEY `user` (`user`,`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> show create table msg_life_b;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                         |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| msg_life_b | CREATE TABLE `msg_life_b` (
  `user` varchar(45) NOT NULL,
  `msgid` varchar(40) NOT NULL,
  `ctime` int(11) NOT NULL,
  `rtime` int(11) NOT NULL,
  `atime` int(11) NOT NULL,
  `dtime` int(11) default NULL,
  PRIMARY KEY  (`user`,`msgid`),
  KEY `user` (`user`,`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[7 Feb 2006 13:35] Eider Oliveira
mysql> show create table msg_life_c;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                         |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| msg_life_c | CREATE TABLE `msg_life_c` (
  `user` varchar(45) NOT NULL,
  `msgid` varchar(40) NOT NULL,
  `ctime` int(11) NOT NULL,
  `rtime` int(11) NOT NULL,
  `atime` int(11) NOT NULL,
  `dtime` int(11) default NULL,
  PRIMARY KEY  (`user`,`msgid`),
  KEY `user` (`user`,`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> show create table msg_summary;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                           |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| msg_summary | CREATE TABLE `msg_summary` (
  `user` varchar(45) NOT NULL,
  `msgid` varchar(40) NOT NULL,
  `ctime` int(11) NOT NULL,
  `rtime` int(11) NOT NULL,
  `atime` int(11) NOT NULL,
  `dtime` int(11) default NULL,
  PRIMARY KEY  (`user`,`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.15 sec)
[8 Feb 2006 9:31] Valeriy Kravchuk
Thank you for the additional information. Send your my.cnf file content and SHOW TABLE STATUS results for the table that usually corrupted. df -k results may be also useful. I am trying to check is it a hardware failure/lack of resources.

Can you repeat that corruption each and every time with the same SQL statements?
[8 Feb 2006 9:49] Eider Oliveira
It was running in my notebook, so it wasn't energy problemm, and it happened only once.
I've found no my.cnf file.
 df -k
Filesystem              1K-blocks     Used    Avail Capacity  Mounted on
/dev/disk0s3             97554672 74502144 22796528    77%    /
devfs                          98       98        0   100%    /dev
fdesc                           1        1        0   100%    /dev
<volfs>                       512      512        0   100%    /.vol
automount -nsl [153]            0        0        0   100%    /Network
automount -fstab [157]          0        0        0   100%    /automount/Servers
automount -static [157]         0        0        0   100%    /automount/static
[8 Feb 2006 9:55] Eider Oliveira
| msg_life    | MyISAM |      10 | Dynamic    | 52905555 |             49 |  2606671896 | 281474976710655 |   1428187136 |         0 |           NULL | 2006-01-19 08:14:37 | 2006-02-07 11:12:59 | 2006-02-07 11:13:01 | latin1_swedish_ci |     NULL |
[9 Feb 2006 9:29] Valeriy Kravchuk
Thank you for the additional information. If you can't find my.cnf (have you tried to use find utility for that?), please, send the SHOW VARIABLES statement results. Please, upload them as a separate file using the File tab.
[9 Feb 2006 9:34] Valeriy Kravchuk
Please, create also copies of that msg_life table involved in update and selects, put a smaller amount of rows into it and try to repeat the test. We need a (reasonably) small and repeatable test case anyway.
[10 Mar 2006 0: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".