Bug #32084 ALTER TABLE ADD PRIMARY KEY auto_increment breaks replication
Submitted: 3 Nov 2007 23:14 Modified: 6 Nov 2007 11:49
Reporter: Grigory Rubtsov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1.22 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[3 Nov 2007 23:14] Grigory Rubtsov
Description:
When primary key with auto_increment is added, it's values is different on master and slave. Happens only with ndb but probably may be with other engines under special conditions.

How to repeat:
master> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| amec2-bin.000002 |      106 |              |                  | 
+------------------+----------+--------------+------------------+
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: amec2.webew.ru
                  Master_User: s
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: amec2-bin.000002
          Read_Master_Log_Pos: 106
               Relay_Log_File: amec1-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: amec2-bin.000002
             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: 106
              Relay_Log_Space: 406
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 

master> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    | 
| log_bin_trust_function_creators | OFF   | 
| log_bin_trust_routine_creators  | OFF   | 
+---------------------------------+-------+
3 rows in set (0.00 sec)

master> use test

master> create table tbl (a int) ENGINE=ndb;
master> insert into tbl VALUES (1),(4),(2),(9),(-9);
master> select * from tbl; 
+------+
| a    |
+------+
|    2 | 
|   -9 | 
|    4 | 
|    1 | 
|    9 | 
+------+
5 rows in set (0.04 sec)

slave> select * from tbl;
+------+
| a    |
+------+
|    4 | 
|    2 | 
|    9 | 
|   -9 | 
|    1 | 
+------+
5 rows in set (0.04 sec)

master> alter table tbl add id int primary key auto_increment;

master> select * from tbl;
+------+----+
| a    | id |
+------+----+
|    4 |  3 | 
|    9 |  5 | 
|    2 |  1 | 
|   -9 |  2 | 
|    1 |  4 | 
+------+----+
5 rows in set (0.05 sec)

slave> select * from tbl;
+------+----+
| a    | id |
+------+----+
|    1 |  3 | 
|    2 |  5 | 
|    9 |  1 | 
|   -9 |  2 | 
|    4 |  4 | 
+------+----+
5 rows in set (0.04 sec)

We see that tables are different
[3 Nov 2007 23:15] Grigory Rubtsov
config.ini is the same for master and slave:
[NDBD DEFAULT]
DataDir=/cluster
NoOfReplicas=2
DataMemory=6M
IndexMemory=3M
RedoBuffer=3M
TransactionBufferMemory=1M

[NDB_MGMD]
DataDir=/cluster
HostName=localhost

[NDBD]
[NDBD]

[MYSQLD]
[6 Nov 2007 11:49] Tomas Ulin
so this is the same problem with e.g. myisam

you have to make sure that the scan order is different on the master and slave (they are always in ndb)

example of how to reproduce in myisam
master> create table t1 (a int);
master> insert into t1 values (1),(2),(3);
master> reset master;
slave> create table t1 (a int);
slave> insert into t1 values (3),(2),(1);
slave> start slave;
master> alter table t1 add id int primary key auto_increment;
master> select * from t1;
+------+----+
| a    | id |
+------+----+
|    1 |  1 | 
|    2 |  2 | 
|    3 |  3 | 
+------+----+
3 rows in set (0.00 sec)

slave> select * from t1;
+------+----+
| a    | id |
+------+----+
|    3 |  1 | 
|    2 |  2 | 
|    1 |  3 | 
+------+----+
[3 Dec 2007 22:39] Lars Thalmann
REFINED PROBLEM DESCRIPTION
---------------------------
The auto inc column on slave can get different initial values than
the master

SUGGESTED SOLUTIONS
-------------------
1. Prohibit "ALTER TABLE add auto increment column" when binlog is enabled.
2. Make sure that alter table assigns new numbers in a deterministic way.

I think alternative 1 is probably best.

Then the users will need to do the operation in two steps:
1. alter table add column (without auto inc columns)
2. change column to auto inc column