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

Find out why and how we replaced an AWS RDS/MySQL solution with a Tungsten Cluster composite active/active MySQL solution to offer a geo-distributed MySQL back-end for IoT services at Samsung.

Tags:  Webinar MySQL use case tungsten clustering AWS Aurora aws rds mysql cluster Geo-Distributed

Earlier this week, Oracle released their Q4 releases series. As on the previous releases, backward compatibility has been broken with previous versions of the server. This time on both MySQL 5.7 and 8.0:

MySQL 5.7.32

While our QA team was performing an extensive test on it,  we found out this version introduced a new compression format version. This change breaks backward compatibility with older versions of MySQL, which is expected on the 8.0 series but is not on 5.7. As Percona XtraBackup (PXB) is based on MySQL code, it makes MySQL 5.7.32 incompatible with current versions of Percona XtraBackup 2.4.20 and prior.

The issue does not affect only Percona XtraBackup but also prevents users from downgrading the server from 5.7.32 to any lower version on the 5.7 series – More details at …

We have video recording available for you to learn how you can benefit the New Galera Manager. It includes live demo how to install Galera Manager and deploy easily Galera Cluster on Amazon Web Service for Geo-distributed Multi-master MySQL, Disaster Recovery and fast local reads and writes. Now you can monitor and manage your Galera Cluster with Graphical Interface.

“The presentation was great with lots of valuable information. We will definitely try to implement Galera Manager in our environment very soon” stated attendee of the webinar.

Watch Galera Manager webinar recording

Download Galera Manager

One of the most exciting storage-related features in Kubernetes is Volume snapshot and clone. It allows you to take a snapshot of data volume and later to clone into a new volume, which opens a variety of possibilities like instant backups or testing upgrades. This feature also brings Kubernetes deployments close to cloud providers, which allow you to get volume snapshots with one click.

Word of caution: for the database, it still might be required to apply fsfreeze and FLUSH TABLES WITH READ LOCK or

LOCK BINLOG FOR BACKUP

.

It is much easier in MySQL 8 now, because as with atomic DDL, MySQL 8 should provide crash-safe consistent snapshots without additional locking.

Let’s review how we can use this feature with Google Cloud Kubernetes Engine and …

For developers building out a web application a transactional datastore is the obvious and proven choice but with success comes scale limitations.