Sunday, April 15, 2018

TYPO3 MySQL database import fails with "Index column size too large. The maximum column size is 767 bytes"

I recently migrated a TYPO3 7.6 Website to TYPO3 8.7 and while importing the migrated TYPO3 database on the production server, the import failed with the following MySQL error:

ERROR 1709 (HY000) at line 2060: Index column size too large. The maximum column size is 767 bytes.

The error occurred for the import of the TYPO3 table sys_refindex. After some research and local debugging I found out, that I locally was using MySQL 5.7 and the production server was using MySQL 5.6, but settings in regard to innodb_large_prefix and innodb_file_format were equal.

In order to import the dump from my MySQL 5.7 server to the production MySQL 5.6 Server, I executed the following SQL query before creating the MySQL dump:  

ALTER TABLE sys_refindex ROW_FORMAT=DYNAMIC;

After setting the ROW_FORMAT to DYNAMIC, the database dump from MySQL 5.7 could finally be imported without errors on the MySQL 5.6 production server.