Oracle’s MySQL 5.7 has had a good, long run, but the official End of Life is October of 2023. The Era of MySQL 5.x will be over, and only MySQL 8.0 will be officially supported. Yup, six months away. So if you are running MySQL, you need to consider upgrading to version 8.0 N-O-W!!

What does an upgrade provide?

MySQL 8.0 has many really cool features and improvements that are well worth the upgrade. The default character set of UTF8MB4 gives Unicode version 9.0 support. So you get the Umaluts, Cedils, and C-J-K Language support in your data, plus emojis. 8.0 is optimized around this character set. This gives you all the international characters you probably need to support global operations.

The Structured Query Language has been greatly enhanced. If you have trouble writing subqueries, you rejoice in lateral-derived joins and Common Table Expressions (CTEs). There is a new intersect clause to aid with sets. …

ChatGPT is the hottest topic in the tech world right now. One story even says that ChatGPT has passed Google’s Level 3 programming interview.  I wondered, does that mean ChatGPT is ready to replace MySQL DBAs, too? No. Let me show you why.

Recently, one of our clients was considering encrypting their data at rest using the Percona file-based keyring plugin. To make the process more secure, they considered removing the local keyring_file after MySQL started. So even if someone gets host access, the data files are still protected because they do not have access to the master key used to encrypt the keys for the tables running Encryption at Rest.

Let ChatGPT try its hand at MySQL administration

Let’s see what ChatGPT will say.

I asked ChatGPT the …

Bloom filters are an essential component of an LSM-based database engine like MyRocks. This post will illustrate through a simple example how bloom filters work in MyRocks.

Why?

With MyRocks/RocksDB, data is stored in a set of large SST files.  When MyRocks needs to find the value associated with a given key, it uses a bloom filter to guess if the key could potentially be in an SST file.

How?

A bloom filter is a space-efficient way of storing information about a list of keys. At its base, there is a bitmap and a hash function.  The hash value of the keys stored in an SST is computed, and the results are used to set some bits to “1” in the bitmap.  When you want to know if a key is present or not in the list, you run it through the hash function and check if the corresponding bits in the bitmap are …

Recently, I was working with my colleagues Edwin Wang and Taras Onishchuk and found an interesting edge case involving a situation where a replica running Percona Server for MySQL 5.7, external to AWS Aurora instance version 2.10.2 (5.7-compatible), broke. I recreated the issue in my lab with a simple create database statement, as you will see below.

Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/share/percona-server/charsets/Index.xml' file' on query. Default database: 'lab'. Query: 'create database test'

The interesting thing to note here is the character set ‘#255’. You won’t see this available if you check the list of available collations in Percona Server for MySQL 5.7 for the UTF8MB4 character set.

mysql> SHOW COLLATION WHERE Charset = 'utf8mb4' and id = 255; 
Empty set (0.01 sec)

Percona XtraBackup is an open source hot backup utility for MySQL-based servers. To take consistent and hot backup, it uses various locking methods, especially for non-transactional storage engine tables. This blog post discusses the cause and possible solution for queries with ​Waiting for table flush state in processlist when taking backups using Percona XtraBackup. Only MySQL 5.7 version is affected by this, as per my tests.

Type of locks taken by Percona XtraBackup

Before discussing the main issue, let’s learn about the type of locks used by Percona XtraBackup to take consistent backups. Percona XtraBackup uses backup locks as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is …

Amazon instance profiles are used to pass IAM roles to an EC2 instance. This IAM role can be queried using EC2 instance metadata to access an S3 bucket. Please check Amazon’s Official Documentation for more information.

Today we are happy to announce that starting with Percona XtraBackup 8.0.31-24, xbcloud can read instance metadata and fetch credentials from an instance profile, utilizing it to authenticate against an S3 bucket. Xbcloud is a tool part of Percona XtraBackup and allows you to upload and download backups to Amazon S3 storage.

How it works

Configure your EC2 instance …

Most of us are lucky that software bugs are a small part of our lives. The ‘things just work’ attitude works for most situations, but occasionally something appears that may seem trivial at first glance may be catastrophic.

A bug appeared in MySQL’s recently released 8.0.32 that really caught my attention. This is a prime example of how a minor bug could have a significant impact on your life and a detrimental impact on your company or project.

The title Wrong result for AVG() OVER(ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) is the first thing that caught my eye. A lot of developers heavily use the AVG() function. My first thought was, ‘ut-oh, AVG() being broken is a terrible thing,’ but then I realized it was thankfully not that drastic.

The OVER() clause indicates this is a problem in a Window Function. And odds are, if you are using OVER() for …

A couple of days ago I was creating an index on the source and when I checked the replica side it was not replicated, so I just wanted to explain how the replication filter may increase the complexity of your DBA operations.

Replication occurs by reading events from the binary log of the source and then executing them on the replica. The events in the binary log are recorded in different formats, depending on the type of event. These formats are determined by the binary logging format used when the events were initially recorded on the source. The relationship between the binary logging formats and the terminology used during replication is as follows:

When using statement-based binary logging, the source writes SQL statements to the binary log. Replication of the source to the replica is performed by executing these SQL statements on the replica. This is known as statement-based replication (SBR), and it …

We had a couple of cases where clients reported that the MySQL error log was flooded with the below note:

2023-01-18T13:07:56.946323Z 2 [Note] InnoDB: Stopping purge
2023-01-18T13:07:56.948621Z 2 [Note] InnoDB: Resuming purge
2023-01-18T13:08:27.229703Z 2 [Note] InnoDB: Stopping purge
2023-01-18T13:08:27.231552Z 2 [Note] InnoDB: Resuming purge
2023-01-18T13:08:28.581674Z 2 [Note] InnoDB: Stopping purge

One of my colleagues Sami Ahlroos found that whenever we trigger a truncate on any table, the function is stopping the purge and then resuming it once it has found it stopped.

Below are the steps to reproduce.

1. Log verbosity needs to be set to 3 (the default value)

mysql> show variables like 'log_error_verbosity%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| …

How can we fix a nasty out-of-space issue leveraging the flexibility of Percona Operator for MySQL?

When planning a database deployment, one of the most challenging factors to consider is the amount of space we need to dedicate to data on disk.

This is even more cumbersome when working on bare metal, as it is more difficult to add space when using this kind of solution with respect to the cloud.

When using cloud storage like EBS or similar, it is normally easy(er) to extend volumes, which gives us the luxury to plan the space to allocate for data with a good grade of relaxation. 

Is this also true when using a solution based on Kubernetes like Percona Operator for MySQL? Well, it depends on where you run it. However, if the platform you choose supports the option to extend volumes, K8s per se gives you the …