SQL errors during site migration

During database migration alot of SQL queries are run, temporary tables are created, data is moved etc.
Sometimes errors happen during these queries and often it is about incompatibility between the table structure/data coming from the source database and going to the target database.

Some of these errors start with something like "Migration aborted, due to a SQL query failing. See log for specific information about the query that failed. The specific error from SQL server was: X". In the below section we are going to focus on the specific error messages.

This is some of the common errors we see and their solution:

Specified key was too long; max key length is X bytes.

Problem here is that one of the keys, like primary key or indexes are longer (in bytes) that the database engine allows.
The limit in database engine also differ in different version of MySQL/MariaDB, which does not help.

A simple example:
We have a table with a with this definition:

CREATE TABLE `wp_options` ( `option_id` varchar(255) unsigned NOT NULL AUTO_INCREMENT COMMENT ''test'', `option_name` varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '''' COMMENT ''test2'', `option_value` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL COMMENT ''test3'', `autoload` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ''yes'' COMMENT ''test4'', PRIMARY KEY (`option_id`), UNIQUE KEY `option_name` (`option_name`) ) ENGINE=MyISAM AUTO_INCREMENT=1288 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

So our primary key here is "option_id" column, which is a varchar type with max length of 255. Because we are using the utf8mb4 charset, each char needs 4 bytes, so that is 255 x 4 = 1020. Our "key length" for this field is then 1020 bytes.

If we check the other key "option_name", it has a length of 191. With 4 bytes, it gives a key length of 764 bytes.
So if our max key length for the SQL server is 767 bytes, we only have a problem with the column "option_id", as it is larger than 767 bytes. "option_name" on the other hand, stays inside the limit of 767 bytes.

Solution:

So we have a few ways to fix this:

• Decrease the "option_id" varchar max length, so the key will be less than 767 bytes in length.
• Change the table engine type to InnoDB, which supports longer keys
• Modify the SQL server to accept longer keys

Unknown collation: 'utf8mb4_unicode_520_ci'

Problem here is that one of the database tables have a definition that is not supported on the target database engine. For every major release, MySQL/MariaDB comes up with some new charsets, which is not supported in older versions.

So if a table is created on the latest version of MySQL with one of the new charsets, that table would give errors when trying to migrate it to a site using a older version of MySQL.

Solution:

You need to change the charset of the problematic tables to one that is supported on both version of MySQL.

Error in database migration for table X - It is not transferred

During the final stage of migration, WP Synchro checks that everything is good, before actually overwriting the data.
For database migration it checks that the tables are existing as temporary tables to on the target and that the temporary tables contains the amount of data we expect. If that is not the case, we get this error.

Normally we see this error if the data from the source is not compatible on the target database.
Such as using data types that are not understood on the target database.

Solution:

There is no clear solution to this. It depends on the specific error. You can contact support to get help with your specific issue.
It is always possible to exclude the table as a solution, if the table is not important.