- commits - lists.mariadb.org

[Commits] 91ed8f78228: MDEV-13577 slave_parallel_mode=optimistic should not report the mode's
by andrei.elkin＠pp.inet.fi 11 Jun '18

11 Jun '18

revision-id: 91ed8f7822894ec8ea493a6168010e381c97d841 (mariadb-10.1.33-31-g91ed8f78228) parent(s): 3b7da8a44c8a0ff4b40b37e4db01f7e397aefab5 author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-06-11 11:42:40 +0300 message: MDEV-13577 slave_parallel_mode=optimistic should not report the mode's specific temporary errors The optimistic parallel slave's worker thread could face a run-time error due to the algorithm's specifics which allows for conflicts like the reported "Can't find record in 'table'". A typical stack is like {noformat} #0 handler::print_error (this=0x61c00008f8a0, error=149, errflag=0) at handler.cc:3650 #1 0x0000555555e95361 in write_record (thd=thd@entry=0x62a0000a2208, table=table@entry=0x61f00008ce88, info=info@entry=0x7fffdee356d0) at sql_insert.cc:1944 #2 0x0000555555ea7767 in mysql_insert (thd=thd@entry=0x62a0000a2208, table_list=0x61b00012ada0, fields=..., values_list=..., update_fields=..., update_values=..., duplic=<optimized out>, ignore=<optimized out>) at sql_insert.cc:1039 #3 0x0000555555efda90 in mysql_execute_command (thd=thd@entry=0x62a0000a2208) at sql_parse.cc:3927 #4 0x0000555555f0cc50 in mysql_parse (thd=0x62a0000a2208, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at sql_parse.cc:7449 #5 0x00005555566d4444 in Query_log_event::do_apply_event (this=0x61200005b9c8, rgi=<optimized out>, query_arg=<optimized out>, q_len_arg=<optimized out>) at log_event.cc:4508 #6 0x00005555566d639e in Query_log_event::do_apply_event (this=<optimized out>, rgi=<optimized out>) at log_event.cc:4185 #7 0x0000555555d738cf in Log_event::apply_event (rgi=0x61d0001ea080, this=0x61200005b9c8) at log_event.h:1343 #8 apply_event_and_update_pos_apply (ev=ev@entry=0x61200005b9c8, thd=thd@entry=0x62a0000a2208, rgi=rgi@entry=0x61d0001ea080, reason=<optimized out>) at slave.cc:3479 #9 0x0000555555d8596b in apply_event_and_update_pos_for_parallel (ev=ev@entry=0x61200005b9c8, thd=thd@entry=0x62a0000a2208, rgi=rgi@entry=0x61d0001ea080) at slave.cc:3623 #10 0x00005555562aca83 in rpt_handle_event (qev=qev@entry=0x6190000fa088, rpt=rpt@entry=0x62200002bd68) at rpl_parallel.cc:50 #11 0x00005555562bd04e in handle_rpl_parallel_thread (arg=arg@entry=0x62200002bd68) at rpl_parallel.cc:1258 {noformat} Here {{handler::print_error}} computes whether to error log the current error when --log-warnings > 1. The decision flag is consulted bu {{my_message_sql()}} which can be eventually called. In the bug case the decision is to log. However in the optimistic mode slave applier case any conflict is attempted to resolve with rollback and retry to success. Hence the logging is at least extraneous. The case is fixed with adding a new flag {{ME_LOG_AS_WARN}} which {{handler::print_error}} may propagate further on through {{my_error}} when the error comes from an optimistically running slave worker thread. The new flag effectively requests the warning level for the errlog record, while the thread's DA records the actual error (which is regarded as temporary one by the parallel slave error handler). --- include/my_sys.h | 1 + .../suite/rpl/r/rpl_parallel_optimistic.result | 23 ++++++++ .../suite/rpl/t/rpl_parallel_optimistic.test | 64 +++++++++++++++++++++- sql/handler.cc | 4 +- sql/mysqld.cc | 10 ++++ sql/sql_class.cc | 7 +++ sql/sql_class.h | 6 ++ 7 files changed, 113 insertions(+), 2 deletions(-) diff --git a/include/my_sys.h b/include/my_sys.h index 110a2ee9af3..1c5649812d1 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -112,6 +112,7 @@ typedef struct my_aio_result { #define ME_JUST_INFO 1024 /**< not error but just info */ #define ME_JUST_WARNING 2048 /**< not error but just warning */ #define ME_FATALERROR 4096 /* Fatal statement error */ +#define ME_LOG_AS_WARN 8192 /* is error but error-logged as warning */ /* Bits in last argument to fn_format */ #define MY_REPLACE_DIR 1 /* replace dir in name with 'dir' */ diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result index 0177e65b10f..a6da3399fab 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result @@ -1,4 +1,6 @@ include/rpl_init.inc [topology=1->2] +call mtr.add_suppression("Warning.*Deadlock found when trying to get lock; try restarting transaction"); +call mtr.add_suppression("Warning.*mysqld: Can't find record in 't2'"); ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; @@ -543,6 +545,27 @@ a b 57 7 58 8 59 9 +DELETE FROM t1; +DELETE FROM t2; +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +BEGIN; +INSERT INTO t1 SET a=1; +SET @save.binlog_format=@@session.binlog_format; +SET @@SESSION.binlog_format=row; +BEGIN; +INSERT INTO t1 SET a=1; +INSERT INTO t2 SET a=1; +COMMIT; +BEGIN; +DELETE FROM t2; +COMMIT; +ROLLBACK; +SET @@SESSION.binlog_format= @save.binlog_format; +DELETE FROM t1; +DELETE FROM t2; +include/save_master_gtid.inc +include/sync_with_master_gtid.inc include/stop_slave.inc SET GLOBAL slave_parallel_mode=@old_parallel_mode; SET GLOBAL slave_parallel_threads=@old_parallel_threads; diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test index 41fb6ebb72e..28bf4a77fd4 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test @@ -4,6 +4,11 @@ --let $rpl_topology=1->2 --source include/rpl_init.inc +--connection server_2 +call mtr.add_suppression("Warning.*Deadlock found when trying to get lock; try restarting transaction"); +# The following instruction is a part of the proof of MDEV-13577 fixes, below. +call mtr.add_suppression("Warning.*mysqld: Can't find record in 't2'"); + --connection server_1 ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; @@ -480,8 +485,65 @@ SELECT * FROM t2 WHERE a >= 40 ORDER BY a; SELECT * FROM t1 WHERE a >= 40 ORDER BY a; SELECT * FROM t2 WHERE a >= 40 ORDER BY a; -# Clean up. +# partial cleanup to reuse the tables by following tests +--connection server_1 +DELETE FROM t1; +DELETE FROM t2; +--source include/save_master_gtid.inc + +--connection server_2 +--source include/sync_with_master_gtid.inc +# +# MDEV-13577 optimistic parallel slave errors out to error log unnecessary +# + +# The 1st of the following two trx:s a blocker on slave +--connection server_2 +BEGIN; +INSERT INTO t1 SET a=1; + +--connection server_1 +SET @save.binlog_format=@@session.binlog_format; +SET @@SESSION.binlog_format=row; + +BEGIN; + INSERT INTO t1 SET a=1; + INSERT INTO t2 SET a=1; +COMMIT; + +# This transaction is going to win optimistical race with above INSERT +# on slave while being depend on it. That means it will face a kind of temporary error +# and then will retry to succeed. +BEGIN; + DELETE FROM t2; +COMMIT; + +# First make sure DELETE raced indeed to get stuck at retrying stage +# where it runs "realistically" now. There is nomore optimistic error +# in the errorlog, which is downgraded to the warning level (when +# --log-warnings > 1), see above suppression. +--connection server_2 +--let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit" +--source include/wait_condition.inc + +# Next release the 1st trx to commit. +--connection server_2 +ROLLBACK; + +# MDEV-13577 local cleanup: +--connection server_1 +SET @@SESSION.binlog_format= @save.binlog_format; +DELETE FROM t1; +DELETE FROM t2; +--source include/save_master_gtid.inc + +--connection server_2 +--source include/sync_with_master_gtid.inc + +# +# Clean up. +# --connection server_2 --source include/stop_slave.inc SET GLOBAL slave_parallel_mode=@old_parallel_mode; diff --git a/sql/handler.cc b/sql/handler.cc index 397891ceec5..dc12a006049 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3643,8 +3643,10 @@ void handler::print_error(int error, myf errflag) Log error to log before we crash or if extended warnings are requested */ errflag|= ME_NOREFRESH; + if (ha_thd()->is_optimistic_slave_worker()) + errflag|= ME_LOG_AS_WARN; } - } + } /* if we got an OS error from a file-based engine, specify a path of error */ if (error < HA_ERR_FIRST && bas_ext()[0]) diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 5f954f7576d..5bf5e3f73fc 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -3517,6 +3517,16 @@ void my_message_sql(uint error, const char *str, myf MyFlags) level= Sql_condition::WARN_LEVEL_WARN; func= sql_print_warning; } + else if (MyFlags & ME_LOG_AS_WARN) + { + /* + Typical use case is optimistic parallel slave where DA needs to hold + an error condition caused by the current error, but the error-log + level is relaxed to the warning one. + */ + level= Sql_condition::WARN_LEVEL_ERROR; + func= sql_print_warning; + } else { level= Sql_condition::WARN_LEVEL_ERROR; diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 24140246b96..37f29115171 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -7100,6 +7100,13 @@ bool THD::rgi_have_temporary_tables() return rgi_slave->rli->save_temporary_tables != 0; } +bool THD::is_optimistic_slave_worker() +{ + DBUG_ASSERT(system_thread != SYSTEM_THREAD_SLAVE_SQL || rgi_slave); + + return system_thread == SYSTEM_THREAD_SLAVE_SQL && rgi_slave && + rgi_slave->speculation == rpl_group_info::SPECULATE_OPTIMISTIC; +} void wait_for_commit::reinit() diff --git a/sql/sql_class.h b/sql/sql_class.h index ca6155ec93f..0ced84791a0 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4167,6 +4167,12 @@ class THD :public Statement, (THD_TRANS::DID_WAIT | THD_TRANS::CREATED_TEMP_TABLE | THD_TRANS::DROPPED_TEMP_TABLE | THD_TRANS::DID_DDL)); } + + /* + Returns true when the thread handle belongs to a slave worker thread + running in the optimistic execution mode. + */ + bool is_optimistic_slave_worker(); };

