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 ***************************
       Table: prd_details
Create Table: CREATE TABLE `prd_details` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_catalog_id` int(11) unsigned NOT NULL,
  ......
  CONSTRAINT `prd_details_ibfk_1` FOREIGN KEY (`product_catalog_id`) REFERENCES `product_catalog` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)i
mysql> RENAME TABLE product_catalog TO product_cat ;
Query OK, 0 rows affected (0.15 sec)
mysql> show create table prd_details \G
*************************** 1. row ***************************
       Table: prd_details
Create Table: CREATE TABLE `prd_details` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_catalog_id` int(11) unsigned NOT NULL,
  ......
  CONSTRAINT `prd_details_ibfk_1` FOREIGN KEY (`product_catalog_id`) REFERENCES `product_cat` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Well, that is indeed very nice and to be expected. But please allow me to explain how this becomes a problem when dealing with DDL changes using pt-online-schema-change. The tool implements the DDL changes as mentioned below. Please keep in mind that these are just to give an idea of how the tool works, as there would be more internal things going on in reality.

Let’s take an example ALTER for this case:

Query:

ALTER TABLE T1 MODIFY COLUMN c1 BIGINT UNSIGNED NOT NULL ;

pt-online-schema-change steps for the above alter:

1. Create a similar table _T1_new
2. Modify the column c1 to BIGINT in the table _T1_new
3. Define triggers on table T1 so that changes to data on the original table will be applied to _T1_new as well.
4. Copy the data from table T1 to _T1_new.
5. Swap the tables
6. Drop triggers.

All looks good so far. Now let’s see why these steps create a problem, with a close look at Step #5 (Swap the tables).

Without foreign keys: Swapping of these tables is done as below, which looks nice.

• Rename T1 —> T1_old
• Rename _T1_new –> T1
• If everything right, drop the table T1_old
• Drop triggers on the new T1 table

With foreign keys: As I mentioned earlier, if there are any child tables with foreign keys to table T1, after renaming, they would automatically point to T1_old but not the new T1. Pt online schema change has to ensure the child table refers to a correct parent table by the end of this DDL change.

• Rename T1 —> T1_old       =====? The child table refers to T1_old automatically.
• Rename _T1_new —> T1

In this case, the foreign keys in the child table are still referring to the old table T1_old which don’t have the schema change in place. If you drop T1_old, child table CT1 ends up pointing to a table that doesn’t exist. That’s a very bad situation. Now let’s talk about how the tool handles this.

How does pt-online-schema-change handle this?

The tool comes up with an option named --alter-foreign-keys-method This option supports two values at a high level and below you can see what are those and how they will work.

alter-foreign-keys-method=drop_swap

With this value, it won’t swap as mentioned in the steps. Rather, it drops the old table and then renames the new table with the change in place.

• Disable foreign key checks for the session (FOREIGN_KEY_CHECKS=0)
• Drop the table T1_old
• Rename the new table _T1_new –> T1

The good thing here is that it is quick, but the bad thing is that it’s not reliable. If something goes wrong with renaming, it ends up with the same problem of referring to an unexisting table.

alter-foreign-keys-method=rebuild_constraints

This is the preferred approach for the reason it maintains the consistency of the schema and its relations. In this approach, before dropping the old table, it runs ALTER on all the child tables to drop existing FK and re-add new FK constraints that points to the columns from the new table (with the schema change in place).  Below sequence of bullet points explains the same.

• Rename T1 –> T1_old
• Rename _T1_new –> T1
• ALTER on child table to adjust the foreign key so that it points to T1 rather T1_old.

ALTER TABLE child_table DROP FOREIGN KEY `fk_name`, ADD CONSTRAINT `_fk_name` FOREIGN KEY  (`child_table_column`) REFERENCES _T1_new (`parent_table_column`)

• Drop the table T1_old
• Drop triggers from the new T1 table.

I would like to mention that the current implementation to rebuild the child table can be improved by making use of the INPLACE ALTER which I hope would probably be available in upcoming releases. You can see more information about this in the existing bug report here. I will discuss in brief about the two other options available, which are derived based on the above two. Let’s have a quick look.

auto:  If this value is used, it leaves the decision up to the tool itself to choose from the two (drop_swap/rebuild_constraints) options available. If the number of rows in the child table is small, it uses rebuild_constraints; otherwise, it goes with the drop_swap approach. For this reason, this option should always be chosen carefully as it can end up with unexpected results when choosing drop_swap. Below is an example log snippet which explains this behavior:

# pt-online-schema-change --user=root --password=xxxxxxx --alter-foreign-keys-method=auto  --alter "MODIFY COLUMN header_id BIGINT unsigned NOT NULL AUTO_INCREMENT" D=DB1,t=T1 --execute
...........
Copying `DB1`.`T1`:  75% 00:18 remain
2019-05-28T12:49:41 Copied rows OK.
2019-05-28T12:49:41 Max rows for the rebuild_constraints method: 5588
Determining the method to update foreign keys...
2019-05-28T12:49:41   `DB1`.`child_of_T1`: too many rows: 197076; must use drop_swap
2019-05-28T12:49:41 Drop-swapping tables...
...........

none: If this value is used, it is similar to drop_swap but without swapping. In other words, it just drops the original table and leaves the child tables in a state which they point to a table that doesn’t exist. In this case, DBA’s have need to fix the leftover job.

Photo by Silas Köhler on Unsplash

