System: Win XP home, Mysql 4.0.15. Implementation with this version since 10/01/2003 with no problems.
Normal access: MySQL JDBC driver (used to be the MM driver, now obtained from MySQL), Java SDK 1.4.2_01. Problem has been verified using mysql command line as well.
Here's a sample table definition:
CREATE TABLE appraised ( idNumber int(11) NOT NULL default '0', indexNo int(11) NOT NULL default '0', status int(11) default NULL, appraiser int(11) default NULL, comments text, PRIMARY KEY (idNumber,indexNo) ) TYPE=MyISAM;
idNumber is a foreign key (it is the user's ID number).
indexNo is a foreign key (it ties in to an appraisal table with item descriptions).
the primary key allows multiple appraisals for a single user (i.e.the idNumber and indexNo pair must be unique).
I want to delete ALL appraisals for a given user, but this is generating MySQL errors. These errors first appeared in the servlet/JDBC error logs, but was then confirmed by typing the SQL command manually from a DOS window.
The SQL command that causes the problem is:
DELETE FROM appraised WHERE idNumber=1423;
(where 1423 is any valid ID number).
The delete proceeds normally, but the next attempt at ANY table access (i.e. "SELECT * FROM appraisal") generates the following error (this is the JDBC error):
DBMS SQLException. Time:Fri Dec 26 15:14:16 GMT-07:00 2003 SQL Problem: General error: Got error 22 from table handler SQL State: S1000 Vendor Error: 1030
Basically, the error means the table is destroyed. Any attempt to access the table generates this error - the table must be rebuilt. I also often get ERROR 127 as well.
Now - if I (using JDBC/servlet) get all records for a single user, and then delete using a loop on each idNumber-index pair, this error does not occur. It seems to be some problem with deleting records based on one attribute of a primary key pair, but this should not normally be a problem.
SO - Has anyone seen this error before, and can anyone point me to the cause (and solution)?
Thanks,
-Richard
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
