Replication and AUTO-opracowanie

Replication and AUTO_INCREMENT
Statement-based replication of AUTO_INCREMENT, LAST_INSERT_ID() [1290], and TIMESTAMP
values is done correctly, subject to the following exceptions:
• When using statement-based replication prior to MySQL 5.6.10, AUTO_INCREMENT columns
in tables on the slave must match the same columns on the master; that is, AUTO_INCREMENT
columns must be replicated to AUTO_INCREMENT columns. (Bug #12669186)
• A statement invoking a trigger or function that causes an update to an AUTO_INCREMENT column
is not replicated correctly using statement-based replication. In MySQL 5.6, such statements are
marked as unsafe. (Bug #45677)
• An INSERT into a table that has a composite primary key that includes an AUTO_INCREMENT
column that is not the first column of this composite key is not safe for statement-based logging or
replication. Beginning with MySQL 5.6.6, such statements are marked as unsafe. (Bug #11754117,
Bug #45670)
This issue does not affect tables using the InnoDB storage engine, since an InnoDB table with an
AUTO_INCREMENT column requires at least one key where the auto-increment column is the only
or leftmost column.
• Adding an AUTO_INCREMENT column to a table with ALTER TABLE might not produce the same
ordering of the rows on the slave and the master. This occurs because the order in which the rows
are numbered depends on the specific storage engine used for the table and the order in which
the rows were inserted. If it is important to have the same order on the master and slave, the rows
must be ordered before assigning an AUTO_INCREMENT number. Assuming that you want to add an
AUTO_INCREMENT column to a table t1 that has columns col1 and col2, the following statements
produce a new table t2 identical to t1 but with an AUTO_INCREMENT column:
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
... zobacz całą notatkę