Percona Server for MySQL now ships with a fully open source OpenID Connect (OIDC) authentication plugin, available starting with Percona Server for MySQL 8.4.11-11 and 9.7.2-2 (not yet released as of this writing). It allows a MySQL account to authenticate against any standards-compliant Identity Provider (IdP) instead of relying on a locally stored password, closing the gap with MySQL Enterprise Edition, which has offered OIDC authentication since MySQL 9.1 and, in several respects, going beyond it.

Oracle offers the same category of functionality, but its server-side plugin is part of the paid MySQL Enterprise Edition. Percona’s implementation is open source and adds three capabilities the Enterprise plugin does not provide: automatic signing-key synchronization from a JWKS endpoint, IdP group-to-role mapping, and proxy-user support. This article explains how the plugin works and why those differences matter in practice.

What OpenID Connect Brings to MySQL Authentication

OpenID Connect is an identity layer built on top of the OAuth 2.0 authorization framework [5]. Whereas OAuth 2.0 governs delegated access to resources, OIDC adds a standardized way for a client to establish who a user is. After a user signs in to an Identity Provider, the IdP issues a signed JSON Web Token (JWT), called an ID token, that carries the user’s identity and attributes in a verifiable, tamper-evident form.

Using that model for MySQL authentication brings several practical advantages over password-based accounts:

• Alignment with single sign-on. Users authenticate once with their IdP and can reuse that session context across OIDC-aware applications, including databases. User lifecycle and password management remain centralized.
• No long-lived secrets on the wire. ID tokens are short-lived and cryptographically signed, so there is no static password to steal, rotate, or accidentally commit to a configuration file.
• Support for hybrid deployments. Organizations that run MySQL on-premises while hosting applications in the cloud can still authenticate through the same identity plane on both sides.
• Broad interoperability. Because OpenID Connect is a widely adopted standard, the plugin can work with any compliant provider, including Keycloak, Okta, Microsoft Entra ID, and Google Identity.

None of that is unique to Percona; Oracle makes a similar value proposition for the Enterprise plugin. The real difference lies in how much operational burden the plugin removes from the administrator, which becomes clear in the next sections.

How OpenID Connect Authentication Works

Once the plugin and its configuration are in place, the authentication path is the same regardless of which IdP issued the token:

1. The user authenticates to the IdP and receives a signed ID token.
2. The token is written to a local file that only the client operating system account can read.
3. The MySQL client uses an option that causes the client-side OIDC plugin to load and read the token from the file. The token is sent to the server as part of the authentication handshake.
4. The server validates the secure channel and decodes the token. It then verifies the token signature using the selected IdP’s public key, checks the expiration time, and validates the configured claims.
5. The server resolves the final identity as either a personal account or a group-based proxy target. The plugin may also return roles mapped from the user’s group membership.

Configuring Trusted Providers and Letting the Plugin Manage the Keys

Identity Providers rotate their signing keys periodically as a basic security measure. If a key is ever compromised, limiting its lifetime reduces the potential impact, and regular rotation also lowers the long-term value of any one key as a target. In practice, rotation is gradual: a new key is published and accepted before it starts signing tokens, and an old key remains valid for a period after it stops signing so that tokens already in flight can still be verified.

Public keys are exposed through the standard JWKS (JSON Web Key Set) endpoint, which applications can use to verify tokens issued by the IdP [6].

The Percona OpenID Connect authentication plugin can download public keys from a configured JWKS endpoint when the plugin is loaded, typically during installation and server startup, and store them in a cache. It also provides a User Defined Function (UDF) that can refresh the cache on demand or periodically through the Event Scheduler.

By contrast, Oracle’s counterpart plugin requires signing keys to be configured statically through the authentication_openid_connect_configuration server variable, supplied either as an inline JSON string or as a path to a JSON file. There is no retrieval or refresh from the JWKS endpoint, so keeping keys current after each rotation remains a manual task for the administrator. In the window just after a rotation, tokens signed with the previous key are still valid but cannot be verified until the configuration is updated. Percona’s plugin supports static key configuration as well, but that mode is better suited to testing or temporary setups than to production.

Example

Using the feature requires two simple steps. First, JWKS endpoint URL must be set in the plugin’s configuration. For example, the below configuration defines IdP named as example-keycloak (pay attention to jwks-url element):

{
  "example-keycloak": {
  "issuer-name": "https://keycloak.example.com/realms/master",
  "jwks-url": "https://keycloak.example.com/realms/master/protocol/openid-connect/certs",
  "audiences": [ "mysql-oidc" ]
  }
}

The second step is ensuring the MySQL event scheduler is running and creating an event updating the keys. For example, to enable updating the keys for example-keycloak every hour run from MySQL client:

CREATE EVENT update_oidc_keys
  ON SCHEDULE EVERY 1 HOUR
  DO SELECT update_jwks("example-keycloak");

Benefits of Using IdP Groups

This is where Percona’s plugin diverges most clearly from the Enterprise implementation.

Groups are managed by the corporate Identity Provider and group membership may be carried by ID tokens. OIDC does not define a standard claim for that, but most IdP implementations allow adding a group claim to the tokens. The Percona’s plugin allows the administrator to configure the group claim name so that it matches the token format used by the chosen IdP.

There are two practical ways to take advantage of this feature:  group-to-role mapping and proxy users.

Group-to-Role Mapping

Membership in a group can automatically translate into MySQL roles and therefore privileges across multiple MySQL servers at the same time. On a single server, the flow looks like this:

1. The administrator creates roles and grants them privileges.
2. The administrator defines the IdP group-to-MySQL role mapping in the plugin configuration file.
3. When the user connects, the plugin returns the roles that match the user’s groups, and the server automatically grants those roles to the user.
4. The user can activate any granted role and exercise the privileges assigned to it.

Please note, that group-to-role mapping still requires an account created for each user, but automates managing user privileges.

Example

To create roles and grant them some privileges one may run:

CREATE ROLE accounting;
GRANT ALL PRIVILEGES ON accounting_database.* TO accounting;
CREATE ROLE sales;
GRANT ALL PRIVILEGES ON sales_database.* TO sales;

Then, to to define the mapping add to IDP configuration:

"group-claim": "groups",
"group-role": [
  { "/accounting": "accounting" },
  { "/marketing": "marketing" }
]

Any user connecting with an ID token containing claim “groups”:[“/accounting”] will be granted with role accounting and effectively obtain access to accounting_database and so on.

Proxy Users

The proxy capability in MySQL allows an authentication plugin to request that the connecting external user be logged in as a different MySQL user. In this model, the external identity is the proxy user and the mapped MySQL account is the proxied user. The purpose is to let multiple users share accounts with the same privilege set, avoiding the need to create a separate personal database account for every individual.

This feature must be supported by the authentication plugin, whose job is to choose the proxied user according to the specifics of the authentication method. In the Percona OIDC plugin, that selection is based on the group claim in the token and works as follows:

1. The administrator creates a proxy user identified by the OIDC plugin. This can be either a single anonymous account (”@”) without a specific group name, referred to as anonymous proxying, or multiple group-related accounts, referred to as named group proxying.
2. The administrator creates proxied users for each group. These accounts should not use a login plugin, so nobody can connect to them directly. The username must match the group name.
3. The administrator grants the PROXY privilege for each proxy user on all related proxied users.
4. When a user connects, in the anonymous proxying case the plugin returns the user’s first group as the proxied username. In the named group proxying case, the plugin checks whether the user belongs to the group and returns that group as the proxied username.
5. The server verifies that the requested proxied account exists and that the proxy user has the required PROXY privilege on it. If both checks succeed, the session runs with the proxied account’s privileges.

Other Features

Supported signing algorithms include RSASSA-PKCS1-v1_5, RSASSA-PSS, and ECDSA with SHA-256, SHA-384, and SHA-512 hashing functions.

The Percona approach uses the client-side OpenID Connect plugin from upstream MySQL, which ensures compatibility with the standard Oracle client.

Both client-side and server-side OpenID Connect plugins ensure that the token is sent via a secure channel. Accepted protocols are TCP protected by TLS, Unix sockets, and shared memory.

What OpenID Connect Authentication Does Not Do

There are some limits worth knowing.

The first comes from MySQL’s authentication design: any authentication plugin is used at connection time only. In the case of OIDC, the token is validated when the user connects, and a session that stays open may outlive the ID token that opened it. There is no out-of-the-box mechanism to force re-authentication after some time (except for idle connection timeout).

A similar situation applies to group-role mapping. The roles tied to the user’s groups in the ID token are granted or revoked at connection time. As a result, if a user is added to or removed from an IdP group, they must reconnect to Percona Server for the change to be reflected in their granted roles.

The proxying mechanism uses group membership claim instead of the token’s subject, so any token signed by a configured IdP that carries the required group is accepted. Group membership is your trust boundary in those modes, so treat it that way.

The current proxying implementation assumes the proxied user’s name matches the group name. This can be a problem when a group name isn’t a valid MySQL username (for example, it’s too long or contains disallowed characters), or when multiple groups need to map to a single account. We plan to add group-to-proxied-account mapping in future releases to address this.

The client-side plugin doesn’t verify the ID token (for example, check whether it has expired) before connecting, and the server doesn’t report the reason for access being denied (for security reasons). A good practice is to obtain a fresh token before connecting.

Conclusion

Functionally, Percona’s OpenID Connect plugin covers the same core ground as the counterpart in MySQL Enterprise Edition: signed ID tokens, claim validation, subject matching, and secure-transport enforcement.

It goes further in several important areas:

• It is open source.
• Keys can stay current automatically through JWKS synchronization.
• Group-to-role mapping allows IdP group membership to drive MySQL role grants for the lifetime of the session.
• Proxy-user support allows many IdP identities to share a smaller set of MySQL accounts.

Our OIDC implementation is suitable for real-world identity operations at scale. It can automatically map identities and groups managed by an IdP to database users and roles, and synchronize cryptographic keys.

References

1. Percona Server for MySQL documentation: OpenID Connect authentication.
2. Percona Server for MySQL documentation: Get started with OpenID Connect authentication.
3. MySQL 9.7 Reference Manual: OpenID Connect Pluggable Authentication.
4. MySQL 9.7 Reference Manual: Proxy Users.
5. OpenID Foundation: How OpenID Connect Works
6. auth0 Docs: JSON Web Key Sets.

---

Written by Michal Jankowski. Reviewed by Dennis Kittrell and Oleksiy Lukin.
Percona® is a registered trademark of Percona LLC. MySQL® is a registered trademark of Oracle Corporation.

The post OpenID Connect Authentication for MySQL, Now Fully Open Source appeared first on Percona.

Introduction

A software bill of materials (SBOM) offers end users enhanced supply chain visibility, thereby facilitating license compliance and timely vulnerability detection. An SBOM of an application, library, or framework (collectively referred to as a “component”) is a machine-readable document that enumerates all other components it incorporates, including transitive ones. In this way, an SBOM represents the dependency graph of a particular component. For each component, including the one for which the SBOM is created, it provides the component’s specific version and license.

Given the dependency graph in the figure below, the SBOM for component A would include components B, C, and D. In addition, the SBOM would list each relationship between components (depicted as arrows in the figure) in its “dependencies” section.

Periodic SBOM scans

Given an SBOM file for a component, one can scan it for vulnerabilities with one of the many available tools. If the tool doesn’t show any, it can be tempting to declare that we are safe and forget about the SBOM until the next version of the component in question is released, together with a new SBOM. That, however, would be a mistake.

SBOM scanning tools have two data inputs. The first one is an SBOM itself. It is passed to the tool explicitly and typically doesn’t change over time for a particular component version. The second data input is the complete opposite: it is implicit and updated daily or even more often. We are talking about the vulnerability database(s). SBOM scanning tools download the updates to the vulnerability database(s) before each SBOM scan. The same scan command, using the same SBOM file that previously reported no vulnerabilities, can easily report serious vulnerabilities in an hour.

That is why it is important to run scans periodically to be notified of new vulnerabilities in a timely manner and to start remediation before the component’s maintainers prepare a fix. This is especially true in the era of AI-assisted vulnerability discovery, which often leaves maintainers overloaded with vulnerability reports, so fixing takes longer than before.

SBOMs in Percona Server for MongoDB

