Bug #52537 slave data inconsistency due to the magic version comments
Submitted: 1 Apr 2010 19:20 Modified: 1 Apr 2010 21:33
Reporter: Andrei Elkin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1+, 4.1, 5.0, 5.1, 5.6.99 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: cross-version replication, statement format replication, version comments
Triage: Triaged: D3 (Medium)

[1 Apr 2010 19:20] Andrei Elkin
Description:
This issue is essentially an elaborated towards consistency form of bug#49124.

If master and slave have different versions,
e.g v(master) < v(slave), and a replicated query
contains /*!version*/ magics
e.g 

    create table t (a int /*!v(slave), b int*/);

then 
*F1*: the query executes as

    create table t (a int)

on master, and as

    create table t (a int, b int)

on slave.

*F2*: Notice that the stored func, proc and trigger do not log the version comments but rather their expansion (null or the comments content).

*F1* the artifact might be viewed differently than the bug if the version comments were defined to be logged with the query regardless of their expansion at parsing on the master.
However, the docs do not specify the binlogging behaviour, and there exist
the fact *F2* (I call *F2* as the fact not artifact because nothing gets broken on the slave) that clashes with *F1*.

If *F1* accepted to be a bug, and therefore master would have to expand/resolve the comments instead of replicating them, then three results will be gained:

a. bug#49124 will be fixed
b. *F1* won't exist
c. *F2* style of binlogging will apply to the plain query as it does to
   the compound statements.

If *F1* will not be accepted as a bug, then

a. version comments will become officially a replication feature 
b. *F2* will be viewed as the artifact and will have to be fixed
   (unless we would like to have different vc binlogging style for
    the plain query and the compound statement - i doubt we would though)
c. bug#49124 will have to be fixed by different means.

How to repeat:
*F1*:

Setup 5.1 master and 5.5 slave.
Execute 

master> create table t (a int /*!50500 , b int*/);
master> show create table t;

| t     | CREATE TABLE `t` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 

slave> show create table t;

| t     | CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

===========================================================
^ That is the comments are encapsulated into the query event
and brought over to slave

*F2*

master> create trigger trg before insert on t for each row set @a:=1 /*!50500 + 1*/;
show binlog events;

| master-bin.000001 | 285 | Query       |         1 |         435 | use `test`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t for each row set @a:=1

==========================================================
^ That is the comments are resolved by the master's parser.

Suggested fix:
My personal opinion is to keep the simpler of two possibilities.
That is the master is responsible to parse out the version comments.
That naturally provides the user won't be able to engineer neither 
inconsistency nor security issue.
Notice, closing possibility for the user does not mean replication 
framework internally may not exploit the version comments.
[1 Apr 2010 21:33] Sveta Smirnova
Thank you for the report.

Verified as described.