How to Disable Strict mode in Mysql

[Freecast]

How to Disable Strict mode in Mysql in Direct Admin ?

 

[smtalk]

You can just set:

sql_mode=

In /etc/my.cnf.d/server.cnf under [mysqld] section.

 

[AndrewN]

Check /usr/local/mysql/my.cnf for the sql_mode setting and comment it in that file

 

[mbsmt]

MySQL configuration file in my server was in /etc/my.cnf, so you might need to put sql_mode= in that file.

 

[Richard G]

I think after 3 years, they know that.

 

[mbsmt]

? I knew. I came back to this post after years, and I saw there is not such path in the comments. So I decided to add it.

I think after 3 years, they know that.

 

[Richard G]

So I decided to add it.

In fact, the correct answer for these days is already given in post #2.
The /etc/my.cnf is still present but normally the config directory is used.
Which is probably the reason nobody else answered.

 

[prithwirajsinha99]

You can just set:

sql_mode=

In /etc/my.cnf.d/server.cnf under [mysqld] section.

I am getting this error. Do you know the fix?
https://prnt.sc/132e15h
1_60a3558c421b75529c12ac26.log

 

[Richard G]

The backup may not be complete it says. Please open a seperate topic for your own issues.

 

[dataonline.vn]

The backup may not be complete it says. Please open a seperate topic for your own issues.

I also got the same error. Cannot backup (dump) DB when setting sqlmode = in etc/my.cnf

 

[Zhenyapan]

@hostingviet try add different or empty mode field to mysqldump section of my.cnf

 

[Stije]

Don't use space. Just add in the file: /etc/my.cnf
MariaDB older than 10.6 or MySQL 5.7

Below: [mysqld]

sql_mode=NO_ENGINE_SUBSTITUTION

Mariadb 10.6 or higher

sql_mode=

Mysql 5.7 or higher needs quotes:

sql-mode=""

Restart mysql/mariadb

Or login via CLI
To check mode:

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;

And set them:

SET GLOBAL sql_mode = '';

SET sql_mode = '';

 

[dataonline.vn]

I fixed it with this configuration in my.cnf. Can both backup the database (dump) and fix the "Disable Strict" error. And it has optimized settings, you can refer to it under "key_buffer_size"

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include *.cnf from the config directory
#
#!includedir /etc/my.cnf.d

[mysqld]
max_allowed_packet=64M
local-infile=0
innodb_file_per_table
#skip-grant-tables
innodb_default_row_format=dynamic

sql_mode =

key_buffer_size = 384M #400M #384M

max_allowed_packet = 50M #2M #1M

sort_buffer_size = 3M #2M #8M #2M #8M #2M

read_buffer_size = 3M #2M #8M #2M #8M #2M

read_rnd_buffer_size = 8M #16M #8M #16M #8M

myisam_sort_buffer_size = 96M #64M #96M #64M #128M #64M

thread_cache_size = 8

query_cache_size = 600M #128M #500M #400M #300M #150M #256M #128M #32M #128M #50M #256M #50M #32M

 

[Zhenmue]

I fixed it with this configuration in my.cnf. Can both backup the database (dump) and fix the "Disable Strict" error. And it has optimized settings, you can refer to it under "key_buffer_size"

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include *.cnf from the config directory
#
#!includedir /etc/my.cnf.d

[mysqld]
max_allowed_packet=64M
local-infile=0
innodb_file_per_table
#skip-grant-tables
innodb_default_row_format=dynamic

sql_mode =

key_buffer_size = 384M #400M #384M

max_allowed_packet = 50M #2M #1M

sort_buffer_size = 3M #2M #8M #2M #8M #2M

read_buffer_size = 3M #2M #8M #2M #8M #2M

read_rnd_buffer_size = 8M #16M #8M #16M #8M

myisam_sort_buffer_size = 96M #64M #96M #64M #128M #64M

thread_cache_size = 8

query_cache_size = 600M #128M #500M #400M #300M #150M #256M #128M #32M #128M #50M #256M #50M #32M

that's a very odd configuration, plus, optimized values for you, are not the same for me or anyone else.

it depends on servers specs, and mysql load of your users.

 

[dataonline.vn]

that's a very odd configuration, plus, optimized values for you, are not the same for me or anyone else.

it depends on servers specs, and mysql load of your users.

Yes, I get it. I also explain that You do not need configuration from this line down
key_buffer_size = 384M #400M #384M...


Note that this configuration allows backups. I encountered an error with the SQL backup issue when setting additional values
sql_mode =