1 0

[Commits] f72361e0734: MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY
by varunraiko1803＠gmail.com 09 Jun '18

09 Jun '18

revision-id: f72361e07346d142c4f3dda482b441c086c626f2 (mariadb-10.0.30-372-gf72361e0734) parent(s): 3a724800eebf78bd179bd1a2637ebe0a175e84d6 author: Varun Gupta committer: Varun Gupta timestamp: 2018-06-10 02:16:12 +0530 message: MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY The issue in this case is that we take in account the estimates from quick keys instead of rec_per_key. The estimates for quick keys are better than rec_per_key only if we have ref(const), so we need to check that all keyparts in the ref key are of the type ref(const). Also we need to make sure that the # of keyparts in ref and quick keys are same. --- mysql-test/r/order_by_innodb.result | 48 +++++++++++++++++++++++++++++++++++++ mysql-test/t/order_by_innodb.test | 34 +++++++++++++++++++++++++- sql/sql_select.cc | 18 +++++++++++++- 3 files changed, 98 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 3c6c4053741..d98e89c730a 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -11,3 +11,51 @@ a b c d 8 NULL 9 NULL 8 NULL 10 NULL DROP TABLE t1; +# +# MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY +# +create table t1(a int) engine=innodb; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2( +id int primary key, +key1 int, +col1 int, +key(key1)) engine=innodb; +insert into t2 select A.a + B.a*10 + C.a*100 + D.a* 1000,A.a + 10*B.a, 123456 +from t1 A, t1 B, t1 C, t1 D; +alter table t2 add key2 int; +update t2 set key2=key1; +alter table t2 add key(key2); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain select +(SELECT +concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # +2 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a # Using index condition; Using where; Using filesort +select(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +9900-0-123456 +9901-1-123456 +9902-2-123456 +9903-3-123456 +9904-4-123456 +9905-5-123456 +9906-6-123456 +9907-7-123456 +9908-8-123456 +9909-9-123456 +drop table t1,t2; diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index c20eaceb053..3dad752b16b 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -18,6 +18,38 @@ INSERT INTO t1 (a,c) VALUES (20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28); SELECT * FROM t1 WHERE a = 8 AND (b = 1 OR b IS NULL) ORDER BY c; - DROP TABLE t1; +--echo # +--echo # MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY +--echo # + +create table t1(a int) engine=innodb; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2( +id int primary key, +key1 int, +col1 int, +key(key1)) engine=innodb; +insert into t2 select A.a + B.a*10 + C.a*100 + D.a* 1000,A.a + 10*B.a, 123456 +from t1 A, t1 B, t1 C, t1 D; + +alter table t2 add key2 int; +update t2 set key2=key1; +alter table t2 add key(key2); +analyze table t2; + +--replace_column 9 # +explain select +(SELECT +concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +select(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +drop table t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f8435eca995..c6ab451501d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25117,7 +25117,23 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, */ if (ref_key >= 0 && tab->type == JT_REF) { - if (table->quick_keys.is_set(ref_key)) + bool all_ref_parts_const= TRUE; + /* + For all the parts of the ref key we check if all of them belong + to the type ref(const). This is done because if all parts of the ref + key are of type ref(const), then we are sure that the estimates + provides by quick keys is better than that provide by rec_per_key. + */ + for (uint key_part=0; key_part < tab->ref.key_parts; key_part++) + { + if (!(tab->ref.const_ref_part_map & (key_part_map(1) << key_part))) + { + all_ref_parts_const= FALSE; + break; + } + } + if (all_ref_parts_const && table->quick_keys.is_set(ref_key) && + table->quick_key_parts[ref_key] == tab->ref.key_parts) refkey_rows_estimate= table->quick_rows[ref_key]; else {

1 0

[Commits] 3a724800eeb: MDEV-15247: Crash when SET NAMES 'utf8' is set
by varunraiko1803＠gmail.com 09 Jun '18

09 Jun '18

revision-id: 3a724800eebf78bd179bd1a2637ebe0a175e84d6 (mariadb-10.0.30-371-g3a724800eeb) parent(s): cd33280b682692f0517a26178d7b5337db648751 author: Varun Gupta committer: Varun Gupta timestamp: 2018-06-09 18:52:50 +0530 message: MDEV-15247: Crash when SET NAMES 'utf8' is set In this case we are accessing incorrect memory when we have mergeable semi-joins. In the case when we have mergeable semi joins parent select will have a table count of all the tables in that select plus all the tables involved in the IN-subquery. But this table count does not include the "sjm table" (only includes the inner and outer tables) denotes as <subquery#> in explain. --- mysql-test/r/subselect_sj2_mat.result | 90 +++++++++++++++++++++++++++++++++++ mysql-test/t/subselect_sj2_mat.test | 89 ++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 4 +- 3 files changed, 182 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 5d0c64e9ddb..ff40d908c63 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1634,3 +1634,93 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where DROP TABLE t1,t2; +# +# MDEV-15247: Crash when SET NAMES 'utf8' is set +# +CREATE TABLE t1 ( +id_category int unsigned, +id_product int unsigned, +PRIMARY KEY (id_category,id_product) +) ENGINE=MyISAM; +INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102); +CREATE TABLE t2 ( +id_product int, +id_t2 int, +KEY id_t2 (id_t2), +KEY id_product (id_product) +) ENGINE=MyISAM; +INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32), +(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26), +(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32), +(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19), +(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19), +(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20), +(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32), +(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19), +(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16), +(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31), +(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24), +(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19), +(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31), +(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32), +(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26), +(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22), +(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19), +(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32), +(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30), +(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23), +(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19), +(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15), +(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33), +(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18), +(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15), +(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19); +CREATE TABLE t3 ( +id_product int unsigned, +PRIMARY KEY (id_product) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES +(102),(103),(104),(105),(106),(107),(108),(109),(110), +(315371),(315373),(315374),(315375),(315376),(315377), +(315378),(315379),(315380); +CREATE TABLE t4 ( +id_product int not null, +id_shop int, +PRIMARY KEY (id_product,id_shop) +) ENGINE=MyISAM ; +INSERT INTO t4 VALUES +(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1), +(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1), +(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1), +(177,1),(176,1),(126,1),(315380,1); +CREATE TABLE t5 (id_product int) ENGINE=MyISAM; +INSERT INTO `t5` VALUES +(652),(668),(669),(670),(671),(673),(674),(675),(676), +(677),(679),(680),(681),(682),(683),(684),(685),(686); +explain +SELECT * FROM t3 +JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1) +JOIN t1 ON (t1.id_product = t3.id_product) +LEFT JOIN t5 ON (t5.id_product = t3.id_product) +WHERE 1=1 +AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32) +AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15) +AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19) +AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23) +AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.id_product 1 Using index +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t1.id_product,const 1 Using where; Using index +1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) +5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where +4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 32 Using index condition; Using where +3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 +2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 50 +6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 30 Using index condition; Using where +drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test index cfb6c8c2819..4b0394e617a 100644 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@ -303,3 +303,92 @@ eval $q; eval explain $q; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-15247: Crash when SET NAMES 'utf8' is set +--echo # + +CREATE TABLE t1 ( + id_category int unsigned, + id_product int unsigned, + PRIMARY KEY (id_category,id_product) +) ENGINE=MyISAM; + +INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102); + +CREATE TABLE t2 ( + id_product int, + id_t2 int, + KEY id_t2 (id_t2), + KEY id_product (id_product) +) ENGINE=MyISAM; + +INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32), +(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26), +(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32), +(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19), +(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19), +(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20), +(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32), +(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19), +(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16), +(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31), +(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24), +(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19), +(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31), +(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32), +(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26), +(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22), +(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19), +(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32), +(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30), +(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23), +(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19), +(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15), +(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33), +(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18), +(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15), +(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19); + + +CREATE TABLE t3 ( + id_product int unsigned, + PRIMARY KEY (id_product) +) ENGINE=MyISAM; + +INSERT INTO t3 VALUES +(102),(103),(104),(105),(106),(107),(108),(109),(110), +(315371),(315373),(315374),(315375),(315376),(315377), +(315378),(315379),(315380); + + +CREATE TABLE t4 ( + id_product int not null, + id_shop int, + PRIMARY KEY (id_product,id_shop) +) ENGINE=MyISAM ; + +INSERT INTO t4 VALUES +(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1), +(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1), +(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1), +(177,1),(176,1),(126,1),(315380,1); + +CREATE TABLE t5 (id_product int) ENGINE=MyISAM; +INSERT INTO `t5` VALUES +(652),(668),(669),(670),(671),(673),(674),(675),(676), +(677),(679),(680),(681),(682),(683),(684),(685),(686); + +explain +SELECT * FROM t3 + JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1) + JOIN t1 ON (t1.id_product = t3.id_product) +LEFT JOIN t5 ON (t5.id_product = t3.id_product) +WHERE 1=1 + AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32) + AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15) + AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19) + AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23) + AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26); + +drop table t1,t2,t3,t4,t5; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1f15d5bcec0..f8435eca995 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9584,7 +9584,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) table_map current_map; i= join->const_tables; for (tab= first_depth_first_tab(join); tab; - tab= next_depth_first_tab(join, tab), i++) + tab= next_depth_first_tab(join, tab)) { bool is_hj; /* @@ -10055,6 +10055,8 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) } first_inner_tab= first_inner_tab->first_upper; } + if (!tab->bush_children) + i++; } } DBUG_RETURN(0);

1 0

[Commits] 71cb28e39ad: MDEV-13577 slave_parallel_mode=optimistic should not report the mode's
by andrei.elkin＠pp.inet.fi 09 Jun '18

09 Jun '18

