Turn off MySQL strict mode
by bernt & torsten
I recently run into a problem with a new server, some features of the application I was using was not working anymore.
Finding the problem
The application I’m using is built with CodeIgniter, in CodeIgniter, there is an error logging feature, so I switch that one to see what type of error I was getting.
To my surprise I was getting SQL errors on Insert and Updates, looking closer I saw that the SQL statement for two specific fields was blank ‘; and that the field type in the table was set to an integer.
Researching
I googled to find out what the issue could be, and that’s when I found a statement that the default value for MySql for 5.6 is set to strict mode.
I checked the new server, and the MySql on that server is version 5.6, the fix of removing the strict mode is straight forward. Make the following changes in the “my.ini/my.cnf”:
Look for the following line:
sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Change it to:
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Then add
sql-mode="" (Blank)
- Restart the MySQL service.
- You could also do the following, it’s just temporarily.
mysql> SET GLOBAL sql_mode = '';
If you using this method, you would need to add it each time you restart MySQL or server reboot.
Why It’s Important to Exercise When You’re Over 60
Many of us find ourselves in a pickle as the years pile up. Once reliable sidekicks, our...
A Poem: The Last Time
You never know when it will be,
The last time you ski down slopes of snow,
A Poem: Time Millionaire
When the morning wakes, still and clear,
No more alarms, no more rush....