Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.5.8
-
openSUSE Leap 15.3
Description
When inserting into a table with an AUTO_INCREMENT column and inserting the function name now() it fails and causes the auto_increment value for the table to jump to the maximum for the data type. No further records can be inserted until the rouge rows are deleted and the auto_increment is reset. (Not a common mistake to make in scripting, but can happen if inserted fields are in the incorrect order.)
CREATE TABLE `T_test` ( |
`ID` int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`ID`) |
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; |
 |
insert into T_test values (now()); |
1 row(s) affected, 1 warning(s): 1264 Out of range value for column 'ID' at row 1 |
2147483647
|
Does the same for LOCALTIMESTAMP() & LOCALTIME()
select AUTO_INCREMENT from information_schema.TABLES where TABLE_NAME='T_test'; |
2147483647
|
set @nowtime=now(); insert into T_test values (@nowtime); |
does not give the error.