From 01f8130aa68392d14f36cc17c01e1b349bf844f5 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 15 Aug 2007 09:23:44 +0200 Subject: bug#28570: handler::index_read() is called with different find_flag when ORDER BY is used The range analysis module did not correctly signal to the handler that a range represents a ref (EQ_RANGE flag). This causes non-range queries like SELECT ... FROM ... WHERE keypart_1=const, ..., keypart_n=const ORDER BY ... FOR UPDATE to wait for a lock unneccesarily if another running transaction uses SELECT ... FOR UPDATE on the same table. Fixed by setting EQ_RANGE for all range accesses that represent an equality predicate. mysql-test/r/innodb_mysql.result: bug#28570: Test Result mysql-test/t/innodb_mysql.test: bug#28570: Test Case sql/handler.cc: bug#28570: Updated comment sql/opt_range.cc: bug#28570: Removed the criterion that key has to be unique (HA_NOSAME) in order for the EQ_RANGE flag to be set. It is sufficient that the range represent a ref access. --- mysql-test/r/innodb_mysql.result | 18 ++++++++++++++++++ mysql-test/t/innodb_mysql.test | 29 +++++++++++++++++++++++++++++ 2 files changed, 47 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 5b3ca972467..0bb93318557 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1007,4 +1007,22 @@ CALL p1(); CALL p1(); DROP PROCEDURE p1; DROP TABLE t1; +CREATE TABLE t1 ( +a INT, +b INT, +KEY (b) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30); +START TRANSACTION; +SELECT * FROM t1 WHERE b=20 FOR UPDATE; +a b +2 20 +START TRANSACTION; +SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE; +a b +1 10 +2 10 +ROLLBACK; +ROLLBACK; +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 63431e10bbf..e27db9944fe 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -840,5 +840,34 @@ DISCONNECT con2; DROP PROCEDURE p1; DROP TABLE t1; +# +# Bug #28570: handler::index_read() is called with different find_flag when +# ORDER BY is used +# + +CREATE TABLE t1 ( + a INT, + b INT, + KEY (b) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30); + +START TRANSACTION; +SELECT * FROM t1 WHERE b=20 FOR UPDATE; + +--connect (conn2, localhost, root,,test) + +# This statement gives a "failed: 1205: Lock wait timeout exceeded; try +# restarting transaction" message when the bug is present. +START TRANSACTION; +SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE; +ROLLBACK; + +--disconnect conn2 +--connection default + +ROLLBACK; +DROP TABLE t1; --echo End of 5.0 tests -- cgit v1.2.1 From a8f8e5483e7925ce804c3900752f88fdf43cb3ee Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 15 Aug 2007 10:24:18 -0700 Subject: Fixed bug #30396. The bug caused memory corruption for some queries with top OR level in the WHERE condition if they contained equality predicates and other sargable predicates in disjunctive parts of the condition. The corruption happened because the upper bound of the memory allocated for KEY_FIELD and SARGABLE_PARAM internal structures containing info about potential lookup keys was calculated incorrectly in some cases. In particular it was calculated incorrectly when the WHERE condition was an OR formula with disjuncts being AND formulas including equalities and other sargable predicates. mysql-test/r/select.result: Added a test case for bug #30396. mysql-test/t/select.test: Added a test case for bug #30396. sql/item_cmpfunc.h: Removed max_members from the COND_EQUAL class as not useful anymore. sql/sql_base.cc: Added the max_equal_elems field to the st_select_lex structure. sql/sql_lex.cc: Added the max_equal_elems field to the st_select_lex structure. sql/sql_lex.h: Added the max_equal_elems field to the st_select_lex structure. The field contains the maximal number of elements in multiple equalities built for the query conditions. sql/sql_select.cc: Fixed bug #30396. The bug caused memory corruption for some queries with top OR level in the WHERE condition if they contained equality predicates and other sargable predicates in disjunctive parts of the condition. The corruption happened because the upper bound of the memory allocated for KEY_FIELD and SARGABLE_PARAM internal structures containing info about potential lookup keys was calculated incorrectly in some cases. In particular it was calculated incorrectly when the WHERE condition was an OR formula with disjuncts being AND formulas including equalities and other sargable predicates. The max_equal_elems field to the st_select_lex structure is used now to calculate the above mentioned upper bound. The field contains the maximal number of elements in multiple equalities built for the query conditions. --- mysql-test/r/select.result | 57 +++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/select.test | 60 ++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 117 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 5930c36029f..2cf1316bb47 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4005,4 +4005,61 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) > 0; ERROR HY000: Too high level of nesting for select DROP TABLE t1; +CREATE TABLE t1 ( +c1 int(11) NOT NULL AUTO_INCREMENT, +c2 varchar(1000) DEFAULT NULL, +c3 bigint(20) DEFAULT NULL, +c4 bigint(20) DEFAULT NULL, +PRIMARY KEY (c1) +); +EXPLAIN EXTENDED +SELECT join_2.c1 +FROM +t1 AS join_0, +t1 AS join_1, +t1 AS join_2, +t1 AS join_3, +t1 AS join_4, +t1 AS join_5, +t1 AS join_6, +t1 AS join_7 +WHERE +join_0.c1=join_1.c1 AND +join_1.c1=join_2.c1 AND +join_2.c1=join_3.c1 AND +join_3.c1=join_4.c1 AND +join_4.c1=join_5.c1 AND +join_5.c1=join_6.c1 AND +join_6.c1=join_7.c1 +OR +join_0.c2 < '?' AND +join_1.c2 < '?' AND +join_2.c2 > '?' AND +join_2.c2 < '!' AND +join_3.c2 > '?' AND +join_4.c2 = '?' AND +join_5.c2 <> '?' AND +join_6.c2 <> '?' AND +join_7.c2 >= '?' AND +join_0.c1=join_1.c1 AND +join_1.c1=join_2.c1 AND +join_2.c1=join_3.c1 AND +join_3.c1=join_4.c1 AND +join_4.c1=join_5.c1 AND +join_5.c1=join_6.c1 AND +join_6.c1=join_7.c1 +GROUP BY +join_3.c1, +join_2.c1, +join_7.c1, +join_1.c1, +join_0.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select '0' AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by '0','0','0','0','0' +SHOW WARNINGS; +Level Code Message +Note 1003 select '0' AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by '0','0','0','0','0' +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 8bfa12539fa..abf55745080 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3400,4 +3400,64 @@ eval EXPLAIN SELECT c1 FROM t1 WHERE $q > 0; DROP TABLE t1; +# +# Bug #30396: crash for a join with equalities and sargable predicates +# in disjunctive parts of the WHERE condition +# + +CREATE TABLE t1 ( + c1 int(11) NOT NULL AUTO_INCREMENT, + c2 varchar(1000) DEFAULT NULL, + c3 bigint(20) DEFAULT NULL, + c4 bigint(20) DEFAULT NULL, + PRIMARY KEY (c1) +); + +EXPLAIN EXTENDED +SELECT join_2.c1 +FROM + t1 AS join_0, + t1 AS join_1, + t1 AS join_2, + t1 AS join_3, + t1 AS join_4, + t1 AS join_5, + t1 AS join_6, + t1 AS join_7 +WHERE + join_0.c1=join_1.c1 AND + join_1.c1=join_2.c1 AND + join_2.c1=join_3.c1 AND + join_3.c1=join_4.c1 AND + join_4.c1=join_5.c1 AND + join_5.c1=join_6.c1 AND + join_6.c1=join_7.c1 + OR + join_0.c2 < '?' AND + join_1.c2 < '?' AND + join_2.c2 > '?' AND + join_2.c2 < '!' AND + join_3.c2 > '?' AND + join_4.c2 = '?' AND + join_5.c2 <> '?' AND + join_6.c2 <> '?' AND + join_7.c2 >= '?' AND + join_0.c1=join_1.c1 AND + join_1.c1=join_2.c1 AND + join_2.c1=join_3.c1 AND + join_3.c1=join_4.c1 AND + join_4.c1=join_5.c1 AND + join_5.c1=join_6.c1 AND + join_6.c1=join_7.c1 +GROUP BY + join_3.c1, + join_2.c1, + join_7.c1, + join_1.c1, + join_0.c1; + +SHOW WARNINGS; + +DROP TABLE t1; + --echo End of 5.0 tests -- cgit v1.2.1 From 0d9046ecaf3107371e0b2a59792104385da74349 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 16 Aug 2007 19:27:44 -0700 Subject: Post-merge fix. --- mysql-test/r/select.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index f1bfbec1555..48e8bf933e1 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4039,8 +4039,8 @@ join_2.c1, join_7.c1, join_1.c1, join_0.c1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select '0' AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by '0','0','0','0','0' SHOW WARNINGS; -- cgit v1.2.1 From 29ee5ed0a04a2db4e5e23a359a3acf9b4e474ab0 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 20 Aug 2007 11:23:08 +0500 Subject: Bug#27629 Possible security flaw in INFORMATION_SCHEMA and SHOW statements added SUPER_ACL check for I_S.TRIGGERS mysql-test/r/information_schema.result: result fix mysql-test/r/information_schema_db.result: result fix mysql-test/t/information_schema.test: test case sql/sql_show.cc: added SUPER_ACL check for I_S.TRIGGERS --- mysql-test/r/information_schema.result | 26 +++++++++++++++++++++++++- mysql-test/r/information_schema_db.result | 2 -- mysql-test/t/information_schema.test | 26 ++++++++++++++++++++++++++ 3 files changed, 51 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 9d0e41b341a..612e744a0f4 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -180,7 +180,6 @@ t1 a select show columns from mysqltest.t1; Field Type Null Key Default Extra a int(11) YES NULL -b varchar(30) YES MUL NULL select table_name, column_name, privileges from information_schema.columns where table_schema = 'mysqltest' and table_name = 'v1'; table_name column_name privileges @@ -1330,4 +1329,29 @@ alter database; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 alter database test; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 +create database mysqltest; +create table mysqltest.t1(a int, b int, c int); +create trigger mysqltest.t1_ai after insert on mysqltest.t1 +for each row set @a = new.a + new.b + new.c; +grant select(b) on mysqltest.t1 to mysqltest_1@localhost; +select trigger_name from information_schema.triggers +where event_object_table='t1'; +trigger_name +t1_ai +show triggers from mysqltest; +Trigger Event Table Statement Timing Created sql_mode Definer +t1_ai INSERT t1 set @a = new.a + new.b + new.c AFTER NULL root@localhost +show columns from t1; +Field Type Null Key Default Extra +b int(11) YES NULL +select column_name from information_schema.columns where table_name='t1'; +column_name +b +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer +select trigger_name from information_schema.triggers +where event_object_table='t1'; +trigger_name +drop user mysqltest_1@localhost; +drop database mysqltest; End of 5.0 tests. diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 2d330dda333..dd1f0295277 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -140,13 +140,11 @@ create view v2 as select f1 from testdb_1.v1; create view v4 as select f1,f2 from testdb_1.v3; show fields from testdb_1.v5; Field Type Null Key Default Extra -f1 char(4) YES NULL show create view testdb_1.v5; View Create View v5 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_1`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v5` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1` show fields from testdb_1.v6; Field Type Null Key Default Extra -f1 char(4) YES NULL show create view testdb_1.v6; View Create View v6 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v6` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1` diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 6cf4ad8f576..7637a027e8f 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1045,4 +1045,30 @@ drop table t1,t2; alter database; --error ER_PARSE_ERROR alter database test; + +# +# Bug#27629 Possible security flaw in INFORMATION_SCHEMA and SHOW statements +# + +create database mysqltest; +create table mysqltest.t1(a int, b int, c int); +create trigger mysqltest.t1_ai after insert on mysqltest.t1 + for each row set @a = new.a + new.b + new.c; +grant select(b) on mysqltest.t1 to mysqltest_1@localhost; + +select trigger_name from information_schema.triggers +where event_object_table='t1'; +show triggers from mysqltest; + +connect (con27629,localhost,mysqltest_1,,mysqltest); +show columns from t1; +select column_name from information_schema.columns where table_name='t1'; + +show triggers; +select trigger_name from information_schema.triggers +where event_object_table='t1'; +connection default; +drop user mysqltest_1@localhost; +drop database mysqltest; + --echo End of 5.0 tests. -- cgit v1.2.1 From 700c3786188e8e2c112615841d3be77e3d8e0f59 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 21 Aug 2007 17:55:49 +0500 Subject: Bug#27629 Possible security flaw in INFORMATION_SCHEMA and SHOW statements(addon for 5.1) added TRIGGER_ACL check for I_S.TRIGGERS mysql-test/r/information_schema.result: result fix --- mysql-test/r/information_schema.result | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 1ad30824214..e87b46ac90f 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1419,8 +1419,8 @@ where event_object_table='t1'; trigger_name t1_ai show triggers from mysqltest; -Trigger Event Table Statement Timing Created sql_mode Definer -t1_ai INSERT t1 set @a = new.a + new.b + new.c AFTER NULL root@localhost +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +t1_ai INSERT t1 set @a = new.a + new.b + new.c AFTER NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci show columns from t1; Field Type Null Key Default Extra b int(11) YES NULL @@ -1428,7 +1428,7 @@ select column_name from information_schema.columns where table_name='t1'; column_name b show triggers; -Trigger Event Table Statement Timing Created sql_mode Definer +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation select trigger_name from information_schema.triggers where event_object_table='t1'; trigger_name -- cgit v1.2.1 From f0d1ac25c349f09f2512d75f4e12f39d3a7729c3 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 23 Aug 2007 23:34:48 +0500 Subject: Bug #28430 Failure in replication of innodb partitioned tables on row/mixed format. In the ha_partition::position() we didn't calculate the number of the partition of the record. We used m_last_part value instead, relying on that it is set in other place like previous call of a method like ::write_row(). In replication we don't call any of these befor position(). Delete_rows_log_event::do_exec_row calls find_and_fetch_row. In case of InnoDB-based PARTITION table, we have HA_PRIMARY_KEY_REQUIRED_FOR_POSITION enabled, so use position() / rnd_pos() calls to fetch the record. Fixed by adding partition_id calculation to the ha_partition::position() sql/ha_partition.h: Bug #28430 Failure in replication of innodb partitioned tables on row/mixed format. column_bitmaps_signal interface added sql/ha_partition.cc: Bug #28430 Failure in replication of innodb partitioned tables on row/mixed format. Calculate the number of the partition in ha_partition::position(). ha_partition::column_bitmaps_signal() implemented mysql-test/r/partition_pruning.result: Bug #28430 Failure in replication of innodb partitioned tables on row/mixed format. test result fixed --- mysql-test/r/partition_pruning.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 9595676016c..776e6f3a15a 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -631,7 +631,7 @@ flush status; delete from t2 where b > 5; show status like 'Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 1215 +Handler_read_rnd_next 1115 show status like 'Handler_read_key'; Variable_name Value Handler_read_key 0 @@ -645,7 +645,7 @@ flush status; delete from t2 where b < 5 or b > 3; show status like 'Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 1215 +Handler_read_rnd_next 1115 show status like 'Handler_read_key'; Variable_name Value Handler_read_key 0 -- cgit v1.2.1