mysql 5.6 sql mode_升级到MySQL 5.6后,将sql_mode设置为“blank”

将MySQL从5.5升级到5.6后,我们的一些应用面临问题,需要将sql_mode设置为空白才能解决此问题.我在my.cnf中添加了sql_mode =”,但对mysql设置没有影响.

如何保持sql_mode为空？

解决方法:

序幕

有人在我的组织中问过同样的事情,因为每个人都在使用MySQL 5.5.所有数据库服务器在过去8个月内升级到MySQL 5.6.一些客户端应用程序也受到sql_mode更改的影响.

根本原因

我刚刚发现为什么你做的不起作用,解决方法非常简单.

> MySQL 5.6.5中的空白字符串并返回

> NO_ENGINE_SUBSTITUTION,5.6.6中的STRICT_TRANS_TABLES和GA

好的,我希望你坐下来.

这是Oracle在MySQL 5.6中实现sql_mode的懒惰方式：还有一个my.cnf文件.

如果你跑

cat /usr/my.cnf

你会看到以下内容

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# socket = .....

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

见第28行？

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

解

第01步：注释/usr/my.cnf第28行

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

步骤02：手动设置sql_mode

mysql> SET GLOBAL sql_mode = '';

而已 ！！！

结语

> STEP 01阻止mysqld重新启动以更改sql_mode.

> STEP 02现在设置sql_mode,因此不需要立即重启mysqld

试试看 ！！！

标签：mysql-5-6,mysql,upgrade,mysql-5-5

来源： https://codeday.me/bug/20190805/1593756.html