All

Fix for this is now pushed into maria-5.5-monty tree
After QA this will be pushed into MariaDB 5.5.
Should be safe to push into 5.5 as this only makes errors consistent and the errors only happens in out-of-range conditions, which is not something very common.

The following diff in revno 3535 on maria-5.5-monty shows a problem. INSERT_ID is gone from the binary log; it means that a slave will treat the INSERT IGNORE statement with the standard INSERT_ID (in this case, 1), and thus will insert a value successfully, while on master it was ignored; so, we'll have a data discrepancy between master and slave.
INSERT_ID should stay in the binlog.

+++ mysql-test/suite/binlog/r/binlog_stm_binlog.result 2012-09-13 19:04:10 +0000
@@ -388,12 +388,11 @@
reset master;
create table t1 (id tinyint auto_increment primary key);
set insert_id=128;
-insert into t1 values(null);
+insert ignore into t1 values(null);
Warnings:
Warning 1264 Out of range value for column 'id' at row 1
select * from t1;
id
-127
drop table t1;
create table t1 (a int);
create table if not exists t2 select * from t1;
@@ -408,8 +407,7 @@
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; create table t1 (id tinyint auto_increment primary key)
master-bin.000001 # Query # # BEGIN
-master-bin.000001 # Intvar # # INSERT_ID=127
-master-bin.000001 # Query # # use `test`; insert into t1 values(null)
+master-bin.000001 # Query # # use `test`; insert ignore into t1 values(null)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
master-bin.000001 # Query # # use `test`; create table t1 (a int)

In the scenario below, with the old version the 2nd INSERT succeeds (without any warnings) and inserts 127, while with the new version it fails with "Out of range value". The reason is that after the first INSERT with the old version AUTO_INCREMENT value on the table is set to 127, while with the old version – to 128. The latter (128) is more logical of the two, so I don't think it's a big deal since it's only one value that we can theoretically lose in a rather unlikely scenario, but technically it can be considered a regression.

New version:
---------------

drop table if exists t1;

1. Query OK, 0 rows affected (0.05 sec)

create table t1 (i tinyint auto_increment primary key) engine=innodb;

1. Query OK, 0 rows affected (0.23 sec)

insert into t1 values (127),(null);

1. ERROR 1264 (22003): Out of range value for column 't1' at row 2

insert into t1 values (null);

1. ERROR 1264 (22003): Out of range value for column 't1' at row 1

select * from t1;

1. Empty set (0.00 sec)

Old version:
--------------

drop table if exists t1;

1. Query OK, 0 rows affected (0.07 sec)

create table t1 (i tinyint auto_increment primary key) engine=innodb;

1. Query OK, 0 rows affected (0.24 sec)

insert into t1 values (127),(null);

1. ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'

insert into t1 values (null);

1. Query OK, 1 row affected (0.04 sec)

select * from t1;

1. -----

2. i

3. -----

4. 127

5. -----
6. 1 row in set (0.00 sec)

For a note, I find the behavior in general somewhat unfortunate – one could expect that the AUTO_INCREMENT value wouldn't change at all if the INSERT didn't succeed; but even if it can be improved, it goes far beyond the scope of this task.

As a nice side-effect, this patch fixes a crash which happens on MySQL/MariaDB 5.5 with the following scenario:

CREATE TABLE t1 (i TINYINT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB AUTO_INCREMENT=128;
INSERT INTO t1 VALUES (NULL);

On maria-5.5 revno 3533, mysql-5.5.27:

InnoDB: Assertion failure in thread 140005269079808 in file ha_innodb.cc line 1478
InnoDB: Failing assertion: current <= max_value

On maria-5.5-monty revno 3535:

Out of range value for column 't1' at row 1
(as expected)

Replication old master (5.5.27 and earlier) => new slave (maria-5.5-monty) breaks due to the different error code:

On master:

drop table if exists t3;
create table t3 (i tinyint auto_increment primary key) engine=myisam;
insert into t3 values (126),(null),(null);

1. ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'

On slave:

1. Slave_IO_Running: Yes
2. Slave_SQL_Running: No
3. Last_Errno: 1264
4. Last_Error: Out of range value for column 't3' at row 3

[ERROR] Slave SQL: Query caused different errors on master and slave. Error on master: message (format)='Duplicate entry '%-.192s' for key %d' error code=1062 ; Error on slave: actual message='Out of range value for column 't3' at row 3', error code=1264. Default database: 'test'. Query: 'insert into t3 values (126),(null),(null)', Error_code: 0
[ERROR] Slave SQL: Out of range value for column 't3' at row 3, Error_code: 1264

The "out of range" error message contains wrong data: table name instead of a column name:

create table t1 (i tinyint auto_increment primary key);
insert into t1 values (127),(null);

1. ERROR 1264 (22003): Out of range value for column 't1' at row 2

Regarding the comment about that we now get an out-of-range error when trying to do insert a too big value in an auto-increment column instead of getting the 'max allowed value' inserted.
I see this as a bug fix and the old behavior was wrong; We should never inserted a smaller number than requested as this could break replication in some scenarios.
This also fixes the case when reset a table by dropping and recreating with specific AUTO_INCREMENT=xxx in the CREATE statement.
With the old code we would have inserted an already used value. Now you will get an error if the first value is out of range.

New patch pushed into maria-5.5-monty. This includes a patch for the wrong usage of table names.

I have now fixed the problem with replication from old master to new slave so that ER_DUP_ENTRY and HA_ERR_AUTOINC_ERANGE are regarded as equal errors.
This will fix most (should be all-real-life) cases of this kind of replication issues.

Checked on revno 3540, all of OM=>NS replication problem, missing INSERT_ID and the column name are fixed now; no new problems have been found so far. I think the patch can be pushed into maria/5.5 now.

4 day coding, one day testing
Pushed into 5.5 to be released in 5.5.28