It used to be easy: a company developed a new application, chose a database solution, launched the new application and then tuned the chosen database solution. A team of DBAs looked at the infrastructure as well as the workload and made changes (or suggestions) as needed. The application then stayed in production for years and small tweaks were made as needed.

Those days are long gone.

As technology has evolved, so has the workflow and deployment strategy within the large enterprise.  Large, monolithic applications are being split into several microservices, generally decoupled but still working together and somewhat interdependent. Waterfall deployment strategies are replaced with agile methodology and continuous code deployment. Tuning and maintaining large installations of physical hardware has become less of the focus with the advent of virtualization, containerization, and orchestrated deployments.

Despite all of these changes and radical shifts in the market, one question for executives and management has remained constant: what approach should I use to maximize my return and give me the most productive environment for the lowest cost? As any good consultant will tell you, “it depends”.  Even with all the advances in technology, frameworks, and deployment strategies, there is still no silver bullet that achieves everything you need within your organization (while also preparing your meals and walking your dog).

Choosing an Enterprise Database Solution

In this post, we’ll discuss some of the paths you can take as a guide on your journey of choosing an enterprise database solution. It’s not meant to provide technical advice or suggest a “best option.”

Before going into some of the options, let’s put a few assumptions out there:

• Your organization wants to use the right database solution for the job (or a few limited solutions)
• You DO NOT want to rack new physical servers every time you need a new server or expect growth
• Your application teams far outnumber your operations and database team (in terms of number of teams and overall members)
• The question of “what does your application do” is more accurately replaced with several variations of “what does this particular application do”

Now that we have that out of the way, let’s start with buzzword number one: the cloud. While it is used all the time, there are a few different meanings. Originally (and most commonly), the cloud is referring to the “public” cloud — entities like Amazon Web Services (AWS), Microsoft Azure, and Google Cloud.  When it first came to fruition, the most common barrier to organizations moving to the cloud was security. As more and more PII data is stored by large enterprises, the inherent fear of a breach in the public cloud led many companies to shy away. Although this is much less of a concern given all the advances in security, there are some instances where an organization might still believe that storing data in a “public” datacenter is a hard no. If this is your organization, feel free to skip ahead to the on-premise discussion below.

Public Cloud

Assuming that you can engineer proper security in the public cloud of your choosing, some of the main benefits of outsourcing your infrastructure quickly bubble to the top:

• Elasticity
• Flexibility
• Agility
• Cost

Elasticity

In many circumstances, you need increased capacity now, but only for a limited time. Does this scenario sound familiar? The beauty of the public cloud is that you generally only pay for what you are using. Looking at things from a long-term cost perspective, if you only need two times your capacity for two weeks out the year, why should you pay for half of your infrastructure to sit idle for the other fifty weeks annually?

Since you don’t have to actually maintain any physical gear in the public cloud, you have the ability to add/remove capacity as needed. There is no need to plan for or provision for additional hardware — and everything that comes with that (e.g., maintaining the cooling systems for double the number of data center servers, increased power costs, expanded physical space, etc.).

Flexibility / Agility

Most public clouds offer more than simply instant access to additional compute instances. There are managed services for several common use cases: relational databases, NoSQL databases, big data stores, message queues, and the list goes on. This flexibility is evident in using various managed services as glue to hold other managed services together.

In traditional environments, you may identify the need for a technology (think message queue), but opt against it due to the complexity of needing to actually manage it and use a less efficient alternative (a relational database for example). With these components readily available in most public clouds, your organization has the flexibility to use the correct technology for each use case without the burden of maintaining it.

Along with the flexibility of plugging in the appropriate technology, you greatly increase the speed at which this can be done. There is much less need from an infrastructure standpoint to plan for supporting a new technology. With the click of a button, the new technology is ready to go in your stack.  In an agile work environment, having an agile platform to accompany the methodology is very important.

Cost

While the above benefits are all really great, the bottom line is always (the most) important. Depending on how you determine the overall cost of your infrastructure (i.e., hardware only, or do you include operations staff, building costs, etc.) you can see cost savings. One of the big challenges with running physical gear is the initial cost. If I want to run a rack of 20 servers, I have to buy 20 servers, rack them up and turn them on. My ongoing operational cost is likely going to be less than in the cloud (remember, in the cloud you are paying as you use it), but I also need to spread the initial cost over time.

While an overall cost analysis is well outside the scope of this document, you can see how determining cost savings using the public cloud vs. an on-premise solution can be challenging. With all else being equal, you will generally have a more predictable monthly cost when using the public cloud and often can get volume (or reserved) discounts. For example, AWS provides a “CTO Calculator” to estimate how you could save on cost by switching to the public cloud: https://aws.amazon.com/tco-calculator/.

On-Premise

So the powers that be at your company have drawn a line in the sand and said “no” to using the public cloud. Does that mean that each time an application team needs a database, your operations team is racking a server and setting it up? It very well could, but let’s explore a few of the options available to your infrastructure team.

Dedicated Hardware

While this option can seem outdated, there are several benefits to provisioning bare metal machines in your data center:

• Complete control over the machine
  • OS tuning
  • Hardware choices
  • Physical control
• Easy to make different “classes” of machine
  • Spinning disks for DR slaves
  • SSD for slaves
  • Flash storage for masters
  • Etc
• Easier troubleshooting
  • Less of a need to determine which “layer” is having problems
• Less overhead for virtualization/containerization
• No “extra servers” needed for managing the infrastructure

