MySQL Bugs: #31334: Out of range values are inserted without throwing error

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:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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:
CREATE TABLE  `TEST` (
  `ID` int(10) NOT NULL auto_increment,
  `Ref` varchar(50) character set latin1 default NULL,
  PRIMARY KEY  (`ID`)
) 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

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
mysql>

I thought that inserting a bad value as Primary Key should throw an error in any mode...