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!

Multiple MySQL Service conflict

Hi there,

At beginning of programming life, I installed MySQL 5 by downloading it directly from their website. I was using it with IIS (installed PHP) and it worked fine.

Later, I decided to not use IIS because my office programming interface was using Xampp and I was given no option because I need the Xampp to run Apache. I disabled my IIS and installed Xampp. All worked fine.

Until one fine day, I accidentally "Stop" and "Start" the MySQL service provided in Xampp Control Panel. As result of it, all my database went missing (no such DB anymore). I went panic until I understood that Xampp has its own MySQL Service offered in the bundle. In this case, when I clicked "Stop", it had actually stop my original MySQL service. And when I clicked "Start", it started the Xampp's bundled MySQL service. They both are 2 different service and has 2 different data folder.

Basically, the original MySQL service name would be "MySQL" and the Xampp bundled service name would be "mysql" (only the letter case are different).

The problem is, once the "mysql" service has been started (and stopped), the "MySQL" service cannot be started. It would show "Service unable to start. Error 0".

Googling further helped me to solve this issue with little trick of commenting out (disabling) a single line in the my.ini (MySQL's configuration file).
Reference: http://www.experts-exchange.com/Database/MySQL/Q_24356864.html

I comment out this line,
default-storage-engine=INNODB    (add # at beginning of line to comment out)
in the configuration file stored at this path,
C:\Program Files\MySQL\MySQL Server 5.0\my.ini   (your config file may be different depend on where you installed the original MySQL)

And then, start the "MySQL" service like this,
Run "SERVICES.MSC", look for "MySQL", right-click on it and select "Start".

Done, the service runs again like charm and I can access my databases again without crashing them ;)

Have fun guys!