From e26b07dbc6d85733eaf655895648ec08163debb7 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Fri, 11 May 2018 18:17:26 +0300 Subject: Add a test case for a MySQL 5.7 bug that did not affect MariaDB --- mysql-test/suite/innodb/r/foreign_key.result | 11 +++++++++++ mysql-test/suite/innodb/t/foreign_key.test | 12 ++++++++++++ 2 files changed, 23 insertions(+) diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index e569fc7dba7..5838c3a1fd5 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -306,3 +306,14 @@ id member_id SELECT * FROM payment_method; id member_id cardholder_address_id DROP TABLE payment_method,address,member; +# +# Bug #26958695 INNODB NESTED STORED FIELD WITH CONSTRAINT KEY +# PRODUCE BROKEN TABLE (no bug in MariaDB) +# +create table t1(f1 int,f2 int, primary key(f1), key(f2, f1))engine=innodb; +create table t2(f1 int, f2 int as (2) stored, f3 int as (f2) stored, +foreign key(f1) references t1(f2) on update set NULL) +engine=innodb; +insert into t1 values(1, 1); +insert into t2(f1) values(1); +drop table t2, t1; diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index 862717647b5..b586f3e9406 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -276,4 +276,16 @@ SELECT * FROM payment_method; DROP TABLE payment_method,address,member; +--echo # +--echo # Bug #26958695 INNODB NESTED STORED FIELD WITH CONSTRAINT KEY +--echo # PRODUCE BROKEN TABLE (no bug in MariaDB) +--echo # +create table t1(f1 int,f2 int, primary key(f1), key(f2, f1))engine=innodb; +create table t2(f1 int, f2 int as (2) stored, f3 int as (f2) stored, + foreign key(f1) references t1(f2) on update set NULL) +engine=innodb; +insert into t1 values(1, 1); +insert into t2(f1) values(1); +drop table t2, t1; + --source include/wait_until_count_sessions.inc -- cgit v1.2.1 From 3d10966b7d5ed408f9c085f5ba9ed9b12a7317aa Mon Sep 17 00:00:00 2001 From: Sachin Agarwal Date: Tue, 5 Dec 2017 19:13:12 +0530 Subject: Bug #26731689 FK ON TABLE WITH GENERATED COLS: ASSERTION POS < N_DEF Problem: During ALTER, when filling stored column info, wrong column number is used. This is because we ignored virtual column when iterating over columns in table and lead to debug assertion. Fix: In InnoDB table cache object, vcols are on stored on one list, stored and normal columns are stored in another list. When looking for stored column, ignore the virtual columns to get the right column number of stored column. Reviewed by: Thiru , Satya RB: 17939 --- mysql-test/suite/innodb/r/stored_fk.result | 66 ++++++++++++++++++ mysql-test/suite/innodb/t/stored_fk.test | 105 +++++++++++++++++++++++++++++ storage/innobase/handler/handler0alter.cc | 10 ++- 3 files changed, 179 insertions(+), 2 deletions(-) create mode 100644 mysql-test/suite/innodb/r/stored_fk.result create mode 100644 mysql-test/suite/innodb/t/stored_fk.test diff --git a/mysql-test/suite/innodb/r/stored_fk.result b/mysql-test/suite/innodb/r/stored_fk.result new file mode 100644 index 00000000000..6fb1684497f --- /dev/null +++ b/mysql-test/suite/innodb/r/stored_fk.result @@ -0,0 +1,66 @@ +# Create statement with FK on base column of stored column +create table t1(f1 int, f2 int as(f1) stored, +foreign key(f1) references t2(f1) on delete cascade)engine=innodb; +ERROR HY000: Cannot add foreign key constraint +# adding new stored column during alter table copy operation. +create table t1(f1 int primary key); +create table t2(f1 int not null, f2 int as (f1) virtual, +foreign key(f1) references t1(f1) on update cascade)engine=innodb; +alter table t2 add column f3 int as (f1) stored, add column f4 int as (f1) virtual; +ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 150 - Foreign key constraint is incorrectly formed) +drop table t2, t1; +# adding foreign key constraint for base columns during alter copy. +create table t1(f1 int primary key); +create table t2(f1 int not null, f2 int as (f1) stored); +alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=copy; +ERROR HY000: Cannot add foreign key constraint +drop table t2, t1; +# adding foreign key constraint for base columns during online alter. +create table t1(f1 int primary key); +create table t2(f1 int not null, f2 int as (f1) stored); +set foreign_key_checks = 0; +alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=inplace; +ERROR HY000: Cannot add foreign key on the base column of stored column. +drop table t2, t1; +# adding stored column via online alter. +create table t1(f1 int primary key); +create table t2(f1 int not null, +foreign key(f1) references t1(f1) on update cascade)engine=innodb; +alter table t2 add column f2 int as (f1) stored, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +drop table t2, t1; +set foreign_key_checks = 1; +# +# BUG#26731689 FK ON TABLE WITH GENERATED COLS: ASSERTION POS < N_DEF +# +SET @foreign_key_checks_saved = @@foreign_key_checks; +SET foreign_key_checks=0; +DROP TABLE IF EXISTS s,t; +CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED, c INT, +d INT GENERATED ALWAYS AS (0) VIRTUAL, e INT) ENGINE=innodb; +CREATE TABLE t (a INT) ENGINE=innodb; +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (e) REFERENCES t(a) ON UPDATE SET null; +ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'c' in the referenced table 't' +ALTER TABLE t ADD PRIMARY KEY(a); +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (e) REFERENCES t(a) ON UPDATE SET null; +DROP TABLE s,t; +CREATE TABLE s (a INT GENERATED ALWAYS AS (0) VIRTUAL, +b INT GENERATED ALWAYS AS (0) STORED, c INT) ENGINE=innodb; +CREATE TABLE t (a INT) ENGINE=innodb; +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES t(a) ON UPDATE SET null; +ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'c' in the referenced table 't' +ALTER TABLE t ADD PRIMARY KEY(a); +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES t(a) ON UPDATE SET null; +DROP TABLE s,t; +CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED) ENGINE=innodb; +CREATE TABLE t (a INT PRIMARY KEY) ENGINE=innodb; +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; +DROP TABLE s,t; +CREATE TABLE s (a INT, b INT) ENGINE=innodb; +CREATE TABLE t (a INT) ENGINE=innodb; +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; +ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'c' in the referenced table 't' +ALTER TABLE t ADD PRIMARY KEY(a); +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; +DROP TABLE s,t; +SET @@foreign_key_checks = @foreign_key_checks_saved; diff --git a/mysql-test/suite/innodb/t/stored_fk.test b/mysql-test/suite/innodb/t/stored_fk.test new file mode 100644 index 00000000000..da8df775523 --- /dev/null +++ b/mysql-test/suite/innodb/t/stored_fk.test @@ -0,0 +1,105 @@ +--source include/have_innodb.inc + +--echo # Create statement with FK on base column of stored column +--error ER_CANNOT_ADD_FOREIGN +create table t1(f1 int, f2 int as(f1) stored, + foreign key(f1) references t2(f1) on delete cascade)engine=innodb; + +--echo # adding new stored column during alter table copy operation. +create table t1(f1 int primary key); +create table t2(f1 int not null, f2 int as (f1) virtual, + foreign key(f1) references t1(f1) on update cascade)engine=innodb; + +--replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/ +--error ER_ERROR_ON_RENAME +alter table t2 add column f3 int as (f1) stored, add column f4 int as (f1) virtual; +drop table t2, t1; + +--echo # adding foreign key constraint for base columns during alter copy. +create table t1(f1 int primary key); +create table t2(f1 int not null, f2 int as (f1) stored); +--error ER_CANNOT_ADD_FOREIGN +alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=copy; +drop table t2, t1; + +--echo # adding foreign key constraint for base columns during online alter. +create table t1(f1 int primary key); +create table t2(f1 int not null, f2 int as (f1) stored); +set foreign_key_checks = 0; +--error ER_CANNOT_ADD_FOREIGN_BASE_COL_STORED +alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=inplace; +drop table t2, t1; + +--echo # adding stored column via online alter. +create table t1(f1 int primary key); +create table t2(f1 int not null, + foreign key(f1) references t1(f1) on update cascade)engine=innodb; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t2 add column f2 int as (f1) stored, algorithm=inplace; +drop table t2, t1; +set foreign_key_checks = 1; + +--echo # +--echo # BUG#26731689 FK ON TABLE WITH GENERATED COLS: ASSERTION POS < N_DEF +--echo # + +SET @foreign_key_checks_saved = @@foreign_key_checks; +SET foreign_key_checks=0; + +--disable_warnings +DROP TABLE IF EXISTS s,t; +--enable_warnings + +CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED, c INT, + d INT GENERATED ALWAYS AS (0) VIRTUAL, e INT) ENGINE=innodb; + +CREATE TABLE t (a INT) ENGINE=innodb; + +# This would fail. No corresponding index +--error ER_FK_NO_INDEX_PARENT +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (e) REFERENCES t(a) ON UPDATE SET null; + +ALTER TABLE t ADD PRIMARY KEY(a); + +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (e) REFERENCES t(a) ON UPDATE SET null; + +DROP TABLE s,t; + +CREATE TABLE s (a INT GENERATED ALWAYS AS (0) VIRTUAL, + b INT GENERATED ALWAYS AS (0) STORED, c INT) ENGINE=innodb; + +CREATE TABLE t (a INT) ENGINE=innodb; + +# This would fail. No corresponding index +--error ER_FK_NO_INDEX_PARENT +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES t(a) ON UPDATE SET null; + +ALTER TABLE t ADD PRIMARY KEY(a); + +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES t(a) ON UPDATE SET null; + +DROP TABLE s,t; + +CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED) ENGINE=innodb; + +CREATE TABLE t (a INT PRIMARY KEY) ENGINE=innodb; + +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; + +DROP TABLE s,t; + +CREATE TABLE s (a INT, b INT) ENGINE=innodb; + +CREATE TABLE t (a INT) ENGINE=innodb; + +# This would fail. No corresponding index +--error ER_FK_NO_INDEX_PARENT +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; + +ALTER TABLE t ADD PRIMARY KEY(a); + +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; + +DROP TABLE s,t; + +SET @@foreign_key_checks = @foreign_key_checks_saved; diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index c483e2dea59..2ae4f65efc5 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -5434,18 +5434,24 @@ alter_fill_stored_column( dict_s_col_list** s_cols, mem_heap_t** s_heap) { - ulint n_cols = altered_table->s->fields; + ulint n_cols = altered_table->s->fields; + ulint stored_col_no = 0; for (ulint i = 0; i < n_cols; i++) { Field* field = altered_table->field[i]; dict_s_col_t s_col; + if (!innobase_is_v_fld(field)) { + stored_col_no++; + } + if (!innobase_is_s_fld(field)) { continue; } ulint num_base = 0; - dict_col_t* col = dict_table_get_nth_col(table, i); + dict_col_t* col = dict_table_get_nth_col(table, + stored_col_no); s_col.m_col = col; s_col.s_pos = i; -- cgit v1.2.1 From 671a37f60e6663a175368b134574c0274964589b Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Fri, 11 May 2018 15:08:08 +0300 Subject: Adjust the test case for MariaDB Note: it does not appear to cover the bug fix! The test will pass even if the Oracle Bug #26731689 fix is reverted. --- mysql-test/suite/innodb/r/stored_fk.result | 46 ++++++++++++++++++------------ mysql-test/suite/innodb/t/stored_fk.test | 37 +++++++++--------------- 2 files changed, 40 insertions(+), 43 deletions(-) diff --git a/mysql-test/suite/innodb/r/stored_fk.result b/mysql-test/suite/innodb/r/stored_fk.result index 6fb1684497f..35524d5a88f 100644 --- a/mysql-test/suite/innodb/r/stored_fk.result +++ b/mysql-test/suite/innodb/r/stored_fk.result @@ -1,41 +1,50 @@ # Create statement with FK on base column of stored column create table t1(f1 int, f2 int as(f1) stored, foreign key(f1) references t2(f1) on delete cascade)engine=innodb; -ERROR HY000: Cannot add foreign key constraint +ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") # adding new stored column during alter table copy operation. -create table t1(f1 int primary key); +create table t1(f1 int primary key) engine=innodb; create table t2(f1 int not null, f2 int as (f1) virtual, foreign key(f1) references t1(f1) on update cascade)engine=innodb; alter table t2 add column f3 int as (f1) stored, add column f4 int as (f1) virtual; -ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 150 - Foreign key constraint is incorrectly formed) -drop table t2, t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f1` int(11) NOT NULL, + `f2` int(11) GENERATED ALWAYS AS (`f1`) VIRTUAL, + `f3` int(11) GENERATED ALWAYS AS (`f1`) STORED, + `f4` int(11) GENERATED ALWAYS AS (`f1`) VIRTUAL, + KEY `f1` (`f1`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t2; # adding foreign key constraint for base columns during alter copy. -create table t1(f1 int primary key); -create table t2(f1 int not null, f2 int as (f1) stored); +create table t2(f1 int not null, f2 int as (f1) stored) engine=innodb; alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=copy; -ERROR HY000: Cannot add foreign key constraint -drop table t2, t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f1` int(11) NOT NULL, + `f2` int(11) GENERATED ALWAYS AS (`f1`) STORED, + KEY `f1` (`f1`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t2; # adding foreign key constraint for base columns during online alter. -create table t1(f1 int primary key); -create table t2(f1 int not null, f2 int as (f1) stored); +create table t2(f1 int not null, f2 int as (f1) stored) engine=innodb; set foreign_key_checks = 0; alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=inplace; -ERROR HY000: Cannot add foreign key on the base column of stored column. -drop table t2, t1; +ERROR 0A000: Cannot add foreign key on the base column of stored column +drop table t2; # adding stored column via online alter. -create table t1(f1 int primary key); create table t2(f1 int not null, foreign key(f1) references t1(f1) on update cascade)engine=innodb; alter table t2 add column f2 int as (f1) stored, algorithm=inplace; -ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY drop table t2, t1; -set foreign_key_checks = 1; # # BUG#26731689 FK ON TABLE WITH GENERATED COLS: ASSERTION POS < N_DEF # -SET @foreign_key_checks_saved = @@foreign_key_checks; -SET foreign_key_checks=0; -DROP TABLE IF EXISTS s,t; CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED, c INT, d INT GENERATED ALWAYS AS (0) VIRTUAL, e INT) ENGINE=innodb; CREATE TABLE t (a INT) ENGINE=innodb; @@ -63,4 +72,3 @@ ERROR HY000: Failed to add the foreign key constaint. Missing index for constrai ALTER TABLE t ADD PRIMARY KEY(a); ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; DROP TABLE s,t; -SET @@foreign_key_checks = @foreign_key_checks_saved; diff --git a/mysql-test/suite/innodb/t/stored_fk.test b/mysql-test/suite/innodb/t/stored_fk.test index da8df775523..b9c7c934555 100644 --- a/mysql-test/suite/innodb/t/stored_fk.test +++ b/mysql-test/suite/innodb/t/stored_fk.test @@ -1,55 +1,46 @@ --source include/have_innodb.inc --echo # Create statement with FK on base column of stored column ---error ER_CANNOT_ADD_FOREIGN +--error ER_CANT_CREATE_TABLE create table t1(f1 int, f2 int as(f1) stored, foreign key(f1) references t2(f1) on delete cascade)engine=innodb; --echo # adding new stored column during alter table copy operation. -create table t1(f1 int primary key); +create table t1(f1 int primary key) engine=innodb; create table t2(f1 int not null, f2 int as (f1) virtual, foreign key(f1) references t1(f1) on update cascade)engine=innodb; ---replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/ ---error ER_ERROR_ON_RENAME +# MySQL 5.7 would refuse this +#--error ER_ERROR_ON_RENAME alter table t2 add column f3 int as (f1) stored, add column f4 int as (f1) virtual; -drop table t2, t1; +show create table t2; +drop table t2; --echo # adding foreign key constraint for base columns during alter copy. -create table t1(f1 int primary key); -create table t2(f1 int not null, f2 int as (f1) stored); ---error ER_CANNOT_ADD_FOREIGN +create table t2(f1 int not null, f2 int as (f1) stored) engine=innodb; +# MySQL 5.7 would refuse this alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=copy; -drop table t2, t1; +show create table t2; +drop table t2; --echo # adding foreign key constraint for base columns during online alter. -create table t1(f1 int primary key); -create table t2(f1 int not null, f2 int as (f1) stored); +create table t2(f1 int not null, f2 int as (f1) stored) engine=innodb; set foreign_key_checks = 0; ---error ER_CANNOT_ADD_FOREIGN_BASE_COL_STORED +--error 138 alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=inplace; -drop table t2, t1; +drop table t2; --echo # adding stored column via online alter. -create table t1(f1 int primary key); create table t2(f1 int not null, foreign key(f1) references t1(f1) on update cascade)engine=innodb; --error ER_ALTER_OPERATION_NOT_SUPPORTED alter table t2 add column f2 int as (f1) stored, algorithm=inplace; drop table t2, t1; -set foreign_key_checks = 1; --echo # --echo # BUG#26731689 FK ON TABLE WITH GENERATED COLS: ASSERTION POS < N_DEF --echo # -SET @foreign_key_checks_saved = @@foreign_key_checks; -SET foreign_key_checks=0; - ---disable_warnings -DROP TABLE IF EXISTS s,t; ---enable_warnings - CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED, c INT, d INT GENERATED ALWAYS AS (0) VIRTUAL, e INT) ENGINE=innodb; @@ -101,5 +92,3 @@ ALTER TABLE t ADD PRIMARY KEY(a); ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; DROP TABLE s,t; - -SET @@foreign_key_checks = @foreign_key_checks_saved; -- cgit v1.2.1 From b7e333f98a711931cbcbb444302fcbddcefb6f10 Mon Sep 17 00:00:00 2001 From: Sachin Agarwal Date: Mon, 18 Dec 2017 18:40:08 +0530 Subject: Bug #26805833 INNODB COMPLAINS OF SYNTAX ERROR, BUT DOES NOT SAY WHICH OPTION Problem: when incorrect value is assigned to innodb_data_file_path or innodb_temp_data_file_path parameter, Innodb returns error and logs error message in mysqlds.err file but there is no information in error message about the parameter which causes Innodb initialization is failed. Fix: Added error message with parameter name and value, which causes Innodb initialization is failed. Reviewed by: Jimmy RB: 18206 --- mysql-test/suite/innodb/t/temporary_table.test | 1 + storage/innobase/handler/ha_innodb.cc | 4 ++++ 2 files changed, 5 insertions(+) diff --git a/mysql-test/suite/innodb/t/temporary_table.test b/mysql-test/suite/innodb/t/temporary_table.test index 9a640657c4d..db87367dbb9 100644 --- a/mysql-test/suite/innodb/t/temporary_table.test +++ b/mysql-test/suite/innodb/t/temporary_table.test @@ -21,6 +21,7 @@ call mtr.add_suppression("InnoDB: Plugin initialization aborted"); call mtr.add_suppression("innodb_temporary and innodb_system file names seem to be the same"); call mtr.add_suppression("Could not create the shared innodb_temporary"); call mtr.add_suppression("InnoDB: syntax error in file path"); +call mtr.add_suppression("InnoDB: Unable to parse innodb_temp_data_file_path="); --enable_query_log let $MYSQL_TMP_DIR = `select @@tmpdir`; diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 51a8e3abaf0..b74c886c692 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -3932,6 +3932,8 @@ innobase_init( /* Supports raw devices */ if (!srv_sys_space.parse_params(innobase_data_file_path, true)) { + ib::error() << "Unable to parse innodb_data_file_path=" + << innobase_data_file_path; DBUG_RETURN(innobase_init_abort()); } @@ -3950,6 +3952,8 @@ innobase_init( srv_tmp_space.set_flags(FSP_FLAGS_PAGE_SSIZE()); if (!srv_tmp_space.parse_params(innobase_temp_data_file_path, false)) { + ib::error() << "Unable to parse innodb_temp_data_file_path=" + << innobase_temp_data_file_path; DBUG_RETURN(innobase_init_abort()); } -- cgit v1.2.1 From 280879ebd97b286a1ac72e79148badddce9e6e29 Mon Sep 17 00:00:00 2001 From: Aditya A Date: Mon, 29 Jan 2018 17:17:21 +0530 Subject: Bug #27304661 MYSQL CRASH DOING SYNC INDEX ] [FATAL] INNODB: SEMAPHORE WAIT HAS LASTED > 600 PROBLEM ------- Whenever an fts table is created it registers itself in a queue which is operated by a background thread whose job is to optimize the fts tables in background. Additionally we place these fts tables in non-LRU list so that they cannot be evicted from cache. But in the scenario when a node is brought up which is already having fts tables ,we first try to load the fts tables in dictionary ,but we skip the part where it is added in background queue and in non-LRU list because the background thread is not yet created,so these tables are loaded but they can be evicted from the cache. Now coming to the deadlock scenario 1. A Server background thread is trying to evict a table from the cache because the cache is full,so it scans the LRU list for the tables it can evict.It finds the fts table (because of the reason explained above) can be evicted and it takes the dict_sys->mutex (this is a system wide mutex) submits a request to the background thread to remove this table from queue and waits it to be completed. 2. In the mean time fts_optimize_thread() is processing another job in the queue and needs dict_sys->mutex for a small amount of time, but it cannot get it because it is blocked by the first background thread. So Thread 1 is waiting for its job to be completed by Thread 2,whereas Thread 2 is waiting for dict_sys->mutex held by thread 1 ,causing the deadlock. FIX --- .../suite/innodb_fts/r/fulltext_table_evict.result | 19 ++++++ .../suite/innodb_fts/t/fulltext_table_evict.test | 47 +++++++++++++ storage/innobase/dict/dict0dict.cc | 9 ++- storage/innobase/fts/fts0opt.cc | 78 ++++++++++++++++------ 4 files changed, 132 insertions(+), 21 deletions(-) create mode 100644 mysql-test/suite/innodb_fts/r/fulltext_table_evict.result create mode 100644 mysql-test/suite/innodb_fts/t/fulltext_table_evict.test diff --git a/mysql-test/suite/innodb_fts/r/fulltext_table_evict.result b/mysql-test/suite/innodb_fts/r/fulltext_table_evict.result new file mode 100644 index 00000000000..410bf2a34e1 --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/fulltext_table_evict.result @@ -0,0 +1,19 @@ +# +# Bug Bug #27304661 MYSQL CRASH DOING SYNC INDEX ] +# [FATAL] INNODB: SEMAPHORE WAIT HAS LASTED > 600 +# +CREATE TABLE t1 ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +f1 TEXT(500), +FULLTEXT idx (f1) +) ENGINE=InnoDB; +insert into t1 (f1) values ('fjdhfsjhf'),('dhjfhjshfj'),('dhjafjhfj'); +# restart +set @save_table_definition_cache=@@global.table_definition_cache; +set @save_table_open_cache=@@global.table_open_cache; +set global table_definition_cache=400; +set global table_open_cache= 1024; +SET GLOBAL DEBUG="+d,crash_if_fts_table_is_evicted"; +set @@global.table_definition_cache=@save_table_definition_cache; +set @@global.table_open_cache=@save_table_open_cache; +drop table t1; diff --git a/mysql-test/suite/innodb_fts/t/fulltext_table_evict.test b/mysql-test/suite/innodb_fts/t/fulltext_table_evict.test new file mode 100644 index 00000000000..245f00bc336 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/fulltext_table_evict.test @@ -0,0 +1,47 @@ +--echo # +--echo # Bug Bug #27304661 MYSQL CRASH DOING SYNC INDEX ] +--echo # [FATAL] INNODB: SEMAPHORE WAIT HAS LASTED > 600 +--echo # + +--source include/have_innodb.inc +--source include/have_debug.inc + +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + f1 TEXT(500), + FULLTEXT idx (f1) + ) ENGINE=InnoDB; +insert into t1 (f1) values ('fjdhfsjhf'),('dhjfhjshfj'),('dhjafjhfj'); + +--source include/restart_mysqld.inc + +set @save_table_definition_cache=@@global.table_definition_cache; +set @save_table_open_cache=@@global.table_open_cache; + +set global table_definition_cache=400; +set global table_open_cache= 1024; + +SET GLOBAL DEBUG="+d,crash_if_fts_table_is_evicted"; +#Create 1000 tables, try the best to evict t1 . + +--disable_query_log +let $loop=1000; +while($loop) +{ + eval create table t_$loop(id int, name text(100), fulltext idxt_$loop(name) )engine=innodb; + dec $loop; +} + +let $loop=1000; +while($loop) +{ + eval drop table t_$loop; + dec $loop; +} + +SET GLOBAL DEBUG="-d,crash_if_fts_table_is_evicted"; +--enable_query_log +set @@global.table_definition_cache=@save_table_definition_cache; +set @@global.table_open_cache=@save_table_open_cache; +drop table t1; + diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc index ac5357ded3a..ff8b3d75010 100644 --- a/storage/innobase/dict/dict0dict.cc +++ b/storage/innobase/dict/dict0dict.cc @@ -1,6 +1,6 @@ /***************************************************************************** -Copyright (c) 1996, 2016, Oracle and/or its affiliates. All Rights Reserved. +Copyright (c) 1996, 2018, Oracle and/or its affiliates. All Rights Reserved. Copyright (c) 2012, Facebook Inc. Copyright (c) 2013, 2018, MariaDB Corporation. @@ -1453,6 +1453,13 @@ dict_make_room_in_cache( if (dict_table_can_be_evicted(table)) { + DBUG_EXECUTE_IF("crash_if_fts_table_is_evicted", + { + if (table->fts && + dict_table_has_fts_index(table)) { + ut_ad(0); + } + };); dict_table_remove_from_cache_low(table, TRUE); ++n_evicted; diff --git a/storage/innobase/fts/fts0opt.cc b/storage/innobase/fts/fts0opt.cc index 910721d8b32..cb1df7082f7 100644 --- a/storage/innobase/fts/fts0opt.cc +++ b/storage/innobase/fts/fts0opt.cc @@ -1,6 +1,6 @@ /***************************************************************************** -Copyright (c) 2007, 2017, Oracle and/or its affiliates. All Rights Reserved. +Copyright (c) 2007, 2018, Oracle and/or its affiliates. All Rights Reserved. Copyright (c) 2016, 2018, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under @@ -41,6 +41,9 @@ Completed 2011/7/10 Sunny and Jimmy Yang /** The FTS optimize thread's work queue. */ static ib_wqueue_t* fts_optimize_wq; +/** The FTS vector to store fts_slot_t */ +static ib_vector_t* fts_slots; + /** Time to wait for a message. */ static const ulint FTS_QUEUE_WAIT_IN_USECS = 5000000; @@ -2976,9 +2979,6 @@ fts_optimize_thread( /*================*/ void* arg) /*!< in: work queue*/ { - mem_heap_t* heap; - ib_vector_t* tables; - ib_alloc_t* heap_alloc; ulint current = 0; ibool done = FALSE; ulint n_tables = 0; @@ -2988,10 +2988,10 @@ fts_optimize_thread( ut_ad(!srv_read_only_mode); my_thread_init(); - heap = mem_heap_create(sizeof(dict_table_t*) * 64); - heap_alloc = ib_heap_allocator_create(heap); + ut_ad(fts_slots); - tables = ib_vector_create(heap_alloc, sizeof(fts_slot_t), 4); + /* Assign number of tables added in fts_slots_t to n_tables */ + n_tables = ib_vector_size(fts_slots); while (!done && srv_shutdown_state == SRV_SHUTDOWN_NONE) { @@ -3005,10 +3005,10 @@ fts_optimize_thread( fts_slot_t* slot; - ut_a(ib_vector_size(tables) > 0); + ut_a(ib_vector_size(fts_slots) > 0); slot = static_cast( - ib_vector_get(tables, current)); + ib_vector_get(fts_slots, current)); /* Handle the case of empty slots. */ if (slot->state != FTS_STATE_EMPTY) { @@ -3021,8 +3021,8 @@ fts_optimize_thread( ++current; /* Wrap around the counter. */ - if (current >= ib_vector_size(tables)) { - n_optimize = fts_optimize_how_many(tables); + if (current >= ib_vector_size(fts_slots)) { + n_optimize = fts_optimize_how_many(fts_slots); current = 0; } @@ -3036,7 +3036,7 @@ fts_optimize_thread( /* Timeout ? */ if (msg == NULL) { - if (fts_is_sync_needed(tables)) { + if (fts_is_sync_needed(fts_slots)) { fts_need_sync = true; } @@ -3057,7 +3057,7 @@ fts_optimize_thread( case FTS_MSG_ADD_TABLE: ut_a(!done); if (fts_optimize_new_table( - tables, + fts_slots, static_cast( msg->ptr))) { ++n_tables; @@ -3067,7 +3067,7 @@ fts_optimize_thread( case FTS_MSG_OPTIMIZE_TABLE: if (!done) { fts_optimize_start_table( - tables, + fts_slots, static_cast( msg->ptr)); } @@ -3075,7 +3075,7 @@ fts_optimize_thread( case FTS_MSG_DEL_TABLE: if (fts_optimize_del_table( - tables, static_cast( + fts_slots, static_cast( msg->ptr))) { --n_tables; } @@ -3098,7 +3098,7 @@ fts_optimize_thread( mem_heap_free(msg->heap); if (!done) { - n_optimize = fts_optimize_how_many(tables); + n_optimize = fts_optimize_how_many(fts_slots); } else { n_optimize = 0; } @@ -3110,11 +3110,11 @@ fts_optimize_thread( if (n_tables > 0) { ulint i; - for (i = 0; i < ib_vector_size(tables); i++) { + for (i = 0; i < ib_vector_size(fts_slots); i++) { fts_slot_t* slot; slot = static_cast( - ib_vector_get(tables, i)); + ib_vector_get(fts_slots, i)); if (slot->state != FTS_STATE_EMPTY) { fts_optimize_sync_table(slot->table_id); @@ -3122,7 +3122,7 @@ fts_optimize_thread( } } - ib_vector_free(tables); + ib_vector_free(fts_slots); ib::info() << "FTS optimize thread exiting."; @@ -3142,14 +3142,52 @@ void fts_optimize_init(void) /*===================*/ { + mem_heap_t* heap; + ib_alloc_t* heap_alloc; + dict_table_t* table; + ut_ad(!srv_read_only_mode); /* For now we only support one optimize thread. */ ut_a(fts_optimize_wq == NULL); + /* Create FTS optimize work queue */ fts_optimize_wq = ib_wqueue_create(); - fts_opt_shutdown_event = os_event_create(0); ut_a(fts_optimize_wq != NULL); + + /* Create FTS vector to store fts_slot_t */ + heap = mem_heap_create(sizeof(dict_table_t*) * 64); + heap_alloc = ib_heap_allocator_create(heap); + fts_slots = ib_vector_create(heap_alloc, sizeof(fts_slot_t), 4); + + /* Add fts tables to the fts_slots vector which were skipped during restart */ + std::vector table_vector; + std::vector::iterator it; + + mutex_enter(&dict_sys->mutex); + for (table = UT_LIST_GET_FIRST(dict_sys->table_LRU); + table != NULL; + table = UT_LIST_GET_NEXT(table_LRU, table)) { + if (table->fts && + dict_table_has_fts_index(table)) { + if (fts_optimize_new_table(fts_slots, + table)){ + table_vector.push_back(table); + } + } + } + + /* It is better to call dict_table_prevent_eviction() + outside the above loop because it operates on + dict_sys->table_LRU list.*/ + for (it=table_vector.begin();it!=table_vector.end();++it) { + dict_table_prevent_eviction(*it); + } + + mutex_exit(&dict_sys->mutex); + table_vector.clear(); + + fts_opt_shutdown_event = os_event_create(0); last_check_sync_time = ut_time(); os_thread_create(fts_optimize_thread, fts_optimize_wq, NULL); -- cgit v1.2.1 From c88ac735b131328657ef0ed402fca2a4a1e4f766 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Fri, 11 May 2018 16:56:02 +0300 Subject: Adjust the test case for MariaDB --- mysql-test/suite/innodb_fts/r/fulltext_table_evict.result | 4 ++-- mysql-test/suite/innodb_fts/t/fulltext_table_evict.test | 13 +++++++------ 2 files changed, 9 insertions(+), 8 deletions(-) diff --git a/mysql-test/suite/innodb_fts/r/fulltext_table_evict.result b/mysql-test/suite/innodb_fts/r/fulltext_table_evict.result index 410bf2a34e1..d9d329aa6c0 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext_table_evict.result +++ b/mysql-test/suite/innodb_fts/r/fulltext_table_evict.result @@ -8,12 +8,12 @@ f1 TEXT(500), FULLTEXT idx (f1) ) ENGINE=InnoDB; insert into t1 (f1) values ('fjdhfsjhf'),('dhjfhjshfj'),('dhjafjhfj'); -# restart set @save_table_definition_cache=@@global.table_definition_cache; set @save_table_open_cache=@@global.table_open_cache; set global table_definition_cache=400; set global table_open_cache= 1024; -SET GLOBAL DEBUG="+d,crash_if_fts_table_is_evicted"; +SET @save_dbug = @@GLOBAL.debug_dbug; +SET GLOBAL DEBUG_DBUG="+d,crash_if_fts_table_is_evicted"; set @@global.table_definition_cache=@save_table_definition_cache; set @@global.table_open_cache=@save_table_open_cache; drop table t1; diff --git a/mysql-test/suite/innodb_fts/t/fulltext_table_evict.test b/mysql-test/suite/innodb_fts/t/fulltext_table_evict.test index 245f00bc336..2e7aa655aa1 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_table_evict.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_table_evict.test @@ -1,11 +1,12 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/big_test.inc + --echo # --echo # Bug Bug #27304661 MYSQL CRASH DOING SYNC INDEX ] --echo # [FATAL] INNODB: SEMAPHORE WAIT HAS LASTED > 600 --echo # ---source include/have_innodb.inc ---source include/have_debug.inc - CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, f1 TEXT(500), @@ -21,7 +22,8 @@ set @save_table_open_cache=@@global.table_open_cache; set global table_definition_cache=400; set global table_open_cache= 1024; -SET GLOBAL DEBUG="+d,crash_if_fts_table_is_evicted"; +SET @save_dbug = @@GLOBAL.debug_dbug; +SET GLOBAL DEBUG_DBUG="+d,crash_if_fts_table_is_evicted"; #Create 1000 tables, try the best to evict t1 . --disable_query_log @@ -39,9 +41,8 @@ while($loop) dec $loop; } -SET GLOBAL DEBUG="-d,crash_if_fts_table_is_evicted"; +SET GLOBAL DEBUG_DBUG = @save_dbug; --enable_query_log set @@global.table_definition_cache=@save_table_definition_cache; set @@global.table_open_cache=@save_table_open_cache; drop table t1; - -- cgit v1.2.1 From 279f992b85bf024d4d6031a79bf41741da73c949 Mon Sep 17 00:00:00 2001 From: Aakanksha Verma Date: Mon, 29 Jan 2018 18:47:23 +0530 Subject: Bug #27141613 ASSERTION: TRX0REC.CC:319:COL->IS_VIRTUAL() / CRASH IN TRX_UNDO_READ_V_COLS PROBLEM ======= When add of virtual index fails with DB_TOO_BIG_RECORD , the virtual index being freed isn't removed from the list of indexes a virtual column(which is part of the index). This while the undo log is read could fetch a wrong value during rollback and cause the assertion reported in the bug particularly. FIX === Added a function that is called when the virtual index being freed would allow the index be removed from the index list of virtual column which was a field of that index. Reviwed By: Jimmy Yang RB: 18528 --- storage/innobase/dict/dict0dict.cc | 35 +++++++++++++++++++++++++++++++++++ storage/innobase/dict/dict0mem.cc | 3 ++- storage/innobase/include/dict0dict.h | 8 +++++++- 3 files changed, 44 insertions(+), 2 deletions(-) diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc index ff8b3d75010..42378d5520d 100644 --- a/storage/innobase/dict/dict0dict.cc +++ b/storage/innobase/dict/dict0dict.cc @@ -2428,6 +2428,41 @@ dict_index_add_to_cache( table, index, NULL, page_no, strict)); } +/** Clears the virtual column's index list before index is +being freed. +@param[in] index Index being freed */ +void +dict_index_remove_from_v_col_list(dict_index_t* index) { + + if (dict_index_has_virtual(index)) { + const dict_col_t* col; + const dict_v_col_t* vcol; + + for (ulint i = 0; i < dict_index_get_n_fields(index); i++) { + col = dict_index_get_nth_col(index, i); + if (dict_col_is_virtual(col)) { + vcol = reinterpret_cast( + col); + /* This could be NULL, when we do add + virtual column, add index together. We do not + need to track this virtual column's index */ + if (vcol->v_indexes == NULL) { + continue; + } + dict_v_idx_list::iterator it; + for (it = vcol->v_indexes->begin(); + it != vcol->v_indexes->end(); ++it) { + dict_v_idx_t v_index = *it; + if (v_index.index == index) { + vcol->v_indexes->erase(it); + break; + } + } + } + } + } +} + /** Adds an index to the dictionary cache, with possible indexing newly added column. @param[in,out] table table on which the index is diff --git a/storage/innobase/dict/dict0mem.cc b/storage/innobase/dict/dict0mem.cc index 7ade7735048..dcf52c93de1 100644 --- a/storage/innobase/dict/dict0mem.cc +++ b/storage/innobase/dict/dict0mem.cc @@ -1,6 +1,6 @@ /***************************************************************************** -Copyright (c) 1996, 2016, Oracle and/or its affiliates. All Rights Reserved. +Copyright (c) 1996, 2018, Oracle and/or its affiliates. All Rights Reserved. Copyright (c) 2012, Facebook Inc. Copyright (c) 2013, 2018, MariaDB Corporation. @@ -1034,6 +1034,7 @@ dict_mem_index_free( UT_DELETE(index->rtr_track->rtr_active); } + dict_index_remove_from_v_col_list(index); mem_heap_free(index->heap); } diff --git a/storage/innobase/include/dict0dict.h b/storage/innobase/include/dict0dict.h index 0592b64b669..51ce248b98d 100644 --- a/storage/innobase/include/dict0dict.h +++ b/storage/innobase/include/dict0dict.h @@ -1,6 +1,6 @@ /***************************************************************************** -Copyright (c) 1996, 2016, Oracle and/or its affiliates. All Rights Reserved. +Copyright (c) 1996, 2018, Oracle and/or its affiliates. All Rights Reserved. Copyright (c) 2012, Facebook Inc. Copyright (c) 2013, 2018, MariaDB Corporation. @@ -1121,6 +1121,12 @@ dict_index_add_to_cache( ibool strict) MY_ATTRIBUTE((warn_unused_result)); +/** Clears the virtual column's index list before index is being freed. +@param[in] index Index being freed */ +void +dict_index_remove_from_v_col_list( + dict_index_t* index); + /** Adds an index to the dictionary cache, with possible indexing newly added column. @param[in] table table on which the index is -- cgit v1.2.1 From 4c7ea34ef691e1a2ed243959de016abb3df32611 Mon Sep 17 00:00:00 2001 From: Aakanksha Verma Date: Thu, 8 Feb 2018 01:25:10 +0530 Subject: FOLLOW-UP FIX FOR BUG#27141613 PROBLEM Issue found during ntest run is a regression of Bug #27141613. The issue is basically when index is being freed due to an error during its creation,when the index isn't added to dictionary cache its field columns are not set, the derefrencing of null col pointer during the clean of index from the virtual column's leads to a crash. NOTE: Also test i_innodb.virtual_debug was failing on 32k page size and above for the newly added scenario. Fixed that. FIX Added a check that if only the index is cached , the virtual index freeing from the virtual cols index list is performed. Reviewed by: Satya Bodapati RB: 18670 --- storage/innobase/dict/dict0dict.cc | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc index 42378d5520d..4751add93d5 100644 --- a/storage/innobase/dict/dict0dict.cc +++ b/storage/innobase/dict/dict0dict.cc @@ -2433,7 +2433,10 @@ being freed. @param[in] index Index being freed */ void dict_index_remove_from_v_col_list(dict_index_t* index) { - + /* Index is not completely formed */ + if (!index->cached) { + return; + } if (dict_index_has_virtual(index)) { const dict_col_t* col; const dict_v_col_t* vcol; -- cgit v1.2.1 From 0da98472b70d869c719e62a281a89da5d9700178 Mon Sep 17 00:00:00 2001 From: Sachin Agarwal Date: Wed, 14 Feb 2018 12:51:05 +0530 Subject: Bug #23593654 CRASH IN BUF_BLOCK_FROM_AHI WHEN LARGE PAGES AND AHI ARE ENABLED Problem: Fix for Bug #21348684 (#Rb9581) introduced a conditional debug execute 'buf_pool_resize_chunk_null', which causes new chunks memory for 2nd buffer pool instance is freed. Buffer pool resize function removes all old chunks entry from 'buf_chunk_map_reg' and add new chunks entry into it. But when 'buf_pool_resize_chunk_null' is set true, 2nd buffer pool instance's chunk entries are not added into 'buf_chunk_map_reg'. When purge thread tries to access that buffer chunk, it leads to debug assertion. Fix: Added old chunk entries into 'buf_chunk_map_reg' for 2nd buffer pool instance when 'buf_pool_resize_chunk_null' debug condition is set to true. Reviewed by: Jimmy RB: 18664 --- storage/innobase/buf/buf0buf.cc | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) diff --git a/storage/innobase/buf/buf0buf.cc b/storage/innobase/buf/buf0buf.cc index 3aa99dfd7d2..2f385b6f8e6 100644 --- a/storage/innobase/buf/buf0buf.cc +++ b/storage/innobase/buf/buf0buf.cc @@ -1,6 +1,6 @@ /***************************************************************************** -Copyright (c) 1995, 2016, Oracle and/or its affiliates. All Rights Reserved. +Copyright (c) 1995, 2018, Oracle and/or its affiliates. All Rights Reserved. Copyright (c) 2008, Google Inc. Copyright (c) 2013, 2018, MariaDB Corporation. @@ -2869,6 +2869,9 @@ withdraw_retry: = buf_pool->n_chunks; warning = true; buf_pool->chunks_old = NULL; + for (ulint j = 0; j < buf_pool->n_chunks_new; j++) { + buf_pool_register_chunk(&(buf_pool->chunks[j])); + } goto calc_buf_pool_size; } -- cgit v1.2.1