Space is available inside the database because data has been moved around. Perhaps you have very high levels of page splits, or have recently deleted a large portion of data that had previously caused the data file to grow.

SQL Server does not shrink database files automatically when you've freed up space within them, because the logical assumption is that if you've used that space once, you'll use it again. Autogrow can be an expensive event and unnecessary if you've only freed up space temporarily (what were you able to do with all that free space in the meantime?). For the same reasons, you shouldn't try to temporarily reclaim space, either. Just let SQL Server use the 18 GB of available space as you add more data. If you think you will need more than 18 GB of additional space going forward (in which case, you will need to add file(s) on other disks, or move to a bigger disk).

sp_spaceused (and in turn the UI dialog you're looking at) may return more space than is possible because of synchronization issues in the metadata about your tables/indexes/files. In order to make sure it reflects accurate space, run this:

DBCC UPDATEUSAGE(0);

I have also seen scenarios where rebuilding indexes was required in order to rectify the counts/space, but I haven't seen that specific case since SQL Server 2000.

(I suspect this isn't a simple case where your database already spans multiple disks, or you surely would have mentioned that in the question.)

That all said, the fact that when you shrink the data file expands almost immediately leads me to believe that you are actually using the space, but must also be performing big deletes or updates that are freeing it up (which is when you see 18 GB free). Unfortunately it's impossible for us to see exactly why the data file is expanding and then clearing itself out - perhaps you have transactions where you are truncating / re-populating large tables, performing massive archive operations, etc.

The status of the bug is labeled as Won't fix and I can understand why in this context.

The right way would have been to run ALTER TABLE tbl_name DROP PARTITION; because the MySQL Documentation on ALTER TABLE Partition Operations says:

DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used with HASH or KEY partitions; instead, use COALESCE PARTITION (see below). Any data that was stored in the dropped partitions named in the partition_names list is discarded.

ALTER TABLE t1 DROP PARTITION p0, p1;

Once you ran ALTER TABLE tbl_name DISCARD TABLESPACE;, the data dictionary should no longer be aware of the physical presence of the orphaned .ibd files. However, since the bug does not reclaim the diskspace, perhaps the data dictionary entries for your tables is intact. Let's find out.

WARNING : You should run service mysql stop and do a physical backup of /var/lib/mysql

First do this: SHOW CREATE TABLE tbl_name\G

Then, run SELECT * FROM tbl_name WHERE (stuff from partition #x) LIMIT 10;

to test the table's usability of each partition.

Once you are confortable with the table usability, you can any of the following:

REMOVE ANY PARTITION YOU WANT

ALTER TABLE tbl_name DROP PARTITION partition-name;

If you do not trust ALTER TABLE tbl_name DROP PARTITION partition-name;, do it manually

CREATE TABLE tbl_name2 LIKE tbl_name;
ALTER TABLE tbl_name2 DROP PARTITION partition-name;
INSERT INTO tbl_name2 SELECT * FROM tbl_name WHERE (anything but stuff from partition-name)
ALTER TABLE tbl_name RENAME tbl_name9;
ALTER TABLE tbl_name2 RENAME tbl_name;
DROP TABLE tbl_name9;   

DROP ALL DATA FROM PARTITION BUT KEEP PARTITION

ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;

If you do not trust ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;, do it manually

CREATE TABLE tbl_name2 LIKE tbl_name;
INSERT INTO tbl_name2 SELECT * FROM tbl_name WHERE (anything but stuff from partition-name)
ALTER TABLE tbl_name RENAME tbl_name9;
ALTER TABLE tbl_name2 RENAME tbl_name;
DROP TABLE tbl_name9;   

CREATE A BLANK TABLE

CREATE TABLE tbl_name2 LIKE tbl_name;
DROP TABLE tbl_name;
ALTER TABLE tbl_name2 RENAME tbl_name;

Give it a Try !!!

UPDATE 2013-05-20 18:41 EDT

I am glad you were able to drop the partition.

I have some distressing news: It is not possible to shrink ibdata1 without dumping all the data, deleting ibdata1, restarting mysql, and reloading.

Here are my past posts on how to do that safely:

• May 21, 2012 : How large will a MySQL database be relative to the dump file?
• Apr 01, 2012 : Is innodb_file_per_table advisable?
• Mar 25, 2012 : Why does InnoDB store all databases in one file?
• Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
• Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?