Bug #1432 BUG in SQL engine, MySQL v4.0.14b
Submitted: 29 Sep 2003 11:10 Modified: 15 Oct 2003 23:11
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.14b OS:Microsoft Windows (Win2K Prof.)
Assigned to: Miguel Solorzano CPU Architecture:Any

[29 Sep 2003 11:10] [ name withheld ]
Description:
This is the SQL code that produces the error:

DELETE FROM testdata.absences;
INSERT INTO testdata.absences SELECT * FROM sourcedata.absences;
INSERT INTO testdata.absences VALUES (101,302,'2003/09/23',2001,'1',201,0,0,505)

How to repeat:
Data is copied from a source database, sourcedata, to the destination database, testdata,
followed by inserting one record into the testdata.absences table.

The first time through, it runs fine without error. The second time through, this error 
occurs: "1034- Couldn't fix table with quick recovery: Found wrong number of deleted records"

I'll try ftping the tables in a zipped file but I think this will be very easy to get to fail the data is not as important as the sequence of SQL execution.

Suggested fix:
Should work no matter how many times this sequence of SQL statements is executed.
[30 Sep 2003 2:30] Alexander Keremidarski
Looks like you are reporting completely different problem.

"1034- Couldn't fix table with quick recovery: Found wrong number
of deleted records"

This error can be sent only by REPAIR command or in case if your server is started with myisam-recover option.

Is this the case?

Such error means table is corrupted at some point. Not necesarrlily related to queries you have sent.

Please elaborate more on 

"Data is copied from a source database, sourcedata, to the destination
database, testdata, followed by inserting one record into the testdata.absences table."

What exactly do you mean by the above? Does "copy ... from ... to ..." mean you copied databases as files? With mysqld running? Or something else?
[30 Sep 2003 8:19] [ name withheld ]
Try running the SQL statement sequence of: 
DELETE FROM testdata.absences;
INSERT INTO testdata.absences SELECT * FROM sourcedata.absences;
INSERT INTO testdata.absences VALUES
(101,302,'2003/09/23',2001,'1',201,0,0,505) 
using your own database for testdata and another for sourcedata both having the same table structure for one table, here it's the table, absences.
You probably can run this against any table of choosing since it failed against a number of different ones of mine and therefore is not very data dependent. 
You will find that it runs fine the first time through but fails the second time with the indicated error message. YES, the table is corrupted and this is part of the symptom. 
I would worry less about this error message and more about the sequence of SQL statements causing this failure. 
Explanation of the SQL: 
The "DELETE FROM testdata.absences;"
--removes all data from a table, for example, testdata.absences in the testdata database. 
The "INSERT INTO testdata.absences SELECT * FROM sourcedata.absences;" 
--copies all the data from a table, sourcedata.absences in the sourcedata database, to the table, testdata.absences in the testdata database. This setsup my initial data for testing purposes on my project. 
The "INSERT INTO testdata.absences VALUES
(101,302,'2003/09/23',2001,'1',201,0,0,505)" simulates my program code inserting some record. This is where you should find the failure occuring. I believe any new record without an existing primary key will cause the error; the actual data is not so relevant. 
To remove the error condition from the corrupted table, testdata.absences, I do an operating system copy of sourcedata.absences to the testdata database. You can then repeat the SQL statement sequence leading to the failure. 
FYI: SQL server has no problem running this SQL statement sequence repeatedly (it will not fail on the second, third, and so on, runs) as I have been using it in development for some time. 
Hope this clarifies, 
Ken Busse
[5 Oct 2003 9:00] [ name withheld ]
Here are the table files, absences.*, that were used to produce the bug

Attachment: MySQL_Bug.zip (application/x-zip-compressed, text), 63.84 KiB.

[5 Oct 2003 9:57] Alexander Keremidarski
"To remove the error condition from the corrupted table,
testdata.absences, I do an operating system copy of sourcedata.absences
to the testdata database."

Proper way of repairing corrupted table is to run command:
REPAIR TABLE table_name;

"...I do an operating system copy..."

If you do this while mysqld is running this is almost guarantiered way to end up with corrupted table!

Nothing should write to MyISAM files when mysqld is running even MySQL repair tool myisamchk must be used only when server is stopped.

Please provide full repeatable test case including Table striucture as taken with SHOW CREATE TABLE, some Data in there (INSERT statements) and step by step instructions how to repeat the problem.

Your current instructions are not enough. Here is an example of same sequence of queries causing no problems:

