October 31, 2020

Out Here In The Fields

…of musings and ramblings

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

2 min read

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

 

 

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Copyright © All rights reserved. | Newsphere by AF themes.