Jump to content

Conversion MyISAM to InnoDB error..

Recommended Posts

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 


KEY path_segment (path_segment(255), uid)


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. 

Link to comment
Share on other sites

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 by Makoto
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...