In the world of database management, ALTER TABLE operations are a crucial part of modifying database structures. MySQL, a popular database management system, offers online operations since version 5.6, providing a convenient way to perform these alterations without locking the table. However, there are caveats. In this blog, we’ll explore the process of altering VARCHAR columns online, delving into insights gained while expanding the size of such columns.

To kick start our journey, let’s consider a table definition that requires the expansion of a VARCHAR column named “_varchar” to accommodate more data. Here’s the original table definition:

DROP TABLE IF EXISTS varchar_alter;
CREATE TABLE `varchar_alter` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `_varchar` VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

We execute the initial ALTER TABLE command:

mysql> ALTER TABLE test.varchar_alter CHANGE COLUMN …

Dynamic SQL is a desirable feature that allows developers to construct and execute SQL statements dynamically at runtime. While MySQL lacks built-in support for dynamic SQL, this article presents a workaround using prepared statements. We will explore leveraging prepared statements to achieve dynamic query execution, parameterized queries, and dynamic table and column selection.

Understanding prepared statementsMySQL support for server-side prepared statements, leveraging the efficient client/server binary protocol. A prepared statement is a functionality designed to execute identical or similar SQL statements repeatedly, achieving optimal efficiency in database operations.

Advantages of Prepared Statements

1. Reduced Parsing Overhead: Prepared statements minimize the overhead of parsing SQL queries each time they are executed. This is especially advantageous in database applications that …

On July 18th, Oracle released its first pair of MySQL LTS and Innovation releases. (For more, check out A Quick Peek at MySQL 8.0.34 and MySQL 8.1.0.) These releases were announced several months ago and gradually detailed closer to the release date. Today, we know from Oracle’s official blog post what to expect, as well as what the cadence and scope of the upcoming releases will be.

So, the next immediate question that comes to mind is: Is Percona going to follow the same release pattern as “upstream“ MySQL?

The short answer is “yes.”

We are proud to say that over the last several years, Percona delivered on its promise of providing the MySQL community a …

This is a Quick Peek at what Oracle delivered with MySQL 8.0.34 and 8.1.0 on July 17th, 2023. MySQL previously released software quarterly as part of a continuous delivery system where new features were made available. Many people loved access to the new features even if they could be risky, but others wanted stability and bug fixes only. Now there is a long-term support (LTS) version for the more conservative and the risk-averse folk that will have a roughly two-year lifespan between major releases.

The release with the new features will be called the Innovation series, and MySQL 8.1.0 is the first of these releases. And the LTS will be based on 8.0.34. Please note that comments in italics are mine and do not reflect the views of Percona and possibly not anyone else.

Quick reminder

The End of Life for MySQL 8.0 is in April of 2026, and the …

In this blog post, we will describe the improvements to Percona XtraBackup 8.0.33-28 (PXB), which significantly reduces the time to prepare the backups before the restore operation. This improvement in Percona XtraBackup significantly reduces the time required for a new node to join the Percona XtraDB Cluster (PXC).

Percona XtraDB Cluster uses Percona XtraBackup to do SST (State Snapshot Transfer) from one node to another. When a new node joins the cluster, SST is performed to receive the data from DONOR to the JOINER. JOINER uses PXB to stream the data directory from DONOR. JOINER must prepare the backup before using it. It is observed that when the DONOR has a huge number of tablespaces (one million),  XtraBackup on JOINER …

When it comes to backups, there are several options for saving backup files. You can choose to save them locally on the same server, stream them to different servers, or store them in object storage. Percona XtraBackup facilitates streaming through the use of an auxiliary tool called xbcloud.

STDOUT Datasink

This diagram displays the process of streaming a backup to object storage utilizing the current STDOUT datasink:

• XtraBackup spawns multiple copy threads. Each one will be reading a chunk of data from a specific file.
• Each copy thread will write the chunk of data to a pipe (aka STDOUT).
• Xbcloud will have a red thread that will be responsible for reading each chunk of data from STDIN. This chunk will be uploaded to object storage utilizing an async request, and a callback will be added to an …

Well, “easy” if you know just a tiny bit of C++.

MySQL is well known for its ease of use, being easy to install, easy to configure, and easy to maintain. What if there is something more that you’d like MySQL to do? How would you integrate some new fancy processing library into MySQL without having to recreate the complexities in pure SQL?

MySQL Loadable Functions would be the way to go. In this blog post, you’ll learn how to set up a build environment for compiling your own MySQL plugin to be loaded into MySQL as a function. Our function will implement a ULID generator using a C++ library from ChrisBove/ulid.

Creating the build environment

The first step is downloading the …

This blog was originally published in January 2022 and was updated in July 2023.

Working with hundreds of different customers, I often face similar problems around running queries. One very common problem when trying to optimize a database environment is index usage. A query that cannot use an index is usually a long-running one, consuming more memory or triggering more disk iops.

A very common case is when a query uses a filter condition against a column that is involved in some kind of functional expression. An index on that column can not be used.

Starting from MySQL 8.0.13, functional indexes are supported. In this article, I will first explain an overview of indexes in MySQL and cover the MySQL CREATE INDEX before diving into showing what functional indexes are and how they work.

Introduction to MySQL Indexes

In the world of modern web applications, it is increasingly important to support a diverse range of languages and character sets. With the rise of globalization, the need to store and process multilingual data has become essential. MySQL, one of the most popular relational database management systems, recognizes this need and has introduced utf8mb4 in its 8.0 version as a game-changer. In this blog post, we will explore utf8mb4 and its advantages in MySQL 8.0, backed by practical examples.

Understanding utf8mb4

Before diving into the benefits, let’s clarify what utf8mb4 represents. In MySQL, “utf8” refers to a character encoding that supports the Unicode character set using a maximum of three bytes per character. However, the original utf8 implementation in MySQL does not cover all Unicode characters. utf8mb4, on the other hand, is a modified version of utf8 that supports the complete Unicode character set, including emojis …

Fragmentation is a common concern in some database systems. Highly fragmented tables can affect performance and resource allocation. But reducing fragmentation often involves rebuilding the table completely. This blog post will discuss fragmentation and its impact on InnoDB.

What is fragmentation?

We say that something is fragmented when it is formed by parts that are separate or placed in a different order than the natural one. In databases, we can experiment with different types of fragmentation:

• Segment Fragmentation: segments are fragmented; they are stored not following the order of data, or there are empty pages gaps between the data pages.
• Tablespace Fragmentation: the tablespace is stored in non-consecutive filesystem blocks.
• Table Fragmentation: data is stored not following the primary key order (heap tables), …