Bug #46603 MyISAM table crash, probably caused by a stored procedure
Submitted: 7 Aug 2009 14:44 Modified: 15 Oct 2009 8:05
Reporter: Frederic Steinfels Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.1.36 and earlier (?) OS:Any
Assigned to: CPU Architecture:Any
Tags: crash, myisam, stored procedure, table

[7 Aug 2009 14:44] Frederic Steinfels
Description:
For half a year, I do have troubles with exactly one of my tables called 'mybar'. I can therefore assume it is not some random hardware or compilation time error.

The table mybar is heavily updated by a cascade of triggers and stored procedures. When the error occurs, I do get this message:

090807 16:17:09 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './dvdupgrades/mybar.MYI'; try to repair it
090807 16:17:09 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './dvdupgrades/mybar.MYI'; try to repair it
090807 16:20:30 [ERROR] /usr/libexec/mysqld: Table './dvdupgrades/mybar' is marked as crashed and should be repaired
090807 16:20:30 [ERROR] /usr/libexec/mysqld: Table './dvdupgrades/mybar' is marked as crashed and should be repaired

As this error occurs randomly every few days, I do not know what the trigger is.

How to repeat:
TBA (private conversation)

Suggested fix:
Make it not crash :-)
[7 Aug 2009 14:53] Valeriy Kravchuk
Thank you for the problem report. Do you have any other suspicious messages in the error log before these? Please, send the results of:

show create table mybar\G
show table status like 'mybar'\G

and your my.cnf/my.ini file content.
[7 Aug 2009 15:37] Frederic Steinfels
CREATE TABLE `mybar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cat1_id` int(11) NOT NULL DEFAULT '0',
  `cat2_id` int(11) NOT NULL DEFAULT '0',
  `manufacturer_id` int(11) NOT NULL DEFAULT '0',
  `pg` enum('0','1') NOT NULL DEFAULT '0',
  `hd` enum('0','1') NOT NULL DEFAULT '0',
  `valid` enum('0','1') NOT NULL DEFAULT '0',
  `dealeronly` enum('0','1') NOT NULL DEFAULT '0',
  `c1d` varchar(64) NOT NULL,
  `c2d` varchar(64) NOT NULL,
  `mnd` varchar(64) NOT NULL,
  `c1e` varchar(64) NOT NULL,
  `c2e` varchar(64) NOT NULL,
  `mne` varchar(64) NOT NULL,
  `c1f` varchar(64) NOT NULL,
  `c2f` varchar(64) NOT NULL,
  `mnf` varchar(64) NOT NULL,
  `c1c` varchar(64) NOT NULL,
  `c2c` varchar(64) NOT NULL,
  `mnc` varchar(64) NOT NULL,
  `c1i` varchar(64) NOT NULL,
  `c2i` varchar(64) NOT NULL,
  `mni` varchar(64) NOT NULL,
  `soldout` enum('0','1') NOT NULL DEFAULT '0',
  UNIQUE KEY `cat1_id` (`cat1_id`,`cat2_id`,`manufacturer_id`),
  UNIQUE KEY `id` (`id`),
  KEY `pg` (`pg`),
  KEY `hd` (`hd`),
  KEY `valid` (`valid`),
  KEY `dealeronly` (`dealeronly`),
  KEY `soldout` (`soldout`),
  KEY `c1d` (`c1d`),
  KEY `c2d` (`c2d`),
  KEY `mnd` (`mnd`),
  KEY `c1e` (`c1e`),
  KEY `c2e` (`c2e`),
  KEY `mne` (`mne`),
  KEY `cat1_id_2` (`cat1_id`),
  KEY `manufacturer_id` (`manufacturer_id`),
  KEY `cat2_id` (`cat2_id`),
  KEY `c1f` (`c1f`),
  KEY `c2f` (`c2f`),
  KEY `mnf` (`mnf`),
  KEY `c1c` (`c1c`),
  KEY `c2c` (`c2c`),
  KEY `mnc` (`mnc`),
  KEY `c1i` (`c1i`),
  KEY `c2i` (`c2i`),
  KEY `mni` (`mni`)
) ENGINE=MyISAM AUTO_INCREMENT=40080 DEFAULT CHARSET=utf8
[7 Aug 2009 15:37] Frederic Steinfels
[mysqld]
default-character-set = utf8
thread_cache_size=100
thread_stack=1M
join_buffer_size=1024M
join_buffer=1024M
key_buffer_size=2048M
key_buffer=2048M
max_allowed_packet= 16M
read_buffer_size= 512M
read_rnd_buffer_size= 512M
sort_buffer_size= 512M
table_cache=500
group_concat_max_len=8M
thread_stack           = 1M
query_cache_limit      = 1000
query_cache_size        = 500M
query_cache_type        = 1
max_connections = 200
long_query_time = 3
log-slow-queries = /var/log/mysqlslow.log
log-queries-not-using-indexes = FALSE
max_heap_table_size = 512M
tmp_table_size = 512M

[mysqld_safe]
err-log=/var/log/mysqld.log
[7 Aug 2009 15:48] Frederic Steinfels
There are no more messages in mysqld.log

