I have a auto increment field in a table and after doing a few operations the flag has gone away. This is using mysql 3.23.36 on OpenBSD. Here is how it happened:

I am using perl with DBI to interface with mysql. I have been inserting "NULL" into this field to allow mysql to fill in the auto_increment value for me. Eventually I started using mysqlimport to put records into this same table. When the text file had "NULL" for the auto_increment field, mysqlimport would count that as a warning. Wanting to get mysqlimport to run warning free I tried changing the values from NULL to 0 for the auto_increment field. This worked great.

Sometime a week later, and many many imports, I changed the perl code to insert with 0 instead of "NULL". Instead of working as advertised, the insert resulted in a row with id set to "0" rather than the next auto_increment value. I deleted the row with id 0, and tried running mysqlimport again only to find it will not insert anything because of duplicate key entry "0" (even though it has been deleted). I ran myisamchk and it said the table was fine. After running myisamchk I noticed the extra column does not list "auto_increment" when doing a "describe table". It just went away.

When I try to do "select * from table where id = 0" I get error 127, record-file is crashed.

Does anyone know how I can fix this without loosing any data or changing the values in the auto_increment field? Did I do something wrong to screw up the table? How can this be avoided in the future?

Thanks in advance. ryan