Unable to migrate a subscription which has databases on different remote servers

Symptoms

Unable to migrate a subscription, e.g. example.com , which has got databases on different remote servers, e.g. mysqlserver1.com , mysqlserver2.com and mysqlserver3.com.

After the migration, the domain example.com on the destination server should have the same databases on the same database servers.

The migration via Plesk > Tools & Settings > Migration & Transfer Manager migrates only databases which belong to one remote database server and fails with the following error messages for other databases:

PLESK_ERROR: Failed to copy content of database 'database_2_from_mysqlserver2.com'
Migration tools tried to perform operation in 3 attempts: Command execution failed on the source server 'source' (203.0.113.2) with non-zero exit code.
command: MYSQL_PWD="$(cat)" mysqldump --no-defaults -h mysqlserver2.com -P 3306 -uroot --quick --quote-names --add-drop-table --default-character-set=utf8 --set-charset --routines --events database_2_from_mysqlserver2.com > /root/plesk_migrator/plesk_migrator-17l90rj176g9on332v2nu1w4957xibfo/db-dumps/database_2_from_mysqlserver2.com.sql
exit code: 2
stdout:
stderr: mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)PLESK_ERROR: Unable to create database user 'user_of_database_2_from_mysqlserver2.com' in subscription 'example.com': Command execution failed on the local server with non-zero exit code.
command: /usr/local/psa/bin/database --create-dbuser user_of_database_2_from_mysqlserver2.com -passwd '***hidden***' -domain example.com -database database_2_from_mysqlserver2.com -type mysql -server mysqlserver2.com:3306 -ignore-nonexistent-options
exit code: 1
stdout:
stderr: Unable to add database user: The user user_of_database_2_from_mysqlserver2.com already exists.

Resync and solution from this article does not fix the issue

As a result, the whole content, except failed databases, are migrated successfully.

Cause

Currently, such scenario is not supported by Plesk Migrator.

Resolution

As a workaround, perform the following actions:

1. Connect to the source server via SSH;

2. Get the list subscriptions which have got databases on more than one remote database server ("Count" value means an amount of different remote database servers used by the subscription):

   # plesk db "select subc.name as 'Subscription_name', ccc.count as 'Count' from (select dom_id as ddd,COUNT(DISTINCT db_server_id) AS count from data_bases group by dom_id order by count desc) ccc, domains subc where subc.id=ccc.ddd and ccc.count!=1;"
   
   +-------------------+------+
   | Subscription_name | Count|
   +-------------------+------+
   |    example.com    |   2  |
   +-------------------+------+

3. Go to Plesk > Tools & Settings > Migration & Transfer Manager > 203.0.113.2 > List of subscriptions;

4. Choose the subscriptions which should be migrated, e.g. example.com;

5. Unmark the checkbox Migrate database data (tables, rows, etc) :

   

   Note: even though the checkbox Migrate database data (tables, rows, etc) was disabled, the "skeletons" of databases and users will be created. Also, corresponding records in psa database will be also added on the destination server

6. Start the migration via Migrate button;

7. Wait until the migration will be completed;

8. Log in via SSH to the destination server;

9. Create a backup of psa database on both servers:

   # plesk db dump > /root/psa_backup.sql

10. Perform the following actions for each migrated subscription:

    • Check id values of remote database servers on both servers:

      # plesk db "select id, host from DatabaseServers;"
      
      [SOURCE]
      +----+-----------------------+
      | id |          host         |
      +----+-----------------------+
      | 1  |        localhost      |
      | 2  |    mysqlserver1.com   |
      | 3  |    mysqlserver2.com   |
      | 4  |    mysqlserver3.com   |
      +----+-----------------------+
      
      [DEST]
      +----+-----------------------+
      | id |           host        |
      +----+-----------------------+
      | 1  |       localhost       |
      | 2  |    mysqlserver1.com   |
      | 3  |    mysqlserver2.com   |
      | 4  |    mysqlserver3.com   |
      +----+-----------------------+

      Note: in this example, the id of remote database servers are coincide

    • Get the domain id on both servers:

      # plesk db "select id from domains where name='example.com';"
      
      [SOURCE]
      +----+
      | id |
      +----+
      | 5  |
      +----+
      
      [DEST]
      +----+
      | id |
      +----+
      | 10 |
      +----+

    • Get the information from data_bases and db_users tables on both servers for the separate subscription using dom_id gotten from the previous step:

      # [SOURCE SERVER]
      
      # plesk db "select dom_id, name, db_server_id, default_user_id from data_bases where dom_id=17;"
      
      +----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
      | id | dom_id |               name               |                      login               | db_server_id | default_user_id |
      +----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
      | 15 | 5 | database_2_from_mysqlserver2.com | user_of_database_2_from_mysqlserver2.com | 3 | 115 |
      +----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
      
      # plesk db "select id, db_id, dom_id, db_server_id from db_users where dom_id=17;"
      
      +----+-------+--------+--------------+
      | id | db_id | dom_id | db_server_id |
      +----+-------+--------+--------------+
      | 115 | 15 | 5 | 3 |
      +----+-------+--------+--------------+
      
      # [DESTINTAION SERVER]
      
      # plesk db "select dom_id, name, db_server_id, default_user_id from data_bases where dom_id=5;"
      
      +----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
      | id | dom_id |               name               |                      login               | db_server_id | default_user_id |
      +----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
      | 30 | 10 | database_2_from_mysqlserver2.com | user_of_database_2_from_mysqlserver2.com | 1 | NULL |
      +----+--------+----------------------------------+------------------------------------------+--------------+-----------------+
      
      # plesk db "select id, db_id, dom_id, db_server_id from db_users where dom_id=17;"
      
      +----+-------+--------+--------------+
      | id | db_id | dom_id | db_server_id |
      +----+-------+--------+--------------+
      | 330 | NULL | 10 | 1 |
      +----+-------+--------+--------------+

    • Change the db_server_id value in the data_bases table on the destination server to the correct one:

      # plesk db "update data_bases set db_server_id=3 where id=30;"

    • Change the default_user_id value in the data_bases table on the destination server to the correct one:

      # plesk db "update data_bases set default_user_id=330 where id=30;"

    • Change the db_id value in the db_users table on the destination server to the correct one:

      # plesk db "update db_users set db_id=30 where id=330;"

    • Change the db_server_id value in the db_users table on the destination server to the correct one:

      # plesk db "update db_users set db_server_id=3 where id=330;"