Since versions 7.0.39-21, 8.0.28-12, and 8.3.7-1, Percona Server for MongoDB (further referred to as PSMDB) provides SBOMs in its binary packages. In Debian and RPM packages, one can find the SBOM in the /usr/share/doc/percona-server-mongodb-server/sbom.cdx.json file once the percona-server-mongodb-server package is installed. In the binary tarball, the SBOM is located at doc/sbom.cdx.json relative to the tarball’s root directory. As you might have already guessed, the cdx extension in the filename indicates that the SBOM is in the CycloneDX format.

Percona Server for MongoDB also provides two SBOMs for its Docker images. The first one covers PSMDB exclusively and is embedded in the image’s filesystem at the same /usr/share/doc/percona-server-mongodb-server/sbom.cdx.json path. It is the same SBOM as in the corresponding RPM package. The second one covers the image as a whole, including the base OS, libraries and utilities installed on top of it, and so on. This SBOM considers PSMDB as a dependency, and is associated with the Docker image as an OCI artifact.

Since PSMDB’s second SBOM differs little from other OCI-attached SBOMs in how it is scanned, we won’t discuss it here. One can refer to the documentation for detailed instructions and examples. Instead, the rest of the post focuses on the first SBOM: the one that is included in a Debian package, an RPM package, and a binary tarball, and embedded in the Docker image.

Scanning the SBOM

One can scan PSMDB’s SBOM with grype as follows:

grype --distro ubuntu:24.04 sbom:/usr/share/doc/percona-server-mongodb-server/sbom.cdx.json

Clearly, they need to pass the distribution name and version they run PSMDB on, e.g.: —distro rhel:9.8.

One can also use OWASP Dependency Track. Instead of being a command-line utility, Dependency Track is a fully fledged GUI-based service. Nevertheless, deploying it is as easy as two shell commands:

curl -fsSLO https://dependencytrack.org/docker-compose.yml
docker compose up -d

Then, do the following:

• Go to http://localhost:8080 and authenticate with username admin and password admin
• Change the password and reauthenticate with the username admin and the new password
• In the left pane, select “Projects” and then click the “Create Project” button
• After creating the project, click on its name and select the “Component” tab
• Click the “Upload BOM” button and upload the “sbom.cdx.json” file

At the time of writing, Grype and OWASP Dependency Track are the only tools we are aware of that can scan PSMDB’s SBOM. Other popular options, notably Trivy, skip analysis of the most PSMDB dependencies in the SBOM. This is the result of the approach to dependency management that PSMDB has to follow.

Evaluating Scanning Results

Running Grype as shown above on the SBOM from PSMDB version 7.0.39-21 gives quite a scary report (redacted for length):

$ grype --distro ubuntu:24.04 \
    sbom:/usr/share/doc/percona-server-mongodb-server/sbom.cdx.json
NAME                          INSTALLED  VULNERABILITY   SEVERITY  EPSS
unicode-org/ICU4C             57.1       CVE-2016-7415   Critical  5.8% (92nd)
unicode-org/ICU4C             57.1       CVE-2017-14952  Critical  5.1% (91st)
unicode-org/ICU4C             57.1       CVE-2016-6293   Critical  5.0% (91st)
unicode-org/ICU4C             57.1       CVE-2017-17484  Critical  4.6% (90th)
unicode-org/ICU4C             57.1       CVE-2017-7867   High      4.6% (90th)
unicode-org/ICU4C             57.1       CVE-2017-7868   High      4.4% (90th)
unicode-org/ICU4C             57.1       CVE-2020-10531  High      2.7% (84th)
unicode-org/ICU4C             57.1       CVE-2017-15422  Medium    2.5% (82nd)
unicode-org/ICU4C             57.1       CVE-2017-15396  Medium    2.2% (80th)
libtom/LibTomCrypt            1.18.2     CVE-2019-17362  Critical  3.1% (86th)
google.opensource/Protobuf    3.19.5     CVE-2024-7254   High      2.8% (84th)
c-ares/c-ares                 1.19.1     CVE-2024-25629  Medium    0.3% (27th)
pcre2/PCRE2                   10.40      CVE-2022-41409  High      1.1% (63rd)
google.opensource/gRPC (C++)  1.46.6     CVE-2026-33186  Critical  1.6% (72nd)
google.opensource/gRPC (C++)  1.46.6     CVE-2023-44487  High      100.0% (99th)
google.opensource/gRPC (C++)  1.46.6     CVE-2023-4785   High      0.7% (48th)
google.opensource/gRPC (C++)  1.46.6     CVE-2023-33953  High      0.5% (38th)
google.opensource/gRPC (C++)  1.46.6     CVE-2023-32732  Medium    0.5% (41st)
mongodb/mongodb/mongo         7.0.39     CVE-2017-2665   High      0.3% (25th)
mongodb/mongodb/mongo         7.0.39     CVE-2014-8180   Medium    0.3% (19th)
mongodb/MongoDB C Driver      1.27.6     CVE-2026-6231   High      0.2% (8th)
mongodb/MongoDB C Driver      1.27.6     CVE-2025-12119  Low       0.2% (10th)
mongodb/MongoDB C Driver      1.27.6     CVE-2026-4359   Low       0.2% (8th)

The first thing to note about the results above is that PSMDB version 7.0.39-21 does not introduce all those vulnerabilities. They have existed for a long time in the 7.0 version series; the SBOM and the scanning tools only made them visible.

