How to avoid data loss in MySQL Primary Key change

Primary keys are the backbone of efficient data access and maintaining data consistency in your MySQL databases. However, altering them requires careful planning and execution, as incorrect procedures can lead to data loss. Recently I came across a question where user attempted to change Primary Key using pt-online-schema-change and ended up loosing data.

This blog post highlights how an improperly executed Primary Key change in MySQL can cause data loss and explain the reasoning behind the same. We will perform online Primary Key change using industry standard Percona’s pt-online-schema-change tool.

Preparing for Primary Key Change in MySQL

Let us create a hypothetical scenario for changing primary key and see to generate the data loss situation. We will create a table primary_change and aim here is to use new_id column as primary key instead of id column.

(I am sure you already know MySQL random data generator script to generate dummy data.)

mysql> create table primary_change ( id int not null primary key, new_id int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> show create table primary_change\G
*************************** 1. row ***************************
       Table: primary_change
Create Table: CREATE TABLE `primary_change` (
  `id` int NOT NULL,
  `new_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> insert into primary_change values (1,1), (2,2), (3,3), (4,4), (5,4);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from primary_change;
+----+--------+
| id | new_id |
+----+--------+
|  1 |      1 |
|  2 |      2 |
|  3 |      3 |
|  4 |      4 |
|  5 |      4 |
+----+--------+
5 rows in set (0.00 sec)

Note that the new_id is our candidate primary key and it is having duplicate values.

Aim here is to switch the primary key from “id” column to “new_id” column.

Changing Primary Key with pt-online-schema-change

[root@centos_1 ~]# pt-online-schema-change    \
> --print --progress time,10 \
> --pause-file=/tmp/pt-primary-change.pause \
> --no-swap-tables --no-drop-old-table --no-drop-new-table --no-drop-triggers \
> --alter="DROP PRIMARY KEY, ADD PRIMARY KEY (new_id)" \
> --set-vars transaction_isolation=\'READ-COMMITTED\',lock_wait_timeout=30 \
> h=localhost,D=test,t=primary_change  \
> --no-check-unique-key-change \
> --chunk-time=1 \
> --new-table-name=_%T_primary_change_new \
> --execute
No slaves found.  See --recursion-method if host centos_1.localdomain has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`primary_change`...
--alter contains 'DROP PRIMARY KEY'.  Dropping and altering the primary key can be dangerous, especially if the original table does not have other unique indexes.
The tool should handle this correctly, but you should test it first and carefully examine the triggers which rely on the PRIMARY KEY or a unique index.  Specify --no-check-alter to disable this check and perform the --alter.
--check-alter failed.
`test`.`primary_change` was not altered.
[root@centos_1 ~]#

Pay careful attention here, out tool, pt-online-schema-change warns about the dangers of altering Primary Key without having a careful examination. That’s why the tool employs a security check and asks us to use –no-check-alter flag to confirm that we have done the due diligence for executing the change.

Data Loss by Primary Key change in MySQL

Let us not do the due-diligence and use –no-check-alter to run the command anyhow.

[root@centos_1 ~]# pt-online-schema-change    --print --progress time,10 --pause-file=/tmp/pt-primary-change.pause --no-swap-tables --no-drop-old-table --no-drop-new-table --no-drop-triggers --alter="DROP PRIMARY KEY, ADD PRIMARY KEY (new_id)" --set-vars transaction_isolation=\'READ-COMMITTED\',lock_wait_timeout=30 h=localhost,D=test,t=primary_change  --no-check-unique-key-change --chunk-time=1 --new-table-name=_%T_primary_change_new --execute --no-check-alter
No slaves found.  See --recursion-method if host centos_1.localdomain has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`primary_change`...
Creating new table...
CREATE TABLE `test`.`_primary_change_primary_change_new` (
  `id` int NOT NULL,
  `new_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table test._primary_change_primary_change_new OK.
Altering new table...
ALTER TABLE `test`.`_primary_change_primary_change_new` DROP PRIMARY KEY, ADD PRIMARY KEY (new_id)
Altered `test`.`_primary_change_primary_change_new` OK.
2024-02-26T05:43:53 Creating triggers...
-----------------------------------------------------------
Event : DELETE
Name  : pt_osc_test_primary_change_del
SQL   : CREATE TRIGGER `pt_osc_test_primary_change_del` AFTER DELETE ON `test`.`primary_change` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `test`.`_primary_change_primary_change_new` WHERE `test`.`_primary_change_primary_change_new`.`new_id` <=> OLD.`new_id`; END
Suffix: del
Time  : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : UPDATE
Name  : pt_osc_test_primary_change_upd
SQL   : CREATE TRIGGER `pt_osc_test_primary_change_upd` AFTER UPDATE ON `test`.`primary_change` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `test`.`_primary_change_primary_change_new` WHERE !(OLD.`new_id` <=> NEW.`new_id`) AND `test`.`_primary_change_primary_change_new`.`new_id` <=> OLD.`new_id`; REPLACE INTO `test`.`_primary_change_primary_change_new` (`id`, `new_id`) VALUES (NEW.`id`, NEW.`new_id`); END
Suffix: upd
Time  : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : INSERT
Name  : pt_osc_test_primary_change_ins
SQL   : CREATE TRIGGER `pt_osc_test_primary_change_ins` AFTER INSERT ON `test`.`primary_change` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `test`.`_primary_change_primary_change_new` (`id`, `new_id`) VALUES (NEW.`id`, NEW.`new_id`);END
Suffix: ins
Time  : AFTER
-----------------------------------------------------------
2024-02-26T05:43:53 Created triggers OK.
2024-02-26T05:43:53 Copying approximately 5 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_primary_change_primary_change_new` (`id`, `new_id`) SELECT `id`, `new_id` FROM `test`.`primary_change` LOCK IN SHARE MODE /*pt-online-schema-change 1282 copy table*/
2024-02-26T05:43:53 Copied rows OK.
Not dropping old table because --no-drop-triggers was specified.
Not dropping triggers because --no-drop-triggers was specified.  To drop the triggers, execute:
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_primary_change_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_primary_change_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_primary_change_ins`
Not dropping the new table `test`.`_primary_change_primary_change_new` because --no-drop-new-table was specified.  To drop the new table, execute:
DROP TABLE IF EXISTS `test`.`_primary_change_primary_change_new`;
Successfully altered `test`.`primary_change`.
[root@centos_1 ~]#

mysql> select * from _primary_change_primary_change_new;
+----+--------+
| id | new_id |
+----+--------+
|  1 |      1 |
|  2 |      2 |
|  3 |      3 |
|  4 |      4 |
+----+--------+
4 rows in set (0.00 sec)

Note that the “new” table created by pt-online-schema-change has missing record for id=5. This happened because the new_id column had duplicate records “4” for both id “4” and “5”.

Why This Kolaveri Di?

I mean to ask, so why did we see data loss in previous Primary Key change using pt-online-schema-change?

During the online schema change, pt-online-schema-change creates a temporary table with the desired modifications. It then copies data from the original table to the new one, using INSERT IGNORE statements to bypass potential duplicate key errors in the new primary key column. Additionally, the tool utilizes REPLACE INTO statements within the created triggers, which can also lead to data loss if duplicates exist.

I invite you to explore the code of the pt-online-schema-change, isn’t that the beauty of open-source?
This section of the nibbeling process uses INSERT IGNORE to avoid duplicate errors:

my $dml = "INSERT LOW_PRIORITY IGNORE INTO $new_tbl->{name} "
. "(" . join(', ', map { $q->quote($_->{new}) } @common_cols) . ") "
. "SELECT";
my $select = join(', ', map { $q->quote($_->{old}) } @common_cols);

Source: https://github.com/percona/percona-toolkit/blob/3.x/bin/pt-online-schema-change.

Ensure uniqueness of Primary Key in MySQL

A very easy query for identifying if the data of a column has duplicates is using GROUP BY .. HAVING clauses. Find a sample MySQL query below:

SELECT col_name, COUNT(col_name)
FROM table_name
GROUP BY col_name
HAVING COUNT(col_name) > 1;

If you’re candidate primary key has duplicate values, consider taking appropriate actions.

Consider testing the online schema change in a non-production environment before applying it to your main database. Regularly back up your databases to ensure you have a reliable recovery point in case of unforeseen issues.

Conclusion

With power comes responsibility – pt-online-schema-change is every DBA’s power tool but should be used responsibly. Keep keen eyes to the warnings presented by scripts. When you plan your primary key change with a proactive approach and attention to detail, you can navigate the complexities of primary key changes with confidence and safeguard your valuable data assets.