mysql> delete from b2.y;
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into b2.y values(1, 2, 3, '2003-09-23', 'a');
Query OK, 1 row affected (0.00 sec)
 
mysql> delete from b2.y;
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into b2.y select * from b1.x;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> check table b2.y;
+-------+-------+----------+----------+
| Table | Op    | Msg_type | Msg_text |
+-------+-------+----------+----------+
| b2.y  | check | status   | OK       |
+-------+-------+----------+----------+
1 row in set (0.00 sec)
[5 Oct 2003 20:37] [ name withheld ]
This is NOT the same sequence of queries:
mysql> delete from b2.y;
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into b2.y values(1, 2, 3, '2003-09-23', 'a');
Query OK, 1 row affected (0.00 sec)
 
mysql> delete from b2.y;
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into b2.y select * from b1.x;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
as what I sent you. PLEASE compare. I don't know why you can't run the SQL 
as I gave you or at least modified as I suggested.

REMEMBER: I said, "You will find that it runs fine the first time through but fails the
second time with the indicated error message. YES, the table is
corrupted and this is part of the symptom."

But I will do as you've asked with "Please provide full repeatable test case including Table striucture as
taken with SHOW CREATE TABLE, some Data in there (INSERT statements)
"
Ken
[7 Oct 2003 8:21] [ name withheld ]
ShowCreateTable text output of running this command against Absences table.

Attachment: ShowCreateTable.txt (text/plain), 2.50 KiB.

[7 Oct 2003 8:35] [ name withheld ]
The three files of Absences for testing with data.

Attachment: Tables.exe (application/octet-stream, text), 30.61 KiB.

[7 Oct 2003 8:38] [ name withheld ]
To reproduce the bug use this SQL with testdata.absences and sourcedata.absences both having no records:
DELETE FROM testdata.absences;
INSERT INTO testdata.absences SELECT * FROM sourcedata.absences;
INSERT INTO testdata.absences VALUES
(101,302,'2003/09/23','1',201,0,0,505);

DELETE FROM testdata.absences;
INSERT INTO testdata.absences SELECT * FROM sourcedata.absences;
INSERT INTO testdata.absences VALUES
(101,302,'2003/09/23','1',201,0,0,505);

