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:
Category:MySQL Server Severity:S3 (Non-critical)
Version:before 5.0.2 OS:
Assigned to: CPU Architecture:Any

[4 Aug 2005 21:56] StuFF mc
This seems to be allowed in MySql 4



How to repeat:
Try installing Mambo on a MySql 4, works, on 5, does not (which is logical because default '' is not an int)
[5 Aug 2005 7:26] Valeriy Kravchuk
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.
[5 Aug 2005 7:39] StuFF mc
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.
[5 Aug 2005 7:43] Valeriy Kravchuk
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.
[5 Aug 2005 7:50] StuFF mc
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...