MySQL Bugs: #3835: alter table enable/disable keys corrupts database

Bug #3835	alter table enable/disable keys corrupts database
Submitted:	20 May 2004 10:51	Modified:	24 May 2004 11:03
Reporter:	Mirza Hadzic	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.1.2	OS:	Windows (Win 2000/XP)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any
Tags:	corruption, myisam

Description:
Hello,

I had problems with DB corruption in MyISAM table that I only created and then inserted lot of records (exectly 227553) using c program. Then I realized that problem disapears when I remove "alter table disable keys" from begin and "alter table enable keys" from the end of the program. I put it there for performance reasons. There is only one index: fulltext.
I am sending you table streets that checks OK, becouse I removed last "alter table enable keys". You can do that yourself from sql prompt:

>alter table streets enable keys

...and database becomes corrupted.

How to repeat:
alter table streets enable keys

now, I see that I cannot attach file larger 200KB, but this .zip file has 7MB. Is there any other means of sending files?

one more thing: sorry for the bogus version: it is not 4.2 but 4.1.2 snapshot 20040517

Please only attach the table structure schema and
your C program for I will do the test.

Thanks.

Unfortunatelly this is not possible becouse source of the data are huge GIS databases (Many GB) that are read, transformed and written to this MySQL table. I can however sand this database to any FTP you say. Resulting MySQL database is small (7MB) becouse I shortened main loop in program to point where database already gets corrupted (that is 227553 records, one record less and everything works OK).

Ok. Please upload the zip file at:

ftp://support.mysql.com/pub/mysql/secret/

and let me know. Thanks

I wrote a wrong ftp URL before. Please use:

ftp://ftp.mysql.com/pub/mysql/secret/

Thanks

OK it is transferred, ta_sw... zip file. I wonder wether it will reproduce on your site.

I tried to copy to that 2nd FTP and it says that same file already exists there, is it correct?

Yes it is correct, just now I saw your file on
our server. Thanks.

Thank you for your test case:

C:\servers\4.1.2\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.2-alpha-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use ta_swiss_2003_3;
Database changed
mysql> check table streets\G
*************************** 1. row ***************************
   Table: ta_swiss_2003_3.streets
      Op: check
Msg_type: status
Msg_text: OK
1 row in set (5.63 sec)

mysql> alter table streets enable keys\G
Query OK, 0 rows affected (20.13 sec)

mysql> check table streets\G
*************************** 1. row ***************************
   Table: ta_swiss_2003_3.streets
      Op: check
Msg_type: error
Msg_text: Key in wrong position at page 4434944
*************************** 2. row ***************************
   Table: ta_swiss_2003_3.streets
      Op: check
Msg_type: error
Msg_text: Corrupt
2 rows in set (0.58 sec)

Here some notes that might help you:

- This table had a lot (8) indicies but:
a) problem disapears when I remove fulltext index AND r-tree spatial index ("normal" indicies works OK)
b) If I keep fulltext and/or spatial index, database gets corrupted

- today I encountered that this is not related to alter table disable/enable keys. I was probably lucky first time, but now it also occurs without disable/enable keys.

mirza

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

fixed in 4.1.3

Fixed in 4.1.3? So, it is not available in daily snapshots in Download section? As far as I know those snapshots are 4.1.2 version?

Not really , daily snapshots are 4.1 - that is the latest 4.1.x
At some point in time, the tree is cloned to build a release. After that, changes won't reach 4.1.2 - that's why I wrote 4.1.3. But because of a couple of build problemsthe build was restarted, so the fix *will* be in 4.1.2.