ALTER TABLE on PRIMARY KEY without Downtime or Lock Table

Introduction

When it comes to modifying a table’s structure, particularly when the primary key column is involved, MySQL provides several options for doing so without downtime or locking the table. In this article, we will explore the different approaches available and provide examples of how to implement each one.

Understanding PRIMARY KEY Constraints

Before diving into the solutions, it’s essential to understand what a PRIMARY KEY constraint does in MySQL. A PRIMARY KEY is a unique identifier for each row in a table. It ensures that there are no duplicate values for this column across all rows in the table. When you create a PRIMARY KEY on a column, MySQL will prevent you from inserting a new row with an existing value for that column.

The Problem: Altering a Primary Key Column

In your case, you want to change the type of the primary key column from INT to UNSIGNED INT. This is a straightforward modification, but it poses a challenge because the table’s structure must remain intact until the change is applied.

If you try to alter the column using the standard ALTER TABLE statement without proper precautions, MySQL will lock the table, preventing any further writes until the operation completes.

Solution 1: Using pt-online-schema-change

One popular tool for performing online schema changes in MySQL is pt-online-schema-change. This tool allows you to modify your database structure while still allowing concurrent writes to the table. It works by temporarily partitioning the table into smaller, more manageable pieces and applying the changes to each partition separately.

To use pt-online-schema-change, you’ll need to follow these steps:

1. Install and configure pt-online-schema-change on your MySQL server.
2. Identify the primary key column(s) that require modification.
3. Run the pt-online-schema-change command to start the process. This will create a temporary partition for the table.
4. Modify the primary key column type using the standard ALTER TABLE statement.
5. Update any dependent processes (e.g., indexes, views) as needed.
6. Once you’ve completed these steps, run the pt-online-schema-change command again to reassemble the table from its partitioned state.

Here’s an example of how you might use pt-online-schema-change:

-- Create a new user for pt-online-schema-change
CREATE USER 'pt_online_schema_change'@'%' IDENTIFIED BY 'password';

GRANT PROCESS ON *.* TO 'pt_online_schema_change'@'%';
GRANT RELOAD, SHUTDOWN ON *.* TO 'pt_online_schema_change'@'%';

-- Set the MySQL server to allow pt-online-schema-change
SET GLOBAL innodb_flush_log_at_trx_commit = 0;
SET GLOBAL log_bin_trust_function_calls = 1;

-- Start pt-online-schema-change
pt-online-schema-change --server=your_server_address \
                    --db=your_database_name \
                    --table=your_table_name \
                    --changes='ALTER TABLE your_table_name CHANGE id id UNSIGNED INT AUTO_INCREMENT NOT NULL'

Solution 2: Using MySQL Replication and Bin Logging

Another approach is to use MySQL replication and bin logging to modify the primary key column without locking the table. This method requires a bit more setup, but it provides flexibility if you need to apply changes across multiple databases.

Here’s a step-by-step guide:

1. Set up replication between your source database (where you want to make the change) and a slave database.
2. Configure bin logging on the source database to capture all changes made after a specific point in time.
3. Identify the primary key column(s) that require modification.
4. Run a SELECT statement with the new primary key column type to update the table in memory (i.e., the temporary result set).
5. Use the binlog command-line tool to create a bin log dump of the changes made during step 4.
6. Apply the same ALTER TABLE statement to the slave database, using the bin log dump as input.

Here’s an example:

-- Set up replication and configure bin logging on the source database
SET GLOBAL innodb_flush_log_at_trx_commit = 0;
SET GLOBAL log_bin_trust_function_calls = 1;

-- Start a new transaction to capture changes
START TRANSACTION;

-- Run the SELECT statement with the new primary key column type
SELECT id, id UNSIGNED INT AS new_id FROM your_table_name WHERE id IN (SELECT MIN(id) FROM your_table_name);

-- Create a bin log dump of the changes made during the previous step
binlogDump -b 1024 --format=JSON > temp_bin_log.json

-- Apply the same ALTER TABLE statement to the slave database, using the bin log dump as input
mysql -h your_slave_server_address -u your_username -p your_password \
    -e "SELECT * FROM your_table_name WHERE id IN (SELECT MIN(id) FROM your_table_name);" \
    -e "ALTER TABLE your_table_name CHANGE id id UNSIGNED INT AUTO_INCREMENT NOT NULL;" \
    < temp_bin_log.json

-- Commit the transaction
COMMIT;

Solution 3: Using MySQL 8.0’s ON CHANGE Statement

MySQL 8.0 introduces a new statement called ON CHANGE that allows you to modify the structure of a table without locking it.

Here’s an example:

ALTER TABLE your_table_name
ADD COLUMN new_id UNSIGNED INT ON CHANGE(id) TO id;

ALTER TABLE your_table_name
MODIFY COLUMN new_id UNSIGNED INT AUTO_INCREMENT NOT NULL;

The ON CHANGE statement allows you to specify a trigger function that will be executed whenever the specified column is updated. In this case, we’re using an empty trigger function to simply update the existing value.

Conclusion

Modifying a table’s primary key column without downtime or locking it can seem daunting, but there are several approaches available in MySQL. By understanding PRIMARY KEY constraints, utilizing tools like pt-online-schema-change, leveraging replication and bin logging, and taking advantage of MySQL 8.0’s ON CHANGE statement, you can successfully modify your database structure while minimizing disruption to concurrent applications.

Remember to choose the approach that best suits your specific use case and performance requirements. With careful planning and execution, you can ensure a smooth migration process for your MySQL database.

Last modified on 2023-09-29