Now that we’ve migrated to MySQL for our core database course, I’m building MySQL Workbench analysis problems. We start with a lecture trying to flush out a simple address, and then ask them to figure out how to link it to a customer table.

Designing it, I emphasized how it resolves the issue of a city occurring in multiple counties and states, like Fremont. Fremont occurs 17 times in the US and once in Haiti:

Naturally, I left two other design issues in the problem. I’m working through it for the first time with classes tomorrow. I hope it works well. Any comments?

The new MyDumper 0.10.5 version, which includes many new features and bug fixes, is now available.  You can download the code from here.

For this release, we focused on fixing some old issues and testing old pull requests to get higher quality code. On releases 0.10.1, 0.10.3, and 0.10.5, we released the packages compiled against MySQL 5.7 libraries, but from now on, we are also compiling against MySQL 8 libraries for testing purposes, not releasing, as we think that more people in the community will start compiling against the latest version, and we should be prepared.

New Features:

• Password obfuscation #312
• Using …

The easiest way to deploy Joomla! on Oracle Cloud Infrastructure and MySQL Database Service, is to use OCI Resource Manager’s stack.

Let’s see how easy it’s to deploy such solution.

If you don’t have already an OCI account, you can get Free Tier at https://oracle.com/mysql

The easiest way to deploy all the required resources (vcn, subnets, gateways, compute and MySQL instances, security lists, …) is to use a stack for Resource Manager. It consists in Terraform modules and information to easily deploy the architecture on OCI.

To deploy Joomla! and a MDS instance, we go then on this GitHub repo and click on button to deploy on OCI:

The Oracle Cloud Dashboard will be open and the stack creation form will be loaded like …

Introduction

Sysbench is a tool to benchmark to test open source databases. We have integrated Sysbench into the RonDB installation. This makes it extremely easy to run benchmarks with RonDB. This paper will describe the use of these benchmarks in RonDB. These benchmarks were executed with 1 cluster connection per MySQL Server. This limited the scalability per MySQL Server to about 12 VCPUs. Since we executed those benchmarks we have increased the number of cluster connections per MySQL Server to 4 providing scalability to at least 32 VCPUs per MySQL Server.

As preparation to run those benchmarks we have created a RonDB cluster using the Hopsworks framework that is currently used to create …

There is a common pattern in life, you often discover or understand things by accident. Many scientific discoveries fit such a description. In our database world, I was looking to see how BLOB/TEXT columns are allocated using overlay pages and I stumbled upon something interesting and unexpected. Let me present to you my findings, along with my attempt at explaining what is happening.

InnoDB Tablespaces

The first oddity I found is a bunch of free pages in each tablespace it is skipping. Here’s an example from a simple table with only an integer primary key and a char(32) column:

root@LabPS8_1:~/1btr# innodb_space -f /var/lib/mysql/test/t1.ibd space-extents
start_page  page_used_bitmap
0               #####################################........................... <--- free pages
64              ################################################################
128 …

While I was working on my grFailOver POC, I have also done some additional parallel testing. One of them was to see how online DDL are executed inside a Group Replication cluster.

The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Checking the Group Replication (GR) official documentation I was trying to identify if any limitation exists, but the only thing I have found was:

"Concurrent DDL versus DML Operations.  Concurrent data definition statements and data manipulation statements executing against the same object but on different servers is not supported when using multi-primary mode. During execution of Data Definition Language (DDL) statements on an object, executing concurrent Data Manipulation Language (DML) on the same object but on a different server instance has the risk of conflicting DDL executing on different instances not being detected."

This …

AKA Cluster Error Voting

What is Cluster Error Voting (CEV)?

“Cluster Error Voting is a new feature implemented by Alexey Yurchenko, and it is a protocol for nodes to decide how the cluster will react to problems in replication. When one or several nodes have an issue to apply an incoming transaction(s) (e.g. suspected inconsistency), this new feature helps. In a 5-node cluster, if 2-nodes fail to apply the transaction, they get removed and a DBA can go in to fix what went wrong so that the nodes can rejoin the cluster. (Seppo Jaakola)”

This feature was ported to Percona PXC in version 8.0.21, and as indicated above, it is about increasing the resilience of the cluster especially when TWO nodes fail to operate and may drop from the cluster abruptly. The protocol is activated in a cluster with any number of nodes.  

Before CEV if a node has a problem/error during a transaction, …

In the late 1990s I learned MySQL and Java at approximately the same time. How did I teach myself? For MySQL I read the online MySQL manual cover to cover. For Java it was the Java Language Specification or Java Programming Language book, again cover to cover. Then for record I read Effective Java, and I was totally lost. I read it three or four years later and then it made sense.

At that time, with MySQL 3.22/3.23 the scope of the product was smaller, so was Java 1.2. I am confident I have forgotten as much as I retain, however it does marvel me when sometimes the most simplest of functionality I do not recall, or perhaps never learned. Today’s What Did I Learn This Week.

In MySQL, there is a TRIM() function, as the name suggests it trims whitespace, or so I thought. It actually does a lot more as the current MySQL 8.0 manual …

From MySQL Shell 8.0.24, it’s possible to log all the SQL statements issued in MySQL Shell.

Everything is well documented in the dedicated manual section: System Logging for SQL Statements.

Let’s see it in action. We will first start MySQL Shell with --syslog option:

$ mysqlsh --syslog --sql root@localhost

We enter a statement:

MySQL  localhost:33060+   2021-04-30 09:18:39 
SQL  show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| bookstore                     |
| clusterdemo                   |
...

And we can verify in syslog if something has been logged. I use Systemd and Journald therefor I will …

While testing a XA transactions based workload, I noticed in the output of SHOW ENGINE INNODB STATUS;

---TRANSACTION 108674, ACTIVE (PREPARED) 35 sec
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2

So far, so good. A XA transaction has been PREPARED, and reported as such. But after a while (and I was not really looking at the session), I repeated the command and the output turned to:

---TRANSACTION 108674, ACTIVE (PREPARED) 91 sec recovered trx
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2

I went to check logs, because XA transactions are recovered after MySQL Server restart, and I made sure …