With the latest MySQL release (8.0.31), MySQL adds support for the SQL standard INTERSECT and EXCEPT table operators.

Hello dear MySQL Community !

As you may already know FOSDEM 2023 is again going to be held in-person. FOSDEM will take place February 4th and 5th 2023.

We have also decided to put our pre-FOSDEM MySQL Day on track for a fifth edition.

As for the last edition, the event will be spread over 2 days.

These 2 extra days related to the world’s most popular open source database will take place just before FOSDEM, the 2nd and 3rd February at the usual location in Brussels.

Please don’t forget to register as soon as possible as you may already know, the seats are limited !

Register on eventbrite: https://mysqldays2023.eventbrite.com

And, please don’t forget, that if you have register for the event and you cannot make it, please free back your ticket …

Recently we had a question on getting your Galera Cluster upgraded from MySQL 5.7 to MySQL 8.0. While we have plenty of extensive documentation on Upgrading Galera Cluster, this blog covers the Linux example for a rolling major upgrade (the procedure is documented, but this is more extensive). So we will describe going from MySQL 5.7.23 to MySQL 8.0.28 on one node. We did this on CentOS 7.

Firstly, let’s start by disabling SELinux by executing setenforce 0.

We need to install the appropriate MySQL 5.7 binaries, so we can grab them from releases.galeracluster.com. Since we have decided on MySQL 5.7.23, we should get the packages from …

Every day we use dozens if not hundreds of applications connecting to some kind of data repository. This simple step is normally executed over the network and, given so, it is subject to possible sniffing with all the possible related consequences.

Given that, it is normally better to protect your connection using data encryption like SSL, or at the minimum, make the information you pass to connect less easy to be intercepted.

At the same time, it is a best practice to not store connection credentials in clear text, not even inside a table in your database. Doing that is the equivalent of writing your password on a sticky note on your desk. Not a good idea.

Instead, the main options are either transforming the passwords to be less identifiable via hashing or storing the information in an external centralized vault.

In MySQL, the passwords are transformed to not be clear text, and several different plugins …

Next week, November 16th, I will participate to the MySQL Innovation and Cloud Virtual Day in French.

My colleagues will present what’s new in MySQL 8.0.31 and also summarize all the big news that was announced at Oracle Cloud World in Las Vegas.

Attendees will learn about the MySQL HeatWave offering in OCI.

I will be presenting something that is only available in MySQL on-prem and in OCI as a managed service: MySQL Document Store.

The event is in French and attendees will have the opportunity to discuss and chat with MySQL experts (included Olivier!!) during the event.

Registration is required to attend this free event: Register Here.

On se voit la semaine prochaine !

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

By Mouhamadou Diaw

During a consulting on a customer, we faced the following issue when trying to delete a dbhome

Message:  DCS-10001:Internal error encountered: PRGO-2470 : Working copy “OraDB19000_home1” is involved in an incomplete move or upgrade operation

The result of the job is shown below

[2022-11-08 10:53:35 root@odaserverb]# odacli describe-job -i 671b5899-02ac-45ff-b5af-ee254ef0bc72

Job details
----------------------------------------------------------------
                     ID:  671b5899-02ac-45ff-b5af-ee254ef0bc72
            Description:  Database Home OraDB19000_home1 Deletion with id a1bfe23e-2569-407b-8b87-7af9f9f586bf
                 Status:  Failure
                Created:  October 26, 2022 6:45:49 AM CEST
                Message:  DCS-10001:Internal error encountered: PRGO-2470 : Working copy "OraDB19000_home1" is involved in an incomplete move or upgrade …

Recently, I wrote three articles on how to analyze queries and generate a slow query log for MySQL Database Service on OCI:

• https://lefred.be/content/analyzing-queries-in-mysql-database-service/
• https://lefred.be/content/analyzing-queries-in-mysql-database-service-slow-query-log-part-1/
• https://lefred.be/content/analyzing-queries-in-mysql-database-service-slow-query-log-part-2/

In these post, we were generating a slow query log in text or JSON directly in Object Storage.

Today, we will see how we can generate …

This week’s OpenLampTech newsletter has a great mix of round-up posts for you across the PHP and MySQL backend web space. In just 1 more week, we are 1 year old! How nuts is that?!? Enjoy this week’s publication and share with others.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In OpenLampTech issue #51, we are looking at content covering:

• WordPress keeps the lights on
• PHP Readonly classes
• Overview of the LOMP stack
• Headless Drupal in the CMS space
• And much much more

A free …

Continuing on from Setting up Galera Load Balancer (GLB), wouldn’t it be nice to ensure that Galera Load Balancer (GLB) handles failover, seamlessly?

Router:
------------------------------------------------------
        Address       :   weight   usage    map  conns
188.166.179.177:3306  :    1.000   0.500    N/A      1
  165.22.50.152:3306  :    1.000   0.500    N/A      1
   165.22.49.92:3306  :    1.000   0.000    N/A      0
------------------------------------------------------
Destinations: 3, total connections: 2 of 493 max

Pool: connections per thread:     1     1     0     0     0     0

Here is an example of a Galera Load Balancer (GLB) handling 3 backend MySQL servers, and it currently has 2 connections, evenly distributed between 2 hosts.

We can verify this by doing: select @@hostname; which shows: