The latest from the MySQL community

Codership is pleased to announce a new Generally Available (GA) release of the multi-master Galera Cluster for MySQL 8.0, consisting of MySQL-wsrep 8.0.36-26.18 (release notes, download). There is no change to the Galera replication library 4.18 (release notes, download) implementing wsrep API version 26. This release incorporates all changes to MySQL 8.0.36, adding a synchronous option for your MySQL High Availability solutions.

This is a …

A support question: Somebody had a WordPress installation, in which a table had entries with an id column that contained multiple entries with 0. The table was supposed to undergo a schema change where id becomes an actual primary key, and auto_increment. They needed to find all rows WHERE id=0 and assign them unique id values.

Here is a test table:

mysql> create table b ( id integer not null, d varchar(255));
Query OK, 0 rows affected (0.25 sec)

kris> insert into b values (0, "1"), (0, "2"), (3, "3");
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

kris> select * from b;
+----+------+
| id | d    |
+----+------+
|  0 | 1    |
|  0 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

…

A support question: Somebody had a WordPress installation, in which a table had entries with an id column that contained multiple entries with 0. The table was supposed to undergo a schema change where id becomes an actual primary key, and auto_increment. They needed to find all rows WHERE id=0 and assign them unique id values.

Here is a test table:

mysql> create table b ( id integer not null, d varchar(255));
Query OK, 0 rows affected (0.25 sec)

kris> insert into b values (0, "1"), (0, "2"), (3, "3");
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0

kris> select * from b;
+----+------+
| id | d |
+----+------+
| 0 | 1 |
| 0 | 2 |
| 3 | 3 |
+----+------+
3 rows in set (0.00 sec)

The old, …

When considering the performance of any software, there's a classic trade-off between time and space. In the process of assessing the performance of a MySQL query, we often focus on execution time (or query latency) and use it as the primary metric for query performance. This is a good metric to use, as ultimately, we want to get query results as quickly as possible.

I recently released a blog post about how to identify and profile problematic MySQL queries, with a discussion centered around measuring poor performance in terms of execution time and row reads. However, in this discussion, memory consumption was largely ignored.

Though it may not be needed as often, MySQL also has built-in mechanisms for gaining a deep understanding of both how much memory a query is using and also what that memory is being used for. Let's …

The Oracle Cloud Infrastructure (OCI) command line interface (CLI) allows users to manage OCI resources. In this post, we will discuss how to use the OCI CLI to retrieve a list of MySQL HeatWave instances, retrieve more detailed information for a specific instance, and update properties of that instance.

This is the second post in a series dedicated to showing how to use the OCI CLI to manage MySQL HeatWave resources. In this post we will discuss how to list MySQL HeatWaves instances in a compartment and how to update some of the information pertaining to the instance. Prerequisites Before you can run any of the examples below, […]

Oracle offers a variety of SDKs for interacting with Oracle Cloud Infrastructure resources. In this post we discuss how to update some propertied of a backup of a MySQL HeatWave instance.

This post is the sixth in a series that will demonstrate how to view and manage MySQL HeatWave instances in Oracle Cloud Infrastructure (OCI). Oracle offers several SDKs that can be used to view and manage resources in OCI. In this post, we will discuss how to leverage the TypeScript/JavaScript SDK to programmatically update data of a backup of a MySQL HeatWave instance. […]

I’ve been using Alan Beaulieu’s Learning SQL to teach my SQL Development class with MySQL 8. It’s a great book overall but Chapter 12 lacks a complete exercise. Here’s all that the author provides to the reader. This is inadequate for most readers to work with to solve the concept of a transaction.

Exercise 12-1

Generate a unit of work to transfer $50 from account 123 to account 789. You will need to insert two rows into the transaction table and update two rows in the account table. Use the following table definitions/data:

                      Account:
account_id     avail_balance    last_activity_date
-----------    --------------   ------------------
       123               450    2019-07-10 20:53:27
       789               125    2019-06-22 15:18:35

                      Transaction:
txn_id    txn_date      account_id    txn_type_cd    amount
------    ----------    -------+--    -----------    ------
  1001 …

Though we try our best to avoid it, it's easy to let underperforming queries slip through the cracks in our workloads, negatively impacting the performance of a database system. This is especially true in large-scale database environments, with many gigabytes or terabytes of data, hundreds of tables, and thousands of query patterns being executed on a daily basis.

Thankfully, MySQL has the ability to collect data that can be leveraged for identifying problematic queries, and can also do profiling on them in order to drill into their poor performance. In this article, I'll go over several built-in techniques for how to do this in native MySQL. If you use PlanetScale, this type of information can be gathered more easily and intuitively using the PlanetScale Insights dashboard. I'll include a brief discussion of this feature later on.

For this …