OPTIMIZE TABLE filled my disk, can't find a way to recover the lost disk space

Hello,

I did this with a database :

mysql> OPTIMIZE LOCAL TABLE a_lien; OPTIMIZE LOCAL TABLE a_analyse;

+-----------+----------+----------+--------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+----------+----------+--------------------------------------+
| RM.a_lien | optimize | error | The table '#sql-10ef_94788c' is full |
| RM.a_lien | optimize | status | Operation failed |
+-----------+----------+----------+--------------------------------------+
2 rows in set, 1 warning (3 hours 30 min 47.67 sec)

+--------------+----------+----------+--------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+--------------------------------------------------------------+
| RM.a_analyse | optimize | error | Error writing file './RM/#sql-10ef_94788c.frm' (Errcode: 28) |
| RM.a_analyse | optimize | status | Operation failed |
+--------------+----------+----------+--------------------------------------------------------------+
2 rows in set, 1 warning (0.03 sec)

Before this operation, I had 20 GB of free disk space (out of 273 GB), now my disk is full. I restarted mysqld, thinking that it would delete temporary files, but it didn't happen, and I was not able to find such files.

mysql> show variables like "tmpdir";
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| tmpdir | /var/tmp |
+---------------+----------+
1 row in set (0.00 sec)

But /var/tmp is empty.

sh-3.1# ls -lh /var/lib/mysql
total 229G
drwx------ 2 mysql mysql 4.0K 2008-05-15 17:47 vrep
drwx------ 2 mysql mysql 4.0K 2009-02-27 15:10 blogfr
drwx------ 2 mysql mysql 4.0K 2007-09-05 17:38 blogus
-rw-r--r-- 1 root root 0 2006-09-13 17:09 debian-5.0.flag
drwx------ 2 mysql mysql 4.0K 2009-04-20 21:33 formu
-rw-rw---- 1 mysql mysql 229G 2009-05-28 21:05 ibdata1
-rw-rw---- 1 mysql mysql 5.0M 2009-05-28 21:05 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M 2009-05-28 00:47 ib_logfile1
drwx------ 2 mysql mysql 4.0K 2008-12-16 09:03 insc2
drwxr-xr-x 2 mysql root 4.0K 2006-11-17 17:50 mysql
-rw------- 1 root root 7 2006-09-13 17:09 mysql_upgrade_info
drwx------ 2 mysql mysql 8.0K 2008-01-28 15:49 postaff
drwx------ 2 mysql mysql 8.0K 2008-12-22 16:56 postaff4
drwx------ 2 mysql mysql 4.0K 2006-09-15 13:11 prosrm
drwx------ 2 mysql mysql 4.0K 2009-03-19 13:24 prv
drwx------ 2 mysql mysql 4.0K 2008-03-28 13:44 qcm
drwx------ 2 mysql mysql 4.0K 2008-10-24 16:36 gfg
drwx------ 2 mysql mysql 4.0K 2009-04-07 17:13 sitew
drwx------ 2 mysql mysql 4.0K 2009-05-28 00:48 RM
drwx------ 2 mysql mysql 4.0K 2007-09-10 18:43 ceom

sh-3.1# du -chs /var/lib/mysql
229G /var/lib/mysql
229G total

sh-3.1# du -chs /var/lib/mysql/RM
244K /var/lib/mysql/RM
244K total

How come the 20 GB hasn't been freed after the OPTIMIZE TABLE failed ?

Regards,

Damien



Edited 1 time(s). Last edit at 05/28/2009 01:18PM by .