MySQL Bugs: #1321: Table gets corrupted when executing a DELETE FROM table

Bug #1321	Table gets corrupted when executing a DELETE FROM table
Submitted:	17 Sep 2003 7:58	Modified:	18 Oct 2003 15:36
Reporter:	Mark Matthews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.013 or newer	OS:	Windows (Windows)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
>>Table gets corrupted when executing a DELETE FROM table. Tested on MyISAM
>>> > tables, don't know other types.
>>> > This behaviour doesn't shows in version 4.0.12 (Same machine, just
>>> > uninstalling 4.0.14 and installing 4.0.12)

I was able to repeat this on a fresh install of 4.0.15 on Windows 2k3, by following the above instructions (see How to repeat)

How-To-Repeat:
> > 1) Fresh install on Win2K Server (same thing on Win 2003 Server)
> > 2) Create database
> > 3) Create table MyISAM (Imported from other server in my case)
> > 4) Run check table on that table -> OK
> > 5) Execute a DELETE FROM table
> > 6) Run check table on that table -> Corrupt
> > 7) Run repair table on that table -> OK

How to repeat:
C:\Documents and Settings\Administrator>c:\data\mysql-4.0.15-gpl\bin\mysql test
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> create table deleteFrom(field1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into deleteFrom values (1);
Query OK, 1 row affected (0.00 sec)

mysql> check table deleteFrom;
+-----------------+-------+----------+----------+
| Table           | Op    | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| test.deletefrom | check | status   | OK       |
+-----------------+-------+----------+----------+
1 row in set (0.01 sec)

mysql> delete from deleteFrom;
Query OK, 1 row affected (0.00 sec)

mysql> check table deleteFrom;
+-----------------+-------+----------+------------------------------------------
-------+
| Table           | Op    | Msg_type | Msg_text
       |
+-----------------+-------+----------+------------------------------------------
-------+
| test.deleteFrom | check | warning  | Size of datafile is: 5       Should be: 0
       |
| test.deleteFrom | check | error    | Record-count is not ok; is 1   Should be:
 0     |
| test.deleteFrom | check | warning  | Found 1 parts                Should be: 0
 parts |
| test.deletefrom | check | error    | Corrupt
       |
+-----------------+-------+----------+------------------------------------------
-------+
4 rows in set (0.00 sec)

mysql>

Mark,

I have experienced the same using 4.0.13 (Recompiled from Source).

I have performed a count-of-records on a table of 10000 records; deleted the last 100 records in that table; counted the records and still got 10000 count-of-records.

   A quick, but inefficient work-around is to execute "FLUSH TABLES;" right after deleting any records from a table. The statistics will indicate that the table is ok and this problem will go away.

The better solution would be to "FLUSH TABLES ATableName;" but this does not reset the 'KEY STATISTICS'.  You can also use "SHOW STATUS" afterwards and get the results you are reporting.  In the mean time, I will have to use "FLUSH TABLES;" after deleting any records from any table.  I don't know if this is by design but if you have multiple databases with open tables and you do a "FLUSH TABLES" from any one of those databases ALL TABLES IN ALL DATABASES are FLUSHed.

I hope that the MySQL Team will address this "FLUSH" issue at the same time as addressing your noted problem because I think it relates to the problem you noted.

Farley

I wasn't able to repeat: 

Microsoft Windows 2000 [Versão 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

c:\>cd\mysql\bin

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.15-max-nt

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

mysql> create table deleteFrom(field1 int);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into deleteFrom values (1);
Query OK, 1 row affected (0.05 sec)

mysql> check table deleteFrom;
+-----------------+-------+----------+----------+
| Table           | Op    | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| test.deleteFrom | check | status   | OK       |
+-----------------+-------+----------+----------+
1 row in set (0.01 sec)

mysql> delete from deleteFrom;
Query OK, 1 row affected (0.05 sec)

mysql> check table deleteFrom\G
*************************** 1. row ***************************
   Table: test.deleteFrom
      Op: check
Msg_type: status
Msg_text: OK
1 row in set (0.00 sec)

mysql>

Miguel,

I noticed you tested this with 4.0.15.  A fix was noted in the change-log for 4.0.15 that may have corrected this issue.

<Fixed rare bug in MYISAM introduced in 4.0.3 where the index file header was not updated directly after an UPDATE of split dynamic rows. The symptom was that the table had a corrupted delete-link if mysqld was shut down or the table was checked directly after the update.>

I will make further tests with 4.0.13, 4.0.15 and I will post my findings.

Thanks,

Farley

I actually tested it with a _fresh_ install of 4.0.15 on Win2k3 (where the problem was repeatable). I'll try it later today on a different computer with XP.

HELLO ALL,

	I have pinpointed the source of my DELETE-AND-FLUSH problem.

I periodically connect an external USB-DISK-DRIVE to my computer to off-load large files.  Having this drive connected to my Win2kSP3-Server while creating tables on an internal permanent-disk-drive has somehow interfered with table-creations.

	Earlier today, I DUMPed the entire database to a TEXT-File and IMPORTed it to another computer.  It too was a Win2kSP3-Server.  Everything worked great.  COUNT the records (10000).  DELETEd records (100).  COUNTed the records without doing a FLUSH (9900).  CHECK TABLE (OK).  In short, I was unable to reproduce the problem that I had mentioned yesterday.

	Later today, I DUMPed the entire database to a TEXT-File and IMPORTed it to the computer with a USB-DISK-DRIVE attached.  COUNT the records (10000).  DELETEd records (100).  COUNT the records without doing a FLUSH (10000).  CHECK TABLE (Marked as Corrupted).  I then disconnected the external USB-DISK-DRIVE and ReStarted the Win2kSP3-Server.  I performed the same steps and I am now unable to reproduce this problem.

	It is obvious that a strange-corruption of one or more of the three table files is induced when a USB-DISK-DRIVE is connected to the computer used to create MYISAM-TABLES even though that drive is not utilized during the creation of the MYISAM-TABLES.	

	If the MYISAM-TABLE files are created with this issue and the three files (FRM, MYD, MYI) that make up a TABLE-STRUCTURE are copied to another computer then the problem will continue to exist on that computer also.  Doing all of the MYIAMCHK repairs (-f –r AND –n –a –S) had NO-CORRECTIVE-EFFECT !!!  The solution to eliminate this problem was to DUMP the TABLE and its DATA to a TEXT-File then DROP the TABLE then IMPORT the TABLE-STRUCTURE and DATA letting MYSQL recreate the TABLE on a computer WITHOUT HAVING A USB-DISK-DRIVE ATTACHED.

	In all of my tests I was using a Re-Compiled-Source-of-MySQL-4.0.13 and the table structure use was as follows:

CREATE TABLE STORESTATS (
  Mkt char(3) NOT NULL default '',
  Store smallint(6) NOT NULL default '0',
  Date date NOT NULL default '0000-00-00',
  NewCusts smallint(6) default NULL,
  EFTs smallint(6) default NULL,
  Revenue double(16,2) default NULL,
  Tans smallint(6) default NULL,
  PRIMARY KEY  (Mkt,Store,Date)
) TYPE=MyISAM MAX_ROWS=400000000 AVG_ROW_LENGTH=23;

I hope this to be useful information,

Farley

I am seeing this bug under the following circumstances:

- WinXP on a Dell Dimension 2350 w/Celeron 2GHz 256MB, 30GB HDD
- MySQL 4.0.15

Steps to reproduce:

1. Set up a table in MySQL.
2. Load data using mysqlimport.
3. Issue the command DELETE FROM <table>
4. Observe disk - data files have *not* been altered!
5. Issue the command CHECK TABLE <table> - reports corruption.
6. Issue the command REPAIR TABLE <table> - table goes back to the way it was before you issued DELETE command!

Workaround: Issue the command DELETE FROM <TABLE> WHERE <field> LIKE '%' instead.

Any chance this could get looked at?

Thanks

Andrew.

I have found this bug in my system:

Celeron 2.0GHz, 512MbRAM, Windows XP SP1
MySQL 4.0.15

delete from table               ---- do not work
delete from table where xxx=zzz ---- worked properly

I hope that the bug will be fixed soon.....

Best regards from Ukraine :)

It looks like the bug Miguel was fixing recently

P.S.

query:
delete from table

error:
Can't change size of file (Errcode: 2)

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

So did this make it into 4.0.16?

So did this make it into 4.0.16?

Yes.