revision-id: 71cb28e39ad4f670b4cf067f8b877be352c5cc4c (mariadb-10.1.33-31-g71cb28e39ad) parent(s): 3b7da8a44c8a0ff4b40b37e4db01f7e397aefab5 author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-06-09 11:16:37 +0300 message: MDEV-13577 slave_parallel_mode=optimistic should not report the mode's specific temporary errors The optimistic parallel slave's worker thread could face a run-time error due to the algorithm's specifics which allows for conflicts like the reported "Can't find record in 'table'". A typical stack is like {noformat} #0 handler::print_error (this=0x61c00008f8a0, error=149, errflag=0) at handler.cc:3650 #1 0x0000555555e95361 in write_record (thd=thd@entry=0x62a0000a2208, table=table@entry=0x61f00008ce88, info=info@entry=0x7fffdee356d0) at sql_insert.cc:1944 #2 0x0000555555ea7767 in mysql_insert (thd=thd@entry=0x62a0000a2208, table_list=0x61b00012ada0, fields=..., values_list=..., update_fields=..., update_values=..., duplic=<optimized out>, ignore=<optimized out>) at sql_insert.cc:1039 #3 0x0000555555efda90 in mysql_execute_command (thd=thd@entry=0x62a0000a2208) at sql_parse.cc:3927 #4 0x0000555555f0cc50 in mysql_parse (thd=0x62a0000a2208, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at sql_parse.cc:7449 #5 0x00005555566d4444 in Query_log_event::do_apply_event (this=0x61200005b9c8, rgi=<optimized out>, query_arg=<optimized out>, q_len_arg=<optimized out>) at log_event.cc:4508 #6 0x00005555566d639e in Query_log_event::do_apply_event (this=<optimized out>, rgi=<optimized out>) at log_event.cc:4185 #7 0x0000555555d738cf in Log_event::apply_event (rgi=0x61d0001ea080, this=0x61200005b9c8) at log_event.h:1343 #8 apply_event_and_update_pos_apply (ev=ev@entry=0x61200005b9c8, thd=thd@entry=0x62a0000a2208, rgi=rgi@entry=0x61d0001ea080, reason=<optimized out>) at slave.cc:3479 #9 0x0000555555d8596b in apply_event_and_update_pos_for_parallel (ev=ev@entry=0x61200005b9c8, thd=thd@entry=0x62a0000a2208, rgi=rgi@entry=0x61d0001ea080) at slave.cc:3623 #10 0x00005555562aca83 in rpt_handle_event (qev=qev@entry=0x6190000fa088, rpt=rpt@entry=0x62200002bd68) at rpl_parallel.cc:50 #11 0x00005555562bd04e in handle_rpl_parallel_thread (arg=arg@entry=0x62200002bd68) at rpl_parallel.cc:1258 {noformat} Here {{handler::print_error}} computes whether to error log the current error when --log-warnings > 1. The decision flag is consulted bu {{my_message_sql()}} which can be eventually called. In the bug case the decision is to log. However in the optimistic mode slave applier case any conflict is attempted to resolve with rollback and retry to success. Hence the logging is at least extraneous. The case is fixed with adding a new flag {{ME_LOG_AS_WARN}} which {{handler::print_error}} may propagate further on through {{my_error}} when the error comes from an optimistically running slave worker thread. The new flag effectively requests the warning level for the errlog record, while the thread's DA records the actual error (which is regarded as temporary one by the parallel slave error handler). --- include/my_sys.h | 1 + .../suite/rpl/r/rpl_parallel_optimistic.result | 23 ++++++++ .../suite/rpl/t/rpl_parallel_optimistic.test | 64 +++++++++++++++++++++- sql/handler.cc | 9 ++- sql/mysqld.cc | 10 ++++ sql/sql_class.cc | 5 ++ sql/sql_class.h | 6 ++ 7 files changed, 116 insertions(+), 2 deletions(-) diff --git a/include/my_sys.h b/include/my_sys.h index 110a2ee9af3..1c5649812d1 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -112,6 +112,7 @@ typedef struct my_aio_result { #define ME_JUST_INFO 1024 /**< not error but just info */ #define ME_JUST_WARNING 2048 /**< not error but just warning */ #define ME_FATALERROR 4096 /* Fatal statement error */ +#define ME_LOG_AS_WARN 8192 /* is error but error-logged as warning */ /* Bits in last argument to fn_format */ #define MY_REPLACE_DIR 1 /* replace dir in name with 'dir' */ diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result index 0177e65b10f..a6da3399fab 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result @@ -1,4 +1,6 @@ include/rpl_init.inc [topology=1->2] +call mtr.add_suppression("Warning.*Deadlock found when trying to get lock; try restarting transaction"); +call mtr.add_suppression("Warning.*mysqld: Can't find record in 't2'"); ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; @@ -543,6 +545,27 @@ a b 57 7 58 8 59 9 +DELETE FROM t1; +DELETE FROM t2; +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +BEGIN; +INSERT INTO t1 SET a=1; +SET @save.binlog_format=@@session.binlog_format; +SET @@SESSION.binlog_format=row; +BEGIN; +INSERT INTO t1 SET a=1; +INSERT INTO t2 SET a=1; +COMMIT; +BEGIN; +DELETE FROM t2; +COMMIT; +ROLLBACK; +SET @@SESSION.binlog_format= @save.binlog_format; +DELETE FROM t1; +DELETE FROM t2; +include/save_master_gtid.inc +include/sync_with_master_gtid.inc include/stop_slave.inc SET GLOBAL slave_parallel_mode=@old_parallel_mode; SET GLOBAL slave_parallel_threads=@old_parallel_threads; diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test index 41fb6ebb72e..d0164c1cefa 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test @@ -4,6 +4,11 @@ --let $rpl_topology=1->2 --source include/rpl_init.inc +--connection server_2 +call mtr.add_suppression("Warning.*Deadlock found when trying to get lock; try restarting transaction"); +# The following instruction is a part of the proof of MDEV-13577 fixes, below. +call mtr.add_suppression("Warning.*mysqld: Can't find record in 't2'"); + --connection server_1 ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; @@ -480,8 +485,65 @@ SELECT * FROM t2 WHERE a >= 40 ORDER BY a; SELECT * FROM t1 WHERE a >= 40 ORDER BY a; SELECT * FROM t2 WHERE a >= 40 ORDER BY a; -# Clean up. +# partial cleanup to reuse the tables by following tests +--connection server_1 +DELETE FROM t1; +DELETE FROM t2; +--source include/save_master_gtid.inc + +--connection server_2 +--source include/sync_with_master_gtid.inc +# +# MDEV-13577 optimistic parallel slave errors out to error log unnecessary +# + +# The 1st of the following two trx blocker on slave +--connection server_2 +BEGIN; +INSERT INTO t1 SET a=1; + +--connection server_1 +SET @save.binlog_format=@@session.binlog_format; +SET @@SESSION.binlog_format=row; + +BEGIN; + INSERT INTO t1 SET a=1; + INSERT INTO t2 SET a=1; +COMMIT; + +# This transaction is going to win optimistical race with above INSERT +# on slave while being depend on it. That means it will face a kind of temporary error +# and then will retry to succeed. +BEGIN; + DELETE FROM t2; +COMMIT; + +# First make sure DELETE raced indeed to get stuck at retrying stage +# where it runs "realistically" now. There is nomore optimistic error +# in the errorlog, which is downgraded to the warning level (when +# --log-warnings > 1), see above suppression. +--connection server_2 +--let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit" +--source include/wait_condition.inc + +# Next release the 1st trx to commit. +--connection server_2 +ROLLBACK; + +# MDEV-13577 local cleanup: +--connection server_1 +SET @@SESSION.binlog_format= @save.binlog_format; +DELETE FROM t1; +DELETE FROM t2; +--source include/save_master_gtid.inc + +--connection server_2 +--source include/sync_with_master_gtid.inc + +# +# Clean up. +# --connection server_2 --source include/stop_slave.inc SET GLOBAL slave_parallel_mode=@old_parallel_mode; diff --git a/sql/handler.cc b/sql/handler.cc index 397891ceec5..1778b099a4a 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3639,12 +3639,19 @@ void handler::print_error(int error, myf errflag) if ((debug_assert_if_crashed_table || global_system_variables.log_warnings > 1)) { + THD *thd= ha_thd(); /* Log error to log before we crash or if extended warnings are requested */ + DBUG_ASSERT(!(thd && thd->system_thread == SYSTEM_THREAD_SLAVE_SQL) || + (thd == current_thd && + thd->rgi_slave)); + errflag|= ME_NOREFRESH; + if (thd->is_optimistic_slave_worker()) + errflag|= ME_LOG_AS_WARN; } - } + } /* if we got an OS error from a file-based engine, specify a path of error */ if (error < HA_ERR_FIRST && bas_ext()[0]) diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 5f954f7576d..5bf5e3f73fc 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -3517,6 +3517,16 @@ void my_message_sql(uint error, const char *str, myf MyFlags) level= Sql_condition::WARN_LEVEL_WARN; func= sql_print_warning; } + else if (MyFlags & ME_LOG_AS_WARN) + { + /* + Typical use case is optimistic parallel slave where DA needs to hold + an error condition caused by the current error, but the error-log + level is relaxed to the warning one. + */ + level= Sql_condition::WARN_LEVEL_ERROR; + func= sql_print_warning; + } else { level= Sql_condition::WARN_LEVEL_ERROR; diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 24140246b96..cc1d1974a60 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -7100,6 +7100,11 @@ bool THD::rgi_have_temporary_tables() return rgi_slave->rli->save_temporary_tables != 0; } +bool THD::is_optimistic_slave_worker() +{ + return system_thread == SYSTEM_THREAD_SLAVE_SQL && rgi_slave && + rgi_slave->speculation == rpl_group_info::SPECULATE_OPTIMISTIC; +} void wait_for_commit::reinit() diff --git a/sql/sql_class.h b/sql/sql_class.h index ca6155ec93f..0ced84791a0 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4167,6 +4167,12 @@ class THD :public Statement, (THD_TRANS::DID_WAIT | THD_TRANS::CREATED_TEMP_TABLE | THD_TRANS::DROPPED_TEMP_TABLE | THD_TRANS::DID_DDL)); } + + /* + Returns true when the thread handle belongs to a slave worker thread + running in the optimistic execution mode. + */ + bool is_optimistic_slave_worker(); };

1 0

[Commits] 9c2344951e3: MDEV-16374: Filtered shows 0 for materilization scan for a semi join, which makes optimizer always picks
by varunraiko1803＠gmail.com 09 Jun '18

09 Jun '18

