A short introduction to the new MySQL Shell for VS Code extension.

PlanetScale Rewind is the world’s first data migration time machine. Revert changes to your database after a migration with no downtime and zero data loss.

Read the full story

Learn how we used VReplication to allow for migration reverts with data retention.

Read the full story

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 ( …

In MySQL, the @@sql_mode parameter should generally use ONLY_FULL_GROUP_BY. If it doesn’t include it and you don’t have the ability to change the database parameters, you can use a MySQL PSM (Persistent Stored Module), like:

Create the set_full_group_by procedure:

-- Drop procedure conditionally on whether it exists already.
DROP PROCEDURE IF EXISTS set_full_group_by;

-- Reset delimter to allow semicolons to terminate statements.
DELIMITER $$

-- Create a procedure to verify and set connection parameter.
CREATE PROCEDURE set_full_group_by()
  LANGUAGE SQL
  NOT DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT 'Set connection parameter when not set.'
BEGIN

  /* Check whether full group by is set in the connection and
     if unset, set it in the scope of the connection. */
  IF NOT EXISTS
    (SELECT NULL
     WHERE  REGEXP_LIKE(@@SQL_MODE,'ONLY_FULL_GROUP_BY'))
  THEN
    SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
  END IF; …

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 …

As part of my ongoing series around MySQL 8 user administration, I’d like to cover one of the new features introduced in MySQL 8.0.27 – multi-factor authentication. In order to establish identity, multi-factor authentication (MFA) is the use of multiple authentication values (factors) during the MySQL authentication process.

Introduction

MFA provides greater security compared to a single-factor authentication method, which has historically been based on simple methods such as password authentication. With MFA, additional authentication methods are enabled, such as requiring multiple passwords, or with devices such as smart cards, security keys, or biometric readers.

As of MySQL 8.0.27, it is now possible to require up to three authentication values to establish identity. In addition to the more common 2FA (two-factor authentication), MySQL can now also support 3FA (three-factor authentication) to complement the …

This blog post is an excerpt from premium MySQL Beginner content I am creating to help anyone learn how to use MySQL. Thank you for reading and please do provide feedback.

Image by Willi Heidelbach from Pixabay 

You may also be interested in the blog post, MySQL Beginners Series — The SELECT statement, which is a primer for this article.

Limit …