Oracle 5.0, Reference Manual

Page: 1518 / 3232

Replication Features and Issues
1498
• When a statement uses a stored function that inserts into an
AUTO_INCREMENT
column, the
generated
AUTO_INCREMENT
value is not written into the binary log, so a different value can in
some cases be inserted on the slave. This is also true of a trigger that causes an
INSERT
into an
AUTO_INCREMENT
column.
• An insert into an
AUTO_INCREMENT
column caused by a stored routine or trigger running on a
master that uses MySQL 5.0.60 or earlier does not replicate correctly to a slave running MySQL
5.1.12 through 5.1.23 (inclusive). (Bug #33029)
• 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 logged or replicated correctly.
This issue does not affect tables using the
InnoDB
storage engine, since
InnoDB
does not allow the
creation of a composite key that includes an
AUTO_INCREMENT
column that is not the first column in
the key.
• 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;
Important
To guarantee the same ordering on both master and slave, the
ORDER BY
clause must name all columns of
t1
.
The instructions just given are subject to the limitations of
CREATE TABLE ... LIKE
: Foreign key
definitions are ignored, as are the
DATA DIRECTORY
and
INDEX DIRECTORY
table options. If a
table definition includes any of those characteristics, create
t2
using a
CREATE TABLE
statement
that is identical to the one used to create
t1
, but with the addition of the
AUTO_INCREMENT
column.
Regardless of the method used to create and populate the copy having the
AUTO_INCREMENT
column, the final step is to drop the original table and then rename the copy:
DROP t1;
ALTER TABLE t2 RENAME t1;
See also Section C.5.7.1, “Problems with
ALTER TABLE
” .
16.4.1.2. Replication and Character Sets
The following applies to replication between MySQL servers that use different character sets:
• If the master uses MySQL 4.1, you must always use the same global character set and collation on
the master and the slave, regardless of the slave MySQL version. (These are controlled by the
--
character-set-server
[403] and
--collation-server
[403] options.) Otherwise, you may
get duplicate-key errors on the slave, because a key that is unique in the master character set might
not be unique in the slave character set. Note that this is not a cause for concern when master and
slave are both MySQL 5.0 or later.
• If the master is older than MySQL 4.1.3, the character set of any client should never be made
different from its global value because this character set change is not known to the slave. In other
words, clients should not use
SET NAMES
,
SET CHARACTER SET
, and so forth. If both the master
and the slave are 4.1.3 or newer, clients can freely set session values for character set variables
because these settings are written to the binary log and so are known to the slave. That is, clients