mysql5.6 error

最新推荐文章于 2025-07-24 22:28:10 发布

原创最新推荐文章于 2025-07-24 22:28:10 发布 · 429 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

39 篇文章

订阅专栏

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