Mysql query error index column size too large the maximum column size is 767 bytes

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

While importing a MySQL database, you might encounter an error “Error 1709 (HY000) at line x: Index column size too large. The maximum column size is 767 bytes”.

If you are getting this error, you are on the right page. In this article, I will explain why this error occurs and how you can resolve it.

Cause of Error

The error occurs when you create tables with ROW_FORMAT=COMPACT with utf8 encoding which makes the table row too large. You will most likely get this error while restoring a database dump from MySQL server to a MariaDB server or vice versa. This happens because in MySQL server, the default row format for InnoDB tables is “COMPACT” whereas in MariaDB version 10.2.2 (or higher), the default row format is “DYNAMIC”.

Resolution of Error

To fix the above mentioned error, you need to set the default row format for InnoDB tables to “DYNAMIC”. This can be done in multiple ways.

Using phpMyAdmin

If you’re using phpMyAdmin to manage your MySQL or MariaDB server, click on Variables tab, search for “default row format” . You will see the default value as shown in the following screenshot:

To change the value, click on Edit link under Action and type either compact or dynamic as per your requirement. After making this change, restart your MySQL or MariaDB server.

Using MySQL Configuration File

If you want to change the settings using configuration file (e.g., my.cnf), just open the file in a text editor like nano and find the following line:

innodb-default-row-format=compact

Now change this line to the following:

innodb-default-row-format=dynamic

See the following screenshot for reference:

That’s it. Don’t forget to restart your MySQL or MariaDB server after making this change. You can now import your database and the error will no longer occur.

The current state is that TYPO3 in v8 & v9 is creating new tables with explicit charset — utf8 — and you can’t change it. (there is a feature request to make it configurable, but it’s not there yet).
So these versions will be able to create new tables (or install TYPO3) without issues.

Where it’s failing is importing db dumped with regular mysqldump.
Just checked it again and its failing on executing:

/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sys_refindex` (
  `hash` varchar(32) NOT NULL DEFAULT '',
  `tablename` varchar(255) NOT NULL DEFAULT '',
  `recuid` int(11) NOT NULL DEFAULT '0',
  `field` varchar(64) NOT NULL DEFAULT '',
  `flexpointer` varchar(255) NOT NULL DEFAULT '',
  `softref_key` varchar(30) NOT NULL DEFAULT '',
  `softref_id` varchar(40) NOT NULL DEFAULT '',
  `sorting` int(11) NOT NULL DEFAULT '0',
  `deleted` smallint(6) NOT NULL DEFAULT '0',
  `workspace` int(11) NOT NULL DEFAULT '0',
  `ref_table` varchar(255) NOT NULL DEFAULT '',
  `ref_uid` int(11) NOT NULL DEFAULT '0',
  `ref_string` varchar(1024) NOT NULL DEFAULT '',
  PRIMARY KEY (`hash`),
  KEY `lookup_rec` (`tablename`,`recuid`),
  KEY `lookup_uid` (`ref_table`,`ref_uid`),
  KEY `lookup_string` (`ref_string`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

with error:

Failed to import database for typo3-core-master: Failed to run docker-compose [-f .ddev/docker-compose.yaml exec -T db bash -c cat /db/*.sql | mysql], err='exit status 1', stdout='ERROR 1709 (HY000) at line 2451: Index column size too large. The maximum column size is 767 bytes.
', stderr='' 

After I modified the dump and manually changed the row to have row format definition like:

) ENGINE=InnoDB  ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8;

It went through. As dynamic row format is a default one in Mariadb 10.2, I think this will solve the problem, but I have not tested it myself.

After thinking about it, I think its wrong that mysql reports this issue when we explicitly state the charset for a table (so utf-8 should be used, and bytes limit would not be exceeded). It looks like now MySQL uses database default charset to check keys lengths instead of charsed used in the create table statement.