In a relatively static environment, this is still a great choice as you have full access and minimal layers to deal with. If you see disk errors, you don’t have to decide which “layer” is actually having problems – it is likely the disk. While this is nice, it can be cumbersome and a burden on your operations staff when there are always new databases being added (for microservices or scaling).

In this model, each server is assumed to be a static resource. Generally, you wouldn’t provision a bare metal machine with an OS and database and then wipe it and start over repeatedly. Rather, this model of deployment is best suited to an established application running a predictable workload, where scaling is slow and over time.

A major downside to this approach is resource utilization. Normally, you wouldn’t want to only use half of everything that you purchase. When dealing with bare metal machines, you generally don’t want to have everything running at maximum capacity all the time so that you can handle spikes in traffic.  When provisioning bare metal machines, this means you either have to pay for all of your potential resources and then watch most of them sit idle much of the time or risk outages while continuously running at the limits.

Virtualization/Containers

Right up there with “the cloud”, another buzzword these days is “containers”. At a high level, containers and virtualization are similar in that they both allow you to use part of a larger physical server to emulate a smaller server. This gives operations teams the ability to create “images” that can be used to quickly provision “servers” on larger bare metal machines.

While this does add a new layer to your stack, and can potentially introduce some additional complexity in tuning and/or troubleshooting, two major problems with bare metal provisioning are addressed:

• Flexibility
• Resource utilization

In terms of flexibility, operations teams are able to have a collection of standard images for various systems, such as application servers or database servers, and quickly spin them up on readily waiting hardware. This makes it much easier when an application team says “we need a new database for this service and will need four application servers with it.”  Rather than racking up and setting up five physical machines and installing the OS along with various packages, the operations team simply starts five virtual machines (or containers for those of you “containerites” out there) and hands them off.

This also helps with resource utilization. Rather than setting one application server up on a physical machine and keeping it under 50% utilization all the time, you are able to launch multiple VMs on this machine, each just using a portion. When the physical machine reaches maximum capacity, you can move an image to a new physical machine. This process gets rinsed and repeated as traffic patterns change and resource demands shift. It decreases some of the pain that comes from watching bare machines sit idle.

Private Cloud

Now, let’s put it all together and talk about creating a private cloud. It’s the best of both worlds, right?  All the flexibility and elasticity of the public cloud, but in your own data center where you can retain full control of everything. In this scenario, an organization is generally doing the following:

• Managing a data center of generic, physical machines
• Leveraging virtualization and/or containerization to quickly launch/destroy server images
• Using an orchestration layer to manage all of the VMs/containers

This is a great fit for organizations that already have made an investment in a large physical infrastructure. You likely already have hundreds of servers at your disposal, so why not get the most utilization you can out of them and make your infrastructure much more dynamic?

Consider this…

While this sounds amazing (and quite often IS the best fit), here’s what to consider.  When dealing with a large internal cloud, you will need people experienced in managing this sort of infrastructure. Even though application teams now just hit a button to launch a database and application server, the cloud is still backed by a traditional data center with bare metal servers. An operations team is still a very needed entity — even though they may not be your traditional “DBA” or “ops guy”.

Also, the complexity of managing (and definitely troubleshooting) an environment such as this generally increases by an order of magnitude. Generic questions like “why is my application running slow?” used to be easier to answer: you check the application server and the database server, look at some metrics, and can generally pinpoint what is happening. In a large private cloud, now you’ll need to look at:

• Application/query layer
• Orchestration layer
• Virtualization / container layer
• Physical layer

It is not to say it isn’t worth it, but managing an internal cloud is not a trivial task and much thought needs to be put in.

How Can Percona Help?

Having been in the open source database space for years, Percona has seen and worked on just about every possible MySQL deployment possible. We also focus on picking the proper tool for the job and will meet your organization where you are. Running Postgres on bare metal servers? We can help.  Serving your application off of EC2 instances backed by an RDS database? No problem. MongoDB on Kubernetes in your private cloud? Check.

We can also work with your organization to help you choose the best path to follow. We love open source databases and the flexibility that they can provide. Our team has experience designing and deploying architectures ranging from a single database cloud server to hundreds of bare metal machines spanning across multiple data centers. With that sort of experience, we can help your organization with an enterprise database solution too!

Recently, I received a support request where the customer wanted to convert an INTEGER column to BIGINT on two tables. These tables are related by a foreign key, and it is a 3 node PXC cluster. These tables are 20GB and 82 GB in size and DDL’s on such tables in a production environment is always a challenge. We have options like direct ALTER or using pt-online-schema-change to get this done, but this is a very special case where none of them will be able to do this DDL. To demonstrate why this is so, please follow the table schema and the example below about DDL queries on foreign key columns. In the end, I will discuss an easy workaround, too.

Please take a look at table schema before reading further. In the below schema, the product_catalog_id column from the product_details table refers to catalog_id column from the product_catalog table. Both these fields are INT(11) and the customer wanted to convert them to BIGINT:

mysql> show create table product_catalog \G
*************************** 1. row ***************************
       Table: product_catalog
