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

[22 Jan 2007 18:54] Harel Ben-Attia
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;
[23 Jan 2007 1:06] Miguel Solorzano
Thank you for the bug report. Please inform the result with version 5.0.27.
Thanks in advance.
[23 Jan 2007 9:37] Harel Ben-Attia
Tested under 5.0.27 as well. Behavior is the same.

RL
[25 Jan 2007 14:30] Sveta Smirnova
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.
[25 Jan 2007 17:18] Harel Ben-Attia
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 ?
[25 Jan 2007 17:51] Sveta Smirnova
Hi Harel,

thank you for update.

It was misprint.

Correct description:

Verified as described on Mac and flash device using current BK sources.
[17 Apr 2007 22:28] Bugs System
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.
[18 Apr 2007 17:22] Bugs System
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.
[19 Apr 2007 17:41] Bugs System
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
[19 Apr 2007 18:00] Damien Katz
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.
[30 Apr 2007 21:54] Damien Katz
Patch pushed to 50 and 5.1 -maint
[1 May 2007 20:58] Bugs System
Pushed into 5.1.18-beta
[1 May 2007 20:59] Bugs System
Pushed into 5.0.42
[2 May 2007 19:01] Paul Dubois
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.