Shulou(Shulou.com)05/31 Report--

Mysq5.7.28 configuration innodb_page_size error caused by ERROR 1071 (42000) what to do, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

ERROR 1071 (42000): Specified key was too long; max key length is 1536 bytes error occurred when helping customers deploy a set of mysql and import data

Environment: database version: mysql5.7.28

Operating system: centos6.8

CREATE TABLE `QRTZ_BLOB_ varchar (CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL),-> `TRIGGER_ NAME` varchar,-> `TRIGGER_ GROUP` varchar (200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,-> `BLOB_ DATA` blob NULL,-> PRIMARY KEY (`SCHED_ NAME`, `TRIGGER_ NAME`, `TRIGGER_ GROUP`) USING BTREE -> CONSTRAINT `NAME`, `TRIGGER_ NAME`, `TRIGGER_ GROUP`) REFERENCES `QRTZ_ qrtz_blob_triggers_ibfk_ FOREIGN KEY (`SCHED_ NAME`, `TRIGGER_ NAME`, `TRIGGER_ GROUP`) ON DELETE RESTRICT ON UPDATE RESTRICT->) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = 'InnoDB free: 504832 kB (`NAME``TRIGGER_ NAME`` TRIGGE' ROW_FORMAT = Dynamic;ERROR 1071 (42000): Specified key was too long; max key length is 1536 bytes

4. This error is caused by the system variable innodb_large_prefix (enabled by default. Note that the experimental version is MySQL 5.6.41, which is turned off by default.

MySQL 5.7 is on by default, the index key prefix is limited to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If disabled

Innodb_large_prefix, the index key prefix is limited to 767 bytes for any row format table.

Nnodb_large_prefix will be deleted and deprecated in future versions. Innodb_large_prefix was introduced in MySQL 5.5to disable large prefix indexes

To be compatible with earlier versions of InnoDB that do not support large index key prefixes.

For InnoDB tables that use REDUNDANT or COMPACT row format, the index key prefix length is limited to 767 bytes. For example, you might use the TEXT or VARCHAR column to

This limit is achieved with a column prefix index of more than 255 characters, assuming the utf8mb3 character set, and each character contains a maximum of 3 bytes.

Trying to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in the replication configuration, avoid enabling on the primary server

Enableinnodb_large_prefix if it cannot be enabled on the slave server.

The restrictions that apply to index key prefixes also apply to full columns of index keys.

Note: the above is 767 bytes, not characters, specific to the number of characters, which is related to the character set. GBK is double-byte and UTF-8 is three-byte

5. The official solution is:

1: system variable innodb_large_prefix is ON 2: system variable innodb_file_format is Barracuda 3: ROW_FORMAT is DYNAMIC or COMPRESSED

6. After testing, changing the above three parameters still can not solve the problem of error reporting, but the non-profile deployment mysql that you try to install by default can be created normally, which is determined by

Unable to create the table caused by the configuration file. After testing, it is found that the configuration size of the innodb_page_size parameter in the configuration file is 8192, while the default configuration size

Is 16384, adjust this parameter to 16384 problem solved.

7. The byte size required to create the foreign key index of the above statement is (120 / 200 / 200) * 3 / 1560, but it is no problem why the page page is changed to 16k. No intuitive evidence has been found, and it is not known yet (it is speculated that it should be the relationship between page size and index row length. 16k pages correspond to 3072 pages and 8k pages correspond to 1536).

After reading the above, do you know what to do with ERROR 1071 (42000) caused by mysq5.7.28 configuration innodb_page_size errors? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.