| Bug #47995 | Mark system functions as unsafe | ||
|---|---|---|---|
| Submitted: | 12 Oct 2009 12:10 | Modified: | 15 Mar 2010 4:51 |
| Reporter: | Sven Sandberg | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.1+ | OS: | Any |
| Assigned to: | Sven Sandberg | CPU Architecture: | Any |
| Tags: | user functions | ||
[12 Oct 2009 12:10]
Sven Sandberg
[13 Oct 2009 10:45]
Sven Sandberg
In addition, SYSDATE() should be marked unsafe.
[13 Oct 2009 10:46]
Sven Sandberg
Documentation request: at http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html , under "Disadvantages of statement-based replication", there is a bullet "Statements using any of the following functions cannot be replicated properly using statement-based replication:" that should be updated.
[13 Oct 2009 10:50]
Sven Sandberg
Documentation request 2: At http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_sysdate , in the following paragraph: "Because SYSDATE() can return different values even within the same statement, and is not affected by SET TIMESTAMP, it is nondeterministic and therefore unsafe for replication if statement-based binary logging is used. If that is a problem, you can use row-based logging, or start the server with the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(). The nondeterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it." ... it should be mentioned that, after this fix, SYSDATE() is safe when binlog_format=MIXED or binlog_format=STATEMENT.
[26 Oct 2009 17:01]
Sven Sandberg
See BUG#48321 for a related problem (CURRENT_USER used in special contexts).
[2 Nov 2009 11:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/88918 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.
[9 Nov 2009 12:25]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/89791 3160 Sven Sandberg 2009-11-09 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.
[9 Nov 2009 12:31]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/89794 3160 Sven Sandberg 2009-11-09 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/item_strfunc.cc Clarified comment related to this bug. @ sql/sql_yacc.yy Marked sysdate unsafe.
[17 Nov 2009 11:49]
Sven Sandberg
Correction: CONNECTION_ID does not need to be marked unsafe. The connection id is replicated in query_log_event (where it goes under the names slave_proxy_id and pseudo_thread_id).
[18 Nov 2009 13:02]
Sven Sandberg
The VERSION() function should also be marked unsafe.
[18 Nov 2009 14:51]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/90870 3207 Sven Sandberg 2009-11-18 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: GET_LOCK IS_FREE_LOCK IS_USED_LOCK MASTER_POS_WAIT RELEASE_LOCK SLEEP SYSDATE VERSION Fix: Mark these functions unsafe. @ mysql-test/extra/rpl_tests/rpl_stm_000001.test - The test does not work in mixed mode any more, since it tries to simulate an error in the sql thread in a query that uses get_lock. Since get_lock now causes the query to be logged in row format, the error didn't happen. Hence, we now force statement mode. - Warnings must be disabled when the unsafe query is issued. - Replaced some save_master_pos+connection slave+sync_with_master by sync_slave_with_master. @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result updated result file @ mysql-test/suite/binlog/r/binlog_stm_row.result updated result file @ mysql-test/suite/binlog/r/binlog_unsafe.result updated result file @ mysql-test/suite/binlog/t/binlog_killed.test binlog_killed only works in statement format now, since it switches to row mode in mixed mode. @ mysql-test/suite/binlog/t/binlog_stm_mix_innodb_myisam.test suppress warnings for unsafe statements @ mysql-test/suite/binlog/t/binlog_stm_row.test - Suppress warnings in test that causes warnings. - The test sets binlog format explicitly, so no need to execute it twice. @ 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. @ mysql-test/suite/rpl/r/rpl_err_ignoredtable.result updated result file @ mysql-test/suite/rpl/r/rpl_get_lock.result updated result file @ mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result new result file @ mysql-test/suite/rpl/r/rpl_stm_000001.result updated result file @ mysql-test/suite/rpl/r/rpl_trigger.result updated result file @ mysql-test/suite/rpl/t/rpl_err_ignoredtable.test - suppress warnings for unsafe statement - replaced save_master_pos+connection slave+sync_with_master with sync_slave_with_master @ mysql-test/suite/rpl/t/rpl_get_lock.test update test case that causes new warnings @ mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test Added new test case for nondeterministic functions. @ mysql-test/suite/rpl/t/rpl_trigger.test update test case that causes new warnings @ sql/item_create.cc Marked some system functions unsafe. @ sql/item_strfunc.cc Clarified comment related to this bug. @ sql/sql_yacc.yy Marked sysdate unsafe.
[18 Nov 2009 14:57]
Sven Sandberg
pushed to 5.1-bugteam
[18 Nov 2009 14:58]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/90874 3690 Sven Sandberg 2009-11-18 [merge] merge BUG#47995 from 5.1-bugteam to pe
[2 Dec 2009 8:03]
Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)
[16 Dec 2009 8:41]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:47]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:54]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[16 Dec 2009 22:19]
Jon Stephens
Documented bugfix in the 5.1.42, 5.5.1, and 6.0.14 changelogs, as follows:
The following functions have been marked unsafe for
statement-based replication:
GET_LOCK()
IS_FREE_LOCK()
IS_USED_LOCK()
MASTER_POS_WAIT()
RELEASE_LOCK()
SLEEP()
SYSDATE()
VERSION()
None of the functions just listed are guaranteed to replicate
correctly when using the statement-based format, because they
can produce different results on the master and the slave. The
use of any of these functions while binlog_format is set to
STATEMENT is logged with the warning -Statement is not safe to
log in statement format-. If binlog_format is set to MIXED,
the binary logging format is automatically switched to the
row-based format when any of these functions are used.
Also updated relevant material at http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html, function descriptions under http://dev.mysql.com/doc/refman/5.1/en/functions.html, and http://dev.mysql.com/doc/refman/5.1/en/replication-features-functions.html (as well as their counterparts in the 5.5 and 6.0 versions of the Manual).
Closed.
[12 Mar 2010 14:16]
Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:32]
Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:48]
Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[15 Mar 2010 4:51]
Jon Stephens
5.1-main -> telco merge; no new changelog entries required. Closed.
