The latest from the MySQL community

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 …

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 randint
from sys import exit
from multiprocessing import Process

import click
import MySQLdb
import MySQLdb.cursors …

As some of you likely know, I have a favorable view of ZFS and especially of MySQL on ZFS. As I published a few years ago, the argument for ZFS was less about performance than its useful features like data compression and snapshots. At the time, ZFS was significantly slower than xfs and ext4 except when the L2ARC was used.

Since then, however, ZFS on Linux has progressed a lot and I also learned how to better tune it. Also, I found out the sysbench benchmark I used at the time was not a fair choice since the dataset it generates compresses much less than a realistic one. For all these reasons, I believe that it is time to revisit the performance aspect of MySQL on ZFS.

ZFS Evolution

In 2018, I reported ZFS performance results based on version 0.6.5.6, the default version available in Ubuntu Xenial. The present post is using …

MySQL 8 brought many highly anticipated features, with support for user roles, a new shell, a more robust data dictionary, and better SQL support, just to name a few. There are lesser-known new features, however, that aim to reduce overall DBA workload and streamline management processes – and one of these is support for dual passwords, first implemented in MySQL 8.0.14. User accounts are now permitted to have dual passwords, with a designated primary and secondary. This makes it possible to seamlessly perform user credential changes even with a large number of servers, or with multiple applications connecting to different MySQL servers.

Historically, a MySQL credential change had to be timed so that when the password change was made and propagated throughout the database nodes, all applications that use that account for connections had to be updated at the same time. This is problematic for many reasons, but with database and application …

My students liked the MySQL Transaction post but wanted one that showed how an external web application would interact with MySQL in the scope of a transaction. So, I put a little PHP function together that write across two related tables in the context of a transaction. It uses mysqli (MySQL Improved Extension) to connect PHP to the MySQL database.

The function is barebones and uses the oldest approach of hidden inputs to maintain context between rendered forms using an HTML POST method. The hidden inputs are preceded with “h_” and snake case is used for variable names.

The function only writes to two tables. It writes to the member table and when that completes successfully to the contact table. The function:

• Submits credentials from a file and raises an error when they don’t work.
• Initializes a …