I've submitted this problem three times now, and been ignored all three times. I guess bugs are simply not popular. Since the problem described below is 100% repeatable, I will now escallate it from "problem with delete/insert" to "BUG IN MYSQL".
Cheers...
I've distilled the problem I'm having with DELETE/INSERT to an even simpler test case.
Here's the SQL - it can be used with any database, not just the special "expdb" I created. I run this script followed by running "mysqlcheck expdb".
######################################### USE expdb;
DROP TABLE IF EXISTS appraised; CREATE TABLE appraised ( idNumber int(11) NOT NULL default '0', indexNo int(11) NOT NULL default '0', experience int(11) default NULL, lastused int(11) default NULL, competence int(11) default NULL, status int(11) default NULL, appraiser int(11) default NULL, comments text, PRIMARY KEY (idNumber,indexNo) ) TYPE=MyISAM;
PRINT; DELETE FROM appraised; INSERT INTO appraised VALUES (1,1,1,1,1,1,1,'aaa1'); SELECT * FROM appraised; DELETE FROM appraised; #########################################
If you run this with the INSERT commented out, mysqlcheck reports OK. If you don't comment out the INSERT, mysqlcheck reports:
C:\mysql-4.0.15\bin>mysqlcheck expdb expdb.appraised warning : Table is marked as crashed warning : Size of indexfile is: 2048 Should be: 1024 warning : Size of datafile is: 160 Should be: 0 error : Record-count is not ok; is 4 Should be: 0 warning : Found 4 parts Should be: 0 parts error : Corrupt
This happens every single time. You cannot use the -autorepair function, as it reports "appraised.MYD" cannot be opened.
What is going on?
Ran your exact command sequence here, on MySQL 4.0.16, running on Linux,
and it worked just fine. No need to comment out the insert.
-- Jim Richardson http://www.eskimo.com/~warlock Man's way to God is with beer in hand. --Koffyar Tribal Wisdom, Nigeria
signature.asc
Description: Digital signature
