mysql5.6 error

1: [Err] 1071 - Specified key was too long; max key length is 767 bytes

2:[Err] 1709 - Index column size too large. The maximum column size is 767 bytes.

3.[Err] 1215 - Cannot add foreign key constraint

检查

SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'innodb_large_prefix';  
SHOW VARIABLES LIKE 'innodb_file_format';

-- 修改最大索引长度限制

SET GLOBAL innodb_large_prefix=ON;
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;

2:设置


mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_large_prefix';  
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_format';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| innodb_file_format | Antelope |
+--------------------+----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_format_max';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_file_format_max | Antelope |
+------------------------+----------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_large_prefix=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'innodb_large_prefix';  
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_format_max';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_file_format_max | Antelope |
+------------------------+----------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_file_per_table=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_format_max';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_file_format_max | Antelope |
+------------------------+----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> 

语句也需要修改

ROW_FORMAT=DYNAMIC;

CREATE TABLE `qrtz_blob_triggers` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `BLOB_DATA` blob,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  CONSTRAINT `qrtz_blob_triggers_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `qrtz_triggers` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMI

 

发布了233 篇原创文章 · 获赞 148 · 访问量 162万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览