CODEWITHSUNDEEP
innodbmysqlrds
Naijaba
Naijaba

Reputation: 1029

Amazon RDS to Custom MySQL Server InnoDB Key Size

I'd like to export an Amazon MySQL RDS instance to my own server running MySQL. I successfully dumped the database and recreated the users on the new database, but when I go to import the dumpfile, I get:

ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 767 bytes

Some Googling revealed that InnoDB has a max key size of 767. It turns out that we were using the following options in RDS:

innodb_large_prefix=on
innodb_file_format=barracuda
innodb_file_per_table=true
log_bin_trust_function_creators=1

I added these options to my.cnf, but I got the same error message. I then read that innodb_large_prefix only works on tables with ROW_TYPE=DYNAMIC. It turned out that we were using dynamic rows on RDS, but that these rows were not being created as DYNAMIC in the dumpfile. I then found this StackOverflow post that added the ROW_TYPE=DYNAMIC option to the dumpfile: Force row_format on mysqldump

And yet, still I get the same error message. Ideas?

Upvotes: 4

Views: 2757

Answers (1)

akuzminsky
akuzminsky

Reputation: 2257

I believe this is encoding issue. If latin1 was used on RDS, but UTF-8 in your environment then indexed VARCHAR(256) is the problem.

Because in UTF-8 VARCHAR( 256) becomes VARCHAR( 768 ) internally.

Upvotes: 1

Related Questions