Some times it’s important to know the size of a transaction, especially when you plan to migrate to a HA solution where by default transactions have a limited size to guarantee an optimal behavior of the cluster.

Today we will see the different possibilities to have an idea of the size of transactions.

First we need to split the transaction in two types:

• those generating data (writes, like insert, delete and update, DML)
• those only ready data (select, DQL)

To implement High Availability, only the first category is important.

Size of DML

To know the size of a DML transaction, the only possibility we have is to parse the binary log (or query the binlog event).

We need to check the binlog event from the binlog file and then calculate its size. To illustrate this, let’s try to find the transaction identified by a specific GTID: …

One of the most frustrating experiences when dealing with databases is when you've designed the perfect index, but MySQL still doesn't use it. There are several reasons why this could be the case, and in this article, we'll explore some of the most common ones.

Throughout this article, we'll be working with a very simple people table that looks like this:

CREATE TABLE `people` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `first_name` (`first_name`),
  KEY `state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

We'll be adding and dropping keys throughout to show different scenarios, but this is a good starting place.

…

In this blog post, we will see how to persist the password inside the ProxySQL mysql_users table in hashed format only. Also, even if someone stored the password in cleartext, we see how to change those into the hashed format easily.

Here we are just highlighting one of the scenarios during work on the client environment where we noticed that the ProxySQL mysql_users table had more than 100 user entries, but some of them were available/inserted into the clear text password, whereas some were inserted properly into hashed entries.

Before just explaining those simple commands that were used to fix those clear text entries into the hashed entry quickly, let’s see some more information about the ProxySQL mysql_users table and the password formats.

Password formats inside ProxySQL

ProxySQL is capable of storing passwords in two different formats within the mysql_users.password …

The KubeCon around the Kubernetes technology was one of the events I dreamed to attend since I’m focusing on the CloudNative solutions. I had the great opportunity to attend the KubeCon & CloundNativeCon in Amsterdam with my colleague Benoît Entzmann.

There is now CNCF-hosted Co-located events adding more topics and interesting sessions. This is community-driven, vendor-neutral events hosted and managed by the CNCF. ArgoCD, Cilium, Linkerd have now their own conference. Here is the list of CNCF-hosted Co-located events.

Unfortunately, all the above events were already sold out when we decided to come at the KubeCon.

During the 1st day Keynote, Chris Aniszczyk CTO of the CNCF mentioned that for this year, the increase of the number of participants was just amazing reaching more than 10’000 people. The number of CNCF projects also increased a lot. I feel that the main message of this Keynote is that …

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) …

Codership is pleased to announce a new Generally Available (GA) release of the multi-master Galera Cluster for MySQL 5.7, consisting of MySQL-wsrep 5.7.41 (release notes, download) and MySQL-wsrep 8.0.32 (release notes, download). This release incorporates all changes to MySQL 5.7.41 and MySQL 8.0.32 respectively, adding a …

Thank you to everyone who joined us for the 20th edition of MyWebinar. We hope that our time together helped to elevate and seam your work to perfection. We’re committed to giving back to the opensource database community in the coming days by presenting more LIVE events.

Monu Mahto, Database Consultant from Mydbops was successful in making this event more interactive, engaging, and, most importantly, productive for the audience. Thank you for your continuous contribution to the opensource community.

Topic: MySQL Data Encryption at Rest

Date: 25th of March, 2023

Time: 11 AM IST

The most important key takeaways are:

What is Data-at-rest Encryption ?

Data at rest means all inactive data stored on disk. …

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 …

In the MySQL world, EXPLAIN is a keyword used to gain information about query execution. This blog post will demonstrate how to utilize MySQL EXPLAIN to remedy problematic queries.

On the Technical Solutions team here at PlanetScale, we frequently talk with users who seek advice regarding query performance. Although creating an EXPLAIN plan is relatively simple, the output isn’t exactly intuitive. It’s essential to understand its features and how to leverage it best to achieve performance goals.

EXPLAIN vs. EXPLAIN ANALYZE

When you prepend the EXPLAIN keyword to the beginning of a query, it explains how the database executes that query and the estimated costs. By leveraging this internal MySQL tool, you can observe the following: …

The End-of-Life (EOL) date for MySQL 5.7 is scheduled for October 2023, which means that after that date, MySQL 5.7 will no longer receive updates, bug fixes, or security patches. This does not mean that MySQL 5.7 will stop working after the EOL date, but it does mean that any issues or vulnerabilities discovered after that date will not be addressed, This can leave your database at risk of security breaches or performance issues. AWS RDS support for MySQL will also hold good only till October 2023.

To avoid these potential issues, it is recommended to upgrade to a newer version of MySQL before the EOL date for MySQL 5.7. Upgrading to a newer version such as MySQL 8 …