使用MySQL Workbench克隆数据库时导入失败，报错Error 1071

Oh man, I’ve run into this exact issue before when migrating databases between servers—super frustrating when the export works fine but the import blows up with that 1071 error! Let me walk you through what’s going on and how to fix it.

Why This Happens #

Your production database is running smoothly because it’s likely configured to support longer indexes, but your target server isn’t. Here’s the breakdown:

• By default, InnoDB’s older Antelope file format caps index entries at 767 bytes.
• If your target server uses utf8mb4 (the modern UTF-8 that supports emojis/Full Unicode, 4 bytes per character), a varchar(512) index would need 512×4=2048 bytes—way over the limit. Even a varchar(255) index hits 255×4=1020 bytes, which exceeds 767.
• Your production DB is probably using the Barracuda file format with innodb_large_prefix enabled, which lets indexes go up to 3072 bytes for tables using DYNAMIC or COMPRESSED row formats.

Fix 1: Adjust Target Server’s InnoDB Settings (Recommended if you have access) #

This matches your production setup and avoids modifying your schema:

1. Connect to your target server via MySQL Workbench or CLI (you’ll need SUPER privileges) and run these commands:

   SET GLOBAL innodb_file_format = Barracuda;
   SET GLOBAL innodb_large_prefix = ON;
   SET GLOBAL innodb_file_per_table = ON;
   

2. If your exported SQL doesn’t specify row format, edit the file to add ROW_FORMAT=DYNAMIC (or COMPRESSED) to every CREATE TABLE statement:

   CREATE TABLE your_table (
     id INT PRIMARY KEY,
     long_column VARCHAR(512),
     KEY idx_long_column (long_column)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
   

3. To make these settings persist after server restarts, add them to your my.cnf (or my.ini on Windows):

   innodb_file_format = Barracuda
   innodb_large_prefix = ON
   innodb_file_per_table = ON
   

4. Now re-run the Data Import—it should work without index errors.

Fix 2: Modify the Exported SQL File (If you can’t tweak target server config) #

If you don’t have access to change the target server’s settings, shorten the indexes manually:

1. Open your exported SQL file in a text editor (VS Code, Sublime, etc.)
2. Find all index definitions for your varchar(512) and varchar(255) columns (look for CREATE INDEX or KEY clauses in CREATE TABLE statements)
3. For utf8mb4 character set:
   • Trim the varchar(512) index to 191 characters (191×4=764 bytes, under the 767 limit)
   • Trim the varchar(255) index to 191 characters too (255×4=1020 bytes is over)
     Example:

   -- Before
   CREATE INDEX idx_long_column ON your_table(long_column);
   -- After
   CREATE INDEX idx_long_column ON your_table(long_column(191));
   

4. If your production DB uses the older utf8 (now called utf8mb3, 3 bytes per character), you can keep the varchar(255) index as-is (255×3=765 bytes) but still need to trim the varchar(512) index to 255 characters.

Fix 3: Verify Character Set Consistency #

Double-check that your target database uses the same character set as production. Run this query on both servers:

SHOW VARIABLES LIKE 'character_set_database';

If production uses utf8mb3 but target uses utf8mb4, that’s why the index size calculation differs. You can either change the target DB’s character set to match, or adjust indexes as in Fix 2.

One last tip: Always test these changes in a staging environment first to make sure your queries still work correctly with shorter indexes!

内容的提问来源于stack exchange，提问作者anon