MySQL Bugs: #32606: auto_increment on slave doesn`t work expected after null insert

Bug #32606	auto_increment on slave doesn`t work expected after null insert
Submitted:	21 Nov 2007 23:27	Modified:	29 Nov 2007 12:58
Reporter:	Robert Eisele	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, null, replication

Description:
I use a Master to replicate a table on a seperated slave. The content on the master does not matter, so i will use the blackhole storage engine. The Problem is, that the slave can also insert data in this table - i know it`s not realy clean but in this case the best solution.

How to repeat:
On the master side the table looks like this:

CREATE TABLE test (
  id int(11),
  col varchar(64)
) ENGINE=MyISAM;

As I said, on the master the storage engine will be blackhole.

On the slave side the table looks loke this:

CREATE TABLE test (
  id int(11) auto_increment,
  col varchar(64)
  PRIMARY KEY  (id)
) ENGINE=MyISAM;

The auto_increment should tick from 1 to n by 1 step. When I insert a row on the master i tought the inserted id on the slave is 1, 2, 3,... but it begins at 2783672 - random stuff. Are more than one row inserted by a session on the master the auto_increment works.

Suggested fix:
When the content is NULL, on the slave side the auto_increment trigger should run as expected from a statement based replication.

Thank you for the report.

I can not repeat described behaviour. Please provide your binary log and configuration file for slave.

On the slave the binary log is disabled. I scaned the binary log on the master and found my inserts as i put it on the console:

...
insert into test values (null, 'test')/*!*/;
insert into test values (null, 'test')/*!*/;
insert into test values (null, 'test')/*!*/;
insert into test values (null, 'test')/*!*/;
...

As I indicated yesterday, the master is okay i think. The problem is the slave,
when he receives a NULL value on an AUTO_INCREMENT column. By repeating the query directly on the Slave, it does it right.

When I define "id" as a column without auto_increment and allow NULL, the replication works and the columns become the right value (null).

To fix the problem temporary I implemented an insert trigger on the slave, which simulates the auto_increment functionality.

I activated the binary log on the slave and set also the option log-slave-updates. 

In the slave log-file is the same query:
insert into test (id, col) values (NULL, 'test')/*!*/;

I think it`s a faulty implementation of auto_increment, when the query comes through the relay-log.

Thank you for the feedback.

I am sorry for not clear request.

We need binary log from master and configuration file from slave.

Sorry that I didn`t immediately answered. The replication setup is in a productive environment, so the binary log is very huge. Which information can i scan for you? The important points of the my.cnf of the slaveare:

[mysqld]
server-id               = 38
skip-name-resolve
skip-locking
log-bin
log-slave-updates
replicate-do-table      = data.test

To repeat the problem, simply create the tables from my first post each on the master and slave. Then run a insert on the master with NULL for the field, which should increment automatic on the slave and you will see the faulty auto_increment on the slave though it should hop by auto_increment_increment from auto_increment_offset (both 1).

I haven't yet tried another storage engine on the slave unlike myisam. As I said, I use a trigger to avoid the problem.

Robert,

thank you for the feedback.

I tried to repeat described behaviour in myself environment and got correct sequence.

We need part of binlog (or mysqlbinlog output) which contains:

1. INSERT statement into problem talbe
2. Some other commands you issued after this INSERT
3. INSERT statement into problem talbe execution of which leads to having wrong values in slave table.

That`s really strange. I now have some more information for you. While I was looking, I looked after the Slave version: 5.0.37. I thought, you such an idiot. So I upgraded the Slave up to 5.0.45 but the problem remains. I droped and created the table again. Unfortunately, without success.

I`ve configured mysql with the following command during the installation:
./configure --enable-assembler --prefix=/usr/local/mysql --sysconfdir=/etc --without-debug --without-innodb

My my.cnf (built back for test purpose):

[client]
port                    = 3306
socket                  = /tmp/mysql.sock

[mysqld]
server-id               = 100
user                    = mysql
port                    = 3306
socket                  = /tmp/mysql.sock
replicate-do-table      = data.test

[mysqldump]
quick
max_allowed_packet      = 32M

[mysql]
no-auto-rehash

[isamchk]
key_buffer              = 128M
sort_buffer_size        = 128M
read_buffer             = 2M
write_buffer            = 2M

[myisamchk]
key_buffer              = 128M
sort_buffer_size        = 128M
read_buffer             = 2M
write_buffer            = 2M

[mysqlhotcopy]
interactive-timeout

On the Master I only run the following command. No trigger is activated and otherwise there is no additional query (from mysqlbinlog).

SET TIMESTAMP=1195833636/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=192,@@session.collation_server=33/*!*/;
INSERT INTO `test` (`id`, `col`) VALUES (NULL, 'test')/*!*/;
# at 228808728
#071123 17:00:36 server id 1  end_log_pos 228808858     Query   thread_id=887759        exec_time=0     error_code=0

Here is the table from the master ones more:

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| testa | CREATE TABLE `test` (
  `id` int(11) default NULL,
  `spalte` text character set latin1
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

And the table from the slave:

mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `spalte` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

My temporary fix looks like:

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `id` int(11) null,
    ->   `spalte` text,
    ->   key(id)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter |
mysql> CREATE TRIGGER testinc BEFORE INSERT ON test
    ->   FOR EACH ROW BEGIN
    ->      INSERT INTO stat (id) values (null);
    ->      SET NEW.id=LAST_INSERT_ID();
    ->   END|
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

It works, but it could also be easier.

Thank you for the feedback.

I still can not repeat described behaviour. I beleive there is influence from other queries in your case. But as you can not provide us more representative part of master binary log I close the report as "Can't repeat".