MySQL Bugs: #20573: strict mode: inserts autogenerated auto

Bug #20573	strict mode: inserts autogenerated auto_increment value bigger than max
Submitted:	20 Jun 2006 14:10	Modified:	25 Sep 2006 17:48
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:	Linux (Linux)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
If auto_increment_offset makes MySQL generate a value bigger than the column's max possible value, the INSERT is accepted in strict mode, while it should be refused.

How to repeat:
latest 5.0:
CREATE TABLE `auto` (
  `a` tinyint(4) NOT NULL auto_increment,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

set auto_increment_increment=1000;

set auto_increment_offset=700;

insert into auto values(null);
Query OK, 1 row affected, 1 warning (0.00 sec)

show warnings;
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1264 | Out of range value adjusted for column 'a' at row 1 | # normal again
+-------+------+-----------------------------------------------------+

truncate table auto;

set sql_mode=strict_all_tables;

insert into auto values(1000);
ERROR 1264 (22003): Out of range value adjusted for column 'a' at row 1 # normal

select * from auto;
Empty set (0.00 sec)

insert into auto values(null);
Query OK, 1 row affected, 1 warning (0.01 sec)

show warnings;
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1264 | Out of range value adjusted for column 'a' at row 1 |
+-------+------+-----------------------------------------------------+

Uh? the warning above should be an error and have made the insertion fail; now in the table we have:
select * from auto;
+-----+
| a   |
+-----+
| 127 |
+-----+

MySQL tried to insert 700 (as expected), but this is bigger than the max possible value of the field,
and instead of failing (this is strict mode!) it inserted the max possible value (127).
Whereas when one explicitely puts 700 in the INSERT, the INSERT fails, as expected.

Suggested fix:
Don't know.
To the bugfixer: when I push fix for BUG#20524, rpl_auto_increment.test will contain queries ready to test the present bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9573

comments sent to dev-bugs

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10919

ChangeSet@1.2252, 2006-08-28 03:12:08-07:00, acurtis@xiphis.org +20 -0
  Bug#20573
    "strict mode: inserts autogenerated auto_increment value bigger than max"
    Strict mode should fail if autoincrement value is out of range

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11114

ChangeSet@1.2252, 2006-08-30 12:12:03-07:00, acurtis@xiphis.org +21 -0
  Bug#20573
    "strict mode: inserts autogenerated auto_increment value bigger than max"
    Strict mode should fail if autoincrement value is out of range

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11124

ChangeSet@1.2252, 2006-08-30 13:20:39-07:00, acurtis@xiphis.org +21 -0
  Bug#20573
    "strict mode: inserts autogenerated auto_increment value bigger than max"
    Strict mode should fail if autoincrement value is out of range

Reviewed by Guilhem who requested minor changes.
Pushed to 5.0-engines tree.

Pushed to 5.1.12.

Pushed to 5.0.26.

Noted in 5.0.26, 5.1.12 changelog.