We know that when filtering text data in a MySQL WHERE clause conditional, strings are placed in single quotes. In this blog post, learn how to filter numeric data in a MySQL WHERE clause conditional…

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.

Image by Gerd Altmann from Pixabay 

The MySQL InnoDB storage engine lives off primary keys: Records are stored physically in a B+-Tree, a balanced tree where the data pages are the leaves of the primary key B-Tree.

That implies that the data is stored physically in primary key order – records with similar primary keys are usually physically closer together than records with larger differences in primary key value. In literature, such physically ordered indexes are often called clustered indexes.

InnoDB depends on primary keys

Secondary indexes in databases need to store the indexed data together with a row address, a pointer to the full row (in InnoDB, into the primary key).

If that row address was a physical address (a disk block number or a similar pointer tied to the position of the data), growing data pages and moving them around would …

Codership is pleased to announce a new Generally Available (GA) release of the multi-master Galera Cluster for MySQL 5.7, consisting of MySQL-wsrep 5.7.38 (release notes, download). This release incorporates all changes to MySQL 5.7.38, adding a synchronous option for your MySQL High Availability solutions.

In MySQL 5.7.38, we now distinguish between unset ssl_mode and ssl_mode explicitly set to “DISABLED”, because previously ssl_mode=DISABLED was ignored in the xtrabackup-v2 script.

This is a rare release where you are also meant to continue using the Galera replication library 3.37 implementing the wsrep API version 25, from the previous release, as there was no changes made to the Galera replication …

Azure Database for MySQL

Introduction

This blog is the third chapter related to deploying a MySQL infrastructure on the Azure cloud. In addition to performance, we should indeed also consider backup and restore capabilities. The objective of this blog is to present the main backup and restore possibilities offered by Azure through a simple example and show a second backup/restore possibility using MySQL Shell dump utilities.

Backup window and mechanism

Flexible Azure for MySQL generates by default a 7 days server backup retention period. This retention period can be extended up to 35 days or shorten to 1 day. In addition we can decide if you want to have a Geo Redundant backup storage. By default the backups are locally redundant.

It’s important to understand that Azure makes …

The OpenLampTech publication is entering its 9th month of publishing and I have you to thank. OpenLampTech continues to grow. Thank you! We have several great articles this week. Enjoy 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 #40 we have articles covering:

• The SQL NULL
• Custom helper functions in CodeIgniter 4
• PHP built-in web server
• MySQL command line
• And much much more

Be sure and head over to the …

We recently migrated some of EverSQL's workload from AWS RDS MySQL to Aurora MySQL. In this post I'll share several options I explored, and the issues I ran into when using each of them, and specifically AWS DMS, so hopefully you could avoid similar issues when going through the migration process.

Potential solutions for migrating RDS MySQL to Aurora MySQL

Naturally, I first started with AWS's documentation, which states you can migrate RDS MySQL to Aurora MySQL by creating an Aurora read replicate and promoting it after replication is done. Unfortunately, that solution wasn't viable in our case, as the RDS instance was already upgraded to MySQL 8.0.28, while Aurora MySQL only supports …

Question on the Libera/#mysql IRC channel:

Is there a way to split a simple select into multiple returned rows? For example, select 1, 2, 3 to be returned as rows?

This is actually asking for a table literal notation. I know of four ways to construct a table literal in MySQL:

UNION ALL

The oldest way to construct a table literal in any SQL that supports UNION is the UNION ALL construct. Write SELECT statements to return literal rows, and add them together to a table using UNION ALL:

mysql> select i from (
    ->   select 1 as i union all
    ->   select 2 as i union all
    ->   select 3 as i
    -> ) as t;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

This has always worked, even on the oldest versions …

MySQL Summit is the must-attend MySQL conference of the year. It is open for registration with an exclusive $400 discount code for MySQL attendees! Meet and learn how companies like Booking.com, Toyota, Meta, NVIDIA and 8x8 use the latest MySQL technologies.

The title is not entirely true, but ‘FTWRL on MyDumper is not needed anymore for consistent backups’ was a long title. One more time, I wanted to share a new feature in MyDumper. This is related to an important piece: the locking mechanism that mydumper uses to sync all the threads.

MyDumper was born because, at that time, we didn’t have a tool that could take a consistent logical backup using multiple threads. Syncing all the threads was one of the problems, which has been solved using FLUSH TABLE WITH READ LOCK (FTWRL), til all the threads execute START TRANSACTION WITH CONSISTENT SNAPSHOT (STWCS), then we release the FTWRL and all the threads are in sync. We all know that FTWRL is very expensive and difficult to acquire on some database workloads.

I started to think about alternatives to avoid using FTWRL, and my first thought was, why …

One of the questions I am often asked is in what cases I would prefer MyRocks over InnoDB. We have covered MyRocks in our blog previously:

MyRocks Performance – Percona Database Performance Blog

Saving With MyRocks in The Cloud – Percona Database Performance Blog

But it would be good to refresh some materials.

This time I want to take an interesting (and real) data set, which I also covered previously: the Reddit Comments dataset (see Big Dataset: All Reddit Comments – Analyzing with ClickHouse – Percona Database Performance Blog). The dataset is still available for download from …