Bug #17871 swithing to RBR in mixed mode replication for queries with SYSDATE()
Submitted: 2 Mar 2006 18:10 Modified: 18 Dec 2009 1:44
Reporter: Andrei Elkin Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.1, 6.0 OS:Any
Assigned to: Luis Manuel Oliveira Soares CPU Architecture:Any

[2 Mar 2006 18:10] Andrei Elkin
Description:
Since 5.0 SYSDATE() is not an alias of NOW(), see bug#15101 for referrences and a workaround to restore aliasing via a new server's command line option.
The following example shows the difference between these two functions.

 mysql> select now(),sysdate(),sleep(1),now(),sysdate() \G
*************************** 1. row ***************************
     now(): 2006-03-02 10:44:30
sysdate(): 2006-03-02 10:44:30
 sleep(1): 0
     now(): 2006-03-02 10:44:30
sysdate(): 2006-03-02 10:44:31

one of the calls that should trigger replication with rows instead of statements when running in the new mixed mode replication

How to repeat:
Feature request.

Suggested fix:
Since SYSDATE()-s return different values within a query RBR sounds to be
an efficient solution and should work straight out of the box.

Todo: to implement replication switch to RBR in mixed mode of replication for
queries including of SYSDATE() calls.
[2 Mar 2006 18:15] Andrei Elkin
please ignore the last paragraph of the description `one of the calls ...'
[10 Oct 2008 10:57] Susanne Ebrecht
Many thanks for writing a feature request.

Verified as described.
[18 Dec 2009 1:44] Luis Manuel Oliveira Soares
This seems to have been done already. 
Can't repeat using 5.1-bugteam tree:

  - mysql-5.1-bugteam
  - revision-id: jimw@mysql.com-20091217200636-jqgx9hijt8lnnpur

Test Case
=========

-- source include/have_log_bin.inc
-- source include/have_binlog_format_mixed.inc

CREATE TABLE t1 (c1 TIMESTAMP);
INSERT INTO t1 VALUES ('2000-01-01 00:00:00');
INSERT INTO t1 VALUES (SYSDATE());
DROP TABLE t1; 

-- source include/show_binlog_events.inc

-- exit

Result
======

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
binlog.binlog_y 'row'                    [ skipped ]  Doesn't support --binlog-format='row'
binlog.binlog_y 'stmt'                   [ skipped ]  Doesn't support --binlog-format='statement'
CREATE TABLE t1 (c1 TIMESTAMP);
INSERT INTO t1 VALUES ('2000-01-01 00:00:00');
INSERT INTO t1 VALUES (SYSDATE());
DROP TABLE t1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (c1 TIMESTAMP)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ('2000-01-01 00:00:00')
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	use `test`; DROP TABLE t1
binlog.binlog_y 'mix'                    [ pass ]     25

Note that the second insert is indeed replicated using a Write_rows_log_event.

This seems to have been done as part of patch for BUG#47995.