| Bug #69680 | Auto_inc value not properly generated with RBR and auto_inc column only on slave | ||
|---|---|---|---|
| Submitted: | 5 Jul 2013 17:44 | Modified: | 15 Jan 2014 7:55 |
| Reporter: | Ovais Tariq | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S3 (Non-critical) |
| Version: | 5.5.32 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Jul 2013 10:49]
MySQL Verification Team
Hello Ovais, Thank you for the bug report and the test case. Verified as described. Thanks, Umesh
[8 Jul 2013 10:51]
MySQL Verification Team
// Duplicate key error on 5.5.32, 5.6.12 [ERROR] Slave SQL: Could not execute Write_rows event on table test.tbl; Duplicate entry '0' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000001, end_log_pos 961, Error_code: 1062
[8 Jul 2013 10:52]
MySQL Verification Team
mtr test case
Attachment: 69680.test (application/octet-stream, text), 1007 bytes.
[15 Jan 2014 7:55]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
[15 Jan 2014 7:55]
Jon Stephens
Documented fix in the 5.5.37, 5.6.17, and 5.7.4 changelogs as follows:
When using row-based replication, an additional auto-increment
column on the slave version of a table was not updated
correctly; a zero was inserted instead.
Closed.
[27 Mar 2014 13:44]
Laurynas Biveinis
5.5$ bzr log -r 4579 -n0
------------------------------------------------------------
revno: 4579 [merge]
committer: Luis Soares <luis.soares@oracle.com>
branch nick: mysql-5.5
timestamp: Thu 2014-01-09 12:53:49 +0000
message:
BUG#17066269
- Automerged from bug branch into latest mysql-5.5.
- Fixed trailing whitespaces.
- Updated the copyright notice year to 2014.
------------------------------------------------------------
revno: 4560.1.1
committer: Luis Soares <luis.soares@oracle.com>
branch nick: mysql-5.5
timestamp: Wed 2013-12-18 11:17:24 +0000
message:
BUG#17066269: AUTO_INC VALUE NOT PROPERLY GENERATED WITH RBR AND
AUTO_INC COLUMN ONLY ON SLAVE
In RBR, if the slave's table as one additional auto_inc column,
then, it will insert the value 0 instead of generating the next
auto_inc number.
We fix this by checking that if an auto_inc extra column exists,
when compared to column data of the row event, we explicitly set
it to NULL and flag the engine that a nulled auto_inc column will
be inserted.
Description: If I have a master and a slave with a table that has the auto_increment column only on the slave, then the auto_increment values are not generated on the slave when ROW replication is being used. The value inserted into the auto_increment column is always 0. This is different from how auto_increment works when a query is executed directly without specifying the value of the auto_increment column. The default sql_mode is being used, and so a 0 or a NULL value should mean that the next sequence number should be generated. MySQL version info: Server version: 5.5.32-log MySQL Community Server (GPL) How to repeat: -- Check the binlog_format and make sure its ROW master [localhost] {msandbox} (test) > select @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | ROW | +-----------------+ 1 row in set (0.00 sec) -- Check the sql_mode on the slave slave1 [localhost] {msandbox} (test) > select @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec) -- Prepare the table master [localhost] {msandbox} (test) > CREATE TABLE `tbl` ( -> `a` int(11) NOT NULL DEFAULT '0' -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.22 sec) master [localhost] {msandbox} (test) > insert into tbl(a) values (1), (2), (4), (8); Query OK, 4 rows affected (0.23 sec) Records: 4 Duplicates: 0 Warnings: 0 master [localhost] {msandbox} (test) > select * from tbl; +---+ | a | +---+ | 1 | | 2 | | 4 | | 8 | +---+ 4 rows in set (0.00 sec) -- Add auto_increment column on the slave slave1 [localhost] {msandbox} (test) > alter table tbl add column i int(11) not null auto_increment primary key; Query OK, 4 rows affected (0.26 sec) Records: 4 Duplicates: 0 Warnings: 0 -- Do an insert on the master master [localhost] {msandbox} (test) > insert into tbl(a) values(16); Query OK, 1 row affected (0.21 sec) slave1 [localhost] {msandbox} (test) > select * from tbl; +---+---+ | a | i | +---+---+ | 1 | 1 | | 2 | 2 | | 4 | 3 | | 8 | 4 | +---+---+ 4 rows in set (0.00 sec) master [localhost] {msandbox} (test) > select * from tbl; +----+ | a | +----+ | 1 | | 2 | | 4 | | 8 | | 16 | +----+ 5 rows in set (0.00 sec) -- Check the generated value of the auto_increment column for the new row slave1 [localhost] {msandbox} (test) > select * from tbl; +----+---+ | a | i | +----+---+ | 16 | 0 | | 1 | 1 | | 2 | 2 | | 4 | 3 | | 8 | 4 | +----+---+ 5 rows in set (0.00 sec) As you can see that the column `i` was set to a value of 0. Suggested fix: The behavior should be similar to what happens during a regular INSERT, where non-presence of auto_increment column in the INSERT query, or the insertion of 0 or NULL causes the next auto_increment sequence number to be generated.