Advertisements

Turn off MySQL strict mode

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 were blank ‘; and that the field type in the table was set to 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”:

1.  Look for the following line:

sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

2.  Change it to:

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Then add

sql-mode="" (Blank)
  1. Restart the MySQL service.

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

Advertisements

Leave a reply:

Your email address will not be published.

Site Footer

Sliding Sidebar

Subscribe for updates

Enter your email address to subscribe to receive notifications of new content by email.

Join 5,807 other subscribers

Advertisements