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”
\As well as these “Error Number 1292”
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.