| Bug #24689 | Create index in stored procedure fails replication | ||
|---|---|---|---|
| Submitted: | 29 Nov 2006 12:41 | Modified: | 30 Nov 2006 11:18 |
| Reporter: | Ruturaj Vartak | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.24a | OS: | Linux (Fedora 5) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | CALL, create index, replicaton, stored procedures | ||
[29 Nov 2006 21:57]
MySQL Verification Team
Thank you for the bug report. I was unable to repeat with the current
source server. Please verify with the latest release server or wait
for the release of the current source server.
miguel@hegel:~/dbs/5.0> bin/mysql --defaults-file=/home/miguel/dbs/5.0/my.cnf -uroot --prompt="master>"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.32-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
master>show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 | 218 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
master>use test
Database changed
master>delimiter $$
master>CREATE DEFINER=`root`@`localhost` procedure tst()
-> begin
-> drop table if exists repl_table;
-> create table repl_table (rpl int);
-> create index ix_rpl on repl_table(rpl);
-> end $$
Query OK, 0 rows affected (0.03 sec)
master>delimiter ;
master>call tst() ;
Query OK, 0 rows affected, 1 warning (0.05 sec)
master>show warnings;
+-------+------+----------------------------+
| Level | Code | Message |
+-------+------+----------------------------+
| Note | 1051 | Unknown table 'repl_table' |
+-------+------+----------------------------+
1 row in set (0.00 sec)
master>
-----------------------------------------------------------------------
miguel@hegel:~/dbs/5.0s> bin/mysql --defaults-file=/home/miguel/dbs/5.0s/my.cnf -uroot --prompt="slave>"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.32-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
slave>CHANGE MASTER TO
-> MASTER_HOST='localhost',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='slave',
-> MASTER_LOG_FILE='binlog.000001',
-> MASTER_LOG_POS=218;
Query OK, 0 rows affected (0.02 sec)
slave>start slave;
Query OK, 0 rows affected (0.01 sec)
slave>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 218
Relay_Log_File: hegel-relay-bin.000002
Relay_Log_Pos: 232
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 218
Relay_Log_Space: 232
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)
slave>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 460
Relay_Log_File: hegel-relay-bin.000002
Relay_Log_Pos: 474
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 460
Relay_Log_Space: 474
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)
slave>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 775
Relay_Log_File: hegel-relay-bin.000002
Relay_Log_Pos: 789
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 775
Relay_Log_Space: 789
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.01 sec)
slave>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
slave>show tables;
+----------------+
| Tables_in_test |
+----------------+
| repl_table |
+----------------+
1 row in set (0.01 sec)
slave>
[30 Nov 2006 11:18]
Ruturaj Vartak
Hi,
Its seems the bug has vanished in v 5.0.27
##########################################
Check how the complete index statement has propagated in the binary log
##########################################
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-3308.000002 | 705 | Query | 3308 | 811 | use `test`; create table repl_table (
rpl int
) |
| mysql-3308.000002 | 811 | Query | 3308 | 920 | use `test`; create index ix_rpl on repl_table(rpl)
Description: When a call is made to a stored procedure in which an index is created on a table, the command that is logged in the binlog is half or incorrect, which when read by the slave server causes SQL syntax Error. How to repeat: create a replication for test.sometable Master Server --------------- 1. Create a Stored procedure as follows CREATE DEFINER=`root`@`localhost` procedure tst() begin drop table if exists repl_table; create table repl_table (rpl int); create index ix_rpl on repl_table(rpl); end This SP is properly replicated to the Slave. 2. Call the SP on the Master call tst() ; Here is what goes into the binlog | mysql-3307.000004 | 2692 | Query | 3307 | 2794 | use `test`; drop table if exists repl_table | | mysql-3307.000004 | 2794 | Query | 3307 | 2918 | use `test`; create table repl_table ( rpl int) | | mysql-3307.000004 | 2918 | Query | 3307 | 3026 | use `test`; create index ix_rpl on repl_table(rpl | 3. Check the slave status *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-3307.000004 Read_Master_Log_Pos: 3666 Relay_Log_File: ruturaj-vartak-relay-bin.000002 Relay_Log_Pos: 767 Relay_Master_Log_File: mysql-3307.000004 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: test,test Replicate_Ignore_DB: Replicate_Do_Table: test.logtable,test.logtable 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: 'test'. Query: 'create index ix_rpl on test.repl_table(rpl' Skip_Counter: 0 Exec_Master_Log_Pos: 3553 Relay_Log_Space: 880 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) Suggested fix: When the index is created at the time of creation of the table, this replication error does not pop.