Bug #39137 | DELETE makes stored function to fail (if table type is MyISAM) | ||
---|---|---|---|
Submitted: | 30 Aug 2008 18:53 | Modified: | 31 Aug 2008 18:28 |
Reporter: | [ name withheld ] | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
Version: | 5.0.51, 5.0.66a | OS: | Linux (Ubuntu hardy) |
Assigned to: | CPU Architecture: | Any |
[30 Aug 2008 18:53]
[ name withheld ]
[31 Aug 2008 16:25]
Valeriy Kravchuk
Verified with 5.0.66a: C:\Program Files\MySQL\MySQL Server 6.0\bin>mysql -uroot -proot -P3308 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DELIMITER @@ mysql> CREATE FUNCTION `test`() RETURNS char(64) CHARSET latin1 -> BEGIN -> DECLARE val CHAR(64); -> SELECT attr INTO val FROM test_table; -> DELETE FROM test_table; -> RETURN val; -> END@@ Query OK, 0 rows affected (0.13 sec) mysql> DELIMITER ; mysql> create table test_table (attr varchar(64)) engine=myisam; Query OK, 0 rows affected (0.08 sec) mysql> insert into test_table set attr='foo'; Query OK, 1 row affected (0.00 sec) mysql> select test(); +--------+ | test() | +--------+ | foo | +--------+ 1 row in set (0.03 sec) mysql> select test(); +--------+ | test() | +--------+ | NULL | +--------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1329 Message: No data - zero rows fetched, selected, or processed 1 row in set (0.00 sec) mysql> create table another_table (another_attr varchar(64)) engine=myisam; Query OK, 0 rows affected (0.05 sec) mysql> insert into another_table set another_attr='foo'; Query OK, 1 row affected (0.00 sec) mysql> select * from another_table; +--------------+ | another_attr | +--------------+ | foo | +--------------+ 1 row in set (0.00 sec) mysql> select * from another_table where another_attr=test(); Empty set, 1 warning (0.01 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1329 Message: No data - zero rows fetched, selected, or processed 1 row in set (0.00 sec) The above may be even somehow expected - there are NO rows in test_table... But let's continue: mysql> insert into test_table set attr='foo'; Query OK, 1 row affected (0.00 sec) mysql> select * from another_table where another_attr=test(); Empty set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1329 Message: No data - zero rows fetched, selected, or processed 1 row in set (0.00 sec) We just inserted one row, so function should work. This is a bug, IMHO. Actually, stored functions that change tables should NOT be allowed in SQL statements at all. 5.1.26 just does NOT allow to create a function like this: C:\Program Files\MySQL\MySQL Server 6.0\bin>mysql -uroot -proot -P3310 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.1.26-rc-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DELIMITER @@ mysql> CREATE FUNCTION `test`() RETURNS char(64) CHARSET latin1 -> BEGIN -> DECLARE val CHAR(64); -> SELECT attr INTO val FROM test_table; -> DELETE FROM test_table; -> RETURN val; -> END@@ ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQ L DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
[31 Aug 2008 18:28]
[ name withheld ]
> This is a bug, IMHO. Actually, stored > functions that change tables should NOT be allowed in SQL statements at all. > 5.1.26 just does NOT allow to create a function like this: it is very unfortunate if capability to delete rows from tables that are unrelated to the use of the function is not anymore supported in 5.1. without that capability, i have not found any simple solution to the problem that i have in hand. the query is used in freeradius, which does not allow more than one mysql statement when returning attributes.