MySQL Bugs: #13085: ALTER TABLE not replicating when containing comma

Bug #13085	ALTER TABLE not replicating when containing comma
Submitted:	9 Sep 2005 11:50	Modified:	23 Oct 2005 10:08
Reporter:	Silvan Mühlemann	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.25	OS:	Linux (Gentoo (2.6.11), RH 8.0 (2.4.20))
Assigned to:		CPU Architecture:	Any

Description:
The following statement ist being executed properly on the master, written properly to the binary log but NOT being executed on the slave.

ALTER TABLE `mytable`, ADD`uid` INT( 10 ) UNSIGNED DEFAULT NULL

The following statement executed on the master is being replicated to the slave properly:

ALTER TABLE `mytable` ADD `uid` INT( 10 ) UNSIGNED DEFAULT NULL

Note the comma after the TABLE-Clause.

This causes inconsistency between master and slave. Eventually, when a row is being added to the table the insert won't work on the slaves and the replication stops.

How to repeat:
Execute an ALTER TABLE statement containing a comma on the master.

IMHO, the syntactically false statement (containing the comma) should NOT be executed on the master and instead issue an error to the client.

I was unable to repeat the issue reported with current source server:

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

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

master>show master status\G
*************************** 1. row ***************************
            File: hegel-bin.004
        Position: 79
    Binlog_do_db: 
Binlog_ignore_db: 
1 row in set (0.01 sec)

master>create database dbtest;
Query OK, 1 row affected (0.00 sec)

master>use dbtest;
Database changed
master>create table mytable (id int);
Query OK, 0 rows affected (0.05 sec)

master>ALTER TABLE `mytable`, ADD`uid` INT( 10 ) UNSIGNED DEFAULT NULL
    -> ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

master>insert into mytable set id=1, uid=10;
Query OK, 1 row affected (0.01 sec)

master>desc mytable\G
*************************** 1. row ***************************
  Field: id
   Type: int(11)
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 2. row ***************************
  Field: uid
   Type: int(10) unsigned
   Null: YES
    Key: 
Default: NULL
  Extra: 
2 rows in set (0.00 sec)

master>insert into mytable set id=2, uid=20;
Query OK, 1 row affected (0.01 sec)

master>

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

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

slave>CHANGE MASTER TO
    -> MASTER_HOST='localhost',
    -> MASTER_PORT=3306,
    -> MASTER_USER='miguel',
    -> MASTER_PASSWORD='testguy',
    -> MASTER_LOG_FILE='hegel-bin.003',
    -> MASTER_LOG_POS=79;
Query OK, 0 rows affected (0.00 sec)

slave>start slave;
Query OK, 0 rows affected (0.01 sec)

slave>show slave status\G
*************************** 1. row ***************************
          Master_Host: localhost
          Master_User: miguel
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: hegel-bin.004
  Read_Master_Log_Pos: 79
       Relay_Log_File: hegel-relay-bin.001
        Relay_Log_Pos: 159
Relay_Master_Log_File: hegel-bin.004
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db: 
  Replicate_ignore_db: 
           Last_errno: 0
           Last_error: 
         Skip_counter: 0
  Exec_master_log_pos: 79
      Relay_log_space: 159
1 row in set (0.00 sec)

slave>show databases;
+----------+
| Database |
+----------+
| dbtest   |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

slave>show slave status\G
*************************** 1. row ***************************
          Master_Host: localhost
          Master_User: miguel
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: hegel-bin.004
  Read_Master_Log_Pos: 371
       Relay_Log_File: hegel-relay-bin.001
        Relay_Log_Pos: 451
Relay_Master_Log_File: hegel-bin.004
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db: 
  Replicate_ignore_db: 
           Last_errno: 0
           Last_error: 
         Skip_counter: 0
  Exec_master_log_pos: 371
      Relay_log_space: 451
1 row in set (0.00 sec)

slave>use dbtest;
Database changed
slave>select * from mytable;
+------+------+
| id   | uid  |
+------+------+
|    1 |   10 |
+------+------+
1 row in set (0.00 sec)

slave>desc mytable\G
*************************** 1. row ***************************
  Field: id
   Type: int(11)
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 2. row ***************************
  Field: uid
   Type: int(10) unsigned
   Null: YES
    Key: 
Default: NULL
  Extra: 
2 rows in set (0.00 sec)

slave>show slave status\G
*************************** 1. row ***************************
          Master_Host: localhost
          Master_User: miguel
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: hegel-bin.004
  Read_Master_Log_Pos: 444
       Relay_Log_File: hegel-relay-bin.001
        Relay_Log_Pos: 524
Relay_Master_Log_File: hegel-bin.004
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db: 
  Replicate_ignore_db: 
           Last_errno: 0
           Last_error: 
         Skip_counter: 0
  Exec_master_log_pos: 444
      Relay_log_space: 524
1 row in set (0.00 sec)

slave>

What MySQL version do you use for the master, and for the slave ?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".