We can start sorting out this pile of vulnerabilities with the critical CVE-2026-33186 in the gRPC framework. If we read its description, we will learn that it affects only the Go implementation of the framework. The C++ implementation used in PSMDB is hosted in a separate repository (https://github.com/grpc/grpc, as opposed to https://github.com/grpc/grpc-go) and is not affected by the vulnerability. But why did Grype report it then? We believe that is a result of the quite wide Common Platform Enumeration (CPE) of this component in the SBOM file:

cpe:2.3:a:grpc:grpc:1.46.6:*:*:*:*:*:*:*

In the CVEs page, CPE is cpe:2.3:a:grpc:grpc:*:*:*:*:*:go:*:* stating that only Go code is vulnerable. So the SBOM’s CPE matched the CVE’s CPE, making Grype report the vulnerability, though it is a false positive in reality.

Another gRPC vulnerability, CVE-2023-44487, does not apply to Percona Server for MongoDB either. It is exploitable only if the support of the alternative gRPC-based wire-protocol transport is enabled during the build process. It is disabled by default, though, and Percona has never enabled it.

Next, let us look at the critical CVE-2016-7415 in ICU4C, which is a C/C++ library for handling Unicode. The CVE correctly points out that version 57.1 is vulnerable to a buffer overflow. However, the PSMDB codebase has local fixes for this and other ICU4C-related CVEs. This is still version 57.1, but with fixes applied.

Finally, consider the critical vulnerability CVE-2019-17362 in LibTomCrypt. Its description says the issue is in the der_decode_utf8_string function in the der_decode_utf8_string.c file. However, Percona Server for MongoDB codebase does not even include the file: only a small part of LibTomCrypt is vendored into the PSMDB codebase, leaving der_decode_utf8_string.c aside. That brings us to the conclusion that the vulnerability does not actually affect Percona Server for MongoDB.

False Positives and Paranoia

By now, you have probably noticed a pattern here. Many of the reported CVEs are false positives, meaning that they are reported but don’t actually affect PSMDB. One may argue that Grype produces too many of them. But there are a couple of reasons for that. First, Grype could not know about local patches for some components, components excluded from the build, or partially vendored components.

Second, if we consider the “paranoia spectrum”, where the left extreme is total naivety and the right one is being suspicious even in innocent cases, it is perfectly reasonable for a security tool to lean to the right end. In our use case, that means reporting false-positive results and leaving the analysis to a human being is a much safer choice than omitting a potential issue that could turn into a serious security breach.

Vulnerability Exploitability Exchange

Repeating the analysis we showed above for each reported CVE on the user side is tedious and sometimes difficult. In theory, a user could realize CVE-2026-33186 (an issue in the Go code) and CVE-2019-17362 (an issue in the code that doesn’t actually exist in PSMDB) are false positives. But it would be unreasonable to expect a user to dig into the internals of Percona Server for MongoDB to discover that some code is excluded from the build (CVE-2023-44487) or has local patches (CVE-2016-7415).

That is why the developers of Percona Server for MongoDB conducted the analysis themselves and prepared its results as a Vulnerability Exploitability eXchange (VEX) document. Each Percona Server for MongoDB release that comes with an SBOM file (versions 7.0.39-21, 8.0.28-12, and 8.3.7-1 and above) also has a corresponding VEX document located at https://percona.github.io/percona-server-mongodb/vex/percona-server-mongodb-<version>.vex.json. Below is an example of downloading the VEX document and using it in Grype to filter out false positives for version 7.0.39-21 (redacted for length):

$ curl -fsSLO https://percona.github.io/percona-server-mongodb/vex/percona-server-mongodb-7.0.39-21.vex.json
$ grype --distro ubuntu:24.04 \
    --vex=percona-server-mongodb-7.0.39-21.vex.json \
    sbom:/usr/share/doc/percona-server-mongodb-server/sbom.cdx.json
NAME                      INSTALLED  VULNERABILITY  SEVERITY  EPSS
mongodb/MongoDB C Driver  1.27.6     CVE-2026-6231  High      0.2% (8th)

We can see that after taking a VEX document into account, only one vulnerability that actually affects PSMDB remains. What is more, a VEX document can list not only those vulnerabilities that don’t affect particular software but also those that actually do. In our case, percona-server-mongodb-7.0.39-21.vex.json says that CVE-2026-6231 would affect PSMDB only if Queryable Encryption (aka Field Level Encryption) is enabled. As a side comment, at Percona, we plan to eliminate that vulnerability in the future.

The dependency on MongoDB Community Edition

An attentive reader could have probably noticed that scanning the SBOM of Percona Server for MongoDB version 7.0.39-21 reported a couple of vulnerabilities in mongodb/mongodb/mongo, also version 7.0.39, which can look strange, but it is actually not. Percona Server for MongoDB is a fork and a drop-in replacement of MongoDB Community Edition. That is the reason every vulnerability that affects the latter also affects the former. Percona Server for MongoDB having a “dependency” on MongoDB Community Edition enables SBOM scanning tools to detect vulnerabilities in the MongoDB Community Edition code itself, in addition to those in its dependencies.

Shared Libraries

Even if an SBOM scanning tool had shown no CVEs after being passed a VEX file, we still can’t conclude that our PSMDB deployment is free from known vulnerabilities. That is because an SBOM file can’t cover dependencies linked to Percona Server for MongoDB at runtime as shared libraries. Their specific versions are governed by a particular OS and can change over time. Even if the PSMDB package for a particular OS version included shared libraries specifying their versions at build time, any upgrade of the packages on the machine where PSMDB is installed could make those versions incorrect.

In addition to periodically scanning the Percona Server for MongoDB SBOM, we recommend that users download and scan the SBOMs for the shared libraries that Percona Server for MongoDB directly links to at runtime. At the time of writing, those libraries are:

Name	Typical Linked Library Filename(s)
GNU C Library	libc.so.6, libm.so.6, libresolv.so.2, etc.
GCC Support Library	libgcc_s.so.1
libcurl	libcurl.so.4
Cyrus SASL	libsasl2.so.2
Kerberos 5 GSS-API	libgssapi_krb5.so.2
OpenLDAP	liblber.so.2, libldap.so.2
OpenSSL	libcrypto.so.3, libssl.so.3

Please note version numbers in library filenames above (e.g. 6 in libc.so.6). They are just examples and can differ from platform to platform.

Conclusion

Percona Server for MongoDB has provided SBOM and VEX documents since versions 7.0.39-21, 8.0.28-12, and 8.3.7-1, giving users better visibility into its supply chain for both licensing and security. Regularly scanning PSMDB’s SBOM, along with the SBOMs of the shared libraries it links to, helps operators detect potential vulnerabilities and begin remediation before they become security breaches.

The post Software Bill of Materials in Percona Server for MongoDB appeared first on Percona.

Our first post showed MySQL 9.7 with one change: mark a table ENGINE=DuckDB and its analytical queries run in DuckDB instead of InnoDB. The question we kept getting after that was about replication. Can you keep a normal InnoDB primary for the writes, and run a replica where the big tables are ENGINE=DuckDB? Then the heavy reports run on a column store, and ordinary MySQL replication keeps it current. No export job. No second database to sync by hand.

So we tried it. The first run failed, and it failed in a way that is easy to miss: the replica took every transaction, reported success, and stored nothing. We tracked down why, fixed it, and the whole test suite passes now. This post is what we tested, how we checked it, the bug we found, and where it stands.

It’s still an experiment, not production software. The code and the test harness are on GitHub under GPLv2: https://github.com/Percona-Lab/ducksdb-mysql-engine.

Why replicate into DuckDB

A DuckDB table on one server is already useful. The analytical queries get fast and the application does not change. But almost nobody runs their reports on the primary – they run them on a replica, so the big scans stay out of the way of the OLTP traffic.

So the shape of it is simple. The primary stays InnoDB and takes the writes. The replica has the same tables, only marked ENGINE=DuckDB. Row-based replication ships the changes across, the replica writes them into the column store, and the reports run there. You get an analytics replica out of the replication you already run.

Row events are engine-agnostic on purpose. The primary logs the row changes, not the SQL, and the replica applies them through the storage-engine API. On paper, then, the replica should not care that one side is InnoDB and the other DuckDB. We wanted to see the paper version hold up on a running server.

The setup

Two containers from the same image, one primary and one replica. It’s all in Docker, so it repeats cleanly.

• Primary: InnoDB, binlog_format=ROW, GTID on.
• Replica: same server, GTID on, tables made with ENGINE=DuckDB.
• Replication uses SOURCE_AUTO_POSITION=1.

One thing you have to get right before any data moves. Create the replica tables as ENGINE=DuckDB yourself. A CREATE TABLE … ENGINE=InnoDB on the primary goes into the binlog with the ENGINE word still in it, and the replica runs it exactly as written, so you would end up with an InnoDB table there, not a DuckDB one. There is no automatic mapping. Pre-create the DuckDB tables on the replica, and let the row changes flow into them.

-- primary (InnoDB)
CREATE TABLE t1 (id BIGINT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=InnoDB;

-- replica (same columns, DuckDB)
CREATE TABLE t1 (id BIGINT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=DuckDB;

The other rule is a primary key on the replica table. UPDATE and DELETE row events find the row by its old image, and the engine needs the key for that. INSERT works without one, but put a key on it anyway.

One script drives all of this: bench/tb/07-replication-spike.sh. It starts both containers, wires up replication, runs every scenario below, and prints PASS or FAIL for each.

What we tested, and how

The part that matters is the checking. Row counts are not enough – the replica can hold the right number of rows and still have the wrong data in them. So after each step the script dumps the whole table on both sides, ordered by primary key, and compares an md5 of the two dumps. One byte off is a FAIL. And rather than sleep between steps, it waits on WAIT_FOR_EXECUTED_GTID_SET(), so the checks do not race the replica.

Here is what went through it.

Basic DML. Insert, update a row, delete a row, compared after each one.

All the column types, in a single wide table: signed and unsigned integers, DECIMAL, DOUBLE, DATE, DATETIME, TIMESTAMP, CHAR, VARCHAR, TEXT, BLOB, a few NULLs, and a unicode string. Insert it, update it, compare byte for byte. Blobs get their own note below.

DDL. ALTER TABLE ADD COLUMN, ALTER TABLE ADD INDEX, and DROP TABLE against a DuckDB replica table. These arrive as statements. We check that the column shows up, the index shows up, the old rows survive, and the drop removes the table.

Transactions. A transaction with two inserts and an update has to land on the replica as one unit. A transaction the primary rolls back has to leave nothing behind. We also open a transaction straight on the replica and both roll it back and commit it, to check the engine’s own commit and rollback.

Bulk load. 5000 rows through LOAD DATA on the primary, has to arrive and match.

Durability. Two cases, and the second is the hard one.

• Clean restart. Stop the replica properly, write on the primary while it is down, start it again, and see it pick up from its GTID position.

• Crash. Apply some rows, then SIGKILL the replica. No clean shutdown, no checkpoint. Bring it back, write more on the primary, and check one exact thing: every row present once. Nothing lost – DuckDB has to replay its write-ahead log when it opens the file – and nothing applied twice, which means the saved position has to line up with the data that actually reached disk.

The bug: multi-engine transactions lost data

The first full run fell down on the wide-table test. Zero rows on the replica, and then everything after it failed too. The applier had stopped with HA_ERR_KEY_NOT_FOUND. It went to UPDATE a row that was not there, because the INSERT before it had returned success and written nothing.

When a scenario fails, the harness saves the applier error, both server logs, and both schemas. The replica log had the line that mattered:

[Warning] Combining the storage engines InnoDB and DuckDB is deprecated, but the
statement or transaction updates both the InnoDB table mysql.slave_worker_info and the
DuckDB table rpl.wide.

That line is the whole thing. A replica does not only write your data. In the same transaction it also writes its own position into InnoDB system tables – mysql.slave_worker_info, the relay-log info, gtid_executed. So every applied transaction touches two engines at once: InnoDB for the position, DuckDB for the data. Two engines means MySQL runs a real two-phase commit: prepare, then commit.

Our prepare was wrong. It took the open DuckDB transaction, moved it into a registry meant for external XA COMMIT, and cleared the per-connection state. Then commit looked at that state, found it empty, and committed nothing. The position went into InnoDB, the GTID advanced, the binlog moved on, and the DuckDB rows were thrown away. No error anywhere. The replica looked healthy while it dropped every write.

We cut it down to the smallest case, with no replication at all. One server, one transaction into a DuckDB table and an InnoDB table:

BEGIN;
INSERT INTO duck VALUES (1,10),(2,20),(3,30);   -- DuckDB
INSERT INTO inno VALUES (1,10),(2,20),(3,30);   -- InnoDB
COMMIT;
-- duck: 0 rows   inno: 3 rows

InnoDB kept its three rows, DuckDB kept none, and COMMIT said it was fine. A DuckDB-only transaction was fine as well, because with one engine MySQL skips the prepare step. It only broke with a second engine in the transaction. And on a replica, that is every transaction.

The fix

Small change, in the engine’s transaction code. prepare now remembers which prepared transaction belongs to the connection, and commit finishes that one instead of an empty state. External XA is untouched. It went out as v0.2.3.

With that in place the reproducer keeps three rows in both tables, and the full run comes back clean, crash test included:

[8]  data integrity: all column types, NULL / unicode / negatives ....... PASS
[9]  DDL replication (ALTER ADD COLUMN / ADD INDEX / DROP) .............. PASS
[10] transactions (atomic commit, rollback, engine commit/rollback) ..... PASS
[11] bulk LOAD DATA on master -> replica ................................ PASS
[12] durability: graceful restart, then SIGKILL crash recovery .......... PASS

VERDICT: PASS=24  FAIL=0

The crash case is the important one. After a SIGKILL in the middle of applying, the replica came back with every committed row exactly once, matching the primary. Committed transactions survive the kill, and the position stays in step with them.

We left two tests behind so this cannot slip back in quietly: an MTR test, txn_mixed_engine, that runs a mixed DuckDB+InnoDB transaction on every build, and scripts/repro-2pc-dataloss.sh, which you can point at any published image to check it.

What works, and what doesn’t yet

Where it stands on v0.2.3, for an InnoDB primary feeding a DuckDB replica:

Scenario	Result
INSERT / UPDATE / DELETE	works, content matches
All column types (numeric, temporal, string, BLOB, NULL, unicode)	works
ALTER ADD COLUMN / ADD INDEX, DROP TABLE	works
Transaction commit / rollback	works, atomic
Bulk LOAD DATA	works
Graceful restart, resume from GTID	works
SIGKILL crash, no loss / no duplicates	works

The things to keep in mind:

• Create the replica tables as ENGINE=DuckDB yourself. A replicated CREATE TABLE keeps the primary’s engine, so it will not turn into DuckDB on its own.

• Replica tables need a primary key for UPDATE and DELETE.

• The applier goes row by row. That is fine for a normal OLTP change stream. It is not fine for keeping up with a primary that bulk-loads at full speed – the replica will fall behind.

• Committed transactions are crash-safe, with one small gap. The engine holds a prepared-but-not-committed transaction in memory only, so a crash in the short window between prepare and commit can lose that single transaction. The applier commits right away, so the window is small, but it is not zero.

• Blobs behave differently over replication than through a direct statement. A plain UPDATE of a BLOB or TEXT column has a known limit in the engine and does not apply. Over replication it does apply, because the row event carries a full before-and-after image instead of the shared buffer the direct path uses.

And the obvious one. This is an experiment. It is a functional result from a test harness on small data, not an HA or failover benchmark. We did not test multi-source replication, filters, or a real write rate.

Where it stands

An InnoDB primary feeding a DuckDB replica works on v0.2.3. Inserts, updates, deletes, every common type, schema changes, transactions, bulk load – they all replicate and match, and it comes back clean from both a graceful restart and a hard kill. The one real bug, silent data loss on every replicated transaction, is found, understood, fixed, and covered by tests.

It is not production-ready, and we do not treat it as such. But the idea holds up. Point normal MySQL replication at a DuckDB replica, and you get an analytics copy that keeps itself in sync.

The post Replicating from InnoDB into a DuckDB storage engine appeared first on Percona.

TL;DR: Starting with PBM 2.15.0 and PCSM 0.9.0, every release artifact – binary tarballs, RPM and DEB packages, and Docker images – ships a CycloneDX 1.6 Software Bill of Materials in JSON. Scan it with Trivy, Grype, or any CycloneDX-compatible tool. For Docker images, the fastest path is a Trivy image –sbom-sources oci <image>. There is nothing to enable, as the SBOM is already a part of the artifact you were going to download.

If you run Percona Backup for MongoDB (PBM) or Percona ClusterSync for MongoDB (PCSM), both part of the Percona Software for MongoDB family, you can now answer one of the stressful questions in operations: “Is my database tooling affected by this CVE?” in seconds instead of days. And when a customer or auditor asks for a parts list of what you deployed, you can hand it over as a single file instead of starting a multi-day investigation.

That parts list is a Software Bill of Materials (SBOM), and starting with PBM 2.15.0 and PCSM 0.9.0, it comes along in every channel we publish – tarballs, RPM and DEB packages, and Docker images. This post shows what is in it, where to find it, and three ways to scan it.

The problem: you can’t patch what you can’t see

Most MongoDB operators run database tooling assembled by someone else – official RPMs, official containers, official tarballs. When something goes wrong upstream, the first question is always the same: what is actually inside this artifact, and is any of it affected?

Two incidents made that question a board-level concern, and a third made it personal for MongoDB teams. Log4Shell in late 2021 forced thousands of teams to manually audit Java dependencies they did not know they had. The xz-utils backdoor disclosed in early 2024 hid in a compression library buried deep inside Linux base images. Then, in December 2025, MongoBleed (CVE-2025-14847) forced MongoDB teams to urgently identify which server and tooling versions were actually deployed. In every case, teams that already had an SBOM per artifact answered “are we affected?” in minutes; teams that did not took days.

The other version of the problem is that the SBOM is something someone else asks of you – a customer running a security review, an auditor checking EU Cyber Resilience Act or US Executive Order 14028 compliance, or a procurement team working through a vendor questionnaire. Without one, every such request turns into a discovery project.

What Percona Backup for MongoDB and ClusterSync now ship

An SBOM is a machine-readable inventory of every component, library, and OS package inside a built artifact, with versions, licenses, and dependency relationships. Think of it as a packing slip for a software shipment, except a computer can parse it, search it, and cross-reference it against vulnerability databases.

Plenty of vendors now ship SBOMs. What matters is whether the SBOM is actually usable, and PBM and PCSM cover the three things that decide that. The format is CycloneDX 1.6, an OWASP-backed JSON standard that every major tool reads: Trivy, Grype, Snyk, and Dependency-Track. The files are generated with Syft from the actual built binary and the staged file tree, so the inventory reflects what was really packaged, not what the source tree implies. And the SBOM ships in every distribution channel, not just one:

Distribution channel	SBOM location
Binary tarball	<product>-<version>.cdx.json at the root of the archive
RPM package	/usr/share/doc/<product>/<product>-<version>.cdx.json
DEB package	/usr/share/doc/<product>/<product>-<version>.cdx.json
Docker image	Two SBOMs ship side by side — see below

Docker images carry two SBOMs

Docker images get special treatment because they bundle two different things: our binary on top of a base operating system. Each PBM and PCSM image, therefore, carries two SBOMs with overlapping scopes.

The embedded SBOM is stored in the image filesystem (the same file the RPM installs) and describes our binary and its Go modules. This is the SBOM to use in offline or air-gapped clusters where you cannot reach a registry.

The OCI-attached SBOM lives next to the image in the registry as an OCI 1.1 referrer artifact and describes the full image, including the base OS packages. This is the canonical SBOM for a Docker image, and the easiest to fetch programmatically.

SBOM	Scope / how you reach it
Embedded (installed by the RPM)	Our package only: Go modules of the binary; inside the image filesystem
OCI-attached (registry-side)	Full image: our package + UBI9 OS packages; via the OCI Referrers API

 

How the SBOM is generated across PBM and PCSM artifacts, and why a Docker image carries two.

Three ways to scan your SBOM

The examples below use Trivy because it is the most common choice in Percona QA pipelines, but Grype, Snyk, and any other CycloneDX-compatible scanner accept the same .cdx.json files unchanged. By default, these commands report everything the scanner finds. In a CI pipeline, you would want to narrow down the output by leaving only –severity HIGH, CRITICAL to focus on the most serious findings, and –ignore-unfixed to skip CVEs that have no upstream fix yet.

• From a downloaded package or tarball

Once the artifact is on disk, the SBOM is just a file in a predictable location. Point Trivy at it:

trivy sbom --severity HIGH,CRITICAL,MEDIUM,LOW \
    /usr/share/doc/percona-clustersync-mongodb/percona-clustersync-mongodb-0.9.0.cdx.json

The same command works against the SBOM extracted from a tarball or installed by an RPM or DEB package – only the file path changes. For Oracle Linux, replace with the exact compatible RHEL minor, e.g., --distro redhat/9.8, since Trivy does not recognize the ol OS family on its own, but the RHEL vulnerability database is binary-compatible.

• From a Docker image, without pulling it

For Docker images, Trivy can fetch the OCI-attached SBOM straight from the registry. No image pull, no extraction step:

trivy image --severity HIGH,CRITICAL,MEDIUM,LOW --sbom-sources oci \
    docker.io/percona/percona-clustersync-mongodb:0.9.0

Trivy resolves the multi-arch index, picks your platform-specific child digest, asks the registry for any attached SBOMs via the OCI Referrers API, and scans whichever one it finds. A typical run looks like this:

Report Summary:

Target	Type	Vulnerabilities
docker.io/percona/percona-clustersync-mongodb:0.9.0 (redhat 9.8)	redhat	0
usr/bin/pcsm	gobinary	8

Two targets, two scopes: the base OS layer (no findings) and the PCSM binary (eight Go standard-library CVEs, all flagged because the image was built against a Go release that has since been superseded). The same –sbom-sources oci flag works for PBM images, and against both the public percona/ images on Docker Hub and the engineering perconalab/ images.

• By hand, with ORAS

If you would rather inspect the OCI artifact directly, to confirm what is attached, or pull the SBOM to disk, the ORAS CLI does that without a scanner in the loop:

oras discover --format tree \
    docker.io/percona/percona-clustersync-mongodb:0.9.0-amd64

The per-arch tag (:<version>-<arch>) resolves straight to the image manifest that the SBOM is attached to. From there, oras pull <digest> writes the .cdx.json to disk for archival or for feeding into a scanner of your choice.

What changes for you

For operators, the upgrade story gets simpler. When a new CVE is disclosed, you no longer have to guess whether your PBM or PCSM deployment is affected; you scan the SBOM that shipped with the exact version you are running and get a yes/no answer in seconds. The SBOM is also a stable input for the dashboards you already run (Dependency-Track, DefectDojo, your SIEM), so PBM and PCSM are no longer blind spots in the inventory.

For security and platform teams, the OCI-attached SBOM means you can pre-screen container images in CI before they ever reach a cluster. Pull-request gates that already scan first-party images for HIGH/CRITICAL findings can pick up PBM and PCSM with no extra glue code: –sbom-sources oci is enough.

 

Try it on your own deployment

SBOMs ship starting with PBM 2.15.0 and PCSM 0.9.0 across every channel we publish: tarballs, RPM, DEB, and Docker. There is nothing to switch on: download the artifact you would have downloaded, and the SBOM is already there.Full details, including the ORAS walkthrough and per-channel paths, are in the PBM and PCSM documentation (PBM, PCSM).&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;/p>

Run a scanner over your Percona Backup for MongoDB or Percona ClusterSync for MongoDB deployment using one of the commands above. If the result surprises you in any direction, if you have a finding you would like us to address, or if a workflow did not behave as our docs imply, we would like to hear about it, and the SBOM lets you point us to the exact component and version rather than a hunch. Open an issue in the PBM or PCSM Jira project, or stop by the Percona Community Forum.

One last thing: an SBOM tells you what is inside the artifact, not what we have determined about each finding. That second part is coming. Alongside the per-artifact SBOMs described here, we also plan to publish source SBOMs in the repository, and a follow-up in this series will cover the VEX (Vulnerability Exploitability eXchange) documents that record, for each finding, whether it is fixed, not exploitable in our build, or still under investigation. Watch for both in future re

leases.

The post Alert on CVEs in Your Percona Tools for MongoDB on Day One appeared first on Percona.

TL;DR: Starting with PBM 2.15.0 and PCSM 0.9.0, every release artifact – binary tarballs, RPM and DEB packages, and Docker images – ships a CycloneDX 1.6 Software Bill of Materials in JSON. Scan it with Trivy, Grype, or any CycloneDX-compatible tool. For Docker images, the fastest path is a Trivy image –sbom-sources oci <image>. There is nothing to enable, as the SBOM is already a part of the artifact you were going to download.

If you run Percona Backup for MongoDB (PBM) or Percona ClusterSync for MongoDB (PCSM), both part of the Percona Software for MongoDB family, you can now answer one of the stressful questions in operations: “Is my database tooling affected by this CVE?” in seconds instead of days. And when a customer or auditor asks for a parts list of what you deployed, you can hand it over as a single file instead of starting a multi-day investigation.

That parts list is a Software Bill of Materials (SBOM), and starting with PBM 2.15.0 and PCSM 0.9.0, it comes along in every channel we publish – tarballs, RPM and DEB packages, and Docker images. This post shows what is in it, where to find it, and three ways to scan it.

The problem: you can’t patch what you can’t see

Most MongoDB operators run database tooling assembled by someone else – official RPMs, official containers, official tarballs. When something goes wrong upstream, the first question is always the same: what is actually inside this artifact, and is any of it affected?

Two incidents made that question a board-level concern, and a third made it personal for MongoDB teams. Log4Shell in late 2021 forced thousands of teams to manually audit Java dependencies they did not know they had. The xz-utils backdoor disclosed in early 2024 hid in a compression library buried deep inside Linux base images. Then, in December 2025, MongoBleed (CVE-2025-14847) forced MongoDB teams to urgently identify which server and tooling versions were actually deployed. In every case, teams that already had an SBOM per artifact answered “are we affected?” in minutes; teams that did not took days.

The other version of the problem is that the SBOM is something someone else asks of you – a customer running a security review, an auditor checking EU Cyber Resilience Act or US Executive Order 14028 compliance, or a procurement team working through a vendor questionnaire. Without one, every such request turns into a discovery project.

What Percona Backup for MongoDB and ClusterSync now ship

An SBOM is a machine-readable inventory of every component, library, and OS package inside a built artifact, with versions, licenses, and dependency relationships. Think of it as a packing slip for a software shipment, except a computer can parse it, search it, and cross-reference it against vulnerability databases.

Plenty of vendors now ship SBOMs. What matters is whether the SBOM is actually usable, and PBM and PCSM cover the three things that decide that. The format is CycloneDX 1.6, an OWASP-backed JSON standard that every major tool reads: Trivy, Grype, Snyk, and Dependency-Track. The files are generated with Syft from the actual built binary and the staged file tree, so the inventory reflects what was really packaged, not what the source tree implies. And the SBOM ships in every distribution channel, not just one:

Distribution channel	SBOM location
Binary tarball	<product>-<version>.cdx.json at the root of the archive
RPM package	/usr/share/doc/<product>/<product>-<version>.cdx.json
DEB package	/usr/share/doc/<product>/<product>-<version>.cdx.json
Docker image	Two SBOMs ship side by side — see below

Docker images carry two SBOMs

Docker images get special treatment because they bundle two different things: our binary on top of a base operating system. Each PBM and PCSM image, therefore, carries two SBOMs with overlapping scopes.

The embedded SBOM is stored in the image filesystem (the same file the RPM installs) and describes our binary and its Go modules. This is the SBOM to use in offline or air-gapped clusters where you cannot reach a registry.

The OCI-attached SBOM lives next to the image in the registry as an OCI 1.1 referrer artifact and describes the full image, including the base OS packages. This is the canonical SBOM for a Docker image, and the easiest to fetch programmatically.

SBOM	Scope / how you reach it
Embedded (installed by the RPM)	Our package only: Go modules of the binary; inside the image filesystem
OCI-attached (registry-side)	Full image: our package + UBI9 OS packages; via the OCI Referrers API

 

How the SBOM is generated across PBM and PCSM artifacts, and why a Docker image carries two.

Three ways to scan your SBOM

The examples below use Trivy because it is the most common choice in Percona QA pipelines, but Grype, Snyk, and any other CycloneDX-compatible scanner accept the same .cdx.json files unchanged. By default, these commands report everything the scanner finds. In a CI pipeline, you would want to narrow down the output by leaving only –severity HIGH, CRITICAL to focus on the most serious findings, and –ignore-unfixed to skip CVEs that have no upstream fix yet.

• From a downloaded package or tarball

Once the artifact is on disk, the SBOM is just a file in a predictable location. Point Trivy at it:

trivy sbom --severity HIGH,CRITICAL,MEDIUM,LOW \
    /usr/share/doc/percona-clustersync-mongodb/percona-clustersync-mongodb-0.9.0.cdx.json

The same command works against the SBOM extracted from a tarball or installed by an RPM or DEB package – only the file path changes. For Oracle Linux, replace with the exact compatible RHEL minor, e.g., --distro redhat/9.8, since Trivy does not recognize the ol OS family on its own, but the RHEL vulnerability database is binary-compatible.

• From a Docker image, without pulling it

For Docker images, Trivy can fetch the OCI-attached SBOM straight from the registry. No image pull, no extraction step:

trivy image --severity HIGH,CRITICAL,MEDIUM,LOW --sbom-sources oci \
    docker.io/percona/percona-clustersync-mongodb:0.9.0

Trivy resolves the multi-arch index, picks your platform-specific child digest, asks the registry for any attached SBOMs via the OCI Referrers API, and scans whichever one it finds. A typical run looks like this:

Report Summary:

Target	Type	Vulnerabilities
docker.io/percona/percona-clustersync-mongodb:0.9.0 (redhat 9.8)	redhat	0
usr/bin/pcsm	gobinary	8

Two targets, two scopes: the base OS layer (no findings) and the PCSM binary (eight Go standard-library CVEs, all flagged because the image was built against a Go release that has since been superseded). The same –sbom-sources oci flag works for PBM images, and against both the public percona/ images on Docker Hub and the engineering perconalab/ images.

• By hand, with ORAS

If you would rather inspect the OCI artifact directly, to confirm what is attached, or pull the SBOM to disk, the ORAS CLI does that without a scanner in the loop:

oras discover --format tree \
    docker.io/percona/percona-clustersync-mongodb:0.9.0-amd64

The per-arch tag (:<version>-<arch>) resolves straight to the image manifest that the SBOM is attached to. From there, oras pull <digest> writes the .cdx.json to disk for archival or for feeding into a scanner of your choice.

What changes for you

For operators, the upgrade story gets simpler. When a new CVE is disclosed, you no longer have to guess whether your PBM or PCSM deployment is affected; you scan the SBOM that shipped with the exact version you are running and get a yes/no answer in seconds. The SBOM is also a stable input for the dashboards you already run (Dependency-Track, DefectDojo, your SIEM), so PBM and PCSM are no longer blind spots in the inventory.

For security and platform teams, the OCI-attached SBOM means you can pre-screen container images in CI before they ever reach a cluster. Pull-request gates that already scan first-party images for HIGH/CRITICAL findings can pick up PBM and PCSM with no extra glue code: –sbom-sources oci is enough.

 

Try it on your own deployment

SBOMs ship starting with PBM 2.15.0 and PCSM 0.9.0 across every channel we publish: tarballs, RPM, DEB, and Docker. There is nothing to switch on: download the artifact you would have downloaded, and the SBOM is already there.Full details, including the ORAS walkthrough and per-channel paths, are in the PBM and PCSM documentation (PBM, PCSM).&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;/p>

Run a scanner over your Percona Backup for MongoDB or Percona ClusterSync for MongoDB deployment using one of the commands above. If the result surprises you in any direction, if you have a finding you would like us to address, or if a workflow did not behave as our docs imply, we would like to hear about it, and the SBOM lets you point us to the exact component and version rather than a hunch. Open an issue in the PBM or PCSM Jira project, or stop by the Percona Community Forum.

One last thing: an SBOM tells you what is inside the artifact, not what we have determined about each finding. That second part is coming. Alongside the per-artifact SBOMs described here, we also plan to publish source SBOMs in the repository, and a follow-up in this series will cover the VEX (Vulnerability Exploitability eXchange) documents that record, for each finding, whether it is fixed, not exploitable in our build, or still under investigation. Watch for both in future re

leases.

The post Alert on CVEs in Your Percona Tools for MongoDB on Day One appeared first on Percona.

Upstream MySQL published an out-of-schedule release this week with two high-severity CVE fixes. If you’re running Percona Server for MySQL 5.7 or 8.0 under Extended Lifecycle Support (ELS), the program we previously called Post EOL Support, you don’t have to do anything to qualify for them. We’ve already applied the fixes and re-released the affected ELS builds.

This is the point of ELS. When a major version reaches End of Life (EOL), the community stops shipping patches, but the databases running on it don’t stop mattering. ELS keeps critical bug and security fixes coming for versions that are past their EOL date, so you can stay on 5.7 or 8.0 on your own timeline instead of a deadline someone else set.

What we did

These CVE fixes landed upstream outside the normal cadence. Under ELS, customers are entitled to security fixes for the versions they run, so we pulled the patches into the 5.7 and 8.0 builds and re-released them. ELS customers will get access to the updated builds from the usual private repository in the next couple of weeks.

Why this matters if you’re still on 5.7 or 8.0

Percona Server for MySQL 5.7 reached EOL in October 2023. Percona Server for MySQL 8.0 reached EOL in April 2026. Plenty of production systems are still on both, and not every migration can happen on the upstream’s schedule. Running an unpatched database past EOL is where the real risk sits: no security fixes, no bug fixes, and no support when something breaks at 2:00 a.m.

ELS closes that gap. You keep getting the critical fixes, including out-of-schedule security patches like these, while you plan an upgrade on terms that work for your team.

Where to go from here

If you’re on 5.7 or 8.0 and don’t have ELS in place, now is a good time to look at it. The fixes we just shipped are exactly what the program is for. See the details for your version: Extended Lifecycle Support for MySQL 8.0 or Extended Lifecycle Support for MySQL 5.7. Or reach out via percona.com or the Percona Community Forum to discuss coverage for your environment.

 

---

Written by @Dennis Kittrell – Reviewed by @Matthew Boehm & @Varun Nagaraju

The post Still on MySQL 5.7 or 8.0? Those high-severity CVE fixes are covered appeared first on Percona.

Managing data retention policies is one of the most common operational tasks in MySQL.

Applications continuously generate transactional, audit, logging, telemetry, and event data. Over time, these tables can grow to billions of rows, causing:

• Larger backups
• Longer recovery times
• Reduced buffer pool efficiency
• Slower index maintenance
• Increased storage costs
• Degraded query performance

To address these problems, organizations typically implement retention policies based on dates or timestamps. Examples include deleting events older than 90 days or purging session data older than 30 days and so forth. The deleted data can then eventually be archived somewhere else, like in another DBMS or on external files.

One of the most widely used tools for implementing these policies in MySQL ecosystems is pt-archiver, part of the Percona Toolkit.

This article provides a review of what pt-archiver is and how to use it, but in particular it focuses on the fact this tool is not partitioning aware, and this can make the deletion phase more costly. The article shows how to extend pt-archiver with a Perl plugin to make it aware of partitioning.

 

What is pt-archiver?

pt-archiver is a command-line utility from Percona Toolkit designed to:

• Archive rows from MySQL tables
• Purge rows from MySQL tables
• Move data between tables into the local database or a remote one
• Export rows into files

In a few words: implementing retention policies safely.

The tool processes rows incrementally in chunks, avoiding massive transactions and reducing impact on production systems.

Example:

pt-archiver \
  --source h=localhost,D=mydb,t=events \
  --where "created_at < '2026-05-01'" \
  --purge \
  --limit 1000 \
  --commit-each

This command:

• Scans rows matching the WHERE condition
• Processes them in chunks of 1000 rows
• Commits every chunk
• Deletes matching rows from the source table

pt-archiver provides several advantages compared to ad-hoc DELETE statements.

Instead of running:

DELETE FROM events
WHERE created_at < '2026-05-01';

which may:

• Lock rows for a long time
• Generate massive undo/redo logs
• Create replication lag
• Exhaust transaction logs

pt-archiver processes rows incrementally to make the process overhead less impactful for the database performance.

pt-archiver implementation permits flexible archival strategies

Rows can be copied to another table on a remote host, exported to files or removed completely

More details: ps://docs.percona.com/percona-toolkit/pt-archiver.html

Example: Copy rows to a remote archive table

The following example archives rows older than 90 days from a local table into an archive table hosted on a remote MySQL server:

pt-archiver \
  --source h=localhost,D=sales,t=orders,u=archiver,p=secret \
  --dest h=archive-server,D=archive,t=orders_archive,u=archiver,p=secret \
  --where "created_at < '2026-05-01'" \
  --limit 1000 \
  --commit-each \
  --progress 10000 \
  --statistics

In this example:

• –source defines the source table
• –dest defines the remote archive destination
• –where selects rows eligible for archival
• –limit controls batch size
• –commit-each commits every batch independently to reduce transaction overhead

–-progress reports progress every 10,000 rows

If rows should be removed from the source table after being copied, add –purge

Example: Export rows to a file

The following example exports rows older than one year into a text file:

pt-archiver \
  --source h=localhost,D=sales,t=orders,u=archiver,p=secret \
  --where "created_at < NOW() - INTERVAL 1 YEAR" \
  --file '/tmp/orders_archive_%Y-%m-%d.txt' \
  --output-format csv \
  --limit 1000 \
  --commit-each \
  --progress 10000 \
  --statistics

In this example:

• –file specifies the output file
• –-output-format csv exports rows in CSV format
• Date placeholders in the filename are expanded automatically

Rows can optionally be deleted from the source table by adding –purge

This allows pt-archiver to be used both for data retention and for offline archival workflows.

The Hidden Cost of DELETE Statements

Although pt-archiver is much safer than massive DELETE operations, it still fundamentally relies on DELETE statements.

This is a critical point.

Even when there are proper indexes, the rows are processed in chunks, and transactions are small; the large-scale DELETE operations remain expensive.

Deleting rows is expensive in InnoDB because it involves:

• Locating rows via indexes
• Modifying clustered indexes
• Modifying secondary indexes
• Generating undo logs
• Generating redo logs
• Purge thread processing
• Replication event generation
• Page fragmentation

When deleting billions of rows, the overhead becomes enormous.

Indexes help for sure, but only partially.

Consider:

DELETE FROM events
WHERE created_at < '2024-01-01';

If created_at is indexed, MySQL can efficiently locate rows.

However, locating rows efficiently is only part of the cost. The actual delete operations still require all those things we mentioned above.

At considerable scale, this becomes expensive.

Why RANGE Partitioning is Superior for Retention Policies

For time-based retention policies, partitioning is often dramatically more efficient. In particular, RANGE partitioning is very useful for these cases.

Example:

CREATE TABLE events (
    id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    payload JSON,
    PRIMARY KEY(id, created_at)
)

PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
    PARTITION p202605 VALUES LESS THAN (TO_DAYS('2026-06-01')),
    PARTITION p202606 VALUES LESS THAN (TO_DAYS('2026-07-01'))
);

With partitioning, dropping old data becomes:

ALTER TABLE events DROP PARTITION p202604;

This operation is dramatically faster than running a DELETE.

Dropping a partition:

• Removes an entire physical partition
• Avoids row-by-row DELETE
• Avoids undo generation for each row
• Avoids secondary index maintenance per row
• Minimizes redo generation
• Is nearly metadata-only

This can remove millions or billions of rows in a matter of seconds without the same large cost of DELETE.

The Problem: pt-archiver is Not Partition-Aware

Unfortunately, pt-archiver does not automatically understand partitioning strategies.

Even if the table is partitioned or the retention policy perfectly matches partition boundaries, pt-archiver still executes DELETE statements.

Example:

pt-archiver \
  --where "created_at < NOW() - INTERVAL 90 DAY" \
  --purge

Internally, this still produces DELETE … instead of ALTER TABLE … DROP PARTITION …

This means organizations may lose the major operational benefits of partitioning, or they need to implement custom scripts for managing the selection of rows to copy using pt-archiver and then use DROP PARTITION separately from the tool. That is doable, and to be honest, not too complicated, but why not make pt-archiver aware of partitioning for some specific use cases?

Extending pt-archiver with Pulg-ins

Fortunately, pt-archiver supports Perl plug-ins.

A plug-in can do plenty of things. Like: inspect runtime conditions, interact with MySQL, override behaviors, and execute custom logic

This gives us an opportunity to implement partition-aware retention handling.

The plug-in can:

1. Inspect partition definitions
2. Analyze the WHERE condition
3. Determine which partitions are fully expired
4. Execute ALTER TABLE DROP PARTITION
5. Prevent row-by-row DELETE processing

This approach combines the scheduling/orchestration power of pt-archiver with the efficiency of partition pruning.

Plug-in Design

Our plug-in will:

• Connect using the pt-archiver DB handle
• Inspect INFORMATION_SCHEMA.PARTITIONS
• Identify partitions older than the retention cutoff
• Issue DROP PARTITION statements
• Log actions
• Skip DELETE processing

Assumptions:

• The table is RANGE partitioned
• Partitions are DATETIME based using the TO_DAYS() function to define ranges
• Partition naming convention contains dates
• Retention policy aligns with partition boundaries; if the plugin cannot determine a specific boundary, pt-archiver does nothing

Full Perl Plug-in for pt-archiver

package pt_archiver_partition_drop;

use strict;
use warnings;

sub new {
    my ($class, %args) = @_;
    my $self = {
        dbh        => $args{dbh},
        db         => $args{db},
        tbl        => $args{tbl},
        statistics => {},
    };

    bless $self, $class;
    return $self;
}

sub statistics {
    my ($self) = @_;
    return $self->{statistics};
}


sub before_begin {
    my ($self) = @_;
    my $dbh = $self->{dbh} or die "Missing dbh from pt-archiver\n";
    my $db  = $self->{db}  or die "Missing db from pt-archiver plugin args\n";
    my $tbl = $self->{tbl} or die "Missing tbl from pt-archiver plugin args\n";
    my $where  = _get_cmdline_option('where');
    my $dryrun = $ENV{PT_PARTITION_DROP_DRY_RUN} ? 1 : 0;

    die "Missing --where from original command line\n" unless $where;

    print "PLUGIN before_begin called\n";
    print "DB=$db TABLE=$tbl\n";
    print "WHERE=$where\n";
    print "PLUGIN_DRY_RUN=$dryrun\n";

    my ($column, $cutoff_date) = _parse_where($where);

    my $partitions = _get_partitions($dbh, $db, $tbl);

    if (!@$partitions) {
        print "Table `$db`.`$tbl` is not partitioned. Refusing DELETE.\n";
        exit(0);
    }

    my $partition_expr = $partitions->[0]->{expression};
    die "Missing PARTITION_EXPRESSION\n"
        unless defined $partition_expr && length $partition_expr;

    print "Partition expression: $partition_expr\n";

    my $cutoff_value = _evaluate_cutoff(
        $dbh,
        $partition_expr,
        $column,
        $cutoff_date,
    );

    print "Cutoff date: $cutoff_date\n";
    print "Cutoff boundary value: $cutoff_value\n";

    my $matched;

    for my $p (@$partitions) {
        next if !defined $p->{description};
        next if uc($p->{description}) eq 'MAXVALUE';

        if ($p->{description} == $cutoff_value) {
            $matched = $p;
            last;
        }
    }


    if (!$matched) {
        print "No exact partition boundary matches cutoff $cutoff_value. Refusing DELETE.\n";
        exit(0);
    }

    print "Matched boundary partition: $matched->{name}, position $matched->{position}\n";

    my @drop;

    for my $p (@$partitions) {
        next if !defined $p->{description};
        next if uc($p->{description}) eq 'MAXVALUE';

        if ($p->{position} <= $matched->{position}) {
            push @drop, $p->{name};
            print "Eligible for DROP: $p->{name}, boundary $p->{description}\n";
        }
    }

    if (!@drop) {
        print "No partitions eligible for DROP. Refusing DELETE.\n";
        exit(0);
    }

    my $sql = sprintf(
        "ALTER TABLE %s.%s DROP PARTITION %s",
        _quote_ident($db),
        _quote_ident($tbl),
        join(", ", map { _quote_ident($_) } @drop),
    );

    print "SQL: $sql\n";

    if ($dryrun) {
        print "PT_PARTITION_DROP_DRY_RUN enabled. Not executing DROP PARTITION.\n";
    }
    else {
        $dbh->do($sql);
        print "Dropped partitions: " . join(", ", @drop) . "\n";
    }

    $self->{statistics}->{partitions_dropped} = scalar @drop;

    exit(0);
}


sub _parse_where {
    my ($where) = @_;

    $where =~ s/^\s+|\s+$//g;

    die "Only WHERE format supported: created_at < 'YYYY-MM-DD'\n"
        unless $where =~ /^`?([A-Za-z0-9_]+)`?\s*<\s*'(\d{4}-\d{2}-\d{2})'\s*$/;

    return ($1, $2);
}

sub _evaluate_cutoff {
    my ($dbh, $partition_expr, $column, $cutoff_date) = @_;

    my $expr = $partition_expr;
    $expr =~ s/`//g;

    die "Partition expression does not reference column `$column`: $partition_expr\n"
        unless $expr =~ /\b\Q$column\E\b/i;

    $expr =~ s/\b\Q$column\E\b/'$cutoff_date'/ig;

    die "Unsafe generated expression: $expr\n"
        unless $expr =~ /^[A-Za-z0-9_\s\(\)\+\-\*\/,\.'":]+$/;

    my $sql = "SELECT $expr";

    print "Boundary evaluation SQL: $sql\n";

    my ($value) = $dbh->selectrow_array($sql);

    die "Cannot evaluate cutoff expression: $sql\n"
        unless defined $value;

    return $value;
}

sub _get_partitions {
    my ($dbh, $db, $tbl) = @_;

    my $sql = q{
        SELECT
            PARTITION_NAME,
            PARTITION_DESCRIPTION,
            PARTITION_EXPRESSION,
            PARTITION_ORDINAL_POSITION
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA = ?
          AND TABLE_NAME = ?
          AND PARTITION_NAME IS NOT NULL
        ORDER BY PARTITION_ORDINAL_POSITION
    };

    my $sth = $dbh->prepare($sql);
    $sth->execute($db, $tbl);
    my @partitions;

    while (my $row = $sth->fetchrow_hashref()) {
        push @partitions, {
            name        => $row->{PARTITION_NAME},
            description => $row->{PARTITION_DESCRIPTION},
            expression  => $row->{PARTITION_EXPRESSION},
            position    => $row->{PARTITION_ORDINAL_POSITION},
        };
    }

    return \@partitions;
}


sub _get_cmdline_option {

    my ($name) = @_;

    my $opt = "--$name";

    for (my $i = 0; $i < @ARGV; $i++) {
        if ($ARGV[$i] eq $opt && defined $ARGV[$i + 1]) {
            return $ARGV[$i + 1];
        }

        if ($ARGV[$i] =~ /^\Q$opt\E=(.*)$/) {
            return $1;
        }
    }

    if (open my $fh, '<', "/proc/$$/cmdline") {
        local $/;
        my $raw = <$fh>;
        close $fh;

        my @cmd = split /\0/, $raw;

        for (my $i = 0; $i < @cmd; $i++) {
            if ($cmd[$i] eq $opt && defined $cmd[$i + 1]) {
                return $cmd[$i + 1];
            }

            if ($cmd[$i] =~ /^\Q$opt\E=(.*)$/) {
                return $1;
            }
        }
    }

    return undef;
}



sub _quote_ident {

    my ($ident) = @_;

    die "Invalid identifier: $ident\n"
        unless defined $ident && $ident =~ /^[A-Za-z0-9_]+$/;

    return "`$ident`";
}

1;

Create the file named  pt_archiver_partition_drop.pm into the /usr/local/share/perl5 path.

Also set the environment variable PERL5LIB to let pt-archiver where to find the Perl package

export PERL5LIB=/usr/local/share/perl5

Example Usage

First, create the partitioned table events and insert some fake data.

DROP TABLE IF EXISTS events;


CREATE TABLE events (
  id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  payload JSON DEFAULT NULL,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
  PARTITION p202605 VALUES LESS THAN (TO_DAYS('2026-06-01')),
  PARTITION p202606 VALUES LESS THAN (TO_DAYS('2026-07-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

INSERT INTO events (id, created_at, payload) VALUES

-- p202604
(1,  '2026-04-01 08:00:00', JSON_OBJECT('event', 'login',    'user', 'alice')),
(2,  '2026-04-03 09:15:00', JSON_OBJECT('event', 'view',     'page', 'home')),
(3,  '2026-04-05 10:30:00', JSON_OBJECT('event', 'click',    'button', 'signup')),
(4,  '2026-04-08 11:45:00', JSON_OBJECT('event', 'search',   'term', 'mysql')),
(5,  '2026-04-10 12:00:00', JSON_OBJECT('event', 'purchase', 'amount', 100)),
(6,  '2026-04-14 13:20:00', JSON_OBJECT('event', 'logout',   'user', 'alice')),
(7,  '2026-04-18 14:35:00', JSON_OBJECT('event', 'download', 'file', 'report.pdf')),
(8,  '2026-04-22 15:50:00', JSON_OBJECT('event', 'upload',   'file', 'image.png')),
(9,  '2026-04-26 16:05:00', JSON_OBJECT('event', 'click',    'button', 'buy')),
(10, '2026-04-30 23:59:59', JSON_OBJECT('event', 'month_end')),

-- p202605

(11, '2026-05-01 00:00:00', JSON_OBJECT('event', 'login',    'user', 'bob')),
(12, '2026-05-03 08:10:00', JSON_OBJECT('event', 'view',     'page', 'pricing')),
(13, '2026-05-06 09:20:00', JSON_OBJECT('event', 'search',   'term', 'percona')),
(14, '2026-05-09 10:30:00', JSON_OBJECT('event', 'purchase', 'amount', 250)),
(15, '2026-05-12 11:40:00', JSON_OBJECT('event', 'logout',   'user', 'bob')),
(16, '2026-05-16 12:50:00', JSON_OBJECT('event', 'download', 'file', 'backup.sql')),
(17, '2026-05-20 13:00:00', JSON_OBJECT('event', 'upload',   'file', 'data.csv')),
(18, '2026-05-24 14:10:00', JSON_OBJECT('event', 'click',    'button', 'subscribe')),
(19, '2026-05-28 15:20:00', JSON_OBJECT('event', 'view',     'page', 'docs')),
(20, '2026-05-31 23:59:59', JSON_OBJECT('event', 'month_end')),

-- p202606

(21, '2026-06-01 00:00:00', JSON_OBJECT('event', 'login',    'user', 'carol')),
(22, '2026-06-03 08:05:00', JSON_OBJECT('event', 'search',   'term', 'partitioning')),
(23, '2026-06-06 09:15:00', JSON_OBJECT('event', 'view',     'page', 'dashboard')),
(24, '2026-06-09 10:25:00', JSON_OBJECT('event', 'purchase', 'amount', 500)),
(25, '2026-06-12 11:35:00', JSON_OBJECT('event', 'logout',   'user', 'carol')),
(26, '2026-06-16 12:45:00', JSON_OBJECT('event', 'login',    'user', 'dave')),
(27, '2026-06-20 13:55:00', JSON_OBJECT('event', 'download', 'file', 'archive.zip')),
(28, '2026-06-24 14:05:00', JSON_OBJECT('event', 'upload',   'file', 'video.mp4')),
(29, '2026-06-28 15:15:00', JSON_OBJECT('event', 'click',    'button', 'checkout')),
(30, '2026-06-30 23:59:59', JSON_OBJECT('event', 'month_end')),

-- pmax
(31, '2026-07-01 00:00:00', JSON_OBJECT('event', 'login',    'user', 'eve')),
(32, '2026-07-05 08:30:00', JSON_OBJECT('event', 'view',     'page', 'future')),
(33, '2026-07-10 09:45:00', JSON_OBJECT('event', 'search',   'term', 'maxvalue')),
(34, '2026-08-01 10:00:00', JSON_OBJECT('event', 'purchase', 'amount', 750)),
(35, '2026-09-01 11:15:00', JSON_OBJECT('event', 'retained_future'));

 

Now you can run the following command to delete all rows before the 1st of May, which, by the way, matches the entire first partition in the table.

pt-archiver \
  --source h=localhost,D=mydb,t=events,m=pt_archiver_partition_drop \
  --where "created_at < '2026-05-01'" \
  --purge

 

Notice the Perl plugin must be indicated with the m option in the DSN string.

In practice:

• pt-archiver initializes
• The plug-in runs
• Partitions are dropped
• No DELETE statements are executed

Here is what you get from the execution of the above command:

PLUGIN before_begin called
DB=mydb TABLE=events
WHERE=created_at < '2026-05-01'
PLUGIN_DRY_RUN=0
Partition expression: to_days(`created_at`)
Boundary evaluation SQL: SELECT to_days('2026-05-01')
Cutoff date: 2026-05-01
Cutoff boundary value: 740102
Matched boundary partition: p202604, position 1
Eligible for DROP: p202604, boundary 740102
SQL: ALTER TABLE `mydb`.`events` DROP PARTITION `p202604`
Dropped partitions: p202604

You can simply verify the table has been managed correctly:

SELECT * FROM mydb.events;

SHOW CREATE TABLE mydb.events;

 

Now TRUNCATE the table and recreate the data and try now to specify the where conditions that match a RANGE that is not the first in the list of the boundaries.

pt-archiver \
  --source h=localhost,D=mydb,t=events,m=pt_archiver_partition_drop \
  --where "created_at < '2026-06-01'" \
  --purge

You should get:

PLUGIN before_begin called
DB=mydb TABLE=events
WHERE=created_at < '2026-06-01'
PLUGIN_DRY_RUN=0
Partition expression: to_days(`created_at`)
Boundary evaluation SQL: SELECT to_days('2026-06-01')
Cutoff date: 2026-06-01
Cutoff boundary value: 740133
Matched boundary partition: p202605, position 2
Eligible for DROP: p202604, boundary 740102
Eligible for DROP: p202605, boundary 740133
SQL: ALTER TABLE `mydb`.`events` DROP PARTITION `p202604`, `p202605`
Dropped partitions: p202604, p202605

In this case, two partitions have been identified and dropped.

 

Truncate the table and recreate the data again. Try now to provide a WHERE condition that does not match any of the boundaries in the RANGE.

pt-archiver \
  --source h=localhost,D=mydb,t=events,m=pt_archiver_partition_drop \
  --where "created_at < '2026-04-25'" \
  --purge

 

You get the following:

PLUGIN before_begin called
DB=mydb TABLE=events
WHERE=created_at < '2026-04-25'
PLUGIN_DRY_RUN=0
Partition expression: to_days(`created_at`)
Boundary evaluation SQL: SELECT to_days('2026-04-25')
Cutoff date: 2026-04-25
Cutoff boundary value: 740096
No exact partition boundary matches cutoff 740096. Refusing DELETE.

As expected, the tool now refuses to execute anything if it doesn’t find an exact match.

 

Operational Benefits

This approach provides major advantages.

Dropping partitions is vastly faster than deleting rows, and minimal binary logging is needed, compared to billions of row deletes. There is no massive transactional overhead for managing undo logs and purging. You get then a better InnoDB Buffer Pool stability because of less page churn.

In the end, retention jobs are completed quickly and consistently in a predictable way and at the minimal cost.

 

Important Caveats

Partition Boundaries Must Match Retention Policy

If partitions contain mixed retention windows, DROP PARTITION may remove too much data. For this reason, ensure correct partition design.

Recommended:

• daily partitions
• weekly partitions
• monthly partitions

aligned with business retention requirements.

Metadata Locks

ALTER TABLE DROP PARTITION still acquires metadata locks.

Test carefully in production.

Backup Awareness

Ensure dropped partitions are no longer needed before removal or use pt-archiver to also copy the data into a remote server or dump the data into a CSV file before running the DROP PARTITION.

 

Possible Enhancements

The plug-in can be extended further.

Potential improvements:

• Support for daily partitions
• Support for UNIX timestamp partitions
• Dry-run reporting
• Automatic partition creation
• Push Slack notifications
• Export Prometheus metrics
• Safety checks for replicas
• GTID-aware orchestration
• Integration with pt-online-schema-change workflows

These are just some ideas I had meanwhile doing my tests. What you can do by implementing a Perl plugin is only limited by your imagination and your real needs.

Conclusion

pt-archiver remains an excellent tool for implementing retention policies and archival workflows.

However, DELETE-based purging becomes increasingly expensive at scale, even with proper indexing and chunked processing.

For large time-series or historical datasets, RANGE partitioning is often a dramatically superior strategy.

The challenge is that pt-archiver does not natively leverage partition-level operations.

Fortunately, its Perl plug-in architecture allows advanced users to extend its behavior and implement partition-aware cleanup logic.

By combining:

• pt-archiver orchestration
• MySQL RANGE partitioning
• Custom Perl plug-ins

Organizations can achieve:

• Faster retention enforcement
• Lower operational overhead
• Smaller replication impact
• Dramatically improved scalability

For large MySQL deployments, this hybrid approach can turn multi-hour purge operations into near-instant metadata operations.

The use case presented in this article is limited to a specific scenario, but you can reuse it or customize it if you have a different kind of RANGE partitioning, for example, not using TO_DAYS().

Take this as just an example of how you can extend pt-archiver. What you can do for real is driven by your needs and/or only limited by your imagination.

More info about extending pt-archiver:
https://docs.percona.com/percona-toolkit/pt-archiver.html#extending

 

The post Extending pt-archiver with a Partition-Aware Plug-in for Fast Retention Policy Enforcement appeared first on Percona.

Percona Monitoring and Management is a great open source database monitoring, observability, and management tool. Query analytics is one of the prominent features DBA uses actively to trace the incidents and query performance identification.

We all know and love the Query Analytics (QAN) dashboard… It’s the first place we look when an incident alert fires or when a developer asks, “Why is the app slow?” or “What was going on during the midnight production outage?”

But sometimes, the standard dashboards just don’t tell the whole story or maybe are not clear enough. QAN is great, but shouldn’t we have more? If you have PMM running, you already have a Ferrari engine under the hood: ClickHouse. Most of us just drive it in first gear using the default UI.

In this post, we are going to take the training wheels off. We will bypass the standard QAN interface and talk directly to the ClickHouse backend to build highly specialised dashboards. We aren’t just looking for “slow” queries anymore; we are hunting for inefficiency, volatility, and the “silent killers” that standard monitoring often misses.

This is the hands-on blog, so grab your coffee and let’s turn that PMM instance into a deep-dive forensic tool.

Create a New Dashboard in PMM

1. Connect to PMM > Dashboards > Create New Dashboard
2. Save it with name “Slow Query Analysis” and Description “Slow Query Analysis from PMM’s QAN database (clickhouse)”
3. Click on add visualisation & select datasource “ClickHouse”
   
   
4. Choose SQL Builder
   
   
5. Paste the following query to get top 10 slow queries from the database
   

   SELECT fingerprint
       FROM pmm.metrics
       WHERE service_type = 'mysql'
         AND $__timeFilter(period_start)
       GROUP BY fingerprint
       ORDER BY sum(m_query_time_sum) DESC
       LIMIT 10

6. Choose “Table View” on the top to view the list
   When you click “Run Query” you will see the top 10 slow queries in the chosen time period.
7. Let’s Save the dashboard after Panel Options updates as follows7.1 Change Panel Name and Description to: “Slow Query Analysis”7.2 Legend Placement to “Bottom”, Values to “min”,”max”, “mean”7.3 Change Axis’ Scale to “Logarithmic”Logarithmic scale on an axis compresses large ranges of data, making it ideal for visualizing metrics with vastly different magnitudes. This provides good visualisation for queries of different execution time frames.7.4 Save DashboardAlright, we’re at our first step. This first result set shows the top 10 slow query fingerprints across all MySQL services tracked by PMM for the selected time range. It provides a quick, environment-wide view of the most expensive query patterns. But this does not provide a clear picture. Let’s refine the dashboard to focus on specific queries, servers and observe their performance over time.Now, let’s introduce a variable to filter the data.
8. Click on Settings on Dashboard’s home page8.1 Choose “Variables” tab and click on “Add Variable”8.2 Add variable configuration and Save Dashboard 
9. Go Back to Dashboard and Edit “Slow Query Analysis” Panel.
   • Now you should see the Query ID filter on the top.
10. Change the query to the following
    

    SELECT
      period_start AS time,
      left(fingerprint, 80) AS query_text,
      sum(m_query_time_sum/m_query_time_cnt) AS query_time
    FROM
      pmm.metrics
    WHERE
      service_type = 'mysql'
      AND $__timeFilter(period_start)
      AND fingerprint IN (
        SELECT fingerprint
        FROM pmm.metrics
        WHERE service_type = 'mysql'
          AND $__timeFilter(period_start)
          AND ($queryid = '' OR queryid = $queryid)
        GROUP BY fingerprint
        ORDER BY sum(m_query_time_sum) DESC
        LIMIT 10
      )
    GROUP BY
      time,
      fingerprint
    ORDER BY
      time,
      query_time DESC

    • Basically the query is fetching start time, query text and average query time for the selected period for the top 10 Queries in that time-frame.
    • There is a filter for the “queryid” variable which you may use if you want to filter on a specific queryid.
    • Choose “Time Series” as “Query Type”
11. Adjust Panel Options11.1 Choose “Standard options” > “Unit” as “Time / Seconds (s)” from drop down.11.2 Choose “Standard options” > “Display name” as “${__field.labels.query_text}”11.3 Click on “Save Dashboard”
12. Your dashboard should be ready

Now, by default this dashboard is plotting top 10 queries. If you have a query fingerprint handy, you may be able to filter the search by that specific query.  That said, this is still plotting queries across all the monitored instances. Let’s move on to add the service_name filter.

 

Adding service_name filter

1. Add Variable
   1. Create new variable named “service_name”
   2. Use variable type “Query”
   3. Use Data Source as “ClickHouse”
   4. Query:
      

      select distinct service_name from pmm.metrics where service_type = 'mysql';

   5. Unselect all checkboxes in “Selection options”
   6. Save Dashboard
2. Update Query

SELECT
  period_start AS time,
  left(fingerprint, 80) AS query_text,
  sum(m_query_time_sum/m_query_time_cnt) AS query_time
FROM
  pmm.metrics
WHERE
  (service_name = '' OR service_name = '$service_name')
  AND service_type = 'mysql'
  AND $__timeFilter(period_start)
  AND fingerprint IN (
    SELECT fingerprint
    FROM pmm.metrics
    WHERE service_type = 'mysql'
      AND $__timeFilter(period_start)
      AND (service_name = '' OR service_name = '$service_name')
    GROUP BY fingerprint
    ORDER BY sum(m_query_time_sum) DESC
    LIMIT 10
  )
GROUP BY
  time,
  left(fingerprint, 80) 
ORDER BY
  time,
  query_time DESC

I know many of you are naturally curious and enjoy experimenting with PMM and Grafana… So you’ve probably already started thinking about how far this can be taken. Feel free to share your ideas or custom dashboards in the comments.

Sample Dashboards:

The Query Analysis and Insights Dashboard

Okay, for those who are looking to have quick results, I’ve prepared the complete Query Analysis and Insights Dashboard for you to import and use instantly.

By importing the JSON file, you’ll get the full working dashboard with all panels preconfigured, including:

• Slow Query Analysis
• Latency Distribution Heatmap
• Query Volatility (P99 vs Average)
• Lock Wait Ratio Over Time (Top Contended Queries)
• Temporary Table Usage (Disk & Memory)
• Query Efficiency (Rows Examined vs Rows Sent)
• Error Rate vs Throughput
• Workload Distribution by User
• Query Volume by Client Host
• Execution Time vs Lock Wait Time

This allows you to instantly explore PMM Query Analytics data, adjust time ranges and filters, and correlate query performance, contention, and workload behavior without recreating the dashboard from scratch.

Dashboard JSON available here:

• Grafana: https://grafana.com/grafana/dashboards/24896
• GitHub:  https://github.com/Percona-Lab/pmm-dashboards/query_analysis_insights.json

Give it a go and let me know if you have suggestions or requests. Also consider sharing if you create something interesting.

Cheers.

The post Building Query Analysis and Insights Dashboard in PMM appeared first on Percona.

Recently, we received an alert for one of our Managed Services customers indicating that the auto_increment value for the table was 80% of its maximum capacity. The column was INT UNSIGNED, which has a limit of 4,294,967,295.

At 80%, we have enough time to change it to BIGINT.…. Right? Let’s see.

So we used pt-online-schema-change to perform the alter.

It started running at a good pace but slowed over time.

 

Why?

Well, let’s look at the definition of the table:

mysql> show create table myschema.mytableG
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `long_column` varchar(1000) NOT NULL,
  `state` tinyint unsigned NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `short_column` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_long_column` (`long_column`,`state`),
  KEY `idx_short_column` (`short_column`,`state`),
  KEY `idx_short_col2` (`short_column`)
) ENGINE=InnoDB AUTO_INCREMENT=4009973818 DEFAULT CHARSET=utf8mb3

NOTE1: The index on long_column is for a varchar column with a length of 1000; it may not be required, and an index prefix may be more helpful here.

NOTE2: The index idx_short_col2 is duplicated, as it is covered by the index idx_short_column.

Those changes require testing and are out of scope for this emergency, but they are worth mentioning.

 

Table size:

+---------------+------------+------------+---------+----------+---------+----------+--------+
| TABLE_SCHEMA  | TABLE_NAME | TABLE_ROWS | DATA_GB | INDEX_GB | FREE_GB | TOTAL_GB | ENGINE |
+---------------+------------+------------+---------+----------+---------+----------+--------+
| myschema      | mytable    | 3906921584 |    1118 |     1790 |       0 |     2907 | InnoDB |
+---------------+------------+------------+---------+----------+---------+----------+--------+

Look at the indexes being way bigger than the data.

mysql> SELECT database_name, table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_in_mb FROM mysql.innodb_index_stats WHERE stat_name = 'size' AND index_name != 'PRIMARY' and database_name='myschema' and table_name='mytable' ORDER BY size_in_mb DESC;
+---------------+------------+-------------------+------------+
| database_name | table_name | index_name        | size_in_mb |
+---------------+------------+-------------------+------------+
| myschema      | mytable    | idx_long_column   | 1583538.95 |
| myschema      | mytable    | idx_short_column  |  126432.98 |
| myschema      | mytable    | idx_short_col2    |  122699.95 |
+---------------+------------+-------------------+------------+
3 rows in set (0.01 sec)

While the pt-online-schema-change runs, it copies the data to a new table. As the data is being copied, the secondary indexes must be maintained.

NOTE the huge index for a varchar(1000) that is ~1.5T in size. Maintaining such an index becomes increasingly expensive as the data size increases.

The pt-online-schema-change had been running for ~8 days, and its latest estimate was 53 more days, which we can’t afford, since the maximum value would be exceeded in ~15 days. 

Copying `myschema`.`mytable`:  12% 53+16:48:01 remain
Copying `myschema`.`mytable`:  12% 53+16:48:30 remain
Copying `myschema`.`mytable`:  12% 53+16:48:59 remain
Copying `myschema`.`mytable`:  12% 53+16:49:26 remain
Copying `myschema`.`mytable`:  12% 53+16:49:53 remain
Copying `myschema`.`mytable`:  12% 53+16:50:19 remain
Copying `myschema`.`mytable`:  12% 53+16:50:49 remain
Copying `myschema`.`mytable`:  12% 53+16:51:17 remain
Copying `myschema`.`mytable`:  12% 53+16:51:45 remain

 

So what do we do now?

We suggested canceling the pt-online-schema-change and creating an Aurora blue-green deployment.

Then perform the direct ALTER on the green cluster. And finally, when ready, do the failover.

 

Sounds good, doesn’t it?

 

First, we need to ensure that the new cluster (green) has the replica_type_conversions  parameter in its cluster parameter group to “ALL_NON_LOSSY, ALL_UNSIGNED” in order to be able to replicate from an int unsigned column to a bigint unsigned column.

So we tried that, it started too fast ~0.036% per minute, that’s 2 days. That’s great!

We left the process running over the weekend, but we noticed it started to slow down again… By Monday, it was advancing at ~0.01% every 5 mins, which gives an ETA of 34 days. 

Why? 

Again, using the direct ALTER MySQL copies the data to a temp table, and the bigger the data, the harder it is to maintain the indexes. 

Again, unacceptable.

Note that with the above 2 approaches, we lost ~12 days of precious time, and the deadline for auto_increment exhaustion was approaching.

Then we thought: What if we drop the secondary indexes, do the alter, and then add the indexes back?

In theory, it should be faster, as:

• Dropping the indexes is a metadata-only operation with ONLINE DDL.
• Altering the column datatype from INT to BIGINT is not an ONLINE operation, but the fact that it doesn’t have to update secondary indexes during row copying to a new temporary table prevents the slowdown.
  
• Adding back the secondary indexes is an ONLINE DDL operation:

 

“Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.”

https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html

So let’s do this:

The deletion of the indexes was really quick, as expected (metadata-only operation):

mysql> ALTER TABLE myschema.mytable DROP INDEX idx_long_column, DROP INDEX idx_short_column, DROP INDEX idx_short_col2;
Query OK, 0 rows affected (49.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

Then the change of the datatype:

mysql> ALTER TABLE myschema.mytable CHANGE COLUMN id id bigint unsigned NOT NULL AUTO_INCREMENT;
Query OK, 4058047205 rows affected (13 hours 9 min 10.62 sec)
Records: 4058047205  Duplicates: 0  Warnings: 0

 

Looks very promising!!!

 

The final step, add back the indexes:

mysql> ALTER TABLE myschema.mytable ADD INDEX `idx_long_column` (`long_column`,`state`), ADD INDEX `idx_short_column` (`short_column`,`state`), ADD INDEX `short_col2` (`short_column`);
ERROR 1878 (HY000): Temporary file write failure.

 

Why?

Well, the INPLACE operation uses the tmp dir to write sort files. In Aurora, there are certain limits for the temporary space based on the instance type. 

In a regular MySQL instance, we can modify the innodb_tmpdir to another location with enough disk space; however, in Aurora, the parameter is not modifiable, which could have made the whole process easier.

Even with a larger instance type, it’s hard to create the 1.5T index without breaking open the piggy bank.

 

Last resort, add the indexes back with the COPY algorithm:

mysql> ALTER TABLE myschema.mytable ALGORITHM=COPY, ADD INDEX `idx_long_column` (`long_column`,`state`), ADD INDEX `idx_short_column` (`short_column`,`state`), ADD INDEX `idx_short_col2` (`short_column`);
Query OK, 4147498819 rows affected (6 days 1 hour 55 min 57.00 sec)
Records: 4147498819  Duplicates: 0  Warnings: 0

 

Why does it work? Because ALTER TABLE using the COPY algorithm uses the datadir as the destination for the temporary table, the rows are copied there. It doesn’t have the limitation of the temporary directory mentioned above.

We were able to make it on time about 4 days before the auto_increment exhaustion, preventing downtime.

 

In retrospective we could have used the following approach to avoid the use of the blue/green deployment:

1. Perform a pt-online-schema-change on the main table, dropping the indexes, and changing the column type to bigint. ( with –no-swap-tables –no-drop-old-table –no-drop-new-table –no-drop-triggers).
2. Add the secondary indexes using the direct alter with the COPY algorithm in the _new table.
3. Once the alter finishes, swap the tables and drop the triggers.

 

Conclusion:

What initially looked like an easy task with pt-online-schema-change, ended up being more complex. 

You need to check the data definition, the index sizes, the Aurora limits, and how the different algorithms work to make a decision on the best way to proceed with those tasks, specially on situations like these where you have the pressure of the auto_increment being exhausted and there’s risk of downtime if it is not done on time.

And of course, monitor auto_increment exhaustion for your tables, and use a reasonable threshold that gives you enough time to plan and change the table definition. You can use Percona Monitoring and Management for this, specifically on the MySQL > MySQL Table Details dashboard.

The post Run an ALTER TABLE for a huge table in Aurora appeared first on Percona.

Recently, we received an alert for one of our Managed Services customers indicating that the auto_increment value for the table was 80% of its maximum capacity. The column was INT UNSIGNED, which has a limit of 4,294,967,295.

At 80%, we have enough time to change it to BIGINT.…. Right? Let’s see.

So we used pt-online-schema-change to perform the alter.

It started running at a good pace but slowed over time.

 

Why?

Well, let’s look at the definition of the table:

mysql> show create table myschema.mytableG
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `long_column` varchar(1000) NOT NULL,
  `state` tinyint unsigned NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `short_column` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_long_column` (`long_column`,`state`),
  KEY `idx_short_column` (`short_column`,`state`),
  KEY `idx_short_col2` (`short_column`)
) ENGINE=InnoDB AUTO_INCREMENT=4009973818 DEFAULT CHARSET=utf8mb3

NOTE1: The index on long_column is for a varchar column with a length of 1000; it may not be required, and an index prefix may be more helpful here.

NOTE2: The index idx_short_col2 is duplicated, as it is covered by the index idx_short_column.

Those changes require testing and are out of scope for this emergency, but they are worth mentioning.

 

Table size:

+---------------+------------+------------+---------+----------+---------+----------+--------+
| TABLE_SCHEMA  | TABLE_NAME | TABLE_ROWS | DATA_GB | INDEX_GB | FREE_GB | TOTAL_GB | ENGINE |
+---------------+------------+------------+---------+----------+---------+----------+--------+
| myschema      | mytable    | 3906921584 |    1118 |     1790 |       0 |     2907 | InnoDB |
+---------------+------------+------------+---------+----------+---------+----------+--------+

Look at the indexes being way bigger than the data.

mysql> SELECT database_name, table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_in_mb FROM mysql.innodb_index_stats WHERE stat_name = 'size' AND index_name != 'PRIMARY' and database_name='myschema' and table_name='mytable' ORDER BY size_in_mb DESC;
+---------------+------------+-------------------+------------+
| database_name | table_name | index_name        | size_in_mb |
+---------------+------------+-------------------+------------+
| myschema      | mytable    | idx_long_column   | 1583538.95 |
| myschema      | mytable    | idx_short_column  |  126432.98 |
| myschema      | mytable    | idx_short_col2    |  122699.95 |
+---------------+------------+-------------------+------------+
3 rows in set (0.01 sec)

While the pt-online-schema-change runs, it copies the data to a new table. As the data is being copied, the secondary indexes must be maintained.

NOTE the huge index for a varchar(1000) that is ~1.5T in size. Maintaining such an index becomes increasingly expensive as the data size increases.

The pt-online-schema-change had been running for ~8 days, and its latest estimate was 53 more days, which we can’t afford, since the maximum value would be exceeded in ~15 days. 

Copying `myschema`.`mytable`:  12% 53+16:48:01 remain
Copying `myschema`.`mytable`:  12% 53+16:48:30 remain
Copying `myschema`.`mytable`:  12% 53+16:48:59 remain
Copying `myschema`.`mytable`:  12% 53+16:49:26 remain
Copying `myschema`.`mytable`:  12% 53+16:49:53 remain
Copying `myschema`.`mytable`:  12% 53+16:50:19 remain
Copying `myschema`.`mytable`:  12% 53+16:50:49 remain
Copying `myschema`.`mytable`:  12% 53+16:51:17 remain
Copying `myschema`.`mytable`:  12% 53+16:51:45 remain

 

So what do we do now?

We suggested canceling the pt-online-schema-change and creating an Aurora blue-green deployment.

Then perform the direct ALTER on the green cluster. And finally, when ready, do the failover.

 

Sounds good, doesn’t it?

 

First, we need to ensure that the new cluster (green) has the replica_type_conversions  parameter in its cluster parameter group to “ALL_NON_LOSSY, ALL_UNSIGNED” in order to be able to replicate from an int unsigned column to a bigint unsigned column.

So we tried that, it started too fast ~0.036% per minute, that’s 2 days. That’s great!

We left the process running over the weekend, but we noticed it started to slow down again… By Monday, it was advancing at ~0.01% every 5 mins, which gives an ETA of 34 days. 

Why? 

Again, using the direct ALTER MySQL copies the data to a temp table, and the bigger the data, the harder it is to maintain the indexes. 

Again, unacceptable.

Note that with the above 2 approaches, we lost ~12 days of precious time, and the deadline for auto_increment exhaustion was approaching.

Then we thought: What if we drop the secondary indexes, do the alter, and then add the indexes back?

In theory, it should be faster, as:

• Dropping the indexes is a metadata-only operation with ONLINE DDL.
• Altering the column datatype from INT to BIGINT is not an ONLINE operation, but the fact that it doesn’t have to update secondary indexes during row copying to a new temporary table prevents the slowdown.
  
• Adding back the secondary indexes is an ONLINE DDL operation:

 

“Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.”

https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html

So let’s do this:

The deletion of the indexes was really quick, as expected (metadata-only operation):

mysql> ALTER TABLE myschema.mytable DROP INDEX idx_long_column, DROP INDEX idx_short_column, DROP INDEX idx_short_col2;
Query OK, 0 rows affected (49.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

Then the change of the datatype:

mysql> ALTER TABLE myschema.mytable CHANGE COLUMN id id bigint unsigned NOT NULL AUTO_INCREMENT;
Query OK, 4058047205 rows affected (13 hours 9 min 10.62 sec)
Records: 4058047205  Duplicates: 0  Warnings: 0

 

Looks very promising!!!

 

The final step, add back the indexes:

mysql> ALTER TABLE myschema.mytable ADD INDEX `idx_long_column` (`long_column`,`state`), ADD INDEX `idx_short_column` (`short_column`,`state`), ADD INDEX `short_col2` (`short_column`);
ERROR 1878 (HY000): Temporary file write failure.

 

Why?

Well, the INPLACE operation uses the tmp dir to write sort files. In Aurora, there are certain limits for the temporary space based on the instance type. 

In a regular MySQL instance, we can modify the innodb_tmpdir to another location with enough disk space; however, in Aurora, the parameter is not modifiable, which could have made the whole process easier.

Even with a larger instance type, it’s hard to create the 1.5T index without breaking open the piggy bank.

 

Last resort, add the indexes back with the COPY algorithm:

mysql> ALTER TABLE myschema.mytable ALGORITHM=COPY, ADD INDEX `idx_long_column` (`long_column`,`state`), ADD INDEX `idx_short_column` (`short_column`,`state`), ADD INDEX `idx_short_col2` (`short_column`);
Query OK, 4147498819 rows affected (6 days 1 hour 55 min 57.00 sec)
Records: 4147498819  Duplicates: 0  Warnings: 0

 

Why does it work? Because ALTER TABLE using the COPY algorithm uses the datadir as the destination for the temporary table, the rows are copied there. It doesn’t have the limitation of the temporary directory mentioned above.

We were able to make it on time about 4 days before the auto_increment exhaustion, preventing downtime.

 

In retrospective we could have used the following approach to avoid the use of the blue/green deployment:

1. Perform a pt-online-schema-change on the main table, dropping the indexes, and changing the column type to bigint. ( with –no-swap-tables –no-drop-old-table –no-drop-new-table –no-drop-triggers).
2. Add the secondary indexes using the direct alter with the COPY algorithm in the _new table.
3. Once the alter finishes, swap the tables and drop the triggers.

 

Conclusion:

What initially looked like an easy task with pt-online-schema-change, ended up being more complex. 

You need to check the data definition, the index sizes, the Aurora limits, and how the different algorithms work to make a decision on the best way to proceed with those tasks, specially on situations like these where you have the pressure of the auto_increment being exhausted and there’s risk of downtime if it is not done on time.

And of course, monitor auto_increment exhaustion for your tables, and use a reasonable threshold that gives you enough time to plan and change the table definition. You can use Percona Monitoring and Management for this, specifically on the MySQL > MySQL Table Details dashboard.

The post Run an ALTER TABLE for a huge table in Aurora appeared first on Percona.