As we approach end of life for MySQL 5.7 later this year, many businesses are currently working towards upgrading to MySQL 8. Such major version upgrades are rarely simple, but thankfully there are tools that can help smooth the process and ensure a successful upgrade.

It should be noted that while the technical aspects of the upgrade process are beyond the scope of this blog post, it is crucial to create a testing environment to verify the upgrade before proceeding to upgrade your production servers, particularly with MySQL 8. 

As there is no procedure for downgrading from MySQL 8 other than restoring a backup, testing and validation are more critical than previous major version …

MySQL released version 8.0.33 on April 18th, featuring some attention-catching features.  This blog post is a quick review of the release notes looking for the exciting items, and comments in italics are solely my own.

User-defined collations are now deprecated and will be removed in a future version.  This is probably not a show-stopper for most and probably a scary situation for those dependent on them as there may not be an alternative.  Hopefully, UTF8MB4 is good enough.

The Performance Shema now has a Server Telemetry Traces service. This interface provides plugins and components a way to retrieve notifications related to SQL statements’ lifetime. We are directed to the Server telemetry traces service section in the MySQL Source Code documentation

The SSL library has been updated to OpenSSL version …

During the early stages of my career, I was captivated by the theories and concepts surrounding foreign keys and how they empowered us to maintain data integrity. However, in practical application, I have found them to be quite challenging to manage. I am sure you’re a champion DBA, but I keep my distance from foreign keys.

With that short story as a background, this post aims to address the dilemma that arises when utilizing the pt-online-schema-change tool on tables that contain foreign keys.

We already know what one of the most used Percona tools pt-online-schema-change is and how pt-online-schema-change handles foreign keys.

When utilizing the pt-online-schema-change tool to alter a table, such as …

Indexes are crucial for optimizing query execution times in databases, but having an excessive number of indexes, or redundant ones, can negatively impact performance. While pt-duplicate-key-checker is the go-to tool for identifying duplicate or redundant indexes in MySQL, it may not catch all duplicates.

In this blog post, we’ll put ourselves to the test and see if we can identify duplicate and redundant indexes in MySQL. Toward the end, we will identify what the pt-duplicate-key-checker doesn’t.

The unique quiz

Consider the following MySQL table definition. Let’s put our brains to work and note any of the duplicate or redundant indexes (play fair, don’t cheat):

CREATE TABLE `table_with_lot_of_trouble` (
`id` int NOT NULL,
`col1` varchar(1) DEFAULT NULL,
`col2` varchar(2) DEFAULT NULL,
`col3` varchar(3) …

For a long time, MyDumper has been the fastest tool to take Logical Backups. We have been adding several features to expand the use cases. Masquerade was one of these features, but it was only for integer and UUID values. In this blog post, I’m going to present a new functionality that is available in MyDumper and will be available in the next release: we added the possibility to build random data based on a format that the user defines.

How does it work?

During export, mydumper sends SELECT statements to the database. Each row is written one by one as an INSERT statement. Something important that you might not know, is that each column of a row can be transformed by a function. When you execute a backup, the default function is the identity function, as nothing needs to be changed. The function, which can be configured inside the defaults file, will change the …

When talking about the benefits and use cases of ProxySQL with clients, one feature I generally reference is the query rewrite engine. This is a great feature that is often used for sharding (I’ve written about this in the past at Horizontal Scaling in MySQL – Sharding Followup). Another use case I reference is “temporary application fixes.” While this is definitely a valid use case, I hadn’t personally come across an issue in the wild where the application fix wasn’t trivial.

Recently, a client hit a case where pt-archiver wasn’t able to archive rows from a table that had a bit column as part of a primary key. This is certainly an edge case, but we had hoped the fix was trivial. Unfortunately, the root of the issue was around how the Perl DBI library quotes and handles the bit data type by default.

When …

In this blog post, we’ll look at MySQL 8 user attributes and how we can use them.

What is the user attribute?

A user attribute is a JSON object made up of one or more key-value pairs, and it is set while creating the user with CREATE USER and by including ATTRIBUTE ‘json_object’. json_object must be a valid JSON object (should be key-value pairs).

We all know MySQL stores all user-related data in mysql.user table, but we don’t have any column to add any attributes for the user. With this new feature of user attributes, we can actually add some additional details as an attribute for the user, which is pretty useful in getting some additional details of the user, such as mobile number, job title, country, etc.

The user attribute feature is available from MySQL 8.0.21, and it comes with a USER_ATTRIBUTES table from information_schema, which provides information about the user comments and user attributes. It …

MySQL 5.7 reaches End of Life status this October. If you still need to start your migration, time is getting short. The first step for many is looking into the new reserved words in MySQL 8.0. As MySQL gets new functionality or the project matures, there are new additions to the list of reserved words you can not use as column names.

Reserved words added to 8.0

There is a list of the new reserved words later in this document that you need to review.

Odds are you are not using a column named Master_tls_ciphersuites, but what about Rank, System, Skip, or Lead? Those are a lot more common and may be in your table definitions, so your upgrade process will be harder. It is recommended that you use the util.checkForServerUpgrade() in the MySQL Shell to check for these Reserved Words.

So what happens if I use a reserved word?

A partitioned table in MySQL has its data separated into different tablespaces while still being viewed as a single table. Partitioning can be a useful approach in some cases when handling huge sets of data. Deleting huge data sets could be quickened up in a partitioned table, but if not handled properly, it can misplace your data in the table. In this blog, I will share how to check and fix the data in such a table with minimal disruption to the table.

In this example, we use a table partitioned based on a date range.

mysql> show create table salariesG
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(from_date)
(PARTITION p01 VALUES LESS …

On MySQL and Percona Server for MySQL, there is a schema called information_schema (I_S) which provides information about database tables, views, indexes, and more.

A lot of useful information can be retrieved from this schema, for example, table metadata and foreign key relations, but trying to query I_S can induce performance degradation if your server is under heavy load, as shown in the following example test.

Disclaimer: This blog post is meant to show a less-known problem but is not meant to be a serious benchmark. The percentage in degradation will vary depending on many factors {hardware, workload, number of tables, configuration, etc.}.

Test

The test compares a baseline of how the server behaves while “under heavy load but no queries against I_S” vs. ” under heavy load + I_S queries” to …