Migration from Maria DB to Percona DB fails with error: explicit primary key with pxc_strict_mode = ENFORCING

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

Symptoms:
When migrating from a Maria DB to a Percona, an error similar to the following is encountered:

------------ STARTING pre-start-execution at Thu Feb 7 14:02:34 UTC 2019 --------------
ERROR 1105 (HY000) at line 1556: Percona-XtraDB-Cluster prohibits use of DML command on a table (ccdb.backup_ccdb_users) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER
panic: exit status 1

goroutine 1 [running]:
main.main()
/var/vcap/data/compile/migrate-to-pxc/src/migrate-to-pxc/main.go:71 +0x4bd
mysqldump: Got errno 32 on write

Environment

Cause

The migration script expects there to be a primary key in each table because pxc_strict_mode = ENFORCING in Percona. By default, PXC Strict Mode is set to ENFORCING. If a validation fails during startup, the expected behavior of PXC Stick Mode is to halt the server and throw an error.

Users have run into two scenarios which caused this behavior:

1. You upgraded from PAS 2.1 directly to PAS 2.3 AND attempted to migrate MariaDB to PXC all in one deploy. By doing this, you missed out on database schema changes to add primary keys.

2. You created backup tables manually in PAS 2.1. For example: SELECT ... INTO from ccdb.users table. By doing this you, freeze PAS 2.1 tables and as a result, they are missing the primary keys.

Resolution

To successfully migrate databases, follow one of the approaches below based on the cause of the issue:

1. If you upgraded from PAS 2.1 directly to PAS 2.3 AND attempted to migrate MariaDB to PXC all in one deploy, upgrade to PAS 2.2 first before migrating to Percona. 

2. If tables were manually created, there are two options after connecting to Maria DB. To do so, you can ssh into a mysql node an execute mysql --defaults-extra-file=/var/vcap/jobs/mysql/config/mylogin.cnf:

• Drop the user created tables.
• Add primary keys to the tables with the following command:  `ALTER TABLE` ccdb.backup_ccdb_users` ADD `bogusid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY

Note: When all tables have primary keys, this error will go away.