MySQL Strict-Mode need to be not so strict!

Hi,

The best part of PHP or MySQL is their flexibility. MySQL by default has Strict-Mode which will hold you tight to follow the standard rules.

It is a great thing that we all can follow the appropriate standard rules and we have the discipline. But somehow, when you come across scripts that require "not so strict" mode, then we need to work flexible with that script instead of changing all over again.

Just assume that we are freelance programmer and thrown into a large project which has its mysql commands all generated without any strict discipline. We got to work and follow the same instead of keep being standard coder.

The common 2 problems when you are running Strict-Mode:
1) Cannot insert '' (empty string) into Integer Field.
2) Cannot automatically insert NULL if a field is required field.

If you running your MySQL database locally and you need to turn off the Strict-Mode, then look for my.ini file in your MySQL installation folder. For me it was default install, so the file was at: C:\Program Files\MySQL\MySQL Server 5.0\my.ini

Search/Find for "SQL mode". Then you will see the lines;
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Just remove the "STRICT_TRANS_TABLES," from the second line and leave it like this;
# Set the SQL mode to strict
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

That's it!

Restart your MySQL service and you will be running the MySQL without rules. Follow the project standard although it is not so standard ;)

Be flexible! 
Have fun!

====== Update as on 7th January 2013 ======

I noticed the sql-mode is no longer in latest MySQL installations's my.ini configuration file. They made it even easier.

First, execute this SQL command:
SELECT @@global.sql_mode;

You should see the MySQL answers you:
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

As you can see, its running strict-mode, so we need to reset the mode, execute this SQL command:
SET @@global.sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Walaaa! You are done! But if you want to re-confirm that you are no longer running script mode, then execute again this SQL command:

SELECT @@global.sql_mode;

You will see the MySQL answered you:

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Far easier than messing about with the configuration file right? Cool!