Once again, the latest version of MySQL 8.0 includes several contributions from the MySQL Community.

MySQL 8.0.31 was released on October 11th 2022.

I would like to thank all contributors on behalf of the entire Oracle MySQL team !

This new release contains patches from Facebook/Meta, Dennis Gao, Lou Shuai, Caza Zhang, Zhang Simon from Tencent, Dimitry Kudryavtsev, Rahul Malik from Percona, Alex Xing, Marcelo Altmann from Percona, Matthew Steeples, Adam Croot, Luis Pinto and Evgeniy Patlan from Percona for all his patches on MySQL Shell.

Here is the list of the above contributions and related bugs:

Replication

• #101056 – Ack_receiver may lost the slave semi-sync ack due to net timeout – Dennis Gao …

In the previous part, we created our application and our 2 functions.

Now we need to create an API Gateway to be able to call these functions from outside OCI. Using a third party scheduler, our laptop, etc…

Before creating the gateway, we need to create some policy to allow the API Gateway to call our functions. I’ve tried to create the policy after, it seems to not work as expected.

Security

Dynamic Group

We need to create a Dynamic Group that will match our gateway:

The matching rule is checking the type of resource that should be ApiGateway and my compartment’s id:

All {resource.type='ApiGateway', resource.compartment.id = 'ocid1.compartment.oc1..xxxxxxxxx'}

Policy …

In my previous post, I explained how to deal with Performance_Schema and Sys to identify the candidates for Query Optimization but also to understand the workload on the database.

In this article, we will see how we can create an OCI Fn Application that will generate a slow query log from our MySQL Database Service instance and store it to Object Storage.

The creation of the function and its use is similar to the one explained in the …

If like me you are an old experienced MySQL DBA, to analyze your MySQL workload, you certainly have used the slow query log with long_query_time set to 0.

The slow query log is a file that contains all the queries whose execution time is greater than the value of long_query_time. This file can be huge and uses up all available disk space on busy systems.

Constantly writing to that file can also add an overhead to the server.

For those reasons, the slow query log is not available in MySQL Database Service (HeatWave) in OCI.

Plan B ?

As a DBA, what are my options for finding the queries that need to be optimized? As usual, the queries that consume the most time are the ones that need some attention

It can be a very long query or a short query executed too many times.

Currently the MySQL DBAs use …

Let’s continue our journey of deploying the MySQL Database System on OCI with Terraform.
This time we will see how we can use a backup (see [1] and [2]) as a source (initial data) for a new instance.

Within the oci_mysql_mysql_db_system it’s possible to define a source detailing how to provision the initial data of the db system.

Let’s deploy a new MySQL Database Instance using a manual backup we made earlier:

We can also find the backup’s ocid using the oci cli command:

 $ oci mysql backup list …

Today I would share a nice book related to MySQL 8.0 for our Chinese users.

The original title of this book by Scott Yao, is MySQL 8.0 运维与优化

I had the chance to read parts of the book before it was published to have my comment on the back cover.

This book is very detailed with many examples on how to administer a MySQL server.
It covers the new features of MySQL 8.0 and Scott illustrates perfectly how to take advantage of them.

Reading the book, it is obvious that the author is an experienced MySQL DBA and is sharing his experience that many junior DBAs can certainly benefit from.

This is a very technical book, as I like, I really recommend it to all Chinese people and I hope to see it translated into English too.

And finally I would thanks Scott for having sent to me a hard signed copy of the …

Let’s continue the discovery of the MySQL Database Resource when deploying on Oracle Cloud Infrastructure using Terraform.

Last week, we saw how to create custom configurations and define user variables. Today we will see how we can define a backup policy and a maintenance window.

Backup Policy

In the oci_mysql_mysql_db_system resource, we will add a new section called backup_policy like this:

backup_policy {
       is_enabled        = "true"
       retention_in_days = "3"
       window_start_time = "01:00-00:00"
       freeform_tags = {"backup_defined_by"="Terraform"}
       pitr_policy {
            is_enabled = "true"
       }
}

This part of code (you can see in a working Terraform architecture sample), enables backup, sets the retention days to 3. It also defines the starting time …

More and more people are using UUID’s to identify records in their database.

As you already know, for MySQL’s storage engine (InnoDB) the primary key is very important ! (for performance, memory and disk space).

See the following links:

• https://lefred.be/content/mysql-invisible-column-part-ii/
• https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html (GIPK mode in MySQL 8.0.30 !)

Problems

There are 2 major problems having a UUID as Primary Key in InnoDB:

1. generally they are random and cause clustered index to be rebalanced
2. they are included in each secondary indexes (consuming disk and memory)

Let’s have a look at …

You know how much I praise and like MySQL Shell but if like me, for you too, old habits die hard, I advise you to create these different aliases in your ~/.bashrc (or ~/.bash_aliases) file to force yourself to use MySQL Shell even for a small statement check:

alias mysql="mysqlsh --sql mysql://localhost"
alias mysqlx="mysqlsh --js mysqlx://localhost"

Of course you can specify the user you want, by default it uses the same user as the system one.

For example, if this is your test machine and you want to always use the root account (even if not recommended), you can specify it like this by modifying the URI:

alias mysql="mysqlsh --sql mysql://root@localhost"

Example:

So now when using mysql MySQL Shell is launched and it connects directly to localhost in SQL mode using the classic protocol.

With …

Recently, somebody asked me how he can find the long running transactions in MySQL.

I already have one MySQL Shell plugin that allows you to find the current transactions sorted by time. The plugin allows you to also get the details about the desired transaction. See check.getRunningStatements().

Let’s see how we can easily find those long transaction that can be a nightmare for the DBAs (see MySQL History List Length post).

SELECT thr.processlist_id AS mysql_thread_id,
       concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
       Command,
       FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
       current_statement as `latest_statement`
  FROM …