mysql的多个问题

来源：互联网发布：mac应用程序文件夹编辑：程序博客网时间：2024/05/22 13:09

mysql5.5？1#1071 - Specified key was too long; max key length is 767 bytes

解决方法

在my.cnf中

innodb_large_prefix = 1

万恶的官网还要球

innodb_file_format=barracuda andinnodb_file_per_table=true.

参考：

http://bugs.mysql.com/bug.php?id=4541

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_file_format

mysql> CREATE TABLE SAKAI_SESSION ( SESSION_ID VARCHAR (36), SESSION_SERVER VARCHAR (64), SESSION_USER VARCHAR (99), SESSION_IP VARCHAR (128), SESSION_HOSTNAME VARCHAR (255), SESSION_USER_AGENT VARCHAR (255), SESSION_START TIMESTAMP, SESSION_END TIMESTAMP, SESSION_ACTIVE tinyint(1) );
ERROR 1067 (42000): Invalid default value for 'SESSION_END'

原始代码

kernel/kernel-impl/src/main/sql/mysql/sakai_session.sql

比较了一下旧代码也这样。说明迁移mysql到5.7惹祸了。

mysql> SELECT @@sql_mode
-> ;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

做了如下修正，

ysql> SELECT @@sql_mode
-> ;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修正后

mysql> set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode
-> ;
+--------------------------------------------------------------------------------------------------------------+
| @@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 (0.00 sec)

将连个NO_ZERO_IN_DATE, NO_ZERO_DATE都去掉了。

0 0