MySQL Bugs: #16723: SQL Syntax Changes Between 4.0.20 and 5.0.18 Break Replication from old master

Bug #16723	SQL Syntax Changes Between 4.0.20 and 5.0.18 Break Replication from old master
Submitted:	23 Jan 2006 13:19	Modified:	20 Feb 2006 20:35
Reporter:	Scott Spearman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.0.20 and 5.0.18	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
We're having replication issues between a master running 4.0.20 and a slave on 5.0.18. 

The IO thread will run just fine, but the SQL thread keeps reporting syntax errors in the queries that come across. Here's an example: 

Last_Error: Error 'Unknown table 'CurrentMeal' in MULTI DELETE' on query. Default database: 'Dietary'. Query: 'DELETE Dietary.CurrentMeal FROM Dietary.CurrentMeal C, ADT.Visit where C.Account = Visit.Account and DischargeDt > '0000-00-00'' 

After playing around with it, I've found that version 4 wants the query in the form listed above, while version 5 wants it like this: 

'DELETE C FROM Dietary.CurrentMeal C, ADT.Visit where C.Account = Visit.Account and DischargeDt > '0000-00-00'' 

4 will fail the query if I give it the syntax 5 wants, and 5 will not parse the syntax 4 wants.

Is there any way to run 5 and ask it to allow version 4 syntax? I know I can use a --slave-skip-errors=1109 and then watch the logs, but I'd really rather have this automatically work. Is there a program I can use to edit the incomming relay logs (after the Slave dies) to change the format of the query to something that 5 will accept? 

How to repeat:
Set up a 4.0.20 and 5.0.18 replication structure, with 4.0.18 as the master.  Create a few tables and try do a MULTI-DELETE with either of the syntax listed above.

Suggested fix:
Allow version 5 to parse version 4 syntax.  Perhaps a variable to turn this featur on and off.

Thank you for a problem report. Please, send the SHOW CREATE TABLE results for the Dietary.CurrentMeal and ADT.Visit tables.

ON the 5.0.18 SERVER

mysql> SHOW CREATE TABLE Dietary.CurrentMeal\G
*************************** 1. row ***************************
       Table: CurrentMeal
Create Table: CREATE TABLE `CurrentMeal` (
  `Account` bigint(10) unsigned NOT NULL default '0',
  `CurrentMeal` varchar(100) NOT NULL default '',
  `LastMeal` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`Account`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> SHOW CREATE TABLE ADT.Visit\G
*************************** 1. row ***************************
       Table: Visit
Create Table: CREATE TABLE `Visit` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `MRN` int(6) unsigned zerofill NOT NULL default '000000',
  `Account` int(10) unsigned NOT NULL default '0',
  `Lname` varchar(20) NOT NULL default '',
  `Fname` varchar(20) NOT NULL default '',
  `AdmitDT` date NOT NULL default '0000-00-00',
  `DischargeDT` date NOT NULL default '0000-00-00',
  `Location` varchar(15) NOT NULL default '',
  `Sex` enum('M','F') NOT NULL default 'M',
  `BirthDT` date NOT NULL default '0000-00-00',
  `PatientType` char(3) NOT NULL default '',
  `INTN` int(10) unsigned NOT NULL default '0',
  `dx` varchar(45) default '',
  `allergy` varchar(45) default '',
  `Isolation` varchar(30) default '',
  `Height` smallint(5) unsigned default NULL,
  `weight` smallint(5) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `DischargeDt` (`DischargeDT`),
  KEY `Location` (`Location`),
  KEY `MRN` (`MRN`),
  KEY `Name` (`Lname`,`Fname`),
  KEY `Account` (`Account`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Patient Visits'

ON THE 4.0.20 SERVER
mysql> SHOW CREATE TABLE Dietary.CurrentMeal\G
*************************** 1. row ***************************
       Table: CurrentMeal
Create Table: CREATE TABLE `CurrentMeal` (
  `Account` bigint(10) unsigned NOT NULL default '0',
  `CurrentMeal` varchar(100) NOT NULL default '',
  `LastMeal` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`Account`)
) TYPE=MyISAM
1 row in set (0.02 sec)

mysql> SHOW CREATE TABLE ADT.Visit\G
*************************** 1. row ***************************
       Table: Visit
Create Table: CREATE TABLE `Visit` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `MRN` int(6) unsigned zerofill NOT NULL default '000000',
  `Account` int(10) unsigned NOT NULL default '0',
  `Lname` varchar(20) NOT NULL default '',
  `Fname` varchar(20) NOT NULL default '',
  `AdmitDT` date NOT NULL default '0000-00-00',
  `DischargeDT` date NOT NULL default '0000-00-00',
  `Location` varchar(15) NOT NULL default '',
  `Sex` enum('M','F') NOT NULL default 'M',
  `BirthDT` date NOT NULL default '0000-00-00',
  `PatientType` char(3) NOT NULL default '',
  `INTN` int(10) unsigned NOT NULL default '0',
  `dx` varchar(45) default '',
  `allergy` varchar(45) default '',
  `Isolation` varchar(30) default '',
  `Height` smallint(5) unsigned default NULL,
  `weight` smallint(5) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `DischargeDt` (`DischargeDT`),
  KEY `Location` (`Location`),
  KEY `MRN` (`MRN`),
  KEY `Name` (`Lname`,`Fname`),
  KEY `Account` (`Account`)
) TYPE=InnoDB COMMENT='Patient Visits'
1 row in set (0.29 sec)

