- commits - lists.mariadb.org

[Commits] e5c26fdfab3: Merge branch '5.5' into bb-10.0-merge
by Oleksandr Byelkin 17 Jul '18

17 Jul '18

revision-id: e5c26fdfab3d43ad2b0524a3bf29f59f6fab37d8 (mariadb-10.0.35-58-ge5c26fdfab3) parent(s): 1fd84f9129f2ed98706f6e225b06b16a13d0ebd0 2fbf2277ffec86d69f793534da7043b6dd540780 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-17 16:56:21 +0200 message: Merge branch '5.5' into bb-10.0-merge mysql-test/r/derived.result | 33 +++++++++++++++++++++ mysql-test/r/join.result | 40 +++++++++++++++++++++++++ mysql-test/r/join_cache.result | 33 +++++++++++++++++++++ mysql-test/r/subselect_sj2_mat.result | 51 ++++++++++++++++++++++++++++++++ mysql-test/suite/vcol/t/vcol_misc.test | 2 ++ mysql-test/t/derived.test | 21 ++++++++++++++ mysql-test/t/join.test | 53 ++++++++++++++++++++++++++++++++++ mysql-test/t/join_cache.test | 32 +++++++++++++++++++- mysql-test/t/subselect_sj2_mat.test | 49 +++++++++++++++++++++++++++++++ mysql-test/valgrind.supp | 13 +++++++++ scripts/mysql_install_db.sh | 5 ++++ sql/log.cc | 10 +++---- sql/opt_subselect.cc | 3 +- sql/sql_base.cc | 12 ++++---- sql/sql_select.cc | 4 +-- sql/table.cc | 19 ++++++++---- 16 files changed, 361 insertions(+), 19 deletions(-) diff --cc mysql-test/r/join.result index d500b38a8dc,7b0e7807e39..d065403371d --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@@ -1500,54 -1498,42 +1500,94 @@@ DROP VIEW v2 DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; # + # MDEV-16512 + # Server crashes in find_field_in_table_ref on 2nd execution of SP referring to + # non-existing field + # + CREATE TABLE t (i INT); + CREATE PROCEDURE p() SELECT t1.f FROM t AS t1 JOIN t AS t2 USING (f); + CALL p; + ERROR 42S22: Unknown column 'f' in 'from clause' + CALL p; + ERROR 42S22: Unknown column 'f' in 'from clause' + FLUSH TABLES; + CALL p; + ERROR 42S22: Unknown column 'f' in 'from clause' + DROP TABLE t; + CREATE TABLE t (f INT); + CALL p; + ERROR 42S22: Unknown column 'f' in 'from clause' + DROP TABLE t; + CREATE TABLE t (i INT); + CALL p; + ERROR 42S22: Unknown column 'f' in 'from clause' + DROP PROCEDURE p; + DROP TABLE t; + CREATE TABLE t1 (a INT, b INT); + CREATE TABLE t2 (a INT); + CREATE TABLE t3 (a INT, c INT); + CREATE TABLE t4 (a INT, c INT); + CREATE TABLE t5 (a INT, c INT); + CREATE PROCEDURE p1() SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) + LEFT JOIN t5 USING (a)) USING (a); + CALL p1; + ERROR 23000: Column 'c' in field list is ambiguous + CALL p1; + ERROR 23000: Column 'c' in field list is ambiguous + DROP PROCEDURE p1; + DROP TABLE t1,t2,t3,t4,t5; + # + # End of MariaDB 5.5 tests + # ++# +# Bug #35268: Parser can't handle STRAIGHT_JOIN with USING +# +CREATE TABLE t1 (a int); +INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); +CREATE TABLE t2 (a int); +INSERT INTO t2 (a) VALUES (1),(2),(3),(4); +EXPLAIN +SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a; +a +1 +2 +3 +4 +EXPLAIN +SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a; +a +1 +2 +3 +4 +EXPLAIN +SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a; +a +1 +2 +3 +4 +DROP TABLE t1,t2; +# +# MDEV-5635: join of a const table with non-const tables +# +CREATE TABLE t1 (a varchar(3) NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'); +CREATE TABLE t2 (b varchar(3), c varchar(3), INDEX(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('bar', 'bar'),( 'qux', 'qux'); +SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2 +WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a ); +a b c b c +DROP TABLE t1,t2; diff --cc mysql-test/r/join_cache.result index f2383ce2681,f1e6fb577c8..18c3a057398 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@@ -5871,86 -5872,36 +5871,119 @@@ SET join_buffer_space_limit= default set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t4,t5,t2; # + # MDEV-16603: BNLH for query with materialized semi-join + # + set join_cache_level=4; + CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; + INSERT INTO t1 VALUES (7,'x'); + CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; + INSERT INTO t2 VALUES + (NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), + (228,'x'),(3,'y'),(1,'z'),(9,'z'); + CREATE TABLE temp + SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); + SELECT * FROM temp + WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); + f1 f2 + 7 x + 7 x + 7 x + 7 x + 7 x + 7 x + 7 x + EXPLAIN EXTENDED SELECT * FROM temp + WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); + id select_type table type possible_keys key key_len ref rows filtered Extra + 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 + 1 PRIMARY temp hash_ALL NULL #hash#$hj 9 test.t1.i1,test.t1.v1 7 100.00 Using where; Using join buffer (flat, BNLH join) + 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 1 100.00 Using where + 2 MATERIALIZED t2 hash_index v1 #hash#v1:v1 4:9 test.t1.v1 10 10.00 Using join buffer (flat, BNLH join) + Warnings: + Note 1003 select `test`.`temp`.`f1` AS `f1`,`test`.`temp`.`f2` AS `f2` from `test`.`temp` semi join (`test`.`t2` join `test`.`t1`) where ((`test`.`temp`.`f1` = `test`.`t1`.`i1`) and (`test`.`t2`.`v1` = `test`.`t1`.`v1`) and (`test`.`temp`.`f2` = `test`.`t1`.`v1`)) + DROP TABLE t1,t2,temp; + SET join_cache_level = default; ++# +# MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. +# +set join_cache_level=default; +set expensive_subquery_limit=0; +create table t1 (c1 int); +create table t2 (c2 int); +create table t3 (c3 int); +insert into t1 values (1), (2); +insert into t2 values (1), (2); +insert into t3 values (2); +explain +select count(*) from t1 straight_join t2 +where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 UNCACHEABLE SUBQUERY t3 system NULL NULL NULL NULL 1 +set @counter=0; +select count(*) from t1 straight_join t2 +where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); +count(*) +2 +select @counter; +@counter +2 +explain +select count(*) from t1 straight_join t2 +where c1 = c2-0 and +c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and +c2 / 2 = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 UNCACHEABLE SUBQUERY t3 system NULL NULL NULL NULL 1 +set @counter=0; +select count(*) from t1 straight_join t2 +where c1 = c2-0 and +c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and +c2 / 2 = 1; +count(*) +1 +select @counter; +@counter +2 +drop table t1,t2,t3; +set expensive_subquery_limit=default; +# +# MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down +# +create table t1 (a int); +insert into t1 values +(7), (9), (1), (4), (2), (3), (5), (8), (11), (6), (10); +explain select count(*) from t1, t1 t2 where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 +1 SIMPLE t2 ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) +set join_buffer_space_limit=1024*8; +select @@join_buffer_space_limit; +@@join_buffer_space_limit +8192 +select @@join_buffer_size; +@@join_buffer_size +131072 +explain select count(*) from t1, t1 t2 where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 +1 SIMPLE t2 ALL NULL NULL NULL NULL 11 Using where +set join_buffer_space_limit=default; +drop table t1; +# +# MDEV-6687: Assertion `0' failed in Protocol::end_statement on query +# +SET join_cache_level = 3; +# The following should have +# - table order PROFILING,user, +# - table user accessed with hash_ALL: +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = PAGE_FAULTS_MINOR; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL Using where +1 SIMPLE user hash_ALL NULL #hash#$hj 1 information_schema.PROFILING.PAGE_FAULTS_MINOR 4 Using where; Using join buffer (flat, BNLH join) +set join_cache_level=default; set @@optimizer_switch=@save_optimizer_switch; diff --cc mysql-test/r/subselect_sj2_mat.result index c27beb295b8,7f97e1aabee..140739e5195 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@@ -1692,92 -1659,53 +1692,143 @@@ i 13 drop table t1; # + # MDEV-15982: Incorrect results when subquery is materialized + # + CREATE TABLE `t1` (`id` int(32) NOT NULL primary key); + INSERT INTO `t1` VALUES + (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), + (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), + (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92),(93),(94),(95),(96), (97), (98), + (99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113), + (114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128), + (129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146), + (147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161), + (162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173), + (174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (2), (3), (4), (5), (6), (19), (35), + (7), (20), (8), (36), (219), (22), (10), (23), (37), (11), (24); + CREATE TABLE `t2` (`type` int , `id` int(32) NOT NULL primary key); + INSERT INTO `t2` VALUES + (2,2),(2,3),(1,4),(2,5),(1,6),(1,19),(5,7),(1,20),(1,8),(1,21),(1,9), + (1,22),(2,10),(1,23),(2,11),(1,24),(1,12),(1,25),(2,13),(2,26),(2,14), + (2,27),(1,15),(1,28),(3,16),(1,29),(2,17),(1,30),(5,18),(2,1); + CREATE TABLE `t3` (`ref_id` int(32) unsigned ,`type` varchar(80),`id` int(32) NOT NULL ); + INSERT INTO `t3` VALUES + (1,'incident',31),(2,'faux pas',32), + (5,'oopsies',33),(3,'deniable',34), + (11,'wasntme',35),(10,'wasntme',36), + (17,'faux pas',37),(13,'unlikely',38), + (13,'improbable',39),(14,'incident',40), + (26,'problem',41),(14,'problem',42), + (26,'incident',43),(27,'incident',44); + explain + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + id select_type table type possible_keys key key_len ref rows Extra + 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 30 Using index + 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where + 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using index + 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 14 + 2 MATERIALIZED t1 eq_ref PRIMARY PRIMARY 4 test.t3.id 1 Using index + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + id + 10 + 11 + set optimizer_switch='materialization=off'; + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + id + 11 + 10 + set optimizer_switch='materialization=on'; + DROP TABLE t1,t2,t3; ++# +# 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 --cc mysql-test/t/join.test index e07a3665920,feafac57a7e..8a088de91cc --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@@ -1160,43 -1160,54 +1160,96 @@@ DROP TABLE t1,t2 SET optimizer_switch=@save_optimizer_switch; + --echo # + --echo # MDEV-16512 + --echo # Server crashes in find_field_in_table_ref on 2nd execution of SP referring to + --echo # non-existing field + --echo # + + CREATE TABLE t (i INT); + CREATE PROCEDURE p() SELECT t1.f FROM t AS t1 JOIN t AS t2 USING (f); + --error ER_BAD_FIELD_ERROR + CALL p; + --error ER_BAD_FIELD_ERROR + CALL p; + FLUSH TABLES; + --error ER_BAD_FIELD_ERROR + CALL p; + DROP TABLE t; + + # + # Fix the table definition to match the using + # + + CREATE TABLE t (f INT); + # + # The following shouldn't fail as the table is now matching the using + # + --error ER_BAD_FIELD_ERROR + CALL p; + DROP TABLE t; + CREATE TABLE t (i INT); + --error ER_BAD_FIELD_ERROR + CALL p; + DROP PROCEDURE p; + DROP TABLE t; + + CREATE TABLE t1 (a INT, b INT); + CREATE TABLE t2 (a INT); + CREATE TABLE t3 (a INT, c INT); + CREATE TABLE t4 (a INT, c INT); + CREATE TABLE t5 (a INT, c INT); + CREATE PROCEDURE p1() SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) + LEFT JOIN t5 USING (a)) USING (a); + --error ER_NON_UNIQ_ERROR + CALL p1; + --error ER_NON_UNIQ_ERROR + CALL p1; + DROP PROCEDURE p1; + DROP TABLE t1,t2,t3,t4,t5; + + --echo # + --echo # End of MariaDB 5.5 tests + --echo # ++ ++ +--echo # +--echo # Bug #35268: Parser can't handle STRAIGHT_JOIN with USING +--echo # + +CREATE TABLE t1 (a int); + +INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); + +CREATE TABLE t2 (a int); + +INSERT INTO t2 (a) VALUES (1),(2),(3),(4); + +EXPLAIN +SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a; +SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a; + +EXPLAIN +SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a; +SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a; + +EXPLAIN +SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a; +SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a; + +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-5635: join of a const table with non-const tables +--echo # + +CREATE TABLE t1 (a varchar(3) NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'); + +CREATE TABLE t2 (b varchar(3), c varchar(3), INDEX(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('bar', 'bar'),( 'qux', 'qux'); + +SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2 + WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a ); + +DROP TABLE t1,t2; diff --cc mysql-test/t/join_cache.test index 8507d58eecd,d82b4fa6030..e095419e88d --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@@ -3835,82 -3836,36 +3835,112 @@@ set optimizer_switch=@save_optimizer_sw DROP TABLE t1,t4,t5,t2; + --echo # + --echo # MDEV-16603: BNLH for query with materialized semi-join + --echo # + + --source include/have_innodb.inc + + set join_cache_level=4; + + CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; + INSERT INTO t1 VALUES (7,'x'); + + CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; + + INSERT INTO t2 VALUES + (NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), + (228,'x'),(3,'y'),(1,'z'),(9,'z'); + + CREATE TABLE temp + SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); + + let $q = + SELECT * FROM temp + WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); + + eval $q; + eval EXPLAIN EXTENDED $q; + + DROP TABLE t1,t2,temp; + + SET join_cache_level = default; + -# this must be the last command in the file +--echo # +--echo # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. +--echo # + +set join_cache_level=default; +set expensive_subquery_limit=0; + +create table t1 (c1 int); +create table t2 (c2 int); +create table t3 (c3 int); + +insert into t1 values (1), (2); +insert into t2 values (1), (2); +insert into t3 values (2); + +explain +select count(*) from t1 straight_join t2 +where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); + +set @counter=0; + +select count(*) from t1 straight_join t2 +where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); + +select @counter; + +explain +select count(*) from t1 straight_join t2 +where c1 = c2-0 and + c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and + c2 / 2 = 1; + +set @counter=0; + +select count(*) from t1 straight_join t2 +where c1 = c2-0 and + c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and + c2 / 2 = 1; + +select @counter; + +drop table t1,t2,t3; +set expensive_subquery_limit=default; + +--echo # +--echo # MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down +--echo # + +create table t1 (a int); +insert into t1 values +(7), (9), (1), (4), (2), (3), (5), (8), (11), (6), (10); + +explain select count(*) from t1, t1 t2 where t1.a=t2.a; + +set join_buffer_space_limit=1024*8; +select @@join_buffer_space_limit; +select @@join_buffer_size; + +explain select count(*) from t1, t1 t2 where t1.a=t2.a; + +set join_buffer_space_limit=default; + +drop table t1; + +--echo # +--echo # MDEV-6687: Assertion `0' failed in Protocol::end_statement on query +--echo # +SET join_cache_level = 3; +--echo # The following should have +--echo # - table order PROFILING,user, +--echo # - table user accessed with hash_ALL: +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = PAGE_FAULTS_MINOR; + +set join_cache_level=default; + +# The following command must be the last one the file - # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --cc mysql-test/t/subselect_sj2_mat.test index 68a888012f2,43b63459928..f54771856e7 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@@ -345,90 -345,52 +345,139 @@@ WHERE (t.id IN (0,4,12,13,1,10,3,11)) ); drop table t1; + + --echo # + --echo # MDEV-15982: Incorrect results when subquery is materialized + --echo # + + CREATE TABLE `t1` (`id` int(32) NOT NULL primary key); + INSERT INTO `t1` VALUES + (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), + (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), + (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92),(93),(94),(95),(96), (97), (98), + (99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113), + (114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128), + (129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146), + (147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161), + (162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173), + (174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (2), (3), (4), (5), (6), (19), (35), + (7), (20), (8), (36), (219), (22), (10), (23), (37), (11), (24); + + CREATE TABLE `t2` (`type` int , `id` int(32) NOT NULL primary key); + INSERT INTO `t2` VALUES + (2,2),(2,3),(1,4),(2,5),(1,6),(1,19),(5,7),(1,20),(1,8),(1,21),(1,9), + (1,22),(2,10),(1,23),(2,11),(1,24),(1,12),(1,25),(2,13),(2,26),(2,14), + (2,27),(1,15),(1,28),(3,16),(1,29),(2,17),(1,30),(5,18),(2,1); + + CREATE TABLE `t3` (`ref_id` int(32) unsigned ,`type` varchar(80),`id` int(32) NOT NULL ); + INSERT INTO `t3` VALUES + (1,'incident',31),(2,'faux pas',32), + (5,'oopsies',33),(3,'deniable',34), + (11,'wasntme',35),(10,'wasntme',36), + (17,'faux pas',37),(13,'unlikely',38), + (13,'improbable',39),(14,'incident',40), + (26,'problem',41),(14,'problem',42), + (26,'incident',43),(27,'incident',44); + + explain + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + + set optimizer_switch='materialization=off'; + + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + set optimizer_switch='materialization=on'; + + DROP TABLE t1,t2,t3; + + +--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 --cc scripts/mysql_install_db.sh index d04a6f497da,a8f32526a7e..5300036def5 --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@@ -208,11 -208,14 +208,16 @@@ cannot_find_file( fi echo - echo "If you compiled from source, you need to run 'make install' to" + echo "If you compiled from source, you need to either run 'make install' to" echo "copy the software into the correct location ready for operation." + echo "If you don't want to do a full install, you can use the --srcddir" + echo "option to only install the mysql database and privilege tables" echo + echo "If you compiled from source, you need to either run 'make install' to" + echo "copy the software into the correct location ready for operation." + echo "If you don't want to do a full install, you can use the --srcdir" + echo "option to only install the mysql database and privilege tables" + echo echo "If you are using a binary release, you must either be at the top" echo "level of the extracted archive, or pass the --basedir option" echo "pointing to that location."

