MySQL Bugs: #10494: FILTER: SQL syntax error breaks replication even though table is not replicated

Bug #10494	FILTER: SQL syntax error breaks replication even though table is not replicated
Submitted:	9 May 2005 23:02	Modified:	4 Feb 2008 20:16
Reporter:	Dathan Pattishall	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	All	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
A table created in 4.0 uses a reserved word current_user as a column. On a 4.1.10 slave to a 4.0 server an error 1064 is generated eventhough the table is not replicated.

show slave status

Replicate_Do_Table: friendster.invites,friendster.users

 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 'current_user = '16998313', user_date = now() WHERE username = 'shelly'' at line 1' on query. Default database: 'friendster'. Query: '/* /FEUD/user_profile.php:setcurrentuser  */ UPDATE app_user SET current_user = '16998313', user_date = now() WHERE username = 'shelly''

How to repeat:

use a 4.0 master

create a table that uses a reserve word in mySQL 4.1 that is not in mySQL 4.0

replicate the table from  a 4.1 slave 

set replication to replicate certain tables not all that are on the master.

run an event

Suggested fix:

Do not check the sql syntax on tables that are not being replicated.

Yo!

Oy?

What actualy seems to happen here is that the slave needs to parse all incoming queries first to see which tables are involved before it can decide to ignore a query. And as the query doesn't parse correctly the slave stops.

We spend much effort to not introduce new keywords unnecessarily, 
but in the case there are new keywords, it is very hard to avoid 
checking the syntax on the slave.

The work-around is to ALTER TABLE on the master before starting 
4.0 -> 4.1 replication in case one is using reserved keyword 
in 4.1 that does not exist in 4.0.

Docs team, please set bug to "Won't fix" after this is documented.

Since this has been determined to be not to be a bug in the server, but rather expected behaviour, I've changed this to a Docs bug and assigned to myself for fixing, setting Stefan as lead.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.