MySQL Bugs: #12391: DEFAULT '' is allowed for INTEGER NOT NULL column in 4.x.x

Bug #12391	DEFAULT '' is allowed for INTEGER NOT NULL column in 4.x.x
Submitted:	4 Aug 2005 21:56	Modified:	5 Aug 2005 7:43
Reporter:	StuFF mc	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	before 5.0.2	OS:
Assigned to:		CPU Architecture:	Any

Description:
This seems to be allowed in MySql 4

http://mamboforge.net/tracker/?func=detail&atid=101&aid=7062&group_id=5

Regards,

How to repeat:
Try installing Mambo on a MySql 4, works, on 5, does not (which is logical because default '' is not an int)

It is reported as error in 5.0.x:

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.10a-beta-nt |
+-----------------+
1 row in set (0.35 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE `mos_content_rating` (
    ->   `content_id` int(11) NOT NULL default '0',
    ->   `rating_sum` int(11) unsigned NOT NULL default '',
    ->   `rating_count` int(11) unsigned NOT NULL default '0',
    ->   `lastip` varchar(50) NOT NULL default '',
    ->   PRIMARY KEY  (`content_id`)
    -> ) TYPE=MyISAM
    -> ;
ERROR 1067 (42000): Invalid default value for 'rating_sum'

and allowed in 4.0.12 (0 is substituted instead of ''), but let me check latest 4.0.x versions.

Yes I know it is an error on Msql 5, this is how I detect it ;) You might wanna check it on MySql 4.1 also.

Sorry, but this is not a bug (in MySQL, I mean).

This different behaviour of incorrect default values on 4.x.x is documented in the manual (http://dev.mysql.com/doc/mysql/en/constraint-invalid-data.html):

"Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry... 

- If you try to store a string that doesn't start with a number into a numeric column, MySQL Server stores 0."

So, that is exectly the case. 

By the way, use ENGINE= instead of TYPE=, as suggested by warnings on both 4.x.x and 5.0.x versions of MySQL.

I also changed synopsis to more appropriate for future similar "bugs" search.

Okay. Was not sure if it was a bug, but I'm happy that MySql 5 does not allow that anymore ! Have a nice day...