Why "No default value"?

[uprightdog]

I have a script running under Apache 2 on a Unix server. This script inserts into MySQL 4.1.21. This script works perfectly.

I also have a development server - Apache 2 under Windows - running the same script. My development server is using MySQL 5.0.22. This script works far from perfectly. When doing an insert, all fields of a record must either be set with a value, have a default value set in advance, or be nullable. Otherwise MySQL throws a "Field xxx doesn't have a default value" error.

Why is this? And, is there anyway to bypass this issue, without overhauling dozens of database tables, or messing around with the third-party scripts?

[longneck]

in mysql 4.0:

mysql> create table test (i int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `i` int(11) NOT NULL default '0'
) TYPE=MyISAM
1 row in set (0.00 sec)

in mysql 5:

mysql> create table test (i int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `i` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

as you can see, mysql 4.0 automatically adds a DEFAULT clause if you don't specify one. they dropped this "feature" in mysql 5.0.

my suggestion is to never rely on "magic" features such as this and ALWAYS specify EXACTLY what you want.

[r937]

they dropped this "feature" in mysql 5.0.

don't hint at it, longneck, say EXACTLY what you mean

inserting a DEFAULT was bad, and it is good that 5.0 no longer does this

[uprightdog]

as you can see, mysql 4.0 automatically adds a DEFAULT clause if you don't specify one. they dropped this "feature" in mysql 5.0.

Is this connected to 'Strict Mode' - is this mode just set by default in 5.0?

[longneck]

i'm not in a position to test that right now. you could test it and find out. in this case, i'm going to venture a guess and say no.

you should be aware that changing server defaults like this is generally a bad idea, especially since your application may be used in situations where the user has no control over server settings.