The latest from the MySQL community

Anyone familiar with Oracle performance tuning knows about ASH , aka active session history. ASH saves a history of every session that is active every collection, which by default is once per second. Once per second is fast enough to give a clear view of database load.

The ASH data comes from v$session which is a structure that contains information on every connection and shows which connections are active, what SQL they are running (SQL_ID) and if they are runnable on CPU or waiting on a wait_event for some resource like an I/O to complete or a lock or latch. It’s a pretty simple select query on v$session to select who is active,  their wait_event and SQL_ID.

On Oracle that gives us a query like:

select sid||':'||serial# session_id,
       username,
       s.sql_id||':'||sql_child_number sqlid,
       decode(state, 'WAITING', wait_class||':'||event, 'CPU') event
from v$session s
where   (( s.wait_time != 0  /* on CPU  */ …

On 22nd March 2022 08:43 UTC, we became aware of the issue affecting Okta, a third-party identity provider that Percona uses for https://id.percona.com. Initially, there was no statement from Okta, so our Security Operations team reviewed the information available from LAPSUS$ and other public sources.

Based on the public information available about the issue, we evaluated the potential exposure to Percona and determined that the impact was minimal. Percona uses Okta integrations so https://id.percona.com can be used to authenticate against Percona’s deployments of:

• forums.percona.com (Discourse)
• percona.service-now.com (ServiceNow) 
• portal.percona.com (Dashboard portal interface where users & clients can add their PMM integration). 

Integrations of …

How does one use  NESTING_EVENT_ID?

Below I’ve selected from  events_waits_history_long for a single thread, but none of the  NESTING_EVENT_ID values show up in EVENT_ID or END_EVENT_ID.

To simplify things , I’m just showing the data for one THREAD_ID listed in order of TIMER_START.

mysql>  select 
            THREAD_ID,
            EVENT_ID,
            END_EVENT_ID,
            NESTING_EVENT_ID,
            NESTING_EVENT_TYPE,
            EVENT_NAME,
            SOURCE,
            TIMER_WAIT,
            SPINS,
            OBJECT_SCHEMA,
            OBJECT_NAME,
            INDEX_NAME,
            OBJECT_TYPE,
            OBJECT_TYPE,
            OBJECT_INSTANCE_BEGIN,
            OPERATION,
            NUMBER_OF_BYTES,
            FLAGS 
 from 
           performance_schema.events_waits_history_long 
 where 
            THREAD_ID in ( select max(thread_id) from performance_schema.events_waits_history_long where …

For a very long part of its history, MySQL replication has been limited in terms of performance. Because there was no way of knowing if transactions or updates were independent, the updates had to be executed on a replica following the exact same sequence of operations as on the primary server. The only way to guarantee the same sequence of operations on the replica was to use a single thread. In this post, we’ll do a dive into the MySQL multi-threaded replication (MTR) implementation and explore the available tuning options.

MTR is the culmination of the evolution in the development of parallel replication which followed the path:

1. Single-threaded replication
2. Per-database replication
3. Logical clock replication

We’ll leave aside, for now, the recent dependency tracking feature.

Context

Before we discuss the multi-threaded implementation, let’s review in …

I record this for posteriority without much comment.

The error message “Truncated incorrect DOUBLE value” when issued by MySQL can be a confused parser, and masks unintended barely legal syntax:

kris@localhost [kris]> select * from testtable;
+----+------+
| id | d    |
+----+------+
|  1 | eins |
|  2 | zwei |
|  3 | drei |
+----+------+
3 rows in set (0.00 sec)
 
kris@localhost [kris]> update testtable set d="vier" and id = 4 where id = 3;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'vier'

The actual problem here is the incorrect use of and in the set-clause of the update statement when a comma was intended.

kris@localhost [kris]> update testtable set d="vier", id = 4 where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Technically, an and is possible and legal, if you …

When using MySQL with Python, you may want to use the mysqlclient library, which is what most people do and which will work just fine. Or you are using the official MySQL 8 Connector/Python package, which will behave slightly differently, but maybe supports the unique MySQL 8 feature already that is not in mysqlclient, yet.

Your SQL may be hand-writtten, or it may be generated using SQL Alchemy, Django or some other package. If the latter is the case, it may be useful to be able to see the actual SQL string that has been sent to the database in order to facilitate interactive debugging.

Using mysqlclient

If you are using mysqlclient with Python, the class internally sends the SQL to the server in a method _query(self, q) in the Cursor class ( …

Question: how do I know that one user is waiting for a lock and another  user is burning CPU on MySQL?

i.e. how can I create active session history on MySQL like on Oracle?

Two simple cases I’m looking at

1. session waiting on lock
   1. The wait event is ” wait/io/table/sql/handler” which happens for a number of potential reasons
   2. -> I can join to information_schema views INNODB_LOCK_WAITS & INNODB_TRX but this is heavy, and I’d rather only access those after I know there is a lock waiter.
2. session burning CPU
   1. How do I know from performance schema views that the connection is active and burning CPU?
   2. It has a wait event that shows up wait/synch/mutex/sql/THD::LOCK_thd_data
   3. on Oracle to determine CPU, we just …

In keeping with my MySQL 8 user administration and security theme, I’d like to discuss the password verification-required policy introduced in MySQL 8.0.13. With this feature, it is possible to require that attempts to change an account password be verified by specifying the existing current password to be replaced.

Introduction

The password verification-required policy enables DBAs to prevent users from changing a password without proving that they know the current password. When would this happen? Such changes could occur if a user walked away from a terminal session without logging out, and a malicious user uses the open session to change the original user’s MySQL password. As you can imagine, this could have disastrous consequences:

• The original user is now unable to access MySQL until an administrator can reset the account password. 
• The malicious user can access MySQL until a password …

Oracle released MySQL 8.0.28 on January 18th with little fanfare, as part of their four times a year release cycle.  So what is included in the new release? Over the past few years, there have been some cool new features included in these ‘dot’ releases that some in the community say would have been better off being labeled as a major release. But what is in .28? Below are the more interesting changes in the database server and the shell, as there was not a whole lot changed in the other products such as router or MEM… I put my asides in italics and my views probably do not reflect the views of anyone else.

And remember, 8.0.29 is due in April.

The TL;DR

The TL;DR synopsis is that a lot of excellent work went into MySQL Server & shell 8.0.28 but the pressing question is do you really need to install it right away?  On the scale from ‘you probably should wait to upgrade’ to ‘update ASAP’ it …

KMIP (Key Management Interoperability Protocol) is an open standard developed by OASIS (Organization for Advancement of Structured Information Standards) for the encryption of stored data and cryptographic key management.

Percona Server for MySQL 8.0.27 and Percona XtraBackup 8.0.27 now include a KMIP keyring plugin to enable the exchange of cryptographic keys between a key management server and the database for encryption purposes. The procedure to use them with HashiCorp Vault Enterprise is described below.

Install Hashicorp Vault Enterprise

We will first install Hashicorp Vault Enterprise on Ubuntu Linux “Bionic” and then enable …