revision-id: 9c2344951e3d940f45f5cce7014452c8518c78fb (mariadb-10.0.30-367-g9c2344951e3) parent(s): 55abcfa7b70968246a1a26a8839013ebb8f5c506 author: Varun Gupta committer: Varun Gupta timestamp: 2018-06-09 11:09:59 +0530 message: MDEV-16374: Filtered shows 0 for materilization scan for a semi join, which makes optimizer always picks materialization scan over materialization lookup For non-mergeable semi-joins we don't store the estimates of the IN subquery in table->file->stats.records. In the function TABLE_LIST::fetch_number_of_rows, we store the number of rows in the tables (estimates in case of derived table/views). Currently we don't store the estimates for non-mergeable semi-joins, which leads to a problem of selecting materialization scan over materialization lookup. Fixed this by storing these estimated appropriately --- mysql-test/r/selectivity.result | 70 ++++++++++++++++++++++++++++-- mysql-test/r/selectivity_innodb.result | 70 ++++++++++++++++++++++++++++-- mysql-test/r/subselect_sj_nonmerged.result | 6 +-- mysql-test/t/selectivity.test | 18 ++++++++ sql/table.cc | 8 ++++ 5 files changed, 161 insertions(+), 11 deletions(-) diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 61a77d135e7..3e8fb8e2e41 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -356,13 +356,13 @@ and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6005 0.00 Using temporary; Using filesort -1 PRIMARY orders eq_ref PRIMARY,i_o_custkey PRIMARY 4 <subquery2>.l_orderkey 1 100.00 Using where +1 PRIMARY orders ALL PRIMARY,i_o_custkey NULL NULL NULL 1500 100.00 Using where; Using temporary; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 1 100.00 1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00 -1 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 <subquery2>.l_orderkey 4 100.00 +1 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 dbt3_s001.orders.o_orderkey 4 100.00 Using index 2 MATERIALIZED lineitem index NULL i_l_orderkey_quantity 13 NULL 6005 100.00 Using index Warnings: -Note 1003 select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having (sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250)) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where ((`dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey`) and (`dbt3_s001`.`orders`.`o_orderkey` = `<subquery2>`.`l_orderkey`) and (`dbt3_s001`.`lineitem`.`l_orderkey` = `<subquery2>`.`l_orderkey`)) group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`,`dbt3_s001`.`orders `.`o_tot alprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` +Note 1003 select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having (sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250)) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where ((`dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey`) and (`<subquery2>`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey`) and (`dbt3_s001`.`lineitem`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey`)) group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`,`dbt3_s001`. `orders` .`o_totalprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem @@ -1535,6 +1535,68 @@ t 10:00:00 11:00:00 DROP TABLE t1; +# +# MDEV-16374: filtered shows 0 for materilization scan for a semi join, which makes optimizer +# always pick materialization scan over materialization lookup +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int); +insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10), +(11,11),(12,12),(13,13),(14,14),(15,15); +set @@optimizer_use_condition_selectivity=2; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where (`<subquery2>`.`max(a)` = `test`.`t1`.`a`) +select * from t1 where a in (select max(a) from t1 group by b); +a b +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +set @@optimizer_use_condition_selectivity=1; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where (`<subquery2>`.`max(a)` = `test`.`t1`.`a`) +select * from t1 where a in (select max(a) from t1 group by b); +a b +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +drop table t1,t0; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index a026c2e6d92..748ef0cb6ca 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -359,13 +359,13 @@ and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6005 0.00 Using temporary; Using filesort -1 PRIMARY orders eq_ref PRIMARY,i_o_custkey PRIMARY 4 <subquery2>.l_orderkey 1 100.00 Using where +1 PRIMARY orders ALL PRIMARY,i_o_custkey NULL NULL NULL 1500 100.00 Using where; Using temporary; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 1 100.00 1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00 -1 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 <subquery2>.l_orderkey 4 100.00 +1 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 dbt3_s001.orders.o_orderkey 4 100.00 Using index 2 MATERIALIZED lineitem index NULL PRIMARY 8 NULL 6005 100.00 Warnings: -Note 1003 select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having (sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250)) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where ((`dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey`) and (`dbt3_s001`.`orders`.`o_orderkey` = `<subquery2>`.`l_orderkey`) and (`dbt3_s001`.`lineitem`.`l_orderkey` = `<subquery2>`.`l_orderkey`)) group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`,`dbt3_s001`.`orders `.`o_tot alprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` +Note 1003 select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having (sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250)) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where ((`dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey`) and (`<subquery2>`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey`) and (`dbt3_s001`.`lineitem`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey`)) group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`,`dbt3_s001`. `orders` .`o_totalprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem @@ -1539,6 +1539,68 @@ t 10:00:00 11:00:00 DROP TABLE t1; +# +# MDEV-16374: filtered shows 0 for materilization scan for a semi join, which makes optimizer +# always pick materialization scan over materialization lookup +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int); +insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10), +(11,11),(12,12),(13,13),(14,14),(15,15); +set @@optimizer_use_condition_selectivity=2; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where (`<subquery2>`.`max(a)` = `test`.`t1`.`a`) +select * from t1 where a in (select max(a) from t1 group by b); +a b +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +set @@optimizer_use_condition_selectivity=1; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where (`<subquery2>`.`max(a)` = `test`.`t1`.`a`) +select * from t1 where a in (select max(a) from t1 group by b); +a b +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +drop table t1,t0; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/subselect_sj_nonmerged.result b/mysql-test/r/subselect_sj_nonmerged.result index c7e04225ffe..47970668ae5 100644 --- a/mysql-test/r/subselect_sj_nonmerged.result +++ b/mysql-test/r/subselect_sj_nonmerged.result @@ -77,9 +77,9 @@ explain select * from t4 where t4.a in (select max(t2.a) from t1, t2 group by t2.b) and t4.b in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) -1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 12 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 +1 PRIMARY t4 ref a a 5 <subquery2>.max(t2.a) 12 Using index condition +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 test.t4.b 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 548ef295fb2..afaa937c360 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1043,6 +1043,24 @@ SELECT * FROM (SELECT t FROM t1 WHERE d IS NULL) sq; DROP TABLE t1; +--echo # +--echo # MDEV-16374: filtered shows 0 for materilization scan for a semi join, which makes optimizer +--echo # always pick materialization scan over materialization lookup +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int); +insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10), +(11,11),(12,12),(13,13),(14,14),(15,15); +set @@optimizer_use_condition_selectivity=2; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +select * from t1 where a in (select max(a) from t1 group by b); +set @@optimizer_use_condition_selectivity=1; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +select * from t1 where a in (select max(a) from t1 group by b); +drop table t1,t0; + set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; diff --git a/sql/table.cc b/sql/table.cc index bc6e1e754ee..b5082df7076 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -7099,7 +7099,15 @@ int TABLE_LIST::fetch_number_of_rows() { int error= 0; if (jtbm_subselect) + { + if (jtbm_subselect->is_jtbm_merged) + { + table->file->stats.records= jtbm_subselect->jtbm_record_count; + set_if_bigger(table->file->stats.records, 2); + table->used_stat_records= table->file->stats.records; + } return 0; + } if (is_materialized_derived() && !fill_me) {

1 0

[Commits] 27a34db58b5: MDEV-16191: Analyze format=json gives incorrect value for r_limit inside a dependent
by varunraiko1803＠gmail.com 06 Jun '18

06 Jun '18

revision-id: 27a34db58b569862bd17baf8ec5dde75e89ec64e (mariadb-10.1.23-563-g27a34db58b5) parent(s): 1d4e1d3263bf3b87b473c12d4876f368dc3450b6 author: Varun Gupta committer: Varun Gupta timestamp: 2018-06-06 22:09:52 +0530 message: MDEV-16191: Analyze format=json gives incorrect value for r_limit inside a dependent subquery when ORDER BY is present Currently for setting r_limit we divide with the number of iterations we invoke the dependent subquery. This is not needed for the case of limit. For varying limits we produce the output that the limit varies with execution. Also there is a type for filtered , we forgot to multiply by 100 as it is represented as a percent. --- mysql-test/r/analyze_stmt_orderby.result | 2 +- sql/sql_analyze_stmt.cc | 2 +- sql/sql_explain.cc | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/analyze_stmt_orderby.result b/mysql-test/r/analyze_stmt_orderby.result index be1f01a2a52..238baff50e1 100644 --- a/mysql-test/r/analyze_stmt_orderby.result +++ b/mysql-test/r/analyze_stmt_orderby.result @@ -303,7 +303,7 @@ ANALYZE "r_rows": 10, "r_total_time_ms": "REPLACED", "filtered": 100, - "r_filtered": 1, + "r_filtered": 100, "attached_condition": "(t0.a is not null)" } } diff --git a/sql/sql_analyze_stmt.cc b/sql/sql_analyze_stmt.cc index 098e99e88fc..68299d024fd 100644 --- a/sql/sql_analyze_stmt.cc +++ b/sql/sql_analyze_stmt.cc @@ -39,7 +39,7 @@ void Filesort_tracker::print_json_members(Json_writer *writer) if (r_limit == 0) writer->add_str(varied_str); else - writer->add_ll((longlong) rint(r_limit/get_r_loops())); + writer->add_ll((longlong) rint(r_limit)); } writer->add_member("r_used_priority_queue"); diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index ac6bee05001..82107f9b922 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -1642,7 +1642,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, { /* Get r_filtered value from filesort */ if (fs_tracker->get_r_loops()) - writer->add_double(fs_tracker->get_r_filtered()); + writer->add_double(fs_tracker->get_r_filtered()*100); else writer->add_null(); }

1 0

[Commits] 75b4eb5cc96: Catch of OOM situation.
by Oleksandr Byelkin 06 Jun '18

06 Jun '18

