Bug #86809 Failed to drop tablespace after unsuccessful attempt to create partitioned table
Submitted: 23 Jun 2017 8:47 Modified: 23 Jun 2017 9:12
Reporter: Sergei Glushchenko (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.7.18 OS:Any
Assigned to: CPU Architecture:Any

[23 Jun 2017 8:47] Sergei Glushchenko
Description:
Server crashed while executing DROP TABLESPACE command.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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 TABLESPACE ts_compressed ADD DATAFILE 'ts_compressed.ibd' ENGINE=InnoDB FILE_BLOCK_SIZE=8192;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLESPACE ts_not_compressed ADD DATAFILE 'ts_not_compressed.ibd';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t (a INT NOT NULL, PRIMARY KEY(a)) ENGINE=InnoDB TABLESPACE ts_compressed ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 PARTITION BY RANGE (a) PARTITIONS 3 (PARTITION p1 VALUES LESS THAN (2), PARTITION p2 VALUES LESS THAN (4) TABLESPACE innodb_file_per_table, PARTITION p3 VALUES LESS THAN (6) TABLESPACE ts_not_compressed);
ERROR 1478 (HY000): InnoDB: Tablespace `ts_not_compressed` cannot contain a COMPRESSED table
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLESPACE ts_not_compressed;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLESPACE ts_compressed;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

Error log:

2017-06-23T08:43:36.810197Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-06-23T08:43:36.810630Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-06-23T08:43:36.810677Z 0 [Note] bin/mysqld (mysqld 5.7.18) starting as process 24749 ...
2017-06-23T08:43:36.810713Z 0 [ERROR] Can't find error-message file '/Users/sergei/.tmp/mysqld/mysql-5.7.18-macos10.12-x86_64/server/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2017-06-23T08:43:36.812922Z 0 [Warning] Setting lower_case_table_names=2 because file system for /Users/sergei/.tmp/mysqld/mysql-5.7.18-macos10.12-x86_64/dat1/ is case insensitive
2017-06-23T08:43:36.814591Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-06-23T08:43:36.814607Z 0 [Note] InnoDB: Uses event mutexes
2017-06-23T08:43:36.814610Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-06-23T08:43:36.814613Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-06-23T08:43:36.814948Z 0 [Note] InnoDB: Number of pools: 1
2017-06-23T08:43:36.815098Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-06-23T08:43:36.816665Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-06-23T08:43:36.827498Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-06-23T08:43:36.847799Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-23T08:43:36.863015Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-06-23T08:43:36.863182Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-06-23T08:43:37.141799Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-06-23T08:43:37.143553Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-06-23T08:43:37.143571Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-06-23T08:43:37.143935Z 0 [Note] InnoDB: Waiting for purge to start
2017-06-23T08:43:37.196715Z 0 [Note] InnoDB: 5.7.18 started; log sequence number 2535558
2017-06-23T08:43:37.196894Z 0 [Note] InnoDB: Loading buffer pool(s) from /Users/sergei/.tmp/mysqld/mysql-5.7.18-macos10.12-x86_64/dat1/ib_buffer_pool
2017-06-23T08:43:37.197098Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-06-23T08:43:37.203072Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170623 15:43:37
2017-06-23T08:43:37.208244Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-06-23T08:43:37.214044Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017-06-23T08:43:37.214300Z 0 [Note] IPv6 is available.
2017-06-23T08:43:37.214363Z 0 [Note]   - '::' resolves to '::';
2017-06-23T08:43:37.214541Z 0 [Note] Server socket created on IP: '::'.
2017-06-23T08:43:37.278040Z 0 [Note] Event Scheduler: Loaded 0 events
2017-06-23T08:43:37.278197Z 0 [Note] 
2017-06-23T08:43:37.278250Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 
2017-06-23T08:43:37.278262Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-06-23T08:43:37.371694Z 0 [Note] End of list of non-natively partitioned tables
2017-06-23 15:44:28 0x700004797000  InnoDB: Assertion failure in thread 123145377378304 in file ha_innodb.cc line 20897
InnoDB: Failing assertion: format != 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
08:44:28 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68215 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f901f009400
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 700004796e90 thread_stack 0x40000
0   mysqld                              0x0000000103f5ecbd my_print_stacktrace + 61
1   mysqld                              0x0000000103eba770 handle_fatal_signal + 688
2   libsystem_platform.dylib            0x00007fff8f5cab3a _sigtramp + 26
3   ???                                 0x0000000000000003 0x0 + 3
4   libsystem_c.dylib                   0x00007fff8f44f420 abort + 129
5   mysqld                              0x00000001041bc3b1 _Z23ut_dbg_assertion_failedPKcS0_m + 161
6   mysqld                              0x00000001040772b2 _Z11ib_senderrfP3THD14ib_log_level_tjz + 354
7   mysqld                              0x0000000104089f4a _Z7ib_errfP3THD14ib_log_level_tjPKcz + 202
8   mysqld                              0x000000010408c499 _ZL25innobase_alter_tablespaceP10handlertonP3THDP19st_alter_tablespace + 1401
9   mysqld                              0x0000000103e32f99 _Z22mysql_alter_tablespaceP3THDP19st_alter_tablespace + 265
10  mysqld                              0x0000000103dc2b8c _Z21mysql_execute_commandP3THDb + 11452
11  mysqld                              0x0000000103dbf52c _Z11mysql_parseP3THDP12Parser_state + 860
12  mysqld                              0x0000000103dbe381 _Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command + 5169
13  mysqld                              0x0000000103dbefda _Z10do_commandP3THD + 490
14  mysqld                              0x0000000103e9df34 handle_connection + 468
15  mysqld                              0x000000010421dba4 pfs_spawn_thread + 308
16  libsystem_pthread.dylib             0x00007fff8f5d493b _pthread_body + 180
17  libsystem_pthread.dylib             0x00007fff8f5d4887 _pthread_body + 0
18  libsystem_pthread.dylib             0x00007fff8f5d408d thread_start + 13

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f901f00de30): DROP TABLESPACE ts_compressed
Connection ID (thread ID): 3
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
[23 Jun 2017 9:01] Sergei Glushchenko
My bad. Crash happened due to wrong --basedir option provided. With proper basedir I got

