Bug #79880 Can't drop general tablespace if during alter, service was killed
Submitted: 8 Jan 2016 8:54 Modified: 3 Feb 2016 7:11
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[8 Jan 2016 8:54] Shahriyar Rzayev
Description:
This report is about epic result of over-allocating buffer pool size(memory):

http://bugs.mysql.com/bug.php?id=79850 (related to overcomitting)

I have a VM:

[root@mysql-57 ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:           3428         377         664           1        2387        2862
Swap:          2047          92        1955

With CentOS 7:

[root@mysql-57 ~]# uname -a
Linux mysql-57 3.10.0-327.4.4.el7.x86_64 #1 SMP Tue Jan 5 16:07:00 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

Installed MySQL 5.7.10 using YUM repo.

Try to start MySQL with:

innodb_buffer_pool_size=20G
innodb_buffer_pool_instances=6

It will start as usual.

The test table:

mysql> show create table sbtest1;
| sbtest1 | 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` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=25661595 DEFAULT CHARSET=latin1 |

mysql> CREATE TABLESPACE `sbtest1` ADD DATAFILE 'sbtest1.ibd' ENGINE=INNODB;
Query OK, 0 rows affected (0,02 sec)

[root@mysql-57 ~]# ls /var/lib/mysql | grep sbtest
sbtest1.ibd

And then if you try to alter sbtest1 table's tablespace:

mysql> ALTER TABLE sbtest1 TABLESPACE = sbtest1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Well, there will be no crash as there is no entry in error log.

But you can see from dmesg that OOM killer played with MySQL process :)

Jan  8 03:18:03 mysql-57 kernel: Out of memory: Kill process 7613 (mysqld) score 956 or sacrifice child
Jan  8 03:18:03 mysql-57 kernel: Killed process 7613 (mysqld) total-vm:28896136kB, anon-rss:3331568kB, file-rss:0kB
Jan  8 03:18:03 mysql-57 systemd: mysqld.service: main process exited, code=killed, status=9/KILL
Jan  8 03:18:03 mysql-57 systemd: Unit mysqld.service entered failed state.
Jan  8 03:18:03 mysql-57 systemd: mysqld.service failed.
Jan  8 03:18:03 mysql-57 systemd: mysqld.service holdoff time over, scheduling restart.
Jan  8 03:18:03 mysql-57 systemd: Starting MySQL Server...
Jan  8 03:18:07 mysql-57 systemd: Started MySQL Server.
Jan  8 03:23:34 mysql-57 systemd: Stopping MySQL Server...
Jan  8 03:23:38 mysql-57 systemd: Starting MySQL Server...
Jan  8 03:23:39 mysql-57 systemd: Started MySQL Server.

Result:

#RESULT 1

sbtest1.ibd general tablespace file size will grow after each kill and it will not reclaimed:

[root@mysql-57 ~]# ls -lth /var/lib/mysql | grep sbtest1
-rw-r-----. 1 mysql mysql 1,9G янв  8 02:54 sbtest1.ibd

Also there will be temp files on dbtest(database folder) which are also will not be removed:

[root@mysql-57 ~]# ls -lth /var/lib/mysql/dbtest/
total 8,1G
-rw-r-----. 1 mysql mysql 6,7G янв  8 03:27 sbtest1.ibd
-rw-r-----. 1 mysql mysql 8,5K янв  8 03:24 sbtest1.frm
-rw-r-----. 1 mysql mysql 1,5G янв  8 03:18 #sql-ib41-3573152321.ibd
-rw-r-----. 1 mysql mysql 8,5K янв  8 03:17 #sql-1dbd_2.frm
-rw-r-----. 1 mysql mysql 8,5K янв  8 02:53 #sql-1caa_2.frm
-rw-r-----. 1 mysql mysql 8,5K янв  8 02:49 #sql-835_9cf.frm
-rw-r-----. 1 mysql mysql   65 янв  7 08:23 db.opt

#RESULT2
Messy results in metadata information:

mysql> select * from INNODB_SYS_TABLESPACES where name like '%btest%';
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| SPACE | NAME                        | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE  | ALLOCATED_SIZE |
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
|    54 | sbtest1                     | 2048 | Any         | Any        |     16384 |             0 | General    |          4096 | 2021654528 |     2021658624 |
|    59 | dbtest/#sql-ib41-3573152321 |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 | 1526726656 |     1526730752 |
|    62 | dbtest/sbtest1              |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 | 7092568064 |     7092572160 |
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
3 rows in set (0,00 sec)

mysql> select * from information_schema.INNODB_SYS_DATAFILES where path like '%btest%';
+-------+-----------------------------------+
| SPACE | PATH                              |
+-------+-----------------------------------+
|    54 | ./sbtest1.ibd                     |
|    59 | ./dbtest/#sql-ib41-3573152321.ibd |
|    62 | ./dbtest/sbtest1.ibd              |
+-------+-----------------------------------+
3 rows in set (0,00 sec)

#RESULT3

Can not drop tablespace:

mysql> drop tablespace sbtest1;
ERROR 1529 (HY000): Failed to drop TABLESPACE sbtest1

How to repeat:
See the description

Suggested fix:
The best approach is to preventing allocating too much memory.
Calculating buffer pool size against available physical memory.
Related to this report ->  I can't even find solution how to remove this files and tablespace.
[12 Jan 2016 16:24] Sinisa Milivojevic
Duplicate of #79850
[3 Feb 2016 7:11] Shahriyar Rzayev
From now I think it is duplicate of:

http://bugs.mysql.com/bug.php?id=80183
[3 Feb 2016 7:11] Shahriyar Rzayev
Changed Severity