revision-id: 75b4eb5cc969a1f5fc221d03c62987b8e57d6332 (mariadb-5.5.60-17-g75b4eb5cc96) parent(s): 72b6d01848e56a75349d663bc61bbe71f97a280b author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-06-06 15:27:57 +0200 message: Catch of OOM situation. --- sql/sql_base.cc | 3 +++ sql/table.cc | 2 ++ 2 files changed, 5 insertions(+) diff --git a/sql/sql_base.cc b/sql/sql_base.cc index f5864256440..71bbb538b5c 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6426,6 +6426,9 @@ find_field_in_natural_join(THD *thd, TABLE_LIST *table_ref, const char *name, column reference. See create_view_field() for details. */ item= nj_col->create_item(thd); + if (!item) + DBUG_RETURN(NULL); + /* *ref != NULL means that *ref contains the item that we need to replace. If the item was aliased by the user, set the alias to diff --git a/sql/table.cc b/sql/table.cc index bb4eae9b1e2..552f514283d 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5252,6 +5252,8 @@ Item *create_view_field(THD *thd, TABLE_LIST *view, Item **field_ref, Item *item= new Item_direct_view_ref(&view->view->select_lex.context, field_ref, view->alias, name, view); + if (!item) + return NULL; /* Force creation of nullable item for the result tmp table for outer joined views/derived tables.

1 0

[Commits] 7107adad090: MDEV-16401: Apply review comments to MDEV-16005
by jan 06 Jun '18

06 Jun '18

revision-id: 7107adad09074f342ee8f97715ad3a618deac8fe (mariadb-10.1.33-28-g7107adad090) parent(s): c1698e8dc50f0c342c1a6886426ba1d43396cd1e author: Jan Lindström committer: Jan Lindström timestamp: 2018-06-06 14:19:14 +0300 message: MDEV-16401: Apply review comments to MDEV-16005 Do not hold LOCK_thd_data during my_error or WSREP_DEBUG. Similarly, release LOCK_thd_data before close_thread_tables() call. --- sql/sql_parse.cc | 23 +++++++++++++++++------ 1 file changed, 17 insertions(+), 6 deletions(-) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index be7408af77d..292eb1f9831 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1271,9 +1271,9 @@ bool dispatch_command(enum enum_server_command command, THD *thd, if (thd->wsrep_conflict_state == ABORTED && command != COM_STMT_CLOSE && command != COM_QUIT) { + mysql_mutex_unlock(&thd->LOCK_thd_data); my_error(ER_LOCK_DEADLOCK, MYF(0), "wsrep aborted transaction"); WSREP_DEBUG("Deadlock error for: %s", thd->query()); - mysql_mutex_unlock(&thd->LOCK_thd_data); thd->reset_killed(); thd->mysys_var->abort = 0; thd->wsrep_conflict_state = NO_CONFLICT; @@ -7290,34 +7290,44 @@ static void wsrep_mysql_parse(THD *thd, char *rawbuf, uint length, thd->lex->sql_command != SQLCOM_SELECT && (thd->wsrep_retry_counter < thd->variables.wsrep_retry_autocommit)) { - WSREP_DEBUG("wsrep retrying AC query: %s", + mysql_mutex_unlock(&thd->LOCK_thd_data); + WSREP_DEBUG("wsrep retrying AC query: %s", (thd->query()) ? thd->query() : "void"); /* Performance Schema Interface instrumentation, end */ MYSQL_END_STATEMENT(thd->m_statement_psi, thd->get_stmt_da()); thd->m_statement_psi= NULL; thd->m_digest= NULL; + // Released thd->LOCK_thd_data above as below could end up + // close_thread_tables()/close_open_tables()/close_thread_table()/mysql_mutex_lock(&thd->LOCK_thd_data) close_thread_tables(thd); + mysql_mutex_lock(&thd->LOCK_thd_data); thd->wsrep_conflict_state= RETRY_AUTOCOMMIT; thd->wsrep_retry_counter++; // grow wsrep_copy_query(thd); thd->set_time(); parser_state->reset(rawbuf, length); + mysql_mutex_unlock(&thd->LOCK_thd_data); } else { - WSREP_DEBUG("%s, thd: %lu is_AC: %d, retry: %lu - %lu SQL: %s", - (thd->wsrep_conflict_state == ABORTED) ? + mysql_mutex_unlock(&thd->LOCK_thd_data); + // This does dirty read to wsrep variables but it is only a debug code + WSREP_DEBUG("%s, thd: %lu is_AC: %d, retry: %lu - %lu SQL: %s", + (thd->wsrep_conflict_state == ABORTED) ? "BF Aborted" : "cert failure", - thd->thread_id, is_autocommit, thd->wsrep_retry_counter, + thd->thread_id, is_autocommit, thd->wsrep_retry_counter, thd->variables.wsrep_retry_autocommit, thd->query()); my_error(ER_LOCK_DEADLOCK, MYF(0), "wsrep aborted transaction"); + + mysql_mutex_lock(&thd->LOCK_thd_data); thd->wsrep_conflict_state= NO_CONFLICT; if (thd->wsrep_conflict_state != REPLAYING) thd->wsrep_retry_counter= 0; // reset + mysql_mutex_unlock(&thd->LOCK_thd_data); } - mysql_mutex_unlock(&thd->LOCK_thd_data); + thd->reset_killed(); } else @@ -7353,6 +7363,7 @@ static void wsrep_mysql_parse(THD *thd, char *rawbuf, uint length, #endif /* WITH_WSREP */ } + /* When you modify mysql_parse(), you may need to modify mysql_test_parse_for_slave() in this same file.

1 0

[Commits] 25600829593: MDEV-13577 slave_parallel_mode=optimistic should not report the mode's
by andrei.elkin＠pp.inet.fi 06 Jun '18

06 Jun '18

revision-id: 256008295932c655aed7ed7b466b9409c45ce2e5 (mariadb-10.1.33-31-g25600829593) parent(s): 3b7da8a44c8a0ff4b40b37e4db01f7e397aefab5 author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-06-06 13:41:13 +0300 message: MDEV-13577 slave_parallel_mode=optimistic should not report the mode's specific temporary errors The optimistic parallel slave's worker thread could face a run-time error due to the algorithm's specifics which allows for conflicts like the reported "Can't find record in 'table'". A typical stack is like {noformat} #0 handler::print_error (this=0x61c00008f8a0, error=149, errflag=0) at handler.cc:3650 #1 0x0000555555e95361 in write_record (thd=thd@entry=0x62a0000a2208, table=table@entry=0x61f00008ce88, info=info@entry=0x7fffdee356d0) at sql_insert.cc:1944 #2 0x0000555555ea7767 in mysql_insert (thd=thd@entry=0x62a0000a2208, table_list=0x61b00012ada0, fields=..., values_list=..., update_fields=..., update_values=..., duplic=<optimized out>, ignore=<optimized out>) at sql_insert.cc:1039 #3 0x0000555555efda90 in mysql_execute_command (thd=thd@entry=0x62a0000a2208) at sql_parse.cc:3927 #4 0x0000555555f0cc50 in mysql_parse (thd=0x62a0000a2208, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at sql_parse.cc:7449 #5 0x00005555566d4444 in Query_log_event::do_apply_event (this=0x61200005b9c8, rgi=<optimized out>, query_arg=<optimized out>, q_len_arg=<optimized out>) at log_event.cc:4508 #6 0x00005555566d639e in Query_log_event::do_apply_event (this=<optimized out>, rgi=<optimized out>) at log_event.cc:4185 #7 0x0000555555d738cf in Log_event::apply_event (rgi=0x61d0001ea080, this=0x61200005b9c8) at log_event.h:1343 #8 apply_event_and_update_pos_apply (ev=ev@entry=0x61200005b9c8, thd=thd@entry=0x62a0000a2208, rgi=rgi@entry=0x61d0001ea080, reason=<optimized out>) at slave.cc:3479 #9 0x0000555555d8596b in apply_event_and_update_pos_for_parallel (ev=ev@entry=0x61200005b9c8, thd=thd@entry=0x62a0000a2208, rgi=rgi@entry=0x61d0001ea080) at slave.cc:3623 #10 0x00005555562aca83 in rpt_handle_event (qev=qev@entry=0x6190000fa088, rpt=rpt@entry=0x62200002bd68) at rpl_parallel.cc:50 #11 0x00005555562bd04e in handle_rpl_parallel_thread (arg=arg@entry=0x62200002bd68) at rpl_parallel.cc:1258 {noformat} Here {{handler::print_error}} computes whether to error log the current error when --log-warnings=2 inside {{my_message_sql()}} which can be eventually called. In the bug case the decision is to log. However in the optimistic mode slave applier case any conflict is attempted to resolve with rollback and retry to success. Hence the logging is at least extraneous. The case is fixed with refining my_message_sql() to downgrade optionally when --log-warnings=2 the formerly error level to the warning one when the error comes from the *optimistically* {{rpl_group_info::SPECULATE_OPTIMISTIC}} running parallel slave thread. In case of a specific to the optimistical mode ER_PRIOR_COMMIT_FAILED the parallel thread won't retry the error, but it remains to be reported properly with the error level through {{slave_output_error_info()}} as usual. --- .../extra/rpl_tests/rpl_init_debug_sync_func.inc | 39 +++++++++++++++ .../suite/rpl/r/rpl_parallel_optimistic.result | 46 ++++++++++++++++++ .../suite/rpl/t/rpl_parallel_optimistic.test | 56 ++++++++++++++++++++++ sql/mysqld.cc | 23 +++++++++ 4 files changed, 164 insertions(+) diff --git a/mysql-test/extra/rpl_tests/rpl_init_debug_sync_func.inc b/mysql-test/extra/rpl_tests/rpl_init_debug_sync_func.inc new file mode 100644 index 00000000000..5a3e84b9ea0 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_init_debug_sync_func.inc @@ -0,0 +1,39 @@ +# Define a stored function to inject a debug_sync into the appropriate +# slave worker THD. +# The function does nothing on the master, and on the +# slave it injects the desired debug_sync action(s). +# +# Slave must be running. server_1, server_2 connections are to the +# master and slave respectively. + +--connection server_1 +SET sql_log_bin=0; +--delimiter || +CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) + RETURNS INT DETERMINISTIC + BEGIN + RETURN x; + END +|| +--delimiter ; +SET sql_log_bin=1; +--save_master_pos + +--connection server_2 +SET sql_log_bin=0; +--delimiter || +CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) + RETURNS INT DETERMINISTIC + BEGIN + IF d1 != '' THEN + SET debug_sync = d1; + END IF; + IF d2 != '' THEN + SET debug_sync = d2; + END IF; + RETURN x; + END +|| +--delimiter ; +SET sql_log_bin=1; +--sync_with_master diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result index 0177e65b10f..24926ebcf6a 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result @@ -543,6 +543,52 @@ a b 57 7 58 8 59 9 +DELETE FROM t1; +DELETE FROM t2; +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +SET sql_log_bin=0; +CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) +RETURNS INT DETERMINISTIC +BEGIN +RETURN x; +END +|| +SET sql_log_bin=1; +SET sql_log_bin=0; +CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) +RETURNS INT DETERMINISTIC +BEGIN +IF d1 != '' THEN +SET debug_sync = d1; +END IF; +IF d2 != '' THEN +SET debug_sync = d2; +END IF; +RETURN x; +END +|| +SET sql_log_bin=1; +BEGIN; +INSERT INTO t2 SET a=1; +SET @save.binlog_format=@@session.binlog_format; +SET @@SESSION.binlog_format=statement; +BEGIN; +INSERT INTO t2 SET a=1; +INSERT INTO t1 SET a=1; +COMMIT; +SET @@SESSION.binlog_format=mixed; +BEGIN; +INSERT INTO t2 SET a=foo(100, 'rpl_parallel_retry_after_unmark WAIT_FOR go_retrying', ''); +DELETE FROM t1 WHERE a=sleep(0) + 1; +COMMIT; +ROLLBACK; +SET debug_sync='now SIGNAL go_retrying'; +SET @@SESSION.binlog_format= @save.binlog_format; +DROP FUNCTION foo; +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +SET debug_sync='RESET'; include/stop_slave.inc SET GLOBAL slave_parallel_mode=@old_parallel_mode; SET GLOBAL slave_parallel_threads=@old_parallel_threads; diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test index 41fb6ebb72e..555ed9b93ca 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test @@ -480,6 +480,62 @@ SELECT * FROM t2 WHERE a >= 40 ORDER BY a; SELECT * FROM t1 WHERE a >= 40 ORDER BY a; SELECT * FROM t2 WHERE a >= 40 ORDER BY a; +# partial cleanup to reuse the tables by following tests +--connection server_1 +DELETE FROM t1; +DELETE FROM t2; +--source include/save_master_gtid.inc + +--connection server_2 +--source include/sync_with_master_gtid.inc + +# +# MDEV-13577 optimistic parallel slave errors out to error log unnecessary +# +--source extra/rpl_tests/rpl_init_debug_sync_func.inc + +# The 1st of the following two trx blocker on slave +--connection server_2 +BEGIN; +INSERT INTO t2 SET a=1; + +--connection server_1 +SET @save.binlog_format=@@session.binlog_format; +SET @@SESSION.binlog_format=statement; + +BEGIN; +INSERT INTO t2 SET a=1; +INSERT INTO t1 SET a=1; +COMMIT; + +# This transaction is going to win optimistical race with above INSERT +# on slave only to error out at the first attempt due to overly optimistic DELETE +# but to retry to succeed as specified. +SET @@SESSION.binlog_format=mixed; +BEGIN; + INSERT INTO t2 SET a=foo(100, 'rpl_parallel_retry_after_unmark WAIT_FOR go_retrying', ''); + DELETE FROM t1 WHERE a=sleep(0) + 1; +COMMIT; + +# First make sure DELETE raced indeed to get stuck: +--connection server_2 +--let $wait_condition= SELECT COUNT(*) > 0 FROM information_schema.processlist WHERE state = "debug sync point: rpl_parallel_retry_after_unmark" +--source include/wait_condition.inc + +# Next release the 1st trx to commit it and then release DELETE +--connection server_2 +ROLLBACK; +SET debug_sync='now SIGNAL go_retrying'; + +--connection server_1 +SET @@SESSION.binlog_format= @save.binlog_format; +DROP FUNCTION foo; +--source include/save_master_gtid.inc + +--connection server_2 +--source include/sync_with_master_gtid.inc +SET debug_sync='RESET'; + # Clean up. --connection server_2 diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 5f954f7576d..adcbc9bf11b 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -3487,6 +3487,28 @@ static void check_data_home(const char *path) #endif /*!EMBEDDED_LIBRARY*/ #endif /* __WIN__*/ +/** + Helper function for @c my_message_sql() to decide + how to errorlog for the slave thread worker + when it executes in the OPTIMISTIC parallel mode. A persistent + error by the slave worker does not escape being left out to + the driver thread to report. +*/ +inline void fix_func_for_slave_worker(THD *thd, + sql_print_message_func *ptr_func) +{ + DBUG_ASSERT(!(thd && thd->system_thread == SYSTEM_THREAD_SLAVE_SQL) || + thd->rgi_slave); + + if (thd && thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && + thd->rgi_slave->speculation == rpl_group_info::SPECULATE_OPTIMISTIC) + { + if (global_system_variables.log_warnings > 1) + { + *ptr_func= sql_print_warning; + } + } +} /** All global error messages are sent here where the first one is stored @@ -3535,6 +3557,7 @@ void my_message_sql(uint error, const char *str, myf MyFlags) /* When simulating OOM, skip writing to error log to avoid mtr errors */ DBUG_EXECUTE_IF("simulate_out_of_memory", DBUG_VOID_RETURN;); + fix_func_for_slave_worker(thd, &func); if (!thd || thd->log_all_errors || (MyFlags & ME_NOREFRESH)) (*func)("%s: %s", my_progname_short, str); /* purecov: inspected */ DBUG_VOID_RETURN;

1 0

[Commits] 84c9ec5: MDEV-15473 Isolate/sandbox PAM modules, so that they can't crash the server.
by holyfoot＠askmonty.org 06 Jun '18

06 Jun '18

revision-id: 84c9ec5552bf14bda826912fb045ae36f34a4490 (mariadb-10.3.6-9-g84c9ec5) parent(s): 637af7838316a49267e55cde7cf96e23f63e5c9d committer: Alexey Botchkov timestamp: 2018-06-06 14:13:07 +0400 message: MDEV-15473 Isolate/sandbox PAM modules, so that they can't crash the server. Crash-safe version of the PAM plugin added. It runs the auth_pam_tool application that actually calls the PAM modules. --- plugin/auth_pam/CMakeLists.txt | 3 + plugin/auth_pam/auth_pam.c | 141 ++---------------------- plugin/auth_pam/auth_pam_base.c | 173 +++++++++++++++++++++++++++++ plugin/auth_pam/auth_pam_safe.c | 235 ++++++++++++++++++++++++++++++++++++++++ plugin/auth_pam/auth_pam_tool.c | 115 ++++++++++++++++++++ plugin/auth_pam/auth_pam_tool.h | 75 +++++++++++++ 6 files changed, 608 insertions(+), 134 deletions(-) diff --git a/plugin/auth_pam/CMakeLists.txt b/plugin/auth_pam/CMakeLists.txt index 5131752..2371a66 100644 --- a/plugin/auth_pam/CMakeLists.txt +++ b/plugin/auth_pam/CMakeLists.txt @@ -9,5 +9,8 @@ IF(HAVE_PAM_APPL_H) ADD_DEFINITIONS(-DHAVE_STRNDUP) ENDIF(HAVE_STRNDUP) MYSQL_ADD_PLUGIN(auth_pam auth_pam.c LINK_LIBRARIES pam MODULE_ONLY) + MYSQL_ADD_PLUGIN(auth_pam_safe auth_pam_safe.c LINK_LIBRARIES pam dl MODULE_ONLY) + MYSQL_ADD_EXECUTABLE(auth_pam_tool auth_pam_tool.c) + TARGET_LINK_LIBRARIES(auth_pam_tool pam) ENDIF(HAVE_PAM_APPL_H) diff --git a/plugin/auth_pam/auth_pam.c b/plugin/auth_pam/auth_pam.c index ffc3d6f..a1e42f9 100644 --- a/plugin/auth_pam/auth_pam.c +++ b/plugin/auth_pam/auth_pam.c @@ -17,157 +17,30 @@ #define _GNU_SOURCE 1 /* for strndup */ #include <mysql/plugin_auth.h> -#include <stdio.h> -#include <string.h> -#include <security/pam_appl.h> -#include <security/pam_modules.h> struct param { unsigned char buf[10240], *ptr; MYSQL_PLUGIN_VIO *vio; }; -/* It least solaris doesn't have strndup */ - -#ifndef HAVE_STRNDUP -char *strndup(const char *from, size_t length) +static int write_packet(struct param *param, const unsigned char *buf, + int buf_len) { - char *ptr; - size_t max_length= strlen(from); - if (length > max_length) - length= max_length; - if ((ptr= (char*) malloc(length+1)) != 0) - { - memcpy((char*) ptr, (char*) from, length); - ptr[length]=0; - } - return ptr; + return param->vio->write_packet(param->vio, buf, buf_len); } -#endif - -#ifndef DBUG_OFF -static char pam_debug = 0; -#define PAM_DEBUG(X) do { if (pam_debug) { fprintf X; } } while(0) -#else -#define PAM_DEBUG(X) /* no-op */ -#endif -static int conv(int n, const struct pam_message **msg, - struct pam_response **resp, void *data) +static int read_packet(struct param *param, unsigned char **pkt) { - struct param *param = (struct param *)data; - unsigned char *end = param->buf + sizeof(param->buf) - 1; - int i; - - *resp = 0; - - for (i = 0; i < n; i++) - { - /* if there's a message - append it to the buffer */ - if (msg[i]->msg) - { - int len = strlen(msg[i]->msg); - if (len > end - param->ptr) - len = end - param->ptr; - if (len > 0) - { - memcpy(param->ptr, msg[i]->msg, len); - param->ptr+= len; - *(param->ptr)++ = '\n'; - } - } - /* if the message style is *_PROMPT_*, meaning PAM asks a question, - send the accumulated text to the client, read the reply */ - if (msg[i]->msg_style == PAM_PROMPT_ECHO_OFF || - msg[i]->msg_style == PAM_PROMPT_ECHO_ON) - { - int pkt_len; - unsigned char *pkt; - - /* allocate the response array. - freeing it is the responsibility of the caller */ - if (*resp == 0) - { - *resp = calloc(sizeof(struct pam_response), n); - if (*resp == 0) - return PAM_BUF_ERR; - } - - /* dialog plugin interprets the first byte of the packet - as the magic number. - 2 means "read the input with the echo enabled" - 4 means "password-like input, echo disabled" - C'est la vie. */ - param->buf[0] = msg[i]->msg_style == PAM_PROMPT_ECHO_ON ? 2 : 4; - PAM_DEBUG((stderr, "PAM: conv: send(%.*s)\n", (int)(param->ptr - param->buf - 1), param->buf)); - if (param->vio->write_packet(param->vio, param->buf, param->ptr - param->buf - 1)) - return PAM_CONV_ERR; - - pkt_len = param->vio->read_packet(param->vio, &pkt); - if (pkt_len < 0) - { - PAM_DEBUG((stderr, "PAM: conv: recv() ERROR\n")); - return PAM_CONV_ERR; - } - PAM_DEBUG((stderr, "PAM: conv: recv(%.*s)\n", pkt_len, pkt)); - /* allocate and copy the reply to the response array */ - if (!((*resp)[i].resp= strndup((char*) pkt, pkt_len))) - return PAM_CONV_ERR; - param->ptr = param->buf + 1; - } - } - return PAM_SUCCESS; + return param->vio->read_packet(param->vio, pkt); } -#define DO(X) if ((status = (X)) != PAM_SUCCESS) goto end - -#if defined(SOLARIS) || defined(__sun) -typedef void** pam_get_item_3_arg; -#else -typedef const void** pam_get_item_3_arg; -#endif +#include "auth_pam_base.c" static int pam_auth(MYSQL_PLUGIN_VIO *vio, MYSQL_SERVER_AUTH_INFO *info) { - pam_handle_t *pamh = NULL; - int status; - const char *new_username= NULL; struct param param; - /* The following is written in such a way to make also solaris happy */ - struct pam_conv pam_start_arg = { &conv, (char*) &param }; - - /* - get the service name, as specified in - - CREATE USER ... IDENTIFIED WITH pam AS "service" - */ - const char *service = info->auth_string && info->auth_string[0] - ? info->auth_string : "mysql"; - - param.ptr = param.buf + 1; param.vio = vio; - - PAM_DEBUG((stderr, "PAM: pam_start(%s, %s)\n", service, info->user_name)); - DO( pam_start(service, info->user_name, &pam_start_arg, &pamh) ); - - PAM_DEBUG((stderr, "PAM: pam_authenticate(0)\n")); - DO( pam_authenticate (pamh, 0) ); - - PAM_DEBUG((stderr, "PAM: pam_acct_mgmt(0)\n")); - DO( pam_acct_mgmt(pamh, 0) ); - - PAM_DEBUG((stderr, "PAM: pam_get_item(PAM_USER)\n")); - DO( pam_get_item(pamh, PAM_USER, (pam_get_item_3_arg) &new_username) ); - - if (new_username && strcmp(new_username, info->user_name)) - strncpy(info->authenticated_as, new_username, - sizeof(info->authenticated_as)); - info->authenticated_as[sizeof(info->authenticated_as)-1]= 0; - -end: - pam_end(pamh, status); - PAM_DEBUG((stderr, "PAM: status = %d user = %s\n", status, info->authenticated_as)); - return status == PAM_SUCCESS ? CR_OK : CR_ERROR; + return pam_auth_base(&param, info); } static struct st_mysql_auth info = diff --git a/plugin/auth_pam/auth_pam_base.c b/plugin/auth_pam/auth_pam_base.c new file mode 100644 index 0000000..885e6f0 --- /dev/null +++ b/plugin/auth_pam/auth_pam_base.c @@ -0,0 +1,173 @@ +/* + Copyright (c) 2011, 2012, Monty Program Ab + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02111-1301 USA */ + +// struct param { + // unsigned char buf[10240], *ptr; + // MYSQL_PLUGIN_VIO *vio; +// }; +//static int write_packet(struct param *param, const unsigned char *buf, + //int buf_len) +//static int read_packet(struct param *param, unsigned char **pkt) + +#define _GNU_SOURCE 1 /* for strndup */ + +#include <stdio.h> +#include <string.h> +#include <security/pam_appl.h> +#include <security/pam_modules.h> + +/* It least solaris doesn't have strndup */ + +#ifndef HAVE_STRNDUP +char *strndup(const char *from, size_t length) +{ + char *ptr; + size_t max_length= strlen(from); + if (length > max_length) + length= max_length; + if ((ptr= (char*) malloc(length+1)) != 0) + { + memcpy((char*) ptr, (char*) from, length); + ptr[length]=0; + } + return ptr; +} +#endif + +#ifndef DBUG_OFF +static char pam_debug = 0; +#define PAM_DEBUG(X) do { if (pam_debug) { fprintf X; } } while(0) +#else +#define PAM_DEBUG(X) /* no-op */ +#endif + +static int conv(int n, const struct pam_message **msg, + struct pam_response **resp, void *data) +{ + struct param *param = (struct param *)data; + unsigned char *end = param->buf + sizeof(param->buf) - 1; + int i; + + *resp = 0; + + for (i = 0; i < n; i++) + { + /* if there's a message - append it to the buffer */ + if (msg[i]->msg) + { + int len = strlen(msg[i]->msg); + if (len > end - param->ptr) + len = end - param->ptr; + if (len > 0) + { + memcpy(param->ptr, msg[i]->msg, len); + param->ptr+= len; + *(param->ptr)++ = '\n'; + } + } + /* if the message style is *_PROMPT_*, meaning PAM asks a question, + send the accumulated text to the client, read the reply */ + if (msg[i]->msg_style == PAM_PROMPT_ECHO_OFF || + msg[i]->msg_style == PAM_PROMPT_ECHO_ON) + { + int pkt_len; + unsigned char *pkt; + + /* allocate the response array. + freeing it is the responsibility of the caller */ + if (*resp == 0) + { + *resp = calloc(sizeof(struct pam_response), n); + if (*resp == 0) + return PAM_BUF_ERR; + } + + /* dialog plugin interprets the first byte of the packet + as the magic number. + 2 means "read the input with the echo enabled" + 4 means "password-like input, echo disabled" + C'est la vie. */ + param->buf[0] = msg[i]->msg_style == PAM_PROMPT_ECHO_ON ? 2 : 4; + PAM_DEBUG((stderr, "PAM: conv: send(%.*s)\n", + (int)(param->ptr - param->buf - 1), param->buf)); + if (write_packet(param, param->buf, param->ptr - param->buf - 1)) + return PAM_CONV_ERR; + + pkt_len = read_packet(param, &pkt); + if (pkt_len < 0) + { + PAM_DEBUG((stderr, "PAM: conv: recv() ERROR\n")); + return PAM_CONV_ERR; + } + PAM_DEBUG((stderr, "PAM: conv: recv(%.*s)\n", pkt_len, pkt)); + /* allocate and copy the reply to the response array */ + if (!((*resp)[i].resp= strndup((char*) pkt, pkt_len))) + return PAM_CONV_ERR; + param->ptr = param->buf + 1; + } + } + return PAM_SUCCESS; +} + +#define DO(X) if ((status = (X)) != PAM_SUCCESS) goto end + +#if defined(SOLARIS) || defined(__sun) +typedef void** pam_get_item_3_arg; +#else +typedef const void** pam_get_item_3_arg; +#endif + +static int pam_auth_base(struct param *param, MYSQL_SERVER_AUTH_INFO *info) +{ + pam_handle_t *pamh = NULL; + int status; + const char *new_username= NULL; + /* The following is written in such a way to make also solaris happy */ + struct pam_conv pam_start_arg = { &conv, (char*) param }; + + /* + get the service name, as specified in + + CREATE USER ... IDENTIFIED WITH pam AS "service" + */ + const char *service = info->auth_string && info->auth_string[0] + ? info->auth_string : "mysql"; + + param->ptr = param->buf + 1; + + PAM_DEBUG((stderr, "PAM: pam_start(%s, %s)\n", service, info->user_name)); + DO( pam_start(service, info->user_name, &pam_start_arg, &pamh) ); + + PAM_DEBUG((stderr, "PAM: pam_authenticate(0)\n")); + DO( pam_authenticate (pamh, 0) ); + + PAM_DEBUG((stderr, "PAM: pam_acct_mgmt(0)\n")); + DO( pam_acct_mgmt(pamh, 0) ); + + PAM_DEBUG((stderr, "PAM: pam_get_item(PAM_USER)\n")); + DO( pam_get_item(pamh, PAM_USER, (pam_get_item_3_arg) &new_username) ); + + if (new_username && strcmp(new_username, info->user_name)) + strncpy(info->authenticated_as, new_username, + sizeof(info->authenticated_as)); + info->authenticated_as[sizeof(info->authenticated_as)-1]= 0; + +end: + pam_end(pamh, status); + PAM_DEBUG((stderr, "PAM: status = %d user = %s\n", status, info->authenticated_as)); + return status == PAM_SUCCESS ? CR_OK : CR_ERROR; +} + diff --git a/plugin/auth_pam/auth_pam_safe.c b/plugin/auth_pam/auth_pam_safe.c new file mode 100644 index 0000000..28c10b8 --- /dev/null +++ b/plugin/auth_pam/auth_pam_safe.c @@ -0,0 +1,235 @@ +/* + Copyright (c) 2011, 2012, Monty Program Ab + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02111-1301 USA */ + +#define _GNU_SOURCE 1 /* for strndup */ + +#include <unistd.h> +#include <string.h> +#include <mysql/plugin_auth.h> +#include "auth_pam_tool.h" +#include <my_global.h> + +#ifndef DBUG_OFF +static char pam_debug = 0; +#define PAM_DEBUG(X) do { if (pam_debug) { fprintf X; } } while(0) +#else +#define PAM_DEBUG(X) /* no-op */ +#endif + +static char *opt_plugin_dir; /* To be dynamically linked. */ +static const char *tool_name= "auth_pam_tool"; +static const int tool_name_len= 13; + +static int pam_auth(MYSQL_PLUGIN_VIO *vio, MYSQL_SERVER_AUTH_INFO *info) +{ + int p_to_c[2], c_to_p[2]; /* Parent-to-child and child-to-parent pipes. */ + pid_t proc_id; + int result= CR_ERROR;; + + PAM_DEBUG((stderr, "PAM: opening pipes.\n")); + if (pipe(p_to_c) < 0 || pipe(c_to_p) < 0) + { + /* Error creating pipes. */ + return CR_ERROR; + } + PAM_DEBUG((stderr, "PAM: forking.\n")); + if ((proc_id= fork()) < 0) + { + /* Error forking. */ + close(p_to_c[0]); + close(c_to_p[1]); + goto error_ret; + } + + if (proc_id == 0) + { + /* The 'sandbox' process started. */ + const char *arg[5]; + char toolpath[FN_REFLEN]; + size_t plugin_dir_len; + + PAM_DEBUG((stderr, "PAM: Child process prepares pipes.\n")); + + if (close(p_to_c[1]) < 0 || + close(c_to_p[0]) < 0 || + dup2(p_to_c[0], 0) < 0 || /* Parent's pipe to STDIN. */ + dup2(c_to_p[1], 1) < 0) /* Sandbox's pipe to STDOUT. */ + { + exit(-1); + } + + PAM_DEBUG((stderr, "PAM: check tool directory: %s, %s.\n", + opt_plugin_dir, tool_name)); + plugin_dir_len= strlen(opt_plugin_dir); + if (plugin_dir_len + tool_name_len + 2 > sizeof(toolpath)) + { + /* Tool path too long. */ + exit(-1); + } + + memcpy(toolpath, opt_plugin_dir, plugin_dir_len); + if (plugin_dir_len && toolpath[plugin_dir_len-1] != FN_LIBCHAR) + toolpath[plugin_dir_len++]= FN_LIBCHAR; + memcpy(toolpath+plugin_dir_len, tool_name, tool_name_len+1); + + arg[0]= toolpath; + arg[1]= info->user_name; + arg[2]= info->auth_string; + if (pam_debug) + { + arg[3]= "DEBUG"; + arg[4]= 0; + } + else + arg[3]= 0; + + PAM_DEBUG((stderr, "PAM: execute pam sandbox:[%s] [%s] [%s] [%s].\n", + arg[0], arg[1], arg[2], + arg[3] ? arg[3] : "No debug")); + (void) execv(arg[0], (char **) arg); + PAM_DEBUG((stderr, "PAM: exec() failed.\n")); + exit(-1); + } + + /* Parent process continues. */ + + PAM_DEBUG((stderr, "PAM: parent continues.\n")); + if (close(p_to_c[0]) < 0 || + close(c_to_p[1]) < 0) + goto error_ret; + + for (;;) + { + unsigned char field; + + PAM_DEBUG((stderr, "PAM: listening to the sandbox.\n")); + if (read(c_to_p[0], &field, 1) < 1) + { + PAM_DEBUG((stderr, "PAM: read failed.\n")); + goto error_ret; + } + + if (field == AP_EOF) + { + PAM_DEBUG((stderr, "PAM: auth OK returned.\n")); + break; + } + + switch (field) + { + case AP_AUTHENTICATED_AS: + PAM_DEBUG((stderr, "PAM: reading authenticated_as string.\n")); + if (read_string(c_to_p[0], info->authenticated_as, + sizeof(info->authenticated_as)) < 0) + goto error_ret; + break; + + case AP_CONV: + { + unsigned char buf[10240]; + int buf_len; + unsigned char *pkt; + + PAM_DEBUG((stderr, "PAM: getting CONV string.\n")); + if ((buf_len= read_string(c_to_p[0], (char *) buf, sizeof(buf))) < 0) + goto error_ret; + + PAM_DEBUG((stderr, "PAM: sending CONV string.\n")); + if (vio->write_packet(vio, buf, buf_len)) + goto error_ret; + + PAM_DEBUG((stderr, "PAM: reading CONV answer.\n")); + if ((buf_len= vio->read_packet(vio, &pkt)) < 0) + goto error_ret; + + PAM_DEBUG((stderr, "PAM: answering CONV.\n")); + if (write_string(p_to_c[1], pkt, buf_len)) + goto error_ret; + } + break; + + default: + PAM_DEBUG((stderr, "PAM: unknown sandbox field.\n")); + goto error_ret; + } + } + result= CR_OK; + +error_ret: + close(p_to_c[1]); + close(c_to_p[0]); + + PAM_DEBUG((stderr, "PAM: auth result %d.\n", result)); + return result; +} + +static struct st_mysql_auth info = +{ + MYSQL_AUTHENTICATION_INTERFACE_VERSION, + "dialog", + pam_auth +}; + +static char use_cleartext_plugin; +static MYSQL_SYSVAR_BOOL(use_cleartext_plugin, use_cleartext_plugin, + PLUGIN_VAR_NOCMDARG | PLUGIN_VAR_READONLY, + "Use mysql_cleartext_plugin on the client side instead of the dialog " + "plugin. This may be needed for compatibility reasons, but it only " + "supports simple PAM policies that don't require anything besides " + "a password", NULL, NULL, 0); + +#ifndef DBUG_OFF +static MYSQL_SYSVAR_BOOL(debug, pam_debug, PLUGIN_VAR_OPCMDARG, + "Log all PAM activity", NULL, NULL, 0); +#endif + + +static struct st_mysql_sys_var* vars[] = { + MYSQL_SYSVAR(use_cleartext_plugin), +#ifndef DBUG_OFF + MYSQL_SYSVAR(debug), +#endif + NULL +}; + + +static int init(void *p __attribute__((unused))) +{ + if (use_cleartext_plugin) + info.client_auth_plugin= "mysql_clear_password"; + if (!(opt_plugin_dir= dlsym(RTLD_DEFAULT, "opt_plugin_dir"))) + return 1; + + return 0; +} + +maria_declare_plugin(pam) +{ + MYSQL_AUTHENTICATION_PLUGIN, + &info, + "pam", + "MariaDB Corp", + "PAM based authentication (safe)", + PLUGIN_LICENSE_GPL, + init, + NULL, + 0x0100, + NULL, + vars, + "1.0", + MariaDB_PLUGIN_MATURITY_STABLE +} +maria_declare_plugin_end; diff --git a/plugin/auth_pam/auth_pam_tool.c b/plugin/auth_pam/auth_pam_tool.c new file mode 100644 index 0000000..e4addd3 --- /dev/null +++ b/plugin/auth_pam/auth_pam_tool.c @@ -0,0 +1,115 @@ +/* + Copyright (c) 2011, 2012, Monty Program Ab + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02111-1301 USA */ + +#define _GNU_SOURCE 1 /* for strndup */ + +#include <stdlib.h> +#include <unistd.h> +#include <mysql/plugin_auth_common.h> + +struct param { + unsigned char buf[10240], *ptr; +}; + + +#include "auth_pam_tool.h" + + +static int write_packet(struct param *param __attribute__((unused)), + const unsigned char *buf, int buf_len) +{ + unsigned char b= AP_CONV; + return write(1, &b, 1) < 1 || + write_string(1, buf, buf_len); +} + + +static int read_packet(struct param *param, unsigned char **pkt) +{ + *pkt= (unsigned char *) param->buf; + return read_string(0, (char *) param->buf, (int) sizeof(param->buf)) - 1; +} + + +typedef struct st_mysql_server_auth_info +{ + /** + User name as sent by the client and shown in USER(). + NULL if the client packet with the user name was not received yet. + */ + char *user_name; + + /** + A corresponding column value from the mysql.user table for the + matching account name + */ + char *auth_string; + + /** + Matching account name as found in the mysql.user table. + A plugin can override it with another name that will be + used by MySQL for authorization, and shown in CURRENT_USER() + */ + char authenticated_as[MYSQL_USERNAME_LENGTH+1]; +} MYSQL_SERVER_AUTH_INFO; + + +#include "auth_pam_base.c" + + +int main(int argc, char **argv) +{ + struct param param; + MYSQL_SERVER_AUTH_INFO info; + unsigned char field; + int res; + + if (argc < 3) + return -1; + + info.user_name= argv[1]; + info.auth_string= argv[2]; + + if (argc >= 4) + pam_debug= 1; + + PAM_DEBUG((stderr, "PAM: sandbox started [%s] [%s] [%s] [%s].\n", argv[0], + info.user_name, info.auth_string, + pam_debug ? argv[3] : "No debug")); + + if ((res= pam_auth_base(&param, &info)) != CR_OK) + { + PAM_DEBUG((stderr, "PAM: auth failed, sandbox closed.\n")); + return -1; + } + + if (info.authenticated_as[0]) + { + PAM_DEBUG((stderr, "PAM: send authenticated_as field.\n")); + field= AP_AUTHENTICATED_AS; + if (write(1, &field, 1) < 1 || + write_string(1, (unsigned char *) info.authenticated_as, + strlen(info.authenticated_as))) + return -1; + } + + PAM_DEBUG((stderr, "PAM: send OK result.\n")); + field= AP_EOF; + write(1, &field, 1); + + PAM_DEBUG((stderr, "PAM: sandbox closed.\n")); + return 0; +} diff --git a/plugin/auth_pam/auth_pam_tool.h b/plugin/auth_pam/auth_pam_tool.h new file mode 100644 index 0000000..0df3006 --- /dev/null +++ b/plugin/auth_pam/auth_pam_tool.h @@ -0,0 +1,75 @@ +/* + Copyright (c) 2018 MariaDB Corporation + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02111-1301 USA */ + +#define AP_USER 'U' +#define AP_AUTHENTICATED_AS 'A' +#define AP_CONV 'C' +#define AP_EOF 'E' + + +static int read_length(int file) +{ + unsigned char hdr[2]; + + if (read(file, hdr, 2) < 2) + return -1; + + return (((int) hdr[0]) << 8) + (int) hdr[1]; +} + + +static void store_length(int len, unsigned char *p_len) +{ + p_len[0]= (unsigned char) ((len >> 8) & 0xFF); + p_len[1]= (unsigned char) (len & 0xFF); +} + + +/* + Returns the length of the string read, + or -1 on error. +*/ + +static int read_string(int file, char *s, int s_size) +{ + int len; + + len= read_length(file); + + if (len <= 0 || len > s_size-1 || + read(file, s, len) < len) + return -1; + + s[len]= 0; + + return len; +} + + +/* + Returns 0 on success. +*/ + +static int write_string(int file, const unsigned char *s, int s_len) +{ + unsigned char hdr[2]; + store_length(s_len, hdr); + return write(file, hdr, 2) < 2 || + write(file, s, s_len) < s_len; +} + + +#define MAX_PAM_SERVICE_NAME 1024

1 0