Bug #5518 MyISAM table becomes corrupted on system quota exceeding
Submitted: 10 Sep 2004 16:20 Modified: 14 Dec 2004 17:44
Reporter: Serega Romanovsky Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:3.23.58-1.73 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[10 Sep 2004 16:20] Serega Romanovsky
Description:
Database files owned by user which have non-zero system quota.
Quota is almost over. User inserts a row and got error from MySQL "Got error 122 from table handler". That's ok, it's let user know that he should buy more disk space, but the table becomes corrupted at this moment:
mysql>  check table t;
+----------+-------+----------+-----------------------------------------------------+
| Table    | Op    | Msg_type | Msg_text                                            |
+----------+-------+----------+-----------------------------------------------------+
| serega.t | check | warning  | Table is marked as crashed                          |
| serega.t | check | warning  | Size of datafile is: 970752       Should be: 970580 |
| serega.t | check | error    | Found 3734 keys of 3733                             |
| serega.t | check | error    | Corrupt                                             |
+----------+-------+----------+-----------------------------------------------------+
4 rows in set (0.00 sec)

How to repeat:
1. set system quota (for example to 1MB)  for db files owner:
# setquota -a -u username 1024 1024 1024 1024

2. create a table in userName's database:
mysql> create table t (i int not null auto_increment primary key, c char(255) default '');

3. Insert data that will overrun the quota by any method you like:
$ perl -e 'use DBI;
my $dbh =DBI->connect("dbi:mysql:database=databaseName;host=hostName","userName","userName");
for (my $i=0; $i<10000; $i++) {
        $dbh->do("INSERT INTO t VALUES()")
                or die "$DBI::errstr";
}'
DBD::mysql::db do failed: Got error 122 from table handler at -e line 4.
Got error 122 from table handler at -e line 4.

4. check table status
mysql>  check table t;
+----------+-------+----------+-----------------------------------------------------+
| Table    | Op    | Msg_type | Msg_text                                            |
+----------+-------+----------+-----------------------------------------------------+
| serega.t | check | warning  | Table is marked as crashed                          |
| serega.t | check | warning  | Size of datafile is: 970752       Should be: 970580 |
| serega.t | check | error    | Found 3734 keys of 3733                             |
| serega.t | check | error    | Corrupt                                             |
+----------+-------+----------+-----------------------------------------------------+
4 rows in set (0.00 sec)
[28 Sep 2004 15:18] Matthew Lord
Hi,

This is entirely possible with MyISAM tables until we get rollback support for myisam tables.  This 
is something that is currently being worked on.

repair table should remove the partially written row.

Thanks for the bug report!
[28 Sep 2004 16:32] Sergei Golubchik
it shouldn't be "entirely possible", as far as I understand, MyISAM should issue a warning and wait for free disk space, not to corrupt the table.
[4 Oct 2004 23:14] Matthew Lord
I can't get the quota system to work on my machine.  Hopefully someone else will have better 
luck.
[27 Oct 2004 8:47] Victoria Reznichenko
I wasn't able to repeat table corruption. MySQL gives a warning and wait for free space for me.
[3 Dec 2004 14:25] Serega Romanovsky
2Victoria Reznichenko:
Hmm,
could you please describe your test environment (version of kernel, quota db format, mysql version, linux distributive version)? I able to reproduce the problem on 3 boxes with mysql 3.23 installed. Boxes are running under RedHat Linux 7.3 and Fedora 2. I still think that the source of the problem is mysql. I'll be very grateful to you if you tell me to which version of MySQL I should migrate to get rid of this problem.
[14 Dec 2004 17:44] Victoria Reznichenko
We've made more tests with 3.23.58 and 4.0.23 versions and we were able to reproduce table corruption with 3.23.
It's fixed in 4.0.22:

Fixed that if a write to a MyISAM table fails because of a full disk or an exceeded disk quota, it prints a message to the error log every 10 minutes, and waits until disk becomes free.