MySQL Bugs: #18551: Incorrect integer value

Bug #18551	Incorrect integer value
Submitted:	27 Mar 2006 18:26	Modified:	27 Mar 2006 19:16
Reporter:	Daniel Brabec	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 4 and prior, if you set int_column = '', it was converted to the zero value. In MySQL 5 it causes error "Incorrect integer value".

It isn't an error at all, but it's very unpleasant, if your applications uses this "autoconversion" and now it doesn't work.

I haven't found any about this problem in documentation and I don't know whether it's a bug or it's intended.

How to repeat:
CREATE TABLE `test` (
`int_column` INT NOT NULL
) TYPE = MYISAM ;

INSERT INTO test SET int_column = '';

@Daniel

this is an sql_mode issue.  To have the old behaviour you should execute
set sql_mode = ''; -- empty string means 'traditional MySQL-mode'

Refer to: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Also if you use the configuration wizard at install you may specify this as the default for connections.

Also note that TYPE is depreciated - use ENGINE instead.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Thank you for the bug report. You already got the correct answer from
Mr. Peter Laursen.

I found that global.sql_mode='' gives me  different behavior than
global.sql_mode=TRADITIONAL. With the former I was still able to insert blank
integers surrounded by quotes. With the latter I could finally get an error instead of a warning, which is what I wanted. This is MySQL 5.0.22 windows.

I take this behavior to mean that 'traditional MySQL-mode' is different than setting mysql to TRADITIONAL.

Troy Vitullo