This blog post is inspired by Jesper's recent blog post on how the automatic update of InnoDB persistent statistics may never trigger if servers are restarted frequently. However, the matter is even worse! In this blog post I will show that even when the automatic recalculation is performed, there are no guarantees as to when the server will see the changes.

Myths about persistent statistics

I must admit that I, for a long time, believed in the myths I was told about when updates to InnoDB's index statistics become visible to the query optimizer. There were basically two variants:

• The updated statistics will only be visible to new connections (i.e., sessions that are started after the statistics was updated)
• When a new connection accesses the table, the updated statistics …

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

This is the sixth episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

In this episode, we’ll introduce the concept of Security Lists a virtual firewall to control traffic at the network packet level.

The post Discovering MySQL Database Service – Episode 6 – Update the Private Subnet Security List first appeared on dasini.net - Diary of a MySQL expert.

In part 1 of the series about Point-in-Time Recovery in OCI MDS, we saw how to stream the binary log to Object Storage.

In this blog post, we will see how we can restore the data up to certain point.

The high level process is simple:

1. we need to restore the last backup just before the point we want to return to
2. find the last GTID that was executed in that backup (not 100% mandatory but can accelerate the process)
3. choose what is the objective:
   1. just skip one transaction (following transaction could depend on it)
   2. recover up to that transaction and skip all the following ones
4. replay the binary logs taking in consideration the chosen option of point 3

The scenario …

Since MySQL 5.6 InnoDB has supported persistent index statistics. This means that when you restart MySQL, InnoDB does not have to recalculate the statistics for the tables, rather it can read the statistics from its persistent storage. This has several advantages over the transient statistics, but as it turns out, there is also a catch: MySQL may under some circumstances never get around to update the index statistics. This particularly affects instances that are restarted frequently and tables with a large number of rows.

Information

Persistent statistics are the default, so unless you have explicitly chosen not to use them, then you are.

Background

Before I dive into why there are scenarios where persistent statistics are never updated, it is necessary to recap how persistent statistics work. Every time the statistics are updated, the result is stored in the mysql.innodb_index_stats table with …

     The main tool for tuning MySQL queries is the EXPLAIN statement (https://dev.mysql.com/doc/refman/8.0/en/explain.html) and one of the hurdles on the learning curve of the EXPLAIN statement is explaining what EXPLAIN is explaining.  Basically EXPLAIN is prepended to a SELECT, TABLE, DELETE, UPDATE, INSERT or REPLACE statement.  To add to an already steep learning curve is that there are many types of EXPLAIN in MySQL.

Let Me Explain

It is very simple to add EXPLAIN at the very beginning of a query to see how the server wants to execute a query. 

EXPLAIN SELECT col1, col2 FROM my_table;

The output will show the query plan (the actual query the optimizer will instruct the MySQL server to run) and some preliminary information about how the query plan was picked among the many possible options.  Learning to use EXPLAIN to tune queries is a long process and beyond the …

Database architecture and design are becoming an increasingly lost art. With new technologies and the push towards faster development cycles, people continue to take shortcuts, often to the detriment of long-term performance, scalability, and security. Designing how your application stores, accesses, and processes data is so fundamentally important, it can not be overlooked. I want people to understand that early design choices can have a profound impact on their applications. To that end, I will be exploring database design principles and practices over the next several months. I am starting with every developer’s favorite data format: JSON!

It seems that almost every database over the last few years has introduced various degrees of support for storing and interacting with JSON objects directly. While these features are designed to make it easier for application developers to write code faster, the implementations of each implementation …

On MySQL Database Service (aka MDS) on Oracle Cloud Infrastructure (aka OCI), when you create a new instance, you also need to set the credentials for the administrator:

Let’s focus on that account…

Please don’t forget the password, it cannot be recovered !!

First of all that account’s username has some limitations. Indeed, some names are reserved as it’s mentioned in the manual. So you could not use the following usernames:

• administrator
• ociadmin
• ocirpl
• mysql.sys
• mysql.session
• mysql.infoschema

This list can evolve, you can get the list directly from the MySQL DB System creation’s screen:

In fact, those accounts are already existing in the MySQL DB …

Locking is an important concept in databases. They help regulate access to the data, so your SELECT queries return consistent results and DML and DDL statements leave the data and schema in a consistent state. For the data, there are four different transaction isolation levels that influence which locks are taken. The most two commonly used isolation levels are REPEATABLE READ (the default in InnoDB) and READ COMMITTED (the default in some other databases). Both of those are said to provide non-locking reads, but there is a little more to it than that.

Selecting into a user variable causing a lock wait timeout.

One case where reads are always locking is when you explicitly requests locks by adding the FOR SHARE or FOR UPDATE modifiers. However there are also cases where SELECT statements becomes locking due to the way the result of the statement is used. …

MySQL 8.0.26 has been released last week !

As usual, it’s highly advised to read the release notes to get informed about the changes and bug fixed.

MySQL is Open Source and each release contains contributions from our great Community. Let me thanks all the contributors on behalf of the entire MySQL Team: Thank you !

MySQL 8.0.26 includes contributions from Venkatesh Prasad Venugopal, Zhai Weixiang, Miron Balcerzak, Marc Fletcher, Zheng Lai, Huqing Yan, Lee William, Brian Yue, Marcus Ekström, Adam, Cable, Garen Chan, Xiaoyu Wang, Marcelo Altmann and Facebook.

Once again, thank you all for your great contributions.

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

Replication …

When I wrote the book MySQL Concurrency I included a Python module for MySQL Shell that would help reproducing the examples in the book. Since things change, it has been necessary to update the code. In this blog I will explain what the changes are which also give me a chance to say thanks to those that have submitted pull requests.

MySQL Concurrency

Version v1.1 was mostly about correcting the directory structure of the repository which was not as it was meant to be – and different from the instructions in the book. Additionally some files with code listings and images were missing. Finally, I added a check whether the set_current_schema() method is available for the session in the load() method. This check is needed as only the new X Protocol (the …