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:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1+ OS:Any
Assigned to: Sven Sandberg
Tags: user functions
Triage: Triaged: D2 (Serious) / R1 (None/Negligible) / E2 (Low)

[12 Oct 2009 12:10] Sven Sandberg
Description:
Statements may be marked unsafe, meaning that they shall be logged in row format if binlog_format=MIXED. The following user functions are currently marked unsafe:

FOUND_ROWS
LOAD_FILE
ROW_COUNT
UUID
UUID_SHORT

In addition, the following should be marked unsafe:

CONNECTION_ID
GET_LOCK
IS_FREE_LOCK
IS_USED_LOCK
MASTER_POS_WAIT
RELEASE_LOCK
SESSION_USER
SYSTEM_USER
USER
SLEEP

How to repeat:
See the manual for each of these functions:

 - CONNECTION_ID, SESSION_USER, SYSTEM_USER, USER are connection-dependent

 - GET_LOCK is timing dependent

 - IS_FREE_LOCK, IS_USED_LOCK are nondeterministic (and may even interact with the slave's global environment in unwanted ways)

 - RELEASE_LOCK may return a different value on slave (because GET_LOCK is not called on slave, since GET_LOCK is unsafe)

 - MASTER_POS_WAIT and SLEEP are timing-dependent

Suggested fix:
Mark all these functions as unsafe
[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.