diff options
23 files changed, 1011 insertions, 147 deletions
diff --git a/mysql-test/r/create_drop_binlog.result b/mysql-test/r/create_drop_binlog.result index 6aa20a8d4e1..32638c2c053 100644 --- a/mysql-test/r/create_drop_binlog.result +++ b/mysql-test/r/create_drop_binlog.result @@ -27,6 +27,111 @@ Log_name Pos Event_type Server_id End_log_pos Info # # Query 1 # DROP DATABASE IF EXISTS d1 RESET MASTER; USE test; +CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 1; +CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 1; +DROP FUNCTION f1; +CREATE FUNCTION IF NOT EXISTS f1() RETURNS INT RETURN 1; +CREATE FUNCTION IF NOT EXISTS f1() RETURNS INT RETURN 1; +Warnings: +Note 1304 FUNCTION f1 already exists +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f1; +Warnings: +Note 1305 FUNCTION test.f1 does not exist +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +# # Format_desc 1 # VER +# # Gtid_list 1 # [] +# # Binlog_checkpoint 1 # master-bin.000001 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE OR REPLACE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) +RETURN 1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE OR REPLACE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) +RETURN 1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; DROP FUNCTION f1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION IF NOT EXISTS `f1`() RETURNS int(11) +RETURN 1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION IF NOT EXISTS `f1`() RETURNS int(11) +RETURN 1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; DROP FUNCTION IF EXISTS f1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; DROP FUNCTION IF EXISTS f1 +RESET MASTER; +CREATE TABLE t1 (a DATETIME); +CREATE OR REPLACE PROCEDURE p1() DELETE FROM t1; +CREATE OR REPLACE PROCEDURE p1() DELETE FROM t1; +DROP PROCEDURE p1; +CREATE PROCEDURE IF NOT EXISTS p1() DELETE FROM t1; +CREATE PROCEDURE IF NOT EXISTS p1() DELETE FROM t1; +Warnings: +Note 1304 PROCEDURE p1 already exists +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p1; +Warnings: +Note 1305 PROCEDURE test.p1 does not exist +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +# # Format_desc 1 # VER +# # Gtid_list 1 # [] +# # Binlog_checkpoint 1 # master-bin.000001 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE TABLE t1 (a DATETIME) +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `p1`() +DELETE FROM t1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `p1`() +DELETE FROM t1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; DROP PROCEDURE p1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE IF NOT EXISTS `p1`() +DELETE FROM t1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE IF NOT EXISTS `p1`() +DELETE FROM t1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; DROP PROCEDURE IF EXISTS p1 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; DROP PROCEDURE IF EXISTS p1 +DROP TABLE t1; +RESET MASTER; +CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME 'UDF_EXAMPLE_LIB'; +CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME 'UDF_EXAMPLE_LIB'; +DROP FUNCTION metaphon; +CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME 'UDF_EXAMPLE_LIB'; +CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME 'UDF_EXAMPLE_LIB'; +Warnings: +Note 1125 Function 'metaphon' already exists +DROP FUNCTION IF EXISTS metaphon; +DROP FUNCTION IF EXISTS metaphon; +Warnings: +Note 1305 FUNCTION test.metaphon does not exist +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +# # Format_desc 1 # VER +# # Gtid_list 1 # [] +# # Binlog_checkpoint 1 # master-bin.000001 +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME 'UDM_EXAMPLE_LIB' +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME 'UDM_EXAMPLE_LIB' +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; DROP FUNCTION metaphon +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME 'UDM_EXAMPLE_LIB' +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME 'UDM_EXAMPLE_LIB' +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; DROP FUNCTION IF EXISTS metaphon +# # Gtid 1 # GTID #-#-# +# # Query 1 # use `test`; DROP FUNCTION IF EXISTS metaphon +RESET MASTER; # # CREATE SERVER is not logged # diff --git a/mysql-test/r/create_drop_function.result b/mysql-test/r/create_drop_function.result new file mode 100644 index 00000000000..f358c24461a --- /dev/null +++ b/mysql-test/r/create_drop_function.result @@ -0,0 +1,54 @@ +SET timestamp=UNIX_TIMESTAMP('2014-09-30 08:00:00'); +CREATE FUNCTION f1(str char(20)) +RETURNS CHAR(100) +RETURN CONCAT('Hello, ', str, '!'); +SELECT * FROM mysql.proc WHERE name like 'f1'; +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +test f1 FUNCTION f1 SQL CONTAINS_SQL NO DEFINER str char(20) char(100) CHARSET latin1 RETURN CONCAT('Hello, ', str, '!') root@localhost 2014-09-30 08:00:00 2014-09-30 08:00:00 latin1 latin1_swedish_ci latin1_swedish_ci RETURN CONCAT('Hello, ', str, '!') +SELECT f1('world'); +f1('world') +Hello, world! +CREATE FUNCTION f1(str char(20)) +RETURNS TEXT +RETURN CONCAT('Hello2, ', str, '!'); +ERROR 42000: FUNCTION f1 already exists +SELECT body FROM mysql.proc WHERE name like 'f1'; +body +RETURN CONCAT('Hello, ', str, '!') +CREATE FUNCTION IF NOT EXISTS f1(str char(20)) +RETURNS CHAR(100) +RETURN CONCAT('Hello3, ', str, '!'); +Warnings: +Note 1304 FUNCTION f1 already exists +SELECT body FROM mysql.proc WHERE name like 'f1'; +body +RETURN CONCAT('Hello, ', str, '!') +CREATE OR REPLACE FUNCTION IF NOT EXISTS f1(str char(20)) +RETURNS CHAR(100) +RETURN CONCAT('Hello4, ', str, '!'); +ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS +SELECT body FROM mysql.proc WHERE name like 'f1'; +body +RETURN CONCAT('Hello, ', str, '!') +CREATE OR REPLACE FUNCTION f1(str char(20)) +RETURNS CHAR(100) +RETURN CONCAT('Hello5, ', str, '!'); +SELECT body FROM mysql.proc WHERE name like 'f1'; +body +RETURN CONCAT('Hello5, ', str, '!') +DROP FUNCTION f1; +CREATE FUNCTION IF NOT EXISTS f1(str char(20)) +RETURNS CHAR(100) +RETURN CONCAT('Hello6, ', str, '!'); +SELECT body FROM mysql.proc WHERE name like 'f1'; +body +RETURN CONCAT('Hello6, ', str, '!') +SELECT f1('world'); +f1('world') +Hello6, world! +DROP FUNCTION IF EXISTS f1; +SELECT body FROM mysql.proc WHERE name like 'f1'; +body +DROP FUNCTION IF EXISTS f1; +Warnings: +Note 1305 FUNCTION test.f1 does not exist diff --git a/mysql-test/r/create_drop_procedure.result b/mysql-test/r/create_drop_procedure.result new file mode 100644 index 00000000000..02c71ab6446 --- /dev/null +++ b/mysql-test/r/create_drop_procedure.result @@ -0,0 +1,47 @@ +CREATE TABLE t1 (id INT); +CREATE PROCEDURE proc1 (OUT cnt INT) COMMENT 'comment1' BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +CALL proc1(@cnt); +SELECT @cnt; +@cnt +0 +INSERT INTO t1 VALUES (1), (2), (3); +CALL proc1(@cnt); +SELECT @cnt; +@cnt +3 +CREATE PROCEDURE proc1 (OUT cnt INT) COMMENT 'comment2' + BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +ERROR 42000: PROCEDURE proc1 already exists +SELECT comment FROM mysql.proc WHERE name='proc1'$$ +comment +comment1 +CREATE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) COMMENT 'comment3' + BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +Warnings: +Note 1304 PROCEDURE proc1 already exists +SELECT comment FROM mysql.proc WHERE name='proc1'$$ +comment +comment1 +CREATE OR REPLACE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) COMMENT 'comment4' + BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS +SELECT comment FROM mysql.proc WHERE name='proc1'$$ +comment +comment1 +CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) COMMENT 'comment5' + BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +SELECT comment FROM mysql.proc WHERE name='proc1'$$ +comment +comment5 +DROP PROCEDURE proc1; +CREATE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +INSERT INTO t1 VALUES (1), (2), (3); +CALL proc1(@cnt); +SELECT @cnt; +@cnt +6 +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS proc1; +DROP PROCEDURE IF EXISTS proc1; +Warnings: +Note 1305 PROCEDURE test.proc1 does not exist diff --git a/mysql-test/r/create_drop_udf.result b/mysql-test/r/create_drop_udf.result new file mode 100644 index 00000000000..1024be9e30b --- /dev/null +++ b/mysql-test/r/create_drop_udf.result @@ -0,0 +1,38 @@ +CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +SELECT ret FROM mysql.func WHERE name like 'metaphon'; +ret +0 +CREATE FUNCTION metaphon RETURNS INT SONAME "$UDF_EXAMPLE_SO"; +ERROR HY000: Function 'metaphon' already exists +SELECT ret FROM mysql.func WHERE name like 'metaphon'; +ret +0 +CREATE FUNCTION IF NOT EXISTS metaphon RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; +Warnings: +Note 1125 Function 'metaphon' already exists +SELECT ret FROM mysql.func WHERE name like 'metaphon'; +ret +0 +DROP FUNCTION IF EXISTS metaphon; +CREATE FUNCTION IF NOT EXISTS metaphon RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; +SELECT ret FROM mysql.func WHERE name like 'metaphon'; +ret +1 +CREATE OR REPLACE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME "udf_example.so"; +ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS +SELECT ret FROM mysql.func WHERE name like 'metaphon'; +ret +1 +CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +SELECT ret FROM mysql.func WHERE name like 'metaphon'; +ret +0 +CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so"; +ERROR HY000: Function 'metaphon' already exists +SELECT metaphon('mariadb'); +metaphon('mariadb') +MRTB +DROP FUNCTION metaphon; +DROP FUNCTION IF EXISTS metaphon; +Warnings: +Note 1305 FUNCTION test.metaphon does not exist diff --git a/mysql-test/r/create_or_replace_permission.result b/mysql-test/r/create_or_replace_permission.result index 0731cbbc79e..b94f2443d77 100644 --- a/mysql-test/r/create_or_replace_permission.result +++ b/mysql-test/r/create_or_replace_permission.result @@ -29,6 +29,12 @@ ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'db2' USE db1; CREATE OR REPLACE TABLE t1(id INT); ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1' +CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) BEGIN END; +ERROR 42000: alter routine command denied to user 'mysqltest_1'@'localhost' for routine 'db1.proc1' +CREATE OR REPLACE FUNCTION lookup RETURNS STRING SONAME "udf_example.so"; +ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysql' +CREATE OR REPLACE FUNCTION hello(str char(20)) RETURNS TEXT RETURN CONCAT('Hello, ', str, '!'); +ERROR 42000: alter routine command denied to user 'mysqltest_1'@'localhost' for routine 'db1.hello' SELECT CURRENT_USER; CURRENT_USER root@localhost diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 7569673c68c..4373925b8ac 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1223,9 +1223,7 @@ END' at line 1 CREATE PROCEDURE IF NOT EXISTS bug14702() BEGIN END; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF NOT EXISTS bug14702() -BEGIN -END' at line 1 +DROP PROCEDURE IF EXISTS bug14702; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (i INT); CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO @a; diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result index f316bdb7ba2..cf3bc923b19 100644 --- a/mysql-test/suite/funcs_1/r/storedproc.result +++ b/mysql-test/suite/funcs_1/r/storedproc.result @@ -2262,7 +2262,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * from t1 where f2=f1' at line 1 CREATE PROCEDURE if() SELECT * from t1 where f2=f1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'if() +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '() SELECT * from t1 where f2=f1' at line 1 CREATE PROCEDURE ignore() SELECT * from t1 where f2=f1; diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_function.result b/mysql-test/suite/rpl/r/rpl_create_drop_function.result new file mode 100644 index 00000000000..66b1f485aa7 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_create_drop_function.result @@ -0,0 +1,50 @@ +include/master-slave.inc +[connection master] +CREATE FUNCTION hello (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Hello, ', str, '!'); +CREATE FUNCTION hello (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Hello, ', str, '!'); +ERROR 42000: FUNCTION hello already exists +SHOW CREATE FUNCTION hello; +Function sql_mode Create Function character_set_client collation_connection Database Collation +hello CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(str CHAR(20)) RETURNS char(50) CHARSET latin1 + DETERMINISTIC +RETURN +CONCAT('Hello, ', str, '!') latin1 latin1_swedish_ci latin1_swedish_ci +CREATE OR REPLACE FUNCTION IF NOT EXISTS hello (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Hello, ', str, '!'); +ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS +CREATE OR REPLACE FUNCTION hello (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Hello, ', str, '!'); +SELECT hello('master'); +hello('master') +Hello, master! +SELECT hello('slave'); +hello('slave') +Hello, slave! +CREATE FUNCTION IF NOT EXISTS hello (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Hello, ', str, '!'); +Warnings: +Note 1304 FUNCTION hello already exists +CREATE OR REPLACE FUNCTION bye (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Bye, ', str, '!'); +SELECT hello('master'); +hello('master') +Hello, master! +SELECT bye('master'); +bye('master') +Bye, master! +SELECT hello('slave'); +hello('slave') +Hello, slave! +SELECT bye('slave'); +bye('slave') +Bye, slave! +DROP FUNCTION hello; +DROP FUNCTION IF EXISTS bye; +DROP FUNCTION hello; +ERROR 42000: FUNCTION test.hello does not exist +DROP FUNCTION IF EXISTS bye; +Warnings: +Note 1305 FUNCTION test.bye does not exist +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_procedure.result b/mysql-test/suite/rpl/r/rpl_create_drop_procedure.result new file mode 100644 index 00000000000..5fc7d582cb0 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_create_drop_procedure.result @@ -0,0 +1,64 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1 (id INT); +CREATE PROCEDURE proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +CALL proc1(@cnt); +SELECT @cnt; +@cnt +0 +INSERT INTO t1 VALUES (1), (2), (3); +CALL proc1(@cnt); +SELECT @cnt; +@cnt +3 +# Syncing with slave +CALL proc1(@cnt); +SELECT @cnt; +@cnt +3 +CREATE PROCEDURE proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +ERROR 42000: PROCEDURE proc1 already exists +CREATE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +Warnings: +Note 1304 PROCEDURE proc1 already exists +CREATE OR REPLACE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS +CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +# Syncing with slave +CALL proc1(@cnt); +SELECT @cnt; +@cnt +3 +DROP PROCEDURE proc1; +CREATE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +INSERT INTO t1 VALUES (1), (2), (3); +CALL proc1(@cnt); +SELECT @cnt; +@cnt +6 +# Syncing with slave +CALL proc1(@cnt); +SELECT @cnt; +@cnt +6 +DROP PROCEDURE proc1; +CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +INSERT INTO t1 VALUES (1), (2), (3); +CALL proc1(@cnt); +SELECT @cnt; +@cnt +9 +# Syncing with slave +CALL proc1(@cnt); +SELECT @cnt; +@cnt +9 +DROP TABLE IF EXISTS t1; +DROP PROCEDURE proc1; +DROP PROCEDURE IF EXISTS proc2; +Warnings: +Note 1305 PROCEDURE test.proc2 does not exist +# Syncing with slave +DROP PROCEDURE proc1; +ERROR 42000: PROCEDURE test.proc1 does not exist +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_udf.result b/mysql-test/suite/rpl/r/rpl_create_drop_udf.result new file mode 100644 index 00000000000..6c6b766d66d --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_create_drop_udf.result @@ -0,0 +1,42 @@ +include/master-slave.inc +[connection master] +CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +SELECT metaphon('master'); +metaphon('master') +MSTR +CREATE FUNCTION metaphon RETURNS INT SONAME "udf_example.so"; +ERROR HY000: Function 'metaphon' already exists +SELECT metaphon('slave'); +metaphon('slave') +SLF +CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME "udf_example.so"; +Warnings: +Note 1125 Function 'metaphon' already exists +DROP FUNCTION IF EXISTS random_function_name; +Warnings: +Note 1305 FUNCTION test.random_function_name does not exist +CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME "udf_example.so"; +Warnings: +Note 1125 Function 'metaphon' already exists +CREATE OR REPLACE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME "udf_example.so"; +ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS +SELECT metaphon('slave'); +metaphon('slave') +SLF +DROP FUNCTION metaphon; +CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so"; +CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so"; +ERROR HY000: Function 'metaphon' already exists +SELECT metaphon('slave'); +metaphon('slave') +SLF +DROP FUNCTION metaphon; +DROP FUNCTION IF EXISTS metaphon; +Warnings: +Note 1305 FUNCTION test.metaphon does not exist +DROP FUNCTION metaphon; +ERROR 42000: FUNCTION test.metaphon does not exist +DROP FUNCTION IF EXISTS metaphon; +Warnings: +Note 1305 FUNCTION test.metaphon does not exist +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_create_drop_function.test b/mysql-test/suite/rpl/t/rpl_create_drop_function.test new file mode 100644 index 00000000000..5ae0b76554a --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_create_drop_function.test @@ -0,0 +1,54 @@ +--source include/master-slave.inc + +connection master; + +CREATE FUNCTION hello (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Hello, ', str, '!'); + +--error ER_SP_ALREADY_EXISTS +CREATE FUNCTION hello (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Hello, ', str, '!'); + +sync_slave_with_master; + +SHOW CREATE FUNCTION hello; + +connection master; + +--error ER_WRONG_USAGE +CREATE OR REPLACE FUNCTION IF NOT EXISTS hello (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Hello, ', str, '!'); + +CREATE OR REPLACE FUNCTION hello (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Hello, ', str, '!'); + +SELECT hello('master'); +sync_slave_with_master; + +SELECT hello('slave'); + +connection master; +CREATE FUNCTION IF NOT EXISTS hello (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Hello, ', str, '!'); + +CREATE OR REPLACE FUNCTION bye (str CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN +CONCAT('Bye, ', str, '!'); + +SELECT hello('master'); +SELECT bye('master'); +sync_slave_with_master; + +SELECT hello('slave'); +SELECT bye('slave'); + +connection master; +DROP FUNCTION hello; +DROP FUNCTION IF EXISTS bye; +sync_slave_with_master; + +--error ER_SP_DOES_NOT_EXIST +DROP FUNCTION hello; + +DROP FUNCTION IF EXISTS bye; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_create_drop_procedure.test b/mysql-test/suite/rpl/t/rpl_create_drop_procedure.test new file mode 100644 index 00000000000..6fb0313c706 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_create_drop_procedure.test @@ -0,0 +1,81 @@ +--source include/master-slave.inc + +connection master; +CREATE TABLE t1 (id INT); +DELIMITER $$; +CREATE PROCEDURE proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +DELIMITER ;$$ +CALL proc1(@cnt); +SELECT @cnt; +INSERT INTO t1 VALUES (1), (2), (3); +CALL proc1(@cnt); +SELECT @cnt; + +--echo # Syncing with slave +sync_slave_with_master; + +CALL proc1(@cnt); +SELECT @cnt; + +connection master; +DELIMITER $$; +--error ER_SP_ALREADY_EXISTS +CREATE PROCEDURE proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ + +CREATE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ + +--error ER_WRONG_USAGE +CREATE OR REPLACE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ + +CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +DELIMITER ;$$ + +--echo # Syncing with slave +sync_slave_with_master; + +CALL proc1(@cnt); +SELECT @cnt; + +connection master; +DROP PROCEDURE proc1; +DELIMITER $$; +CREATE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +DELIMITER ;$$ +INSERT INTO t1 VALUES (1), (2), (3); +CALL proc1(@cnt); +SELECT @cnt; + +--echo # Syncing with slave +sync_slave_with_master; + +CALL proc1(@cnt); +SELECT @cnt; + +connection master; +DROP PROCEDURE proc1; +DELIMITER $$; +CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +DELIMITER ;$$ +INSERT INTO t1 VALUES (1), (2), (3); +CALL proc1(@cnt); +SELECT @cnt; + +--echo # Syncing with slave +sync_slave_with_master; + +CALL proc1(@cnt); +SELECT @cnt; + +connection master; +DROP TABLE IF EXISTS t1; +DROP PROCEDURE proc1; + +DROP PROCEDURE IF EXISTS proc2; + +--echo # Syncing with slave +sync_slave_with_master; + +--error ER_SP_DOES_NOT_EXIST +DROP PROCEDURE proc1; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_create_drop_udf.test b/mysql-test/suite/rpl/t/rpl_create_drop_udf.test new file mode 100644 index 00000000000..ace2fa0ef1a --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_create_drop_udf.test @@ -0,0 +1,49 @@ +--source include/master-slave.inc +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB + +connection master; + +eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; +SELECT metaphon('master'); + +--error ER_UDF_EXISTS +eval CREATE FUNCTION metaphon RETURNS INT SONAME "$UDF_EXAMPLE_SO"; + +sync_slave_with_master; + +SELECT metaphon('slave'); + +connection master; + +eval CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; +DROP FUNCTION IF EXISTS random_function_name; +eval CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; + +--error ER_WRONG_USAGE +eval CREATE OR REPLACE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; +sync_slave_with_master; + +SELECT metaphon('slave'); + +connection master; +DROP FUNCTION metaphon; + +eval CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; + +--error ER_UDF_EXISTS +eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; +sync_slave_with_master; + +SELECT metaphon('slave'); + +connection master; +DROP FUNCTION metaphon; +DROP FUNCTION IF EXISTS metaphon; +sync_slave_with_master; + +--error ER_SP_DOES_NOT_EXIST +DROP FUNCTION metaphon; + +DROP FUNCTION IF EXISTS metaphon; + +--source include/rpl_end.inc diff --git a/mysql-test/t/create_drop_binlog.test b/mysql-test/t/create_drop_binlog.test index de8bc7a4933..357cf8893fc 100644 --- a/mysql-test/t/create_drop_binlog.test +++ b/mysql-test/t/create_drop_binlog.test @@ -19,6 +19,53 @@ DROP DATABASE IF EXISTS d1; RESET MASTER; USE test; + +CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 1; +CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 1; +DROP FUNCTION f1; +CREATE FUNCTION IF NOT EXISTS f1() RETURNS INT RETURN 1; +CREATE FUNCTION IF NOT EXISTS f1() RETURNS INT RETURN 1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f1; +--replace_column 1 # 2 # 5 # +--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/ +SHOW BINLOG EVENTS; +RESET MASTER; + + +CREATE TABLE t1 (a DATETIME); +CREATE OR REPLACE PROCEDURE p1() DELETE FROM t1; +CREATE OR REPLACE PROCEDURE p1() DELETE FROM t1; +DROP PROCEDURE p1; +CREATE PROCEDURE IF NOT EXISTS p1() DELETE FROM t1; +CREATE PROCEDURE IF NOT EXISTS p1() DELETE FROM t1; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p1; +--replace_column 1 # 2 # 5 # +--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/ +SHOW BINLOG EVENTS; +DROP TABLE t1; +RESET MASTER; + + +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME '$UDF_EXAMPLE_SO'; +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME '$UDF_EXAMPLE_SO'; +DROP FUNCTION metaphon; +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME '$UDF_EXAMPLE_SO'; +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME '$UDF_EXAMPLE_SO'; +DROP FUNCTION IF EXISTS metaphon; +DROP FUNCTION IF EXISTS metaphon; +--replace_column 1 # 2 # 5 # +--replace_result $UDF_EXAMPLE_SO UDM_EXAMPLE_LIB +--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/ +SHOW BINLOG EVENTS; +RESET MASTER; + + --echo # --echo # CREATE SERVER is not logged --echo # diff --git a/mysql-test/t/create_drop_function.test b/mysql-test/t/create_drop_function.test new file mode 100644 index 00000000000..e4d3d684cd5 --- /dev/null +++ b/mysql-test/t/create_drop_function.test @@ -0,0 +1,41 @@ +SET timestamp=UNIX_TIMESTAMP('2014-09-30 08:00:00'); + +CREATE FUNCTION f1(str char(20)) +RETURNS CHAR(100) +RETURN CONCAT('Hello, ', str, '!'); +SELECT * FROM mysql.proc WHERE name like 'f1'; +SELECT f1('world'); + +--error ER_SP_ALREADY_EXISTS +CREATE FUNCTION f1(str char(20)) +RETURNS TEXT +RETURN CONCAT('Hello2, ', str, '!'); +SELECT body FROM mysql.proc WHERE name like 'f1'; + +CREATE FUNCTION IF NOT EXISTS f1(str char(20)) +RETURNS CHAR(100) +RETURN CONCAT('Hello3, ', str, '!'); +SELECT body FROM mysql.proc WHERE name like 'f1'; + +--error ER_WRONG_USAGE +CREATE OR REPLACE FUNCTION IF NOT EXISTS f1(str char(20)) +RETURNS CHAR(100) +RETURN CONCAT('Hello4, ', str, '!'); +SELECT body FROM mysql.proc WHERE name like 'f1'; + +CREATE OR REPLACE FUNCTION f1(str char(20)) +RETURNS CHAR(100) +RETURN CONCAT('Hello5, ', str, '!'); +SELECT body FROM mysql.proc WHERE name like 'f1'; + +DROP FUNCTION f1; +CREATE FUNCTION IF NOT EXISTS f1(str char(20)) +RETURNS CHAR(100) +RETURN CONCAT('Hello6, ', str, '!'); +SELECT body FROM mysql.proc WHERE name like 'f1'; +SELECT f1('world'); + +DROP FUNCTION IF EXISTS f1; +SELECT body FROM mysql.proc WHERE name like 'f1'; +DROP FUNCTION IF EXISTS f1; + diff --git a/mysql-test/t/create_drop_procedure.test b/mysql-test/t/create_drop_procedure.test new file mode 100644 index 00000000000..50e6b1cc61c --- /dev/null +++ b/mysql-test/t/create_drop_procedure.test @@ -0,0 +1,41 @@ +CREATE TABLE t1 (id INT); +DELIMITER $$; +CREATE PROCEDURE proc1 (OUT cnt INT) COMMENT 'comment1' BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +DELIMITER ;$$ +CALL proc1(@cnt); +SELECT @cnt; +INSERT INTO t1 VALUES (1), (2), (3); +CALL proc1(@cnt); +SELECT @cnt; + +DELIMITER $$; +--error ER_SP_ALREADY_EXISTS +CREATE PROCEDURE proc1 (OUT cnt INT) COMMENT 'comment2' + BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +SELECT comment FROM mysql.proc WHERE name='proc1'$$ + +CREATE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) COMMENT 'comment3' + BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +SELECT comment FROM mysql.proc WHERE name='proc1'$$ + +--error ER_WRONG_USAGE +CREATE OR REPLACE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) COMMENT 'comment4' + BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +SELECT comment FROM mysql.proc WHERE name='proc1'$$ + +CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) COMMENT 'comment5' + BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +SELECT comment FROM mysql.proc WHERE name='proc1'$$ + +DELIMITER ;$$ +DROP PROCEDURE proc1; +DELIMITER $$; +CREATE PROCEDURE IF NOT EXISTS proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$$ +DELIMITER ;$$ +INSERT INTO t1 VALUES (1), (2), (3); +CALL proc1(@cnt); +SELECT @cnt; + +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS proc1; +DROP PROCEDURE IF EXISTS proc1; diff --git a/mysql-test/t/create_drop_udf.test b/mysql-test/t/create_drop_udf.test new file mode 100644 index 00000000000..cc1b0a2c51e --- /dev/null +++ b/mysql-test/t/create_drop_udf.test @@ -0,0 +1,31 @@ +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; +SELECT ret FROM mysql.func WHERE name like 'metaphon'; + +--error ER_UDF_EXISTS +CREATE FUNCTION metaphon RETURNS INT SONAME "$UDF_EXAMPLE_SO"; +SELECT ret FROM mysql.func WHERE name like 'metaphon'; + +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE FUNCTION IF NOT EXISTS metaphon RETURNS REAL SONAME "$UDF_EXAMPLE_SO"; +SELECT ret FROM mysql.func WHERE name like 'metaphon'; + +DROP FUNCTION IF EXISTS metaphon; +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE FUNCTION IF NOT EXISTS metaphon RETURNS REAL SONAME "$UDF_EXAMPLE_SO"; +SELECT ret FROM mysql.func WHERE name like 'metaphon'; + +--error ER_WRONG_USAGE +eval CREATE OR REPLACE FUNCTION IF NOT EXISTS metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; +SELECT ret FROM mysql.func WHERE name like 'metaphon'; + +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE OR REPLACE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; +SELECT ret FROM mysql.func WHERE name like 'metaphon'; + +--error ER_UDF_EXISTS +eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; +SELECT metaphon('mariadb'); + +DROP FUNCTION metaphon; +DROP FUNCTION IF EXISTS metaphon; diff --git a/mysql-test/t/create_or_replace_permission.test b/mysql-test/t/create_or_replace_permission.test index 3bb72fba6af..32a721e9d68 100644 --- a/mysql-test/t/create_or_replace_permission.test +++ b/mysql-test/t/create_or_replace_permission.test @@ -41,18 +41,16 @@ USE db1; --error ER_TABLEACCESS_DENIED_ERROR CREATE OR REPLACE TABLE t1(id INT); -#TODO: add this when "MDEV-5359 CREATE OR REPLACE..." is done -#DELIMITER $; -#--error ER_PROCACCESS_DENIED_ERROR -#CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) BEGIN SELECT COUNT(*) INTO cnt FROM t1; END$ -#DELIMITER ;$ -# -#--error ER_DBACCESS_DENIED_ERROR -#CREATE OR REPLACE FUNCTION lookup RETURNS STRING SONAME "udf_example.so"; -# -#--error ER_PROCACCESS_DENIED_ERROR -#CREATE OR REPLACE FUNCTION hello(str char(20)) RETURNS TEXT RETURN CONCAT('Hello, ', str, '!'); -# +--error ER_PROCACCESS_DENIED_ERROR +CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) BEGIN END; + +--error ER_DBACCESS_DENIED_ERROR +CREATE OR REPLACE FUNCTION lookup RETURNS STRING SONAME "udf_example.so"; + +--error ER_PROCACCESS_DENIED_ERROR +CREATE OR REPLACE FUNCTION hello(str char(20)) RETURNS TEXT RETURN CONCAT('Hello, ', str, '!'); + +# TODO: add this when "MDEV-5359 CREATE OR REPLACE..." is done #--error ER_SPECIFIC_ACCESS_DENIED_ERROR #CREATE OR REPLACE USER u1@localhost; # diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index a16ef1205b9..c1a21c8ac85 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1766,11 +1766,10 @@ CREATE IF NOT EXISTS PROCEDURE bug14702() BEGIN END; ---error ER_PARSE_ERROR CREATE PROCEDURE IF NOT EXISTS bug14702() BEGIN END; - +DROP PROCEDURE IF EXISTS bug14702; # # BUG#20953: create proc with a create view that uses local diff --git a/sql/sp.cc b/sql/sp.cc index 965bbbf50ed..b19ba6195e3 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -823,6 +823,8 @@ db_load_routine(THD *thd, stored_procedure_type type, thd->lex= &newlex; newlex.current_select= NULL; + // Resetting REPLACE and EXIST flags in create_info, for show_create_sp() + newlex.create_info.DDL_options_st::init(); defstr.set_charset(creation_ctx->get_client_cs()); @@ -941,6 +943,51 @@ sp_returns_type(THD *thd, String &result, sp_head *sp) /** + Delete the record for the stored routine object from mysql.proc, + which is already opened, locked, and positioned to the record with the + record to be deleted. + + The operation deletes the record for the current record in "table" + and invalidates the stored-routine cache. + + @param thd Thread context. + @param type Stored routine type (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION) + @param name Stored routine name. + @param table A pointer to the opened mysql.proc table + + @returns Error code. + @return SP_OK on success, or SP_DELETE_ROW_FAILED on error. + used to indicate about errors. +*/ +static int +sp_drop_routine_internal(THD *thd, stored_procedure_type type, + sp_name *name, TABLE *table) +{ + DBUG_ENTER("sp_drop_routine_internal"); + + if (table->file->ha_delete_row(table->record[0])) + DBUG_RETURN(SP_DELETE_ROW_FAILED); + + /* Make change permanent and avoid 'table is marked as crashed' errors */ + table->file->extra(HA_EXTRA_FLUSH); + + sp_cache_invalidate(); + /* + A lame workaround for lack of cache flush: + make sure the routine is at least gone from the + local cache. + */ + sp_head *sp; + sp_cache **spc= (type == TYPE_ENUM_FUNCTION ? + &thd->sp_func_cache : &thd->sp_proc_cache); + sp= sp_cache_lookup(spc, name); + if (sp) + sp_cache_flush_obsolete(spc, &sp); + DBUG_RETURN(SP_OK); +} + + +/** Write stored-routine object into mysql.proc. This operation stores attributes of the stored procedure/function into @@ -966,6 +1013,7 @@ sp_returns_type(THD *thd, String &result, sp_head *sp) int sp_create_routine(THD *thd, stored_procedure_type type, sp_head *sp) { + LEX *lex= thd->lex; int ret; TABLE *table; char definer_buf[USER_HOST_BUFF_SIZE]; @@ -1003,6 +1051,36 @@ sp_create_routine(THD *thd, stored_procedure_type type, sp_head *sp) ret= SP_OPEN_TABLE_FAILED; else { + /* Checking if the routine already exists */ + if (db_find_routine_aux(thd, type, lex->spname, table) == SP_OK) + { + if (lex->create_info.or_replace()) + { + if ((ret= sp_drop_routine_internal(thd, type, lex->spname, table))) + goto done; + } + else if (lex->create_info.if_not_exists()) + { + push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, + ER_SP_ALREADY_EXISTS, ER(ER_SP_ALREADY_EXISTS), + type == TYPE_ENUM_FUNCTION ? + "FUNCTION" : "PROCEDURE", + lex->spname->m_name.str); + + ret= SP_OK; + + // Setting retstr as it is used for logging. + if (sp->m_type == TYPE_ENUM_FUNCTION) + sp_returns_type(thd, retstr, sp); + goto log; + } + else + { + ret= SP_WRITE_ROW_FAILED; + goto done; + } + } + restore_record(table, s->default_values); // Get default values for fields /* NOTE: all needed privilege checks have been already done. */ @@ -1166,38 +1244,39 @@ sp_create_routine(THD *thd, stored_procedure_type type, sp_head *sp) if (ret == SP_OK) sp_cache_invalidate(); + } - if (ret == SP_OK && mysql_bin_log.is_open()) +log: + if (ret == SP_OK && mysql_bin_log.is_open()) + { + thd->clear_error(); + + String log_query; + log_query.set_charset(system_charset_info); + + if (!show_create_sp(thd, &log_query, + sp->m_type, + (sp->m_explicit_name ? sp->m_db.str : NULL), + (sp->m_explicit_name ? sp->m_db.length : 0), + sp->m_name.str, sp->m_name.length, + sp->m_params.str, sp->m_params.length, + retstr.ptr(), retstr.length(), + sp->m_body.str, sp->m_body.length, + sp->m_chistics, &(thd->lex->definer->user), + &(thd->lex->definer->host), + saved_mode)) { - thd->clear_error(); - - String log_query; - log_query.set_charset(system_charset_info); - - if (!show_create_sp(thd, &log_query, - sp->m_type, - (sp->m_explicit_name ? sp->m_db.str : NULL), - (sp->m_explicit_name ? sp->m_db.length : 0), - sp->m_name.str, sp->m_name.length, - sp->m_params.str, sp->m_params.length, - retstr.ptr(), retstr.length(), - sp->m_body.str, sp->m_body.length, - sp->m_chistics, &(thd->lex->definer->user), - &(thd->lex->definer->host), - saved_mode)) - { - ret= SP_INTERNAL_ERROR; - goto done; - } - /* restore sql_mode when binloging */ - thd->variables.sql_mode= saved_mode; - /* Such a statement can always go directly to binlog, no trans cache */ - if (thd->binlog_query(THD::STMT_QUERY_TYPE, - log_query.ptr(), log_query.length(), - FALSE, FALSE, FALSE, 0)) - ret= SP_INTERNAL_ERROR; - thd->variables.sql_mode= 0; + ret= SP_INTERNAL_ERROR; + goto done; } + /* restore sql_mode when binloging */ + thd->variables.sql_mode= saved_mode; + /* Such a statement can always go directly to binlog, no trans cache */ + if (thd->binlog_query(THD::STMT_QUERY_TYPE, + log_query.ptr(), log_query.length(), + FALSE, FALSE, FALSE, 0)) + ret= SP_INTERNAL_ERROR; + thd->variables.sql_mode= 0; } done: @@ -1209,7 +1288,8 @@ done: /** - Delete the record for the stored routine object from mysql.proc. + Delete the record for the stored routine object from mysql.proc + and do binary logging. The operation deletes the record for the stored routine specified by name from the mysql.proc table and invalidates the stored-routine cache. @@ -1244,39 +1324,17 @@ sp_drop_routine(THD *thd, stored_procedure_type type, sp_name *name) if (!(table= open_proc_table_for_update(thd))) DBUG_RETURN(SP_OPEN_TABLE_FAILED); + if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK) + ret= sp_drop_routine_internal(thd, type, name, table); + + if (ret == SP_OK && + write_bin_log(thd, TRUE, thd->query(), thd->query_length())) + ret= SP_INTERNAL_ERROR; /* This statement will be replicated as a statement, even when using row-based replication. The flag will be reset at the end of the statement. */ - if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK) - { - if (table->file->ha_delete_row(table->record[0])) - ret= SP_DELETE_ROW_FAILED; - /* Make change permanent and avoid 'table is marked as crashed' errors */ - table->file->extra(HA_EXTRA_FLUSH); - } - - if (ret == SP_OK) - { - if (write_bin_log(thd, TRUE, thd->query(), thd->query_length())) - ret= SP_INTERNAL_ERROR; - sp_cache_invalidate(); - - /* - A lame workaround for lack of cache flush: - make sure the routine is at least gone from the - local cache. - */ - { - sp_head *sp; - sp_cache **spc= (type == TYPE_ENUM_FUNCTION ? - &thd->sp_func_cache : &thd->sp_proc_cache); - sp= sp_cache_lookup(spc, name); - if (sp) - sp_cache_flush_obsolete(spc, &sp); - } - } DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row()); DBUG_RETURN(ret); } @@ -2136,11 +2194,16 @@ show_create_sp(THD *thd, String *buf, thd->variables.sql_mode= sql_mode; buf->append(STRING_WITH_LEN("CREATE ")); + if (thd->lex->create_info.or_replace()) + buf->append(STRING_WITH_LEN("OR REPLACE ")); append_definer(thd, buf, definer_user, definer_host); if (type == TYPE_ENUM_FUNCTION) buf->append(STRING_WITH_LEN("FUNCTION ")); else buf->append(STRING_WITH_LEN("PROCEDURE ")); + if (thd->lex->create_info.if_not_exists()) + buf->append(STRING_WITH_LEN("IF NOT EXISTS ")); + if (dblen > 0) { append_identifier(thd, buf, db, dblen); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 52cf27d3741..6c6c8ac7c9f 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4480,7 +4480,9 @@ end_with_restore_list: #endif case SQLCOM_CREATE_FUNCTION: // UDF function { - if (check_access(thd, INSERT_ACL, "mysql", NULL, NULL, 1, 0)) + if (check_access(thd, lex->create_info.or_replace() ? + (INSERT_ACL | DELETE_ACL) : INSERT_ACL, + "mysql", NULL, NULL, 1, 0)) break; #ifdef HAVE_DLOPEN WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL) @@ -4950,6 +4952,15 @@ end_with_restore_list: goto create_sp_error; } + /* Checking the drop permissions if CREATE OR REPLACE is used */ + if (lex->create_info.or_replace()) + { + if (check_routine_access(thd, ALTER_PROC_ACL, lex->spname->m_db.str, + lex->spname->m_name.str, + lex->sql_command == SQLCOM_DROP_PROCEDURE, 0)) + goto create_sp_error; + } + name= lex->sphead->name(&namelen); #ifdef HAVE_DLOPEN if (lex->sphead->m_type == TYPE_ENUM_FUNCTION) diff --git a/sql/sql_udf.cc b/sql/sql_udf.cc index bd5732c3696..2354f120041 100644 --- a/sql/sql_udf.cc +++ b/sql/sql_udf.cc @@ -413,6 +413,50 @@ static udf_func *add_udf(LEX_STRING *name, Item_result ret, char *dl, return tmp; } +/* + Drop user defined function. + + @param thd Thread handler. + @param udf Existing udf_func pointer which is to be deleted. + @param table mysql.func table reference (opened and locked) + + Assumption + + - udf is not null. + - table is already opened and locked +*/ +static int mysql_drop_function_internal(THD *thd, udf_func *udf, TABLE *table) +{ + DBUG_ENTER("mysql_drop_function_internal"); + + char *exact_name_str= udf->name.str; + uint exact_name_len= udf->name.length; + + del_udf(udf); + /* + Close the handle if this was function that was found during boot or + CREATE FUNCTION and it's not in use by any other udf function + */ + if (udf->dlhandle && !find_udf_dl(udf->dl)) + dlclose(udf->dlhandle); + + if (!table) + DBUG_RETURN(1); + + table->use_all_columns(); + table->field[0]->store(exact_name_str, exact_name_len, &my_charset_bin); + if (!table->file->ha_index_read_idx_map(table->record[0], 0, + (uchar*) table->field[0]->ptr, + HA_WHOLE_KEY, + HA_READ_KEY_EXACT)) + { + int error; + if ((error= table->file->ha_delete_row(table->record[0]))) + table->file->print_error(error, MYF(0)); + } + DBUG_RETURN(0); +} + /** Create a user defined function. @@ -466,10 +510,26 @@ int mysql_create_function(THD *thd,udf_func *udf) mysql_rwlock_wrlock(&THR_LOCK_udf); DEBUG_SYNC(current_thd, "mysql_create_function_after_lock"); - if ((my_hash_search(&udf_hash,(uchar*) udf->name.str, udf->name.length))) + if ((u_d= (udf_func*) my_hash_search(&udf_hash, (uchar*) udf->name.str, + udf->name.length))) { - my_error(ER_UDF_EXISTS, MYF(0), udf->name.str); - goto err; + if (thd->lex->create_info.or_replace()) + { + if ((error= mysql_drop_function_internal(thd, u_d, table))) + goto err; + } + else if (thd->lex->create_info.if_not_exists()) + { + push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, ER_UDF_EXISTS, + ER(ER_UDF_EXISTS), udf->name.str); + + goto done; + } + else + { + my_error(ER_UDF_EXISTS, MYF(0), udf->name.str); + goto err; + } } if (!(dl = find_udf_dl(udf->dl))) { @@ -496,16 +556,16 @@ int mysql_create_function(THD *thd,udf_func *udf) goto err; } } - udf->name.str=strdup_root(&mem,udf->name.str); - udf->dl=strdup_root(&mem,udf->dl); + udf->name.str= strdup_root(&mem,udf->name.str); + udf->dl= strdup_root(&mem,udf->dl); if (!(u_d=add_udf(&udf->name,udf->returns,udf->dl,udf->type))) goto err; - u_d->dlhandle = dl; - u_d->func=udf->func; - u_d->func_init=udf->func_init; - u_d->func_deinit=udf->func_deinit; - u_d->func_clear=udf->func_clear; - u_d->func_add=udf->func_add; + u_d->dlhandle= dl; + u_d->func= udf->func; + u_d->func_init= udf->func_init; + u_d->func_deinit= udf->func_deinit; + u_d->func_clear= udf->func_clear; + u_d->func_add= udf->func_add; /* create entry in mysql.func table */ @@ -527,6 +587,8 @@ int mysql_create_function(THD *thd,udf_func *udf) del_udf(u_d); goto err; } + +done: mysql_rwlock_unlock(&THR_LOCK_udf); /* Binlog the create function. */ @@ -535,7 +597,7 @@ int mysql_create_function(THD *thd,udf_func *udf) DBUG_RETURN(0); - err: +err: if (new_dl) dlclose(dl); mysql_rwlock_unlock(&THR_LOCK_udf); @@ -548,8 +610,6 @@ int mysql_drop_function(THD *thd,const LEX_STRING *udf_name) TABLE *table; TABLE_LIST tables; udf_func *udf; - char *exact_name_str; - uint exact_name_len; DBUG_ENTER("mysql_drop_function"); if (!initialized) @@ -567,35 +627,25 @@ int mysql_drop_function(THD *thd,const LEX_STRING *udf_name) mysql_rwlock_wrlock(&THR_LOCK_udf); DEBUG_SYNC(current_thd, "mysql_drop_function_after_lock"); - if (!(udf=(udf_func*) my_hash_search(&udf_hash,(uchar*) udf_name->str, - (uint) udf_name->length))) + if (!(udf= (udf_func*) my_hash_search(&udf_hash, (uchar*) udf_name->str, + (uint) udf_name->length)) ) { + if (thd->lex->check_exists) + { + push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, + ER_FUNCTION_NOT_DEFINED, ER(ER_FUNCTION_NOT_DEFINED), + udf_name->str); + goto done; + } + my_error(ER_FUNCTION_NOT_DEFINED, MYF(0), udf_name->str); goto err; } - exact_name_str= udf->name.str; - exact_name_len= udf->name.length; - del_udf(udf); - /* - Close the handle if this was function that was found during boot or - CREATE FUNCTION and it's not in use by any other udf function - */ - if (udf->dlhandle && !find_udf_dl(udf->dl)) - dlclose(udf->dlhandle); - if (!table) + if (mysql_drop_function_internal(thd, udf, table)) goto err; - table->use_all_columns(); - table->field[0]->store(exact_name_str, exact_name_len, &my_charset_bin); - if (!table->file->ha_index_read_idx_map(table->record[0], 0, - (uchar*) table->field[0]->ptr, - HA_WHOLE_KEY, - HA_READ_KEY_EXACT)) - { - int error; - if ((error = table->file->ha_delete_row(table->record[0]))) - table->file->print_error(error, MYF(0)); - } + +done: mysql_rwlock_unlock(&THR_LOCK_udf); /* diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 927de37f2f1..7f9ca84bdf0 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1936,7 +1936,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); view_algorithm view_or_trigger_or_sp_or_event definer_tail no_definer_tail view_suid view_tail view_list_opt view_list view_select - view_check_option trigger_tail sp_tail sf_tail udf_tail event_tail + view_check_option trigger_tail sp_tail sf_tail event_tail + udf_tail udf_tail2 install uninstall partition_entry binlog_base64_event init_key_options normal_key_options normal_key_opts all_key_opt spatial_key_options fulltext_key_options normal_key_opt @@ -2561,7 +2562,11 @@ create: view_or_trigger_or_sp_or_event { // TODO: remove this when "MDEV-5359 CREATE OR REPLACE..." is done - if ($1.or_replace() && Lex->sql_command != SQLCOM_CREATE_VIEW) + if ($1.or_replace() && + Lex->sql_command != SQLCOM_CREATE_VIEW && + Lex->sql_command != SQLCOM_CREATE_FUNCTION && + Lex->sql_command != SQLCOM_CREATE_SPFUNCTION && + Lex->sql_command != SQLCOM_CREATE_PROCEDURE) { my_error(ER_WRONG_USAGE, MYF(0), "OR REPLACE", "TRIGGERS / SP / EVENT"); @@ -16218,45 +16223,32 @@ trigger_tail: **************************************************************************/ udf_tail: - AGGREGATE_SYM remember_name FUNCTION_SYM ident + AGGREGATE_SYM udf_tail2 { thd->lex->udf.type= UDFTYPE_AGGREGATE; } + | udf_tail2 { thd->lex->udf.type= UDFTYPE_FUNCTION; } + ; + +udf_tail2: + FUNCTION_SYM opt_if_not_exists ident RETURNS_SYM udf_type SONAME_SYM TEXT_STRING_sys { LEX *lex= thd->lex; - if (is_native_function(thd, & $4)) - { - my_error(ER_NATIVE_FCT_NAME_COLLISION, MYF(0), - $4.str); + if (lex->add_create_options_with_check($2)) MYSQL_YYABORT; - } - lex->sql_command = SQLCOM_CREATE_FUNCTION; - lex->udf.type= UDFTYPE_AGGREGATE; - lex->stmt_definition_begin= $2; - lex->udf.name = $4; - lex->udf.returns=(Item_result) $6; - lex->udf.dl=$8.str; - } - | remember_name FUNCTION_SYM ident - RETURNS_SYM udf_type SONAME_SYM TEXT_STRING_sys - { - LEX *lex= thd->lex; if (is_native_function(thd, & $3)) { - my_error(ER_NATIVE_FCT_NAME_COLLISION, MYF(0), - $3.str); + my_error(ER_NATIVE_FCT_NAME_COLLISION, MYF(0), $3.str); MYSQL_YYABORT; } - lex->sql_command = SQLCOM_CREATE_FUNCTION; - lex->udf.type= UDFTYPE_FUNCTION; - lex->stmt_definition_begin= $1; - lex->udf.name = $3; - lex->udf.returns=(Item_result) $5; - lex->udf.dl=$7.str; + lex->sql_command= SQLCOM_CREATE_FUNCTION; + lex->udf.name= $3; + lex->udf.returns= (Item_result) $5; + lex->udf.dl= $7.str; } ; sf_tail: - remember_name /* $1 */ - FUNCTION_SYM /* $2 */ + FUNCTION_SYM /* $1 */ + opt_if_not_exists /* $2 */ sp_name /* $3 */ '(' /* $4 */ { /* $5 */ @@ -16264,7 +16256,8 @@ sf_tail: Lex_input_stream *lip= YYLIP; const char* tmp_param_begin; - lex->stmt_definition_begin= $1; + if (lex->add_create_options_with_check($2)) + MYSQL_YYABORT; lex->spname= $3; if (lex->sphead) @@ -16359,18 +16352,20 @@ sf_tail: ; sp_tail: - PROCEDURE_SYM remember_name sp_name + PROCEDURE_SYM opt_if_not_exists sp_name { + if (Lex->add_create_options_with_check($2)) + MYSQL_YYABORT; + if (Lex->sphead) { my_error(ER_SP_NO_RECURSIVE_CREATE, MYF(0), "PROCEDURE"); MYSQL_YYABORT; } - Lex->stmt_definition_begin= $2; - if (!make_sp_head(thd, $3, TYPE_ENUM_PROCEDURE)) MYSQL_YYABORT; + Lex->spname= $3; } '(' { |