1 0

[Commits] b75d819: MDEV-16711 Crash in Field_blob::store() while reading statistics
by IgorBabaev 16 Jul '18

16 Jul '18

revision-id: b75d819604bbb6c461e6363e331bd6bd1ba7a787 (mariadb-10.1.34-21-gb75d819) parent(s): ae0eb507bda858a13475faef4476dd496c4dbc01 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-15 18:40:25 -0700 message: MDEV-16711 Crash in Field_blob::store() while reading statistics for the small InnoDB table This bug was introduced by the patch 6c414fcf89510215d6d3466eb9992d444eadae89. The patch has not taken into account that some objects of the Field_* types are created only for TABLE_SHARE and the field 'table' is set to NULL for them. In particular such are objects created to store statistical min/max values for columns. --- mysql-test/r/stat_tables.result | 20 ++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 20 ++++++++++++++++++++ mysql-test/t/stat_tables.test | 20 +++++++++++++++++++- sql/field.cc | 8 +++++++- 4 files changed, 66 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index 1f1cad1..279c09f 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -539,6 +539,10 @@ SELECT * FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; # @@ -566,3 +570,19 @@ SELECT * FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +SELECT MAX(pk) FROM t1; +MAX(pk) +NULL +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index fc1ecdf..1604c41 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -566,6 +566,10 @@ SELECT * FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; # @@ -593,5 +597,21 @@ SELECT * FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +SELECT MAX(pk) FROM t1; +MAX(pk) +NULL +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index b98ca64..51c9995 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -325,7 +325,7 @@ INSERT INTO mysql.column_stats VALUES --sorted_result SELECT * FROM mysql.column_stats; -# SELECT pk FROM t1; +SELECT pk FROM t1; DROP TABLE t1; @@ -350,3 +350,21 @@ SELECT * FROM mysql.column_stats; DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; + + +--echo # +--echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT); + +SELECT MAX(pk) FROM t1; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; diff --git a/sql/field.cc b/sql/field.cc index 64c5167..71fc568 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -7942,7 +7942,13 @@ int Field_blob::store(const char *from,uint length,CHARSET_INFO *cs) return 0; } - if (table->blob_storage) // GROUP_CONCAT with ORDER BY | DISTINCT + /* + For min/max fields of statistical data 'table' is set to NULL. + It could not be otherwise as this data is shared by many instances + of the same base table. + */ + + if (table && table->blob_storage) // GROUP_CONCAT with ORDER BY | DISTINCT { DBUG_ASSERT(!f_is_hex_escape(flags)); DBUG_ASSERT(field_charset == cs);