mysql> DROP TABLESPACE ts_compressed;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts_compressed

Which still looks incorrect.

mysql> SHOW TABLES;
Empty set (0.00 sec)

And there is leftover file-per-table partition as well:

mysql> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES;
+---------------------------------------+
| FILE_NAME                             |
+---------------------------------------+
| ./ibdata1                             |
| ./mysql/engine_cost.ibd               |
| ./mysql/gtid_executed.ibd             |
| ./mysql/help_category.ibd             |
| ./mysql/help_keyword.ibd              |
| ./mysql/help_relation.ibd             |
| ./mysql/help_topic.ibd                |
| ./mysql/innodb_index_stats.ibd        |
| ./mysql/innodb_table_stats.ibd        |
| ./mysql/plugin.ibd                    |
| ./mysql/server_cost.ibd               |
| ./mysql/servers.ibd                   |
| ./mysql/slave_master_info.ibd         |
| ./mysql/slave_relay_log_info.ibd      |
| ./mysql/slave_worker_info.ibd         |
| ./mysql/time_zone.ibd                 |
| ./mysql/time_zone_leap_second.ibd     |
| ./mysql/time_zone_name.ibd            |
| ./mysql/time_zone_transition.ibd      |
| ./mysql/time_zone_transition_type.ibd |
| ./sys/sys_config.ibd                  |
| ./ibtmp1                              |
| ./ts_compressed.ibd                   |
| ./test/t#P#p2.ibd                     |
+---------------------------------------+
[23 Jun 2017 9:12] MySQL Verification Team
Hello Sergei,

Thank you for the report and feedback!

Thanks,
Umesh
[23 Jun 2017 9:13] MySQL Verification Team
## Crash not observed but observed incorrect behavior as observed in the report

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.18: bin/mysqld-debug --basedir=$PWD --datadir=$PWD/86809 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/86809/log.err 2>&1 &
[1] 21137
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.18: bin/mysql -uroot  -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-enterprise-commercial-advanced-debug MySQL Enterprise Server - Advanced Edition Debug (Commercial)

Copyright (c) 2000, 2017, 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.

root@localhost [(none)]> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use test;
Database changed
root@localhost [test]> CREATE TABLESPACE ts_compressed ADD DATAFILE 'ts_compressed.ibd' ENGINE=InnoDB FILE_BLOCK_SIZE=8192;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> CREATE TABLESPACE ts_not_compressed ADD DATAFILE 'ts_not_compressed.ibd';
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> CREATE TABLE t (a INT NOT NULL, PRIMARY KEY(a)) ENGINE=InnoDB TABLESPACE ts_compressed ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 PARTITION BY RANGE (a) PARTITIONS 3 (PARTITION p1 VALUES LESS THAN (2), PARTITION p2 VALUES LESS THAN (4) TABLESPACE innodb_file_per_table, PARTITION p3 VALUES LESS THAN (6) TABLESPACE ts_not_compressed);
DROP TABLE IF EXISTS t;
ERROR 1478 (HY000): InnoDB: Tablespace `ts_not_compressed` cannot contain a COMPRESSED table
root@localhost [test]> DROP TABLE IF EXISTS t;
DROP TABLESPACE ts_not_compressed;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test]> DROP TABLESPACE ts_not_compressed;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> DROP TABLESPACE ts_compressed;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts_compressed
root@localhost [test]>
root@localhost [test]> DROP TABLESPACE ts_compressed;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts_compressed
root@localhost [test]> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES;
+---------------------------------------+
| FILE_NAME                             |
+---------------------------------------+
| ./ibdata1                             |
| ./mysql/engine_cost.ibd               |
| ./mysql/gtid_executed.ibd             |
| ./mysql/help_category.ibd             |
| ./mysql/help_keyword.ibd              |
| ./mysql/help_relation.ibd             |
| ./mysql/help_topic.ibd                |
| ./mysql/innodb_index_stats.ibd        |
| ./mysql/innodb_table_stats.ibd        |
| ./mysql/plugin.ibd                    |
| ./mysql/server_cost.ibd               |
| ./mysql/servers.ibd                   |
| ./mysql/slave_master_info.ibd         |
| ./mysql/slave_relay_log_info.ibd      |
| ./mysql/slave_worker_info.ibd         |
| ./mysql/time_zone.ibd                 |
| ./mysql/time_zone_leap_second.ibd     |
| ./mysql/time_zone_name.ibd            |
| ./mysql/time_zone_transition.ibd      |
| ./mysql/time_zone_transition_type.ibd |
| ./sys/sys_config.ibd                  |
| ./ibtmp1                              |
| ./ts_compressed.ibd                   |
| ./test/t#P#p2.ibd                     |
+---------------------------------------+
24 rows in set (0.00 sec)

root@localhost [test]>

root@localhost [test]> show tables;
Empty set (0.00 sec)
[13 Dec 2018 3:52] Satya Bodapati
This bug is automatically fixed in 8.0.13

Partitions in general tablespaces are no longer allowed