| Bug #95743 | core at create table with FK for table transferred from MyISAM to InnoDB | ||
|---|---|---|---|
| Submitted: | 12 Jun 2019 8:06 | Modified: | 14 May 2020 17:23 |
| Reporter: | dave do | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 8.0.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Jun 2019 8:14]
MySQL Verification Team
Hello Dave do, Thank you for the report and test case. Observed that 8.0.16 debug build is affected. regards, Umesh
[25 Nov 2019 7:47]
dave do
there are some updates for this issue. we found this issue may cause deadlock in some situation, after our further test. so please consider to reaise the Severity of this issue to S3/S2. and root cause issue, in my opinion, it is because the engine_substitution process is too late while we put it in create_table_impl now. I think we should advance these process to Sql_cmd_create_table::execute instead. deadlock info like this: ---TRANSACTION 19684203, ACTIVE 316 sec adding foreign keys 8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 35 MySQL thread id 897, OS thread handle 140367571412736, query id 10832956 192.107.57.39 root Waiting for table metadata lock =====> Wait MDL ddl_create_drop_foreign_key_table_005_table_0d create table ddl_create_drop_foreign_key_table_005_table_1 ( w_id bigint not null AUTO_INCREMENT, w_name varchar(10), w_street_1 varchar(20), w_street_2 varchar(20), w_city varchar(20), w_state char(2), w_zip char(9), w_tax decimal(4,2), w_ytd decimal(12,2), foreign key (w_id) REFERENCES ddl_create_drop_foreign_key_table_005_table_0d(c_bigint) ON UPDATE RESTRICT ON DELETE RESTRICT) Engine=MyISAM ---TRANSACTION 19681886, ACTIVE 317 sec 23 lock struct(s), heap size 1136, 107 row lock(s), undo log entries 206 MySQL thread id 898, OS thread handle 140367571121920, query id 10829170 192.107.57.39 root rename result table alter table ddl_create_drop_foreign_key_table_005_table_3 add constraint fkey_ddl_create_drop_foreign_key_table_005_table_3 foreign key(c_bigint) references ddl_create_drop_foreign_key_table_005_table_0d(c_bigint) Trx read view will not see trx with id >= 19682317, sees < 19655331 ---TRANSACTION 19683190, ACTIVE 316 sec dropping table 4 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 24 MySQL thread id 902, OS thread handle 140367632852736, query id 10829654 192.107.57.39 root checking permissions drop table if exists ddl_create_drop_partition_table_004_key_1 --Thread 140367571121920 has waited at dict0stats.cc line 3225 for 316 seconds the semaphore: X-lock on RW-latch at 0x7fac207b1dd8 created in file dict0dict.cc line 1047 a writer (thread id 140367632852736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file not yet reserved line 0 Last time write locked in file /src/sql/storage/innobase/row/row0mysql.cc line 3834 --Thread 140367632852736 has waited at dict0dict.h line 1171 for 316 seconds the semaphore: X-lock on RW-latch at 0x7fac227a5918 created in file dict0dict.cc line 1038 a writer (thread id 140367571412736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file dict0dict.h line 1169 Last time write locked in file /src/sql/storage/innobase/include/dict0dict.h line 1171 summary as: 140367571412736 create table , hold dict sys lock, wait for MDL of ddl_create_drop_foreign_key_table_005_table_0d 140367571121920 alter table , hold MDL, wait for dict stats lock 140367632852736 drop table , hold dict stats lock, wait for dict sys lock detail stack info I will put in the files tab.
[25 Nov 2019 7:49]
dave do
Deadlock stack info of Bug #95743
Attachment: Deadlock stack info of Bug #95743.log (application/octet-stream, text), 20.33 KiB.
[26 Nov 2019 2:38]
dave do
Not only have assert in debug build, but also deadlock in release build.
[14 May 2020 17:23]
Paul DuBois
Posted by developer: Fixed in 8.0.21. An assertion was raised if creating a child table in a foreign key relation caused an engine substitution.
Description: start mysqld with configuration as below: sql_mode="" disabled_storage_engines="myisam" default_storage_engine="innodb" create a table wiht foreign key with myisam engine will hit an DBUG_ASSERT. Assert fail at Dictionary_client::acquire // Check proper MDL lock. > DBUG_ASSERT(MDL_checker::is_read_locked(m_thd, *object)); How to repeat: create table Table_Parent_CREATE_TABLE_FOREIGN_077 (C_ID INT,C_D_ID INTEGER NOT NULL,C_W_ID BIGINT NOT NULL,C_DOUBLE DOUBLE NOT NULL,C_DECIMAL DECIMAL NOT NULL,C_FIRST VARCHAR(64) NOT NULL,C_MIDDLE CHAR(2),C_LAST VARCHAR(64) NOT NULL,C_STREET_1 VARCHAR(20) NOT NULL,C_STREET_2 VARCHAR(20),C_CITY VARCHAR(64) NOT NULL,C_STATE CHAR(2) NOT NULL,C_ZIP CHAR(9) NOT NULL,C_PHONE CHAR(16) NOT NULL,C_SINCE TIMESTAMP,C_CREDIT CHAR(2) NOT NULL,C_CREDIT_LIM NUMERIC(12,2),C_DISCOUNT NUMERIC(4,4),C_BALANCE NUMERIC(12,2),C_YTD_PAYMENT REAL NOT NULL,C_PAYMENT_CNT FLOAT NOT NULL,C_DELIVERY_CNT BOOLEAN NOT NULL,C_END DATE NOT NULL,C_VCHAR VARCHAR(1000),C_DATA TEXT,C_TEXT BLOB,C_TINYTEXT TINYTEXT,C_MEDIUMBLOB MEDIUMBLOB,C_LONGBLOB LONGBLOB,primary key (C_ID,C_D_ID,C_W_ID)); CREATE TABLE IF NOT EXISTS `01ABC#*@%&` (C_ID INT DEFAULT 99 COMMENT 'string' REFERENCES test.CREATE_TABLE_000 (C_ID) MATCH FULL ON DELETE NO ACTION ,C_M_ID INT AS ('qwe@#123' LIKE 'asd@213#') STORED NOT NULL UNIQUE KEY COMMENT 'string', C_FK_ID INT,C_D_ID INTEGER NOT NULL,C_W_ID BIGINT NOT NULL,C_DOUBLE DOUBLE NOT NULL,C_DECIMAL DECIMAL NOT NULL,C_FIRST VARCHAR(64) NOT NULL,C_MIDDLE CHAR(2),C_LAST VARCHAR(64) NOT NULL,C_STREET_1 VARCHAR(20) NOT NULL,C_STREET_2 VARCHAR(20),C_CITY VARCHAR(64) NOT NULL,C_STATE CHAR(2) NOT NULL,C_ZIP CHAR(9) NOT NULL,C_PHONE CHAR(16) NOT NULL,C_SINCE TIMESTAMP,C_CREDIT CHAR(2) NOT NULL,C_CREDIT_LIM NUMERIC(12,2),C_DISCOUNT NUMERIC(4,4),C_BALANCE NUMERIC(12,2),C_YTD_PAYMENT REAL NOT NULL,C_PAYMENT_CNT FLOAT NOT NULL,C_DELIVERY_CNT BOOLEAN NOT NULL,C_END DATE NOT NULL,C_VCHAR VARCHAR(1000),C_DATA TEXT,C_TEXT BLOB,C_TINYTEXT TINYTEXT,C_MEDIUMBLOB MEDIUMBLOB,C_LONGBLOB LONGBLOB,C_LINESTRING LINESTRING not null, CONSTRAINT pk_CREATE_TABLE_FOREIGN_077_taurus PRIMARY KEY USING BTREE (C_D_ID ASC) KEY_BLOCK_SIZE 10, CONSTRAINT UNIQUE_fk_CREATE_TABLE_FOREIGN_077_taurus UNIQUE USING BTREE (C_ID,C_D_ID,C_W_ID ASC) VISIBLE, KEY index_CREATE_TABLE_FOREIGN_077_taurus USING BTREE (C_FIRST ASC) COMMENT 'string', CONSTRAINT fk_CREATE_TABLE_FOREIGN_077_taurus FOREIGN KEY (C_FK_ID,C_D_ID,C_W_ID) REFERENCES Table_Parent_CREATE_TABLE_FOREIGN_077 (C_ID,C_D_ID,C_W_ID) ON DELETE CASCADE, CHECK ('agdj56!#54'IS UNKNOWN)) AUTO_INCREMENT = 9, AVG_ROW_LENGTH = 8, DEFAULT CHARACTER SET utf8, CHECKSUM 0, COLLATE utf8_general_ci, COMMENT = 'string', CONNECTION = 'connect_string', DELAY_KEY_WRITE = 0, ENGINE = MyISAM, INSERT_METHOD LAST, MAX_ROWS = 99, MIN_ROWS 99, PACK_KEYS = DEFAULT, PASSWORD = 'string', ROW_FORMAT COMPACT, STATS_AUTO_RECALC DEFAULT; Suggested fix: we do the engine check and substitution at create_table_impl, it is too late for table creation process. so, i suggest to advance the engine check and substitution to the start of create table process to avoid inconsistency of create_info.db_type , like following patch: --- a/sql/sql_cmd_ddl_table.cc +++ b/sql/sql_cmd_ddl_table.cc @@ -174,6 +174,45 @@ bool Sql_cmd_create_table::execute(THD *thd) { thd->work_part_info = part_info; } + // + if (ha_is_storage_engine_disabled(create_info.db_type)) { + /* + If table creation is disabled for the engine then substitute the engine + for the table with the default engine only if sql mode + NO_ENGINE_SUBSTITUTION is disabled. + */ + handlerton *new_engine = nullptr; + if (is_engine_substitution_allowed(thd)) + new_engine = ha_default_handlerton(thd); + + /* + Proceed with the engine substitution only if, + 1. The disabled engine and the default engine are not the same. + 2. The default engine is not in the disabled engines list. + else report an error. + */ + if (new_engine && create_info.db_type && + new_engine != create_info.db_type && + !ha_is_storage_engine_disabled(new_engine)) { + push_warning_printf(thd, Sql_condition::SL_WARNING, + ER_DISABLED_STORAGE_ENGINE, + ER_THD(thd, ER_DISABLED_STORAGE_ENGINE), + ha_resolve_storage_engine_name(create_info.db_type)); + + create_info.db_type = new_engine; + + push_warning_printf( + thd, Sql_condition::SL_WARNING, ER_WARN_USING_OTHER_HANDLER, + ER_THD(thd, ER_WARN_USING_OTHER_HANDLER), + ha_resolve_storage_engine_name(create_info.db_type), create_table->table_name); + } + else { + my_error(ER_DISABLED_STORAGE_ENGINE, MYF(0), + ha_resolve_storage_engine_name(create_info.db_type)); + return true; + } + } + bool res = false;