One more time, I am having trouble with the MySQL database I administer.
Look, this MySQL database was inherited. It contains ~30 databases, most of them MyISAM type. One of them, the biggest, with more than 30GB of data (150 millions registers). The configuration for MySQL especified a first innodb tablespace file of 20GB and a second file of 10MB, with autoextend. From my.cnf:

innodb_data_home_dir = /data/mysql/data
innodb_data_file_path = ibdata1:20000M;ibdata2:10M:autoextend

I was in the migration from MyISAM to InnoDB, in order to have transaction support. But, as I noted after starting the migration of tables, the innodb files used for storing the mysql tables NEVER shrinks. Never, even if you delete tables, drop tables or drop databases. Always grows but never returns unused space to the operating system. Unbelievable.
According to the bug tracking system for mysql (bugs 1341, 1287) this is a design decision to improve performance: if the space were disposed each time a delete is peformed, it would need to be allocated again when more data were written to the database. So it is only marked as deleted but not returned to the operating system. That is reasonable. In fact it is a good point. But there are times when you just need to free the innodb unused space and IT IS NOT possible, in an automatic or, at least, in a manual way.
Why would you need this. Supose, for example, that I have a 100 GB disk. It is used only for mysql. There is only a database of 40GB in it. So I have ~60GB of space free. One day I need to restore an old backup for this database. I create a new database and restore the old backup inside this database. If I have MySQL configured as all Innodb, the new database would be created in the innodb tablespace, so MySQL will grow the tablespace to say 80GB so the restored backup can be loaded. At this moment, I have 80GB used by the tablespace and only 20GB of free space in the disk. After extracting the data from the backup, I delete the restored database. One would think that the 40GB would be returned to the OS, but NO! The table space size is 80GB although only 40GB are really used, in fact, SHOW TABLE STATUS would show that it is only 50% used. If my original database doesn’t grows to fill the 80GB used by the innodb tablespace or grows very slowly I have actually lost 40GB from my disk and I can not do anything easy to recover that space. In other words, I have lost that space.
Of course there are workarounds for this problem, but they involve dumping and restoring the databases and more important, YOU MUST shutdown the database. That is not always posible. Maybe in a weekend or in the night, but that can be too late if you are running out of disk space.
It is needed a command to force MySQL to free unused space, under the responsability of the DB administrator. At this time, there isn’t any.
So the only way to add more free space to my database was by means of the operating system. My filesystem was like this:

[root@usp02 root]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/cciss/c0d0p3      64G   36G   26G  59% /
/dev/cciss/c0d0p2      99M   11M   83M  12% /boot
/dev/cciss/c0d1p1      67G   62G  2.4G  97% /data
none                  2.9G     0  2.9G   0% /dev/shm

and there were still 3 tables in the migration process. I was feeling real panic. Besides, it is Monday morning. The only feasible moment for shutdown is the next weekend or, in case of extreme urgency, until tonight.
Finally, after several hours searching for a solution for the problem in MySQL, I decided to search ways to free space in the filesystem of the operating system. Happily I found a way to reduce the space reserved for root in a ext2 filesystem from the default 5% of the disk size to 1%. The instructions are here. And I don’t even have to remount the filesystem. The change was applied inmediately.
I just typed:

[root@usp02 root]# tune2fs -m 1 /dev/cciss/c0d1p1
tune2fs 1.32 (09-Nov-2002)
Setting reserved blocks percentage to 1 (177816 blocks)
[root@usp02 root]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/cciss/c0d0p3      64G   36G   26G  59% /
/dev/cciss/c0d0p2      99M   11M   83M  12% /boot
/dev/cciss/c0d1p1      67G   62G  5.1G  93% /data
none                  2.9G     0  2.9G   0% /dev/shm

So I could get 2.7GB that hopefully will last until I can shutdown MySQL, remove the autoextend from the second innodb file and add a third tablespace in another filesystem with enought free space.

One more thing I don’t like about MySQL.

Uff.