From df3a48ea43b93ad91546d71c49963665f13179db Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 22 Mar 2007 19:17:15 +0200 Subject: Bug #27354 stored function in where condition was always treated as const Possible problems: function call could be eliminated from where class and only be evaluated once; function can be evaluated during table and item setup phase which could cause side effects not to be registered in binlog. Fixed with introducing func_item_sp::used_tables() returning the correct table_map constant. mysql-test/r/sp.result: results changed mysql-test/t/sp.test: regression test demonstrating that function's returns match where condition of the top-level query table. sql/item_func.h: private used_tables() method returning the correct table_bit with meaning the item is not a constant --- mysql-test/r/sp.result | 25 +++++++++++++++++++++++++ mysql-test/t/sp.test | 24 ++++++++++++++++++++++++ 2 files changed, 49 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 34f2aa94000..eae5c3a38d6 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5779,3 +5779,28 @@ SUM(f2) bug25373(f1) DROP FUNCTION bug25373| DROP TABLE t3| drop table t1,t2; +CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM; +CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb; +set @a=0; +CREATE function bug27354() RETURNS int deterministic +begin +insert into t1 values (null); +set @a=@a+1; +return @a; +end| +update t2 set b=1 where a=bug27354(); +select count(t_1.a),count(t_2.a) from t1 as t_1, t2 as t_2 /* must be 0,0 */; +count(t_1.a) count(t_2.a) +0 0 +insert into t2 values (1,1),(2,2),(3,3); +update t2 set b=-b where a=bug27354(); +select * from t2 /* must return 1,-1 ... */; +a b +1 -1 +2 -2 +3 -3 +select count(*) from t1 /* must be 3 */; +count(*) +3 +drop table t1,t2; +drop function bug27354; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 9af24ee0337..0875c61ef0f 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -6770,3 +6770,27 @@ DROP TABLE t3| # practical, or create table t3, t4 etc temporarily (and drop them). delimiter ;| drop table t1,t2; + +CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM; +CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb; +set @a=0; + +delimiter |; +CREATE function bug27354() RETURNS int deterministic +begin +insert into t1 values (null); +set @a=@a+1; +return @a; +end| + +delimiter ;| +update t2 set b=1 where a=bug27354(); +select count(t_1.a),count(t_2.a) from t1 as t_1, t2 as t_2 /* must be 0,0 */; +insert into t2 values (1,1),(2,2),(3,3); +update t2 set b=-b where a=bug27354(); +select * from t2 /* must return 1,-1 ... */; +select count(*) from t1 /* must be 3 */; + + +drop table t1,t2; +drop function bug27354; -- cgit v1.2.1 From 4a76ac5fa6e844c78ec565c2e15ac88f0bb9a8a6 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 23 Mar 2007 17:12:58 +0200 Subject: Bug #27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit was not restored at the end of SF() invocation, where SF() modified non-ta table. As the result of this artifact it was not possible to detect whether there were any side-effects when top-level query ends. If the top level query table was not modified and the bit is lost there would be no binlogging. Fixed with preserving the bit inside of thd->no_trans_update struct. The struct agregates two bool flags telling whether the current query and the current transaction modified any non-ta table. The flags stmt, all are dropped at the end of the query and the transaction. mysql-test/r/sp_trans.result: results will be changed once again after bug#23333 will be fixed. mysql-test/t/sp_trans.test: regression test added sql/ha_ndbcluster.cc: replacing thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit and bool thd->no_trans_update with thd->no_trans_update as struct sql/handler.cc: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. sql/log.cc: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. sql/set_var.cc: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. sql/sp_head.cc: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. sql/sql_class.h: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. sql/sql_delete.cc: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. sql/sql_insert.cc: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. sql/sql_load.cc: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. sql/sql_parse.cc: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. sql/sql_table.cc: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. sql/sql_update.cc: replacing operations with thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit with the member thd->no_trans_update.all; converting thd->no_trans_update into struct of bools. --- mysql-test/r/sp_trans.result | 32 ++++++++++++++++++++++++++++++++ mysql-test/t/sp_trans.test | 30 ++++++++++++++++++++++++++++++ 2 files changed, 62 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/sp_trans.result b/mysql-test/r/sp_trans.result index 564e31c9e32..513bbc43ce3 100644 --- a/mysql-test/r/sp_trans.result +++ b/mysql-test/r/sp_trans.result @@ -530,3 +530,35 @@ count(*) drop table t3, t4| drop procedure bug14210| set @@session.max_heap_table_size=default| +drop function if exists bug23333| +drop table if exists t1,t2| +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM| +CREATE TABLE t2 (a int NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB| +reset master| +insert into t2 values (1,1)| +create function bug23333() +RETURNS int(11) +DETERMINISTIC +begin +insert into t1 values (null); +select count(*) from t1 into @a; +return @a; +end| +insert into t2 values (bug23333(),1)| +ERROR 23000: Duplicate entry '1' for key 1 +show binlog events /* must show the insert */| +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 98 Server ver: 5.0.40-debug-log, Binlog ver: 4 +master-bin.000001 98 Query 1 90 use `test`; insert into t2 values (1,1) +master-bin.000001 188 Xid 1 215 COMMIT /* xid=1165 */ +master-bin.000001 215 Query 1 446 use `test`; CREATE DEFINER=`root`@`localhost` function bug23333() +RETURNS int(11) +DETERMINISTIC +begin +insert into t1 values (null); +select count(*) from t1 into @a; +return @a; +end +select count(*),@a from t1 /* must be 1,1 */| +count(*) @a +1 1 diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index 1ea32316f1e..579af44f28d 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -553,6 +553,36 @@ drop procedure bug14210| set @@session.max_heap_table_size=default| +# +# Bug #13270 INSERT,UPDATE,etc that calls func with side-effect does not binlog +# Bug #23333 stored function + non-transac table + transac table = +# breaks stmt-based binlog +# Bug #27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() +# +--disable_warnings +drop function if exists bug23333| +drop table if exists t1,t2| +--enable_warnings + CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM| + CREATE TABLE t2 (a int NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB| + +reset master| +insert into t2 values (1,1)| + +create function bug23333() +RETURNS int(11) +DETERMINISTIC +begin + insert into t1 values (null); + select count(*) from t1 into @a; + return @a; +end| + +--error ER_DUP_ENTRY +insert into t2 values (bug23333(),1)| +show binlog events /* must show the insert */| +select count(*),@a from t1 /* must be 1,1 */| + # # BUG#NNNN: New bug synopsis # -- cgit v1.2.1 From b765a8af9bb0417a8bbb12cef44014de9badd938 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 23 Mar 2007 19:24:03 +0100 Subject: Bug #26817: mysqldump fails to backup database containing view with invalid definer give some leeway on required permissions for SHOW FIELDS on views so an unknonwn DEFINER will no longer break mysqldump client/client_priv.h: Bug #26817: mysqldump fails to backup database containing view with invalid definer New option for mysqldump: redirect stderr to file ("2> for Windows") client/mysqldump.c: Bug #26817: mysqldump fails to backup database containing view with invalid definer New option for mysqldump: redirect stderr to file ("2> for Windows") mysql-test/r/information_schema_db.result: Bug #26817: mysqldump fails to backup database containing view with invalid definer New option for mysqldump: redirect stderr to file ("2> for Windows") mysql-test/t/information_schema_db.test: Bug #26817: mysqldump fails to backup database containing view with invalid definer New option for mysqldump: redirect stderr to file ("2> for Windows") sql/sql_base.cc: Bug #26817: mysqldump fails to backup database containing view with invalid definer be a little more lenient for SHOW FIELDS FROM sql/sql_parse.cc: Bug #26817: mysqldump fails to backup database containing view with invalid definer be a little more lenient for SHOW FIELDS FROM on views on views sql/sql_view.cc: Bug #26817: mysqldump fails to backup database containing view with invalid definer give SHOW FIELDS the same perks as SHOW CREATE sql/table.cc: Bug #26817: mysqldump fails to backup database containing view with invalid definer give SHOW FIELDS the same perks as SHOW CREATE --- mysql-test/r/information_schema_db.result | 71 ++++++++++++++++++++++++++++++- mysql-test/t/information_schema_db.test | 55 +++++++++++++++++++++++- 2 files changed, 122 insertions(+), 4 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 47efe1d17ad..2d330dda333 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -106,16 +106,82 @@ use testdb_1; create table t1 (f1 char(4)); create view v1 as select f1 from t1; grant insert on v1 to testdb_2@localhost; +create view v5 as select f1 from t1; +grant show view on v5 to testdb_2@localhost; +create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1; +ERROR 42000: Access denied; you need the SUPER privilege for this operation +use testdb_1; +create view v6 as select f1 from t1; +grant show view on v6 to testdb_2@localhost; +create table t2 (f1 char(4)); +create definer=`no_such_user`@`no_such_host` view v7 as select * from t2; +Warnings: +Note 1449 There is no 'no_such_user'@'no_such_host' registered +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 `v6` AS select `t1`.`f1` AS `f1` from `t1` +show create view testdb_1.v7; +View Create View +v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` +Warnings: +Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +show fields from testdb_1.v7; +Field Type Null Key Default Extra +f1 null YES NULL +Warnings: +Note 1449 There is no 'no_such_user'@'no_such_host' registered create table t3 (f1 char(4), f2 char(4)); create view v3 as select f1,f2 from t3; grant insert(f1), insert(f2) on v3 to testdb_2@localhost; 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` +show fields from testdb_1.v7; +Field Type Null Key Default Extra +f1 null YES NULL +Warnings: +Note 1449 There is no 'no_such_user'@'no_such_host' registered +show create view testdb_1.v7; +View Create View +v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` +Warnings: +Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them revoke insert(f1) on v3 from testdb_2@localhost; +revoke show view on v5 from testdb_2@localhost; +use testdb_1; +revoke show view on v6 from testdb_2@localhost; +show fields from testdb_1.v5; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v5' +show create view testdb_1.v5; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v5' +show fields from testdb_1.v6; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v6' +show create view testdb_1.v6; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v6' +show fields from testdb_1.v7; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7' +show create view testdb_1.v7; +ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7' show create view v4; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table show fields from v4; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +Field Type Null Key Default Extra +f1 null YES NULL +f2 char(4) YES NULL show fields from v2; Field Type Null Key Default Extra f1 char(4) YES NULL @@ -140,7 +206,8 @@ where a.table_name = 'testdb_1.v1'; view_definition select * from v2; ERROR HY000: View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -drop view testdb_1.v1,v2, testdb_1.v3, v4; +use test; +drop view testdb_1.v1, v2, testdb_1.v3, v4; drop database testdb_1; drop user testdb_1@localhost; drop user testdb_2@localhost; diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index e15e50e8766..666f331c7b9 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -121,6 +121,28 @@ create table t1 (f1 char(4)); create view v1 as select f1 from t1; grant insert on v1 to testdb_2@localhost; +create view v5 as select f1 from t1; +grant show view on v5 to testdb_2@localhost; + +--error 1227 +create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1; + +connection default; +use testdb_1; +create view v6 as select f1 from t1; +grant show view on v6 to testdb_2@localhost; + +create table t2 (f1 char(4)); +create definer=`no_such_user`@`no_such_host` view v7 as select * from t2; + +show fields from testdb_1.v6; +show create view testdb_1.v6; + +show create view testdb_1.v7; +show fields from testdb_1.v7; + +connection testdb_1; + create table t3 (f1 char(4), f2 char(4)); create view v3 as select f1,f2 from t3; grant insert(f1), insert(f2) on v3 to testdb_2@localhost; @@ -129,13 +151,41 @@ connect (testdb_2,localhost,testdb_2,,test); 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; +show create view testdb_1.v5; + +show fields from testdb_1.v6; +show create view testdb_1.v6; + connection testdb_1; +show fields from testdb_1.v7; +show create view testdb_1.v7; + revoke insert(f1) on v3 from testdb_2@localhost; +revoke show view on v5 from testdb_2@localhost; +connection default; +use testdb_1; +revoke show view on v6 from testdb_2@localhost; connection testdb_2; +--error 1142 +show fields from testdb_1.v5; +--error 1142 +show create view testdb_1.v5; + +--error 1142 +show fields from testdb_1.v6; +--error 1142 +show create view testdb_1.v6; + +--error 1142 +show fields from testdb_1.v7; +--error 1142 +show create view testdb_1.v7; + --error 1345 show create view v4; ---error 1345 +#--error 1345 show fields from v4; show fields from v2; @@ -155,7 +205,8 @@ where a.table_name = 'testdb_1.v1'; select * from v2; connection default; -drop view testdb_1.v1,v2, testdb_1.v3, v4; +use test; +drop view testdb_1.v1, v2, testdb_1.v3, v4; drop database testdb_1; drop user testdb_1@localhost; drop user testdb_2@localhost; -- cgit v1.2.1 From d18945535d24107c865239d7c3f071e98f5cd31b Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 24 Mar 2007 14:40:38 +0200 Subject: Bug #27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() eliminating the unnecessary option; and replacing site-dependant stuff in the test mysql-test/r/sp_trans.result: results changed, will be changed again after bug#23333 fixed mysql-test/t/sp_trans.test: replacing sensitive stuff sql/mysql_priv.h: removal, as part of Bug#27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() --- mysql-test/r/sp_trans.result | 9 ++++----- mysql-test/t/sp_trans.test | 3 ++- 2 files changed, 6 insertions(+), 6 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/sp_trans.result b/mysql-test/r/sp_trans.result index 513bbc43ce3..57c73bea86e 100644 --- a/mysql-test/r/sp_trans.result +++ b/mysql-test/r/sp_trans.result @@ -546,12 +546,11 @@ return @a; end| insert into t2 values (bug23333(),1)| ERROR 23000: Duplicate entry '1' for key 1 -show binlog events /* must show the insert */| +show binlog events from 98 /* must show the insert */| Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 4 Format_desc 1 98 Server ver: 5.0.40-debug-log, Binlog ver: 4 -master-bin.000001 98 Query 1 90 use `test`; insert into t2 values (1,1) -master-bin.000001 188 Xid 1 215 COMMIT /* xid=1165 */ -master-bin.000001 215 Query 1 446 use `test`; CREATE DEFINER=`root`@`localhost` function bug23333() +master-bin.000001 # Query 1 # use `test`; insert into t2 values (1,1) +master-bin.000001 # Xid 1 # COMMIT /* xid=1165 */ +master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` function bug23333() RETURNS int(11) DETERMINISTIC begin diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index 579af44f28d..f2ed39c093e 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -580,7 +580,8 @@ end| --error ER_DUP_ENTRY insert into t2 values (bug23333(),1)| -show binlog events /* must show the insert */| +--replace_column 2 # 5 # +show binlog events from 98 /* must show the insert */| select count(*),@a from t1 /* must be 1,1 */| # -- cgit v1.2.1