For what reason should I use a real multi-Primary setup?
To be clear, not a multi-writer solution where any node can become the active writer in case of needs, as for PXC or PS-Group_replication.
No, we are talking about a multi-Primary setup where I can write at the same time on multiple nodes.
I want to insist on this “why?”.

After having excluded the possible solutions mentioned above, both covering the famous 99,995% availability, which is 26.30 minutes downtime in a year, what is left?

Disaster Recovery? Well that is something I would love to have, but to be a real DR solution we need to put several kilometers (miles for imperial) in the middle. 

And we know (see here and …

Many times, we do not want duplicate rows or values in our SQL tables. On the other hand, in some situations, it does not matter if there are duplicates present. For whatever reason, suppose duplicates have found their way into one of your tables. How can you find them quickly and easily? The ROW_NUMBER() Window function is a fantastic tool to use. Continue reading and see example queries you can apply to your own tables and find those duplicates…

Photo by Joe Green on Unsplash

OS and DB used:

…

This blog discusses new features about the persisted system variables and how we can use it to make variable changes persistent. The MySQL server maintains system variables that control its operations. The dynamic variables used prior to the MySQL 8 release are not persistent and are reset upon restart. These variables can be changed at runtime using the SET statement to affect the operation of the current server instance but we have to manually update my.cnf config file to make them persistent. In many cases, updating my.cnf from the server-side is not a convenient option, and leaving the variable just updated dynamically reverts on the subsequent restart without any history.

Persisted system variables are one of the useful features introduced in MySQL 8. The new functionality helps DBAs update the variables dynamically and register them without touching the configuration files from the server-side.

How to Persist the Global …

 In the previous post I showed some improvements based on using

Windows functions in rewritten TPC-H queries. In this blog post

I will discuss the improvements made in Q15 using a CTE (Common

Table Expression). This is again based on inspiration from new and

old blogs written by Öystein Grövlen (should be norwegian ö's).

Here is Q15 in TPC-H:

create view revenue (supplier_no, total_revenue) as

        select

                l_suppkey,

                sum(l_extendedprice * (1 - l_discount))

        from

                lineitem

        where

                l_shipdate >= '1996-01-01' …

If you often deploy services in the cloud, you certainly, at least once, forgot to stop a test instance. I am like you and I forgot my share of these. Another mistake I do once in a while is to provision a bigger instance than needed, just in case, and forget to downsize it. While this is true for compute instances, it is especially true for database instances. Over time, this situation ends up adding a cost premium. In this post, we’ll discuss a solution to mitigate these extra costs, the use of the RDS Aurora Serverless service.

What is Amazon Aurora Serverless?

Since last spring, Amazon unveiled a new database related product: RDS Aurora Serverless. The aim of this new product is to simplify the management around Aurora clusters. It brings a likely benefit for the end users, better control over cost. Here are some …

 This is probably the first time I post a blog that handles variants of

how to use SQL :)

I got the inspiration from reading some new and old blogs by Öystein Grövlen

where he mentioned various ways to rewrite queries as Common Table

Expressions (CTEs) and using windows functions instead of using subqueries.

I tried this and found that the rewritten queries was faster using windows in some

cases. Obviously the CTE variant and the subquery variant of the query can

be executed in the same way. But SQL implementations are not perfect and

are able to handle some constructs better than others.

This got me a bit puzzled, so I wanted to understand what specifically is the

thing that makes the CTEs and windows functions variant run faster.

Let's take Q17 in TPC-H as an example.

Here is the original …

MySQL 8.0.22 was released on Oct 19, 2020, and came with nice features and a lot of bug fixes. Now, you can configure your async replica to choose the new source in case the existing source connection (IO thread) fails. In this blog, I am going to explain the entire process involved in this configuration with a use case.

Overview

This feature is very helpful to keep your replica server in sync in case of current source fails. 

To activate asynchronous connection failover, we need to set the “SOURCE_CONNECTION_AUTO_FAILOVER=1” on the “CHANGE MASTER” statement.

Once the IO connection fails, it will try to connect the existing source based on the “MASTER_RETRY_COUNT, MASTER_CONNECT_RETRY”. Then only it will do the failover. 

The feature will only work when the IO connection is failed, maybe the source crashed or stopped, or any network failures. This will not work if the replica is …

I am always surprised to learn something new whenever I talk to a member of the open-source community. No matter how much I think I have heard of every use case there is for Liquibase (and database change management in general), I always hear something that makes this space still feel new. There’s always something left to discover.

Today, that new something is the problem of how to perform large batches of changes with SQL ALTER TABLE statements. No problem you say? Okay, but this ALTER needs to happen in production. Still not worried? Well, let’s say you have millions of rows, and because you’re so successful, you have many transactions happening per minute (maybe even per second). Yeah…now we are talking. You can’t alter the table because you can’t afford to lock that table for the …

The amount of transactions committed is an important information, but how could you return an accurate value?

This is a question I got from my colleague Ivan, he was challenging with global status values like COM_COMMIT or HANDLER_COMMIT, then checking in innodb_metrics… but this was not accurate.

In fact depending which storage engine you are using, if binary logs are enabled, if you rollback transactions, if you are using auto_commit, etc… all those parameters influence those values.

So the first question was “What metrics or else should we use ?”. In my opinion, the most accurate “counter” for transactions are the GTIDs.

And this is why I created yet another MySQL Shell plugin that does that calculation:

This plugin is available on my GitHub repository …

 Data Con LA starts October 23rd and I will be speaking on Sunday and I will be speaking on Sunday the 25th on MySQL's NoSQL JSON Store in a talk titled MySQL Without the SQL -- Oh My!

The talk is pre-recorded but I will be live for the question and answer segment. So this is your chance to ask me your question.

Dave's MySQL Basics is a new video based series I am starting to teach the basics of MySQL.  I have been talking with book publishers, hiring managers, DBAs of all levels, and many others.  They have all said they would like to see a simple, modular way to learn MySQL.  So rather than take a few years to produce a book, I am creating a series of videos that will be short (the goal is five minutes or less), not too pedantic (still have to teach the very low level basics), and that can be updated quickly when/if the material changes.  …