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.
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:
- 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.