#At file:///home/sven/bzr/b47995-user_func_unsafe/5.1-bugteam/ based on revid:v.narayanan@stripped
3160 Sven Sandberg 2009-11-02
BUG#47995: Mark system functions as unsafe
Problem: Some system functions that could return different values on
master and slave were not marked unsafe. In particular:
CONNECTION_ID
GET_LOCK
IS_FREE_LOCK
IS_USED_LOCK
MASTER_POS_WAIT
RELEASE_LOCK
SLEEP
SYSDATE
Fix: Mark these functions unsafe.
@ mysql-test/suite/binlog/r/binlog_unsafe.result
updated result file
@ mysql-test/suite/binlog/t/binlog_unsafe.test
Added test for all unsafe system functions. This test also includes
system functions that were unsafe prior to BUG#47995.
@ sql/item_create.cc
Marked some system functions unsafe.
@ sql/sql_yacc.yy
Marked sysdate unsafe.
modified:
mysql-test/suite/binlog/r/binlog_unsafe.result
mysql-test/suite/binlog/t/binlog_unsafe.test
sql/item_create.cc
sql/item_strfunc.cc
sql/sql_yacc.yy
=== modified file 'mysql-test/suite/binlog/r/binlog_unsafe.result'
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result 2009-07-10 23:12:13 +0000
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result 2009-11-02 11:00:49 +0000
@@ -328,3 +328,56 @@ Note 1592 Statement may not be safe to l
DROP TABLE t1, t2;
SET @@SESSION.SQL_MODE = @save_sql_mode;
"End of tests"
+CREATE TABLE t1 (a VARCHAR(1000));
+INSERT INTO t1 VALUES (CONNECTION_ID());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (CURRENT_USER());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (FOUND_ROWS());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (GET_LOCK('tmp', 1));
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp'));
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (IS_USED_LOCK('tmp'));
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat'));
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1));
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (RELEASE_LOCK('tmp'));
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (ROW_COUNT());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (SESSION_USER());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (SLEEP(1));
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (SYSDATE());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (SYSTEM_USER());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (USER());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (UUID());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (UUID_SHORT());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+DROP TABLE t1;
=== modified file 'mysql-test/suite/binlog/t/binlog_unsafe.test'
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test 2009-07-10 23:12:13 +0000
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test 2009-11-02 11:00:49 +0000
@@ -389,3 +389,31 @@ DELETE FROM t1 LIMIT 1;
DROP TABLE t1, t2;
SET @@SESSION.SQL_MODE = @save_sql_mode;
--echo "End of tests"
+
+#
+# BUG#47995: Mark user functions as unsafe
+#
+# Test that the system functions that are supposed to be marked unsafe
+# generate a warning. Each INSERT statement below should generate a
+# warning.
+#
+
+CREATE TABLE t1 (a VARCHAR(1000));
+INSERT INTO t1 VALUES (CONNECTION_ID());
+INSERT INTO t1 VALUES (CURRENT_USER()); #marked unsafe before BUG#39934
+INSERT INTO t1 VALUES (FOUND_ROWS()); #marked unsafe before BUG#39934
+INSERT INTO t1 VALUES (GET_LOCK('tmp', 1));
+INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp'));
+INSERT INTO t1 VALUES (IS_USED_LOCK('tmp'));
+INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #marked unsafe before BUG#39934
+INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1));
+INSERT INTO t1 VALUES (RELEASE_LOCK('tmp'));
+INSERT INTO t1 VALUES (ROW_COUNT()); #marked unsafe before BUG#39934
+INSERT INTO t1 VALUES (SESSION_USER()); #marked unsafe before BUG#39934
+INSERT INTO t1 VALUES (SLEEP(1));
+INSERT INTO t1 VALUES (SYSDATE());
+INSERT INTO t1 VALUES (SYSTEM_USER()); #marked unsafe before BUG#39934
+INSERT INTO t1 VALUES (USER()); #marked unsafe before BUG#39934
+INSERT INTO t1 VALUES (UUID()); #marked unsafe before BUG#39934
+INSERT INTO t1 VALUES (UUID_SHORT()); #marked unsafe before BUG#39934
+DROP TABLE t1;
=== modified file 'sql/item_create.cc'
--- a/sql/item_create.cc 2009-08-28 16:21:54 +0000
+++ b/sql/item_create.cc 2009-11-02 11:00:49 +0000
@@ -2884,6 +2884,7 @@ Create_func_connection_id Create_func_co
Item*
Create_func_connection_id::create(THD *thd)
{
+ thd->lex->set_stmt_unsafe();
thd->lex->safe_to_cache_query= 0;
return new (thd->mem_root) Item_func_connection_id();
}
@@ -3524,6 +3525,7 @@ Create_func_get_lock Create_func_get_loc
Item*
Create_func_get_lock::create(THD *thd, Item *arg1, Item *arg2)
{
+ thd->lex->set_stmt_unsafe();
thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
return new (thd->mem_root) Item_func_get_lock(arg1, arg2);
}
@@ -3635,6 +3637,7 @@ Create_func_is_free_lock Create_func_is_
Item*
Create_func_is_free_lock::create(THD *thd, Item *arg1)
{
+ thd->lex->set_stmt_unsafe();
thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
return new (thd->mem_root) Item_func_is_free_lock(arg1);
}
@@ -3645,6 +3648,7 @@ Create_func_is_used_lock Create_func_is_
Item*
Create_func_is_used_lock::create(THD *thd, Item *arg1)
{
+ thd->lex->set_stmt_unsafe();
thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
return new (thd->mem_root) Item_func_is_used_lock(arg1);
}
@@ -3961,6 +3965,8 @@ Create_func_master_pos_wait::create_nati
Item *func= NULL;
int arg_count= 0;
+ thd->lex->set_stmt_unsafe();
+
if (item_list != NULL)
arg_count= item_list->elements;
@@ -4203,6 +4209,7 @@ Create_func_release_lock Create_func_rel
Item*
Create_func_release_lock::create(THD *thd, Item *arg1)
{
+ thd->lex->set_stmt_unsafe();
thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
return new (thd->mem_root) Item_func_release_lock(arg1);
}
@@ -4325,6 +4332,7 @@ Create_func_sleep Create_func_sleep::s_s
Item*
Create_func_sleep::create(THD *thd, Item *arg1)
{
+ thd->lex->set_stmt_unsafe();
thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
return new (thd->mem_root) Item_func_sleep(arg1);
}
=== modified file 'sql/item_strfunc.cc'
--- a/sql/item_strfunc.cc 2009-09-10 10:30:03 +0000
+++ b/sql/item_strfunc.cc 2009-11-02 11:00:49 +0000
@@ -1827,8 +1827,9 @@ String *Item_func_database::val_str(Stri
/**
- @todo
- make USER() replicate properly (currently it is replicated to "")
+ @note USER() is replicated correctly if binlog_format=ROW or (as of
+ BUG#47442) binlog_format=MIXED, but incorrectly replicated to '' if
+ binlog_format=STATEMENT.
*/
bool Item_func_user::init(const char *user, const char *host)
{
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2009-09-30 19:37:52 +0000
+++ b/sql/sql_yacc.yy 2009-11-02 11:00:49 +0000
@@ -7523,6 +7523,14 @@ function_call_nonkeyword:
}
| SYSDATE optional_braces
{
+ /*
+ Unlike other time-related functions, SYSDATE() is
+ replication-unsafe because it is not affected by the
+ TIMESTAMP variable. It is unsafe even if
+ sysdate_is_now=1, because the slave may have
+ sysdate_is_now=0.
+ */
+ Lex->set_stmt_unsafe();
if (global_system_variables.sysdate_is_now == 0)
$$= new (YYTHD->mem_root) Item_func_sysdate_local();
else
Attachment: [text/bzr-bundle] bzr/sven.sandberg@sun.com-20091102110049-vvr976tbsaqjjngy.bundle
