The latest from the MySQL community

     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 …

I’ve been doing a lot of database stuff lately, and not much PHP, so when I returned to make my first Laravel project for a while, I had to check the docs to remind myself how some of this works. I noticed that the default approach to database credentials is still to use separate credentials for the host, port, and other variables. I’m using Aiven databases (because I work there and managed databases are great for demo apps as well as real ones!) which supply connection strings, but Laravel supports these too.

For MySQL, the database connection string from Aiven is in the format:

mysql://username:password@hostname:port/dbname

You can remove the configuration for DB_* and instead use the variable DATABASE_URL with the full string.

Pro-tip: Laravel’s built …

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 …

MySQL, an open source database developed by Oracle, powers some of Facebook’s most important workloads. We actively develop new features in MySQL to support our evolving requirements. These features change many different areas of MySQL, including client connectors, storage engine, optimizer, and replication. Each new major version of MySQL requires significant time and effort to migrate our workloads. The challenges  include:

• Porting our custom features to the new version
• Ensuring replication is compatible between the major versions
• Minimizing changes needed for existing application queries
• Fixing performance regressions that prevent the server from supporting our workloads

Our last major version upgrade, to MySQL 5.6, took more than a year to roll out. When version 5.7 was released, we were still in the …

Percona Toolkit + pt-table-checksum + pt-table-sync = Faster Replica Recovery

Asynchronous replication with MySQL is a tried and true technology. Add the use of GTID’s and you have a very stable solution.

The fundamental issue with async replication is that writes sent to the Replica are not guaranteed to be written. I have only seen a handful of times when writes did not get applied to the replica. Most of the time this happens is due to network packet drops or a replica crashes before new data is committed.

I can remember long nights of restoring backups of the primary to the replica’s. Not a painful process but time consuming.

Please take a few moments to review the full documentation of both tools before trying this example on live data: pt-table-checksum, pt-table-sync.

With …

Somebody needed a job queue in Python: Multiple writers insert into it in random order, and the jobs are written into the MySQL table jobs. From the jobs table, multiple consumers claim jobs in batches of n or smaller (n=100), and process them. After processing, the consumers delete the jobs. We need concurrent job generation and consumption, with proper and efficient locking.

The full source for this example can be seen in mysql-dev-examples

in mysql-claim-jobs.py

.

Base Program

Using our usual includes and setup,

from time import sleep
from random import …