Problem with table corrupting

Tue, 30 Dec 2003 11:41:09 -0800

I've isolated this problem down to a single table and SQL script that demonstrates the problem repeatably.

There are 4 SQLl scripts here - one to create remove the database, one to create the database and user entries, one to create the table, and one to access the table. It is the 3rd that causes the problem. Run it once, all is OK. Run it a second time - error (it claims a duplicate index but the query just prior to the insert clearly shows this is incorrect). Run it a third and you get the table error 127.

If you comment out the lines for "testDeleteUser", (and rebuild the table), you can run the query dozens of times and never get the error.

Somehow, the SQL in "testDeleteUser" is breaking the table, causing "testupdate" to break on one insert (always the same place!).

Any ideas???

Thanks,

-Richard


First, the database and user remove script (to rebuild the table later).
########################################################################
USE mysql;

DELETE FROM db WHERE db='expdb';

DELETE FROM user WHERE user='expdemo';

DROP DATABASE expdb;
########################################################################


Now, the database create and user create...

########################################################################
USE mysql;

INSERT INTO user (host,user,password)
 VALUES ('localhost','expdemo',password('demo66exp'));

INSERT INTO db (host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES('localhost','expdb','expdemo','Y','Y','Y','Y','Y','Y');

########################################################################

Now, the table create...
########################################################################
--
-- Current Database: expdb
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ expdb;

USE expdb;

--
-- Table structure for table '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;

########################################################################

Finally, the SQL that causes the problem...

########################################################################
USE expdb;

####### start of tests
####### testupdate
PRINT;
DELETE FROM appraised;
INSERT INTO appraised VALUES (1,1,1,1,1,1,1,'aaa1');
INSERT INTO appraised VALUES (2,2,2,2,2,2,2,'aaa2');
INSERT INTO appraised VALUES (3,3,3,3,3,3,3,'aaa3');
INSERT INTO appraised VALUES (4,4,4,4,4,4,4,'aaa4');
SELECT * FROM appraised;
SELECT * FROM appraised WHERE idNumber=1 AND indexNo=1;
SELECT * FROM appraised WHERE idNumber=2 AND indexNo=2;
SELECT * FROM appraised WHERE idNumber=3 AND indexNo=3;
SELECT * FROM appraised WHERE idNumber=4 AND indexNo=4;
DELETE FROM appraised WHERE idNumber=2 AND indexNo=2;
INSERT INTO appraised VALUES (2,2,2,2,2,2,2,'aaa2');
SELECT * FROM appraised WHERE idNumber=2 AND indexNo=2;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=2 AND indexNo=2;
INSERT INTO appraised VALUES (2,2,2,2,2,3,2,'aaa2');
SELECT * FROM appraised WHERE idNumber=2 AND indexNo=2;
SELECT * FROM appraised;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=4 AND indexNo=4;
INSERT INTO appraised VALUES (4,4,4,4,4,4,4,'aaa4');
SELECT * FROM appraised WHERE idNumber=4 AND indexNo=4;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=4 AND indexNo=4;
INSERT INTO appraised VALUES (4,4,4,4,4,6,4,'aaa4');
SELECT * FROM appraised WHERE idNumber=4 AND indexNo=4;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=6 AND indexNo=6;
INSERT INTO appraised VALUES (6,6,6,6,6,6,6,'aaa6');
SELECT * FROM appraised WHERE idNumber=6 AND indexNo=6;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=6 AND indexNo=6;
INSERT INTO appraised VALUES (6,6,6,6,6,9,6,'aaa6');
SELECT * FROM appraised WHERE idNumber=6 AND indexNo=6;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=8 AND indexNo=8;
INSERT INTO appraised VALUES (8,8,8,8,8,8,8,'aaa8');
SELECT * FROM appraised WHERE idNumber=8 AND indexNo=8;
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=8 AND indexNo=8;
INSERT INTO appraised VALUES (8,8,8,8,8,12,8,'aaa8');
SELECT * FROM appraised WHERE idNumber=8 AND indexNo=8;
SELECT * FROM appraised;
DELETE FROM appraised;

####### testDeleteUser
####### NOTE: comment out the SQL statements below and the problem disappears!
PRINT;
DELETE FROM appraised;
INSERT INTO appraised VALUES (1,1,1,1,1,1,1,'aaa1');
INSERT INTO appraised VALUES (1,5,5,5,5,5,5,'aaa14');
INSERT INTO appraised VALUES (2,2,2,2,2,2,2,'aaa2');
INSERT INTO appraised VALUES (2,6,6,6,6,6,6,'aaa24');
INSERT INTO appraised VALUES (3,3,3,3,3,3,3,'aaa3');
INSERT INTO appraised VALUES (3,7,7,7,7,7,7,'aaa34');
INSERT INTO appraised VALUES (4,4,4,4,4,4,4,'aaa4');
INSERT INTO appraised VALUES (4,8,8,8,8,8,8,'aaa44');
SELECT * FROM appraised;
DELETE FROM appraised WHERE idNumber=1;
DELETE FROM appraised WHERE idNumber=2;
DELETE FROM appraised WHERE idNumber=3;
DELETE FROM appraised WHERE idNumber=4;
SELECT * FROM appraised;
DELETE FROM appraised;

####### end of tests

########################################################################




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to