Another interesting feature released with MySQL 8.0 is full support for functional indexes.

Although this is not a strictly new concept in the MySQL world (indexed generated columns provided the same functionality), I find it worth reviewing, through some applications, notes and considerations.

All in all, I’m not 100% bought into functional indexes (as opposed to indexed generated columns); I’ll elaborate on this over the course of the article.

As a natural fit, generated columns are included in the article; additionally, some constructs build on my previous article, in relation to the subject of CTEs.

Updated on 12/Mar/2020: Found another bug.

Contents:

• …

A long-time missing (and missed) functionality in MySQL, is sequences/ranges.

As of MySQL 8.0, this functionality is still not supported in a general sense, however, it’s now possible to generate a sequence to be used within a single query.

In this article, I’ll give a brief introduction to CTEs, and explain how to build different sequence generators; additionally, I’ll introduce the new (cool) MySQL 8.0 query hint SET_VAR, and a pinch of virtual columns and functional indexes (“functional key parts”, another MySQL 8.0 feature).

Contents:

• A brief introduction to Common Table Expressions (CTEs)
• …

In this post I’ll expand on the subject of my MySQL pre-FOSDEM talk: what dbadmins need to know and do, when upgrading from MySQL 5.7 to 8.0.

I’ve already published two posts on two specific issues; in this article, I’ll give the complete picture.

As usual, I’ll use this post to introduce tooling concepts that may be useful in generic system administration.

The presentation code is hosted on a GitHub repository (including the …

I posted a few days ago some initial thoughts on the the MySQL native cloning functionality. Overall this looks good and I need to spend time to test further. I’m here in San Francisco ahead of Oracle Open World which starts today. As is usual with trips like this jet lag wakes you up rather … Continue reading MySQL Cloning: more thoughts

The post MySQL Cloning: more thoughts first appeared on Simon J Mudd's Blog.

If you use replication with MySQL, if you need a backup, if you need a spare copy of a system for testing and for many other reasons you need a way to make a copy of your MySQL system. In the past you could make a copy in various ways: using a cold file system … Continue reading MySQL 8.0.17+: Cloning is now much easier

The post MySQL 8.0.17+: Cloning is now much easier first appeared on Simon J Mudd's Blog.

Fairly recently, we’ve upgraded to MySQL 8; it’s been a relatively smooth transition, however, some minor differences needed to be handled. One of them is the behavior of trailing spaces.

Trailing spaces are a (not in a good way) surprising, but also widely covered argument. This article gives a short overview, and relates it to how this affects people upgrading to MySQL 8.0.

Contents:

• Premises/Requirements
• Behavior in different contexts
  • …

Ran into an interesting situation trying to configure a MySQL JDBC driver to connect over TLS (though the driver may call it SSL, TLS is the name for more recent versions of the protocol).

The error I was getting was pretty generic:

Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

With the relevant parts of the stacktrace, also being non helpful:

at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
        at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:455)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
        at …

We’re in the process of upgrading our MySQL databases from v5.7 to v8.0; since one of the differences in v8.0 is that the default encoding changed from utf8 to utf8mb4, and we had the conversion in plan anyway, we anticipated it and performed it as preliminary step for the upgrade.

This post describes in depth the overall experience, including tooling and pitfalls, and related subjects.

Contents:

• Introduction
• Migration plan: overview and considerations
  • …

We recently had to drop a column in production, from a relatively large (order of 10⁷ records) table.

On modern MySQL setups, dropping a column doesn’t lock the table (it does, actually, but for a relatively short time), however, we wanted to improve a very typical Rails migration scenario in a few ways:

1. offloading the column dropping time from the deploy;
2. ensuring that in the time between the column is dropped and the app servers restarted, the app doesn’t raise errors due to the expectation that the column is present;
3. not overloading the database with I/O.

I’ll give the Gh-ost tool a brief introduction, and show how to fulfill the above requirements in a simple way, by using this tool and an ActiveRecord flag.

This workflow can be applied to almost any table alteration scenario.

Contents:

• …

This is a quick reminder that on Thursday (tomorrow) there there is a Madrid MySQL Users Group Meeting where Giuseppe Maxia will be presenting dbdeployer to us. More information can be found here. Do not forget to sign up if you are interested. We look forward to seeing you there.

The post Reminder: Madrid MySQL User Group Meetup presenting dbdeployer on Thursday in Madrid first appeared on Simon J Mudd's Blog.