Description:
Hello,
It appears that when you specify /*!50001 CREATE VIEW.... */ It will write a query with an invalid syntax in the binlog. It apparently removes the "/*!50001" but leaves the trailing "*/" intact when the query is written to the log. This causes an error on the slave node which causes replication to break.
-- IN MASTER BINLOG --
# at 57099293
#060728 15:44:44 server id 1 end_log_pos 57099936 Query thread_id=17376 exec_time=0 error_code=0
use v5cwcs85101_train;
SET TIMESTAMP=1154115884;
SET @@session.foreign_key_checks=0, @@session.sql_auto_is_null=1, @@session.unique_checks=0;
SET @@session.sql_mode=524288;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8;
CREATE ALGORITHM=UNDEFINED DEFINER=`nirvana`@`%` SQL SECURITY DEFINER VIEW `v_active_status_types` AS select `status_types`.`status_type_id` AS `status_type_id`,`status_types`.`status_type_name` AS `status_type_name`,`status_types`.`status_type_description` AS `status_type_description`,`status_types`.`hold_inventory_ind` AS `hold_inventory_ind`,`status_types`.`status_type_group` AS `status_type_group`,`status_types`.`display_color` AS `display_color`,`status_types`.`hold_time` AS `hold_time` from `status_types` where (`status_types`.`hold_inventory_ind` = 1) */;
-- ON SLAVE --
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.195
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: 93137-db1-bin.000242
Read_Master_Log_Pos: 161083900
Relay_Log_File: 93136-app1-relay-bin.000757
Relay_Log_Pos: 52866604
Relay_Master_Log_File: 93137-db1-bin.000242
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: 'v5cwcs85101_train'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`nirvana`@`%` SQL SECURITY DEFINER VIEW `v_active_status_types` AS select `status_types`.`status_type_id` AS `status_type_id`,`status_types`.`status_type_name` AS `status_type_name`,`status_types`.`status_type_description` AS `status_type_description`,`status_types`.`hold_inventory_ind` AS `hold_inventory_ind`,`status_types`.`status_type_group` AS `status_type_group`,`status_types`.`display_color` AS `display_color`,`status_types`.`hold_time` AS `hold_time` from `status_types` where (`status_types`.`hold_inventory_ind` = 1) */' Skip_Counter: 0
Exec_Master_Log_Pos: 57099293
Relay_Log_Space: 156851504
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)
How to repeat:
Configure replication between two MySQL 5.0.22 servers.
master: use test
master: CREATE TABLE t1 (id INT(4) NOT NULL AUTO_INCREMENT, name varchar(10), PRIMARY KEY (ID));
master: /*!50001 CREATE VIEW count_t1 AS SELECT COUNT(*) from t1 */;
slave : SHOW SLAVE STATUS\G
Suggested fix:
Do not remove the prefixing '/*!50001' from the query when writing it to the binlog.
Description: Hello, It appears that when you specify /*!50001 CREATE VIEW.... */ It will write a query with an invalid syntax in the binlog. It apparently removes the "/*!50001" but leaves the trailing "*/" intact when the query is written to the log. This causes an error on the slave node which causes replication to break. -- IN MASTER BINLOG -- # at 57099293 #060728 15:44:44 server id 1 end_log_pos 57099936 Query thread_id=17376 exec_time=0 error_code=0 use v5cwcs85101_train; SET TIMESTAMP=1154115884; SET @@session.foreign_key_checks=0, @@session.sql_auto_is_null=1, @@session.unique_checks=0; SET @@session.sql_mode=524288; /*!\C utf8 */; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8; CREATE ALGORITHM=UNDEFINED DEFINER=`nirvana`@`%` SQL SECURITY DEFINER VIEW `v_active_status_types` AS select `status_types`.`status_type_id` AS `status_type_id`,`status_types`.`status_type_name` AS `status_type_name`,`status_types`.`status_type_description` AS `status_type_description`,`status_types`.`hold_inventory_ind` AS `hold_inventory_ind`,`status_types`.`status_type_group` AS `status_type_group`,`status_types`.`display_color` AS `display_color`,`status_types`.`hold_time` AS `hold_time` from `status_types` where (`status_types`.`hold_inventory_ind` = 1) */; -- ON SLAVE -- mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.195 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: 93137-db1-bin.000242 Read_Master_Log_Pos: 161083900 Relay_Log_File: 93136-app1-relay-bin.000757 Relay_Log_Pos: 52866604 Relay_Master_Log_File: 93137-db1-bin.000242 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: 'v5cwcs85101_train'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`nirvana`@`%` SQL SECURITY DEFINER VIEW `v_active_status_types` AS select `status_types`.`status_type_id` AS `status_type_id`,`status_types`.`status_type_name` AS `status_type_name`,`status_types`.`status_type_description` AS `status_type_description`,`status_types`.`hold_inventory_ind` AS `hold_inventory_ind`,`status_types`.`status_type_group` AS `status_type_group`,`status_types`.`display_color` AS `display_color`,`status_types`.`hold_time` AS `hold_time` from `status_types` where (`status_types`.`hold_inventory_ind` = 1) */' Skip_Counter: 0 Exec_Master_Log_Pos: 57099293 Relay_Log_Space: 156851504 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) How to repeat: Configure replication between two MySQL 5.0.22 servers. master: use test master: CREATE TABLE t1 (id INT(4) NOT NULL AUTO_INCREMENT, name varchar(10), PRIMARY KEY (ID)); master: /*!50001 CREATE VIEW count_t1 AS SELECT COUNT(*) from t1 */; slave : SHOW SLAVE STATUS\G Suggested fix: Do not remove the prefixing '/*!50001' from the query when writing it to the binlog.