mySQL rules different between dev & live sites

[eluser]theshiftexchange[/eluser]
Hi guys,

I've just noticed that when moving my DB to my live site, I got an error which I could not reproduce in my dev environment.

Its turns out my dev environment mySQL server does not 'enforce' not-null rules in the tables, while the live site does.

So now I'm worried about other things that might be different, not to mention the 'null' vs 'notnull' issue.

Any ideas where/how to find out the 'rules' that are enforced for each server?

[eluser]WanWizard[/eluser]
Afaik 'not null' is an attribute of a table column, which you should have set when you created the table.
So you have to check the way you have recreated your tables...

[eluser]theshiftexchange[/eluser]
[quote author="WanWizard" date="1274809450"]Afaik 'not null' is an attribute of a table column, which you should have set when you created the table.
So you have to check the way you have recreated your tables...[/quote]

Sorry - thats my point - in both databases "not null" is set (i.e. the tables are identical - thats the first thing I checked).

One of the databaes enforces the rule, the other does not...

[eluser]WanWizard[/eluser]
Since MySQL 5.0.2 this is configurable at engine or session level.

It's very nice (not!) that someone has disabled this in the config, usually done to support a poorly written legacy application. Unfortunately, this affects other applications using the same engine, as you have noticed.

If you don't need DB portability, you can enforce it from your application at runtime, by executing

Code:

$this->db->query("SET SESSION sql_mode='STRICT_ALL_TABLES'");

[eluser]theshiftexchange[/eluser]
[quote author="WanWizard" date="1274812618"]Since MySQL 5.0.2 this is configurable at engine or session level.

It's very nice (not!) that someone has disabled this in the config, usually done to support a poorly written legacy application. Unfortunately, this affects other applications using the same engine, as you have noticed.

If you don't need DB portability, you can enforce it from your application at runtime, by executing

Code:

$this->db->query("SET SESSION sql_mode='STRICT_ALL_TABLES'");

[/quote]

Interesting; its actually on my dev box, which a Ubuntu 'lamp' server, with no config changes made by me - so the default is to have it turned off!

I'll have to look into how to change it on my mysql settings...

edit: thanks very much for your help

[eluser]WanWizard[/eluser]
I think it depends on the distro used, and probably the age of the distro/config. I know early MySQL v5's had it turned off by default to retain compatibility with v4.x.

I run CentOS on my production machines, and afaik I haven't changed the default config (with respect to these kind of settings).