The SHOW CREATE TABLE sourcedata.absences output is:
| absences | CREATE TABLE `absences` (
  `AbsencesID` int(8) NOT NULL default '0',
  `StuID` int(8) NOT NULL default '0',
  `AbsenceDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `ClassID` int(8) NOT NULL default '0',
  `AbsenceType` char(1) NOT NULL default '',
  `AbsenceExcuseID` int(8) NOT NULL default '0',
  `MinutesLate` int(8) NOT NULL default '0',
  `TempAbsence` smallint(1) NOT NULL default '0',
  `ProcessedByEmpID` int(8) default '0',
  PRIMARY KEY  (`AbsencesID`),
  KEY `StudentId` (`StuID`),
  KEY `AbsenceExc` (`AbsenceExcuseID`),
  KEY `FK_Classes` (`ClassID`)

As I said before, the first 3 lines of SQL will run fine but the second three lines will produce the error: "1034. Couldn't fix table with quick recovery: Found wrong number of deleted records."

I've attached a file of the SHOW CREATE TABLE output in case you need it.
Also, if you need them, the table files are attached in Table.zip.
Disregard the MySQL_Bug.zip file.

Ken
[13 Oct 2003 4:25] Alexander Keremidarski
I would ask you to upgrade to 4.0.15 as we fixed couple of table corrupting bugs in it. Chance is that you have hit one of them.

There is also chance that problem is Window specific.

Please confirm if you are doing file copy (I do an operating system copy) with or without running mysqld.

If mysqld is running during copy this can explain all corruption problems.

I tried your sequence of queries several times with different data, but no corruption happened. 
Yes you are correct I sent comment with different order of queries, but I tested it with different order of INSERT and DELETE too. Both as you describe it and as I sent in my comment works fine for me without causing any corruption.

Please also confirm of you are running mysqld with myisam-recover option.
[13 Oct 2003 4:38] Alexander Keremidarski
Here is one more test this time with your table structure and random data. Only database names are different.

mysql> DELETE FROM b2.absences;
Query OK, 501 rows affected (0.01 sec)
 
mysql> INSERT INTO b2.absences SELECT * FROM b1.absences;
Query OK, 500 rows affected (0.01 sec)
Records: 500  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO b2.absences VALUES (101,302,'2003/09/23',2001,'1',201,0,0,505);
Query OK, 1 row affected (0.00 sec)
 
mysql> CHECK TABLE b2.absences;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| b2.absences | check | status   | OK       |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)

And one more time with both tables empty as you said in your last comment:

mysql> DELETE FROM b1.absences;
Query OK, 500 rows affected (0.00 sec)
 
mysql> DELETE FROM b2.absences;
Query OK, 501 rows affected (0.00 sec)

mysql> INSERT INTO  b2.absences VALUES (101,302,'2003/09/23',2001,'1',201,0,0,505);
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM b2.absences;
Query OK, 1 row affected (0.03 sec)
 
mysql> INSERT INTO b2.absences SELECT * FROM b1.absences;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO  b2.absences VALUES (101,302,'2003/09/23',2001,'1',201,0,0,505);
Query OK, 1 row affected (0.00 sec)
 
mysql> CHECK TABLE b2.absences;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| b2.absences | check | status   | OK       |
+-------------+-------+----------+----------+
[13 Oct 2003 7:00] [ name withheld ]
The MySqlD is setup with the installed defaults and if that supports the Recover option then yes.
I'll try the 4.0.15 version but it looks like this is a Windows specific problem.

I use PremiumSoft's Navicat to copy from the good database to the test database that is experiencing the corruption. I am sure they do the copy procedure properly.
[15 Oct 2003 8:05] [ name withheld ]
I ran the script that generates the error against your 4.0.15
version and it ran better in the sense that the error message,
#1034, occured once but running the script again afterwards, produced no problem. 
Ken
[15 Oct 2003 12:54] Alexander Keremidarski
This is still somethng which should never happen.

To check if myisam-recover option is on or off use:

mysql> show variables like "myisam_recover%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| myisam_recover_options | OFF   |
+------------------------+-------+

OFF is default.
[15 Oct 2003 23:11] Miguel Solorzano
Thank you for the bug report, this issue was already fixed with the
  ChangeSet:
  1.1573 03/10/15 21:41:13 monty@mashka.mysql.fi +10 -0
  Fixed chsize() problem on windows

Additional comments in sake of elucidation:

1- You are right: it always was a windows server issue.
2- The first sequence of commands at the bottom of this comment were performed
   against a 4.0.15 server which  showed the error messages for you reported
3- The second sequence of commands were performed against a server 4.0.15
   patched with the above changeset mentioned and showed that the issue is
   already fixed. I did them  more than 10 times (the server bugged with the
   second sequence had showed the bug).
4- If you are curious about the reason why, please read the bug:
   http://bugs.mysql.com/bug.php?id=1397 
   which also will be changed its status to closed (in few seconds) for
   obvious reason.

e:\servers\scripts>e:/servers/mysql-4.0.15/bin/mysql -uroot --port=3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELETE FROM testdata.absences;
Query OK, 10 rows affected (0.01 sec)

mysql> INSERT INTO testdata.absences SELECT *
    -> FROM sourcedata.absences;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> INSERT INTO testdata.absences VALUES
    -> (101,302,'2003/09/23',2001,'1',201,0,0,505);
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM testdata.absences;
Query OK, 11 rows affected (0.01 sec)

mysql> INSERT INTO testdata.absences SELECT *
    -> FROM sourcedata.absences;
ERROR 1034: Key 2 - Found too many records; Can't continue
mysql> INSERT INTO testdata.absences VALUES
    -> (101,302,'2003/09/23',2001,'1',201,0,0,505);
Query OK, 1 row affected (0.01 sec)

----------------------------------------------------------------------
e:\servers\scripts>e:/servers/mysql-4.0.15/bin/mysql -uroot --port=3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELETE FROM testdata.absences;
Query OK, 10 rows affected (0.04 sec)

mysql> INSERT INTO testdata.absences SELECT *
    -> FROM sourcedata.absences;
Query OK, 10 rows affected (0.02 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> INSERT INTO testdata.absences VALUES
    -> (101,302,'2003/09/23',2001,'1',201,0,0,505);
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM testdata.absences;
Query OK, 11 rows affected (0.00 sec)

mysql> INSERT INTO testdata.absences SELECT *
    -> FROM sourcedata.absences;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> INSERT INTO testdata.absences VALUES
    -> (101,302,'2003/09/23',2001,'1',201,0,0,505);
Query OK, 1 row affected (0.01 sec)

--------------------------------------------------------------------