MySQL Bugs: #24674: Adding DEFINER in binlog can cause replication to fail

Bug #24674	Adding DEFINER in binlog can cause replication to fail
Submitted:	29 Nov 2006 0:32	Modified:	26 Jun 2007 19:00
Reporter:	Markus Popp	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	Master >= 5.0.20, Slave < 5.0.20	OS:	Any (all)
Assigned to:	Lars Thalmann	CPU Architecture:	Any

Description:
Assuming that the Master runs a MySQL version >= 5.0.20 and the Slave runs a MySQL version < 5.0.20. If a Stored Routine is being created on the Master, it adds DEFINER to the binlog entry (even if it hasn't been supplied to the original query). Since the DEFINER clause has been introduced in 5.0.20, the slave doesn't understand it and fails.

How to repeat:
MASTER (MySQL 5.0.27):

mysql> create procedure test() select 123;
Query OK, 0 rows affected (0.00 sec)

This creates the binlog entry:

CREATE DEFINER=`mpopp`@`localhost` procedure test() select 123;

and causes the slave, running MySQL 5.0.19, to fail:

mysql> show slave status\G
*************************** 1. row ***************************
...
                 Last_Errno: 1064
                 Last_Error: Error 'You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax to
 use near 'procedure test() select 123' at line 1' on query. Default database: '
test'. Query: 'CREATE DEFINER=`mpopp`@`localhost` procedure test() select 123'
...
1 row in set (0.00 sec)

Suggested fix:
The binlog entry should be created in the form:

CREATE /*50020 DEFINER=`mpopp`@`localhost` */ procedure test() select 123

Thank you for a problem report. I agree with your suggested fix as a nice and useful feature request, but this is not a bug. Our manual (http://dev.mysql.com/doc/refman/5.0/en/replication-features.html) clearly states:

"In general, replication compatibility at the SQL level requires that any features used be supported by both the master and the slave servers. If you use a feature on a master server that is available only as of a given version of MySQL, you cannot replicate to a slave that is older than that version."

My point of view is that Stored Routines are features of both versions that are involved in this replication setup. Since both versions are the same main version (5.0) and both are GA, I think that replication should be able to handle it.

I still think it is a feature request, as we need to change the behaviour already implemented (even if not ablsolutely correctly). Surely we should avoid adding features that breaks replication with same major version 5.0. But this already happened. 

"Slaves should be newer or same version as master" is a well-known general rule...

This type of downward replication will be implemented later 
(probably 5.1), but not in 5.0.