项目中升级mysql遇到的若干问题
项目中之前用的mysql版本为5.7，这次改版后部署时，心心念念想升级一下版本，明知道会面临各种问题，本着不作不死的精神，还是义无反顾的开始了这次折腾之旅。把遇到的问题记录一下
问题一
导入数据库时不成功，提示：
Specified key was too long; max key length is 767 bytes
在5.6中启用innodb_large_prefix
查看innodb_large_prefix是否开启：
mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | OFF |
+---------------------+-------+
1 row in set (0.03 sec)
开启innodb_large_prefix：
mysql> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)
再次查看：
mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | ON |
+---------------------+-------+
1 row in set (0.04 sec)
问题二
Index column size too large. The maximum column size is 767 bytes.
INNODB 引擎，UTF-8，主键字符串默认最大 767，需要修改
mysql> set global innodb_file_format = BARRACUDA;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.04 sec)
在5.7中默认为on，不用修改。
问题三
[Err] [Dtf] 1067 - Invalid default value for 'add_time'
[Err] [Dtf] Finished - Unsuccessfully
报错原因意思是说：mysql5.7版本中有了一个STRICT mode（严格模式），而在此模式下默认是不允许设置日期时间的值为全0值的，所以想要解决这个问题，就需要修改sql_mode的值。
先查看一下：
show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
问题四
tomcat启动时，报错，直接挂了
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.COURSE_NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
提示最后一句很清楚：
this is incompatible with sql_mode=only_full_group_by
就是与sql_mode = only_full_group_by不兼容
由于开启了ONLY_FULL_GROUP_BY的设置，如果select 的字段不在 group by 中，并且select的字段未使用聚合函数（SUM,AVG,MAX,MIN等）的话，那么这条sql查询是被mysql认为非法的，会报错误…
解决的方法有很多，我选了一种永久修改的sql_mode
编辑my.cnf文件，没有该文件就创建：
添加sql-mode，去掉ONLY_FULL_GROUP_BY
[mysqld]
lower_case_table_names = 1
sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------------------------------------------------------+
1 row in set
mysql>
先到这儿吧~