Bug #31334 Out of range values are inserted without throwing error
Submitted: 2 Oct 2007 9:46 Modified: 3 Oct 2007 10:22
Reporter: media forest Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[2 Oct 2007 9:46] media forest
When inserting a too big value into an int column, mysql doesn't throw any error message and silently fill the field with the max value the field can contain.

How to repeat:
  `ID` int(10) NOT NULL auto_increment,
  `Ref` varchar(50) character set latin1 default NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO TEST VALUES (3118957900,'spp3916_20071002');

at this point everything seems to have worked, but when trying :

INSERT INTO TEST VALUES (3118957901,'spp3916_20071003');

Mysql throw an error about duplicate value 2147483647 for the key

Suggested fix:
Mysql shouldn't accept an out of range value, or It should say something about that, and it should not fill a field with a default value.
As it can have serious consequences on a database system, the message be an error, not a warning
[2 Oct 2007 11:44] MySQL Verification Team
Thank you for the bug report. Use the sql_mode for:

mysql> set sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO TEST VALUES (3118957900,'spp3916_20071002');
ERROR 1264 (22003): Out of range value adjusted for column 'ID' at row 1
[3 Oct 2007 10:22] media forest
I thought that inserting a bad value as Primary Key should throw an error in any mode...