MySQL 8.0.31 added INTERSECT and EXCEPT to augment the long-lived UNION operator. That is the good news. The bad news is that you have to be careful using the EXCEPT operator as there is a trick.

Let’s start with some simple tables and load some simple data.

SQL > create table a (id int, nbr int);
Query OK, 0 rows affected (0.0180 sec)
SQL > create table b (id int, nbr int);
Query OK, 0 rows affected (0.0199 sec)
SQL > insert into a (id,nbr) values (1,10),(3,30),(5,50),(7,70);
Query OK, 4 rows affected (0.0076 sec)

Records: 4  Duplicates: 0  Warnings: 0
SQL > insert into b (id,nbr) values (1,10),(2,20),(3,30),(4,40);
Query OK, 4 rows affected (0.0159 sec)

Records: 4  Duplicates: 0  Warnings: 0

So each table has four rows of data with two rows – (1,10) and (3,30) – appearing in both.

SQL > select id,nbr from a;
+----+-----+
| id | nbr |
+----+-----+
|  1 |  10 |
|  3 |  30 |
|  5 |  50 |
|  7 |  70 | …

Are you familiar with the UNION statement for your SQL queries? Most likely, you are. It has been supported for a long time.

In case you are not familiar with UNION, don’t worry, I’m going to show you how it works with simple examples.

Considering “Set Theory”, other than the UNION, starting from the newly deployed MySQL Community 8.0.31, a couple of new clauses are available: INTERSECT and EXCEPT.

From now on you can rely on more powerful options to write your queries and deploy some Set Theory logic.

In this article, I’m going to present how the three options work.

The traditional UNION

UNION combines the results from two or multiple SELECT statements into a single result set. Let’s suppose you have two queries, A and B, they return the same number of columns with the same data types and you need to merge all rows from both queries into a single result set. You can …

Oracle releases updates to MySQL on a quarterly basis and the Release Notes for 8.0.31 arrived just before the software.  This time around there are some very interesting new features that will be handy including SQL standards support, as well as over 130 bug fixes.

The TL;DR:  Some nice new features but nothing spectacular.

• FULL is now a reserved word.
• InnoDB now supports parallel index builds, which improves index build performance. In particular, loading sorted index entries into a B-tree is now multithreaded.
• The OpenSSL library for MySQL Server has been updated to version 1.1.1q.
• The optimizer has been improved so that the old ER_NOT_SUPORTED_YET is not thrown when you try something like this:
  

      ((SELECT a, b, c FROM t ORDER BY a LIMIT 3) ORDER BY b LIMIT 2) ORDER …

A few weeks back, we faced an issue in a replication environment for a Managed Services client:

LAST_ERROR_MESSAGE: Worker 2 failed executing transaction ‘UUID:GTID’ at master binlog.0012345, end_log_pos 98765; Error ‘Operation CREATE USER failed for ‘test_user’@’10.10.10.10” on query. Default database: ‘mysql’. Query: ‘CREATE USER ‘test_user’@’10.10.10.10’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘************”

After some initial investigation, we noticed that the user in the replica didn’t exist! Was MySQL going crazy? But then the customer mentioned they had the following error in the primary before being able to execute the query successfully:

root@localhost [mysql]> CREATE USER 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY …

When you develop an application you expect success, and often success comes with growth problems.  These problems especially show themselves in the area of data storage, where being stateful is not as easy to scale as the stateless parts of the application.

There are several stages of approaching database scalability:

1. Configuration and query optimization. This step can help a lot, and I would recommend a recent book by Daniel Nichter “Efficient MySQL Performance: Best Practices and Techniques”, which goes into this topic.
2. If #1 is done and you continue to push the limits of your database, the next step is to improve the hardware: adding extra memory, improving storage throughput (regular SSD, NVMe storage layer, etc.), or increasing the size of the cloud instances (this is what I call “optimization by credit card”). This typically should help, but only to a certain limit. And there is only so much …

Do you wonder if MySQL tells you the truth about writes to tables that have foreign key constraints? The situation is complex, and getting visibility on what is really happening can be a problem.

I found this issue intriguing and decided to share and highlight some examples.

Query Plan

Let us take this example table:

CREATE TABLE `product` (
  `category` int NOT NULL,
  `id` int NOT NULL,
  `price` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`category`,`id`)
) ENGINE=InnoDB;

We want to know how costly an example UPDATE against this table will be:

mysql > EXPLAIN update product set id=id+1 where id=65032158 and category=3741760\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: product
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const,const
         rows: 1 …

MySQL Shell For VS Code integrates the MySQL Shell directly into VS Code development workflows and was released last week. This extension to the popular VS Code platform enables interactive editing and execution of SQL for MySQL Databases and optionally the MySQL Database Service for several simultaneous sessions.  It is a preview release and not ready for production but it does have several features that may make the MySQL GUI of choice.

Installation

The installation itself is easy but you will need to download the code from here and not the usual places for MySQL products.  You will, of course, have to have VS Code installed first, and be warned that some of the more tantalizing links for things like documentation are not connected.

MySQL Shell for VS Code installation screen and yes, you will need VS Code installed first.

Usage

The interface is familiar to that of MySQL Workbench but …

The utf8mb4 character set is the new default as of MySQL 8.0, and this change neither affects existing data nor forces any upgrades.

Migration to utf8mb4 has many advantages including:

• It can store more symbols, including emojis
• It has new collations for Asian languages
• It is faster than utf8mb3

Still, you may wonder how migration affects your existing data. This blog covers multiple aspects of it.

Storage Requirements

As the name suggests, the maximum number of bytes that one character can take with character set utf8mb4 is four bytes. This is larger than the requirements for utf8mb3 which takes three bytes and many other MySQL character sets.

Fortunately, utf8mb3 is a subset of …

Oracle released MySQL 8.0.28 on January 18th with little fanfare, as part of their four times a year release cycle.  So what is included in the new release? Over the past few years, there have been some cool new features included in these ‘dot’ releases that some in the community say would have been better off being labeled as a major release. But what is in .28? Below are the more interesting changes in the database server and the shell, as there was not a whole lot changed in the other products such as router or MEM… I put my asides in italics and my views probably do not reflect the views of anyone else.

And remember, 8.0.29 is due in April.

The TL;DR

The TL;DR synopsis is that a lot of excellent work went into MySQL Server & shell 8.0.28 but the pressing question is do you really need to install it right away?  On the scale from ‘you probably should wait to upgrade’ to ‘update ASAP’ it …

KMIP (Key Management Interoperability Protocol) is an open standard developed by OASIS (Organization for Advancement of Structured Information Standards) for the encryption of stored data and cryptographic key management.

Percona Server for MySQL 8.0.27 and Percona XtraBackup 8.0.27 now include a KMIP keyring plugin to enable the exchange of cryptographic keys between a key management server and the database for encryption purposes. The procedure to use them with HashiCorp Vault Enterprise is described below.

Install Hashicorp Vault Enterprise

We will first install Hashicorp Vault Enterprise on Ubuntu Linux “Bionic” and then enable …