I had this error multiple times. Not always is mysql reporting this error immediately. Sometimes doing a select with where condition, the output is the same row over and over again although the table contains 10k different rows. Furthermore sometimes a "ANALYZE TABLE mybar" results OK (no error found). I think there might also be a descripancy between the cache of the table and the table on the disk...

I have uploaded this (crashed) table to your ftp.
I have also made a private attachement with all stored procedures. You will see there is a chain of triggers going through like multiple tables (psource, pactive, product, mybar). Maybe this can help debugging.
[7 Aug 2009 19:14] Frederic Steinfels
mybar2 is a copy of the table as it was on disk when it crashed. mybar is the repaired version

mysql> show table status like 'mybar';
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| mybar | MyISAM |      10 | Dynamic    | 13647 |            162 |     2213756 | 281474976710655 |      2150400 |         0 |          40080 | 2009-01-09 06:27:46 | 2009-08-07 16:20:59 | 2009-08-07 16:21:00 | utf8_general_ci |     NULL |                |         |
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> show table status like 'mybar2';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+--------------------------------------------------------------------------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment                                                                  |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+--------------------------------------------------------------------------+
| mybar2 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | Table './dvdupgrades/mybar2' is marked as crashed and should be repaired |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+--------------------------------------------------------------------------+
1 row in set (0.01 sec)
[8 Aug 2009 12:42] Sveta Smirnova
Thank you for the data provided.

When I run CHECK TABLE mybar2 I get:

mysql> check table mybar2;
+-----------------+-------+----------+-------------------------------------------------------+
| Table           | Op    | Msg_type | Msg_text                                              |
+-----------------+-------+----------+-------------------------------------------------------+
| bug46603.mybar2 | check | warning  | Table is marked as crashed                            |
| bug46603.mybar2 | check | warning  | 1 client is using or hasn't closed the table properly |
| bug46603.mybar2 | check | status   | OK                                                    |
+-----------------+-------+----------+-------------------------------------------------------+
3 rows in set (10.38 sec)

Please consult manual page at http://dev.mysql.com/doc/refman/5.1/en/myisam-table-close.html and try to find what in your application lead to such behavior.
[8 Aug 2009 15:16] Frederic Steinfels
I have copied the MyISAM files on purpose without doing any flush or whatever to minimize the possibility of additional changes to the table files which might jeopardize the investigation of what lead to the crash in the first place.
I have absolutely no problem with table locks. I am not sure wheter you do understand the bug that I have reported.
[8 Aug 2009 18:47] Sveta Smirnova
Thank you for the feedback.

If I understood correctly you have repeatable table corruptions. To prove this is MySQL bug and nothing wrong in your environment we need to create repeatable test case.

My first guess was large key_buffer_size as we had reported similar problem with it. But in this case problem should be fixed in version 5.1.36.

So would be good if you could find what exactly causes corruption. Please provide output of SHOW CREATE TABLE for tables which have triggers which update table mybar. Also, please, provide scenario with load you do. We need to repeat same actions on the table as you do.

Do you run table maintaince statements or do you update table mybar only from triggers provided?
[9 Aug 2009 3:28] Frederic Steinfels
I understand you need a repeatable test case but the problem is the table corruptions are not repeatable, they are just reoccuring. Even if I encounter this again, it might be hours after the error occured. I have no clue what statement caused it.

if you absolutely need a test case and can not try to figure out a way around, we probably have to leave it as is. as table corruption is really a very critical thing, it might be advisable to put extra effort into this. I am willing to assist you if you want to try to provoke this error manually (on my system).
[9 Aug 2009 5:42] MySQL Verification Team
Hi Frederic,

I got the .pl script, but can you upload output of this (hidden):

mysqldump --routines --no-data --all-databases -uroot -p > bug46603_schema.sql

Thanks!
[10 Aug 2009 9:26] Frederic Steinfels
I have uploaded the schema.

As I said I do not think you can reproduce it even with this information. The bug occurs only on a rare base. If you want I can give you some tables with data in it and I can tell you what fileds/tables have been written at the time the error occured...
[13 Aug 2009 12:06] Susanne Ebrecht
Just another idea:

How old is your database. Did you upgrade the database to 5.1 from previous MySQL versions like 5.0 or 4.1?
[13 Aug 2009 19:05] Sveta Smirnova
Frederic,

I did some tests and can not repeat described behavior.

Problem is table corruption only can be considered as a bug if we prove this is caused by SQL statements and not with other actions. Table corruption itself is not a bug.

Please send us dump of tables manufacturer, product, category and examples of queries which update/insert into these tables.
[14 Aug 2009 11:55] Frederic Steinfels
That's what I said: the error occurs so randomly that I can not repeat it myself. I am 100% sure the hardware is ok, no other tables are affected although I have much bigger tables with much more r/w access.

I thought maybe you could get a clue from analyzing the crashed table file but if that is not possible, I can only wait till it happens again and then probably do nothing. Unless you can tell me how to get more debug information out of it...

The table was created in january 2009 using a 5.1 version of mysql.
[15 Sep 2009 8:05] Sveta Smirnova
Thank you for the feedback.

But we still need repeatable test case as generic tests which we tried did not lead to described behavior.

Please send us dump of tables manufacturer, product, category and examples of queries
which update/insert into these tables.
[15 Oct 2009 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".