evcom Posted November 24, 2020 Posted November 24, 2020 I have converted some tables from MyISAM to InnoDB, but some table conversions fail. Error: #1709 - Index column size too large. The maximum column size is 767 bytes. Contacted my hosting provider. Because I am on a shared hosting account (MySQL 5.6) a change of MySQL config is not possible. One recommended option was to reduce the index per column. E.g. with From: KEY path_segment (path_segment(255), uid) to: KEY path_segment (path_segment(185), uid) Anyone knows, what the exact implications of this would be? Does someone has experience with it? The second alternative option was to upgrade the hosting plan using MariaDB which has the needed configuration in place.
Makoto Posted November 24, 2020 Posted November 24, 2020 (edited) Generally, the recommendation would be to enable innodb_large_prefix in the MySQL server configuration, but if you're not able to do that you'll have to adjust the index sizes to accommodate. The problem is there are going to be several indexes that need updating, so doing them one by one would take a very long time. I believe I had a script that automated this once before but I'll have to try and dig it up again. Edited November 24, 2020 by Makoto
evcom Posted November 24, 2020 Author Posted November 24, 2020 Thanks Makoto, given the circumstances, I will probably stick with MyISAM for the time being. If the community grows over time, I will then consider moving to another server where I can either configure the setting myself or onto a shared hosting which has the option"innodb_large_prefix=1" enabled. Makoto 1
Recommended Posts