diff options
73 files changed, 3264 insertions, 688 deletions
diff --git a/.bzrignore b/.bzrignore index 388dcac2ca6..25dece78561 100644 --- a/.bzrignore +++ b/.bzrignore @@ -1127,3 +1127,144 @@ vio/test-ssl vio/test-sslclient vio/test-sslserver vio/viotest-ssl +*.bin +*.exe +*.idb +*.lib +*.map +*.obj +*.pch +*.pdb +*.res +*.sbr +./copy_mysql_files.bat +./fix-project-files +./mysql*.ds? +./mysql.ncb +./mysql.sln +./mysql.suo +./prepare +./README.build-files +bdb/*.ds? +bdb/*.vcproj +client/*.ds? +client/*.vcproj +client/completion_hash.cpp +client/mysql.cpp +client/mysqladmin.cpp +client/mysqlbinlog.cpp +client/readline.cpp +client/sql_string.cpp +client_debug/* +client_release/* +comp_err/*.ds? +comp_err/*.vcproj +contrib/*.ds? +contrib/*.vcproj +dbug/*.ds? +dbug/*.vcproj +examples/*.ds? +examples/*.vcproj +examples/udf_example/udf_example.def +heap/*.ds? +heap/*.vcproj +innobase/*.ds? +innobase/*.vcproj +isam/*.ds? +isam/*.vcproj +isamchk/*.ds? +isamchk/*.vcproj +lib_debug/* +lib_release/* +libmysql/*.ds? +libmysql/*.vcproj +libmysql/debug/libmysql.exp +libmysql/release/libmysql.exp +libmysqld/*.ds? +libmysqld/*.vcproj +libmysqld/emb_qcache.cpp +libmysqld/lib_sql.cpp +libmysqld/sql_yacc.cpp +libmysqld/sql_yacc.h +libmysqltest/*.ds? +libmysqltest/*.vcproj +libmysqltest/mytest.c +merge/*.ds? +merge/*.vcproj +my_print_defaults/*.ds? +my_print_defaults/*.vcproj +myisam/*.ds? +myisam/*.vcproj +myisam_ftdump/*.ds? +myisam_ftdump/*.vcproj +myisamchk/*.ds? +myisamchk/*.vcproj +myisamlog/*.ds? +myisamlog/*.vcproj +myisammrg/*.ds? +myisammrg/*.vcproj +myisampack/*.ds? +myisampack/*.vcproj +mysql-test/*.ds? +mysql-test/*.vcproj +mysql-test/r/*.err +mysql-test/r/*.out +mysqlbinlog/*.ds? +mysqlbinlog/*.vcproj +mysqlcheck/*.ds? +mysqlcheck/*.vcproj +mysqldemb/*.ds? +mysqldemb/*.vcproj +mysqlserver/*.ds? +mysqlserver/*.vcproj +mysys/*.ds? +mysys/*.vcproj +mysys/my_new.cpp +mysys/raid.cpp +pack_isam/*.ds? +perror/*.ds? +perror/*.vcproj +regex/*.ds? +regex/*.vcproj +replace/*.ds? +replace/*.vcproj +server-tools/instance-manager/buffer.cpp +server-tools/instance-manager/command.cpp +server-tools/instance-manager/commands.cpp +server-tools/instance-manager/guardian.cpp +server-tools/instance-manager/instance.cpp +server-tools/instance-manager/instance_map.cpp +server-tools/instance-manager/instance_options.cpp +server-tools/instance-manager/listener.cpp +server-tools/instance-manager/log.cpp +server-tools/instance-manager/manager.cpp +server-tools/instance-manager/messages.cpp +server-tools/instance-manager/mysql_connection.cpp +server-tools/instance-manager/mysqlmanager.cpp +server-tools/instance-manager/options.cpp +server-tools/instance-manager/parse.cpp +server-tools/instance-manager/parse_output.cpp +server-tools/instance-manager/priv.cpp +server-tools/instance-manager/protocol.cpp +server-tools/instance-manager/thread_registry.cpp +server-tools/instance-manager/user_map.cpp +sql/*.cpp +sql/*.ds? +sql/*.vcproj +sql/max/* +sql/message.h +sql/message.mc +sql/message.rc +strings/*.ds? +strings/*.vcproj +test1/* +tests/*.ds? +tests/*.vcproj +thr_insert_test/* +thr_test/* +vio/*.ds? +vio/*.vcproj +vio/viotest-sslconnect.cpp +vio/viotest.cpp +zlib/*.ds? +zlib/*.vcproj diff --git a/VC++Files/mysqlbinlog/mysqlbinlog.vcproj b/VC++Files/mysqlbinlog/mysqlbinlog.vcproj index 9d5d4db2565..bfe70d6d1af 100644 --- a/VC++Files/mysqlbinlog/mysqlbinlog.vcproj +++ b/VC++Files/mysqlbinlog/mysqlbinlog.vcproj @@ -22,7 +22,7 @@ Name="VCCLCompilerTool" Optimization="0" OptimizeForProcessor="2" - AdditionalIncludeDirectories="../include,../,../sql" + AdditionalIncludeDirectories="../include,../,../sql,../strings" PreprocessorDefinitions="_DEBUG;SAFEMALLOC;SAFE_MUTEX;_CONSOLE;_WINDOWS;MYSQL_SERVER" RuntimeLibrary="1" PrecompiledHeaderFile=".\Debug/mysqlbinlog.pch" diff --git a/include/config-win.h b/include/config-win.h index fe099c11a2f..5c2f8e00e86 100644 --- a/include/config-win.h +++ b/include/config-win.h @@ -392,8 +392,8 @@ inline double ulonglong2double(ulonglong value) #define HAVE_SPATIAL 1 #define HAVE_RTREE_KEYS 1 -#define HAVE_OPENSSL 1 -#define HAVE_YASSL 1 +/* #undef HAVE_OPENSSL */ +/* #undef HAVE_YASSL */ /* Define charsets you want */ /* #undef HAVE_CHARSET_armscii8 */ diff --git a/include/my_global.h b/include/my_global.h index b32a8fe6baa..33cdfa2d03c 100644 --- a/include/my_global.h +++ b/include/my_global.h @@ -553,6 +553,15 @@ typedef SOCKET_SIZE_TYPE size_socket; #define O_NOFOLLOW 0 #endif +/* additional file share flags for win32 */ +#ifdef __WIN__ +#define _SH_DENYRWD 0x110 /* deny read/write mode & delete */ +#define _SH_DENYWRD 0x120 /* deny write mode & delete */ +#define _SH_DENYRDD 0x130 /* deny read mode & delete */ +#define _SH_DENYDEL 0x140 /* deny delete only */ +#endif /* __WIN__ */ + + /* #define USE_RECORD_LOCK */ /* Unsigned types supported by the compiler */ diff --git a/include/my_sys.h b/include/my_sys.h index 76031806b82..44fe383bf4f 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -601,6 +601,7 @@ extern char *_my_strdup_with_length(const byte *from, uint length, #ifdef __WIN__ extern int my_access(const char *path, int amode); +extern File my_sopen(const char *path, int oflag, int shflag, int pmode); #else #define my_access access #endif diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index ea4fa1082db..225b36ba7f6 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -930,7 +930,7 @@ sub executable_setup () { } $exe_mysqlcheck= mtr_exe_exists("$path_client_bindir/mysqlcheck"); $exe_mysqldump= mtr_exe_exists("$path_client_bindir/mysqldump"); - $exe_mysqlimport= mtr_exe_exists("$path_client_bindir/mysqlimport"); + $exe_mysqlimport= mtr_exe_exists("$path_client_bindir/mysqlimport"); $exe_mysqlshow= mtr_exe_exists("$path_client_bindir/mysqlshow"); $exe_mysqlbinlog= mtr_exe_exists("$path_client_bindir/mysqlbinlog"); $exe_mysqladmin= mtr_exe_exists("$path_client_bindir/mysqladmin"); @@ -947,6 +947,7 @@ sub executable_setup () { $path_client_bindir= mtr_path_exists("$glob_basedir/bin"); $exe_mysqlcheck= mtr_exe_exists("$path_client_bindir/mysqlcheck"); $exe_mysqldump= mtr_exe_exists("$path_client_bindir/mysqldump"); + $exe_mysqlimport= mtr_exe_exists("$path_client_bindir/mysqlimport"); $exe_mysqlshow= mtr_exe_exists("$path_client_bindir/mysqlshow"); $exe_mysqlbinlog= mtr_exe_exists("$path_client_bindir/mysqlbinlog"); $exe_mysqladmin= mtr_exe_exists("$path_client_bindir/mysqladmin"); @@ -2008,7 +2009,7 @@ sub mysqld_arguments ($$$$$) { mtr_add_arg($args, "%s--basedir=%s", $prefix, $path_my_basedir); mtr_add_arg($args, "%s--character-sets-dir=%s", $prefix, $path_charsetsdir); mtr_add_arg($args, "%s--core", $prefix); - mtr_add_arg($args, "%s--log-bin-trust-routine-creators", $prefix); + mtr_add_arg($args, "%s--log-bin-trust-function-creators", $prefix); mtr_add_arg($args, "%s--default-character-set=latin1", $prefix); mtr_add_arg($args, "%s--language=%s", $prefix, $path_language); mtr_add_arg($args, "%s--tmpdir=$opt_tmpdir", $prefix); @@ -2131,7 +2132,7 @@ sub mysqld_arguments ($$$$$) { mtr_add_arg($args, "%s--key_buffer_size=1M", $prefix); mtr_add_arg($args, "%s--sort_buffer=256K", $prefix); mtr_add_arg($args, "%s--max_heap_table_size=1M", $prefix); - mtr_add_arg($args, "%s--log-bin-trust-routine-creators", $prefix); + mtr_add_arg($args, "%s--log-bin-trust-function-creators", $prefix); if ( $opt_ssl_supported ) { diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index 62c2b9014c3..c84763713e1 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -1275,7 +1275,7 @@ start_master() --language=$LANGUAGE \ --innodb_data_file_path=ibdata1:128M:autoextend \ --open-files-limit=1024 \ - --log-bin-trust-routine-creators \ + --log-bin-trust-function-creators \ $MASTER_40_ARGS \ $SMALL_SERVER \ $EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT \ @@ -1296,7 +1296,7 @@ start_master() --tmpdir=$MYSQL_TMP_DIR \ --language=$LANGUAGE \ --innodb_data_file_path=ibdata1:128M:autoextend \ - --log-bin-trust-routine-creators \ + --log-bin-trust-function-creators \ $MASTER_40_ARGS \ $SMALL_SERVER \ $EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT \ @@ -1429,7 +1429,7 @@ start_slave() --report-port=$slave_port \ --master-retry-count=10 \ -O slave_net_timeout=10 \ - --log-bin-trust-routine-creators \ + --log-bin-trust-function-creators \ $SMALL_SERVER \ $EXTRA_SLAVE_OPT $EXTRA_SLAVE_MYSQLD_OPT" CUR_MYERR=$slave_err diff --git a/mysql-test/mysql_test_run_new.c b/mysql-test/mysql_test_run_new.c index 33a69eba872..79db71fa274 100644 --- a/mysql-test/mysql_test_run_new.c +++ b/mysql-test/mysql_test_run_new.c @@ -486,7 +486,7 @@ void start_master() #endif add_arg(&al, "--local-infile"); add_arg(&al, "--core"); - add_arg(&al, "--log-bin-trust-routine-creators"); + add_arg(&al, "--log-bin-trust-function-creators"); add_arg(&al, "--datadir=%s", master_dir); #ifndef __WIN__ add_arg(&al, "--pid-file=%s", master_pid); diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index a5852dc31b4..079a1af1184 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -647,12 +647,16 @@ drop function sub1; select table_name from information_schema.views where table_schema='test'; table_name +v2 +v3 Warnings: Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select table_name from information_schema.views where table_schema='test'; table_name +v2 +v3 Warnings: Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them @@ -669,6 +673,16 @@ f1_key select constraint_name from information_schema.table_constraints where table_schema='test'; constraint_name +show create view v2; +View Create View +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `test`.`t1`.`f1` AS `c` from `t1` +Warnings: +Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +show create table v3; +View Create View +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select sql_no_cache `test`.`sub1`(1) AS `c` +Warnings: +Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them drop view v2; drop view v3; drop table t4; @@ -722,6 +736,7 @@ information_schema ROUTINES SQL_MODE information_schema TRIGGERS ACTION_CONDITION information_schema TRIGGERS ACTION_STATEMENT information_schema TRIGGERS SQL_MODE +information_schema TRIGGERS DEFINER information_schema VIEWS VIEW_DEFINITION select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; @@ -800,45 +815,45 @@ set @fired:= "Yes"; end if; end| show triggers; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 begin if new.j > 10 then set new.j := 10; end if; -end BEFORE NULL +end BEFORE NULL root@localhost trg2 UPDATE t1 begin if old.i % 2 = 0 then set new.j := -1; end if; -end BEFORE NULL +end BEFORE NULL root@localhost trg3 UPDATE t1 begin if new.j = -1 then set @fired:= "Yes"; end if; -end AFTER NULL +end AFTER NULL root@localhost select * from information_schema.triggers; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER NULL test trg1 INSERT NULL test t1 0 NULL begin if new.j > 10 then set new.j := 10; end if; -end ROW BEFORE NULL NULL OLD NEW NULL +end ROW BEFORE NULL NULL OLD NEW NULL root@localhost NULL test trg2 UPDATE NULL test t1 0 NULL begin if old.i % 2 = 0 then set new.j := -1; end if; -end ROW BEFORE NULL NULL OLD NEW NULL +end ROW BEFORE NULL NULL OLD NEW NULL root@localhost NULL test trg3 UPDATE NULL test t1 0 NULL begin if new.j = -1 then set @fired:= "Yes"; end if; -end ROW AFTER NULL NULL OLD NEW NULL +end ROW AFTER NULL NULL OLD NEW NULL root@localhost drop trigger trg1; drop trigger trg2; drop trigger trg3; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 82a761252b5..3b3db08303a 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -1614,6 +1614,16 @@ mysqldump: Couldn't find table: "t\1" mysqldump: Couldn't find table: "t/1" +mysqldump: Couldn't find table: "T_1" + +mysqldump: Couldn't find table: "T%1" + +mysqldump: Couldn't find table: "T'1" + +mysqldump: Couldn't find table: "T_1" + +mysqldump: Couldn't find table: "T_" + test_sequence ------ Testing with illegal database names ------ mysqldump: Got error: 1049: Unknown database 'mysqldump_test_d' when selecting the database @@ -1926,23 +1936,23 @@ end if; end| set sql_mode=default| show triggers like "t1"; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; -end BEFORE 0000-00-00 00:00:00 +end BEFORE 0000-00-00 00:00:00 root@localhost trg2 UPDATE t1 begin if old.a % 2 = 0 then set new.b := 12; end if; -end BEFORE 0000-00-00 00:00:00 +end BEFORE 0000-00-00 00:00:00 root@localhost trg3 UPDATE t1 begin if new.a = -1 then set @fired:= "Yes"; end if; -end AFTER 0000-00-00 00:00:00 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +end AFTER 0000-00-00 00:00:00 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost INSERT INTO t1 (a) VALUES (1),(2),(3),(22); update t1 set a = 4 where a=3; @@ -2085,29 +2095,29 @@ Tables_in_test t1 t2 show triggers; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; -end BEFORE # +end BEFORE # root@localhost trg2 UPDATE t1 begin if old.a % 2 = 0 then set new.b := 12; end if; -end BEFORE # +end BEFORE # root@localhost trg3 UPDATE t1 begin if new.a = -1 then set @fired:= "Yes"; end if; -end AFTER # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +end AFTER # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost trg4 INSERT t2 begin if new.a > 10 then set @fired:= "No"; end if; -end BEFORE # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +end BEFORE # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost DROP TABLE t1, t2; --port=1234 --port=1234 @@ -2130,9 +2140,9 @@ SELECT * FROM `test2`; a2 1 SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer testref INSERT test1 BEGIN -INSERT INTO test2 SET a2 = NEW.a1; END BEFORE NULL +INSERT INTO test2 SET a2 = NEW.a1; END BEFORE NULL root@localhost SELECT * FROM `test1`; a1 1 @@ -2147,6 +2157,7 @@ DROP FUNCTION IF EXISTS bug9056_func1; DROP FUNCTION IF EXISTS bug9056_func2; DROP PROCEDURE IF EXISTS bug9056_proc1; DROP PROCEDURE IF EXISTS bug9056_proc2; +DROP PROCEDURE IF EXISTS `a'b`; CREATE TABLE t1 (id int); INSERT INTO t1 VALUES(1), (2), (3), (4), (5); CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11) RETURN a+b // diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index d41d1b74ca7..aba3da02c4c 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -332,7 +332,6 @@ CSV YES/NO CSV storage engine ndbcluster YES/NO Clustered, fault-tolerant, memory-based tables FEDERATED YES/NO Federated MySQL storage engine MRG_MYISAM YES/NO Collection of identical MyISAM tables -binlog YES/NO This is a meta storage engine to represent the binlog in a transaction ISAM YES/NO Obsolete storage engine drop table if exists t5; prepare stmt1 from ' drop table if exists t5 ' ; diff --git a/mysql-test/r/rpl_ddl.result b/mysql-test/r/rpl_ddl.result index 2a97da63c64..4d8f2f11d4a 100644 --- a/mysql-test/r/rpl_ddl.result +++ b/mysql-test/r/rpl_ddl.result @@ -1465,13 +1465,13 @@ flush logs; -------- switch to master ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode -trg1 INSERT t1 SET @a:=1 BEFORE NULL +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost -------- switch to slave ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode -trg1 INSERT t1 SET @a:=1 BEFORE NULL +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost ######## DROP TRIGGER trg1 ######## @@ -1520,11 +1520,11 @@ flush logs; -------- switch to master ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer -------- switch to slave ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer ######## CREATE USER user1@localhost ######## diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result index 5f1c3afd14d..ba840caf6c2 100644 --- a/mysql-test/r/rpl_sp.result +++ b/mysql-test/r/rpl_sp.result @@ -4,16 +4,11 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; -create database if not exists mysqltest1; +drop database if exists mysqltest1; +create database mysqltest1; use mysqltest1; create table t1 (a varchar(100)); use mysqltest1; -drop procedure if exists foo; -drop procedure if exists foo2; -drop procedure if exists foo3; -drop procedure if exists foo4; -drop procedure if exists bar; -drop function if exists fn1; create procedure foo() begin declare b int; @@ -21,21 +16,9 @@ set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); end| -ERROR HY000: This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) -show binlog events from 98| -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # create database if not exists mysqltest1 -master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a varchar(100)) -create procedure foo() deterministic -begin -declare b int; -set b = 8; -insert into t1 values (b); -insert into t1 values (unix_timestamp()); -end| select * from mysql.proc where name='foo' and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL YES DEFINER begin +mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin declare b int; set b = 8; insert into t1 values (b); @@ -43,7 +26,7 @@ insert into t1 values (unix_timestamp()); end root@localhost # # select * from mysql.proc where name='foo' and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL YES DEFINER begin +mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin declare b int; set b = 8; insert into t1 values (b); @@ -51,17 +34,6 @@ insert into t1 values (unix_timestamp()); end @ # # set timestamp=1000000000; call foo(); -show binlog events from 308; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo() deterministic -begin -declare b int; -set b = 8; -insert into t1 values (b); -insert into t1 values (unix_timestamp()); -end -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8)) -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp()) select * from t1; a 8 @@ -72,22 +44,10 @@ a 1000000000 delete from t1; create procedure foo2() -not deterministic -reads sql data select * from mysqltest1.t1; call foo2(); a -show binlog events from 518; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8)) -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp()) -master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 -master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo2() -not deterministic -reads sql data -select * from mysqltest1.t1 alter procedure foo2 contains sql; -ERROR HY000: This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) drop table t1; create table t1 (a int); create table t2 like t1; @@ -99,57 +59,21 @@ grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1; create procedure foo4() deterministic -insert into t1 values (10); -ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) -set global log_bin_trust_routine_creators=1; -create procedure foo4() -deterministic begin insert into t2 values(3); insert into t1 values (5); end| call foo4(); Got one of the listed errors -show warnings; -Level Code Message -Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1' -Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes call foo3(); show warnings; Level Code Message call foo4(); Got one of the listed errors -show warnings; -Level Code Message -Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'127.0.0.1' for table 't1' -Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes alter procedure foo4 sql security invoker; call foo4(); show warnings; Level Code Message -show binlog events from 990; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1 -master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int) -master-bin.000001 # Query 1 # use `mysqltest1`; create table t2 like t1 -master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo3() -deterministic -insert into t1 values (15) -master-bin.000001 # Query 1 # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1 -master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1 -master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1 -master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4() -deterministic -begin -insert into t2 values(3); -insert into t1 values (5); -end -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (15) -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) -master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo4 sql security invoker -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (5) select * from t1; a 15 @@ -168,11 +92,29 @@ a 3 3 3 +delete from t2; +alter table t2 add unique (a); +drop procedure foo4; +create procedure foo4() +deterministic +begin +insert into t2 values(20),(20); +end| +call foo4(); +ERROR 23000: Duplicate entry '20' for key 1 +show warnings; +Level Code Message +Error 1062 Duplicate entry '20' for key 1 +select * from t2; +a +20 +select * from t2; +a +20 select * from mysql.proc where name="foo4" and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES INVOKER begin -insert into t2 values(3); -insert into t1 values (5); +mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES DEFINER begin +insert into t2 values(20),(20); end @ # # drop procedure foo4; select * from mysql.proc where name="foo4" and db='mysqltest1'; @@ -184,6 +126,13 @@ drop procedure foo2; drop procedure foo3; create function fn1(x int) returns int +begin +insert into t1 values (x); +return x+2; +end| +ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) +create function fn1(x int) +returns int deterministic begin insert into t1 values (x); @@ -211,17 +160,54 @@ a drop function fn1; create function fn1() returns int -deterministic +no sql begin return unix_timestamp(); end| +alter function fn1 contains sql; +ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) delete from t1; set timestamp=1000000000; insert into t1 values(fn1()); +create function fn2() +returns int +no sql +begin +return unix_timestamp(); +end| +ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) +set global log_bin_trust_routine_creators=1; +Warnings: +Warning 1287 'log_bin_trust_routine_creators' is deprecated; use 'log_bin_trust_function_creators' instead +set global log_bin_trust_function_creators=0; +set global log_bin_trust_function_creators=1; +set global log_bin_trust_function_creators=1; +create function fn2() +returns int +no sql +begin +return unix_timestamp(); +end| +create function fn3() +returns int +not deterministic +reads sql data +begin +return 0; +end| +select fn3(); +fn3() +0 select * from mysql.proc where db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -mysqltest1 fn1 FUNCTION fn1 SQL CONTAINS_SQL YES DEFINER int(11) begin +mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin +return unix_timestamp(); +end root@localhost # # +mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); +end zedjzlcsjhd@localhost # # +mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin +return 0; end root@localhost # # select * from t1; a @@ -232,12 +218,34 @@ a 1000000000 select * from mysql.proc where db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -mysqltest1 fn1 FUNCTION fn1 SQL CONTAINS_SQL YES DEFINER int(11) begin +mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin +return unix_timestamp(); +end @ # # +mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); end @ # # +mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin +return 0; +end @ # # +delete from t2; +alter table t2 add unique (a); +drop function fn1; +create function fn1() +returns int +begin +insert into t2 values(20),(20); +return 10; +end| +select fn1(); +ERROR 23000: Duplicate entry '20' for key 1 +select * from t2; +a +20 +select * from t2; +a +20 create trigger trg before insert on t1 for each row set new.a= 10; ERROR 42000: Access denied; you need the SUPER privilege for this operation -flush logs; delete from t1; create trigger trg before insert on t1 for each row set new.a= 10; insert into t1 values (1); @@ -253,14 +261,106 @@ insert into t1 values (1); select * from t1; a 1 -show binlog events in 'master-bin.000002' from 98; +show binlog events in 'master-bin.000001' from 98; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 -master-bin.000002 # Query 1 # use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10 -master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) -master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 -master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger trg -master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) +master-bin.000001 # Query 1 # drop database if exists mysqltest1 +master-bin.000001 # Query 1 # create database mysqltest1 +master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a varchar(100)) +master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo() +begin +declare b int; +set b = 8; +insert into t1 values (b); +insert into t1 values (unix_timestamp()); +end +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8)) +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp()) +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo2() +select * from mysqltest1.t1 +master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo2 contains sql +master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1 +master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int) +master-bin.000001 # Query 1 # use `mysqltest1`; create table t2 like t1 +master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo3() +deterministic +insert into t1 values (15) +master-bin.000001 # Query 1 # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1 +master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1 +master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1 +master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4() +deterministic +begin +insert into t2 values(3); +insert into t1 values (5); +end +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (15) +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) +master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo4 sql security invoker +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (5) +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t2 +master-bin.000001 # Query 1 # use `mysqltest1`; alter table t2 add unique (a) +master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo4 +master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4() +deterministic +begin +insert into t2 values(20),(20); +end +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(20),(20) +master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo4 +master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo +master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo2 +master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo3 +master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1(x int) +returns int +deterministic +begin +insert into t1 values (x); +return x+2; +end +master-bin.000001 # Query 1 # use `mysqltest1`; delete t1,t2 from t1,t2 +master-bin.000001 # Query 1 # use `mysqltest1`; DO `fn1`(20) +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(fn1(21)) +master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1 +master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1() +returns int +no sql +begin +return unix_timestamp(); +end +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values(fn1()) +master-bin.000001 # Query 1 # use `mysqltest1`; create function fn2() +returns int +no sql +begin +return unix_timestamp(); +end +master-bin.000001 # Query 1 # use `mysqltest1`; create function fn3() +returns int +not deterministic +reads sql data +begin +return 0; +end +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t2 +master-bin.000001 # Query 1 # use `mysqltest1`; alter table t2 add unique (a) +master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1 +master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1() +returns int +begin +insert into t2 values(20),(20); +return 10; +end +master-bin.000001 # Query 1 # use `mysqltest1`; DO `fn1`() +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10 +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (1) +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; drop trigger trg +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (1) select * from t1; a 1 diff --git a/mysql-test/r/rpl_trigger.result b/mysql-test/r/rpl_trigger.result index db824c9c423..999af131b8b 100644 --- a/mysql-test/r/rpl_trigger.result +++ b/mysql-test/r/rpl_trigger.result @@ -89,8 +89,24 @@ insert into t1 set a = now(); select a=b && a=c from t1; a=b && a=c 1 +SELECT routine_name, definer +FROM information_schema.routines; +routine_name definer +bug12480 root@localhost +SELECT trigger_name, definer +FROM information_schema.triggers; +trigger_name definer +t1_first root@localhost --- On slave -- +SELECT routine_name, definer +FROM information_schema.routines; +routine_name definer +bug12480 @ +SELECT trigger_name, definer +FROM information_schema.triggers; +trigger_name definer +t1_first root@localhost select a=b && a=c from t1; a=b && a=c 1 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 318cf8e7b65..8a126b7ddfb 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3241,3 +3241,45 @@ f1 f2 Warnings: Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 drop table t1; +create table t1 (f1 int, f2 int); +insert into t1 values (1, 30), (2, 20), (3, 10); +create algorithm=merge view v1 as select f1, f2 from t1; +create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1; +create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1; +select t1.f1 as x1, f1 from t1 order by t1.f1; +x1 f1 +1 1 +2 2 +3 3 +select v1.f1 as x1, f1 from v1 order by v1.f1; +x1 f1 +1 1 +2 2 +3 3 +select v2.f1 as x1, f1 from v2 order by v2.f1; +x1 f1 +10 10 +20 20 +30 30 +select v3.f1 as x1, f1 from v3 order by v3.f1; +x1 f1 +10 10 +20 20 +30 30 +select f1, f2, v1.f1 as x1 from v1 order by v1.f1; +f1 f2 x1 +1 30 1 +2 20 2 +3 10 3 +select f1, f2, v2.f1 as x1 from v2 order by v2.f1; +f1 f2 x1 +10 3 10 +20 2 20 +30 1 30 +select f1, f2, v3.f1 as x1 from v3 order by v3.f1; +f1 f2 x1 +10 3 10 +20 2 20 +30 1 30 +drop table t1; +drop view v1, v2, v3; diff --git a/mysql-test/r/skip_grants.result b/mysql-test/r/skip_grants.result index 4d723f8e12a..5dc770a7363 100644 --- a/mysql-test/r/skip_grants.result +++ b/mysql-test/r/skip_grants.result @@ -4,7 +4,7 @@ drop procedure if exists f1; use test; create table t1 (field1 INT); CREATE VIEW v1 AS SELECT field1 FROM t1; -ERROR HY000: View definer is not fully qualified +ERROR HY000: Definer is not fully qualified drop table t1; create procedure f1() select 1; drop procedure f1; diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index a23b584e510..4dec7882d58 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -172,3 +172,75 @@ group by country; countrycount smcnt country total_funds 1 1200 USA 1200 drop table t1; +CREATE TABLE `t1` ( +`t3_id` int NOT NULL, +`t1_id` int NOT NULL, +PRIMARY KEY (`t1_id`) +); +CREATE TABLE `t2` ( +`t2_id` int NOT NULL, +`t1_id` int NOT NULL, +`b` int NOT NULL, +PRIMARY KEY (`t2_id`), +UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`) +) ENGINE=InnoDB; +CREATE TABLE `t3` ( +`t3_id` int NOT NULL +); +INSERT INTO `t3` VALUES (3); +select +(SELECT rs.t2_id +FROM t2 rs +WHERE rs.t1_id= +(SELECT lt.t1_id +FROM t1 lt +WHERE lt.t3_id=a.t3_id) +ORDER BY b DESC LIMIT 1) +from t3 AS a; +(SELECT rs.t2_id +FROM t2 rs +WHERE rs.t1_id= +(SELECT lt.t1_id +FROM t1 lt +WHERE lt.t3_id=a.t3_id) +ORDER BY b DESC LIMIT 1) +NULL +DROP PROCEDURE IF EXISTS p1; +create procedure p1() +begin +declare done int default 3; +repeat +select +(SELECT rs.t2_id +FROM t2 rs +WHERE rs.t1_id= +(SELECT lt.t1_id +FROM t1 lt +WHERE lt.t3_id=a.t3_id) +ORDER BY b DESC LIMIT 1) as x +from t3 AS a; +set done= done-1; +until done <= 0 end repeat; +end// +call p1(); +x +NULL +x +NULL +x +NULL +call p1(); +x +NULL +x +NULL +x +NULL +call p1(); +x +NULL +x +NULL +x +NULL +drop tables t1,t2,t3; diff --git a/mysql-test/r/trigger-compat.result b/mysql-test/r/trigger-compat.result new file mode 100644 index 00000000000..5c104a2d2d5 --- /dev/null +++ b/mysql-test/r/trigger-compat.result @@ -0,0 +1,40 @@ +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; +DROP DATABASE IF EXISTS mysqltest_db1; +CREATE DATABASE mysqltest_db1; +CREATE USER mysqltest_dfn@localhost; +CREATE USER mysqltest_inv@localhost; +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; +GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +---> connection: wl2818_definer_con +CREATE TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); +CREATE TRIGGER wl2818_trg1 BEFORE INSERT ON t1 +FOR EACH ROW +INSERT INTO t2 VALUES(CURRENT_USER()); + +---> patching t1.TRG... + +CREATE TRIGGER wl2818_trg2 AFTER INSERT ON t1 +FOR EACH ROW +INSERT INTO t2 VALUES(CURRENT_USER()); +Warnings: +Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. + +SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; +trigger_name definer +wl2818_trg1 +wl2818_trg2 mysqltest_dfn@localhost +Warnings: +Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER +NULL mysqltest_db1 wl2818_trg1 INSERT NULL mysqltest_db1 t1 0 NULL +INSERT INTO t2 VALUES(CURRENT_USER()) ROW BEFORE NULL NULL OLD NEW NULL +NULL mysqltest_db1 wl2818_trg2 INSERT NULL mysqltest_db1 t1 0 NULL +INSERT INTO t2 VALUES(CURRENT_USER()) ROW AFTER NULL NULL OLD NEW NULL mysqltest_dfn@localhost diff --git a/mysql-test/r/trigger-grant.result b/mysql-test/r/trigger-grant.result new file mode 100644 index 00000000000..eda1adfdf65 --- /dev/null +++ b/mysql-test/r/trigger-grant.result @@ -0,0 +1,238 @@ +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; +DROP DATABASE IF EXISTS mysqltest_db1; +CREATE DATABASE mysqltest_db1; +CREATE USER mysqltest_dfn@localhost; +CREATE USER mysqltest_inv@localhost; +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; +GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +---> connection: wl2818_definer_con +CREATE TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); +CREATE TRIGGER trg1 AFTER INSERT ON t1 +FOR EACH ROW +INSERT INTO t2 VALUES(CURRENT_USER()); + +---> connection: default +GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; +GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; +GRANT ALL PRIVILEGES ON mysqltest_db1.t1 +TO 'mysqltest_inv'@localhost; +GRANT SELECT ON mysqltest_db1.t2 +TO 'mysqltest_inv'@localhost; + +---> connection: wl2818_definer_con +use mysqltest_db1; +INSERT INTO t1 VALUES(1); +SELECT * FROM t1; +num_value +1 +SELECT * FROM t2; +user_str +mysqltest_dfn@localhost + +---> connection: wl2818_invoker_con +use mysqltest_db1; +INSERT INTO t1 VALUES(2); +SELECT * FROM t1; +num_value +1 +2 +SELECT * FROM t2; +user_str +mysqltest_dfn@localhost +mysqltest_dfn@localhost + +---> connection: default +use mysqltest_db1; +REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; + +---> connection: wl2818_invoker_con +use mysqltest_db1; +INSERT INTO t1 VALUES(3); +ERROR 42000: INSERT command denied to user 'mysqltest_dfn'@'localhost' for table 't2' +SELECT * FROM t1; +num_value +1 +2 +3 +SELECT * FROM t2; +user_str +mysqltest_dfn@localhost +mysqltest_dfn@localhost + +---> connection: default +use mysqltest_db1; +REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; + +---> connection: wl2818_definer_con +use mysqltest_db1; +DROP TRIGGER trg1; +SET @new_sum = 0; +SET @old_sum = 0; +---> INSERT INTO statement; BEFORE timing +CREATE TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +INSERT INTO t1 VALUES(4); +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> INSERT INTO statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER INSERT ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +INSERT INTO t1 VALUES(5); +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> UPDATE statement; BEFORE timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 BEFORE UPDATE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +UPDATE t1 SET num_value = 10; +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> UPDATE statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER UPDATE ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +UPDATE t1 SET num_value = 20; +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> DELETE statement; BEFORE timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 BEFORE DELETE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +DELETE FROM t1; +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> DELETE statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER DELETE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +DELETE FROM t1; +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' + +---> connection: default +use mysqltest_db1; +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; +REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; + +---> connection: wl2818_definer_con +use mysqltest_db1; +DROP TRIGGER trg1; +SET @new_sum = 0; +SET @old_sum = 0; +---> INSERT INTO statement; BEFORE timing +CREATE TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +INSERT INTO t1 VALUES(4); +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> INSERT INTO statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER INSERT ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +INSERT INTO t1 VALUES(5); +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> UPDATE statement; BEFORE timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 BEFORE UPDATE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +UPDATE t1 SET num_value = 10; +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> UPDATE statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER UPDATE ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +UPDATE t1 SET num_value = 20; +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> DELETE statement; BEFORE timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 BEFORE DELETE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +DELETE FROM t1; +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> DELETE statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER DELETE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +DELETE FROM t1; +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' + +---> connection: wl2818_definer_con +use mysqltest_db1; +DROP TRIGGER trg1; +CREATE DEFINER='mysqltest_inv'@'localhost' + TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @new_sum = 0; +CREATE DEFINER='mysqltest_nonexs'@'localhost' + TRIGGER trg2 AFTER INSERT ON t1 +FOR EACH ROW +SET @new_sum = 0; +Warnings: +Note 1449 There is no 'mysqltest_nonexs'@'localhost' registered +INSERT INTO t1 VALUES(6); +ERROR 42000: Access denied; you need the SUPER privilege for this operation +SHOW TRIGGERS; +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 +SET @new_sum = 0 BEFORE NULL mysqltest_inv@localhost +trg2 INSERT t1 +SET @new_sum = 0 AFTER NULL mysqltest_nonexs@localhost +DROP TRIGGER trg1; +DROP TRIGGER trg2; +CREATE TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @a = 1; +CREATE TRIGGER trg2 AFTER INSERT ON t1 +FOR EACH ROW +SET @a = 2; +CREATE TRIGGER trg3 BEFORE UPDATE ON t1 +FOR EACH ROW +SET @a = 3; +CREATE TRIGGER trg4 AFTER UPDATE ON t1 +FOR EACH ROW +SET @a = 4; +CREATE TRIGGER trg5 BEFORE DELETE ON t1 +FOR EACH ROW +SET @a = 5; + +SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; +trigger_name definer +trg1 +trg2 @ +trg3 @abc@def@@ +trg4 @hostname +trg5 @abcdef@@@hostname +Warnings: +Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER +NULL mysqltest_db1 trg1 INSERT NULL mysqltest_db1 t1 0 NULL +SET @a = 1 ROW BEFORE NULL NULL OLD NEW NULL +NULL mysqltest_db1 trg2 INSERT NULL mysqltest_db1 t1 0 NULL +SET @a = 2 ROW AFTER NULL NULL OLD NEW NULL @ +NULL mysqltest_db1 trg3 UPDATE NULL mysqltest_db1 t1 0 NULL +SET @a = 3 ROW BEFORE NULL NULL OLD NEW NULL @abc@def@@ +NULL mysqltest_db1 trg4 UPDATE NULL mysqltest_db1 t1 0 NULL +SET @a = 4 ROW AFTER NULL NULL OLD NEW NULL @hostname +NULL mysqltest_db1 trg5 DELETE NULL mysqltest_db1 t1 0 NULL +SET @a = 5 ROW BEFORE NULL NULL OLD NEW NULL @abcdef@@@hostname + +---> connection: default +DROP USER mysqltest_dfn@localhost; +DROP USER mysqltest_inv@localhost; +DROP DATABASE mysqltest_db1; +Warnings: +Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index b305691fa18..d3fbb56e493 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -611,9 +611,9 @@ select @a; @a 10 show triggers; -Trigger Event Table Statement Timing Created sql_mode -t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI -t1_af INSERT t1 set @a=10 AFTER # +Trigger Event Table Statement Timing Created sql_mode Definer +t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI root@localhost +t1_af INSERT t1 set @a=10 AFTER # root@localhost drop table t1; set sql_mode="traditional"; create table t1 (a date); @@ -633,8 +633,8 @@ t1 CREATE TABLE `t1` ( `a` date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show triggers; -Trigger Event Table Statement Timing Created sql_mode -t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # +Trigger Event Table Statement Timing Created sql_mode Definer +t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # root@localhost drop table t1; create table t1 (id int); create trigger t1_ai after insert on t1 for each row flush tables; diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 1ed4135d52b..7d7fe7efa35 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1020,6 +1020,416 @@ cast(@non_existing_user_var/2 as DECIMAL) NULL create table t (d decimal(0,10)); ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'd'). +CREATE TABLE t1 ( +my_float FLOAT, +my_double DOUBLE, +my_varchar VARCHAR(50), +my_decimal DECIMAL(65,30) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `my_float` float default NULL, + `my_double` double default NULL, + `my_varchar` varchar(50) default NULL, + `my_decimal` decimal(65,30) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 SET my_float = 1.175494345e-32, +my_double = 1.175494345e-32, +my_varchar = '1.175494345e-32'; +INSERT INTO t1 SET my_float = 1.175494345e-31, +my_double = 1.175494345e-31, +my_varchar = '1.175494345e-31'; +INSERT INTO t1 SET my_float = 1.175494345e-30, +my_double = 1.175494345e-30, +my_varchar = '1.175494345e-30'; +INSERT INTO t1 SET my_float = 1.175494345e-29, +my_double = 1.175494345e-29, +my_varchar = '1.175494345e-29'; +INSERT INTO t1 SET my_float = 1.175494345e-28, +my_double = 1.175494345e-28, +my_varchar = '1.175494345e-28'; +INSERT INTO t1 SET my_float = 1.175494345e-27, +my_double = 1.175494345e-27, +my_varchar = '1.175494345e-27'; +INSERT INTO t1 SET my_float = 1.175494345e-26, +my_double = 1.175494345e-26, +my_varchar = '1.175494345e-26'; +INSERT INTO t1 SET my_float = 1.175494345e-25, +my_double = 1.175494345e-25, +my_varchar = '1.175494345e-25'; +INSERT INTO t1 SET my_float = 1.175494345e-24, +my_double = 1.175494345e-24, +my_varchar = '1.175494345e-24'; +INSERT INTO t1 SET my_float = 1.175494345e-23, +my_double = 1.175494345e-23, +my_varchar = '1.175494345e-23'; +INSERT INTO t1 SET my_float = 1.175494345e-22, +my_double = 1.175494345e-22, +my_varchar = '1.175494345e-22'; +INSERT INTO t1 SET my_float = 1.175494345e-21, +my_double = 1.175494345e-21, +my_varchar = '1.175494345e-21'; +INSERT INTO t1 SET my_float = 1.175494345e-20, +my_double = 1.175494345e-20, +my_varchar = '1.175494345e-20'; +INSERT INTO t1 SET my_float = 1.175494345e-19, +my_double = 1.175494345e-19, +my_varchar = '1.175494345e-19'; +INSERT INTO t1 SET my_float = 1.175494345e-18, +my_double = 1.175494345e-18, +my_varchar = '1.175494345e-18'; +INSERT INTO t1 SET my_float = 1.175494345e-17, +my_double = 1.175494345e-17, +my_varchar = '1.175494345e-17'; +INSERT INTO t1 SET my_float = 1.175494345e-16, +my_double = 1.175494345e-16, +my_varchar = '1.175494345e-16'; +INSERT INTO t1 SET my_float = 1.175494345e-15, +my_double = 1.175494345e-15, +my_varchar = '1.175494345e-15'; +INSERT INTO t1 SET my_float = 1.175494345e-14, +my_double = 1.175494345e-14, +my_varchar = '1.175494345e-14'; +INSERT INTO t1 SET my_float = 1.175494345e-13, +my_double = 1.175494345e-13, +my_varchar = '1.175494345e-13'; +INSERT INTO t1 SET my_float = 1.175494345e-12, +my_double = 1.175494345e-12, +my_varchar = '1.175494345e-12'; +INSERT INTO t1 SET my_float = 1.175494345e-11, +my_double = 1.175494345e-11, +my_varchar = '1.175494345e-11'; +INSERT INTO t1 SET my_float = 1.175494345e-10, +my_double = 1.175494345e-10, +my_varchar = '1.175494345e-10'; +INSERT INTO t1 SET my_float = 1.175494345e-9, +my_double = 1.175494345e-9, +my_varchar = '1.175494345e-9'; +INSERT INTO t1 SET my_float = 1.175494345e-8, +my_double = 1.175494345e-8, +my_varchar = '1.175494345e-8'; +INSERT INTO t1 SET my_float = 1.175494345e-7, +my_double = 1.175494345e-7, +my_varchar = '1.175494345e-7'; +INSERT INTO t1 SET my_float = 1.175494345e-6, +my_double = 1.175494345e-6, +my_varchar = '1.175494345e-6'; +INSERT INTO t1 SET my_float = 1.175494345e-5, +my_double = 1.175494345e-5, +my_varchar = '1.175494345e-5'; +INSERT INTO t1 SET my_float = 1.175494345e-4, +my_double = 1.175494345e-4, +my_varchar = '1.175494345e-4'; +INSERT INTO t1 SET my_float = 1.175494345e-3, +my_double = 1.175494345e-3, +my_varchar = '1.175494345e-3'; +INSERT INTO t1 SET my_float = 1.175494345e-2, +my_double = 1.175494345e-2, +my_varchar = '1.175494345e-2'; +INSERT INTO t1 SET my_float = 1.175494345e-1, +my_double = 1.175494345e-1, +my_varchar = '1.175494345e-1'; +SELECT my_float, my_double, my_varchar FROM t1; +my_float my_double my_varchar +1.17549e-32 1.175494345e-32 1.175494345e-32 +1.17549e-31 1.175494345e-31 1.175494345e-31 +1.17549e-30 1.175494345e-30 1.175494345e-30 +1.17549e-29 1.175494345e-29 1.175494345e-29 +1.17549e-28 1.175494345e-28 1.175494345e-28 +1.17549e-27 1.175494345e-27 1.175494345e-27 +1.17549e-26 1.175494345e-26 1.175494345e-26 +1.17549e-25 1.175494345e-25 1.175494345e-25 +1.17549e-24 1.175494345e-24 1.175494345e-24 +1.17549e-23 1.175494345e-23 1.175494345e-23 +1.17549e-22 1.175494345e-22 1.175494345e-22 +1.17549e-21 1.175494345e-21 1.175494345e-21 +1.17549e-20 1.175494345e-20 1.175494345e-20 +1.17549e-19 1.175494345e-19 1.175494345e-19 +1.17549e-18 1.175494345e-18 1.175494345e-18 +1.17549e-17 1.175494345e-17 1.175494345e-17 +1.17549e-16 1.175494345e-16 1.175494345e-16 +1.17549e-15 1.175494345e-15 1.175494345e-15 +1.17549e-14 1.175494345e-14 1.175494345e-14 +1.17549e-13 1.175494345e-13 1.175494345e-13 +1.17549e-12 1.175494345e-12 1.175494345e-12 +1.17549e-11 1.175494345e-11 1.175494345e-11 +1.17549e-10 1.175494345e-10 1.175494345e-10 +1.17549e-09 1.175494345e-09 1.175494345e-9 +1.17549e-08 1.175494345e-08 1.175494345e-8 +1.17549e-07 1.175494345e-07 1.175494345e-7 +1.17549e-06 1.175494345e-06 1.175494345e-6 +1.17549e-05 1.175494345e-05 1.175494345e-5 +0.000117549 0.0001175494345 1.175494345e-4 +0.00117549 0.001175494345 1.175494345e-3 +0.0117549 0.01175494345 1.175494345e-2 +0.117549 0.1175494345 1.175494345e-1 +SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1; +CAST(my_float AS DECIMAL(65,30)) my_float +0.000000000000000000000000000000 1.17549e-32 +0.000000000000000000000000000000 1.17549e-31 +0.000000000000000000000000000001 1.17549e-30 +0.000000000000000000000000000012 1.17549e-29 +0.000000000000000000000000000118 1.17549e-28 +0.000000000000000000000000001175 1.17549e-27 +0.000000000000000000000000011755 1.17549e-26 +0.000000000000000000000000117549 1.17549e-25 +0.000000000000000000000001175494 1.17549e-24 +0.000000000000000000000011754943 1.17549e-23 +0.000000000000000000000117549438 1.17549e-22 +0.000000000000000000001175494332 1.17549e-21 +0.000000000000000000011754943324 1.17549e-20 +0.000000000000000000117549434853 1.17549e-19 +0.000000000000000001175494374380 1.17549e-18 +0.000000000000000011754943743802 1.17549e-17 +0.000000000000000117549432474939 1.17549e-16 +0.000000000000001175494324749389 1.17549e-15 +0.000000000000011754943671010360 1.17549e-14 +0.000000000000117549429933840000 1.17549e-13 +0.000000000001175494380653563000 1.17549e-12 +0.000000000011754943372854760000 1.17549e-11 +0.000000000117549428524377200000 1.17549e-10 +0.000000001175494368510499000000 1.17549e-09 +0.000000011754943685104990000000 1.17549e-08 +0.000000117549433298336200000000 1.17549e-07 +0.000001175494389826781000000000 1.17549e-06 +0.000011754943443520460000000000 1.17549e-05 +0.000117549432616215200000000000 0.000117549 +0.001175494398921728000000000000 0.00117549 +0.011754943057894710000000000000 0.0117549 +0.117549434304237400000000000000 0.117549 +SELECT CAST(my_double AS DECIMAL(65,30)), my_double FROM t1; +CAST(my_double AS DECIMAL(65,30)) my_double +0.000000000000000000000000000000 1.175494345e-32 +0.000000000000000000000000000000 1.175494345e-31 +0.000000000000000000000000000001 1.175494345e-30 +0.000000000000000000000000000012 1.175494345e-29 +0.000000000000000000000000000118 1.175494345e-28 +0.000000000000000000000000001175 1.175494345e-27 +0.000000000000000000000000011755 1.175494345e-26 +0.000000000000000000000000117549 1.175494345e-25 +0.000000000000000000000001175494 1.175494345e-24 +0.000000000000000000000011754943 1.175494345e-23 +0.000000000000000000000117549435 1.175494345e-22 +0.000000000000000000001175494345 1.175494345e-21 +0.000000000000000000011754943450 1.175494345e-20 +0.000000000000000000117549434500 1.175494345e-19 +0.000000000000000001175494345000 1.175494345e-18 +0.000000000000000011754943450000 1.175494345e-17 +0.000000000000000117549434500000 1.175494345e-16 +0.000000000000001175494345000000 1.175494345e-15 +0.000000000000011754943450000000 1.175494345e-14 +0.000000000000117549434500000000 1.175494345e-13 +0.000000000001175494345000000000 1.175494345e-12 +0.000000000011754943450000000000 1.175494345e-11 +0.000000000117549434500000000000 1.175494345e-10 +0.000000001175494345000000000000 1.175494345e-09 +0.000000011754943450000000000000 1.175494345e-08 +0.000000117549434500000000000000 1.175494345e-07 +0.000001175494345000000000000000 1.175494345e-06 +0.000011754943450000000000000000 1.175494345e-05 +0.000117549434500000000000000000 0.0001175494345 +0.001175494345000000000000000000 0.001175494345 +0.011754943450000000000000000000 0.01175494345 +0.117549434500000000000000000000 0.1175494345 +SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1; +CAST(my_varchar AS DECIMAL(65,30)) my_varchar +0.000000000000000000000000000000 1.175494345e-32 +0.000000000000000000000000000000 1.175494345e-31 +0.000000000000000000000000000001 1.175494345e-30 +0.000000000000000000000000000012 1.175494345e-29 +0.000000000000000000000000000118 1.175494345e-28 +0.000000000000000000000000001175 1.175494345e-27 +0.000000000000000000000000011755 1.175494345e-26 +0.000000000000000000000000117549 1.175494345e-25 +0.000000000000000000000001175494 1.175494345e-24 +0.000000000000000000000011754943 1.175494345e-23 +0.000000000000000000000117549435 1.175494345e-22 +0.000000000000000000001175494345 1.175494345e-21 +0.000000000000000000011754943450 1.175494345e-20 +0.000000000000000000117549434500 1.175494345e-19 +0.000000000000000001175494345000 1.175494345e-18 +0.000000000000000011754943450000 1.175494345e-17 +0.000000000000000117549434500000 1.175494345e-16 +0.000000000000001175494345000000 1.175494345e-15 +0.000000000000011754943450000000 1.175494345e-14 +0.000000000000117549434500000000 1.175494345e-13 +0.000000000001175494345000000000 1.175494345e-12 +0.000000000011754943450000000000 1.175494345e-11 +0.000000000117549434500000000000 1.175494345e-10 +0.000000001175494345000000000000 1.175494345e-9 +0.000000011754943450000000000000 1.175494345e-8 +0.000000117549434500000000000000 1.175494345e-7 +0.000001175494345000000000000000 1.175494345e-6 +0.000011754943450000000000000000 1.175494345e-5 +0.000117549434500000000000000000 1.175494345e-4 +0.001175494345000000000000000000 1.175494345e-3 +0.011754943450000000000000000000 1.175494345e-2 +0.117549434500000000000000000000 1.175494345e-1 +UPDATE t1 SET my_decimal = my_float; +Warnings: +Note 1265 Data truncated for column 'my_decimal' at row 1 +Note 1265 Data truncated for column 'my_decimal' at row 2 +Note 1265 Data truncated for column 'my_decimal' at row 3 +Note 1265 Data truncated for column 'my_decimal' at row 4 +Note 1265 Data truncated for column 'my_decimal' at row 5 +Note 1265 Data truncated for column 'my_decimal' at row 6 +Note 1265 Data truncated for column 'my_decimal' at row 7 +Note 1265 Data truncated for column 'my_decimal' at row 8 +Note 1265 Data truncated for column 'my_decimal' at row 9 +Note 1265 Data truncated for column 'my_decimal' at row 10 +Note 1265 Data truncated for column 'my_decimal' at row 11 +Note 1265 Data truncated for column 'my_decimal' at row 12 +Note 1265 Data truncated for column 'my_decimal' at row 13 +Note 1265 Data truncated for column 'my_decimal' at row 14 +Note 1265 Data truncated for column 'my_decimal' at row 15 +Note 1265 Data truncated for column 'my_decimal' at row 16 +Note 1265 Data truncated for column 'my_decimal' at row 17 +Note 1265 Data truncated for column 'my_decimal' at row 19 +Note 1265 Data truncated for column 'my_decimal' at row 20 +Note 1265 Data truncated for column 'my_decimal' at row 21 +Note 1265 Data truncated for column 'my_decimal' at row 22 +Note 1265 Data truncated for column 'my_decimal' at row 23 +Note 1265 Data truncated for column 'my_decimal' at row 26 +Note 1265 Data truncated for column 'my_decimal' at row 27 +Note 1265 Data truncated for column 'my_decimal' at row 30 +Note 1265 Data truncated for column 'my_decimal' at row 31 +Note 1265 Data truncated for column 'my_decimal' at row 32 +SELECT my_decimal, my_float FROM t1; +my_decimal my_float +0.000000000000000000000000000000 1.17549e-32 +0.000000000000000000000000000000 1.17549e-31 +0.000000000000000000000000000001 1.17549e-30 +0.000000000000000000000000000012 1.17549e-29 +0.000000000000000000000000000118 1.17549e-28 +0.000000000000000000000000001175 1.17549e-27 +0.000000000000000000000000011755 1.17549e-26 +0.000000000000000000000000117549 1.17549e-25 +0.000000000000000000000001175494 1.17549e-24 +0.000000000000000000000011754943 1.17549e-23 +0.000000000000000000000117549438 1.17549e-22 +0.000000000000000000001175494332 1.17549e-21 +0.000000000000000000011754943324 1.17549e-20 +0.000000000000000000117549434853 1.17549e-19 +0.000000000000000001175494374380 1.17549e-18 +0.000000000000000011754943743802 1.17549e-17 +0.000000000000000117549432474939 1.17549e-16 +0.000000000000001175494324749389 1.17549e-15 +0.000000000000011754943671010360 1.17549e-14 +0.000000000000117549429933840000 1.17549e-13 +0.000000000001175494380653563000 1.17549e-12 +0.000000000011754943372854760000 1.17549e-11 +0.000000000117549428524377200000 1.17549e-10 +0.000000001175494368510499000000 1.17549e-09 +0.000000011754943685104990000000 1.17549e-08 +0.000000117549433298336200000000 1.17549e-07 +0.000001175494389826781000000000 1.17549e-06 +0.000011754943443520460000000000 1.17549e-05 +0.000117549432616215200000000000 0.000117549 +0.001175494398921728000000000000 0.00117549 +0.011754943057894710000000000000 0.0117549 +0.117549434304237400000000000000 0.117549 +UPDATE t1 SET my_decimal = my_double; +Warnings: +Note 1265 Data truncated for column 'my_decimal' at row 1 +Note 1265 Data truncated for column 'my_decimal' at row 2 +Note 1265 Data truncated for column 'my_decimal' at row 3 +Note 1265 Data truncated for column 'my_decimal' at row 4 +Note 1265 Data truncated for column 'my_decimal' at row 5 +Note 1265 Data truncated for column 'my_decimal' at row 6 +Note 1265 Data truncated for column 'my_decimal' at row 7 +Note 1265 Data truncated for column 'my_decimal' at row 8 +Note 1265 Data truncated for column 'my_decimal' at row 9 +Note 1265 Data truncated for column 'my_decimal' at row 10 +Note 1265 Data truncated for column 'my_decimal' at row 11 +Note 1265 Data truncated for column 'my_decimal' at row 13 +Note 1265 Data truncated for column 'my_decimal' at row 14 +Note 1265 Data truncated for column 'my_decimal' at row 16 +Note 1265 Data truncated for column 'my_decimal' at row 18 +Note 1265 Data truncated for column 'my_decimal' at row 20 +Note 1265 Data truncated for column 'my_decimal' at row 31 +SELECT my_decimal, my_double FROM t1; +my_decimal my_double +0.000000000000000000000000000000 1.175494345e-32 +0.000000000000000000000000000000 1.175494345e-31 +0.000000000000000000000000000001 1.175494345e-30 +0.000000000000000000000000000012 1.175494345e-29 +0.000000000000000000000000000118 1.175494345e-28 +0.000000000000000000000000001175 1.175494345e-27 +0.000000000000000000000000011755 1.175494345e-26 +0.000000000000000000000000117549 1.175494345e-25 +0.000000000000000000000001175494 1.175494345e-24 +0.000000000000000000000011754943 1.175494345e-23 +0.000000000000000000000117549435 1.175494345e-22 +0.000000000000000000001175494345 1.175494345e-21 +0.000000000000000000011754943450 1.175494345e-20 +0.000000000000000000117549434500 1.175494345e-19 +0.000000000000000001175494345000 1.175494345e-18 +0.000000000000000011754943450000 1.175494345e-17 +0.000000000000000117549434500000 1.175494345e-16 +0.000000000000001175494345000000 1.175494345e-15 +0.000000000000011754943450000000 1.175494345e-14 +0.000000000000117549434500000000 1.175494345e-13 +0.000000000001175494345000000000 1.175494345e-12 +0.000000000011754943450000000000 1.175494345e-11 +0.000000000117549434500000000000 1.175494345e-10 +0.000000001175494345000000000000 1.175494345e-09 +0.000000011754943450000000000000 1.175494345e-08 +0.000000117549434500000000000000 1.175494345e-07 +0.000001175494345000000000000000 1.175494345e-06 +0.000011754943450000000000000000 1.175494345e-05 +0.000117549434500000000000000000 0.0001175494345 +0.001175494345000000000000000000 0.001175494345 +0.011754943450000000000000000000 0.01175494345 +0.117549434500000000000000000000 0.1175494345 +UPDATE t1 SET my_decimal = my_varchar; +Warnings: +Note 1265 Data truncated for column 'my_decimal' at row 1 +Note 1265 Data truncated for column 'my_decimal' at row 2 +Note 1265 Data truncated for column 'my_decimal' at row 3 +Note 1265 Data truncated for column 'my_decimal' at row 4 +Note 1265 Data truncated for column 'my_decimal' at row 5 +Note 1265 Data truncated for column 'my_decimal' at row 6 +Note 1265 Data truncated for column 'my_decimal' at row 7 +Note 1265 Data truncated for column 'my_decimal' at row 8 +Note 1265 Data truncated for column 'my_decimal' at row 9 +Note 1265 Data truncated for column 'my_decimal' at row 10 +Note 1265 Data truncated for column 'my_decimal' at row 11 +SELECT my_decimal, my_varchar FROM t1; +my_decimal my_varchar +0.000000000000000000000000000000 1.175494345e-32 +0.000000000000000000000000000000 1.175494345e-31 +0.000000000000000000000000000001 1.175494345e-30 +0.000000000000000000000000000012 1.175494345e-29 +0.000000000000000000000000000118 1.175494345e-28 +0.000000000000000000000000001175 1.175494345e-27 +0.000000000000000000000000011755 1.175494345e-26 +0.000000000000000000000000117549 1.175494345e-25 +0.000000000000000000000001175494 1.175494345e-24 +0.000000000000000000000011754943 1.175494345e-23 +0.000000000000000000000117549435 1.175494345e-22 +0.000000000000000000001175494345 1.175494345e-21 +0.000000000000000000011754943450 1.175494345e-20 +0.000000000000000000117549434500 1.175494345e-19 +0.000000000000000001175494345000 1.175494345e-18 +0.000000000000000011754943450000 1.175494345e-17 +0.000000000000000117549434500000 1.175494345e-16 +0.000000000000001175494345000000 1.175494345e-15 +0.000000000000011754943450000000 1.175494345e-14 +0.000000000000117549434500000000 1.175494345e-13 +0.000000000001175494345000000000 1.175494345e-12 +0.000000000011754943450000000000 1.175494345e-11 +0.000000000117549434500000000000 1.175494345e-10 +0.000000001175494345000000000000 1.175494345e-9 +0.000000011754943450000000000000 1.175494345e-8 +0.000000117549434500000000000000 1.175494345e-7 +0.000001175494345000000000000000 1.175494345e-6 +0.000011754943450000000000000000 1.175494345e-5 +0.000117549434500000000000000000 1.175494345e-4 +0.001175494345000000000000000000 1.175494345e-3 +0.011754943450000000000000000000 1.175494345e-2 +0.117549434500000000000000000000 1.175494345e-1 +DROP TABLE t1; create table t1 (c1 decimal(64)); insert into t1 values( 89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000); diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index b31ec9a3bab..97df059c86a 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2199,10 +2199,10 @@ r_object_id users_names drop view v1, v2; drop table t1, t2; create definer=some_user@`` sql security invoker view v1 as select 1; -ERROR HY000: View definer is not fully qualified +ERROR HY000: Definer is not fully qualified create definer=some_user@localhost sql security invoker view v1 as select 1; Warnings: -Note 1449 There is not some_user@localhost registered +Note 1449 There is no 'some_user'@'localhost' registered show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` @@ -2375,3 +2375,13 @@ Warnings: Error 1146 Table 'test.t1' doesn't exist Error 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them DROP VIEW v1; +create definer = current_user() sql security invoker view v1 as select 1; +show create view v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` +drop view v1; +create definer = current_user sql security invoker view v1 as select 1; +show create view v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` +drop view v1; diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 43df5c29f92..89067ec23a2 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -13,7 +13,7 @@ create table mysqltest.t2 (a int, b int); grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; create definer=root@localhost view v1 as select * from mysqltest.t1; -ERROR HY000: You need the SUPER privilege for creation view with root@localhost definer +ERROR 42000: Access denied; you need the SUPER privilege for this operation create view v1 as select * from mysqltest.t1; alter view v1 as select * from mysqltest.t1; ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 8a28e8bbf02..1cd55926e80 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -379,6 +379,8 @@ where table_schema='test'; select index_name from information_schema.statistics where table_schema='test'; select constraint_name from information_schema.table_constraints where table_schema='test'; +show create view v2; +show create table v3; drop view v2; drop view v3; drop table t4; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 91a87e2c774..83085823729 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -658,19 +658,19 @@ select '------ Testing with illegal table names ------' as test_sequence ; --exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "t/1" 2>&1 --error 6 ---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T_1" +--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T_1" 2>&1 --error 6 ---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T%1" +--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T%1" 2>&1 --error 6 ---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T'1" +--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T'1" 2>&1 --error 6 ---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T_1" +--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T_1" 2>&1 --error 6 ---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T_" +--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T_" 2>&1 --disable_query_log select '------ Testing with illegal database names ------' as test_sequence ; @@ -882,6 +882,7 @@ DROP FUNCTION IF EXISTS bug9056_func1; DROP FUNCTION IF EXISTS bug9056_func2; DROP PROCEDURE IF EXISTS bug9056_proc1; DROP PROCEDURE IF EXISTS bug9056_proc2; +DROP PROCEDURE IF EXISTS `a'b`; --enable_warnings CREATE TABLE t1 (id int); diff --git a/mysql-test/t/rpl_drop_db.test b/mysql-test/t/rpl_drop_db.test index 61354198c83..98afc6e3d02 100644 --- a/mysql-test/t/rpl_drop_db.test +++ b/mysql-test/t/rpl_drop_db.test @@ -13,6 +13,7 @@ insert into mysqltest1.t1 values (1); select * from mysqltest1.t1 into outfile 'mysqltest1/f1.txt'; create table mysqltest1.t2 (n int); create table mysqltest1.t3 (n int); +--replace_result \\ / --error 1010 drop database mysqltest1; use mysqltest1; @@ -29,6 +30,7 @@ while ($1) } --enable_query_log +--replace_result \\ / --error 1010 drop database mysqltest1; use mysqltest1; diff --git a/mysql-test/t/rpl_sp-slave.opt b/mysql-test/t/rpl_sp-slave.opt index 709a224fd92..611ee1f33be 100644 --- a/mysql-test/t/rpl_sp-slave.opt +++ b/mysql-test/t/rpl_sp-slave.opt @@ -1 +1 @@ ---log_bin_trust_routine_creators=0 +--log_bin_trust_routine_creators=0 --slave-skip-errors=1062 diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test index e62a6c73c0a..e7a3afca9cb 100644 --- a/mysql-test/t/rpl_sp.test +++ b/mysql-test/t/rpl_sp.test @@ -1,10 +1,18 @@ # Test of replication of stored procedures (WL#2146 for MySQL 5.0) +# Modified by WL#2971. + +# Note that in the .opt files we still use the old variable name +# log-bin-trust-routine-creators so that this test checks that it's +# still accepted (this test also checks that the new name is +# accepted). The old name could be removed in 5.1 or 6.0. source include/master-slave.inc; -# First let's test replication of current_user() (that's a related thing) # we need a db != test, where we don't have automatic grants -create database if not exists mysqltest1; +--disable_warnings +drop database if exists mysqltest1; +--enable_warnings +create database mysqltest1; use mysqltest1; create table t1 (a varchar(100)); sync_slave_with_master; @@ -16,30 +24,15 @@ use mysqltest1; # (same definer, same properties...) connection master; -# cleanup ---disable_warnings -drop procedure if exists foo; -drop procedure if exists foo2; -drop procedure if exists foo3; -drop procedure if exists foo4; -drop procedure if exists bar; -drop function if exists fn1; ---enable_warnings delimiter |; ---error 1418 # not deterministic -create procedure foo() -begin - declare b int; - set b = 8; - insert into t1 values (b); - insert into t1 values (unix_timestamp()); -end| ---replace_column 2 # 5 # -show binlog events from 98| # check that not there +# Stored procedures don't have the limitations that functions have +# regarding binlogging: it's ok to create a procedure as not +# deterministic and updating data, while it's not ok to create such a +# function. We test this. -create procedure foo() deterministic +create procedure foo() begin declare b int; set b = 8; @@ -54,38 +47,29 @@ delimiter ;| --replace_column 13 # 14 # select * from mysql.proc where name='foo' and db='mysqltest1'; sync_slave_with_master; +# You will notice in the result that the definer does not match what +# it is on master, it is a known bug on which Alik is working --replace_result localhost.localdomain localhost 127.0.0.1 localhost --replace_column 13 # 14 # select * from mysql.proc where name='foo' and db='mysqltest1'; -# Now when we call it, does the CALL() get into binlog, -# or the substatements? connection master; # see if timestamp used in SP on slave is same as on master set timestamp=1000000000; call foo(); ---replace_column 2 # 5 # -show binlog events from 308; select * from t1; sync_slave_with_master; select * from t1; -# Now a SP which is supposed to not update tables (CALL should not be -# binlogged) as it's "read sql data", so should not give error even if -# non-deterministic. +# Now a SP which is not updating tables connection master; delete from t1; create procedure foo2() - not deterministic - reads sql data select * from mysqltest1.t1; call foo2(); -# verify CALL is not in binlog ---replace_column 2 # 5 # -show binlog events from 518; ---error 1418 +# check that this is allowed (it's not for functions): alter procedure foo2 contains sql; # SP with definer's right @@ -106,15 +90,7 @@ grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1; connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,); connection con1; ---error 1419 # only full-global-privs user can create a routine -create procedure foo4() - deterministic - insert into t1 values (10); - -connection master; -set global log_bin_trust_routine_creators=1; -connection con1; - +# this routine will fail in the second INSERT because of privileges delimiter |; create procedure foo4() deterministic @@ -128,29 +104,22 @@ delimiter ;| # I add ,0 so that it does not print the error in the test output, # because this error is hostname-dependent --error 1142,0 -call foo4(); # invoker has no INSERT grant on table => failure -show warnings; +call foo4(); # invoker has no INSERT grant on table t1 => failure connection master; call foo3(); # success (definer == root) show warnings; ---replace_result localhost.localdomain localhost 127.0.0.1 localhost --error 1142,0 call foo4(); # definer's rights => failure -show warnings; # we test replication of ALTER PROCEDURE alter procedure foo4 sql security invoker; call foo4(); # invoker's rights => success show warnings; -# Check that only successful CALLs are in binlog ---replace_column 2 # 5 # -show binlog events from 990; - -# Note that half-failed CALLs are not in binlog, which is a known -# bug. If we compare t2 on master and slave we see they differ: +# Note that half-failed procedure calls are ok with binlogging; +# if we compare t2 on master and slave we see they are identical: select * from t1; select * from t2; @@ -158,6 +127,30 @@ sync_slave_with_master; select * from t1; select * from t2; +# Let's check another failing-in-the-middle procedure +connection master; +delete from t2; +alter table t2 add unique (a); + +drop procedure foo4; +delimiter |; +create procedure foo4() + deterministic + begin + insert into t2 values(20),(20); + end| + +delimiter ;| + +--error 1062 +call foo4(); +show warnings; + +select * from t2; +sync_slave_with_master; +# check that this failed-in-the-middle replicated right: +select * from t2; + # Test of DROP PROCEDURE --replace_result localhost.localdomain localhost 127.0.0.1 localhost @@ -177,6 +170,14 @@ drop procedure foo2; drop procedure foo3; delimiter |; +# check that needs "deterministic" +--error 1418 +create function fn1(x int) + returns int +begin + insert into t1 values (x); + return x+2; +end| create function fn1(x int) returns int deterministic @@ -202,15 +203,69 @@ drop function fn1; create function fn1() returns int - deterministic + no sql begin return unix_timestamp(); end| + delimiter ;| +# check that needs "deterministic" +--error 1418 +alter function fn1 contains sql; + delete from t1; set timestamp=1000000000; insert into t1 values(fn1()); +connection con1; + +delimiter |; +--error 1419 # only full-global-privs user can create a function +create function fn2() + returns int + no sql +begin + return unix_timestamp(); +end| +delimiter ;| +connection master; +# test old variable name: +set global log_bin_trust_routine_creators=1; +# now use new name: +set global log_bin_trust_function_creators=0; +set global log_bin_trust_function_creators=1; +# slave needs it too otherwise will not execute what master allowed: +connection slave; +set global log_bin_trust_function_creators=1; + +connection con1; + +delimiter |; +create function fn2() + returns int + no sql +begin + return unix_timestamp(); +end| +delimiter ;| + +connection master; + +# Now a function which is supposed to not update tables +# as it's "reads sql data", so should not give error even if +# non-deterministic. + +delimiter |; +create function fn3() + returns int + not deterministic + reads sql data +begin + return 0; +end| +delimiter ;| + +select fn3(); --replace_result localhost.localdomain localhost 127.0.0.1 localhost --replace_column 13 # 14 # select * from mysql.proc where db='mysqltest1'; @@ -223,18 +278,43 @@ select * from t1; --replace_column 13 # 14 # select * from mysql.proc where db='mysqltest1'; -# And now triggers +# Let's check a failing-in-the-middle function +connection master; +delete from t2; +alter table t2 add unique (a); + +drop function fn1; + +delimiter |; +create function fn1() + returns int +begin + insert into t2 values(20),(20); + return 10; +end| + +delimiter ;| + +# Because of BUG#14769 the following statement requires that we start +# slave with --slave-skip-errors=1062. When that bug is fixed, that +# option can be removed. + +--error 1062 +select fn1(); + +select * from t2; +sync_slave_with_master; + +# check that this failed-in-the-middle replicated right: +select * from t2; + +# ********************** PART 3 : TRIGGERS *************** connection con1; --error 1227 create trigger trg before insert on t1 for each row set new.a= 10; connection master; -# fn1() above uses timestamps, so in !ps-protocol, the timezone will be -# binlogged, but in --ps-protocol it will not be (BUG#9359) so -# the binlog offsets get shifted which spoils SHOW BINLOG EVENTS. -# To be immune, we take a new binlog. -flush logs; delete from t1; # TODO: when triggers can contain an update, test that this update # does not go into binlog. @@ -253,7 +333,7 @@ drop trigger trg; insert into t1 values (1); select * from t1; --replace_column 2 # 5 # -show binlog events in 'master-bin.000002' from 98; +show binlog events in 'master-bin.000001' from 98; sync_slave_with_master; select * from t1; diff --git a/mysql-test/t/rpl_trigger.test b/mysql-test/t/rpl_trigger.test index 715222f0314..bf2836b6049 100644 --- a/mysql-test/t/rpl_trigger.test +++ b/mysql-test/t/rpl_trigger.test @@ -87,12 +87,35 @@ insert into t1 set a = now(); select a=b && a=c from t1; let $time=`select a from t1`; +# Check that definer attribute is replicated properly: +# - dump definers on the master; +# - wait for the slave to synchronize with the master; +# - dump definers on the slave; + +SELECT routine_name, definer +FROM information_schema.routines; + +SELECT trigger_name, definer +FROM information_schema.triggers; + save_master_pos; connection slave; sync_with_master; --disable_query_log select "--- On slave --" as ""; --enable_query_log + +# XXX: Definers of stored procedures and functions are not replicated. WL#2897 +# (Complete definer support in the stored routines) addresses this issue. So, +# the result file is expected to be changed after implementation of this WL +# item. + +SELECT routine_name, definer +FROM information_schema.routines; + +SELECT trigger_name, definer +FROM information_schema.triggers; + select a=b && a=c from t1; --disable_query_log eval select a='$time' as 'test' from t1; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index e7e6d899a66..33a3ff578f8 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2729,3 +2729,23 @@ select * from t1 where f2 >= '2005-09-3a'; select * from t1 where f2 <= '2005-09-31'; select * from t1 where f2 <= '2005-09-3a'; drop table t1; + +# +# Bug ##14662 ORDER BY on column of a view, with an alias of the same +# column causes ambiguous +# + +create table t1 (f1 int, f2 int); +insert into t1 values (1, 30), (2, 20), (3, 10); +create algorithm=merge view v1 as select f1, f2 from t1; +create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1; +create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1; +select t1.f1 as x1, f1 from t1 order by t1.f1; +select v1.f1 as x1, f1 from v1 order by v1.f1; +select v2.f1 as x1, f1 from v2 order by v2.f1; +select v3.f1 as x1, f1 from v3 order by v3.f1; +select f1, f2, v1.f1 as x1 from v1 order by v1.f1; +select f1, f2, v2.f1 as x1 from v2 order by v2.f1; +select f1, f2, v3.f1 as x1 from v3 order by v3.f1; +drop table t1; +drop view v1, v2, v3; diff --git a/mysql-test/t/skip_grants.test b/mysql-test/t/skip_grants.test index 7a729f98661..16b0fbc4d25 100644 --- a/mysql-test/t/skip_grants.test +++ b/mysql-test/t/skip_grants.test @@ -9,7 +9,7 @@ use test; # test that we can create VIEW if privileges check switched off # create table t1 (field1 INT); --- error ER_NO_VIEW_USER +-- error ER_MALFORMED_DEFINER CREATE VIEW v1 AS SELECT field1 FROM t1; drop table t1; diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index a07cc93ad68..4bfc4d17588 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -183,3 +183,57 @@ group by country; drop table t1; +# +# BUG#14342: wrong placement of subquery internals in complex queries +# +CREATE TABLE `t1` ( + `t3_id` int NOT NULL, + `t1_id` int NOT NULL, + PRIMARY KEY (`t1_id`) +); +CREATE TABLE `t2` ( + `t2_id` int NOT NULL, + `t1_id` int NOT NULL, + `b` int NOT NULL, + PRIMARY KEY (`t2_id`), + UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`) +) ENGINE=InnoDB; +CREATE TABLE `t3` ( + `t3_id` int NOT NULL +); +INSERT INTO `t3` VALUES (3); +select + (SELECT rs.t2_id + FROM t2 rs + WHERE rs.t1_id= + (SELECT lt.t1_id + FROM t1 lt + WHERE lt.t3_id=a.t3_id) + ORDER BY b DESC LIMIT 1) +from t3 AS a; +# repeat above query in SP +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings +delimiter //; +create procedure p1() +begin + declare done int default 3; + repeat + select + (SELECT rs.t2_id + FROM t2 rs + WHERE rs.t1_id= + (SELECT lt.t1_id + FROM t1 lt + WHERE lt.t3_id=a.t3_id) + ORDER BY b DESC LIMIT 1) as x + from t3 AS a; + set done= done-1; + until done <= 0 end repeat; +end// +delimiter ;// +call p1(); +call p1(); +call p1(); +drop tables t1,t2,t3; diff --git a/mysql-test/t/trigger-compat.test b/mysql-test/t/trigger-compat.test new file mode 100644 index 00000000000..ace18639172 --- /dev/null +++ b/mysql-test/t/trigger-compat.test @@ -0,0 +1,83 @@ +# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not +# supported in embedded server. So, this test should not be run on embedded +# server. + +-- source include/not_embedded.inc + +########################################################################### +# +# Tests for WL#2818: +# - Check that triggers created w/o DEFINER information work well: +# - create the first trigger; +# - manually remove definer information from corresponding TRG file; +# - create the second trigger (the first trigger will be reloaded; check +# that we receive a warning); +# - check that the triggers loaded correctly; +# +########################################################################### + +# +# Prepare environment. +# + +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +--enable_warnings + +CREATE DATABASE mysqltest_db1; + +CREATE USER mysqltest_dfn@localhost; +CREATE USER mysqltest_inv@localhost; + +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; +GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +# +# Create a table and the first trigger. +# + +--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +CREATE TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); + +CREATE TRIGGER wl2818_trg1 BEFORE INSERT ON t1 + FOR EACH ROW + INSERT INTO t2 VALUES(CURRENT_USER()); + +# +# Remove definers from TRG file. +# + +--echo +--echo ---> patching t1.TRG... + +--exec grep --text -v 'definers=' $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG > $MYSQL_TEST_DIR/var/tmp/t1.TRG +--exec mv $MYSQL_TEST_DIR/var/tmp/t1.TRG $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG + +# +# Create a new trigger. +# + +--echo + +CREATE TRIGGER wl2818_trg2 AFTER INSERT ON t1 + FOR EACH ROW + INSERT INTO t2 VALUES(CURRENT_USER()); + +--echo + +SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; + +--echo + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; diff --git a/mysql-test/t/trigger-grant.test b/mysql-test/t/trigger-grant.test new file mode 100644 index 00000000000..c058816ee75 --- /dev/null +++ b/mysql-test/t/trigger-grant.test @@ -0,0 +1,475 @@ +# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not +# supported in embedded server. So, this test should not be run on embedded +# server. + +-- source include/not_embedded.inc + +########################################################################### +# +# Tests for WL#2818: +# - Check that triggers are executed under the authorization of the definer. +# - Check that if trigger contains NEW/OLD variables, the definer must have +# SELECT privilege on the subject table. +# - Check DEFINER clause of CREATE TRIGGER statement; +# - Check that SUPER privilege required to create a trigger with different +# definer. +# - Check that if the user specified as DEFINER does not exist, a warning +# is emitted. +# - Check that the definer of a trigger does not exist, the trigger will +# not be activated. +# - Check that SHOW TRIGGERS statement provides "Definer" column. +# +# Let's also check that user name part of definer can contain '@' symbol (to +# check that triggers are not affected by BUG#13310 "incorrect user parsing +# by SP"). +# +########################################################################### + +# +# Prepare environment. +# + +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +--enable_warnings + +CREATE DATABASE mysqltest_db1; + +CREATE USER mysqltest_dfn@localhost; +CREATE USER mysqltest_inv@localhost; + +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; +GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +# +# Check that triggers are executed under the authorization of the definer: +# - create two tables under "definer"; +# - grant all privileges on the test db to "definer"; +# - grant all privileges on the first table to "invoker"; +# - grant only select privilege on the second table to "invoker"; +# - create a trigger, which inserts a row into the second table after +# inserting into the first table. +# - insert a row into the first table under "invoker". A row also should be +# inserted into the second table. +# + +--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +CREATE TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); + +CREATE TRIGGER trg1 AFTER INSERT ON t1 + FOR EACH ROW + INSERT INTO t2 VALUES(CURRENT_USER()); + +--connection default +--echo +--echo ---> connection: default + +# Setup definer's privileges. + +GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; +GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; + +# Setup invoker's privileges. + +GRANT ALL PRIVILEGES ON mysqltest_db1.t1 + TO 'mysqltest_inv'@localhost; + +GRANT SELECT ON mysqltest_db1.t2 + TO 'mysqltest_inv'@localhost; + +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +use mysqltest_db1; + +INSERT INTO t1 VALUES(1); + +SELECT * FROM t1; +SELECT * FROM t2; + +--connect (wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1) +--connection wl2818_invoker_con +--echo +--echo ---> connection: wl2818_invoker_con + +use mysqltest_db1; + +INSERT INTO t1 VALUES(2); + +SELECT * FROM t1; +SELECT * FROM t2; + +# +# Check that if definer lost some privilege required to execute (activate) a +# trigger, the trigger will not be activated: +# - create a trigger on insert into the first table, which will insert a row +# into the second table; +# - revoke INSERT privilege on the second table from the definer; +# - insert a row into the first table; +# - check that an error has been risen; +# - check that no row has been inserted into the second table; +# + +--connection default +--echo +--echo ---> connection: default + +use mysqltest_db1; + +REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; + +--connection wl2818_invoker_con +--echo +--echo ---> connection: wl2818_invoker_con + +use mysqltest_db1; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(3); + +SELECT * FROM t1; +SELECT * FROM t2; + +# +# Check that if trigger contains NEW/OLD variables, the definer must have +# SELECT/UPDATE privilege on the subject table: +# - drop the trigger; +# - create a new trigger, which will use NEW variable; +# - create another new trigger, which will use OLD variable; +# - revoke SELECT/UPDATE privilege on the first table from "definer"; +# - insert a row into the first table; +# - analyze error code; +# + +# +# SELECT privilege. +# + +--connection default +--echo +--echo ---> connection: default + +use mysqltest_db1; + +REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; + +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +use mysqltest_db1; + +DROP TRIGGER trg1; + +SET @new_sum = 0; +SET @old_sum = 0; + +# INSERT INTO statement; BEFORE timing + +--echo ---> INSERT INTO statement; BEFORE timing + +CREATE TRIGGER trg1 BEFORE INSERT ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(4); + +# INSERT INTO statement; AFTER timing + +--echo ---> INSERT INTO statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER INSERT ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(5); + +# UPDATE statement; BEFORE timing + +--echo ---> UPDATE statement; BEFORE timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 BEFORE UPDATE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE t1 SET num_value = 10; + +# UPDATE statement; AFTER timing + +--echo ---> UPDATE statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER UPDATE ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE t1 SET num_value = 20; + +# DELETE statement; BEFORE timing + +--echo ---> DELETE statement; BEFORE timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 BEFORE DELETE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +DELETE FROM t1; + +# DELETE statement; AFTER timing + +--echo ---> DELETE statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER DELETE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +DELETE FROM t1; + +# +# UPDATE privilege +# +# NOTE: At the moment, UPDATE privilege is required if the trigger contains +# NEW/OLD variables, whenever the trigger modifies them or not. Moreover, +# UPDATE privilege is checked for whole table, not for individual columns. +# +# The following test cases should be changed when full support of UPDATE +# privilege will be done. +# + +--connection default +--echo +--echo ---> connection: default + +use mysqltest_db1; + +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; +REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; + +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +use mysqltest_db1; + +DROP TRIGGER trg1; + +SET @new_sum = 0; +SET @old_sum = 0; + +# INSERT INTO statement; BEFORE timing + +--echo ---> INSERT INTO statement; BEFORE timing + +CREATE TRIGGER trg1 BEFORE INSERT ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(4); + +# INSERT INTO statement; AFTER timing + +--echo ---> INSERT INTO statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER INSERT ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(5); + +# UPDATE statement; BEFORE timing + +--echo ---> UPDATE statement; BEFORE timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 BEFORE UPDATE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE t1 SET num_value = 10; + +# UPDATE statement; AFTER timing + +--echo ---> UPDATE statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER UPDATE ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE t1 SET num_value = 20; + +# DELETE statement; BEFORE timing + +--echo ---> DELETE statement; BEFORE timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 BEFORE DELETE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +DELETE FROM t1; + +# DELETE statement; AFTER timing + +--echo ---> DELETE statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER DELETE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +DELETE FROM t1; + +# +# Check DEFINER clause of CREATE TRIGGER statement. +# +# NOTE: there is no dedicated TRIGGER privilege for CREATE TRIGGER statement. +# SUPER privilege is used instead. I.e., if one invokes CREATE TRIGGER, it should +# have SUPER privilege, so this test is meaningless right now. +# +# - Check that SUPER privilege required to create a trigger with different +# definer: +# - try to create a trigger with DEFINER="definer@localhost" under +# "invoker"; +# - analyze error code; +# - Check that if the user specified as DEFINER does not exist, a warning is +# emitted: +# - create a trigger with DEFINER="non_existent_user@localhost" from +# "definer"; +# - check that a warning emitted; +# - Check that the definer of a trigger does not exist, the trigger will not +# be activated: +# - activate just created trigger; +# - check error code; +# + +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +use mysqltest_db1; + +DROP TRIGGER trg1; + +# Check that SUPER is required to specify different DEFINER. +# NOTE: meaningless at the moment + +CREATE DEFINER='mysqltest_inv'@'localhost' + TRIGGER trg1 BEFORE INSERT ON t1 + FOR EACH ROW + SET @new_sum = 0; + +# Create with non-existent user. + +CREATE DEFINER='mysqltest_nonexs'@'localhost' + TRIGGER trg2 AFTER INSERT ON t1 + FOR EACH ROW + SET @new_sum = 0; + +# Check that trg2 will not be activated. + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(6); + +# +# Check that SHOW TRIGGERS statement provides "Definer" column. +# + +SHOW TRIGGERS; + +# +# Check that weird definer values do not break functionality. I.e. check the +# following definer values: +# - ''; +# - '@'; +# - '@abc@def@@'; +# - '@hostname'; +# - '@abc@def@@@hostname'; +# + +DROP TRIGGER trg1; +DROP TRIGGER trg2; + +CREATE TRIGGER trg1 BEFORE INSERT ON t1 + FOR EACH ROW + SET @a = 1; + +CREATE TRIGGER trg2 AFTER INSERT ON t1 + FOR EACH ROW + SET @a = 2; + +CREATE TRIGGER trg3 BEFORE UPDATE ON t1 + FOR EACH ROW + SET @a = 3; + +CREATE TRIGGER trg4 AFTER UPDATE ON t1 + FOR EACH ROW + SET @a = 4; + +CREATE TRIGGER trg5 BEFORE DELETE ON t1 + FOR EACH ROW + SET @a = 5; + +--exec egrep --text -v '^definers=' $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG > $MYSQL_TEST_DIR/var/tmp/t1.TRG +--exec echo "definers='' '@' '@abc@def@@' '@hostname' '@abcdef@@@hostname'" >> $MYSQL_TEST_DIR/var/tmp/t1.TRG +--exec mv $MYSQL_TEST_DIR/var/tmp/t1.TRG $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG + +--echo + +SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; + +--echo + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; + +# +# Cleanup +# + +--connection default +--echo +--echo ---> connection: default + +DROP USER mysqltest_dfn@localhost; +DROP USER mysqltest_inv@localhost; + +DROP DATABASE mysqltest_db1; diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index 93a1a1afa45..ad71ffa02e5 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1052,6 +1052,41 @@ select cast(@non_existing_user_var/2 as DECIMAL); create table t (d decimal(0,10)); # +# Bug #14268 (bad FLOAT->DECIMAL conversion) +# + +CREATE TABLE t1 ( + my_float FLOAT, + my_double DOUBLE, + my_varchar VARCHAR(50), + my_decimal DECIMAL(65,30) +); +SHOW CREATE TABLE t1; + +let $max_power= 32; +while ($max_power) +{ + eval INSERT INTO t1 SET my_float = 1.175494345e-$max_power, + my_double = 1.175494345e-$max_power, + my_varchar = '1.175494345e-$max_power'; + dec $max_power; +} +SELECT my_float, my_double, my_varchar FROM t1; + +SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1; +SELECT CAST(my_double AS DECIMAL(65,30)), my_double FROM t1; +SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1; + +UPDATE t1 SET my_decimal = my_float; +SELECT my_decimal, my_float FROM t1; +UPDATE t1 SET my_decimal = my_double; +SELECT my_decimal, my_double FROM t1; +UPDATE t1 SET my_decimal = my_varchar; +SELECT my_decimal, my_varchar FROM t1; + +DROP TABLE t1; + +# # Bug #13573 (Wrong data inserted for too big values) # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 4e287375e5d..5addcd2570d 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2081,7 +2081,7 @@ drop table t1, t2; # # DEFINER information check # --- error ER_NO_VIEW_USER +-- error ER_MALFORMED_DEFINER create definer=some_user@`` sql security invoker view v1 as select 1; create definer=some_user@localhost sql security invoker view v1 as select 1; show create view v1; @@ -2240,3 +2240,16 @@ DROP TABLE t1; OPTIMIZE TABLE v1; DROP VIEW v1; + + +# +# BUG#14719: Views DEFINER grammar is incorrect +# + +create definer = current_user() sql security invoker view v1 as select 1; +show create view v1; +drop view v1; + +create definer = current_user sql security invoker view v1 as select 1; +show create view v1; +drop view v1; diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 7f0cb6d9406..b4f367c2065 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -24,7 +24,7 @@ grant create view,select on test.* to mysqltest_1@localhost; connect (user1,localhost,mysqltest_1,,test); connection user1; --- error ER_VIEW_OTHER_USER +-- error ER_SPECIFIC_ACCESS_DENIED create definer=root@localhost view v1 as select * from mysqltest.t1; create view v1 as select * from mysqltest.t1; # try to modify view without DROP privilege on it diff --git a/mysys/my_create.c b/mysys/my_create.c index 5fa97a9ca78..a85417c7701 100644 --- a/mysys/my_create.c +++ b/mysys/my_create.c @@ -47,13 +47,16 @@ File my_create(const char *FileName, int CreateFlags, int access_flags, #elif defined(VMS) fd = open((my_string) FileName, access_flags | O_CREAT, 0, "ctx=stm","ctx=bin"); -#elif defined(MSDOS) || defined(__WIN__) || defined(__EMX__) || defined(OS2) +#elif defined(MSDOS) || defined(__EMX__) || defined(OS2) if (access_flags & O_SHARE) fd = sopen((my_string) FileName, access_flags | O_CREAT | O_BINARY, SH_DENYNO, MY_S_IREAD | MY_S_IWRITE); else fd = open((my_string) FileName, access_flags | O_CREAT | O_BINARY, MY_S_IREAD | MY_S_IWRITE); +#elif defined(__WIN__) + fd= my_sopen((my_string) FileName, access_flags | O_CREAT | O_BINARY, + SH_DENYNO, MY_S_IREAD | MY_S_IWRITE); #else fd = open(FileName, access_flags); #endif diff --git a/mysys/my_open.c b/mysys/my_open.c index 69d63c49554..baca97450b7 100644 --- a/mysys/my_open.c +++ b/mysys/my_open.c @@ -56,12 +56,18 @@ File my_open(const char *FileName, int Flags, myf MyFlags) DBUG_RETURN(my_register_filename(-1, FileName, FILE_BY_OPEN, EE_FILENOTFOUND, MyFlags)); } +#ifndef __WIN__ if (Flags & O_SHARE) fd = sopen((my_string) FileName, (Flags & ~O_SHARE) | O_BINARY, SH_DENYNO, MY_S_IREAD | MY_S_IWRITE); else fd = open((my_string) FileName, Flags | O_BINARY, MY_S_IREAD | MY_S_IWRITE); +#else + fd= my_sopen((my_string) FileName, (Flags & ~O_SHARE) | O_BINARY, SH_DENYNO, + MY_S_IREAD | MY_S_IWRITE); +#endif + #elif !defined(NO_OPEN_3) fd = open(FileName, Flags, my_umask); /* Normal unix */ #else @@ -167,3 +173,181 @@ File my_register_filename(File fd, const char *FileName, enum file_type FileName, my_errno); return(fd); } + +#ifdef __WIN__ + +extern void __cdecl _dosmaperr(unsigned long); + +/* + Open a file with sharing. Similar to _sopen() from libc, but allows managing + share delete on win32 + + SYNOPSIS + my_sopen() + path fully qualified file name + oflag operation flags + shflag share flag + pmode permission flags + + RETURN VALUE + File descriptor of opened file if success + -1 and sets errno if fails. +*/ + +File my_sopen(const char *path, int oflag, int shflag, int pmode) +{ + int fh; /* handle of opened file */ + int mask; + HANDLE osfh; /* OS handle of opened file */ + DWORD fileaccess; /* OS file access (requested) */ + DWORD fileshare; /* OS file sharing mode */ + DWORD filecreate; /* OS method of opening/creating */ + DWORD fileattrib; /* OS file attribute flags */ + SECURITY_ATTRIBUTES SecurityAttributes; + + SecurityAttributes.nLength= sizeof(SecurityAttributes); + SecurityAttributes.lpSecurityDescriptor= NULL; + SecurityAttributes.bInheritHandle= !(oflag & _O_NOINHERIT); + + /* + * decode the access flags + */ + switch (oflag & (_O_RDONLY | _O_WRONLY | _O_RDWR)) { + case _O_RDONLY: /* read access */ + fileaccess= GENERIC_READ; + break; + case _O_WRONLY: /* write access */ + fileaccess= GENERIC_WRITE; + break; + case _O_RDWR: /* read and write access */ + fileaccess= GENERIC_READ | GENERIC_WRITE; + break; + default: /* error, bad oflag */ + errno= EINVAL; + _doserrno= 0L; /* not an OS error */ + return -1; + } + + /* + * decode sharing flags + */ + switch (shflag) { + case _SH_DENYRW: /* exclusive access except delete */ + fileshare= FILE_SHARE_DELETE; + break; + case _SH_DENYWR: /* share read and delete access */ + fileshare= FILE_SHARE_READ | FILE_SHARE_DELETE; + break; + case _SH_DENYRD: /* share write and delete access */ + fileshare= FILE_SHARE_WRITE | FILE_SHARE_DELETE; + break; + case _SH_DENYNO: /* share read, write and delete access */ + fileshare= FILE_SHARE_READ | FILE_SHARE_WRITE | FILE_SHARE_DELETE; + break; + case _SH_DENYRWD: /* exclusive access */ + fileshare= 0L; + break; + case _SH_DENYWRD: /* share read access */ + fileshare= FILE_SHARE_READ; + break; + case _SH_DENYRDD: /* share write access */ + fileshare= FILE_SHARE_WRITE; + break; + case _SH_DENYDEL: /* share read and write access */ + fileshare= FILE_SHARE_READ | FILE_SHARE_WRITE; + break; + default: /* error, bad shflag */ + errno= EINVAL; + _doserrno= 0L; /* not an OS error */ + return -1; + } + + /* + * decode open/create method flags + */ + switch (oflag & (_O_CREAT | _O_EXCL | _O_TRUNC)) { + case 0: + case _O_EXCL: // ignore EXCL w/o CREAT + filecreate= OPEN_EXISTING; + break; + + case _O_CREAT: + filecreate= OPEN_ALWAYS; + break; + + case _O_CREAT | _O_EXCL: + case _O_CREAT | _O_TRUNC | _O_EXCL: + filecreate= CREATE_NEW; + break; + + case _O_TRUNC: + case _O_TRUNC | _O_EXCL: // ignore EXCL w/o CREAT + filecreate= TRUNCATE_EXISTING; + break; + + case _O_CREAT | _O_TRUNC: + filecreate= CREATE_ALWAYS; + break; + + default: + // this can't happen ... all cases are covered + errno= EINVAL; + _doserrno= 0L; + return -1; + } + + /* + * decode file attribute flags if _O_CREAT was specified + */ + fileattrib= FILE_ATTRIBUTE_NORMAL; /* default */ + if (oflag & _O_CREAT) + { + _umask((mask= _umask(0))); + + if (!((pmode & ~mask) & _S_IWRITE)) + fileattrib= FILE_ATTRIBUTE_READONLY; + } + + /* + * Set temporary file (delete-on-close) attribute if requested. + */ + if (oflag & _O_TEMPORARY) + { + fileattrib|= FILE_FLAG_DELETE_ON_CLOSE; + fileaccess|= DELETE; + } + + /* + * Set temporary file (delay-flush-to-disk) attribute if requested. + */ + if (oflag & _O_SHORT_LIVED) + fileattrib|= FILE_ATTRIBUTE_TEMPORARY; + + /* + * Set sequential or random access attribute if requested. + */ + if (oflag & _O_SEQUENTIAL) + fileattrib|= FILE_FLAG_SEQUENTIAL_SCAN; + else if (oflag & _O_RANDOM) + fileattrib|= FILE_FLAG_RANDOM_ACCESS; + + /* + * try to open/create the file + */ + if ((osfh= CreateFile(path, fileaccess, fileshare, &SecurityAttributes, + filecreate, fileattrib, NULL)) == (HANDLE)0xffffffff) + { + /* + * OS call to open/create file failed! map the error, release + * the lock, and return -1. note that it's not necessary to + * call _free_osfhnd (it hasn't been used yet). + */ + _dosmaperr(GetLastError()); /* map error */ + return -1; /* return error to caller */ + } + + fh= _open_osfhandle((long)osfh, oflag & (_O_APPEND | _O_RDONLY | _O_TEXT)); + + return fh; /* return handle */ +} +#endif /* __WIN__ */ diff --git a/scripts/mysql_fix_privilege_tables.sql b/scripts/mysql_fix_privilege_tables.sql index d1b0c35266e..ed0f9a6c68f 100644 --- a/scripts/mysql_fix_privilege_tables.sql +++ b/scripts/mysql_fix_privilege_tables.sql @@ -29,7 +29,7 @@ ALTER TABLE user add Grant_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,a ALTER TABLE host add Grant_priv enum('N','Y') NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; ALTER TABLE db add Grant_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; ---- Fix privileges for old tables +-- Fix privileges for old tables UPDATE user SET Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; UPDATE db SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; UPDATE host SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; diff --git a/server-tools/instance-manager/priv.cc b/server-tools/instance-manager/priv.cc index a5040aa2e83..e39c12f4ebb 100644 --- a/server-tools/instance-manager/priv.cc +++ b/server-tools/instance-manager/priv.cc @@ -76,6 +76,9 @@ int set_stacksize_n_create_thread(pthread_t *thread, pthread_attr_t *attr, int rc= 0; #ifndef __WIN__ +#ifndef PTHREAD_STACK_MIN +#define PTHREAD_STACK_MIN 32768 +#endif /* Set stack size to be safe on the platforms with too small default thread stack. diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc index 61af1afb2be..556186d5584 100644 --- a/sql/ha_innodb.cc +++ b/sql/ha_innodb.cc @@ -3009,8 +3009,8 @@ ha_innobase::store_key_val_for_row( if (key_part->length > 0 && cs->mbmaxlen > 1) { len = (ulint) cs->cset->well_formed_len(cs, - src_start, - src_start + key_part->length, + (const char *) src_start, + (const char *) src_start + key_part->length, key_part->length / cs->mbmaxlen, &error); } else { diff --git a/sql/handler.h b/sql/handler.h index e317f95b990..be188f7cacd 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -401,8 +401,9 @@ struct show_table_alias_st { /* Possible flags of a handlerton */ #define HTON_NO_FLAGS 0 #define HTON_CLOSE_CURSORS_AT_COMMIT (1 << 0) -#define HTON_ALTER_NOT_SUPPORTED (1 << 1) -#define HTON_CAN_RECREATE (1 << 2) +#define HTON_ALTER_NOT_SUPPORTED (1 << 1) //Engine does not support alter +#define HTON_CAN_RECREATE (1 << 2) //Delete all is used fro truncate +#define HTON_HIDDEN (1 << 3) //Engine does not appear in lists typedef struct st_thd_trans { diff --git a/sql/item.cc b/sql/item.cc index 1850b7d05c3..1767f9d97c1 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -4938,8 +4938,7 @@ bool Item_direct_view_ref::fix_fields(THD *thd, Item **reference) } /* - Compare view field's name with item's name before call to referenced - item's eq() + Compare two view column references for equality. SYNOPSIS Item_direct_view_ref::eq() @@ -4947,12 +4946,13 @@ bool Item_direct_view_ref::fix_fields(THD *thd, Item **reference) binary_cmp make binary comparison DESCRIPTION - Consider queries: - create view v1 as select t1.f1 as f2, t1.f2 as f1 from t1; - select * from v1 order by f1; - In order to choose right field for sorting we need to compare - given item's name (f1) to view field's name prior to calling - referenced item's eq(). + A view column reference is considered equal to another column + reference if the second one is a view column and if both column + references point to the same field. For views 'same field' means + the same Item_field object in the view translation table, where + the view translation table contains all result columns of the + view. This definition ensures that view columns are resolved + in the same manner as table columns. RETURN TRUE Referenced item is equal to given item @@ -4962,9 +4962,18 @@ bool Item_direct_view_ref::fix_fields(THD *thd, Item **reference) bool Item_direct_view_ref::eq(const Item *item, bool binary_cmp) const { - Item *it= ((Item *) item)->real_item(); - return (!it->name || !my_strcasecmp(system_charset_info, it->name, - field_name)) && ref && (*ref)->real_item()->eq(it, binary_cmp); + if (item->type() == REF_ITEM) + { + Item_ref *item_ref= (Item_ref*) item; + if (item_ref->ref_type() == VIEW_REF) + { + Item *item_ref_ref= *(item_ref->ref); + DBUG_ASSERT((*ref)->type() == FIELD_ITEM && + (item_ref_ref->type() == FIELD_ITEM)); + return (*ref == item_ref_ref); + } + } + return FALSE; } void Item_null_helper::print(String *str) diff --git a/sql/item_func.cc b/sql/item_func.cc index f467981540b..7598865fbb7 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -4775,12 +4775,6 @@ Item_func_sp::execute(Item **itp) res= m_sp->execute_function(thd, args, arg_count, itp); thd->restore_sub_statement_state(&statement_state); - if (res && mysql_bin_log.is_open() && - (m_sp->m_chistics->daccess == SP_CONTAINS_SQL || - m_sp->m_chistics->daccess == SP_MODIFIES_SQL_DATA)) - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_FAILED_ROUTINE_BREAK_BINLOG, - ER(ER_FAILED_ROUTINE_BREAK_BINLOG)); #ifndef NO_EMBEDDED_ACCESS_CHECKS sp_restore_security_context(thd, save_ctx_func); error: @@ -4894,7 +4888,7 @@ Item_func_sp::tmp_table_field(TABLE *t_arg) /* - Find the function and chack access rigths to the function + Find the function and check access rights to the function SYNOPSIS find_and_check_access() diff --git a/sql/item_func.h b/sql/item_func.h index 223144a5d51..ed39cb86d3e 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -1129,7 +1129,6 @@ class user_var_entry; class Item_func_set_user_var :public Item_func { enum Item_result cached_result_type; - LEX_STRING name; user_var_entry *entry; char buffer[MAX_FIELD_WIDTH]; String value; @@ -1146,6 +1145,7 @@ class Item_func_set_user_var :public Item_func public: + LEX_STRING name; // keep it public Item_func_set_user_var(LEX_STRING a,Item *b) :Item_func(b), cached_result_type(INT_RESULT), name(a) {} @@ -1168,10 +1168,10 @@ public: class Item_func_get_user_var :public Item_func { - LEX_STRING name; user_var_entry *var_entry; public: + LEX_STRING name; // keep it public Item_func_get_user_var(LEX_STRING a): Item_func(), name(a) {} enum Functype functype() const { return GUSERVAR_FUNC; } diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index 5889821293d..81f809d2b67 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -651,8 +651,8 @@ public: class Item_func_conv_charset :public Item_str_func { - CHARSET_INFO *conv_charset; public: + CHARSET_INFO *conv_charset; // keep it public Item_func_conv_charset(Item *a, CHARSET_INFO *cs) :Item_str_func(a) { conv_charset=cs; } String *val_str(String *); diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index b2352e728c5..71f595184ec 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -641,12 +641,12 @@ enum interval_type class Item_date_add_interval :public Item_date_func { - const interval_type int_type; String value; - const bool date_sub_interval; enum_field_types cached_field_type; public: + const interval_type int_type; // keep it public + const bool date_sub_interval; // keep it public Item_date_add_interval(Item *a,Item *b,interval_type type_arg,bool neg_arg) :Item_date_func(a,b),int_type(type_arg), date_sub_interval(neg_arg) {} String *val_str(String *); @@ -662,10 +662,10 @@ public: class Item_extract :public Item_int_func { - const interval_type int_type; String value; bool date_value; public: + const interval_type int_type; // keep it public Item_extract(interval_type type_arg, Item *a) :Item_int_func(a), int_type(type_arg) {} longlong val_int(); @@ -910,8 +910,8 @@ enum date_time_format class Item_func_get_format :public Item_str_func { - const timestamp_type type; public: + const timestamp_type type; // keep it public Item_func_get_format(timestamp_type type_arg, Item *a) :Item_str_func(a), type(type_arg) {} diff --git a/sql/log.cc b/sql/log.cc index 7ea2dba2144..e42f1def27c 100644 --- a/sql/log.cc +++ b/sql/log.cc @@ -67,7 +67,7 @@ handlerton binlog_hton = { NULL, /* create_cursor_read_view */ NULL, /* set_cursor_read_view */ NULL, /* close_cursor_read_view */ - HTON_NO_FLAGS + HTON_HIDDEN }; /* @@ -357,8 +357,7 @@ static int find_uniq_filename(char *name) MYSQL_LOG::MYSQL_LOG() :bytes_written(0), last_time(0), query_start(0), name(0), prepared_xids(0), log_type(LOG_CLOSED), file_id(1), open_count(1), - readers_count(0), reset_pending(FALSE), write_error(FALSE), inited(FALSE), - need_start_event(TRUE), + write_error(FALSE), inited(FALSE), need_start_event(TRUE), description_event_for_exec(0), description_event_for_queue(0) { /* @@ -385,9 +384,7 @@ void MYSQL_LOG::cleanup() delete description_event_for_exec; (void) pthread_mutex_destroy(&LOCK_log); (void) pthread_mutex_destroy(&LOCK_index); - (void) pthread_mutex_destroy(&LOCK_readers); (void) pthread_cond_destroy(&update_cond); - (void) pthread_cond_destroy(&reset_cond); } DBUG_VOID_RETURN; } @@ -432,9 +429,7 @@ void MYSQL_LOG::init_pthread_objects() inited= 1; (void) pthread_mutex_init(&LOCK_log,MY_MUTEX_INIT_SLOW); (void) pthread_mutex_init(&LOCK_index, MY_MUTEX_INIT_SLOW); - (void) pthread_mutex_init(&LOCK_readers, MY_MUTEX_INIT_SLOW); (void) pthread_cond_init(&update_cond, 0); - (void) pthread_cond_init(&reset_cond, 0); } const char *MYSQL_LOG::generate_name(const char *log_name, @@ -938,12 +933,6 @@ bool MYSQL_LOG::reset_logs(THD* thd) pthread_mutex_lock(&LOCK_log); pthread_mutex_lock(&LOCK_index); - /* - we need one more lock to block attempts to open a log while - we are waiting untill all log files will be closed - */ - pthread_mutex_lock(&LOCK_readers); - /* The following mutex is needed to ensure that no threads call 'delete thd' as we would then risk missing a 'rollback' from this @@ -966,19 +955,6 @@ bool MYSQL_LOG::reset_logs(THD* thd) goto err; } - reset_pending= TRUE; - /* - send update signal just in case so that all reader threads waiting - for log update will leave wait condition - */ - signal_update(); - /* - if there are active readers wait until all of them will - release opened files - */ - while (readers_count) - pthread_cond_wait(&reset_cond, &LOCK_log); - for (;;) { my_delete(linfo.log_file_name, MYF(MY_WME)); @@ -997,10 +973,7 @@ bool MYSQL_LOG::reset_logs(THD* thd) my_free((gptr) save_name, MYF(0)); err: - reset_pending= FALSE; - (void) pthread_mutex_unlock(&LOCK_thread_count); - pthread_mutex_unlock(&LOCK_readers); pthread_mutex_unlock(&LOCK_index); pthread_mutex_unlock(&LOCK_log); DBUG_RETURN(error); @@ -2073,12 +2046,6 @@ void MYSQL_LOG::wait_for_update(THD* thd, bool is_slave) { const char *old_msg; DBUG_ENTER("wait_for_update"); - - if (reset_pending) - { - pthread_mutex_unlock(&LOCK_log); - DBUG_VOID_RETURN; - } old_msg= thd->enter_cond(&update_cond, &LOCK_log, is_slave ? @@ -2330,33 +2297,6 @@ void MYSQL_LOG::signal_update() DBUG_VOID_RETURN; } -void MYSQL_LOG::readers_addref() -{ - /* - There is no necessity for reference counting on *nix, since it allows to - delete opened files, however it is more clean way to wait - untill all files will be closed on *nix as well. - */ - DBUG_ENTER("MYSQL_LOG::reader_addref"); - pthread_mutex_lock(&LOCK_log); - pthread_mutex_lock(&LOCK_readers); - readers_count++; - pthread_mutex_unlock(&LOCK_readers); - pthread_mutex_unlock(&LOCK_log); - DBUG_VOID_RETURN; -} - -void MYSQL_LOG::readers_release() -{ - DBUG_ENTER("MYSQL_LOG::reader_release"); - pthread_mutex_lock(&LOCK_log); - readers_count--; - if (!readers_count) - pthread_cond_broadcast(&reset_cond); - pthread_mutex_unlock(&LOCK_log); - DBUG_VOID_RETURN; -} - #ifdef __NT__ void print_buffer_to_nt_eventlog(enum loglevel level, char *buff, uint length, int buffLen) diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index b7bd34c613f..1719253a458 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -522,8 +522,9 @@ bool delete_precheck(THD *thd, TABLE_LIST *tables); bool insert_precheck(THD *thd, TABLE_LIST *tables); bool create_table_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *create_table); -bool default_view_definer(Security_context *sctx, st_lex_user *definer); +bool get_default_definer(THD *thd, LEX_USER *definer); +LEX_USER *create_definer(THD *thd, LEX_STRING *user_name, LEX_STRING *host_name); enum enum_mysql_completiontype { ROLLBACK_RELEASE=-2, ROLLBACK=1, ROLLBACK_AND_CHAIN=7, @@ -847,6 +848,10 @@ bool mysqld_show_column_types(THD *thd); bool mysqld_help (THD *thd, const char *text); void calc_sum_of_all_status(STATUS_VAR *to); +void append_definer(THD *thd, String *buffer, const LEX_STRING *definer_user, + const LEX_STRING *definer_host); + + /* information schema */ extern LEX_STRING information_schema_name; LEX_STRING *make_lex_string(THD *thd, LEX_STRING *lex_str, @@ -1180,7 +1185,7 @@ extern my_bool opt_enable_named_pipe, opt_sync_frm, opt_allow_suspicious_udfs; extern my_bool opt_secure_auth; extern my_bool opt_log_slow_admin_statements; extern my_bool sp_automatic_privileges, opt_noacl; -extern my_bool opt_old_style_user_limits, trust_routine_creators; +extern my_bool opt_old_style_user_limits, trust_function_creators; extern uint opt_crash_binlog_innodb; extern char *shared_memory_base_name, *mysqld_unix_port; extern my_bool opt_enable_shared_memory; diff --git a/sql/mysqld.cc b/sql/mysqld.cc index acad378353b..890b1716212 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -371,7 +371,7 @@ my_bool opt_log_slow_admin_statements= 0; my_bool lower_case_file_system= 0; my_bool opt_large_pages= 0; uint opt_large_page_size= 0; -my_bool opt_old_style_user_limits= 0, trust_routine_creators= 0; +my_bool opt_old_style_user_limits= 0, trust_function_creators= 0; /* True if there is at least one per-hour limit for some user, so we should check them before each query (and possibly reset counters when hour is @@ -564,7 +564,7 @@ bool mysqld_embedded=1; static const char* default_dbug_option; #endif #ifdef HAVE_LIBWRAP -char *libwrapName= NULL; +const char *libwrapName= NULL; #endif #ifdef HAVE_QUERY_CACHE static ulong query_cache_limit= 0; @@ -4432,7 +4432,7 @@ enum options_mysqld OPT_INNODB_FAST_SHUTDOWN, OPT_INNODB_FILE_PER_TABLE, OPT_CRASH_BINLOG_INNODB, OPT_INNODB_LOCKS_UNSAFE_FOR_BINLOG, - OPT_LOG_BIN_TRUST_ROUTINE_CREATORS, + OPT_LOG_BIN_TRUST_FUNCTION_CREATORS, OPT_SAFE_SHOW_DB, OPT_INNODB_SAFE_BINLOG, OPT_INNODB, OPT_ISAM, OPT_ENGINE_CONDITION_PUSHDOWN, @@ -4857,16 +4857,27 @@ Disable with --skip-innodb-doublewrite.", (gptr*) &innobase_use_doublewrite, "File that holds the names for last binary log files.", (gptr*) &opt_binlog_index_name, (gptr*) &opt_binlog_index_name, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, +#ifndef TO_BE_REMOVED_IN_5_1_OR_6_0 + /* + In 5.0.6 we introduced the below option, then in 5.0.16 we renamed it to + log-bin-trust-function-creators but kept also the old name for + compatibility; the behaviour was also changed to apply only to functions + (and triggers). In a future release this old name could be removed. + */ + {"log-bin-trust-routine-creators", OPT_LOG_BIN_TRUST_FUNCTION_CREATORS, + "(deprecated) Use log-bin-trust-function-creators.", + (gptr*) &trust_function_creators, (gptr*) &trust_function_creators, 0, + GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, +#endif /* This option starts with "log-bin" to emphasize that it is specific of - binary logging. Hopefully in 5.1 nobody will need it anymore, when we have - row-level binlog. + binary logging. */ - {"log-bin-trust-routine-creators", OPT_LOG_BIN_TRUST_ROUTINE_CREATORS, + {"log-bin-trust-function-creators", OPT_LOG_BIN_TRUST_FUNCTION_CREATORS, "If equal to 0 (the default), then when --log-bin is used, creation of " - "a routine is allowed only to users having the SUPER privilege and only" - "if this routine may not break binary logging", - (gptr*) &trust_routine_creators, (gptr*) &trust_routine_creators, 0, + "a function is allowed only to users having the SUPER privilege and only " + "if this function may not break binary logging.", + (gptr*) &trust_function_creators, (gptr*) &trust_function_creators, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, {"log-error", OPT_ERROR_LOG_FILE, "Error log file.", (gptr*) &log_error_file_ptr, (gptr*) &log_error_file_ptr, 0, GET_STR, @@ -5725,7 +5736,7 @@ The minimum value for this variable is 4096.", (gptr*) &max_system_variables.read_buff_size,0, GET_ULONG, REQUIRED_ARG, 128*1024L, IO_SIZE*2+MALLOC_OVERHEAD, ~0L, MALLOC_OVERHEAD, IO_SIZE, 0}, {"read_only", OPT_READONLY, - "Make all tables readonly, with the exception for replication (slave) threads and users with the SUPER privilege", + "Make all non-temporary tables read-only, with the exception for replication (slave) threads and users with the SUPER privilege", (gptr*) &opt_readonly, (gptr*) &opt_readonly, 0, GET_BOOL, NO_ARG, 0, 0, 1, 0, 1, 0}, diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 63a49a46110..de52811c12f 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -5759,10 +5759,17 @@ QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table, MEM_ROOT *old_root= thd->mem_root; /* The following call may change thd->mem_root */ QUICK_RANGE_SELECT *quick= new QUICK_RANGE_SELECT(thd, table, ref->key, 0); + /* save mem_root set by QUICK_RANGE_SELECT constructor */ + MEM_ROOT *alloc= thd->mem_root; KEY *key_info = &table->key_info[ref->key]; KEY_PART *key_part; QUICK_RANGE *range; uint part; + /* + return back default mem_root (thd->mem_root) changed by + QUICK_RANGE_SELECT constructor + */ + thd->mem_root= old_root; if (!quick) return 0; /* no ranges found */ @@ -5774,7 +5781,7 @@ QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table, quick->records= records; if (cp_buffer_from_ref(thd,ref) && thd->is_fatal_error || - !(range= new QUICK_RANGE())) + !(range= new(alloc) QUICK_RANGE())) goto err; // out of memory range->min_key=range->max_key=(char*) ref->key_buff; @@ -5809,20 +5816,20 @@ QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table, QUICK_RANGE *null_range; *ref->null_ref_key= 1; // Set null byte then create a range - if (!(null_range= new QUICK_RANGE((char*)ref->key_buff, ref->key_length, - (char*)ref->key_buff, ref->key_length, - EQ_RANGE))) + if (!(null_range= new (alloc) QUICK_RANGE((char*)ref->key_buff, + ref->key_length, + (char*)ref->key_buff, + ref->key_length, + EQ_RANGE))) goto err; *ref->null_ref_key= 0; // Clear null byte if (insert_dynamic(&quick->ranges,(gptr)&null_range)) goto err; } - thd->mem_root= old_root; return quick; err: - thd->mem_root= old_root; delete quick; return 0; } diff --git a/sql/set_var.cc b/sql/set_var.cc index 8cf7311265c..5a6ff7d05ad 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -200,9 +200,12 @@ sys_var_key_cache_long sys_key_cache_age_threshold("key_cache_age_threshold", param_age_threshold)); sys_var_bool_ptr sys_local_infile("local_infile", &opt_local_infile); -sys_var_bool_ptr +sys_var_trust_routine_creators sys_trust_routine_creators("log_bin_trust_routine_creators", - &trust_routine_creators); + &trust_function_creators); +sys_var_bool_ptr +sys_trust_function_creators("log_bin_trust_function_creators", + &trust_function_creators); sys_var_thd_ulong sys_log_warnings("log_warnings", &SV::log_warnings); sys_var_thd_ulong sys_long_query_time("long_query_time", &SV::long_query_time); @@ -722,6 +725,7 @@ sys_var *sys_variables[]= &sys_innodb_commit_concurrency, #endif &sys_trust_routine_creators, + &sys_trust_function_creators, &sys_engine_condition_pushdown, #ifdef HAVE_NDBCLUSTER_DB &sys_ndb_autoincrement_prefetch_sz, @@ -865,7 +869,7 @@ struct show_var_st init_vars[]= { #endif {"log", (char*) &opt_log, SHOW_BOOL}, {"log_bin", (char*) &opt_bin_log, SHOW_BOOL}, - {sys_trust_routine_creators.name,(char*) &sys_trust_routine_creators, SHOW_SYS}, + {sys_trust_function_creators.name,(char*) &sys_trust_function_creators, SHOW_SYS}, {"log_error", (char*) log_error_file, SHOW_CHAR}, #ifdef HAVE_REPLICATION {"log_slave_updates", (char*) &opt_log_slave_updates, SHOW_MY_BOOL}, @@ -3463,6 +3467,26 @@ bool process_key_caches(int (* func) (const char *name, KEY_CACHE *)) } +void sys_var_trust_routine_creators::warn_deprecated(THD *thd) +{ + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_WARN_DEPRECATED_SYNTAX, + ER(ER_WARN_DEPRECATED_SYNTAX), "log_bin_trust_routine_creators", + "log_bin_trust_function_creators"); +} + +void sys_var_trust_routine_creators::set_default(THD *thd, enum_var_type type) +{ + warn_deprecated(thd); + sys_var_bool_ptr::set_default(thd, type); +} + +bool sys_var_trust_routine_creators::update(THD *thd, set_var *var) +{ + warn_deprecated(thd); + return sys_var_bool_ptr::update(thd, var); +} + /**************************************************************************** Used templates ****************************************************************************/ diff --git a/sql/set_var.h b/sql/set_var.h index 854409c159e..18c3353e8ff 100644 --- a/sql/set_var.h +++ b/sql/set_var.h @@ -738,6 +738,17 @@ public: byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base); }; +class sys_var_trust_routine_creators :public sys_var_bool_ptr +{ + /* We need a derived class only to have a warn_deprecated() */ +public: + sys_var_trust_routine_creators(const char *name_arg, my_bool *value_arg) : + sys_var_bool_ptr(name_arg, value_arg) {}; + void warn_deprecated(THD *thd); + void set_default(THD *thd, enum_var_type type); + bool update(THD *thd, set_var *var); +}; + /**************************************************************************** Classes for parsing of the SET command ****************************************************************************/ diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt index f85bda90e81..a25d1196904 100644 --- a/sql/share/errmsg.txt +++ b/sql/share/errmsg.txt @@ -5349,9 +5349,9 @@ ER_CANT_CREATE_GEOMETRY_OBJECT 22003 ER_FAILED_ROUTINE_BREAK_BINLOG eng "A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes" ER_BINLOG_UNSAFE_ROUTINE - eng "This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)" + eng "This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)" ER_BINLOG_CREATE_ROUTINE_NEED_SUPER - eng "You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)" + eng "You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)" ER_EXEC_STMT_WITH_OPEN_CURSOR eng "You can't execute a prepared statement which has an open cursor associated with it. Reset the statement to re-execute it." ER_STMT_HAS_NO_OPEN_CURSOR @@ -5405,14 +5405,14 @@ ER_PS_NO_RECURSION eng "The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner" ER_SP_CANT_SET_AUTOCOMMIT eng "Not allowed to set autocommit from a stored function or trigger" -ER_NO_VIEW_USER - eng "View definer is not fully qualified" +ER_MALFORMED_DEFINER + eng "Definer is not fully qualified" ER_VIEW_FRM_NO_USER eng "View %-.64s.%-.64s has not definer information (old table format). Current user is used as definer. Please recreate view!" ER_VIEW_OTHER_USER - eng "You need the SUPER privilege for creation view with %-.64s@%-.64s definer" + eng "You need the SUPER privilege for creation view with %-.64s@%-.64s definer" ER_NO_SUCH_USER - eng "There is not %-.64s@%-.64s registered" + eng "There is no '%-.64s'@'%-.64s' registered" ER_FORBID_SCHEMA_CHANGE eng "Changing schema from '%-.64s' to '%-.64s' is not allowed." ER_ROW_IS_REFERENCED_2 23000 @@ -5421,3 +5421,5 @@ ER_NO_REFERENCED_ROW_2 23000 eng "Cannot add or update a child row: a foreign key constraint fails (%.192s)" ER_SP_BAD_VAR_SHADOW 42000 eng "Variable '%-.64s' must be quoted with `...`, or renamed" +ER_TRG_NO_DEFINER + eng "No definer attribute for trigger '%-.64s'.'%-.64s'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger." diff --git a/sql/sp.cc b/sql/sp.cc index 8386c5d58a2..451cec75236 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -441,8 +441,8 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp) if (dbchanged && (ret= mysql_change_db(thd, olddb, 1))) goto done; *sphp= thd->lex->sphead; - (*sphp)->set_info((char *)definer, (uint)strlen(definer), - created, modified, &chistics, sql_mode); + (*sphp)->set_definer((char*) definer, (uint) strlen(definer)); + (*sphp)->set_info(created, modified, &chistics, sql_mode); (*sphp)->optimize(); } thd->lex->sql_command= oldcmd; @@ -551,12 +551,13 @@ db_create_routine(THD *thd, int type, sp_head *sp) store(sp->m_chistics->comment.str, sp->m_chistics->comment.length, system_charset_info); - if (!trust_routine_creators && mysql_bin_log.is_open()) + if ((sp->m_type == TYPE_ENUM_FUNCTION) && + !trust_function_creators && mysql_bin_log.is_open()) { if (!sp->m_chistics->detistic) { /* - Note that for a _function_ this test is not enough; one could use + Note that this test is not perfect; one could use a non-deterministic read-only function in an update statement. */ enum enum_sp_data_access access= diff --git a/sql/sp_head.cc b/sql/sp_head.cc index abc66ce0b21..08a189165b5 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -1569,21 +1569,9 @@ sp_head::check_backpatch(THD *thd) } void -sp_head::set_info(char *definer, uint definerlen, - longlong created, longlong modified, +sp_head::set_info(longlong created, longlong modified, st_sp_chistics *chistics, ulong sql_mode) { - char *p= strchr(definer, '@'); - uint len; - - if (! p) - p= definer; // Weird... - len= p-definer; - m_definer_user.str= strmake_root(mem_root, definer, len); - m_definer_user.length= len; - len= definerlen-len-1; - m_definer_host.str= strmake_root(mem_root, p+1, len); - m_definer_host.length= len; m_created= created; m_modified= modified; m_chistics= (st_sp_chistics *) memdup_root(mem_root, (char*) chistics, @@ -1597,6 +1585,34 @@ sp_head::set_info(char *definer, uint definerlen, m_sql_mode= sql_mode; } + +void +sp_head::set_definer(char *definer, uint definerlen) +{ + char *p= strrchr(definer, '@'); + + if (!p) + { + m_definer_user.str= strmake_root(mem_root, "", 0); + m_definer_user.length= 0; + + m_definer_host.str= strmake_root(mem_root, "", 0); + m_definer_host.length= 0; + } + else + { + const uint user_name_len= p - definer; + const uint host_name_len= definerlen - user_name_len - 1; + + m_definer_user.str= strmake_root(mem_root, definer, user_name_len); + m_definer_user.length= user_name_len; + + m_definer_host.str= strmake_root(mem_root, p + 1, host_name_len); + m_definer_host.length= host_name_len; + } +} + + void sp_head::reset_thd_mem_root(THD *thd) { diff --git a/sql/sp_head.h b/sql/sp_head.h index ed0f3987e01..d1a122fd410 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -251,10 +251,11 @@ public: Field *make_field(uint max_length, const char *name, TABLE *dummy); - void set_info(char *definer, uint definerlen, - longlong created, longlong modified, + void set_info(longlong created, longlong modified, st_sp_chistics *chistics, ulong sql_mode); + void set_definer(char *definer, uint definerlen); + void reset_thd_mem_root(THD *thd); void restore_thd_mem_root(THD *thd); diff --git a/sql/spatial.cc b/sql/spatial.cc index 5af1bec45ca..ca9615236e0 100644 --- a/sql/spatial.cc +++ b/sql/spatial.cc @@ -178,7 +178,9 @@ static double wkb_get_double(const char *ptr, Geometry::wkbByteOrder bo) { double res; if (bo != Geometry::wkb_xdr) + { float8get(res, ptr); + } else { char inv_array[8]; diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 2b5945b74af..bc8b9ba2efb 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -3532,7 +3532,7 @@ bool check_grant(THD *thd, ulong want_access, TABLE_LIST *tables, of other queries). For simple queries first_not_own_table is 0. */ for (i= 0, table= tables; - table != first_not_own_table && i < number; + table && table != first_not_own_table && i < number; table= table->next_global, i++) { /* Remove SHOW_VIEW_ACL, because it will be checked during making view */ diff --git a/sql/sql_class.h b/sql/sql_class.h index 2d1880a6d9d..eaa8291e697 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -189,11 +189,10 @@ class MYSQL_LOG: public TC_LOG { private: /* LOCK_log and LOCK_index are inited by init_pthread_objects() */ - pthread_mutex_t LOCK_log, LOCK_index, LOCK_readers; + pthread_mutex_t LOCK_log, LOCK_index; pthread_mutex_t LOCK_prep_xids; pthread_cond_t COND_prep_xids; pthread_cond_t update_cond; - pthread_cond_t reset_cond; ulonglong bytes_written; time_t last_time,query_start; IO_CACHE log_file; @@ -335,9 +334,6 @@ public: int purge_logs_before_date(time_t purge_time); int purge_first_log(struct st_relay_log_info* rli, bool included); bool reset_logs(THD* thd); - inline bool is_reset_pending() { return reset_pending; } - void readers_addref(); - void readers_release(); void close(uint exiting); // iterating through the log index file diff --git a/sql/sql_cursor.cc b/sql/sql_cursor.cc index e8da691ea18..fc169fe18e8 100644 --- a/sql/sql_cursor.cc +++ b/sql/sql_cursor.cc @@ -558,6 +558,25 @@ int Materialized_cursor::open(JOIN *join __attribute__((unused))) result->prepare(item_list, &fake_unit) || table->file->ha_rnd_init(TRUE)); thd->restore_active_arena(this, &backup_arena); + if (rc == 0) + { + /* + Now send the result set metadata to the client. We need to + do it here, as in Select_materialize::send_fields the items + for column types are not yet created (send_fields requires + a list of items). The new types may differ from the original + ones sent at prepare if some of them were altered by MySQL + HEAP tables mechanism -- used when create_tmp_field_from_item + may alter the original column type. + + We can't simply supply SEND_EOF flag to send_fields, because + send_fields doesn't flush the network buffer. + */ + rc= result->send_fields(item_list, Protocol::SEND_NUM_ROWS); + thd->server_status|= SERVER_STATUS_CURSOR_EXISTS; + result->send_eof(); + thd->server_status&= ~SERVER_STATUS_CURSOR_EXISTS; + } return rc; } @@ -647,14 +666,6 @@ bool Select_materialize::send_fields(List<Item> &list, uint flags) if (create_result_table(unit->thd, unit->get_unit_column_types(), FALSE, thd->options | TMP_TABLE_ALL_COLUMNS, "")) return TRUE; - /* - We can't simply supply SEND_EOF flag to send_fields, because send_fields - doesn't flush the network buffer. - */ - rc= result->send_fields(list, Protocol::SEND_NUM_ROWS); - thd->server_status|= SERVER_STATUS_CURSOR_EXISTS; - result->send_eof(); - thd->server_status&= ~SERVER_STATUS_CURSOR_EXISTS; return rc; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index a8bee9bb59b..0e836b6e9b9 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -737,10 +737,15 @@ typedef struct st_lex TABLE_LIST **query_tables_last; /* store original leaf_tables for INSERT SELECT and PS/SP */ TABLE_LIST *leaf_tables_insert; - st_lex_user *create_view_definer; char *create_view_start; char *create_view_select_start; + /* + The definer of the object being created (view, trigger, stored routine). + I.e. the value of DEFINER clause. + */ + LEX_USER *definer; + List<key_part_spec> col_list; List<key_part_spec> ref_list; List<String> interval_list; @@ -888,6 +893,14 @@ typedef struct st_lex SQL_LIST trg_table_fields; /* + trigger_definition_begin points to the beginning of the word "TRIGGER" in + CREATE TRIGGER statement. This is used to add possibly omitted DEFINER + clause to the trigger definition statement before dumping it to the + binlog. + */ + const char *trigger_definition_begin; + + /* If non-0 then indicates that query requires prelocking and points to next_global member of last own element in query table list (i.e. last table which was not added to it as part of preparation to prelocking). diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index b6ff58e1215..c19d54feda5 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4318,18 +4318,6 @@ end_with_restore_list: So just execute the statement. */ res= sp->execute_procedure(thd, &lex->value_list); - if (mysql_bin_log.is_open() && - (sp->m_chistics->daccess == SP_CONTAINS_SQL || - sp->m_chistics->daccess == SP_MODIFIES_SQL_DATA)) - { - if (res) - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_FAILED_ROUTINE_BREAK_BINLOG, - ER(ER_FAILED_ROUTINE_BREAK_BINLOG)); - else - thd->clear_error(); - } - /* If warnings have been cleared, we have to clear total_warn_count too, otherwise the clients get confused. @@ -4388,7 +4376,8 @@ end_with_restore_list: if (end_active_trans(thd)) goto error; memcpy(&lex->sp_chistics, &chistics, sizeof(lex->sp_chistics)); - if (!trust_routine_creators && mysql_bin_log.is_open() && + if ((sp->m_type == TYPE_ENUM_FUNCTION) && + !trust_function_creators && mysql_bin_log.is_open() && !sp->m_chistics->detistic && (chistics.daccess == SP_CONTAINS_SQL || chistics.daccess == SP_MODIFIES_SQL_DATA)) @@ -4399,6 +4388,12 @@ end_with_restore_list: } else { + /* + Note that if you implement the capability of ALTER FUNCTION to + alter the body of the function, this command should be made to + follow the restrictions that log-bin-trust-function-creators=0 + already puts on CREATE FUNCTION. + */ if (lex->sql_command == SQLCOM_ALTER_PROCEDURE) result= sp_update_procedure(thd, lex->spname, &lex->sp_chistics); else @@ -5062,7 +5057,7 @@ check_table_access(THD *thd, ulong want_access,TABLE_LIST *tables, the given table list refers to the list for prelocking (contains tables of other queries). For simple queries first_not_own_table is 0. */ - for (; tables != first_not_own_table; tables= tables->next_global) + for (; tables && tables != first_not_own_table; tables= tables->next_global) { if (tables->schema_table && (want_access & ~(SELECT_ACL | EXTRA_ACL | FILE_ACL))) @@ -7466,32 +7461,81 @@ Item *negate_expression(THD *thd, Item *expr) return new Item_func_not(expr); } +/* + Set the specified definer to the default value, which is the current user in + the thread. Also check that the current user satisfies to the definers + requirements. + + SYNOPSIS + get_default_definer() + thd [in] thread handler + definer [out] definer + + RETURN + error status, that is: + - FALSE -- on success; + - TRUE -- on error (current user can not be a definer). +*/ + +bool get_default_definer(THD *thd, LEX_USER *definer) +{ + /* Check that current user has non-empty host name. */ + + const Security_context *sctx= thd->security_ctx; + + if (sctx->priv_host[0] == 0) + { + my_error(ER_MALFORMED_DEFINER, MYF(0)); + return TRUE; + } + + /* Fill in. */ + + definer->user.str= (char *) sctx->priv_user; + definer->user.length= strlen(definer->user.str); + + definer->host.str= (char *) sctx->priv_host; + definer->host.length= strlen(definer->host.str); + + return FALSE; +} + /* - Assign as view definer current user + Create definer with the given user and host names. Also check that the user + and host names satisfy definers requirements. SYNOPSIS - default_view_definer() - sctx current security context - definer structure where it should be assigned + create_definer() + thd [in] thread handler + user_name [in] user name + host_name [in] host name RETURN - FALSE OK - TRUE Error + On success, return a valid pointer to the created and initialized + LEX_STRING, which contains definer information. + On error, return 0. */ -bool default_view_definer(Security_context *sctx, st_lex_user *definer) +LEX_USER *create_definer(THD *thd, LEX_STRING *user_name, LEX_STRING *host_name) { - definer->user.str= sctx->priv_user; - definer->user.length= strlen(sctx->priv_user); + LEX_USER *definer; - if (!*sctx->priv_host) + /* Check that specified host name is valid. */ + + if (host_name->length == 0) { - my_error(ER_NO_VIEW_USER, MYF(0)); - return TRUE; + my_error(ER_MALFORMED_DEFINER, MYF(0)); + return 0; } - definer->host.str= sctx->priv_host; - definer->host.length= strlen(sctx->priv_host); - return FALSE; + /* Create and initialize. */ + + if (! (definer= (LEX_USER*) thd->alloc(sizeof (LEX_USER)))) + return 0; + + definer->user= *user_name; + definer->host= *host_name; + + return definer; } diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index b5865fa8816..cd293fc21c7 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -372,11 +372,6 @@ void mysql_binlog_send(THD* thd, char* log_ident, my_off_t pos, goto err; } - /* - Call readers_addref before opening log to track count - of binlog readers - */ - mysql_bin_log.readers_addref(); if ((file=open_binlog(&log, log_file_name, &errmsg)) < 0) { my_errno= ER_MASTER_FATAL_ERROR_READING_BINLOG; @@ -574,8 +569,7 @@ impossible position"; goto err; if (!(flags & BINLOG_DUMP_NON_BLOCK) && - mysql_bin_log.is_active(log_file_name) && - !mysql_bin_log.is_reset_pending()) + mysql_bin_log.is_active(log_file_name)) { /* Block until there is more data in the log @@ -688,13 +682,7 @@ impossible position"; else { bool loop_breaker = 0; - // need this to break out of the for loop from switch - - // if we are going to switch log file anyway, close current log first - end_io_cache(&log); - (void) my_close(file, MYF(MY_WME)); - // decrease reference count of binlog readers - mysql_bin_log.readers_release(); + /* need this to break out of the for loop from switch */ thd->proc_info = "Finished reading one binlog; switching to next binlog"; switch (mysql_bin_log.find_next_log(&linfo, 1)) { @@ -704,25 +692,16 @@ impossible position"; case 0: break; default: - // need following call to do release on err label - mysql_bin_log.readers_addref(); errmsg = "could not find next log"; my_errno= ER_MASTER_FATAL_ERROR_READING_BINLOG; goto err; } - if (loop_breaker) - { - // need following call to do release on end label - mysql_bin_log.readers_addref(); - break; - } - - /* - Call readers_addref before opening log to track count - of binlog readers - */ - mysql_bin_log.readers_addref(); + if (loop_breaker) + break; + + end_io_cache(&log); + (void) my_close(file, MYF(MY_WME)); /* Call fake_rotate_event() in case the previous log (the one which @@ -755,8 +734,6 @@ end: end_io_cache(&log); (void)my_close(file, MYF(MY_WME)); - // decrease reference count of binlog readers - mysql_bin_log.readers_release(); send_eof(thd); thd->proc_info = "Waiting to finalize termination"; @@ -783,8 +760,6 @@ err: pthread_mutex_unlock(&LOCK_thread_count); if (file >= 0) (void) my_close(file, MYF(MY_WME)); - // decrease reference count of binlog readers - mysql_bin_log.readers_release(); my_message(my_errno, errmsg, MYF(0)); DBUG_VOID_RETURN; diff --git a/sql/sql_show.cc b/sql/sql_show.cc index b4b24e0b6be..5ad987d6b7a 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -70,17 +70,20 @@ bool mysqld_show_storage_engines(THD *thd) handlerton **types; for (types= sys_table_types; *types; types++) { - protocol->prepare_for_resend(); - protocol->store((*types)->name, system_charset_info); - const char *option_name= show_comp_option_name[(int) (*types)->state]; - - if ((*types)->state == SHOW_OPTION_YES && - !my_strcasecmp(system_charset_info, default_type_name, (*types)->name)) - option_name= "DEFAULT"; - protocol->store(option_name, system_charset_info); - protocol->store((*types)->comment, system_charset_info); - if (protocol->write()) - DBUG_RETURN(TRUE); + if (!((*types)->flags & HTON_HIDDEN)) + { + protocol->prepare_for_resend(); + protocol->store((*types)->name, system_charset_info); + const char *option_name= show_comp_option_name[(int) (*types)->state]; + + if ((*types)->state == SHOW_OPTION_YES && + !my_strcasecmp(system_charset_info, default_type_name, (*types)->name)) + option_name= "DEFAULT"; + protocol->store(option_name, system_charset_info); + protocol->store((*types)->comment, system_charset_info); + if (protocol->write()) + DBUG_RETURN(TRUE); + } } send_eof(thd); DBUG_RETURN(FALSE); @@ -359,7 +362,21 @@ mysqld_show_create(THD *thd, TABLE_LIST *table_list) /* Only one table for now, but VIEW can involve several tables */ if (open_normal_and_derived_tables(thd, table_list, 0)) - DBUG_RETURN(TRUE); + { + if (!table_list->view || thd->net.last_errno != ER_VIEW_INVALID) + DBUG_RETURN(TRUE); + /* + Clear all messages with 'error' level status and + issue a warning with 'warning' level status in + case of invalid view and last error is ER_VIEW_INVALID + */ + mysql_reset_errors(thd, true); + push_warning_printf(thd,MYSQL_ERROR::WARN_LEVEL_WARN, + ER_VIEW_INVALID, + ER(ER_VIEW_INVALID), + table_list->view_db.str, + table_list->view_name.str); + } /* TODO: add environment variables show when it become possible */ if (thd->lex->only_view && !table_list->view) @@ -1060,18 +1077,36 @@ view_store_options(THD *thd, TABLE_LIST *table, String *buff) default: DBUG_ASSERT(0); // never should happen } - buff->append("DEFINER=", 8); - append_identifier(thd, buff, - table->definer.user.str, table->definer.user.length); - buff->append('@'); - append_identifier(thd, buff, - table->definer.host.str, table->definer.host.length); + append_definer(thd, buff, &table->definer.user, &table->definer.host); if (table->view_suid) - buff->append(" SQL SECURITY DEFINER ", 22); + buff->append("SQL SECURITY DEFINER ", 21); else - buff->append(" SQL SECURITY INVOKER ", 22); + buff->append("SQL SECURITY INVOKER ", 21); } + +/* + Append DEFINER clause to the given buffer. + + SYNOPSIS + append_definer() + thd [in] thread handle + buffer [inout] buffer to hold DEFINER clause + definer_user [in] user name part of definer + definer_host [in] host name part of definer +*/ + +void append_definer(THD *thd, String *buffer, const LEX_STRING *definer_user, + const LEX_STRING *definer_host) +{ + buffer->append(STRING_WITH_LEN("DEFINER=")); + append_identifier(thd, buffer, definer_user->str, definer_user->length); + buffer->append('@'); + append_identifier(thd, buffer, definer_host->str, definer_host->length); + buffer->append(' '); +} + + static int view_store_create_info(THD *thd, TABLE_LIST *table, String *buff) { @@ -2971,47 +3006,44 @@ static int get_schema_views_record(THD *thd, struct st_table_list *tables, DBUG_ENTER("get_schema_views_record"); char definer[HOSTNAME_LENGTH + USERNAME_LENGTH + 2]; uint definer_len; - if (!res) - { - if (tables->view) - { - restore_record(table, s->default_values); - table->field[1]->store(tables->view_db.str, tables->view_db.length, cs); - table->field[2]->store(tables->view_name.str, tables->view_name.length, - cs); - table->field[3]->store(tables->query.str, tables->query.length, cs); - if (tables->with_check != VIEW_CHECK_NONE) - { - if (tables->with_check == VIEW_CHECK_LOCAL) - table->field[4]->store(STRING_WITH_LEN("LOCAL"), cs); - else - table->field[4]->store(STRING_WITH_LEN("CASCADED"), cs); - } - else - table->field[4]->store(STRING_WITH_LEN("NONE"), cs); + if (tables->view) + { + restore_record(table, s->default_values); + table->field[1]->store(tables->view_db.str, tables->view_db.length, cs); + table->field[2]->store(tables->view_name.str, tables->view_name.length, + cs); + table->field[3]->store(tables->query.str, tables->query.length, cs); - if (tables->updatable_view) - table->field[5]->store(STRING_WITH_LEN("YES"), cs); - else - table->field[5]->store(STRING_WITH_LEN("NO"), cs); - definer_len= (strxmov(definer, tables->definer.user.str, "@", - tables->definer.host.str, NullS) - definer); - table->field[6]->store(definer, definer_len, cs); - if (tables->view_suid) - table->field[7]->store(STRING_WITH_LEN("DEFINER"), cs); + if (tables->with_check != VIEW_CHECK_NONE) + { + if (tables->with_check == VIEW_CHECK_LOCAL) + table->field[4]->store(STRING_WITH_LEN("LOCAL"), cs); else - table->field[7]->store(STRING_WITH_LEN("INVOKER"), cs); - DBUG_RETURN(schema_table_store_record(thd, table)); + table->field[4]->store(STRING_WITH_LEN("CASCADED"), cs); } - } - else - { - if (tables->view) + else + table->field[4]->store(STRING_WITH_LEN("NONE"), cs); + + if (tables->updatable_view) + table->field[5]->store(STRING_WITH_LEN("YES"), cs); + else + table->field[5]->store(STRING_WITH_LEN("NO"), cs); + definer_len= (strxmov(definer, tables->definer.user.str, "@", + tables->definer.host.str, NullS) - definer); + table->field[6]->store(definer, definer_len, cs); + if (tables->view_suid) + table->field[7]->store(STRING_WITH_LEN("DEFINER"), cs); + else + table->field[7]->store(STRING_WITH_LEN("INVOKER"), cs); + if (schema_table_store_record(thd, table)) + DBUG_RETURN(1); + if (res) push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, thd->net.last_errno, thd->net.last_error); - thd->clear_error(); } + if (res) + thd->clear_error(); DBUG_RETURN(0); } @@ -3094,7 +3126,8 @@ static bool store_trigger(THD *thd, TABLE *table, const char *db, enum trg_event_type event, enum trg_action_time_type timing, LEX_STRING *trigger_stmt, - ulong sql_mode) + ulong sql_mode, + LEX_STRING *definer_buffer) { CHARSET_INFO *cs= system_charset_info; byte *sql_mode_str; @@ -3119,6 +3152,7 @@ static bool store_trigger(THD *thd, TABLE *table, const char *db, sql_mode, &sql_mode_len); table->field[17]->store((const char*)sql_mode_str, sql_mode_len, cs); + table->field[18]->store((const char *)definer_buffer->str, definer_buffer->length, cs); return schema_table_store_record(thd, table); } @@ -3152,15 +3186,21 @@ static int get_schema_triggers_record(THD *thd, struct st_table_list *tables, LEX_STRING trigger_name; LEX_STRING trigger_stmt; ulong sql_mode; + char definer_holder[HOSTNAME_LENGTH + USERNAME_LENGTH + 2]; + LEX_STRING definer_buffer; + definer_buffer.str= definer_holder; if (triggers->get_trigger_info(thd, (enum trg_event_type) event, (enum trg_action_time_type)timing, &trigger_name, &trigger_stmt, - &sql_mode)) + &sql_mode, + &definer_buffer)) continue; + if (store_trigger(thd, table, base_name, file_name, &trigger_name, (enum trg_event_type) event, (enum trg_action_time_type) timing, &trigger_stmt, - sql_mode)) + sql_mode, + &definer_buffer)) DBUG_RETURN(1); } } @@ -4064,6 +4104,7 @@ ST_FIELD_INFO triggers_fields_info[]= {"ACTION_REFERENCE_NEW_ROW", 3, MYSQL_TYPE_STRING, 0, 0, 0}, {"CREATED", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Created"}, {"SQL_MODE", 65535, MYSQL_TYPE_STRING, 0, 0, "sql_mode"}, + {"DEFINER", 65535, MYSQL_TYPE_STRING, 0, 0, "Definer"}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} }; diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc index dbad8dcffb5..84cc79ee4dc 100644 --- a/sql/sql_trigger.cc +++ b/sql/sql_trigger.cc @@ -32,15 +32,36 @@ const char * const triggers_file_ext= ".TRG"; */ static File_option triggers_file_parameters[]= { - {{(char*)"triggers", 8}, + { + { (char *) STRING_WITH_LEN("triggers") }, offsetof(class Table_triggers_list, definitions_list), - FILE_OPTIONS_STRLIST}, - {{(char*)"sql_modes", 13}, + FILE_OPTIONS_STRLIST + }, + { + /* + FIXME: Length specified for "sql_modes" key is erroneous, problem caused + by this are reported as BUG#14090 and should be fixed ASAP. + */ + { (char *) "sql_modes", 13 }, offsetof(class Table_triggers_list, definition_modes_list), - FILE_OPTIONS_ULLLIST}, - {{0, 0}, 0, FILE_OPTIONS_STRING} + FILE_OPTIONS_ULLLIST + }, + { + { (char *) STRING_WITH_LEN("definers") }, + offsetof(class Table_triggers_list, definers_list), + FILE_OPTIONS_STRLIST + }, + { { 0, 0 }, 0, FILE_OPTIONS_STRING } }; +/* + This must be kept up to date whenever a new option is added to the list + above, as it specifies the number of required parameters of the trigger in + .trg file. +*/ + +static const int TRG_NUM_REQUIRED_PARAMETERS= 4; +static const int TRG_MAX_VERSIONS= 3; /* Structure representing contents of .TRN file which are used to support @@ -58,9 +79,16 @@ const char * const trigname_file_ext= ".TRN"; static File_option trigname_file_parameters[]= { - {{(char*)"trigger_table", 15}, offsetof(struct st_trigname, trigger_table), - FILE_OPTIONS_ESTRING}, - {{0, 0}, 0, FILE_OPTIONS_STRING} + { + /* + FIXME: Length specified for "trigger_table" key is erroneous, problem + caused by this are reported as BUG#14090 and should be fixed ASAP. + */ + { (char *) "trigger_table", 15 }, + offsetof(struct st_trigname, trigger_table), + FILE_OPTIONS_ESTRING + }, + { { 0, 0 }, 0, FILE_OPTIONS_STRING } }; @@ -104,6 +132,9 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create) { TABLE *table; bool result= TRUE; + LEX_STRING definer_user; + LEX_STRING definer_host; + DBUG_ENTER("mysql_create_or_drop_trigger"); /* @@ -131,9 +162,12 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create) But a trigger can in theory be used to do nasty things (if it supported DROP for example) so we do the check for privileges. For now there is already a stronger test right above; but when this stronger test will - be removed, the test below will hold. + be removed, the test below will hold. Because triggers have the same + nature as functions regarding binlogging: their body is implicitely + binlogged, so they share the same danger, so trust_function_creators + applies to them too. */ - if (!trust_routine_creators && mysql_bin_log.is_open() && + if (!trust_function_creators && mysql_bin_log.is_open() && !(thd->security_ctx->master_access & SUPER_ACL)) { my_error(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER, MYF(0)); @@ -184,7 +218,7 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create) } result= (create ? - table->triggers->create_trigger(thd, tables): + table->triggers->create_trigger(thd, tables, &definer_user, &definer_host): table->triggers->drop_trigger(thd, tables)); end: @@ -192,17 +226,30 @@ end: start_waiting_global_read_lock(thd); if (!result) + { + if (mysql_bin_log.is_open()) { - if (mysql_bin_log.is_open()) + thd->clear_error(); + + String log_query(thd->query, thd->query_length, system_charset_info); + + if (create) { - thd->clear_error(); - /* Such a statement can always go directly to binlog, no trans cache */ - Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE); - mysql_bin_log.write(&qinfo); + log_query.set((char *) 0, 0, system_charset_info); /* reset log_query */ + + log_query.append("CREATE "); + append_definer(thd, &log_query, &definer_user, &definer_host); + log_query.append(thd->lex->trigger_definition_begin); } - send_ok(thd); + + /* Such a statement can always go directly to binlog, no trans cache. */ + Query_log_event qinfo(thd, log_query.ptr(), log_query.length(), 0, FALSE); + mysql_bin_log.write(&qinfo); } + send_ok(thd); + } + DBUG_RETURN(result); } @@ -212,15 +259,26 @@ end: SYNOPSIS create_trigger() - thd - current thread context (including trigger definition in LEX) - tables - table list containing one open table for which trigger is - created. + thd - current thread context (including trigger definition in + LEX) + tables - table list containing one open table for which the + trigger is created. + definer_user - [out] after a call it points to 0-terminated string, + which contains user name part of the actual trigger + definer. The caller is responsible to provide memory for + storing LEX_STRING object. + definer_host - [out] after a call it points to 0-terminated string, + which contains host name part of the actual trigger + definer. The caller is responsible to provide memory for + storing LEX_STRING object. RETURN VALUE False - success True - error */ -bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables) +bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables, + LEX_STRING *definer_user, + LEX_STRING *definer_host) { LEX *lex= thd->lex; TABLE *table= tables->table; @@ -229,6 +287,8 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables) LEX_STRING dir, file, trigname_file; LEX_STRING *trg_def, *name; ulonglong *trg_sql_mode; + char trg_definer_holder[HOSTNAME_LENGTH + USERNAME_LENGTH + 2]; + LEX_STRING *trg_definer; Item_trigger_field *trg_field; struct st_trigname trigname; @@ -250,6 +310,31 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables) } /* + Definer attribute of the Lex instance is always set in sql_yacc.yy when + trigger is created. + */ + + DBUG_ASSERT(lex->definer); + + /* + If the specified definer differs from the current user, we should check + that the current user has SUPER privilege (in order to create trigger + under another user one must have SUPER privilege). + */ + + if (strcmp(lex->definer->user.str, thd->security_ctx->priv_user) || + my_strcasecmp(system_charset_info, + lex->definer->host.str, + thd->security_ctx->priv_host)) + { + if (check_global_access(thd, SUPER_ACL)) + { + my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "SUPER"); + return TRUE; + } + } + + /* Let us check if all references to fields in old/new versions of row in this trigger are ok. @@ -318,15 +403,39 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables) definitions_list.push_back(trg_def, &table->mem_root) || !(trg_sql_mode= (ulonglong*)alloc_root(&table->mem_root, sizeof(ulonglong))) || - definition_modes_list.push_back(trg_sql_mode, &table->mem_root)) + definition_modes_list.push_back(trg_sql_mode, &table->mem_root) || + !(trg_definer= (LEX_STRING*) alloc_root(&table->mem_root, + sizeof(LEX_STRING))) || + definers_list.push_back(trg_definer, &table->mem_root)) goto err_with_cleanup; trg_def->str= thd->query; trg_def->length= thd->query_length; *trg_sql_mode= thd->variables.sql_mode; +#ifndef NO_EMBEDDED_ACCESS_CHECKS + if (!is_acl_user(lex->definer->host.str, + lex->definer->user.str)) + { + push_warning_printf(thd, + MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_NO_SUCH_USER, + ER(ER_NO_SUCH_USER), + lex->definer->user.str, + lex->definer->host.str); + } +#endif /* NO_EMBEDDED_ACCESS_CHECKS */ + + *definer_user= lex->definer->user; + *definer_host= lex->definer->host; + + trg_definer->str= trg_definer_holder; + trg_definer->length= strxmov(trg_definer->str, definer_user->str, "@", + definer_host->str, NullS) - trg_definer->str; + if (!sql_create_definition_file(&dir, &file, &triggers_file_type, - (gptr)this, triggers_file_parameters, 3)) + (gptr)this, triggers_file_parameters, + TRG_MAX_VERSIONS)) return 0; err_with_cleanup: @@ -403,12 +512,14 @@ bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables) List_iterator_fast<LEX_STRING> it_name(names_list); List_iterator<LEX_STRING> it_def(definitions_list); List_iterator<ulonglong> it_mod(definition_modes_list); + List_iterator<LEX_STRING> it_definer(definers_list); char path[FN_REFLEN]; while ((name= it_name++)) { it_def++; it_mod++; + it_definer++; if (my_strcasecmp(table_alias_charset, lex->spname->m_name.str, name->str) == 0) @@ -419,6 +530,7 @@ bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables) */ it_def.remove(); it_mod.remove(); + it_definer.remove(); if (definitions_list.is_empty()) { @@ -446,7 +558,7 @@ bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables) if (sql_create_definition_file(&dir, &file, &triggers_file_type, (gptr)this, triggers_file_parameters, - 3)) + TRG_MAX_VERSIONS)) return 1; } @@ -568,7 +680,7 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db, DBUG_RETURN(0); /* - File exists so we got to load triggers + File exists so we got to load triggers. FIXME: A lot of things to do here e.g. how about other funcs and being more paranoical ? */ @@ -584,13 +696,16 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db, DBUG_RETURN(1); /* - We don't have sql_modes in old versions of .TRG file, so we should - initialize list for safety. + We don't have the following attributes in old versions of .TRG file, so + we should initialize the list for safety: + - sql_modes; + - definers; */ triggers->definition_modes_list.empty(); + triggers->definers_list.empty(); if (parser->parse((gptr)triggers, &table->mem_root, - triggers_file_parameters, 2)) + triggers_file_parameters, TRG_NUM_REQUIRED_PARAMETERS)) DBUG_RETURN(1); List_iterator_fast<LEX_STRING> it(triggers->definitions_list); @@ -612,7 +727,7 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db, DBUG_RETURN(1); // EOM } *trg_sql_mode= global_system_variables.sql_mode; - while ((trg_create_str= it++)) + while (it++) { if (triggers->definition_modes_list.push_back(trg_sql_mode, &table->mem_root)) @@ -623,8 +738,43 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db, it.rewind(); } + if (triggers->definers_list.is_empty() && + !triggers->definitions_list.is_empty()) + { + /* + It is old file format => we should fill list of definers. + + If there is no definer information, we should not switch context to + definer when checking privileges. I.e. privileges for such triggers + are checked for "invoker" rather than for "definer". + */ + + LEX_STRING *trg_definer; + + if (! (trg_definer= (LEX_STRING*)alloc_root(&table->mem_root, + sizeof(LEX_STRING)))) + DBUG_RETURN(1); // EOM + + trg_definer->str= ""; + trg_definer->length= 0; + + while (it++) + { + if (triggers->definers_list.push_back(trg_definer, + &table->mem_root)) + { + DBUG_RETURN(1); // EOM + } + } + + it.rewind(); + } + DBUG_ASSERT(triggers->definition_modes_list.elements == triggers->definitions_list.elements); + DBUG_ASSERT(triggers->definers_list.elements == + triggers->definitions_list.elements); + table->triggers= triggers; /* @@ -647,6 +797,8 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db, char *trg_name_buff; List_iterator_fast<ulonglong> itm(triggers->definition_modes_list); + List_iterator_fast<LEX_STRING> it_definer(triggers-> + definers_list); LEX *old_lex= thd->lex, lex; ulong save_sql_mode= thd->variables.sql_mode; @@ -659,22 +811,55 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db, while ((trg_create_str= it++)) { trg_sql_mode= itm++; + LEX_STRING *trg_definer= it_definer++; thd->variables.sql_mode= (ulong)*trg_sql_mode; lex_start(thd, (uchar*)trg_create_str->str, trg_create_str->length); if (yyparse((void *)thd) || thd->is_fatal_error) { /* - Free lex associated resources + Free lex associated resources. QQ: Do we really need all this stuff here ? */ delete lex.sphead; goto err_with_lex_cleanup; } - lex.sphead->m_sql_mode= *trg_sql_mode; + lex.sphead->set_info(0, 0, &lex.sp_chistics, *trg_sql_mode); + triggers->bodies[lex.trg_chistics.event] [lex.trg_chistics.action_time]= lex.sphead; + + if (!trg_definer->length) + { + /* + This trigger was created/imported from the previous version of + MySQL, which does not support triggers definers. We should emit + warning here. + */ + + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRG_NO_DEFINER, ER(ER_TRG_NO_DEFINER), + (const char*) db, + (const char*) lex.sphead->m_name.str); + + /* + Set definer to the '' to correct displaying in the information + schema. + */ + + lex.sphead->set_definer("", 0); + + /* + Triggers without definer information are executed under the + authorization of the invoker. + */ + + lex.sphead->m_chistics->suid= SP_IS_NOT_SUID; + } + else + lex.sphead->set_definer(trg_definer->str, trg_definer->length); + if (triggers->names_list.push_back(&lex.sphead->m_name, &table->mem_root)) goto err_with_lex_cleanup; @@ -701,6 +886,10 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db, trg_field= trg_field->next_trg_field) trg_field->setup_field(thd, table); + triggers->m_spec_var_used[lex.trg_chistics.event] + [lex.trg_chistics.action_time]= + lex.trg_table_fields.first ? TRUE : FALSE; + lex_end(&lex); } thd->db= save_db.str; @@ -744,6 +933,9 @@ err_with_lex_cleanup: name - returns name of trigger stmt - returns statement of trigger sql_mode - returns sql_mode of trigger + definer_user - returns definer/creator of trigger. The caller is + responsible to allocate enough space for storing definer + information. RETURN VALUE False - success @@ -754,7 +946,8 @@ bool Table_triggers_list::get_trigger_info(THD *thd, trg_event_type event, trg_action_time_type time_type, LEX_STRING *trigger_name, LEX_STRING *trigger_stmt, - ulong *sql_mode) + ulong *sql_mode, + LEX_STRING *definer) { sp_head *body; DBUG_ENTER("get_trigger_info"); @@ -763,6 +956,18 @@ bool Table_triggers_list::get_trigger_info(THD *thd, trg_event_type event, *trigger_name= body->m_name; *trigger_stmt= body->m_body; *sql_mode= body->m_sql_mode; + + if (body->m_chistics->suid == SP_IS_NOT_SUID) + { + definer->str[0]= 0; + definer->length= 0; + } + else + { + definer->length= strxmov(definer->str, body->m_definer_user.str, "@", + body->m_definer_host.str, NullS) - definer->str; + } + DBUG_RETURN(0); } DBUG_RETURN(1); @@ -898,8 +1103,9 @@ bool Table_triggers_list::process_triggers(THD *thd, trg_event_type event, bool old_row_is_record1) { int res= 0; + sp_head *sp_trigger= bodies[event][time_type]; - if (bodies[event][time_type]) + if (sp_trigger) { Sub_statement_state statement_state; @@ -914,14 +1120,54 @@ bool Table_triggers_list::process_triggers(THD *thd, trg_event_type event, old_field= table->field; } +#ifndef NO_EMBEDDED_ACCESS_CHECKS + Security_context *save_ctx; + + if (sp_change_security_context(thd, sp_trigger, &save_ctx)) + return TRUE; + /* - FIXME: We should juggle with security context here (because trigger - should be invoked with creator rights). + NOTE: TRIGGER_ACL should be used below. */ + if (check_global_access(thd, SUPER_ACL)) + { + sp_restore_security_context(thd, save_ctx); + return TRUE; + } + + /* + If the trigger uses special variables (NEW/OLD), check that we have + SELECT and UPDATE privileges on the subject table. + */ + + if (is_special_var_used(event, time_type)) + { + TABLE_LIST table_list; + bzero((char *) &table_list, sizeof (table_list)); + table_list.db= (char *) table->s->db; + table_list.db_length= strlen(table_list.db); + table_list.table_name= (char *) table->s->table_name; + table_list.table_name_length= strlen(table_list.table_name); + table_list.alias= (char *) table->alias; + table_list.table= table; + + if (check_table_access(thd, SELECT_ACL | UPDATE_ACL, &table_list, 0)) + { + sp_restore_security_context(thd, save_ctx); + return TRUE; + } + } + +#endif // NO_EMBEDDED_ACCESS_CHECKS + thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER); - res= bodies[event][time_type]->execute_function(thd, 0, 0, 0); + res= sp_trigger->execute_function(thd, 0, 0, 0); thd->restore_sub_statement_state(&statement_state); + +#ifndef NO_EMBEDDED_ACCESS_CHECKS + sp_restore_security_context(thd, save_ctx); +#endif // NO_EMBEDDED_ACCESS_CHECKS } return res; diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h index c1d1f8d0e9e..6be42d7b868 100644 --- a/sql/sql_trigger.h +++ b/sql/sql_trigger.h @@ -55,6 +55,12 @@ class Table_triggers_list: public Sql_alloc */ LEX_STRING sroutines_key; + /* + is_special_var_used specifies whether trigger body contains special + variables (NEW/OLD). + */ + bool m_spec_var_used[TRG_EVENT_MAX][TRG_ACTION_MAX]; + public: /* Field responsible for storing triggers definitions in file. @@ -66,6 +72,8 @@ public: */ List<ulonglong> definition_modes_list; + List<LEX_STRING> definers_list; + Table_triggers_list(TABLE *table_arg): record1_field(0), table(table_arg) { @@ -73,7 +81,9 @@ public: } ~Table_triggers_list(); - bool create_trigger(THD *thd, TABLE_LIST *table); + bool create_trigger(THD *thd, TABLE_LIST *table, + LEX_STRING *definer_user, + LEX_STRING *definer_host); bool drop_trigger(THD *thd, TABLE_LIST *table); bool process_triggers(THD *thd, trg_event_type event, trg_action_time_type time_type, @@ -81,7 +91,8 @@ public: bool get_trigger_info(THD *thd, trg_event_type event, trg_action_time_type time_type, LEX_STRING *trigger_name, LEX_STRING *trigger_stmt, - ulong *sql_mode); + ulong *sql_mode, + LEX_STRING *definer); static bool check_n_load(THD *thd, const char *db, const char *table_name, TABLE *table, bool names_only); @@ -98,6 +109,11 @@ public: return test(bodies[TRG_EVENT_UPDATE][TRG_ACTION_BEFORE]); } + inline bool is_special_var_used(int event, int action_time) const + { + return m_spec_var_used[event][action_time]; + } + void set_table(TABLE *new_table); friend class Item_trigger_field; diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 71c5d198b27..b642d24b30d 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -214,29 +214,28 @@ bool mysql_create_view(THD *thd, - same as current user - current user has SUPER_ACL */ - if (strcmp(lex->create_view_definer->user.str, + if (strcmp(lex->definer->user.str, thd->security_ctx->priv_user) != 0 || my_strcasecmp(system_charset_info, - lex->create_view_definer->host.str, + lex->definer->host.str, thd->security_ctx->priv_host) != 0) { if (!(thd->security_ctx->master_access & SUPER_ACL)) { - my_error(ER_VIEW_OTHER_USER, MYF(0), lex->create_view_definer->user.str, - lex->create_view_definer->host.str); + my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "SUPER"); res= TRUE; goto err; } else { - if (!is_acl_user(lex->create_view_definer->host.str, - lex->create_view_definer->user.str)) + if (!is_acl_user(lex->definer->host.str, + lex->definer->user.str)) { push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, ER_NO_SUCH_USER, ER(ER_NO_SUCH_USER), - lex->create_view_definer->user.str, - lex->create_view_definer->host.str); + lex->definer->user.str, + lex->definer->host.str); } } } @@ -658,8 +657,8 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } view->algorithm= lex->create_view_algorithm; - view->definer.user= lex->create_view_definer->user; - view->definer.host= lex->create_view_definer->host; + view->definer.user= lex->definer->user; + view->definer.host= lex->definer->host; view->view_suid= lex->create_view_suid; view->with_check= lex->create_view_check; if ((view->updatable_view= (can_be_merged && @@ -807,7 +806,7 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table) push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_VIEW_FRM_NO_USER, ER(ER_VIEW_FRM_NO_USER), table->db, table->table_name); - if (default_view_definer(thd->security_ctx, &table->definer)) + if (get_default_definer(thd, &table->definer)) goto err; } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 109dcd7e86a..55002def5e9 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -776,7 +776,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %type <symbol> FUNC_ARG0 FUNC_ARG1 FUNC_ARG2 FUNC_ARG3 keyword keyword_sp -%type <lex_user> user grant_user +%type <lex_user> user grant_user get_definer %type <charset> opt_collate @@ -827,10 +827,12 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); subselect_end select_var_list select_var_list_init help opt_len opt_extended_describe prepare prepare_src execute deallocate - statement sp_suid opt_view_list view_list or_replace algorithm + statement sp_suid sp_c_chistics sp_a_chistics sp_chistic sp_c_chistic xa load_data opt_field_or_var_spec fields_or_vars opt_load_data_set_spec - view_user view_suid + definer view_replace_or_algorithm view_replace view_algorithm_opt + view_algorithm view_or_trigger_tail view_suid view_tail view_list_opt + view_list view_select view_check_option trigger_tail END_OF_INPUT %type <NONE> call sp_proc_stmts sp_proc_stmts1 sp_proc_stmt @@ -1258,80 +1260,14 @@ create: YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES; sp->restore_thd_mem_root(YYTHD); } - | CREATE or_replace algorithm view_user view_suid VIEW_SYM table_ident + | CREATE { - THD *thd= YYTHD; - LEX *lex= thd->lex; - lex->sql_command= SQLCOM_CREATE_VIEW; - lex->create_view_start= thd->query; - /* first table in list is target VIEW name */ - if (!lex->select_lex.add_table_to_list(thd, $7, NULL, 0)) - YYABORT; + Lex->create_view_mode= VIEW_CREATE_NEW; + Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; + Lex->create_view_suid= TRUE; } - opt_view_list AS select_view_init check_option + view_or_trigger {} - | CREATE TRIGGER_SYM sp_name trg_action_time trg_event - ON table_ident FOR_SYM EACH_SYM ROW_SYM - { - LEX *lex= Lex; - sp_head *sp; - - if (lex->sphead) - { - my_error(ER_SP_NO_RECURSIVE_CREATE, MYF(0), "TRIGGER"); - YYABORT; - } - - if (!(sp= new sp_head())) - YYABORT; - sp->reset_thd_mem_root(YYTHD); - sp->init(lex); - - sp->m_type= TYPE_ENUM_TRIGGER; - lex->sphead= sp; - lex->spname= $3; - /* - We have to turn of CLIENT_MULTI_QUERIES while parsing a - stored procedure, otherwise yylex will chop it into pieces - at each ';'. - */ - sp->m_old_cmq= YYTHD->client_capabilities & CLIENT_MULTI_QUERIES; - YYTHD->client_capabilities &= ~CLIENT_MULTI_QUERIES; - - bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics)); - lex->sphead->m_chistics= &lex->sp_chistics; - lex->sphead->m_body_begin= lex->ptr; - } - sp_proc_stmt - { - LEX *lex= Lex; - sp_head *sp= lex->sphead; - - lex->sql_command= SQLCOM_CREATE_TRIGGER; - sp->init_strings(YYTHD, lex, $3); - /* Restore flag if it was cleared above */ - if (sp->m_old_cmq) - YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES; - sp->restore_thd_mem_root(YYTHD); - - if (sp->is_not_allowed_in_function("trigger")) - YYABORT; - - /* - We have to do it after parsing trigger body, because some of - sp_proc_stmt alternatives are not saving/restoring LEX, so - lex->query_tables can be wiped out. - - QQ: What are other consequences of this? - - QQ: Could we loosen lock type in certain cases ? - */ - if (!lex->select_lex.add_table_to_list(YYTHD, $7, - (LEX_STRING*) 0, - TL_OPTION_UPDATING, - TL_WRITE)) - YYABORT; - } | CREATE USER clear_privileges grant_list { Lex->sql_command = SQLCOM_CREATE_USER; @@ -3435,7 +3371,8 @@ alter: lex->sql_command= SQLCOM_ALTER_FUNCTION; lex->spname= $3; } - | ALTER algorithm view_user view_suid VIEW_SYM table_ident + | ALTER view_algorithm_opt definer view_suid + VIEW_SYM table_ident { THD *thd= YYTHD; LEX *lex= thd->lex; @@ -3445,7 +3382,7 @@ alter: /* first table in list is target VIEW name */ lex->select_lex.add_table_to_list(thd, $6, NULL, 0); } - opt_view_list AS select_view_init check_option + view_list_opt AS view_select view_check_option {} ; @@ -4013,18 +3950,6 @@ select_init: | '(' select_paren ')' union_opt; -select_view_init: - SELECT_SYM remember_name select_init2 - { - Lex->create_view_select_start= $2; - } - | - '(' remember_name select_paren ')' union_opt - { - Lex->create_view_select_start= $2; - } - ; - select_paren: SELECT_SYM select_part2 { @@ -8963,8 +8888,119 @@ subselect_end: lex->current_select = lex->current_select->return_after_parsing(); }; -opt_view_list: - /* empty */ {} +definer: + get_definer + { + THD *thd= YYTHD; + + if (! (thd->lex->definer= create_definer(thd, &$1->user, &$1->host))) + YYABORT; + } + ; + +get_definer: + opt_current_definer + { + THD *thd= YYTHD; + + if (!($$=(LEX_USER*) thd->alloc(sizeof(st_lex_user)))) + YYABORT; + + if (get_default_definer(thd, $$)) + YYABORT; + } + | DEFINER_SYM EQ ident_or_text '@' ident_or_text + { + if (!($$=(LEX_USER*) YYTHD->alloc(sizeof(st_lex_user)))) + YYABORT; + + $$->user= $3; + $$->host= $5; + } + ; + +opt_current_definer: + /* empty */ + | DEFINER_SYM EQ CURRENT_USER optional_braces + ; + +/************************************************************************** + + CREATE VIEW statement options. + +**************************************************************************/ + +view_replace_or_algorithm: + view_replace + {} + | view_replace view_algorithm + {} + | view_algorithm + {} + ; + +view_replace: + OR_SYM REPLACE + { Lex->create_view_mode= VIEW_CREATE_OR_REPLACE; } + ; + +view_algorithm: + ALGORITHM_SYM EQ UNDEFINED_SYM + { Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } + | ALGORITHM_SYM EQ MERGE_SYM + { Lex->create_view_algorithm= VIEW_ALGORITHM_MERGE; } + | ALGORITHM_SYM EQ TEMPTABLE_SYM + { Lex->create_view_algorithm= VIEW_ALGORITHM_TMPTABLE; } + ; + +view_algorithm_opt: + /* empty */ + { Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } + | view_algorithm + {} + ; + +view_or_trigger: + definer view_or_trigger_tail + {} + | view_replace_or_algorithm definer view_tail + {} + ; + +view_or_trigger_tail: + view_tail + {} + | trigger_tail + {} + ; + +view_suid: + /* empty */ + { Lex->create_view_suid= TRUE; } + | SQL_SYM SECURITY_SYM DEFINER_SYM + { Lex->create_view_suid= TRUE; } + | SQL_SYM SECURITY_SYM INVOKER_SYM + { Lex->create_view_suid= FALSE; } + ; + +view_tail: + view_suid VIEW_SYM table_ident + { + THD *thd= YYTHD; + LEX *lex= thd->lex; + lex->sql_command= SQLCOM_CREATE_VIEW; + lex->create_view_start= thd->query; + /* first table in list is target VIEW name */ + if (!lex->select_lex.add_table_to_list(thd, $3, NULL, 0)) + YYABORT; + } + view_list_opt AS view_select view_check_option + {} + ; + +view_list_opt: + /* empty */ + {} | '(' view_list ')' ; @@ -8981,79 +9017,102 @@ view_list: } ; -or_replace: - /* empty */ { Lex->create_view_mode= VIEW_CREATE_NEW; } - | OR_SYM REPLACE { Lex->create_view_mode= VIEW_CREATE_OR_REPLACE; } +view_select: + SELECT_SYM remember_name select_init2 + { + Lex->create_view_select_start= $2; + } + | '(' remember_name select_paren ')' union_opt + { + Lex->create_view_select_start= $2; + } ; -algorithm: +view_check_option: /* empty */ - { Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } - | ALGORITHM_SYM EQ UNDEFINED_SYM - { Lex->create_view_algorithm= VIEW_ALGORITHM_UNDEFINED; } - | ALGORITHM_SYM EQ MERGE_SYM - { Lex->create_view_algorithm= VIEW_ALGORITHM_MERGE; } - | ALGORITHM_SYM EQ TEMPTABLE_SYM - { Lex->create_view_algorithm= VIEW_ALGORITHM_TMPTABLE; } + { Lex->create_view_check= VIEW_CHECK_NONE; } + | WITH CHECK_SYM OPTION + { Lex->create_view_check= VIEW_CHECK_CASCADED; } + | WITH CASCADED CHECK_SYM OPTION + { Lex->create_view_check= VIEW_CHECK_CASCADED; } + | WITH LOCAL_SYM CHECK_SYM OPTION + { Lex->create_view_check= VIEW_CHECK_LOCAL; } ; -view_user: - /* empty */ - { - THD *thd= YYTHD; - if (!(thd->lex->create_view_definer= - (LEX_USER*) thd->alloc(sizeof(st_lex_user)))) - YYABORT; - if (default_view_definer(thd->security_ctx, - thd->lex->create_view_definer)) - YYABORT; - } - | CURRENT_USER optional_braces - { - THD *thd= YYTHD; - if (!(thd->lex->create_view_definer= - (LEX_USER*) thd->alloc(sizeof(st_lex_user)))) - YYABORT; - if (default_view_definer(thd->security_ctx, - thd->lex->create_view_definer)) - YYABORT; - } - | DEFINER_SYM EQ ident_or_text '@' ident_or_text +/************************************************************************** + + CREATE TRIGGER statement parts. + +**************************************************************************/ + +trigger_tail: + TRIGGER_SYM remember_name sp_name trg_action_time trg_event + ON table_ident FOR_SYM EACH_SYM ROW_SYM + { + LEX *lex= Lex; + sp_head *sp; + + if (lex->sphead) { - THD *thd= YYTHD; - st_lex_user *view_user; - if (!(thd->lex->create_view_definer= view_user= - (LEX_USER*) thd->alloc(sizeof(st_lex_user)))) - YYABORT; - view_user->user = $3; view_user->host=$5; - if (view_user->host.length == 0) - { - my_error(ER_NO_VIEW_USER, MYF(0)); - YYABORT; - } + my_error(ER_SP_NO_RECURSIVE_CREATE, MYF(0), "TRIGGER"); + YYABORT; } - ; - -view_suid: - /* empty */ - { Lex->create_view_suid= TRUE; } - | - SQL_SYM SECURITY_SYM DEFINER_SYM - { Lex->create_view_suid= TRUE; } - | SQL_SYM SECURITY_SYM INVOKER_SYM - { Lex->create_view_suid= FALSE; } + + if (!(sp= new sp_head())) + YYABORT; + sp->reset_thd_mem_root(YYTHD); + sp->init(lex); + + lex->trigger_definition_begin= $2; + + sp->m_type= TYPE_ENUM_TRIGGER; + lex->sphead= sp; + lex->spname= $3; + /* + We have to turn of CLIENT_MULTI_QUERIES while parsing a + stored procedure, otherwise yylex will chop it into pieces + at each ';'. + */ + sp->m_old_cmq= YYTHD->client_capabilities & CLIENT_MULTI_QUERIES; + YYTHD->client_capabilities &= ~CLIENT_MULTI_QUERIES; + + bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics)); + lex->sphead->m_chistics= &lex->sp_chistics; + lex->sphead->m_body_begin= lex->ptr; + } + sp_proc_stmt + { + LEX *lex= Lex; + sp_head *sp= lex->sphead; + + lex->sql_command= SQLCOM_CREATE_TRIGGER; + sp->init_strings(YYTHD, lex, $3); + /* Restore flag if it was cleared above */ + if (sp->m_old_cmq) + YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES; + sp->restore_thd_mem_root(YYTHD); + + if (sp->is_not_allowed_in_function("trigger")) + YYABORT; + + /* + We have to do it after parsing trigger body, because some of + sp_proc_stmt alternatives are not saving/restoring LEX, so + lex->query_tables can be wiped out. + + QQ: What are other consequences of this? + + QQ: Could we loosen lock type in certain cases ? + */ + if (!lex->select_lex.add_table_to_list(YYTHD, $7, + (LEX_STRING*) 0, + TL_OPTION_UPDATING, + TL_WRITE)) + YYABORT; + } ; -check_option: - /* empty */ - { Lex->create_view_check= VIEW_CHECK_NONE; } - | WITH CHECK_SYM OPTION - { Lex->create_view_check= VIEW_CHECK_CASCADED; } - | WITH CASCADED CHECK_SYM OPTION - { Lex->create_view_check= VIEW_CHECK_CASCADED; } - | WITH LOCAL_SYM CHECK_SYM OPTION - { Lex->create_view_check= VIEW_CHECK_LOCAL; } - ; +/*************************************************************************/ xa: XA_SYM begin_or_start xid opt_join_or_resume { diff --git a/strings/decimal.c b/strings/decimal.c index f536bdb1d6b..0c1f03016e0 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -973,7 +973,7 @@ int double2decimal(double from, decimal_t *to) { /* TODO: fix it, when we'll have dtoa */ char s[400], *end; - sprintf(s, "%f", from); + sprintf(s, "%.16G", from); end= strend(s); return string2decimal(s, to, &end); } diff --git a/support-files/mysql.spec.sh b/support-files/mysql.spec.sh index 7b2eee5f821..991afd367c0 100644 --- a/support-files/mysql.spec.sh +++ b/support-files/mysql.spec.sh @@ -259,7 +259,6 @@ sh -c "PATH=\"${MYSQL_BUILD_PATH:-$PATH}\" \ --includedir=%{_includedir} \ --mandir=%{_mandir} \ --enable-thread-safe-client \ - --with-zlib-dir=bundled \ --with-readline ; \ # Add this for more debugging support # --with-debug @@ -362,8 +361,9 @@ BuildMySQL "--disable-shared \ %if %{STATIC_BUILD} --with-mysqld-ldflags='-all-static' \ --with-client-ldflags='-all-static' \ - --with-zlib-dir=bundled \ $USE_OTHER_LIBC_DIR \ +%else + --with-zlib-dir=bundled \ %endif --with-comment=\"MySQL Community Edition - Standard (GPL)\" \ --with-server-suffix='%{server_suffix}' \ diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index 949c3d5bf1c..b065a0dfa03 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -14419,6 +14419,74 @@ static void test_bug14210() myquery(rc); } +/* Bug#13488 */ + +static void test_bug13488() +{ + MYSQL_BIND bind[3]; + MYSQL_STMT *stmt1; + int rc, f1, f2, f3, i; + const ulong type= CURSOR_TYPE_READ_ONLY; + const char *query= "select * from t1 left join t2 on f1=f2 where f1=1"; + + myheader("test_bug13488"); + + rc= mysql_query(mysql, "drop table if exists t1, t2"); + myquery(rc); + rc= mysql_query(mysql, "create table t1 (f1 int not null primary key)"); + myquery(rc); + rc= mysql_query(mysql, "create table t2 (f2 int not null primary key, " + "f3 int not null)"); + myquery(rc); + rc= mysql_query(mysql, "insert into t1 values (1), (2)"); + myquery(rc); + rc= mysql_query(mysql, "insert into t2 values (1,2), (2,4)"); + myquery(rc); + + memset(bind, 0, sizeof(bind)); + for (i= 0; i < 3; i++) + { + bind[i].buffer_type= MYSQL_TYPE_LONG; + bind[i].buffer_length= 4; + bind[i].length= 0; + } + bind[0].buffer=&f1; + bind[1].buffer=&f2; + bind[2].buffer=&f3; + + stmt1= mysql_stmt_init(mysql); + rc= mysql_stmt_attr_set(stmt1,STMT_ATTR_CURSOR_TYPE, (const void *)&type); + check_execute(stmt1, rc); + + rc= mysql_stmt_prepare(stmt1, query, strlen(query)); + check_execute(stmt1, rc); + + rc= mysql_stmt_execute(stmt1); + check_execute(stmt1, rc); + + rc= mysql_stmt_bind_result(stmt1, bind); + check_execute(stmt1, rc); + + rc= mysql_stmt_fetch(stmt1); + check_execute(stmt1, rc); + + rc= mysql_stmt_free_result(stmt1); + check_execute(stmt1, rc); + + rc= mysql_stmt_reset(stmt1); + check_execute(stmt1, rc); + + rc= mysql_stmt_close(stmt1); + check_execute(stmt1, rc); + + if (!opt_silent) + printf("data is: %s", (f1 == 1 && f2 == 1 && f3 == 2)?"OK": + "wrong"); + DIE_UNLESS(f1 == 1 && f2 == 1 && f3 == 2); + rc= mysql_query(mysql, "drop table t1, t2"); + myquery(rc); +} + /* Read and parse arguments and MySQL options from my.cnf */ @@ -14675,6 +14743,7 @@ static struct my_tests_st my_tests[]= { { "test_bug11904", test_bug11904 }, { "test_bug12243", test_bug12243 }, { "test_bug14210", test_bug14210 }, + { "test_bug13488", test_bug13488 }, { 0, 0 } }; |