MySQL Bugs: #25761: Table is partially created when disk is full, causing database corruption

Bug #25761	Table is partially created when disk is full, causing database corruption
Submitted:	22 Jan 2007 18:54	Modified:	2 May 2007 19:01
Reporter:	Harel Ben-Attia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.27	OS:	Linux (Linux (glibc23))
Assigned to:	Damien Katz	CPU Architecture:	Any

Description:

Problem Description:
When the disk is full and the command CREATE TABLE X LIKE Y is executed, an X.frm file with zero size is created (.MYD and .MYI are not created). From that point, trying to create the table again results in "table X already exists" (error 1050), and trying to delete the table results in an "unexpected eof found when reading file X.frm" (error 9). 

Executing SHOW TABLE STATUS displays table X, but all the columns except the Name are NULL and an "Incorrect information in file X.frm" appears. CHECK TABLE X results in Msg_Type=error with the same message text. Running REPAIR TABLE X does not succeed in reparing table.

Important Note: When performing CREATE TABLE X with a full disk (without the LIKE Y part), the problem does not happen (the failure is complete - no .frm file is created).

Expected behavior:
When the disk is full, the command should either fail completely or succeed, leavning the database in a consistent state.

I'm currently downloading 5.0.27, in order to see if it happens in that version as well. I'll update on this when I have results.

Thanks a lot
RL

How to repeat:

   CREATE DATABASE D;
   USE D
   CREATE TABLE AAA (id INT,data VARCHAR(100));

Fill up the filesystem that the DB data resides on (dd if=/dev/zero of=filler count=<a big number>). ***************Be careful not to do this on a filesystem that can crash your machine if it's full****************

   CREATE TABLE BBB LIKE AAA;

U'll see that in the data/D/ folder, the only file for BBB is BBB.frm. From that moment, performing any of the following will fail:

   CREATE TABLE BBB LIKE AAA;
   DROP TABLE BBB;

Thank you for the bug report. Please inform the result with version 5.0.27.
Thanks in advance.

Tested under 5.0.27 as well. Behavior is the same.

RL

I can not repeat it on Mac using current BK sources:

mysql> CREATE TABLE BBB LIKE AAA;
ERROR 1004 (HY000): Can't create file './d/bbb.frm' (errno: 28)
mysql> \q
Bye

apple@apple ~
$./Applications/mysql-5.0/bin/perror 28
OS error code  28:  No space left on device

$ls -la /Volumes/NO\ NAME/data/D
total 4032
drwxrwxrwx   1 apple  apple      32K Jan 25 17:20 .
drwxrwxrwx   1 apple  apple      32K Jan 25 17:27 ..
-rwxrwxrwx   1 apple  apple       1M Jan 25 17:20 AAA.MYD
-rwxrwxrwx   1 apple  apple       1K Jan 25 17:25 AAA.MYI
-rwxrwxrwx   1 apple  apple       8K Jan 25 17:12 AAA.frm
-rwxrwxrwx   1 apple  apple       0B Jan 25 17:20 bbb.frm
-rwxrwxrwx   1 apple  apple      61B Jan 25 17:12 db.opt

To repeat: use limited partition or simple locate datadir on the flash device.

Hi Sveta, 

Thanks for the effort.

i didn't understand why u wrote "i can not repeat". In the "ls" output u sent the problem is reproduced - the bbb.frm file is created with a zero length. Did u try to recreate/drop the table afterwards ?

Hi Harel,

thank you for update.

It was misprint.

Correct description:

Verified as described on Mac and flash device using current BK sources.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24728

ChangeSet@1.2459, 2007-04-17 18:28:11-04:00, dkatz@damien-katzs-computer.local +1 -0
  Bug #25761  	Table is partially created when disk is full, causing database corruption
  
  Potential fix, exploring ways to test it.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24815

ChangeSet@1.2459, 2007-04-18 13:22:23-04:00, dkatz@damien-katzs-computer.local +1 -0
  Bug #25761  	Table is partially created when disk is full, causing database corruption
  
  Changed to my_copy to delete the destination file if the copy does not complete.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24916

ChangeSet@1.2462, 2007-04-19 13:41:12-04:00, dkatz@damien-katzs-computer.local +1 -0
  Bug #25761  	Table is partially created when disk is full, causing database corruption

The fix for this bug is simple but the bug is hard to reproduce.

Since this bug doesn't really cause database corruption (existing data is unharmed and the spurious .frm files can be deleted via the file system by an admin) and the bug is hard to reproduce without internal modifications to the code (via DBUG_EXECUTE_IF), I don't think explicit test cases should be added. Even if there is a regression in this behaviour, it would not corrupt data and can be corrected by an admin.

Additionally, there are more ways spurious .frm files can be created. Until mysql implements a centralized table definition store, I would consider spurious .frm files to be an inherent design flaw of the current system, however using simple OS level tools the problem is solvable by an admin, which is also feature of the current system.

If not already, this issue should be documented in the admin manual and/or a knowledge-base article.

Patch pushed to 50 and 5.1 -maint

Pushed into 5.1.18-beta

Pushed into 5.0.42

Noted in 5.0.42, 5.1.18 changelogs.

If CREATE TABLE t1 LIKE t2 failed due to a full disk, an empty t2.frm
file could be created but not removed. This file then caused
subsequent attempts to create a table named t2 to fail. This is
easily corrected at the filesystem level by removing the t2.frm 
file manually, but now the server removes the file if the create
operation does not complete successfully.