Change Strict SQL Mode

[NEWCHILD]

Hello,

I'm running:

OS ‪CentOS Linux 7.3.1611 (Core)‬
Product Plesk Onyx
Version 17.5.3 Update #20, last updated on Aug 29, 2017 03:47 AM.

Please how do i disable strict sql mode.

Hope to hear from you soon.

Best Regards
Tolu

 

[UFHH01]

Hi NEWCHILD,

depending to your UNIQUE

sql_mode=XXXXXXX,XXXXXXX,XXXXXXX

... settings at your "my.cnf" - configuration file, you are always able to adjust this with for example:

sql_mode=""

Pls. don't forget to restart your MySQL - service after your changes.

 

[NEWCHILD]

Hello UFHH01,

Please how do i locate
settings at your "my.cnf" - configuration file, you are always able to adjust this with for example:
etc/my.cnf doesn't have anything llike that. It's running Maria DB 10.2.8

Please advise.
Regards
Tolu

 

[UFHH01]

Hi NEWCHILD,

if the output of the command

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "SELECT @@sql_mode;"

shows possible "strict mode" settings, you should post your configuration file, which might include additional configuration files.

In addition, pls. be aware, that on some systems, there are as well additional "my.cnf" files at for example "/root". Pls. locate such files with for example a "locate" - search:

locate my.cnf

( the usage of the command "locate", requires the "mlocate" - package to be installed on your server and requires "mlocate - db - updates" with the command "updatedb" on a regulary basis! )

 

[NEWCHILD]

Hi NEWCHILD,

if the output of the command

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "SELECT @@sql_mode;"

shows possible "strict mode" settings, you should post your configuration file, which might include additional configuration files.

In addition, pls. be aware, that on some systems, there are as well additional "my.cnf" files at for example "/root". Pls. locate such files with for example a "locate" - search:

locate my.cnf

( the usage of the command "locate", requires the "mlocate" - package installed on your server and requires "mlocate - db - updates" with the command "updatedb" on a regulary basis! )

This is what i got from running the command.

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_S UBSTITUTION |
+------------------------------------------------------------------------------- ------------+



[root@vmi118152 ~]# locate my.cnf
-bash: locate: command not found

 

[UFHH01]

Hi NEWCHILD,

sorry, but we can't move any further without configuration files and I am just able to repeat myself:

[root@vmi118152 ~]# locate my.cnf
-bash: locate: command not found

As already stated, you NEED the "mlocate" - package to be installed on your server, if you desire to use the "locate" - command.

( the usage of the command "locate", requires the "mlocate" - package to be installed on your server and requires "mlocate - db - updates" with the command "updatedb" on a regulary basis! )

Pls. install the package and run the "updatedb" - command afterwards, before you try out

locate my.cnf

again.


If you don't post your configuration files as suggested

you should post your configuration file, which might include additional configuration files.

... people willing to help you have no chance to investigate further and additional suggestions can't be done.


The output of

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_S UBSTITUTION |
+------------------------------------------------------------------------------- ------------+

... shows clear, that you have some "sql_mode" - settings, which leads again to my first post: => #2

 

[NEWCHILD]

Hi NEWCHILD,

sorry, but we can't move any further without configuration files and I am just able to repeat myself:


As already stated, you NEED the "mlocate" - package to be installed on your server, if you desire to use the "locate" - command.
Pls. install the package and run the "updatedb" - command afterwards, before you try out

locate my.cnf

again.


If you don't post your configuration files as suggested

... people willing to help you have no chance to investigate further and additional suggestions can't be done.


The output of

... shows clear, that you have some "sql_mode" - settings, which leads again to my first post: => #2

This is what i got:


[root@vmi118152 ~]# locate 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


on checking /etc/my.cnf i have:

[mysqld]
bind-address = ::ffff:127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in Systemd - FedoraProject

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d


Please advise.

Regards
Tolu

 

[UFHH01]

Hi NEWCHILD,

unfortunately, you don't provide the content of the additional configuration files, even that you must have seen the include:

!includedir /etc/my.cnf.d

This situation still leaves us without needed informations, where "sql_mode" has been configured on your server. So pls. post the output of the example "find" commands:

find /etc -type f -name "*.cnf" -exec grep --color -Hni "sql_mode" {} \;

find /etc/my.cnf.d -type f -name "*.cnf" -exec grep --color -Hni "sql_mode" {} \;

find /etc/systemd/system/mariadb.service.d -type f -name "*.conf" -exec grep --color -Hni "sql_mode" {} \;

... and adjust the "find" - command examples with other "name" - definitions, if you can't find the setting already with the provided examples. ​

Afterwards, you should follow the suggestion from => #2 , to modify the found setting to your desire.

 

[NEWCHILD]

Hi NEWCHILD,

unfortunately, you don't provide the content of the additional configuration files, even that you must have seen the include:

!includedir /etc/my.cnf.d

This situation still leaves us without needed informations, where "sql_mode" has been configured on your server. So pls. post the output of the example "find" commands:

find /etc -type f -name "*.cnf" -exec grep --color -Hni "sql_mode" {} \;

find /etc/my.cnf.d -type f -name "*.cnf" -exec grep --color -Hni "sql_mode" {} \;

find /etc/systemd/system/mariadb.service.d -type f -name "*.conf" -exec grep --color -Hni "sql_mode" {} \;

... and adjust the "find" - command examples with other "name" - definitions, if you can't find the setting already with the provided examples. ​

Afterwards, you should follow the suggestion from => #2 , to modify the found setting to your desire.

In my.cnf.d i have

clients.cnf, mysql-clients.cnf & server.cnf.

Their Contents are:

clients.cnf:

#
# These two groups are read by the client library
# Use it for options that affect all clients, but not the server
#


[client]

# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]


mysql-clients.cnf

#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#

[mysql]

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]

[mysqlimport]

[mysqlshow]

[mysqlslap]

Server.cnf:

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]


Hope to hear from you soon.
Regards
Tolu

 

[UFHH01]

Hi NEWCHILD,

please be a little creative and independent!

I already suggested some "find" - command - examples above, which you are able to change/modify in case that the location(s) or definition(s) don't fit to find the place, where your "sql_mode" - settings have been made.


If you are still not able to find the setting on your server, pls. consider to use for example the "Professional Administration Service", provided by Plesk:

=> Plesk Admin Services​

 

[Orama]

I know this thread is old BUT, Where to change sql_mode in DOCKER in PLESK ?