From a408e881cf73d06fc92097fce6ef9584e16edf77 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 10 Jan 2018 09:17:43 +0200 Subject: MDEV-14174 crash on start with innodb-track-changed-pages The XtraDB option innodb_track_changed_pages causes the function log_group_read_log_seg() to be invoked even when recv_sys==NULL, leading to the SIGSEGV. This regression was caused by MDEV-11027 InnoDB log recovery is too noisy --- mysql-test/suite/innodb/t/innodb-master.opt | 1 + storage/xtradb/log/log0log.c | 16 +++++++++------- 2 files changed, 10 insertions(+), 7 deletions(-) diff --git a/mysql-test/suite/innodb/t/innodb-master.opt b/mysql-test/suite/innodb/t/innodb-master.opt index 5266978e4f0..8d9b0302a9e 100644 --- a/mysql-test/suite/innodb/t/innodb-master.opt +++ b/mysql-test/suite/innodb/t/innodb-master.opt @@ -2,3 +2,4 @@ --default-storage-engine=MyISAM --innodb-strict-mode=0 --innodb-file-per-table=0 +--loose-innodb-track-changed-pages diff --git a/storage/xtradb/log/log0log.c b/storage/xtradb/log/log0log.c index e327fa7ea9a..bed7e31da28 100644 --- a/storage/xtradb/log/log0log.c +++ b/storage/xtradb/log/log0log.c @@ -2503,7 +2503,6 @@ log_group_read_log_seg( ulint len; ulint source_offset; ibool sync; - ib_time_t time; ut_ad(mutex_own(&(log_sys->mutex))); @@ -2540,13 +2539,16 @@ loop: start_lsn += len; buf += len; - time = ut_time(); + if (recv_sys) { + ib_time_t time = ut_time(); - if (recv_sys->progress_time - time >= 15) { - recv_sys->progress_time = time; - ut_print_timestamp(stderr); - fprintf(stderr, " InnoDB: Read redo log up to LSN=%llu\n", - start_lsn); + if (recv_sys->progress_time - time >= 15) { + recv_sys->progress_time = time; + ut_print_timestamp(stderr); + fprintf(stderr, + " InnoDB: Read redo log up to LSN=%llu\n", + start_lsn); + } } if (start_lsn != end_lsn) { -- cgit v1.2.1 From a9c55c0059351220674a1dd3d3bf8d4e884b3285 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 10 Jan 2018 10:21:52 +0200 Subject: MDEV-13814 Extra logging when innodb_log_archive=ON Backport the fix from 10.0.33 to 5.5, in case someone compiles XtraDB with -DUNIV_LOG_ARCHIVE --- mysql-test/suite/innodb/t/innodb-master.opt | 1 + storage/xtradb/log/log0log.c | 2 +- 2 files changed, 2 insertions(+), 1 deletion(-) diff --git a/mysql-test/suite/innodb/t/innodb-master.opt b/mysql-test/suite/innodb/t/innodb-master.opt index 8d9b0302a9e..2e71d62206d 100644 --- a/mysql-test/suite/innodb/t/innodb-master.opt +++ b/mysql-test/suite/innodb/t/innodb-master.opt @@ -3,3 +3,4 @@ --innodb-strict-mode=0 --innodb-file-per-table=0 --loose-innodb-track-changed-pages +--loose-innodb-log-archive diff --git a/storage/xtradb/log/log0log.c b/storage/xtradb/log/log0log.c index bed7e31da28..1f9003aa66e 100644 --- a/storage/xtradb/log/log0log.c +++ b/storage/xtradb/log/log0log.c @@ -2539,7 +2539,7 @@ loop: start_lsn += len; buf += len; - if (recv_sys) { + if (recv_recovery_is_on()) { ib_time_t time = ut_time(); if (recv_sys->progress_time - time >= 15) { -- cgit v1.2.1 From 9c9cf556a15af84f9133ef859b50596085f7ae8e Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Fri, 6 Oct 2017 17:52:35 +0200 Subject: MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in Roll back to most general duplicate removing strategi in case of different stratagies for one position. --- mysql-test/r/subselect.result | 26 +++++++++++++ mysql-test/r/subselect_no_mat.result | 26 +++++++++++++ mysql-test/r/subselect_no_opts.result | 26 +++++++++++++ mysql-test/r/subselect_no_scache.result | 26 +++++++++++++ mysql-test/r/subselect_no_semijoin.result | 26 +++++++++++++ mysql-test/t/subselect.test | 28 ++++++++++++++ sql/opt_subselect.cc | 64 +++++++++++++++++++++++-------- 7 files changed, 206 insertions(+), 16 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 8fc9cd38728..e2f2b6521c8 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7146,3 +7146,29 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; (SELECT MAX(sq.f2) FROM t1) NULL drop table t1, t2; +# +# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in +# (5.5 test) +# +SET @optimiser_switch_save= @@optimizer_switch; +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (1),(1),(1),(5),(5); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (5),(1); +CREATE TABLE t3 (c INT, KEY(c)); +INSERT INTO t3 VALUES (5),(5); +SET optimizer_switch='semijoin=on'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); +a +5 +5 +SET optimizer_switch='semijoin=off'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); +a +5 +5 +SET @@optimizer_switch= @optimiser_switch_save; +DROP TABLE t1, t2, t3; +End of 5.5 tests diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index a71df97e6bc..25ef4a76962 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7143,6 +7143,32 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; (SELECT MAX(sq.f2) FROM t1) NULL drop table t1, t2; +# +# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in +# (5.5 test) +# +SET @optimiser_switch_save= @@optimizer_switch; +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (1),(1),(1),(5),(5); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (5),(1); +CREATE TABLE t3 (c INT, KEY(c)); +INSERT INTO t3 VALUES (5),(5); +SET optimizer_switch='semijoin=on'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); +a +5 +5 +SET optimizer_switch='semijoin=off'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); +a +5 +5 +SET @@optimizer_switch= @optimiser_switch_save; +DROP TABLE t1, t2, t3; +End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 2f8c67fa167..074874fbd5b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7141,4 +7141,30 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; (SELECT MAX(sq.f2) FROM t1) NULL drop table t1, t2; +# +# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in +# (5.5 test) +# +SET @optimiser_switch_save= @@optimizer_switch; +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (1),(1),(1),(5),(5); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (5),(1); +CREATE TABLE t3 (c INT, KEY(c)); +INSERT INTO t3 VALUES (5),(5); +SET optimizer_switch='semijoin=on'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); +a +5 +5 +SET optimizer_switch='semijoin=off'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); +a +5 +5 +SET @@optimizer_switch= @optimiser_switch_save; +DROP TABLE t1, t2, t3; +End of 5.5 tests set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 8dda0a4fd36..de49585b562 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7152,6 +7152,32 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; (SELECT MAX(sq.f2) FROM t1) NULL drop table t1, t2; +# +# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in +# (5.5 test) +# +SET @optimiser_switch_save= @@optimizer_switch; +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (1),(1),(1),(5),(5); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (5),(1); +CREATE TABLE t3 (c INT, KEY(c)); +INSERT INTO t3 VALUES (5),(5); +SET optimizer_switch='semijoin=on'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); +a +5 +5 +SET optimizer_switch='semijoin=off'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); +a +5 +5 +SET @@optimizer_switch= @optimiser_switch_save; +DROP TABLE t1, t2, t3; +End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 339e2b89786..46a46c91ddc 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7141,5 +7141,31 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; (SELECT MAX(sq.f2) FROM t1) NULL drop table t1, t2; +# +# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in +# (5.5 test) +# +SET @optimiser_switch_save= @@optimizer_switch; +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (1),(1),(1),(5),(5); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (5),(1); +CREATE TABLE t3 (c INT, KEY(c)); +INSERT INTO t3 VALUES (5),(5); +SET optimizer_switch='semijoin=on'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); +a +5 +5 +SET optimizer_switch='semijoin=off'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); +a +5 +5 +SET @@optimizer_switch= @optimiser_switch_save; +DROP TABLE t1, t2, t3; +End of 5.5 tests set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 50d9517043a..e6233e9de78 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6033,3 +6033,31 @@ CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; INSERT t2 VALUES (6),(9); SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2; drop table t1, t2; + +--echo # +--echo # MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in +--echo # (5.5 test) +--echo # +SET @optimiser_switch_save= @@optimizer_switch; + +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (1),(1),(1),(5),(5); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (5),(1); + +CREATE TABLE t3 (c INT, KEY(c)); +INSERT INTO t3 VALUES (5),(5); + +SET optimizer_switch='semijoin=on'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); + +SET optimizer_switch='semijoin=off'; +select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`) +and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); + +SET @@optimizer_switch= @optimiser_switch_save; +DROP TABLE t1, t2, t3; + +--echo End of 5.5 tests diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 69a5367cdf1..028bf44bf79 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2687,7 +2687,8 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, LooseScan detector in best_access_path) */ remaining_tables &= ~new_join_tab->table->map; - table_map dups_producing_tables; + table_map dups_producing_tables, prev_dups_producing_tables, + prev_sjm_lookup_tables; if (idx == join->const_tables) dups_producing_tables= 0; @@ -2698,7 +2699,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, if ((emb_sj_nest= new_join_tab->emb_sj_nest)) dups_producing_tables |= emb_sj_nest->sj_inner_tables; - Semi_join_strategy_picker **strategy; + Semi_join_strategy_picker **strategy, **prev_strategy; if (idx == join->const_tables) { /* First table, initialize pickers */ @@ -2750,23 +2751,54 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, 3. We have no clue what to do about fanount of semi-join Y. */ if ((dups_producing_tables & handled_fanout) || - (read_time < *current_read_time && + (read_time < *current_read_time && !(handled_fanout & pos->inner_tables_handled_with_other_sjs))) { - /* Mark strategy as used */ - (*strategy)->mark_used(); - pos->sj_strategy= sj_strategy; - if (sj_strategy == SJ_OPT_MATERIALIZE) - join->sjm_lookup_tables |= handled_fanout; + DBUG_ASSERT(pos->sj_strategy != sj_strategy); + /* + If the strategy choosen first time or + the strategy replace strategy which was used to exectly the same + tables + */ + if (pos->sj_strategy == SJ_OPT_NONE || + handled_fanout == + (prev_dups_producing_tables ^ dups_producing_tables)) + { + prev_strategy= strategy; + if (pos->sj_strategy == SJ_OPT_NONE) + { + prev_dups_producing_tables= dups_producing_tables; + prev_sjm_lookup_tables= join->sjm_lookup_tables; + } + /* Mark strategy as used */ + (*strategy)->mark_used(); + pos->sj_strategy= sj_strategy; + if (sj_strategy == SJ_OPT_MATERIALIZE) + join->sjm_lookup_tables |= handled_fanout; + else + join->sjm_lookup_tables &= ~handled_fanout; + *current_read_time= read_time; + *current_record_count= rec_count; + dups_producing_tables &= ~handled_fanout; + //TODO: update bitmap of semi-joins that were handled together with + // others. + if (is_multiple_semi_joins(join, join->positions, idx, + handled_fanout)) + pos->inner_tables_handled_with_other_sjs |= handled_fanout; + } else - join->sjm_lookup_tables &= ~handled_fanout; - *current_read_time= read_time; - *current_record_count= rec_count; - dups_producing_tables &= ~handled_fanout; - //TODO: update bitmap of semi-joins that were handled together with - // others. - if (is_multiple_semi_joins(join, join->positions, idx, handled_fanout)) - pos->inner_tables_handled_with_other_sjs |= handled_fanout; + { + /* Conflict fall to most general variant */ + (*prev_strategy)->set_empty(); + dups_producing_tables= prev_dups_producing_tables; + join->sjm_lookup_tables= prev_sjm_lookup_tables; + // mark it 'none' to avpoid loops + pos->sj_strategy= SJ_OPT_NONE; + // next skip to last; + strategy= pickers + + (sizeof(pickers)/sizeof(Semi_join_strategy_picker*) - 3); + continue; + } } else { -- cgit v1.2.1 From bdcd7f79e4a33e08bae31a86cee98e23ff6824ff Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Thu, 11 Jan 2018 09:33:26 +0200 Subject: MDEV-14916 InnoDB reports warning for "Purge reached the head of the history list" The warning was originally added in commit c67663054a7db1c77dd1dbc85b63595667a53500 (MySQL 4.1.12, 5.0.3) to trace claimed undo log corruption that was analyzed in https://lists.mysql.com/mysql/176250 on November 9, 2004. Originally, the limit was 20,000 undo log headers or transactions, but in commit 9d6d1902e091c868bb288e0ccf9f975ccb474db9 in MySQL 5.5.11 it was increased to 2,000,000. The message can be triggered when the progress of purge is prevented by a long-running transaction (or just an idle transaction whose read view was started a long time ago), by running many transactions that UPDATE or DELETE some records, then starting another transaction with a read view, and finally by executing more than 2,000,000 transactions that UPDATE or DELETE records in InnoDB tables. Finally, when the oldest long-running transaction is completed, purge would run up to the next-oldest transaction, and there would still be more than 2,000,000 transactions to purge. Because the message can be triggered when the database is obviously not corrupted, it should be removed. Heavy users of InnoDB should be monitoring the "History list length" in SHOW ENGINE INNODB STATUS; there is no need to spam the error log. --- storage/innobase/trx/trx0purge.c | 26 -------------------------- storage/xtradb/trx/trx0purge.c | 26 -------------------------- 2 files changed, 52 deletions(-) diff --git a/storage/innobase/trx/trx0purge.c b/storage/innobase/trx/trx0purge.c index 59f8cd52afa..d23d1fc6a1f 100644 --- a/storage/innobase/trx/trx0purge.c +++ b/storage/innobase/trx/trx0purge.c @@ -720,32 +720,6 @@ trx_purge_rseg_get_next_history_log( mutex_exit(&(rseg->mutex)); mtr_commit(&mtr); - - mutex_enter(&kernel_mutex); - - /* Add debug code to track history list corruption reported - on the MySQL mailing list on Nov 9, 2004. The fut0lst.c - file-based list was corrupt. The prev node pointer was - FIL_NULL, even though the list length was over 8 million nodes! - We assume that purge truncates the history list in large - size pieces, and if we here reach the head of the list, the - list cannot be longer than 2000 000 undo logs now. */ - - if (trx_sys->rseg_history_len > 2000000) { - ut_print_timestamp(stderr); - fprintf(stderr, - " InnoDB: Warning: purge reached the" - " head of the history list,\n" - "InnoDB: but its length is still" - " reported as %lu! Make a detailed bug\n" - "InnoDB: report, and submit it" - " to https://jira.mariadb.org/\n", - (ulong) trx_sys->rseg_history_len); - ut_ad(0); - } - - mutex_exit(&kernel_mutex); - return; } diff --git a/storage/xtradb/trx/trx0purge.c b/storage/xtradb/trx/trx0purge.c index bc094df0364..2a9e80fe66a 100644 --- a/storage/xtradb/trx/trx0purge.c +++ b/storage/xtradb/trx/trx0purge.c @@ -728,32 +728,6 @@ trx_purge_rseg_get_next_history_log( mutex_exit(&(rseg->mutex)); mtr_commit(&mtr); - - mutex_enter(&kernel_mutex); - - /* Add debug code to track history list corruption reported - on the MySQL mailing list on Nov 9, 2004. The fut0lst.c - file-based list was corrupt. The prev node pointer was - FIL_NULL, even though the list length was over 8 million nodes! - We assume that purge truncates the history list in large - size pieces, and if we here reach the head of the list, the - list cannot be longer than 2000 000 undo logs now. */ - - if (trx_sys->rseg_history_len > 2000000) { - ut_print_timestamp(stderr); - fprintf(stderr, - " InnoDB: Warning: purge reached the" - " head of the history list,\n" - "InnoDB: but its length is still" - " reported as %lu! Make a detailed bug\n" - "InnoDB: report, and submit it" - " to https://jira.mariadb.org/\n", - (ulong) trx_sys->rseg_history_len); - ut_ad(0); - } - - mutex_exit(&kernel_mutex); - return; } -- cgit v1.2.1