MySQL Bugs: #17871: swithing to RBR in mixed mode replication for queries with SYSDATE()

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 Soares	CPU Architecture:	Any

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.

please ignore the last paragraph of the description `one of the calls ...'

Many thanks for writing a feature request.

Verified as described.

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.