Bug #76182 The new created tablespace is not reported in the INFORMATION_SCHEMA
Submitted: 5 Mar 2015 21:42 Modified: 2 Jun 2015 14:40
Reporter: Marco Tusa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.6, 5.7.7 OS:Linux
Assigned to: CPU Architecture:Any
Tags: information_schema, tablespaces

[5 Mar 2015 21:42] Marco Tusa 
Description:
The new created tablespace is not reported in the INFORMATION_SCHEMA FILE and TABLESPACES tables;
Documentation reference:
http://dev.mysql.com/doc/refman/5.7/en/files-table.html
http://dev.mysql.com/doc/refman/5.7/en/tablespaces-table.html

Given the fact it is now possible to use the tablespace, not having them reported correctly could leave the DBA almost blind.

(root@localhost) [test]>\s
--------------
mysql  Ver 14.14 Distrib 5.5.27, for linux2.6 (x86_64) using readline 5.1

Connection id:		3
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.6-m16-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Insert id:		10001
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/opt/mysql_instances/server57/mysql.sock
Uptime:			17 min 41 sec

Threads: 4  Questions: 94  Slow queries: 0  Opens: 121  Flush tables: 1  Open tables: 113  Queries per second avg: 0.088
--------------

How to repeat:

(root@localhost) [test]>CREATE TABLESPACE `TBS1` ADD DATAFILE './tbs_test/tbs1.ibd' ENGINE=innodb;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]>CREATE TABLE `tbtest1A` (
    ->   `autoInc` bigint(11) NOT NULL AUTO_INCREMENT,
    ->   `a` int(11) NOT NULL,
    ->   `uuid` char(36) NOT NULL,
    ->   `b` varchar(100) NOT NULL,
    ->   `c` char(200) NOT NULL,
    ->   `counter` bigint(20) DEFAULT NULL,
    ->   `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   `partitionid` int(11) NOT NULL DEFAULT '0',
    ->   `date` date NOT NULL,
    ->   `strrecordtype` char(3) DEFAULT NULL,
    ->   PRIMARY KEY (`autoInc`,`partitionid`),
    ->   KEY `IDX_a` (`a`),
    ->   KEY `IDX_uuid` (`uuid`)
    -> ) TABLESPACE TBS1;
Query OK, 0 rows affected (0.01 sec)

[root@mysqlt1 server57]# ll data/tbs_test/
total 64
-rw-r----- 1 mysql mysql 65536 Mar  5 16:30 tbs1.ibd

(root@localhost) [test]>insert into tbtest1A select * from tbtest1 limit 5000;
Query OK, 5000 rows affected (0.27 sec)
Records: 5000  Duplicates: 0  Warnings: 0

[root@mysqlt1 server57]# ll data/tbs_test/
total 11268
-rw-r----- 1 mysql mysql 11534336 Mar  5 16:30 tbs1.ibd

(root@localhost) [test]>select * from information_schema.TABLESPACES;
Empty set (0.00 sec)

(root@localhost) [test]>select * from information_schema.FILES;
Empty set (0.00 sec)

(root@localhost) [test]>show global variables like 'default%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
| default_password_lifetime     | 360                   |
| default_storage_engine        | InnoDB                |
| default_tmp_storage_engine    | InnoDB                |
| default_week_format           | 0                     |
+-------------------------------+-----------------------+
5 rows in set (0.00 sec)

(root@localhost) [test]>
[6 Mar 2015 11:30] MySQL Verification Team 
Hello Marco Tusa,

Thank you for the report and test case.

Thanks,
Umesh
[6 Mar 2015 11:30] MySQL Verification Team 
// 5.7.7

mysql> use test
Database changed
mysql> CREATE TABLESPACE `TBS1` ADD DATAFILE './tbs_test/tbs1.ibd' ENGINE=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `tbtest1A` (
    ->    `autoInc` bigint(11) NOT NULL AUTO_INCREMENT,
    ->    `a` int(11) NOT NULL,
    ->    `uuid` char(36) NOT NULL,
    ->    `b` varchar(100) NOT NULL,
    ->    `c` char(200) NOT NULL,
    ->    `counter` bigint(20) DEFAULT NULL,
    ->    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->    `partitionid` int(11) NOT NULL DEFAULT '0',
    ->    `date` date NOT NULL,
    ->    `strrecordtype` char(3) DEFAULT NULL,
    ->    PRIMARY KEY (`autoInc`,`partitionid`),
    ->    KEY `IDX_a` (`a`),
    ->    KEY `IDX_uuid` (`uuid`)
    ->  ) TABLESPACE TBS1;
Query OK, 0 rows affected (0.00 sec)

// Insert some 50K records

// 
[umshastr@hod03]/export/umesh/mysql-5.7.7: ls -l 76182/tbs_test/tbs1.ibd
-rw-r----- 1 umshastr common 131072 Mar  6 11:43 76182/tbs_test/tbs1.ibd
.
.
[umshastr@hod03]/export/umesh/mysql-5.7.7: ls -l 76182/tbs_test/tbs1.ibd
-rw-r----- 1 umshastr common 62914560 Mar  6 11:52 76182/tbs_test/tbs1.ibd

//

mysql> select * from information_schema.TABLESPACES;
Empty set (0.00 sec)

mysql> select * from information_schema.FILES;
Empty set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.7                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.7-rc-enterprise-commercial-advanced                 |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.01 sec)
[6 Mar 2015 11:31] MySQL Verification Team 
//

##

mysql>
mysql> CREATE TABLESPACE `TBS11` ADD DATAFILE 'test.ibd' ENGINE=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(id int)tablespace TBS11;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.TABLESPACES;
Empty set (0.00 sec)

mysql> select * from information_schema.FILES;
Empty set (0.00 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 select * from t1;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 128 rows affected (0.00 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 256 rows affected (0.00 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 512 rows affected (0.01 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 1024 rows affected (0.01 sec)
Records: 1024  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 2048 rows affected (0.01 sec)
Records: 2048  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 4096 rows affected (0.03 sec)
Records: 4096  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 8192 rows affected (0.06 sec)
Records: 8192  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 16384 rows affected (0.12 sec)
Records: 16384  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 32768 rows affected (0.22 sec)
Records: 32768  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 65536 rows affected (0.45 sec)
Records: 65536  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 131072 rows affected (0.91 sec)
Records: 131072  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.TABLESPACES;
Empty set (0.00 sec)

mysql> select * from information_schema.FILES;
Empty set (0.00 sec)

[umshastr@hod03]/export/umesh/mysql-5.7.7: ls -l  76182/test.ibd
-rw-r----- 1 umshastr common 98304 Mar  6 12:25 76182/test.ibd
[umshastr@hod03]/export/umesh/mysql-5.7.7:
[umshastr@hod03]/export/umesh/mysql-5.7.7: ls -l  76182/test.ibd
-rw-r----- 1 umshastr common 16777216 Mar  6 12:28 76182/test.ibd
[10 May 2015 16:50] Daniël van Eeden 
This looks like a duplicate of Bug #74358
[2 Jun 2015 14:40] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 5.7.8, 5.8.0 releases, and here's the changelog entry:

Information about "InnoDB" tablespaces was not reported in the
"INFORMATION_SCHEMA.TABLESPACES" or "INFORMATION_SCHEMA.FILES" tables.

Thank you for the bug report.
[4 Jun 2015 18:34] Daniel Price 
Posted by developer:
 
The changelog entry has been revised:

The INFORMATION_SCHEMA.FILES table now reports metadata for all InnoDB
tablespace types including file-per-table tablespaces, general
tablespaces, the system tablespace, temporary table tablespaces, and undo
tablespaces (if present). System tablespace and temporary table tablespace
metadata is no longer reported by the
INFORMATION_SCHEMA.INNODB__SYS_TABLESPACES and
INFORMATION_SCHEMA.INNODB_SYS_DATAFILES tables. However, these tables
continue to provide metadata for file-per-table and general tablespaces.