The only appreciable difference I can see is that the 5.0.18 server added the DEFAULT CHARSET bit.  Hopefully this will help you trace it down.

Thanks!

Verified just as described with 4.0.27-BK master and 5.0.19-BK slave on your tables:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: repl
                Master_Port: 3340
              Connect_Retry: 60
            Master_Log_File: mysql-bin.001
        Read_Master_Log_Pos: 1593
             Relay_Log_File: suse-relay-bin.000002
              Relay_Log_Pos: 1660
      Relay_Master_Log_File: mysql-bin.001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: test
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 1593
            Relay_Log_Space: 1660
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uroot --socket=/tmp/mysql40.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.0.27-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> insert into Visit(Account, DischargeDT) values (1, '2006-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> insert into CurrentMeal (Account) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from Visit\G
*************************** 1. row ***************************
         id: 1
        MRN: 000000
    Account: 1
      Lname:
      Fname:
    AdmitDT: 0000-00-00
DischargeDT: 2006-01-01
   Location:
        Sex: M
    BirthDT: 0000-00-00
PatientType:
       INTN: 0
         dx:
    allergy:
  Isolation:
     Height: NULL
     weight: NULL
1 row in set (0.00 sec)

mysql> select * from CurrentMeal;
+---------+-------------+---------------------+
| Account | CurrentMeal | LastMeal            |
+---------+-------------+---------------------+
|       1 |             | 0000-00-00 00:00:00 |
+---------+-------------+---------------------+
1 row in set (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uroot --socket=/tmp/mysql40.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.27-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delete CurrentMeal from CurrentMeal C, Visit where C.Account=Visit.Account and DischargeDt > '0000-00-00';
Query OK, 1 row affected (0.01 sec)

mysql> select * from CurrentMeal\G
Empty set (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from CurrentMeal\G
*************************** 1. row ***************************
    Account: 1
CurrentMeal:
   LastMeal: 0000-00-00 00:00:00
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: repl
                Master_Port: 3340
              Connect_Retry: 60
            Master_Log_File: mysql-bin.001
        Read_Master_Log_Pos: 1939
             Relay_Log_File: suse-relay-bin.000002
              Relay_Log_Pos: 1870
      Relay_Master_Log_File: mysql-bin.001
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: test
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1109
                 Last_Error: Error 'Unknown table 'CurrentMeal' in MULTI DELETE'
 on query. Default database: 'test'. Query: 'delete CurrentMeal from CurrentMeal
 C, Visit where C.Account=Visit.Account and DischargeDt > '0000-00-00''
               Skip_Counter: 0
        Exec_Master_Log_Pos: 1799
            Relay_Log_Space: 2012
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

This is one of the documented SQL syntax changes that prevents proper replication. It should be explained explictely in the manual:

http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

and/or at

http://dev.mysql.com/doc/refman/5.0/en/replication-features.html.

But some workaround, based on clever comments like /*!50017   */ usage, should be also documented.

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
product(s).

Additional info:

I've documented the issue, and its implication for replication,
here:

http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html
http://dev.mysql.com/doc/refman/4.1/en/replication-features.html
http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
http://dev.mysql.com/doc/refman/5.1/en/replication-features.html