MySQL Bugs: #5625: MyISAM Index corruption on ALTER TABLE x ENABLE KEYS due to full tmpdir

Bug #5625	MyISAM Index corruption on ALTER TABLE x ENABLE KEYS due to full tmpdir
Submitted:	17 Sep 2004 9:38	Modified:	14 Oct 2004 18:41
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.0.20	OS:	Windows (Windows, Linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
In my testcase (tmpdir set to a very small FAT partition)
i've seen the server thread becoming idle while the client was 
still hanging on the "ALTER TABLE" statement.
Any attempt to use the table now lead to

  ERROR 1016: Can't open file: 'i.MYI'. (errno: 144)

A repair table showed:

mysql> repair table i;
+--------+--------+----------+-------------------------------------------------+
| Table  | Op     | Msg_type | Msg_text                                        |
+--------+--------+----------+-------------------------------------------------+
| test.i | repair | warning  | Number of rows changed from 3871345 to 12921869 |
| test.i | repair | status   | OK                                              |
+--------+--------+----------+-------------------------------------------------+

How to repeat:
Point tmpdir to a small partition (i used a 32MB FAT partition, if you don't want to repartition floppy A: should do as well)
Disable keys on a table, insert enough new data to be sure the index data will
exceed the free space in tmpdir, enable keys again

Suggested fix:
Add proper error handling for this problem

I cannot repeat this on Linux. I get correct messages:
ERROR 1030 at line 1: Got error 28 from table handler
(which is "No space left on device").

i didn't test this on Linux but it definetly happens on Windows

i was able to reproduce it on my box (XP SP2) the way the customer reported

Step by Step guide:

- change your my.ini configuration so that tmpdir is on a floppy drive

  [mysqld]
  ...
  tmpdir=A:/
  ...

- restart the server, make sure it uses the right configuration

  SHOW VARIABLES LIKE 'tmpdir';

- create a source table with about 10 millon rows

  CREATE i1 (i int);
   ... fill with data, about 10 million random integers- ...

   i can't attach my sample table here, it exceeds the upload limit

- create a 2nd table 

   CREATE i2 (i int, key(i));
- 
- disable keys on this table

   ALTER TABLE i2 DISABLE keys

- fill the 2nd table with data

   INSERT INTO i2 SELECT i FROM i1;- 

- now enable keys on the new table

   ALTER TABLE i2 ENABLE KEYS;

   you will hear your floppy drive working for a while,
   during this time the processlist looks like this

mysql> show processlist;
+----+------+----------------+------+---------+------+-------------------+------
----------------------+
| Id | User | Host           | db   | Command | Time | State             | Info                      |
+----+------+----------------+------+---------+------+-------------------+----------------------------+
| 18 | root | localhost:4048 | test | Query   | 28   | Repair by sorting | alter table i3 enable keys |
| 21 | root | localhost:4051 | NULL | Query   | 0    | NULL              | showprocesslist           |
+----+------+----------------+------+---------+------+-------------------+----------------------------+
2 rows in set (0.00 sec)

  after some time the floppy drive becomes silent and process list shows

mysql> show processlist;
+----+------+----------------+------+---------+------+-------+------------------+
| Id | User | Host           | db   | Command | Time | State | Info|
+----+------+----------------+------+---------+------+-------+------------------+
| 18 | root | localhost:4048 | test | Sleep   | 4    |       | NULL|
| 21 | root | localhost:4051 | NULL | Query   | 0    | NULL  | show processlist|
+----+------+----------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

  so the server pretends it has finished the job but the client still waits
  on the result of the ALTER TABLE, it finally dies with

ERROR 2013: Lost connection to MySQL server during query

  the i2.MYI file is still only 1K 

- try using the i2 table from another connection

  mysql> select * from i3;
  ERROR 1016: Can't open file: 'i3.MYI'. (errno: 144)

- do a REPAIR on table i2

mysql> repair table i3;
+---------+--------+----------+------------------------------------------------+
| Table   | Op     | Msg_type | Msg_text                                       |
+---------+--------+----------+------------------------------------------------+
| test.i3 | repair | warning  | Number of rows changed from 645225 to 12921869 |
| test.i3 | repair | status   | OK                                             |
+---------+--------+----------+------------------------------------------------+
2 rows in set (9.94 sec)

    (12921869 is the number of random integers in my i1 table)

in the step by step guide above some commands and results
refere to a table i3, this was created identically to i2 and with
the same symptoms so you can just replace i3 with i2

Using a floppy drive as tmpdir i get the same results on linux, too.

The only difference here is that no physical access to the floppy
drive ever happens. I assume this is due to the buffer cache.

I took it back after hartmut found a way, how to reproduce it on Linux.

Hartmut and I identified the following problems:
- The returncode of activate_all_index() was not correctly handled. This suppressed the reply from server to client.
- When the repair_by_sort() failed,  no normal repair() was tried. The latter succeeds in this case.
- After the (new) normal repair(), no trace of the "disk full" problem could be found. So we added a warning which shows up in the error log.
bk commit - 4.0 tree (ingo:1.2029) BUG#5625

Fixed in 4.0.22. Changeset: bk commit - 4.0 tree (ingo:1.2029) BUG#5625