Master-Master replication setup, mysql-5.0.22 (CentOS 5.1 revision number 5.0.22-2.1.0.1)
Does anybody see anything in the following description that is:
a) improper mysqldump commandline
b) known problem with 5.0.22
c) known problem with replication
d) known problem with replication of triggers
e) known problem with mysqldump
f) other?
Problem: Replication breaks when replicating triggers. Commands are generated by a mysqldump of a standalone machine running mysql-5.0.22, attempting to import them into a master-master (5.0.22 to 5.0.22) replicated system. (It almost seems like the replication is not parsing the /* */ lines correctly. I have not tried a newer version of mysql yet, because we were hoping to use the vendor provided version and avoid having to use a custom version.) The problem is reproducible.
Problem exhibition:
On one of the masters:
mysql> CREATE TABLE `country` (
-> `id` tinyint(3) unsigned NOT NULL auto_increment,
-> `name` varchar(255) NOT NULL,
-> `abbr` varchar(20) NOT NULL,
-> `phone_code` smallint(5) unsigned NOT NULL,
-> `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
-> `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `abbr` (`abbr`),
-> KEY `name_idx` (`name`),
-> KEY `phone_code_idx` (`phone_code`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER ;;
mysql> /*!50003 SET SESSION SQL_MODE="" */;;
Query OK, 0 rows affected (0.00 sec)
mysql> /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `country_insert_before` BEFORE INSERT ON `country` FOR EACH ROW SET NEW.created_at = NOW(), NEW.updated_at = '0000-00-00 00:00:00' */;;
Query OK, 0 rows affected (0.00 sec)
On the slave, replication was good until the last statement. The last statement caused a failure as shown by show slave status:
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: db01
Master_User: replication
Master_Port: 3206
Connect_Retry: 30
Master_Log_File: db01-bin.000001
Read_Master_Log_Pos: 5287465
Relay_Log_File: db02-relay-bin.000003
Relay_Log_Pos: 5287354
Relay_Master_Log_File: db01-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
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 '/' at line 1' on query. Default database: 'old_ivenue'. Query: 'CREATE DEFINER=`root`@`localhost` TRIGGER `country_insert_before` BEFORE INSERT ON `country` FOR EACH ROW SET NEW.created_at = NOW(), NEW.updated_at = '0000-00-00 00:00:00' */'
Skip_Counter: 0
Exec_Master_Log_Pos: 5287216
Relay_Log_Space: 5287603
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)
All comments and suggestions welcome.