MySQL Bugs: #77814: Altering table twice,with same tablespace will increase general tablespace size

Bug #77814	Altering table twice,with same tablespace will increase general tablespace size
Submitted:	23 Jul 2015 11:28	Modified:	5 Apr 2016 21:22
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.7.7-rc, 5.7.9	OS:	CentOS (7)
Assigned to:	Daniel Price	CPU Architecture:	Any

Description:
Sample Table structure:

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `c1` varchar(85) DEFAULT NULL
) /*!50100 TABLESPACE innodb_file_per_table */ ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

# Creating Tablespace

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

[root@centos7_vm datadir]# ls -ltr | grep ts1.ibd
-rw-r-----. 1 root  root     65536 Jul 23 16:12 ts1.ibd

# Altering t1 table to ts1 tablespace 1st time:

mysql> alter table t1 tablespace ts1;
Query OK, 0 rows affected (1 min 5.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `c1` varchar(85) DEFAULT NULL
) /*!50100 TABLESPACE ts1 */ ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[root@centos7_vm datadir]# ls -ltr | grep ts1.ibd
-rw-r-----. 1 root  root  155189248 Jul 23 16:16 ts1.ibd

NOTE size -> 155189248

# Altering t1 table to ts1 tablespace 2nd time:

mysql> alter table t1 tablespace ts1;
Query OK, 0 rows affected (1 min 5.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# Pay attention to ts1.ibd file size:

[root@centos7_vm datadir]# ls -ltr | grep ts1.ibd
-rw-r-----. 1 root  root  301989888 Jul 23 16:18 ts1.ibd

NOTE size -> 301989888

It is increased by same table twice.
Also there is no error/warning if you execute same alter to same table with same tablespace unlimited times.

How to repeat:
1. Install MySQL 5.7.7-rc
2. Create sample table
3. Create general tablespace
4. Alter table to use general tablespace(1st run) - check size
5. Alter table to use same general tablespace(2ns run) - check size

Note: You can run same alter statement unlimited time without error/warning.

Suggested fix:
Maybe preventing executing alter statement to alter table's tablespace, which is already used by table.

Hello Shahriyar,

Thank you for the report.
Observed this with 5.7.9 build, I'm not sure whether this is by design and intended behavior.

Thanks,
Umesh

// 
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysql -uroot --port=15000 --protocol=tcp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `t1` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `c1` varchar(85) DEFAULT NULL
    -> ) /*!50100 TABLESPACE innodb_file_per_table */ ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> \! ls -l ./77591/test
total 112
-rw-r----- 1 umshastr common    65 Jul 23 13:51 db.opt
-rw-r----- 1 umshastr common  8582 Jul 23 13:51 t1.frm
-rw-r----- 1 umshastr common 98304 Jul 23 13:51 t1.ibd
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> \! ls -l ./77591/test
total 112
-rw-r----- 1 umshastr common    65 Jul 23 13:51 db.opt
-rw-r----- 1 umshastr common  8582 Jul 23 13:51 t1.frm
-rw-r----- 1 umshastr common 98304 Jul 23 13:51 t1.ibd
mysql> \! ls -l ./77591/
total 123080
.
.
drwxr-x--- 2 umshastr common       61 Jul 23 13:51 test
-rw-r----- 1 umshastr common    65536 Jul 23 13:52 ts1.ibd
mysql> \! ls -l ./77591/ts1.ibd
-rw-r----- 1 umshastr common 65536 Jul 23 13:52 ./77591/ts1.ibd
mysql>
mysql> alter table t1 tablespace ts1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \! ls -l ./77591/ts1.ibd
-rw-r----- 1 umshastr common 98304 Jul 23 13:55 ./77591/ts1.ibd <------ 98304
mysql>
mysql> alter table t1 tablespace ts1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \! ls -l ./77591/ts1.ibd
-rw-r----- 1 umshastr common 114688 Jul 23 13:55 ./77591/ts1.ibd <------ 114688
mysql>
mysql> alter table t1 tablespace ts1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \! ls -l ./77591/ts1.ibd
-rw-r----- 1 umshastr common 114688 Jul 23 13:55 ./77591/ts1.ibd
mysql>
mysql> alter table t1 tablespace ts1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \! ls -l ./77591/ts1.ibd
-rw-r----- 1 umshastr common 114688 Jul 23 13:56 ./77591/ts1.ibd
mysql>

Maybe it is something related to reclaiming space.
When we first alter table to use ts1 tablespace it used space as needed.
When we run second time to use same ts1 tablespace it tries to use space by replacing with same data but instead it consumes much more pages doubling size.

If you run 5 or 6 or even more times the size of tablespace stays unchanged, so existing space is reused in that condition.

Posted by developer:
 
Correcting Status setting. Doc Bug should be set to "13 - Doc Bug (Response/Resolution)"

Posted by developer:
 
The following sections have been updated:

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

"For InnoDB tables, a table-copying ALTER TABLE operation on table that
resides in a shared tablespace such as a general tablespace or the system
tablespace can increase the amount of space used by the tablespace. Such
operations require as much additional space as the data in the table plus
indexes. For a table that resides in a shared tablespace, the additional
space used during a table-copying ALTER TABLE operation is not released
back to the operating system as it is for a table that resides in a
file-per-table tablespace."

https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-limitations.html 
https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html 
https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html 

Thank you for the bug report.