Table modifications are a common task for database administrators. In this blog, I’ll explain how to alter tables online in a controlled manner that does not disrupt application users or cause application downtime.

One of the tools in Percona Toolkit is pt-online-schema-change, a utility that alters the structure of a table without interfering with the reads or writes. The tool creates an empty copy of the table to alter and modify as desired before copying the rows from the original table into the new one.

When the copying is finished, it removes the original table and replaces it with the new one. Any changes made to data in the original tables during the copy process will be reflected in the new table as the tool creates triggers on …

One thing I commonly hear when working with my clients is “I want to change my DDL strategy in order to avoid locking in my database! The last time I used the same old method I ended up in a metadata lock situation!”

I agree that metadata locks can be painful, but unfortunately, it’s completely unavoidable, and changing from one tool to another won’t help with this. That said, it’s still worth it to examine how metadata locks work and what the impact is for each of the common tools and processes. In doing so we will see that all these tools will require metadata locks, but knowing more about how they work and how the use locking can help us determine the right tool for your specific use case.

Any time you make a change to a table a metadata lock is needed to ensure consistency between the table itself and MySQL’s data dictionary. In order for MySQL to establish this lock it has to wait for any query against the table in …

Vitess introduces a new way to run schema migrations: non-blocking, asynchronous, scheduled online DDL. With online DDL Vitess simplifies the schema migration process by taking ownership of the operational overhead, and providing the user a simple, familiar interface: the standard ALTER TABLE statement. Let’s first give some background and explain why schema migrations are such an issue in the databases world, and then dive into implementation details The relational model and the operational overhead # The relational model is one of the longest surviving models in the software world, introduced decades ago and widely used until today.

Recently I was dealing with an unexpected issue raised by our Support customer, in which data became inconsistent after a schema change was applied.

After some investigation, it turned out that affected tables had a special word in the comments of some columns, which triggered an already known (and fixed) issue with the TableParser.pm library of Percona Toolkit.  The problem is that the customer was using an outdated Toolkit version, where pt-online-schema-change was using that buggy parser.

This bug applies only to Percona Toolkit versions up to 3.0.10, so if you have already 3.0.11 or newer installed, you can skip the rest of this post as these are no longer affected.

I am writing this post to warn every user of pt-online-schema-change who has not upgraded the toolkit, as …

Foreign key related issues are very common when dealing with DDL changes in MySQL using Percona toolkit. In this blog post, I will explain how the tool (pt-online-schema-change) handles foreign key constraints when executing a DDL change.

First of all, I would like to explain why foreign keys have to be handled at all before writing more about the “How”. Foreign key constraints are aware of table rename operations. In other words, if the parent table is renamed, the child table automatically knows it and changes the foreign key constraint accordingly. Please have a look at the below example, and you can see the table name is automatically updated in the child table after the rename operation on the parent table:

mysql> show create table prd_details \G
*************************** 1. row *************************** …

Before we proceed, here is useful blog post  written by Peter on Hijacking Innodb Foreign Keys.

However, if you are trying to get rid of an unused Foreign Key (FK) constraint and related columns from versions older than mysql 5.6 or tables which cannot be executed with

ALTER TABLE ... ALGORITHM=INPLACE

  because of limitations mentioned here (specifically tables with 5.5 TIMESTAMP formats), you can use pt-online-schema-change  to drop such foreign keys.

For DROP FOREIGN KEY

constraint_name

  with  PT-OSC requires specifying

_constraint_name

  rather than the real

constraint_name …

In this blog post, we’ll look at how to get rid of the unused Foreign Key (FK) constraint and/or related columns/keys with the help of pt-online-schema-change and the power of its plugins.

Before we proceed, here is a useful blog post written by Peter Zaitsev on Hijacking Innodb Foreign Keys.

If you are trying to get rid of an unused foreign key (FK) constraint and related columns from versions older than MySQL 5.6, or tables that cannot be executed with

ALTER TABLE ... ALGORITHM=INPLACE

 because of limitations mentioned here (specifically, …

Percona announces the availability of Percona Toolkit 2.2.20. Released December 9, 2016, Percona Toolkit is a collection of advanced command-line tools that perform a variety of MySQL server and system tasks that DBAs find too difficult or complex for to perform manually. Percona Toolkit, like all Percona software, is free and open source.

This release is the current GA (Generally Available) stable release in the 2.2 series. Downloads are available here and from the Percona Software Repositories.

…

In this blog, we’ll discuss changing the tablespace directory using pt-online-schema-change.

One of the most annoying situations in the life of a DBA is realizing that the disk where the datadir resides is running out of space. If you’re lucky enough to run over an LVM volume or a RAID (depending on the level, though), it is easy to add disk space. But what if you are not that lucky, and your datadir is running on a single disk? Not so funny!

That is the exact situation we recently faced with a customer, for both the master and slave server. When trying to figure out a solution we saw that:

• There was enough space on a different partition within the same server.
• The tables have their own tablespace (innodb_file_per_table = on)
• The MySQL version was …

In this blog post, I discuss some of the insights needed when using Amazon RDS and pt-online-schema-change together.

The pt-online-schema-change tool runs DDL queries (ALTER) online so that the table is not locked for reads and writes. It is a commonly used tool by community users and customers. Using it on Amazon RDS requires knowing about some specific details. First, a high-level explanation of how the tool works.

This is an example from the documentation:

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

The tool runs an ALTER on the table “actor” from the database “sakila.” The alter adds a column named “c1” of type …