1 0

[Commits] ae0eb50: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables
by IgorBabaev 15 Jul '18

15 Jul '18

revision-id: ae0eb507bda858a13475faef4476dd496c4dbc01 (mariadb-10.1.34-20-gae0eb50) parent(s): 095dc81158902380b8618338efabb5ce480dbd79 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-15 16:28:39 -0700 message: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables If the command CREATE OR REPLACE TABLE really replaces a table then it should remove all data on this table from all statistical tables. --- mysql-test/r/stat_tables.result | 25 +++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 25 +++++++++++++++++++++++++ mysql-test/t/stat_tables.test | 20 ++++++++++++++++++++ sql/sql_table.cc | 4 ++++ 4 files changed, 74 insertions(+) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index f299603..1f1cad1 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -541,3 +541,28 @@ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index d2d9296..fc1ecdf 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -568,5 +568,30 @@ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index 5bbd8ca..b98ca64 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -330,3 +330,23 @@ SELECT * FROM mysql.column_stats; DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +SELECT * FROM t1; +SELECT * FROM mysql.column_stats; + +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +SELECT * FROM mysql.column_stats; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 3c91463..9a8c737 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4770,6 +4770,10 @@ int create_table_impl(THD *thd, { if (options.or_replace()) { + LEX_STRING db_name= {(char *) db, strlen(db)}; + LEX_STRING tab_name= {(char *) table_name, strlen(table_name)}; + (void) delete_statistics_for_table(thd, &db_name, &tab_name); + TABLE_LIST table_list; table_list.init_one_table(db, strlen(db), table_name, strlen(table_name), table_name,

1 0

[Commits] 095dc81: MDEV-16757 Memory leak after adding manually min/max statistical data
by IgorBabaev 15 Jul '18

15 Jul '18

revision-id: 095dc81158902380b8618338efabb5ce480dbd79 (mariadb-10.1.34-19-g095dc81) parent(s): 1d10c9afe0f2f4fba73892e6c12ea6efe90d5931 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-15 16:24:24 -0700 message: MDEV-16757 Memory leak after adding manually min/max statistical data for blob column ANALYZE TABLE <table> does not collect statistical data on min/max values for BLOB columns of <table>. However these values can be added into mysql.column_stats manually by executing proper statements. Unfortunately this led to a memory leak because the memory allocated for these values was never freed. This patch provides the server with a function to free memory allocated for min/max statistical values of BLOB types. Temporarily changed the test case until MDEV-16711 is fixed as without this fix the test case for MDEV-16757 did not fail only for 10.0. --- mysql-test/r/stat_tables.result | 24 ++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 24 ++++++++++++++++++++++++ mysql-test/t/stat_tables.test | 24 ++++++++++++++++++++++++ sql/sql_statistics.cc | 33 +++++++++++++++++++++++++++++++++ sql/sql_statistics.h | 1 + sql/table_cache.cc | 2 ++ 6 files changed, 108 insertions(+) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index fcced76..f299603 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -517,3 +517,27 @@ drop database db1; drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 't' +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 0e86675..d2d9296 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -544,5 +544,29 @@ drop database db1; drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 't' +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index 4cbaa9e..5bbd8ca 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -306,3 +306,27 @@ drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-16757: manual addition of min/max statistics for BLOB +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +--sorted_result +SELECT * FROM mysql.column_stats; +DELETE FROM mysql.column_stats + WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES + ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +--sorted_result +SELECT * FROM mysql.column_stats; + +# SELECT pk FROM t1; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index abf3975..a1c2142 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3056,6 +3056,39 @@ int read_statistics_for_table(THD *thd, TABLE *table, TABLE_LIST *stat_tables) /** + @breif + Cleanup of min/max statistical values for table share +*/ + +void delete_stat_values_for_table_share(TABLE_SHARE *table_share) +{ + TABLE_STATISTICS_CB *stats_cb= &table_share->stats_cb; + Table_statistics *table_stats= stats_cb->table_stats; + if (!table_stats) + return; + Column_statistics *column_stats= table_stats->column_stats; + if (!column_stats) + return; + + for (Field **field_ptr= table_share->field; + *field_ptr; + field_ptr++, column_stats++) + { + if (column_stats->min_value) + { + delete column_stats->min_value; + column_stats->min_value= NULL; + } + if (column_stats->max_value) + { + delete column_stats->max_value; + column_stats->max_value= NULL; + } + } +} + + +/** @brief Check whether any statistics is to be read for tables from a table list diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index f465838..0611c02 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -92,6 +92,7 @@ int read_statistics_for_tables_if_needed(THD *thd, TABLE_LIST *tables); int collect_statistics_for_table(THD *thd, TABLE *table); int alloc_statistics_for_table_share(THD* thd, TABLE_SHARE *share, bool is_safe); +void delete_stat_values_for_table_share(TABLE_SHARE *table_share); int alloc_statistics_for_table(THD *thd, TABLE *table); int update_statistics_for_table(THD *thd, TABLE *table); int delete_statistics_for_table(THD *thd, LEX_STRING *db, LEX_STRING *tab); diff --git a/sql/table_cache.cc b/sql/table_cache.cc index 16a47b3..b911252 100644 --- a/sql/table_cache.cc +++ b/sql/table_cache.cc @@ -52,6 +52,7 @@ #include "lf.h" #include "table.h" #include "sql_base.h" +#include "sql_statistics.h" /** Configuration. */ @@ -786,6 +787,7 @@ void tdc_release_share(TABLE_SHARE *share) } if (share->tdc->flushed || tdc_records() > tdc_size) { + delete_stat_values_for_table_share(share); mysql_mutex_unlock(&LOCK_unused_shares); tdc_delete_share_from_hash(share->tdc); DBUG_VOID_RETURN;

1 0

[Commits] f44dd20: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables
by IgorBabaev 15 Jul '18

15 Jul '18

revision-id: f44dd20197a8c35872d292559a6c0571981038b3 (mariadb-10.1.34-20-gf44dd20) parent(s): 6e8bb4218af04e31398743449baa22f486258d3b author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-15 15:20:26 -0700 message: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables If the command CREATE OR REPLACE TABLE really replaces a table then it should remove all data on this table from all statistical tables. --- mysql-test/r/stat_tables.result | 25 +++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 25 +++++++++++++++++++++++++ mysql-test/t/stat_tables.test | 20 ++++++++++++++++++++ sql/sql_table.cc | 4 ++++ 4 files changed, 74 insertions(+) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index f299603..1f1cad1 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -541,3 +541,28 @@ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index d2d9296..fc1ecdf 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -568,5 +568,30 @@ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index 5bbd8ca..b98ca64 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -330,3 +330,23 @@ SELECT * FROM mysql.column_stats; DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +SELECT * FROM t1; +SELECT * FROM mysql.column_stats; + +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +SELECT * FROM mysql.column_stats; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 3c91463..9a8c737 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4770,6 +4770,10 @@ int create_table_impl(THD *thd, { if (options.or_replace()) { + LEX_STRING db_name= {(char *) db, strlen(db)}; + LEX_STRING tab_name= {(char *) table_name, strlen(table_name)}; + (void) delete_statistics_for_table(thd, &db_name, &tab_name); + TABLE_LIST table_list; table_list.init_one_table(db, strlen(db), table_name, strlen(table_name), table_name,

1 0

[Commits] 6e8bb42: MDEV-16757 Memory leak after adding manually min/max statistical data
by IgorBabaev 15 Jul '18

15 Jul '18

revision-id: 6e8bb4218af04e31398743449baa22f486258d3b (mariadb-10.1.34-19-g6e8bb42) parent(s): 1d10c9afe0f2f4fba73892e6c12ea6efe90d5931 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-15 15:18:47 -0700 message: MDEV-16757 Memory leak after adding manually min/max statistical data for blob column ANALYZE TABLE <table> does not collect statistical data on min/max values for BLOB columns of <table>. However these values can be added into mysql.column_stats manually by executing proper statements. Unfortunately this led to a memory leak because the memory allocated for these values was never freed. This patch provides the server with a function to free memory allocated for min/max statistical values of BLOB types. Temporarily changed the test case until MDEV-16711 is fixed as without this fix the test case for MDEV-16757 did not fail only for 10.0. --- mysql-test/r/stat_tables.result | 24 ++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 24 ++++++++++++++++++++++++ mysql-test/t/stat_tables.test | 24 ++++++++++++++++++++++++ sql/sql_statistics.cc | 33 +++++++++++++++++++++++++++++++++ sql/sql_statistics.h | 1 + sql/table_cache.cc | 2 ++ 6 files changed, 108 insertions(+) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index fcced76..f299603 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -517,3 +517,27 @@ drop database db1; drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 't' +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 0e86675..d2d9296 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -544,5 +544,29 @@ drop database db1; drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 't' +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index 4cbaa9e..5bbd8ca 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -306,3 +306,27 @@ drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-16757: manual addition of min/max statistics for BLOB +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +--sorted_result +SELECT * FROM mysql.column_stats; +DELETE FROM mysql.column_stats + WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES + ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +--sorted_result +SELECT * FROM mysql.column_stats; + +# SELECT pk FROM t1; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index abf3975..a1c2142 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3056,6 +3056,39 @@ int read_statistics_for_table(THD *thd, TABLE *table, TABLE_LIST *stat_tables) /** + @breif + Cleanup of min/max statistical values for table share +*/ + +void delete_stat_values_for_table_share(TABLE_SHARE *table_share) +{ + TABLE_STATISTICS_CB *stats_cb= &table_share->stats_cb; + Table_statistics *table_stats= stats_cb->table_stats; + if (!table_stats) + return; + Column_statistics *column_stats= table_stats->column_stats; + if (!column_stats) + return; + + for (Field **field_ptr= table_share->field; + *field_ptr; + field_ptr++, column_stats++) + { + if (column_stats->min_value) + { + delete column_stats->min_value; + column_stats->min_value= NULL; + } + if (column_stats->max_value) + { + delete column_stats->max_value; + column_stats->max_value= NULL; + } + } +} + + +/** @brief Check whether any statistics is to be read for tables from a table list diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index f465838..0611c02 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -92,6 +92,7 @@ int read_statistics_for_tables_if_needed(THD *thd, TABLE_LIST *tables); int collect_statistics_for_table(THD *thd, TABLE *table); int alloc_statistics_for_table_share(THD* thd, TABLE_SHARE *share, bool is_safe); +void delete_stat_values_for_table_share(TABLE_SHARE *table_share); int alloc_statistics_for_table(THD *thd, TABLE *table); int update_statistics_for_table(THD *thd, TABLE *table); int delete_statistics_for_table(THD *thd, LEX_STRING *db, LEX_STRING *tab); diff --git a/sql/table_cache.cc b/sql/table_cache.cc index 16a47b3..2b12685 100644 --- a/sql/table_cache.cc +++ b/sql/table_cache.cc @@ -52,6 +52,7 @@ #include "lf.h" #include "table.h" #include "sql_base.h" +#include "sql_statistics.h" /** Configuration. */ @@ -778,6 +779,7 @@ void tdc_release_share(TABLE_SHARE *share) mysql_mutex_lock(&share->tdc->LOCK_table_share); if (--share->tdc->ref_count) { + delete_stat_values_for_table_share(share); if (!share->is_view) mysql_cond_broadcast(&share->tdc->COND_release); mysql_mutex_unlock(&share->tdc->LOCK_table_share);

1 0

[Commits] 22f27a7: MDEV-16757 Memory leak after adding manually min/max statistical data
by IgorBabaev 15 Jul '18

15 Jul '18

revision-id: 22f27a7b406d8fc23f15fe80c93c8190e40da52b (mariadb-10.1.34-19-g22f27a7) parent(s): 1d10c9afe0f2f4fba73892e6c12ea6efe90d5931 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-15 14:34:06 -0700 message: MDEV-16757 Memory leak after adding manually min/max statistical data for blob column ANALYZE TABLE <table> does not collect statistical data on min/max values for BLOB columns of <table>. However these values can be added into mysql.column_stats manually by executing proper statements. Unfortunately this led to a memory leak because the memory allocated for these values was never freed. This patch provides the server with a function to free memory allocated for min/max statistical values of BLOB types. --- mysql-test/r/stat_tables.result | 28 ++++++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 28 ++++++++++++++++++++++++++++ mysql-test/t/stat_tables.test | 24 ++++++++++++++++++++++++ sql/sql_statistics.cc | 33 +++++++++++++++++++++++++++++++++ sql/sql_statistics.h | 1 + sql/table_cache.cc | 2 ++ 6 files changed, 116 insertions(+) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index fcced76..79a5da3 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -517,3 +517,31 @@ drop database db1; drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t NULL NULL 0.0000 3.0000 NULL NULL NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 0e86675..813c09d 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -544,5 +544,33 @@ drop database db1; drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t NULL NULL 0.0000 3.0000 NULL NULL NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index 4cbaa9e..7223064 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -306,3 +306,27 @@ drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-16757: manual addition of min/max statistics for BLOB +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +--sorted_result +SELECT * FROM mysql.column_stats; +DELETE FROM mysql.column_stats + WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES + ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +--sorted_result +SELECT * FROM mysql.column_stats; + +SELECT pk FROM t1; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index abf3975..a1c2142 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3056,6 +3056,39 @@ int read_statistics_for_table(THD *thd, TABLE *table, TABLE_LIST *stat_tables) /** + @breif + Cleanup of min/max statistical values for table share +*/ + +void delete_stat_values_for_table_share(TABLE_SHARE *table_share) +{ + TABLE_STATISTICS_CB *stats_cb= &table_share->stats_cb; + Table_statistics *table_stats= stats_cb->table_stats; + if (!table_stats) + return; + Column_statistics *column_stats= table_stats->column_stats; + if (!column_stats) + return; + + for (Field **field_ptr= table_share->field; + *field_ptr; + field_ptr++, column_stats++) + { + if (column_stats->min_value) + { + delete column_stats->min_value; + column_stats->min_value= NULL; + } + if (column_stats->max_value) + { + delete column_stats->max_value; + column_stats->max_value= NULL; + } + } +} + + +/** @brief Check whether any statistics is to be read for tables from a table list diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index f465838..0611c02 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -92,6 +92,7 @@ int read_statistics_for_tables_if_needed(THD *thd, TABLE_LIST *tables); int collect_statistics_for_table(THD *thd, TABLE *table); int alloc_statistics_for_table_share(THD* thd, TABLE_SHARE *share, bool is_safe); +void delete_stat_values_for_table_share(TABLE_SHARE *table_share); int alloc_statistics_for_table(THD *thd, TABLE *table); int update_statistics_for_table(THD *thd, TABLE *table); int delete_statistics_for_table(THD *thd, LEX_STRING *db, LEX_STRING *tab); diff --git a/sql/table_cache.cc b/sql/table_cache.cc index 16a47b3..2b12685 100644 --- a/sql/table_cache.cc +++ b/sql/table_cache.cc @@ -52,6 +52,7 @@ #include "lf.h" #include "table.h" #include "sql_base.h" +#include "sql_statistics.h" /** Configuration. */ @@ -778,6 +779,7 @@ void tdc_release_share(TABLE_SHARE *share) mysql_mutex_lock(&share->tdc->LOCK_table_share); if (--share->tdc->ref_count) { + delete_stat_values_for_table_share(share); if (!share->is_view) mysql_cond_broadcast(&share->tdc->COND_release); mysql_mutex_unlock(&share->tdc->LOCK_table_share);

1 0

[Commits] 25410d4: MDEV-15473 Isolate/sandbox PAM modules, so that they can't crash the server.
by holyfoot＠askmonty.org 14 Jul '18

14 Jul '18

revision-id: 25410d448d5cd5796852da106324309d169981c9 (mariadb-10.3.6-49-g25410d4) parent(s): 7fda6161bc9daafbe26fb3ce687b6411537d49f3 committer: Alexey Botchkov timestamp: 2018-07-14 23:06:49 +0400 message: MDEV-15473 Isolate/sandbox PAM modules, so that they can't crash the server. mysql_install_db.sh script fixed. --- plugin/auth_pam/CMakeLists.txt | 7 ++++++- scripts/mysql_install_db.sh | 20 ++++++++++++++++++++ 2 files changed, 26 insertions(+), 1 deletion(-) diff --git a/plugin/auth_pam/CMakeLists.txt b/plugin/auth_pam/CMakeLists.txt index 4943d57..fbf0979 100644 --- a/plugin/auth_pam/CMakeLists.txt +++ b/plugin/auth_pam/CMakeLists.txt @@ -11,7 +11,12 @@ IF(HAVE_PAM_APPL_H) ADD_DEFINITIONS(-D_GNU_SOURCE) MYSQL_ADD_PLUGIN(auth_pam_v1 auth_pam_v1.c LINK_LIBRARIES pam MODULE_ONLY) MYSQL_ADD_PLUGIN(auth_pam auth_pam.c LINK_LIBRARIES pam dl MODULE_ONLY) - MYSQL_ADD_EXECUTABLE(auth_pam_tool auth_pam_tool.c DESTINATION ${INSTALL_PLUGINDIR}/auth_pam_tool_dir COMPONENT Server) + MYSQL_ADD_EXECUTABLE(auth_pam_tool auth_pam_tool.c DESTINATION ${INSTALL_PLUGINDIR}/auth_pam_tool_dir COMPONENT Server) TARGET_LINK_LIBRARIES(auth_pam_tool pam) + INSTALL(CODE "EXECUTE_PROCESS( + COMMAND chmod u=rwx,g=,o= auth_pam_tool_dir + COMMAND chmod u=rwxs,g=rx,o=rx auth_pam_tool_dir/auth_pam_tool + WORKING_DIRECTORY \$ENV{DESTDIR}\${CMAKE_INSTALL_PREFIX}/${INSTALL_PLUGINDIR}/)" + COMPONENT Server) ENDIF(HAVE_PAM_APPL_H) diff --git a/scripts/mysql_install_db.sh b/scripts/mysql_install_db.sh index ad7c028..ea5507f 100644 --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@ -308,6 +308,7 @@ then srcpkgdatadir="$srcdir/scripts" buildpkgdatadir="$builddir/scripts" plugindir="$builddir/plugin/auth_socket" + pamtooldir="$builddir/plugin/auth_pam" elif test -n "$basedir" then bindir="$basedir/bin" # only used in the help text @@ -337,6 +338,7 @@ then exit 1 fi plugindir=`find_in_dirs --dir auth_socket.so $basedir/lib*/plugin $basedir/lib*/mysql/plugin` + pamtooldir=$plugindir else basedir="@prefix@" bindir="@bindir@" @@ -345,6 +347,7 @@ else srcpkgdatadir="@pkgdatadir@" buildpkgdatadir="@pkgdatadir@" plugindir="@pkgplugindir@" + pamtooldir="@pkgplugindir@" fi # Set up paths to SQL scripts required for bootstrap @@ -445,6 +448,23 @@ done if test -n "$user" then + chown $user "$pamtooldir/auth_pam_tool_dir" + if test $? -ne 0 + then + echo "Cannot change ownership of the '$pamtooldir/auth_pam_tool_dir' directory" + echo " to the '$user' user. Check that you have the necessary permissions and try again." + exit 1 + fi + if test -z "$srcdir" + then + chown 0 "$pamtooldir/auth_pam_tool_dir/auth_pam_tool" + if test $? -ne 0 + then + echo "Couldn't set an owner to '$pamtooldir/auth_pam_tool_dir/auth_pam_tool'." + echo " It must be root, the PAM authentication plugin doesn't work otherwise.." + echo + fi + fi args="$args --user=$user" fi

1 0

[Commits] 1fd84f9: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables
by IgorBabaev 14 Jul '18

14 Jul '18

revision-id: 1fd84f9129f2ed98706f6e225b06b16a13d0ebd0 (mariadb-10.0.35-57-g1fd84f9) parent(s): c89bb15c31f98d2d368414c7366ce61955b70b44 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-13 23:03:57 -0700 message: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables If the command CREATE OR REPLACE TABLE really replaces a table then it should remove all data on this table from all statistical tables. --- mysql-test/r/stat_tables.result | 25 +++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 25 +++++++++++++++++++++++++ mysql-test/t/stat_tables.test | 20 ++++++++++++++++++++ sql/sql_table.cc | 4 ++++ 4 files changed, 74 insertions(+) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index e6b6750..c1457d5 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -552,3 +552,28 @@ pk 2 DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 04d7386..2ac868e 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -579,5 +579,30 @@ pk 2 DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index 843e6f8..d69b006 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -336,3 +336,23 @@ SELECT pk FROM t1; DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +SELECT * FROM t1; +SELECT * FROM mysql.column_stats; + +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +SELECT * FROM mysql.column_stats; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 6c71067..ee02b5f 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4762,6 +4762,10 @@ int create_table_impl(THD *thd, { if (create_info->options & HA_LEX_CREATE_REPLACE) { + LEX_STRING db_name= {(char *) db, strlen(db)}; + LEX_STRING tab_name= {(char *) table_name, strlen(table_name)}; + (void) delete_statistics_for_table(thd, &db_name, &tab_name); + TABLE_LIST table_list; table_list.init_one_table(db, strlen(db), table_name, strlen(table_name), table_name,

1 0

[Commits] fa3655e: Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction
by psergey＠askmonty.org 13 Jul '18

13 Jul '18

revision-id: fa3655ec9f7fd098664d6e92870932da6c47a3d4 parent(s): a47797f16671a44a92632935fc93e302e0c969ff committer: Sergei Petrunia branch nick: mysql-5.6-rocksdb-spetrunia timestamp: 2018-07-13 21:39:13 +0300 message: Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction rollback_to_stmt_savepoint() calls do_rollback_to_savepoint(). This removes the changes made by this statement, and also removes the last set savepoint. Before we start processing the next statement, we need to set the new savepoint, so we will have something to rollback to if the next statement fails. Since rollback_to_stmt_savepoint always sets a new savepoint now, m_n_savepoints is now redundant and is removed. --- mysql-test/suite/rocksdb/r/transaction.result | 17 +++++++++++++++++ mysql-test/suite/rocksdb/t/transaction.test | 23 +++++++++++++++++++++++ storage/rocksdb/ha_rocksdb.cc | 14 ++++++++------ 3 files changed, 48 insertions(+), 6 deletions(-) diff --git a/mysql-test/suite/rocksdb/r/transaction.result b/mysql-test/suite/rocksdb/r/transaction.result index 006baaf..7664a11 100644 --- a/mysql-test/suite/rocksdb/r/transaction.result +++ b/mysql-test/suite/rocksdb/r/transaction.result @@ -958,3 +958,20 @@ a rollback; drop function func; drop table t1,t2,t3; +# +# MDEV-16710: Slave SQL: Could not execute Update_rows_v1 event with RocksDB and triggers +# Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction +# +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=RocksDB; +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=RocksDB; +CREATE TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW INSERT INTO non_existing_table VALUES (NULL); +BEGIN; +DELETE FROM t1; +INSERT INTO t2 VALUES (1); +INSERT INTO t2 VALUES (2); +# Must return empty result: +SELECT * FROM t1; +a +COMMIT; +drop table t1,t2; diff --git a/mysql-test/suite/rocksdb/t/transaction.test b/mysql-test/suite/rocksdb/t/transaction.test index 3350db9..52291da 100644 --- a/mysql-test/suite/rocksdb/t/transaction.test +++ b/mysql-test/suite/rocksdb/t/transaction.test @@ -133,3 +133,26 @@ rollback; drop function func; drop table t1,t2,t3; +--echo # +--echo # MDEV-16710: Slave SQL: Could not execute Update_rows_v1 event with RocksDB and triggers +--echo # Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction +--echo # +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=RocksDB; +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=RocksDB; + +CREATE TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW INSERT INTO non_existing_table VALUES (NULL); + +BEGIN; +DELETE FROM t1; +--error 0,ER_NO_SUCH_TABLE +INSERT INTO t2 VALUES (1); +--error 0,ER_NO_SUCH_TABLE +INSERT INTO t2 VALUES (2); +--echo # Must return empty result: +SELECT * FROM t1; +COMMIT; + +drop table t1,t2; + diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 57eaa83..9a61874 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -2436,7 +2436,6 @@ public: entire transaction. */ do_set_savepoint(); - m_n_savepoints= 1; m_writes_at_last_savepoint= m_write_count; } @@ -2453,7 +2452,6 @@ public: { do_set_savepoint(); m_writes_at_last_savepoint= m_write_count; - m_n_savepoints++; } } @@ -2464,10 +2462,14 @@ public: void rollback_to_stmt_savepoint() { if (m_writes_at_last_savepoint != m_write_count) { do_rollback_to_savepoint(); - if (!--m_n_savepoints) { - do_set_savepoint(); - m_n_savepoints= 1; - } + /* + RollbackToSavePoint "removes the most recent SetSavePoint()", so + we need to set it again so that next statement can roll back to this + stage. + It's ok to do it here at statement end (instead of doing it at next + statement start) because setting a savepoint is cheap. + */ + do_set_savepoint(); m_writes_at_last_savepoint= m_write_count; } }

1 0