Create Table: CREATE TABLE `product_catalog` (
  `catalog_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  .....
  PRIMARY KEY (`catalog_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
mysql> show create table product_details \G
*************************** 1. row ***************************
       Table: product_details
Create Table: CREATE TABLE `product_details` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_catalog_id` int(11) unsigned NOT NULL,
  .....
  PRIMARY KEY (`product_id`),
  KEY `fk_audit_detail_audit_header_idx` (`product_catalog_id`),
  CONSTRAINT `product_catalog_ibfk_1` FOREIGN KEY (`product_catalog_id`) REFERENCES `product_catalog` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

Changing a column from INT to BIGINT is an ONLINE ALTER. But in this case, it is not an ONLINE ALTER as the primary key is defined in this column. I started with a direct ALTER and later with pt-online-schema-change. Let’s have a look at how these tools react to this DDL change.

Direct Alter:

Considering the tables are close to 100GB in size together, a direct ALTER is not a good choice especially with a PXC cluster, and also on a standard deployment, it would block queries on metadata lock. But let’s see how the direct ALTER reacts here.  I will first alter the child table and then the parent table.

mysql> ALTER TABLE product_details MODIfY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL  ;
ERROR 1832 (HY000): Cannot change column 'product_catalog_id': used in a foreign key constraint 'product_details_ibfk_1'

It failed with Error 1832. Let’s try changing the column in the parent table first.

mysql> ALTER TABLE product_catalog MODIfY COLUMN catalog_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT  ;
ERROR 1833 (HY000): Cannot change column 'catalog_id': used in a foreign key constraint 'product_details_ibfk_1' of table 'DB255525.product_details'

This time, it is Error 1833. Both these errors state that it cannot change the column involved in a foreign key. The reason is that a foreign key is defined only between two identical columns. Changing any of those column data types would result in an error.

pt-online-schema-change:

It is always recommended to use pt-online-schema-change for DDL’s in PXC cluster deployments, provided additional disk space is available. You can refer to this blog post to know when to use this tool. pt-osc works by creating a new table with the required change in place, and copies data to the new table. The challenge comes in when there are child tables referring some column in this parent table. Presence of foreign keys complicates the job of pt-osc.

There are two ways the tool handles the foreign key constraints on the child table when the parent table is renamed as part of pt-osc. Below explains each of those cases.

alter-foreign-keys-method=auto

# pt-online-schema-change --user=root --password=root --host=172.23.0.2 --alter-foreign-keys-method=auto --alter "MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL" D=DB1,t=product_details --execute
....
....
2019-05-28T09:19:16 Dropping new table...
2019-05-28T09:19:16 Dropped new table OK.
`DB255525`.`product_details` was not altered.
Error altering new table `DB1`.`_product_details_new`: DBD::mysql::db do failed: Cannot change column 'product_catalog_id': used in a foreign key constraint '_product_details_ibfk_1' [for Statement "ALTER TABLE `DB255525`.`_product_details_new` MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL"] at /usr/bin/pt-online-schema-change line 9271.

It failed while renaming the table _product_details_new to product_details. The reason is that it would have BIGINT datatype in child table and INT datatype in the parent table if the rename succeeds – which is not allowed in MySQL. Foreign keys between different data types are not allowed.

alter-foreign-keys-method=rebuild_constraints

# pt-online-schema-change --user=root --password=root --host=172.23.0.2 --alter-foreign-keys-method=rebuild_constraints --alter "MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL" D=DB1,t=product_details --execute
.....
.....
Created new table DB1._product_details_new OK.
Altering new table...
2019-05-28T09:27:24 Dropping new table...
2019-05-28T09:27:25 Dropped new table OK.
`DB255525`.`product_details` was not altered.
Error altering new table `DB1`.`_product_details_new`: DBD::mysql::db do failed: Cannot change column 'product_catalog_id': used in a foreign key constraint '_product_details_ibfk_1' [for Statement "ALTER TABLE `DB255525`.`_product_details_new` MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL"] at /usr/bin/pt-online-schema-change line 9271.

In this case, the child table is rebuilt to point to the correct parent table using an ALTER, but it failed again for the same reason. So, none of the options like direct ALTER nor pt-online-schema-change are working for this particular change.

DDL Queries on Foreign Key Columns Workaround

Even disabling foreign key checks don’t work here, as that trick only works when it comes to data, not the schema changes. This has been reported independently under Percona Server as well as in PXC branches in JIRA. You can see more information about these here and here. There is one easy yet simple workaround that I would suggest. That is to drop the foreign key constraint on the child table, run the DDL on both the child and the parent tables, and finally redefine the foreign key constraint.

As you can see, the integrity constraint is compromised for the duration for this workaround. Be sure to keep the server in read-only mode and not allow any changes to these tables, as it might lead to inconsistent data between the parent and child tables.

Step #1:
mysql> ALTER TABLE product_details DROP FOREIGN KEY product_details_ibfk_1 ;
Query OK, 0 rows affected (0.95 sec)
Records: 0 Duplicates: 0 Warnings: 0
Step #2:
# pt-online-schema-change --user=root --password=XXXXXXX --host=172.23.0.2 --alter "MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL" D=DB1,t=product_details --execute
......
Successfully altered `DB1`.`product_details`.
Step #3:
# pt-online-schema-change --user=root --password=XXXXXXX --host=172.23.0.2 --alter "MODIFY COLUMN catalog_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT" D=DB1,t=product_catalog --execute
.......
Successfully altered `DB1`.`product_catalog`.
Step #4:
mysql> ALTER TABLE product_details ADD FOREIGN KEY product_details_ibfk_1 (product_catalog_id) REFERENCES `product_catalog`(`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ;
Query OK, 0 rows affected (1.55 sec)
Records: 0 Duplicates: 0 Warnings: 0

Summary:

Foreign keys can only be defined and operated between two identical columns. Due to this constraint, DDL queries on columns involved in foreign keys are still a problem in MySQL/PXC, especially when the tables are huge. This workaround, with little downtime for writes, is the only quick way to get this done without spending time on complex logic building and implementation that involves changes on both the DB and the application.

Photo by Vanessa Bucceri on Unsplash

Redhat Enterprise Linux 8 packages have been released into our repositories.  Recent versions of Percona Server for MySQL, Percona XtraDB Cluster, Percona XtraBackup,  Percona Server for MongoDB, Percona Toolkit, and the PMM Client can now be automatically installed via Redhats dnf utility.

How to install RHEL 8

Visit https://www.redhat.com/en/technologies/linux-platforms/enterprise-linux and follow the instructions.  If you have a paid subscription,  you will be able to download the RHEL 8 DVD ISO and use this to install the operating system on a physical or virtual machine.   You may also signup for a free 30-day trial.

Once installed, you will need to register the instance with Redhat using subscription-manager in order to update your instance.   While logged in as a user with administrator privileges from shell prompt, issue the following commands:

$ sudo subscription-manager register
  (provide Red Hat account Username and Password)
$ sudo subscription-manager attach --auto
$ sudo dnf update

How to install Percona packages for RHEL 8

After you have started the RHEL 8 instance and registered it with Redhat,  you can install the percona-release package which will allow you to configure the correct repositories for the product you want to install.  

$ sudo dnf install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Installation instructions per product

Newer Percona products are separated into their own repositories in order to reduce dependency conflicts with other Percona software versions.  The percona-release script is used to configure the correct repositories for each product.

Percona Server 8.0.x

Percona Server 8.0.x is deployed into a separate repository.   Using the percona-release tool, set up the ps80 product.

Note:  Due to the modularity features built into RHEL 8’s dnf software package manager,  the mysql module must first be disabled before installing Percona Server 8.0.

$ sudo dnf module disable mysql
$ sudo percona-release setup ps80
$ sudo dnf install percona-server-server percona-server-client

If you want to install the MyRocks or TokuDB packages

$ sudo dnf install percona-server-rocksdb
$ sudo dnf install percona-server-tokudb

After installation you will need to start the server, and use the assigned temporary password to assign a new one:

$ sudo systemctl start mysqld
$ sudo grep 'temporary password' /var/log/mysqld.log
$ mysqladmin -uroot -p password

Percona Server 5.7.x

Percona Server 5.7.x is deployed into the “original” repository location.   

Note:  Due to the modularity features built into RHEL 8’s dnf software package manager,  the mysql module must first be disabled before installing Percona Server 8.0.

$ sudo dnf module disable mysql
$ sudo percona-release setup ps57
$ sudo dnf install Percona-Server-server-57 Percona-Server-client-57

If you want to install the MyRocks or TokuDB packages:

$ sudo dnf install Percona-Server-rocksdb-57
$ sudo dnf install Percona-Server-tokudb-57

After installation you will need to start the server, and use the assigned temporary password to assign a new one:

$ sudo systemctl start mysqld
$ sudo grep 'temporary password' /var/log/mysqld.log
$ mysqladmin -uroot -p password

Percona XtraBackup 8.0.x

Percona XtraBackup 8.0.x is deployed into a separate tools repository.   If you have configured the repository using the percona-release setup ps80 command no additional repository configuration is required.  If you are installing Percona XtraBackup 8.0 for use with MySQL Community 8.0 you will need to use the percona-release tool to enable the tools repository which contains XtraBackup,  Toolkit, the PMM Client and other dependencies.   If you have followed the instructions for Percona Server 8.0 above,  the tools repository is enabled.

Note:  Due to the modularity features built into RHEL 8’s dnf software package manager,  the mysql module must first be disabled before installing Percona Server 8.0.

To install and use with Percona Server 8.0

$ sudo dnf module disable mysql
$ sudo percona-release setup ps80
$ sudo dnf install percona-xtrabackup-80

To install and use with MySQL Community 8.0

$ sudo percona-release enable-only tools
$ sudo dnf install percona-xtrabackup-80

Percona XtraBackup 2.4.x

Percona XtraBackup 2.4.x is deployed into the “original” repository location.  This repository is enabled by default so no repository configuration is required.

$ sudo dnf install percona-xtrabackup-24

Percona XtraDB Cluster 5.7.x

Percona XtraDB Cluster 5.7.x is deployed into the “original” repository location.   This repository is enabled by default so no repository configuration is required.

Note:  Due to the modularity features built into RHEL 8’s dnf software package manager,  the mysql module must first be disabled before installing Percona Server 8.0.

$ sudo dnf module disable mysql
$ sudo percona-release setup pxc57
$ sudo dnf install Percona-XtraDB-Cluster-57

Percona Server for MongoDB 4.0.x

Percona Server for MongoDB 4.0.x is deployed into a separate repository.   Using the percona-release tool, set up the psmdb40 product repositories.

$ sudo percona-release setup psmdb40
$ sudo dnf install percona-server-mongodb

Percona Toolkit 3.0.x

Percona Toolkit 3.0.x is deployed into both the “original” repository location and the new tools location.  This allows Percona Toolkit to be easily used with different products. If you have set up one of the server products above,  Percona Toolkit 3.0.x will be available.

Note: There is a known issue with Percona Toolkit and RHEL8.  Percona Toolkit uses the RHEL8 perl-DBD-MySQL database driver which is linked against the MariaDB C Connector.  The version of this connector that ships with RHEL8 does not support MySQL 8.0 SHA-2 (SHA256) authentication. Therefore, trying to connect to a Percona Server 8.0 or MySQL Community 8.0 instance that has this authentication mode enabled will fail.   The SHA-2 authentication plugin is enabled by default.  However, at the time of this writing,  the MySQL Community 8.0 server that ships with RHEL8 disables this authentication plugin to workaround the incompatibility with the MariaDB C Connector.  Percona Server 8.0 does not disable SHA-2 by default.

$ sudo dnf install percona-toolkit

PMM Client 1.x

PMM Client 1.x  is deployed into both the “original” repository location and the new tools location.  This allows PMM Client to be easily used with different products. If you have enabled one of the server products above,  PMM Client will be available. This “original” repository is enabled by default so no repository configuration is required.    

$ sudo dnf install pmm-client

It was all aboard the Lone Star river boat for the 100 delegates who joined our Community Dinner. Loading took quite some time, Tom Basil attended to the guest list, and then the necessary signatures for those aboard. I’d woken at 6 am to a less than favorable forecast, but the weather was kind to us, with blue sky and a gentle breeze.

My colleague and local Austin resident Rick Vasquez had on Tuesday treated me to my first visit to a US liquor store, Specs. This store was bigger than the largest store in my hometown but sold only alcohol-related products and drink-friendly snacks. An experience in itself! We stuck to the brief of Texas only brew for our guests.

As people boarded they received their swag – PlanetScale and Percona branded steel enamel mugs – and bar and dinner queues formed. The lines moved relatively quickly for the fajita and tex mex themed buffet. Most people seemed satisfied with the fare. Another relief… once we’re out on the water there’s no going back.

Loaded up in all respects, we set sail for a trip on Lady Bird Lake to see the Austin skyline and the hope of seeing the bats at dusk. Everyone seemed to be in great spirits and happy to spend some time out of the air con and in the relatively cool and fresh air of the lake.

Austin from all angles

It was good to see Austin from a new perspective, and of course there was lots of networking and chat. A few photos can be seen here but there are many more on the Facebook album.

The demand for the trip was significantly greater than the capacity of the boat. I’m truly sorry to anyone who could not get a seat because of space limitations. Next year… a bigger boat!

We are pleased to announce the release of PMM 2 Beta!  PMM (Percona Monitoring and Management) is a free and open-source platform for managing and monitoring MySQL, MongoDB, and PostgreSQL performance.

• Query Analytics:
  • MySQL and MongoDB – Slow log, PERFORMANCE_SCHEMA, and Profiler data sources
  • Support for large environments – default view all queries from all instances
  • Filtering – display only the results matching filters such as the schema name or the server instance
  • Sorting and more columns – now sort by any column.
  • Modify Columns – Add one or more columns for any field exposed by the data source
  • Sparklines –  restyled sparkline targeted at making data representation more accurate
• Labels – Prometheus now supports auto-discovered and custom labels
• Inventory Overview Dashboard – Displays the agents, services, and nodes which are registered with PMM Server
• Environment Overview Dashboard – See issues at a glance across multiple servers
• API – View versions and list hosts using the API
• MySQL, MongoDB, and PostgreSQL Metrics – Visualize database metrics over time
• pmm-agent – Provides secure remote management of the exporter processes and data collectors on the client

Query Analytics Dashboard

Query Analytics Dashboard now defaults to display all queries on each of the systems that are configured for MySQL PERFORMANCE_SCHEMA, Slow Log, and MongoDB Profiler, and includes comprehensive filtering capabilities.

Query Analytics Overview

You’ll recognize some of the common elements in PMM 2 Query Analytics such as the Load, Count, and Latency columns. However, there are new elements such as the filter box and more arrows on the columns:

Query Detail

Query Analytics continues to deliver detailed information regarding individual query performance

Filter and Search By

There is a filtering panel on the left, or use the search by bar to set filters using key:value syntax. For example, I’m interested in just the queries related to mysql-sl2 server, I could then type d_server:mysql-sl2:

Sort by any column

This is a much-requested feature from PMM Query Analytics and we’re glad to announce that you can now sort by any column! Just click the small arrow to the right of the column name and:

Sparklines

As you may have already noticed, we have changed the sparkline representation. New sparklines are not points-based lines, but are interval-based, and look like a staircase line with flat values for each of the displayed period:

We also position a single sparkline for only the left-most column and render numeric values for all remaining columns.

Add extra columns

Now you can add a column for each additional field which is exposed by the data source. For example, you can add Rows Examined by clicking the + sign and typing or selecting from the available list of fields:

MySQL Query Analytics Slow Log source

We’ve increased our MySQL support to include both PERFORMANCE_SCHEMA and Slow log – and if you’re using Percona Server with the Extended Slow Log format, you’ll be able to gain deep insight into the performance of individual queries, for example, InnoDB behavior.  Note the difference between the detail available from PERFORMANCE_SCHEMA vs Slow Log:

PERFORMANCE_SCHEMA:

Slow Log:

MongoDB Metrics

Support for MongoDB Metrics included in this release means you can add a local or remote MongoDB instance to PMM 2 and take advantage of the following view of MongoDB performance:

PostgreSQL Metrics

In this release, we’re also including support for PostgreSQL Metrics. We’re launching PMM 2 Beta with just the PostgreSQL Overview dashboard, but we have others under development, so watch for new Dashboards to appear in subsequent releases!

Environment Overview Dashboard

This new dashboard provides a bird’s-eye view, showing a large number of hosts at once. It allows you to easily figure out the hosts which have issues, and move onto other dashboards for a deeper investigation.

The charts presented show the top five hosts by different parameters:

The eye-catching colored hexagons with statistical data show the current values of parameters and allow you to drill-down to a dashboard which has further details on a specific host.

API

We are exposing an API for PMM Server! You can view versions, list hosts, and more…

The API is not guaranteed to work until GA release – so be prepared for some errors during Beta release.

Browse the API using Swagger at /swagger

Installation and configuration

The default PMM Server credentials are:

username: admin
password: admin

Install PMM Server with docker

The easiest way to install PMM Server is to deploy it with Docker. Running the PMM 2 Docker container with PMM Server can be done by the following commands (note the version tag of 2.0.0-beta1):

docker create -v /srv --name pmm-data-2-0-0-beta1 perconalab/pmm-server:2.0.0-beta1 /bin/true
docker run -d -p 80:80 -p 443:443 --volumes-from pmm-data-2-0-0-beta1 --name pmm-server-2.0.0-beta1 --restart always perconalab/pmm-server:2.0.0-beta1

Install PMM Client

Since PMM 2 is still not GA, you’ll need to leverage our experimental release of the Percona repository. You’ll need to download and install the official percona-release package from Percona, and use it to enable the Percona experimental component of the original repository. See percona-release official documentation for further details on this new tool.

Specific instructions for a Debian system are as follows:

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb

Now enable the experimental repo:

sudo percona-release disable all
sudo percona-release enable original experimental

Install pmm2-client package:

apt-get update
apt-get install pmm2-client

Users who have previously installed pmm2-client alpha version should remove the package and install a new one in order to update to beta1.

Please note that leaving experimental repository enabled may affect further package installation operations with bleeding edge software that may not be suitable for Production. You can revert by disabling experimental via the following commands:

sudo percona-release disable original experimental
sudo apt-get update

Configure PMM

Once PMM Client is installed, run the pmm-admin config command with your PMM Server IP address to register your Node:

# pmm-admin config --server-insecure-tls --server-address=<IP Address>:443

You should see the following:

Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.

Adding MySQL Metrics and Query Analytics

MySQL server can be added for the monitoring in its normal way. Here is a command which adds it using the PERFORMANCE_SCHEMA source:

sudo pmm-admin add mysql --use-perfschema --username=pmm --password=pmm

where username and password are credentials for accessing MySQL.

The syntax to add MySQL services (Metrics and Query Analytics) using the Slow Log source is the following:

sudo pmm-admin add mysql --use-slowlog --username=pmm --password=pmm

When the server is added, you can check your MySQL dashboards and Query Analytics in order to view its performance information!

pmm-admin add mongodb --use-profiler --use-exporter  --username=pmm  --password=pmm

pmm-admin add postgresql --username=pmm --password=pmm

About PMM

Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL®, MongoDB®, and PostgreSQL® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL®, MongoDB®, and PostgreSQL® servers to ensure that your data works as efficiently as possible.

Help us improve our software quality by reporting any Percona Monitoring and Management bugs you encounter using our bug tracking system.

It’s that time of year again, and we’ve just had the presentations of the MySQL Community Awards at Percona Live. A more fulsome blog post will be written for the Community Blog in the next few days about the worthy winners. Meanwhile, though, here’s a summary.

Committee Co-Secretaries Emily Slocombe of Square, and Agustin Gallego of Percona presented these annual community nominate awards today, In four categories, the awards went to:

• Community Contributor of the Year: Weixiang Zhai
• Community Contributor of the Year: Valerii Kravchuk

Both of the above awards were made for bug identification and submission. Valerii was represented by Vicentiu Ciorbaru of MariaDB Foundation who accepted the award on his behalf.

• The Application of the Year award was mad to Dbdeployer, for its incredible usefulness to the community of MySQL users. In this case, Giuseppe Maxia – the Data Charmer – could not attend the conference, so Oracle Community Manager LeFred donned a Dbdeployer shirt to accept the award on his behalf.

Last but not least, the Corporate Contributor of the Year was Tencent, and the award received by two representatives pictured here. In this case my journalistic abilities have let me down and I will update this post REALLY soon with the names of those accepting the award, but they are nevertheless pictured here.

Congratulations to all!

From left to right: Emily Slocombe, two representatives from Tencent (tbd), LeFred on behalf of Giuseppe Maxi, Vincentiu on behalf of Valerii, and Weixiang Zhai

ProxySQL 2.0.4, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.4 which fixes many bugs and introduces a number of features and enhancements. The proxysql-admin tool has not changed since the previous release.

The ProxySQL 2.0.4 source and binary packages available from the Percona download page for ProxySQL include ProxySQL Admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.4 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

ProxySQL is available under Open Source license GPLv3.

Percona announces the release of Percona Server for MySQL 5.7.26-29 on May 27, 2019 (downloads are available here and from the Percona Software Repositories). This release merges changes of MySQL 5.7.26, including all the bug fixes in it. Percona Server for MySQL 5.7.26-29 is now the current GA release in the 5.7 series. All of Percona’s software is open-source and free.

New Features:

• New Audit_log_buffer_size_overflow status variable has been implemented to track when an Audit Log Plugin entry was either dropped or written directly to the file due to its size being bigger than audit_log_buffer_size variable.

Bug Fixes:

• TokuDB storage engine would assert on load when used with jemalloc 5.x. Bug fixed #5406.
• a read-write workload on compressed InnoDB tables could cause an assertion error. Bug fixed #3581.
• using TokuDB or MyRocks native partitioning and index_merge access method could lead to a server crash. Bugs fixed #5206, #5562.
• a stack buffer overrun could happen if the redo log encryption with key rotation was enabled. Bug fixed #5305.
• TokuDB and MyRocks native partitioning handler objects were allocated from a wrong memory allocator. Memory was released only on shutdown and concurrent access to global memory allocator caused memory corruptions and therefore crashes. Bugs fixed #5508, #5525.
• enabling redo log encryption resulted in redo log being written unencrypted. Bug fixed #5547.
• if there are multiple row versions in InnoDB, reading one row from PK may have O(N) complexity and reading from secondary keys may have O(N^2) complexity. Bugs fixed #4712, #5450 (upstream #84958).
• setting the log_slow_verbosity to include innodb value and enabling the slow_query_log could lead to a server crash. Bug fixed #4933.
• the page cleaner could sleep for a long time when the system clock was adjusted to an earlier point in time. Bug fixed #5221 (upstream #93708).
• executing SHOW BINLOG EVENT from an invalid position could result in a segmentation fault on 32bit machines. Bug fixed #5243.
• BLOB entries in the binary log could become corrupted in a case when a database with Blackhole tables served as an intermediate binary log server in a replication chain. Bug fixed #5353 (upstream #93917).
• when Audit Log Plugin was enabled, the server could use a lot of memory when handling large queries. Bug fixed #5395.
• XtraDB changed page tracking was missing pages changed by the in-place DDL. Bug fixed #5447.
• innodb_encrypt_tables variable accepted FORCE option only inside quotes as a string. Bug fixed #5538.
• enabling redo log encryption and XtraDB changed page tracking together would result in the error log flooded with decryption errors. Bug fixed #5541.
• system keyring keys initialization wasn’t thread safe. Bugs fixed #5554.
• when using the Docker image, if the root passwords set in the mounted .cnf configuration file and the one specified with MYSQL_ROOT_PASSWORD option are different, password from the MYSQL_ROOT_PASSWORD option will be used. Bug fixed #5573.
• long running ALTER TABLE ADD INDEX could cause a semaphore wait > 600 assertion. Bug fixed #3410 (upstream #82940).

Other bugs fixed: #5537, #5007 (upstream #93164), #5018, #5561, #5570, #5578, #5610, #5441, and #5442.

This release also contains the fixes for the following security issues: CVE-2019-2632, CVE-2019-1559, CVE-2019-2628, CVE-2019-2581, CVE-2019-2683, CVE-2019-2592, CVE-2019-262, and CVE-2019-2614.

Find the release notes for Percona Server for MySQL 5.7.26-29 in our online documentation. Report bugs in the Jira bug tracker.

This blog is an announcement for a Percona Server for MySQL 5.6.44-85.0-1

This is a CRITICAL update and the fix mitigates the issues described in CVE-2019-12301. If you upgraded packages on Debian/Ubuntu to 5.6.44-85.0-1, please upgrade to 5.6.44-85.0-2 or later and reset all MySQL root passwords.

Issue

On 2019-05-18 Percona discovered an issue with the Debian/Ubuntu 5.6.44-85.0-1 packages for Percona Server for MySQL. When the previous versions, upgraded to the new version PS 5.6.44-85.0-1 on deb based systems, the MySQL root password was removed allowing users to login to the upgraded server as MySQL root without specifying a password.

Scope

This issue is limited to users who upgraded with the Debian/Ubuntu package 5.6.44-85.0-1 for Percona Server for MySQL v. 5.6. Newer versions (v. 5.7 and above) and new installations of v. 5.6 (>= 5.6.44-85.0-2) are not affected by this issue.

The 5.6.44-85.0-1 packages were available for 19 hours, starting at 2019-05-17 and removed from the repository upon discovery of the issue. The 5.6.44-85.0-1 packages were replaced in the repository with the 5.6.44-85.0-2 packages on 2019-05-18 at 12:50 pm UTC (see bug #5640).

Although the fixed package no longer removes the MySQL root password,  it cannot restore the previously removed password.

If you downloaded the packages prior to 12:50 pm UTC on 2019-05-18, please update with a newer version and reset all MySQL root passwords on those servers. If you are not sure whether your version is affected, please verify with the script below.

Global Impact

This impacted a small subset of users who are running a very specific version of Linux + specific version of MySQL. For those in this narrow footprint, please take the steps noted in the Remediation section.

Technical Impact

All MySQL root user entries were reset and need to be reset to cure this issue, after applying the fixed 5.6.44 version of 5.6.44-85.0-2 or higher.

Remediation

To cure the vulnerability reported in CVE-2019-12301, upgrade using the Debian/Ubuntu 5.6.44-85.0-2 package (or newer) and reset all MySQL root passwords. To determine if the residual MySQL root password reset issue was resolved, or to verify whether the root password is empty (which may be common), please deploy this script:

https://gist.github.com/Oneiroi/30fd32261ba8ceb699893c9b20f081c2

Contact

If you have any questions about this issue or need to report a security concern, please contact Percona’s Security Team via security@percona.com or www.percona.com/security.