Question Change Strict SQL Mode

[KiamOe]

New
Hi I hope you can help me out here..

After upgrading from Mysql 5.5.56 to Mariadb 10.2.17 I have a problem with on old php application. It reports "Error: [PDOException] SQLSTATE[HY000]: General error: 1364 Field 'deleted' doesn't have a default value" and from looking around I understand the problem is related to "Strict SQL Mode". I have read thread here but I can't seem to solve it. I have put sql_mode="" into /etc/my.cnf restarted mysql but I still get the same result:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "SELECT @@sql_mode;"
+-------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+

This is my files:

locate my.cnf
/my.cnf
/etc/my.cnf
/etc/my.cnf.d
/etc/my.cnf.rpmnew
/etc/my.cnf.d/enable_encryption.preset
/etc/my.cnf.d/mysql-clients.cnf
/etc/my.cnf.d/server.cnf
/etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf

I get no result searching for files with sql_mode set (grep -rnw '/etc' -e "sql_mode")

How can I fix this? What config file should I update? Or better do it via sql in phpmyadmin?

Thanks!

 

[KiamOe]

An update to my question:
I did manage to change the sql mode in db via phpMyAdmin / Variables where I found "sql mode" and cleared out the content. Now my php app works fine (it seems).
Was this a safe way of doing it?

 

[IgorG]

Was this a safe way of doing it?

I suppose it is safe. On default Plesk installation I see:

[root@ppu17-8 ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "SELECT @@sql_mode"
+------------+
| @@sql_mode |
+------------+
|            |
+------------+

 

[KiamOe]

Thanks

 

[Bitpalast]

No, it is not really the correct way. The problem is that version 10.2 of MariaDB comes with the default as follows in /etc/my.cnf:

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

This needs to be changed to

[mysqld]
sql_mode=''

to make sure that the settings stay in place correctly after reboots and restarts of the service.

However, 10.2 is not yet officially supported by Plesk anyway. We are running one of our hosts on 10.2 with approx. 1,000 customer domains on it, and it does work, but still hoping that 10.2 will be supported and the minor issues with some date fields will be overcome. If you have a choice and you are installing a new server, my suggestion is to downgrade to 10.1 as soon as possible to avoid future issues once the system is in production.

 

[KiamOe]

Thanks for the feedback Peter.. BUT my my.conf did not have any entry at all related to sql_mode.
Apart from my old php application.. things seem to work wind with MariaDB 10.2.. I have a dozen sites on it so not a huge thing. But if we move to another server I'll consider going down a version. I guess I was a bit fast upgrading there. Live and learn...

 

[Bitpalast]

I still suggest to add the sql_mode='', because the other string given is the default, meaning that if you don't clear the setting, the default settings apply, even if they are not expressly set in the configuration file.