SQL errors during site migration

During database migration, a lot of SQL queries are run, temporary tables are created, and data is moved.
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.

These are some of the common errors we see and their solutions:

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

The problem here is that one of the keys, such as a primary key or an index, is longer in bytes than the database engine allows.
The limit in the database engine also differs between different versions of MySQL and MariaDB, which does not help.

A simple example:
We have a table 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'

The problem here is that one of the database tables has 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 may cause errors when you try to migrate it to a site using an older version of MySQL.

Solution:

You need to change the charset of the problematic tables to one that is supported on both versions 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 exist as temporary tables on the target and that the temporary tables contain 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.