| 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: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1.22 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[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
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