mySQL auto_increment whacked out

Anon

Earlier, I had posted (unfortunately it wasn't on this forum) that my mySQL database seemed to be getting corrupted upon $PHP queries. After doing a bunch more digging, I have at least been able to produce something in mySQL (leaving PHP out of the loop, but I saw a related post in this forum) which has me completely baffled (I am running on Win2k with mySQL 4.0.1-alpha, but
I believe the same thing was happening with 4.0.0-alpha):

Synopsis:
Whenever there is an auto_increment primary key I always get the following lines whenever I run:
myisamchk -e ..\data\photos
from the mysql\bin directory (but also happens if I go to the destination dir):

myisamchk: Can't unlock file (Errcode: 11)
- check records and index references
myisamchk: error: 11 when locking indexfile 'orignalArgToMyisamchk'

Steps to reproduce, having selected the HelpMe database:
(1) mysql> create table Test4 (col1 int unsigned NOT NULL auto_increment
PRIMARY KEY, col2 varchar(15) NOT NULL);
(2) mysql> insert into test4 set col2='paint';
(3) mysql> quit;
(4) myisamchk -e -v ..\data\HelpMe\Test4

Any enlightenment is appreciated since I'm completely stumped.
Csaba Gabor from New York

Three outputs follow (The first being, at the least, impertinent):

C:\Winapps\mySQL\bin>myisamchk -o -f -v ..\data\HelpMe\Test4
- recovering (with keycache) MyISAM-table '..\data\HelpMe\Test4'
Data records: 1
myisamchk: error: 11 when locking indexfile '..\data\HelpMe\Test4'
MyISAM-table '..\data\HelpMe\Test4' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option

C:\Winapps\mySQL\bin>myisamchk -e -v ..\data\HelpMe\Test4
Checking MyISAM file: ..\data\HelpMe\Test4
Data records: 1 Deleted blocks: 0
- check file-size
- check key delete-chain
block_size 1024:
- check record delete-chain
No recordlinks
- check index reference
- check data record references index: 1
myisamchk: Can't unlock file (Errcode: 11)
- check records and index references
myisamchk: error: 11 when locking indexfile '..\data\HelpMe\Test4'

C:\Winapps\mySQL\bin>myisamchk -d -v ..\data\HelpMe\Test4

MyISAM file: ..\data\HelpMe\Test4
Record format: Packed
Character set: latin1 (8)
File-version: 1
Creation time: 2002-04-28 17:30:21
Status: changed
Auto increment key: 1 Last value: 1
Data records: 1 Deleted blocks: 0
Datafile parts: 1 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
Datafile length: 20 Keyfile length: 2048
Max datafile length: 4294967294 Max keyfile length: 4398046510079
Recordlength: 19

table description:
Key Start Len Index Type Rec/key Root Blocksize
1 1 4 unique unsigned long 1 1024 1024

[deleted]

-e is a 'last restort' recovery option, I bet it tries _(in vain) to lock the table.

Try running the command without the -e, or stop the database before running it.

Anon

You are correct about the locking issue. However, I have tried this with and without the -e option, with and without 'skip-locking' and also ensuring that the mySql server is down.

Here is a 17 line .bat file which will show the problem when run from the mySql/bin directory, my comments follow

--- Demo.bat --- Run from mySql/bin on Win2K ---
net stop mysql
net start mysql
mysql -e"DROP DATABASE IF EXISTS Csaba;"
mysql -e"CREATE DATABASE Csaba;"
mysql -DCsaba -e"CREATE TABLE test1 (col1 INT NOT NULL AUTO_INCREMENT, col2 char(15)
NOT NULL,
PRIMARY KEY (col1));"
mysql -DCsaba -e"INSERT INTO test1 SET col2='Hi Mom';"
mysql -DCsaba -e"SELECT * FROM test1;"
mysql -DCsaba -e"SHOW KEYS FROM test1;"
mysql -DCsaba -e"SHOW CREATE TABLE test1;"
mysql -DCsaba -e"FLUSH TABLES;"
mysql -e"FLUSH TABLES;"
mysqladmin processlist
net stop mysql
mysqladmin processlist
myisamchk -d -v ..\data\Csaba\test1
myisamchk -e -v ..\data\Csaba\test1
net start mysql
--- End of Demo.bat --- -------------------- ---

1-2) Ensure we have a fresh mysql daemon
3-4) Make a fresh database Csaba
5) Make a fresh table with an Auto Increment column.
6-7) Put in one row, make sure it's there
8-9) Show Keys and Table type. Note that trying to
set a TYPE=ISAM is ignored (converted to MyISAM) so
I could not determine if this is specific to MyISAM
tables. Note that there is a Packed field for SHOW
KEYS which is not what the documentation says.
10-11)Flush tables in two ways cause I'm not 100% clear
on how it works.
12) Show that we're the only process
13) Make sure nothing is locking that table
14) Really show we're shut down
15) Show table/index info
16) --> Error is here <--
17) Restore original server state