Bloated innodb datafile (ibdata1) and how to reclaim space?

Question

I have about 12GB of data in my tables and my datafile, ibdata1, used to be about 12GBs in size. I then ran the following command

alter table `rails_production`.`pictures` change `data` `image_file_data` mediumblob NULL

While it was making a temporary copy of the table, I got the following error

ERROR 1114 (HY000): The table '#sql-7fe4_12c9' is full

I assume this means there wasn't enough space to make a temporary copy of the table. But now the datafile is 17GB! How do I reclaim the space in the datafile?

Is there a way to check how much of that 17GB is being used?

Answer 1

Unfortunately you can't reclaim space from an innodb database if you are not using innodb_file_per_table in your configuration:

http://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table