MySQL Bugs: #57142: Repeatedly dropping/creating database results in corrupt key index in myisam

Bug #57142	Repeatedly dropping/creating database results in corrupt key index in myisam
Submitted:	30 Sep 2010 13:50	Modified:	7 Nov 2010 20:48
Reporter:	James Ravn	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.1.51	OS:	MacOS (10.6)
Assigned to:		CPU Architecture:	Any
Tags:	corruption, key, myisam

Description:
I have an automated build process that cleans out the database between tests by dropping it and then creating it. The sql looks like this:

drop database if exists foodb; create database foodb;

grant all on foodb.* to test@'localhost' identified by 'test';
grant usage, reload, references
on *.* to test@'localhost' identified
by 'test';
flush privileges;

CREATE TABLE IF NOT EXISTS bar_ (
    a VARCHAR(255) BINARY,
    b TIMESTAMP NOT NULL, INDEX(b),
    c INT NOT NULL, INDEX(c),
    d BIGINT UNSIGNED PRIMARY KEY,
    e bigint(20),
    f bigint(20),
    g bigint(20),
    h TINYINT NOT NULL,
    i VARCHAR(255) BINARY NOT NULL UNIQUE,
    j VARCHAR(255) BINARY NOT NULL,
    k INT,
    INDEX(a)
);

CREATE TABLE IF NOT EXISTS ... (many more)

The tests themselves load test fixture data into the tables. This works fine at first, but inevitably the build will eventually fail with this message (loading some initial data):

[ERROR] Failed to execute:  IINSERT INTO bar_ (a, b, c, d, i, j, h) VALUES (null, now(), 10513, 1, 'default', 'Default', true);
[INFO] ------------------------------------------------------------------------
[ERROR] BUILD ERROR
[INFO] ------------------------------------------------------------------------
[INFO] Incorrect key file for table 'bar_'; try to repair it

Running myisamchk doesn't show anything:

# myisamchk -e -v bar_.MYI
Checking MyISAM file: bar_.MYI
Data records:       0   Deleted blocks:       0
- check file-size
- check record delete-chain
No recordlinks
- check key delete-chain
block_size 1024:
block_size 2048:
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check data record references index: 5
- check records and index references

Running the same SQL command in mysql also produces the identical error:

mysql> INSERT INTO bar_ (a, b, c, d, i, j, h) VALUES (null, now(), 10513, 1, 'default', 'Default portfolio', true);

However, restarting mysqld fixes the issue:

mysql> INSERT INTO SDSPortfolio_ (source, modified, class_, id_, name_, description_, approved_) VALUES (null, now(), 10513, 1, 'default', 'Default portfolio', true);
Query OK, 1 row affected (0.00 sec)

My guess is there is some corruption of the in-memory key file, while the on disk data is fine.

This only happens in myisam, innodb works fine. Also, I can always reproduce it on macbooks running either 10.5 or 10.6 (leopard/snow leopard). I have been unable to reproduce this on a linux or windows machine. I have tried numerous combinations of server options (e.g. key-buffer-size of 0), but am unable to find a workaround.

Unfortunately, I was not able to construct contrived SQL to reproduce the problem, and am unable to provide my proprietary schema/build scripts.

How to repeat:
0. Drop database and create it again
1. Create tables with unique indexes.
2. Insert data into tables
3. Repeat 0 through 2 until error occurs

Thank you for the report.

How table bar_ related to database foodb? Is it belongs to or not?

Table bar_ is in foodb. It is recreated each time the database is dropped/created.

thank you for the feedback.

How many rows do you insert into table bar_  in each iteration? Do you run other DML statements on it?

This particular table only gets a single entry inserted. However other tables will add many entries as a result of fixtures being loaded from tests.

I am having the same issue when creating a table via C client calls (though i think this is irrelevant).

Identical to the original poster:
-) i have no issues on other non-mac platforms
-) stopping and restarting the server solves the problem until it randomly appears again
-) this happens on different tables, i.e., seemingly random
-) problem is recognized by trying to insert a single row immediately after table creation, (resulting in error message: "Error: Incorrect key file for table 'chnmeta'; try to repair it", or
-) problem also occurs after table creation followed by Alter Table command, same error as previous
-) this is old code that has been working successfully for years until now

"show table status", immediately after table creation, indicates the table does exist, but also indicates all fields are = NULL.  i assume this means that the create table request itself has not successfully completed?  (even though the create table SQL itself throws no error, one sees the problem only after attempting to access the table in some way.)

my version details:
mac os: 10.6.4
mysql:  5.1.47

Thank you for the feedback.

I can not repeat described behavior using script:

$cat bug57142.sh 
#!/bin/bash

~/host/build/mysql-5.1/bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock -e "drop database if exists foodb; create database foodb;
use foodb; CREATE TABLE IF NOT EXISTS bar_ (
    a VARCHAR(255) BINARY,
    b TIMESTAMP NOT NULL, INDEX(b),
    c INT NOT NULL, INDEX(c),
    d BIGINT UNSIGNED PRIMARY KEY,
    e bigint(20),
    f bigint(20),
    g bigint(20),
    h TINYINT NOT NULL,
    i VARCHAR(255) BINARY NOT NULL UNIQUE,
    j VARCHAR(255) BINARY NOT NULL,
    k INT,
    INDEX(a)
);

INSERT INTO bar_ (a, b, c, d, i, j, h) VALUES (null, now(),
10513, 1, 'default', 'Default', true);
"

And if I run it as for i in `seq 1 1 1000000`; do ./bug57142.sh ; done 

Please try to create public test case we can repeat the problem with.

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".