Troubleshooting of MySQL errors: Difference between revisions

Jump to navigation Jump to search

Troubleshooting of MySQL errors (edit)

Revision as of 19:20, 12 May 2024

1,639 bytes removed ,  12 May 2024
→‎ERROR 1070 (42000): Specified key was too long; max key length is 767 bytes
Line 251: Line 251:
* Check the database 'MY_DATABASE_p' is exists
* Check the database 'MY_DATABASE_p' is exists
* Check there are no {{kbd | key=TAB}} character after database name if you want to connect the database 'MY_DATABASE' when you are using console.
* Check there are no {{kbd | key=TAB}} character after database name if you want to connect the database 'MY_DATABASE' when you are using console.
== ERROR 1070 (42000): Specified key was too long; max key length is 767 bytes ==
'''Envoronment''': MySQL 5.6
'''Root cause''': "By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Statement”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format." <ref>[https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html MySQL :: MySQL 5.6 Reference Manual :: 14.22 InnoDB Limits]</ref>
'''Solution'''<ref>[https://www.opencli.com/mysql/mysql-%E5%95%8F%E9%A1%8C-1071-42000-specified-key-was-too-long MySQL 問題: 1071 (42000): Specified key was too long]</ref><ref>[https://terrytongcc.com/%E5%A6%82%E4%BD%95%E8%A7%A3%E6%B1%BAmysql%E5%87%BA%E7%8F%BEerror-1118-row-size-too-large-8126%E5%95%8F%E9%A1%8C/ 如何解決MySQL出現Error 1118: row size too large (> 8126)問題 | Terry Tong – Full Stack Web Developer]</ref>:
* execute SQL query as the following:
<pre>
SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_format_max = Barracuda;
</pre>
* modify the MySQL configuration file
<pre>
innodb_file_per_table = 1
innodb-file-format = BARRACUDA
innodb-large-prefix = ON
innodb_file_format_max = BARRACUDA
</pre>
* restart the MySQL server
* execute SQL query as the following:
<pre>
ALTER TABLE `table_name` ROW_FORMAT=COMPRESSED;
</pre>


== Database and Table Operation Errors ==
== Database and Table Operation Errors ==
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/24756"

Navigation menu