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

[20 Jun 2006 14:10] Guilhem Bichot
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.
[26 Jul 2006 9:05] Bugs System
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
[27 Jul 2006 14:52] Guilhem Bichot
comments sent to dev-bugs
[28 Aug 2006 10:12] Bugs System
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
[30 Aug 2006 19:15] Bugs System
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
[30 Aug 2006 20:21] Bugs System
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
[31 Aug 2006 0:24] Antony Curtis
Reviewed by Guilhem who requested minor changes.
Pushed to 5.0-engines tree.
[21 Sep 2006 7:58] Ingo Strüwing
Pushed to 5.1.12.
[21 Sep 2006 17:11] Ingo Strüwing
Pushed to 5.0.26.
[25 Sep 2006 17:48] Paul Dubois
Noted in 5.0.26, 5.1.12 changelog.