Old harbour storage in Oulu Finland

How to disable MySQL strict mode on CodeIgniter

I recently installed a CodeIgniter 3.x application and another PHP application on a new server, I did run into some issues immediately, the apps were broken, it connected to the database. Did not allow me to insert new data, gave me an error, took me some time to figure it out, the issue was that MySQL on the new server was set to strict mode.

MySQL has had a strict mode since 5.1, but in 5.7 it became the default.

For the CodeIgniter 3.x App, I fixed the issue in the code: edit your

database.php

config file, and set the key

stricton

with a value of

false

For my other PHP application, I did not have that choice. One option that I could do was to add into my code after selecting the database

mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name,$link);

the following code

mysql_query("SET SESSION sql_mode = ''");

This will remove the strict mode only when you execute the specific PHP script, you would need to add it in all places where you do mysql_connect.

Another way to disable strict mode globally on your server (maybe some variations between different servers) is as follows:

Your configuration file MySQL actually looks at five different places for configuration files, so you can make the change in several places. It’ll look in

  1. /etc/my.cnf,
  2. /etc/mysql/my.cnf,
  3. SYSCONFDIR/my.cnf,$MYSQL_HOME/my.cnf,
  4.  ~/my.cnf.
~/.my.cnf

is user-specific, and the third and fourth options rely on specifics from your environment.

On a default server, MySQL configuration will live in

/etc/mysql/my.cnf

so let’s put our changes there. SSH into your server and use Vim to edit that file.

Open the file my.cnf and scroll down the file a bit, you’ll find the [mysqld] section. We’re going to add a new key, sql_mode. On MySQL 5.7, the default values for this key out of the box are:

STRICT_TRANS_TABLES, 
ONLY_FULL_GROUP_BY,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION

The strict mode comes from STRICT_TRANS_TABLES. So, let’s overwrite the sql_mode and set it to be the same as the default, but without strict mode.

[mysqld]
sql_mode = ONLY_FULL_GROUP_BY,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Save the file, and restart MySQL. From the command line that would be

sudo /etc/init.d/mysql restart

That’s it!


Posted

in

by

Comments

4 responses to “How to disable MySQL strict mode on CodeIgniter”

  1. Hasan Setiawan Avatar
    Hasan Setiawan

    Thank’s mate this help me a lot!

  2. Trond Sundt Avatar
    Trond Sundt

    Life saver! Thank you very much 🙂

  3. Leon Avatar
    Leon

    Adding $this->db->query(“SET SESSION sql_mode = ””) worked best for me. Thanks very much 🙂

  4. vikas Avatar
    vikas

    this code on your query ONLY_FULL_GROUP_BY disable

    for Mysql:– use this code on your query )-

    $set_mode = “SET sql_mode=(SELECT REPLACE(@@sql_mode,’ONLY_FULL_GROUP_BY’,”))”; $resultss = mysqli_query($this->dbh,$set_mode);

    for codeigniter :– use this code on your query )-

    $this->db->query(“SET sql_mode=(SELECT REPLACE(@@sql_mode, ‘ONLY_FULL_GROUP_BY’, ”));”) ;

Leave a Reply

Your email address will not be published. Required fields are marked *