Bug #80263 Abandoned .ibd files each time after killing mysql while running optimize table
Submitted: 4 Feb 2016 9:09 Modified: 4 Feb 2016 15:28
Reporter: Shahriyar Rzayev Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.7.10 OS:CentOS (7)
Assigned to: CPU Architecture:Any

[4 Feb 2016 9:09] Shahriyar Rzayev
Description:
Hi dear experts,
The situation is similar to 
http://bugs.mysql.com/bug.php?id=80181.

But the difference is that, now the statement is 'optimize table'.

 
CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2427738 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

Kill the process while running:

mysql> optimize table sbtest1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

[root@ps-5 mysql]# ls -l dbtest/
total 1092792
-rw-r-----. 1 mysql mysql        65 фев  4 03:43 db.opt
-rw-r-----. 1 mysql mysql      8632 фев  4 03:46 sbtest1.frm
-rw-r-----. 1 mysql mysql 784334848 фев  4 03:53 sbtest1.ibd
-rw-r-----. 1 mysql mysql      8632 фев  4 03:43 #sql-21ee_3.frm
-rw-r-----. 1 mysql mysql      8632 фев  4 03:53 #sql-3037_2.frm
-rw-r-----. 1 mysql mysql 201326592 фев  4 03:43 #sql-ib41-423909887.ibd
-rw-r-----. 1 mysql mysql 159383552 фев  4 03:53 #sql-ib47-3672662344.ibd

mysql> select * from information_schema.innodb_sys_tablespaces where name like '%dbtest%';
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME                        | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|   158 | dbtest/#sql-ib41-423909887  |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 | 201326592 |      201326592 |
|   168 | dbtest/sbtest1              |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 | 784334848 |      758312960 |
|   169 | dbtest/#sql-ib47-3672662344 |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 | 159383552 |      159338496 |
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
3 rows in set (0.00 sec)

How to repeat:
See description

Suggested fix:
Seems to be duplicate of #80181 but with different situation.
[4 Feb 2016 11:43] MySQL Verification Team
Hello Shahriyar,

Thank you for the report.
Verified as described with 5.7.10.

Thanks,
Umesh
[4 Feb 2016 15:26] Kevin Lewis
Posted by developer:
 
This is a duplicate of Bug#80181 because the optimize table process internally is the same as ALTER TABLE when a table is rebuilt.
And Bug#80181 was marked as a duplicate of Bug#80183 which was changed to 5.8 so that we will assure that these orphaned tables are deleted after a crash in the next release.