Bug #31305 myisam tables crash when they are near capacity
Submitted: 30 Sep 2007 16:08 Modified: 26 Nov 2007 19:00
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:4.0.26, 4.1.21, 5.1.23, 5.0.48, 6.0.4 OS:Any
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: bfsm_2007_10_18, corruption, myisam

[30 Sep 2007 16:08] Shane Bester
Description:
when myisam tables are full, inserts fail with error message:

ERROR 1114 (HY000): The table 'part1' is full

But subsequent deletes and inserts can cause corruption.  output from the testcase:

mysql> INSERT IGNORE INTO `part1` SET `a`=64,`b`=repeat('a',abs(197));
ERROR 1114 (HY000): The table 'part1' is full
mysql> delete from part1 order by `a` limit 4;
Query OK, 4 rows affected (0.00 sec)

mysql> check table part1 extended;
+------------+-------+----------+----------------------------------------------+
| Table      | Op    | Msg_type | Msg_text                                     |
+------------+-------+----------+----------------------------------------------+
| test.part1 | check | warning  | Datafile is almost full, 65520 of 65534 used |
| test.part1 | check | status   | OK                                           |
+------------+-------+----------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> INSERT IGNORE INTO `part1` SET `a`=71,`b`=repeat('a',abs(197));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT IGNORE INTO `part1` SET `a`=-29,`b`=repeat('a',abs(197));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO `part1` SET `a`=-113,`b`=repeat('a',abs(197));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO `part1` SET `a`=-2,`b`=repeat('a',abs(197));
ERROR 1114 (HY000): The table 'part1' is full
mysql> INSERT IGNORE INTO `part1` SET `a`=124,`b`=repeat('a',abs(197));
ERROR 1114 (HY000): The table 'part1' is full
mysql> delete from part1 order by `a` limit 4;
Query OK, 4 rows affected (0.00 sec)

mysql> check table part1 extended;
+------------+-------+----------+-----------------------------------------------------+
| Table      | Op    | Msg_type | Msg_text                                            |
+------------+-------+----------+-----------------------------------------------------+
| test.part1 | check | warning  | Datafile is almost full, 65520 of 65534 used        |
| test.part1 | check | error    | got error: 127 when reading datafile at record: 253 |
| test.part1 | check | error    | Corrupt                                             |
+------------+-------+----------+-----------------------------------------------------+
3 rows in set (0.00 sec)

How to repeat:
see attached sql script. run at commandline client.

Suggested fix:
test the edge cases more carefully!
[30 Sep 2007 16:11] MySQL Verification Team
paste into client. 5.0.45 and 5.1.23 cause corruptions.

Attachment: bug31305_testcase.sql (application/octet-stream, text), 118.25 KiB.

[30 Sep 2007 17:44] MySQL Verification Team
All versions of mysql are affected by this bug.  I even tried 4.0 with a 4GB table, same corruptions happened.

mysql> check table part1;
+------------+-------+----------+--------------------------------------------------------+
| Table      | Op    | Msg_type | Msg_text                                               |
+------------+-------+----------+--------------------------------------------------------+
| test.part1 | check | warning  | Table is marked as crashed                             |
| test.part1 | check | warning  | Datafile is almost full, 4294967284 of 4294967294 used |
| test.part1 | check | error    | got error: 127 when reading datafile at record: 0      |
| test.part1 | check | error    | Corrupt                                                |
+------------+-------+----------+--------------------------------------------------------+
4 rows in set (0.00 sec)
[29 Oct 2007 8:30] 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/36523

ChangeSet@1.2688, 2007-10-29 11:25:45+04:00, holyfoot@mysql.com +1 -0
  Bug #31305 myisam tables crash when they are near capacity.
  
  When we insert a record into MYISAM table which is almost 'full',
  we first write record data in the free space inside a file, and then
  check if we have enough space after the end of the file.
  So if we don't have the space, table will left corrupted.
  Similar error also happens when we updata MYISAM tables.
  
  Fixed by modifying write_dynamic_record and update_dynamic_record functions
  to check for free space before writing parts of a record
[10 Nov 2007 19:16] 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/37523

ChangeSet@1.2688, 2007-11-10 23:11:37+04:00, holyfoot@mysql.com +4 -0
  Bug #31305 myisam tables crash when they are near capacity.
  
  When we insert a record into MYISAM table which is almost 'full',
  we first write record data in the free space inside a file, and then
  check if we have enough space after the end of the file.
  So if we don't have the space, table will left corrupted.
  Similar error also happens when we updata MYISAM tables.
  
  Fixed by modifying write_dynamic_record and update_dynamic_record functions
  to check for free space before writing parts of a record
[12 Nov 2007 9:05] 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/37563

ChangeSet@1.2688, 2007-11-12 13:00:22+04:00, holyfoot@mysql.com +4 -0
  Bug #31305 myisam tables crash when they are near capacity.
  
  When we insert a record into MYISAM table which is almost 'full',
  we first write record data in the free space inside a file, and then
  check if we have enough space after the end of the file.
  So if we don't have the space, table will left corrupted.
  Similar error also happens when we updata MYISAM tables.
  
  Fixed by modifying write_dynamic_record and update_dynamic_record functions
  to check for free space before writing parts of a record
[16 Nov 2007 9:28] Bugs System
Pushed into 4.1.24
[16 Nov 2007 9:31] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:36] Bugs System
Pushed into 6.0.4-alpha
[26 Nov 2007 19:00] Paul DuBois
Noted in 4.1.24, 5.0.52, 5.1.23, 6.0.4 changelogs.

For an almost-full MyISAM table, an insert that failed could leave
the table in a corrupt state.