Figured it out

This is a place where people can share how they have solved challenging technology problems. If you have been wrestling with an issue and feel compelled to share your solution, feel free to post here. Go ahead and post at the top so the posts go from most to least recent.

Database Import Problem

April 18,2014

Here a tricky error I encountered while trying to import a database.

The context:

I was trying to import a new database downloaded from Acquia Cloud to my local Drupal setup.

The platform I was using:

AMPPS local setup with Drupal 7

The command I used:

drush sqlc < db.sql

The error:

#1071 - Specified key was too long; max key length is 767 bytes

The cause:

The site was using the utf8mb4 character set. Upon encountering VARCHAR(255), the process stopped because the default limit for a key was 767 bytes. Typically the system is used to utf8, which are 3-byte character sets. However, this particular site was using utf8mb4, which are 4-byte character sets. In a utf8 situation, a 255 VARCHAR would indeed be 255 x 3, or 765 bytes. However, with utf8mb4, the VARCHAR needs 255 x 4, or 1020 bytes. It was my conclusion that the utf8mb4 VARCHAR exceeded the conventional 767-byte limit and caused the error.

The solution:

While the cause of this became obvious, the solution was somewhat cryptic and only came after a lot of searching. As it turned out, I ended up going into "Variables" in phpmyadmin and changing innodb file format to from Antelope to Barracuda. I also set innodb large prefix to ON. This seems to have increased the default key limit to a number above 767 bytes.