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
Category:	Server: Replication	Severity:	S4 (Feature request)
Version:	Master >= 5.0.20, Slave < 5.0.20	OS:	Any (all)
Assigned to:	Lars Thalmann	Target Version:
Triage:	D5 (Feature request)

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.