mysql数据库迁移到新的实例可能遇到的坑-mysql5.7

一、 mysql数据库导入sql文件时提示“Error Code: 1153 - Got a packet bigger than 'max_allowed_packet' bytes”

1.查看目前数据库配置

show VARIABLES like '%max_allowed_packet%';

显示如下结果

+--------------------+---------+

| Variable_name      | Value   |

+--------------------+---------+

| max_allowed_packet | 1048576 |

+--------------------+---------+

说明目前的配置是：1048576/1024/1024 = 1M

 

二、mysql 忽略表名大小写

因为linux下mysql默认是要区分表名大小写的。mysql是否区分大小写设置是由参数lower_case_table_names决定的

lower_case_table_names = 1 
不区分大小写（即对表名大小写不敏感）。这样设置后，表名在硬盘上以小写保存，MySQL将所有表名转换为小写存储和查找表上。该行为也适合数据库名和表的别名。
也就是说，mysql设置为不分区大小写后，创建库或表时，不管创建时使用大写字母，创建成功后，都是强制以小写保存！

修改配置文件：

[root@test-huanqiu ~]# cat /etc/my.cnf                                          //添加下面一行设置
.....
[mysqld]
lower_case_table_names=1

 

三、mysql5.7.x:this is incompatible with DISTINCT

DISTINCT关键字经常在MySQL中使用，在mysql5.7以前的版本中一般没有什么问题，但是在5.7以后的版本中会遇到这样的错误

Caused by: java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column ‘game.giftbag0_.create_date’ which is not in SELECT list; this is incompatible with DISTINCT

 

错误提示DISTINCT不兼容，要么更改SQL，但是对于开发者来讲，sql运行一直都是正常的，可能是mysql 版本升级导致的安全问题，5.7.x安全性提升了很多，解决办法可以考虑修改MySQL配置文件,找到对应的my.cnf或者my.ini

 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

1

添加该行就ok!

 

还有一种错也是不兼容的问题导致的

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘nctest.pivot.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

这种问题就是sql_mode=only_full_group_by导致的，去掉only_full_group_by就解决了