Where is my my.cnf? And disabling strict mode in mysql


Due to having to maintain compatibility with coping with old and perhaps broken codes, I decided to turn off mysql strict mode. It started with the php paging spurting out  database error such as “Error Number 1265”

1\As well as these “Error Number 1292”

3

This happens because MySQL server is running on strict mode, and the queries might have not properly constructed for it to run on a strict mode, or was constructed for an older version of MySQL. Yes, yes, the proper way to handle this kind of stuff is to fix the code and the database records, but for temporary fix, disabling strict mode should do it.

First, check if strict mode is active:

[surfer@www ~]# mysql -e "SELECT @@sql_mode;"
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

Now, to turn it off, you need to make some change to your my.cnf with your favorite text editor. Your mysql installation might perhaps reading from more than a single my.cnf file. Locate them by doing:

[surfer@www usr]# mysqladmin --help

Look around the output for something similar to this:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Piece of cake, right? Well, I can’t have it easy can I? The file

[root@www usr]# locate my.cnf

This should give you the list of my.cnf file that exist in your system:

[root@www usr]# locate my.cnf
/etc/my.cnf
/root/.my.cnf
/usr/my.cnf
/usr/local/cpanel/whostmgr/my.cnf-5.1
/usr/local/cpanel/whostmgr/my.cnf-5.5
/usr/share/mysql-test/include/default_my.cnf
/usr/share/mysql-test/suite/federated/my.cnf
/usr/share/mysql-test/suite/ndb/my.cnf
/usr/share/mysql-test/suite/ndb_big/my.cnf
/usr/share/mysql-test/suite/ndb_binlog/my.cnf
/usr/share/mysql-test/suite/ndb_rpl/my.cnf
/usr/share/mysql-test/suite/ndb_team/my.cnf
/usr/share/mysql-test/suite/rpl/my.cnf
/usr/share/mysql-test/suite/rpl/extension/bhs/my.cnf
/usr/share/mysql-test/suite/rpl_ndb/my.cnf
/var/cpanel/configs.cache/_etc_my.cnf___1081476658
/var/cpanel/configs.cache/_root_.my.cnf___1081476658

You  might have to check every single one of them to find whether one, or some of them has the “sql_mode” on its’ [mysqld]. I found mine at “/usr/my.cnf”

So, look for [mysqld], and find the sql_mode line, it should look like this:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

To disable strict mode, edit the line to:

sql_mode=""

save, and restart mysql by doing:

sudo systemctl restart mysqld

Or on a pre-systemd do:

sudo service mysqld restart

Now, let’s check if strict mode has been disabled:

[surfer@www ~]# mysql -e "SELECT @@sql_mode;"
+------------+
| @@sql_mode |
+------------+
|            |
+------------+

If your output is similar to this, we have successfully disable MySQL strict mode and those error above should dissappear.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s