Description:
This is a pretty edge case and somewhat related to https://bugs.mysql.com/bug.php?id=102395
Basically if a partitioned table is rotated by a series of CREATE TABLE ... LIKE...; RENAME TABLE / DROP TABLE, and the server is restarted in the mean time, the data path changes and is re-created in a buggy manner.
How to repeat:
mysql [localhost:5737] {msandbox} (db1) > CREATE TABLE ts (id INT, purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) )
-> SUBPARTITION BY HASH( TO_DAYS(purchased) )
-> SUBPARTITIONS 1 (
-> PARTITION p0 VALUES LESS THAN (1999) DATA DIRECTORY = '/tmp/data',
-> PARTITION p1 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/tmp/data'
-> );
Query OK, 0 rows affected (0.03 sec)
mysql [localhost:5737] {msandbox} (db1) > select FILE_NAME,TABLESPACE_NAME from information_schema.files where FILE_NAME like '%db1/ts%';
+------------------------------------+---------------------------+
| FILE_NAME | TABLESPACE_NAME |
+------------------------------------+---------------------------+
| /tmp/data/db1/ts#P#p0#SP#p0sp0.ibd | innodb_file_per_table_443 |
| /tmp/data/db1/ts#P#p1#SP#p1sp0.ibd | innodb_file_per_table_444 |
+------------------------------------+---------------------------+
2 rows in set (0.00 sec)
mysql [localhost:5737] {msandbox} (db1) > CREATE TABLE ts_new LIKE ts; RENAME TABLE ts TO ts_old, ts_new TO ts; DROP TABLE ts_old;
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
mysql [localhost:5737] {msandbox} (db1) > select FILE_NAME,TABLESPACE_NAME from information_schema.files where FILE_NAME like '%db1/ts%';
+------------------------------------+---------------------------+
| FILE_NAME | TABLESPACE_NAME |
+------------------------------------+---------------------------+
| /tmp/data/db1/ts#P#p0#SP#p0sp0.ibd | innodb_file_per_table_445 |
| /tmp/data/db1/ts#P#p1#SP#p1sp0.ibd | innodb_file_per_table_446 |
+------------------------------------+---------------------------+
2 rows in set (0.00 sec)
So far so good, but after a restart:
mysql [localhost:5737] {msandbox} (db1) > CREATE TABLE ts_new LIKE ts; RENAME TABLE ts TO ts_old, ts_new TO ts; DROP TABLE ts_old;
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:5737] {msandbox} (db1) > select FILE_NAME,TABLESPACE_NAME from information_schema.files where FILE_NAME like '%db1/ts%';
+-----------------------------------------------------+---------------------------+
| FILE_NAME | TABLESPACE_NAME |
+-----------------------------------------------------+---------------------------+
| /tmp/data/ts#P#p0#SP#p0sp0/db1/ts#P#p0#SP#p0sp0.ibd | innodb_file_per_table_448 |
| /tmp/data/ts#P#p1#SP#p1sp0/db1/ts#P#p1#SP#p1sp0.ibd | innodb_file_per_table_449 |
+-----------------------------------------------------+---------------------------+
2 rows in set (0.00 sec)
-- another restart
mysql [localhost:5737] {msandbox} (db1) > CREATE TABLE ts_new LIKE ts; RENAME TABLE ts TO ts_old, ts_new TO ts; DROP TABLE ts_old;
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5737] {msandbox} (db1) > select FILE_NAME,TABLESPACE_NAME from information_schema.files where FILE_NAME like '%db1/ts%';
+----------------------------------------------------------------------+---------------------------+
| FILE_NAME | TABLESPACE_NAME |
+----------------------------------------------------------------------+---------------------------+
| /tmp/data/ts#P#p0#SP#p0sp0/ts#P#p0#SP#p0sp0/db1/ts#P#p0#SP#p0sp0.ibd | innodb_file_per_table_453 |
| /tmp/data/ts#P#p1#SP#p1sp0/ts#P#p1#SP#p1sp0/db1/ts#P#p1#SP#p1sp0.ibd | innodb_file_per_table_454 |
+----------------------------------------------------------------------+---------------------------+
2 rows in set (0.00 sec)
$ ls -l /tmp/data/ts#P#p0#SP#p0sp0/ts#P#p0#SP#p0sp0/db1/ts#P#p0#SP#p0sp0.ibd
-rw-r----- 1 przemek przemek 98304 Jan 29 10:44 /tmp/data/ts#P#p0#SP#p0sp0/ts#P#p0#SP#p0sp0/db1/ts#P#p0#SP#p0sp0.ibd
Suggested fix:
Stop the data path from changing that crazy way.
