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:

• …

Of course MySQL 8.0.19 that was released on 13th January 2020 also includes Community Contributions. But before talking about them, I would like to thanks Jesper for having started this MySQL Releases Contributions thread. I’ll try to keep his good work on that topic from now.

So, MySQL 8.0.19 includes contributions from Facebook, Satya Bodapati, Nikolai Kostrigin and Olekasandr Peresypkin.

Please note that this list might not be exhaustive as it’s currently a manual process for me to link the contributions with the current release. I will try to improve this in the future.

Here are those contributions:

• Innodb_system_rows_read, Innodb_system_rows_inserted, …

In this talk, we’ll walk through RocksDB technology and look into situations where MyRocks is a good fit versus other engines such as InnoDB. We will go over internals, benchmarks, and the tuning of MyRocks engine. We will also explore the benefits of using MyRocks within the MySQL ecosystem. Attendees will leave with solid knowledge of the latest development tools and integrations within MySQL.

Please join Alkin Tezuysal and Sergey Kuzmichev on Monday, March 23rd, at 9:00 am EDT for their webinar “When is MyRocks a Good Fit?”

View the Recording

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)
• …

Monitoring a database is a critical mission: collecting information over time allows tracking how's behaving certain instance in response to a determined workload. There are several solution on the market, and MySQL offers its own solution in the Enterprise Edition, MySQL Enterprise Monitor (MEM). Today I'd like to remember that there's also another nice solution that is free, and designed by MySQL performance architect, Dimitri Kravtchuk (hence not to be considered as an Oracle MySQL solution, not supported and "as is"). I'm talking about dim_STAT. dim_STAT has a slightly different purpose than MEM as it is specialized to make some low level analysis of the impact of the workload on our MySQL Server instance (e.g. can profile mysqld process), and above all, permits offline metrics captures.
…

Monitoring a database is a critical mission: collecting information over time allows tracking how’s behaving certain instance in response to a determined workload. There are several solutions on the market, and MySQL offers its own in the Enterprise Edition, MySQL Enterprise Monitor (MEM). Today I’d like to remember that there’s also another nice solution that is free, and designed by MySQL performance architect, Dimitri Kravtchuk (hence not to be considered as an Oracle MySQL solution, not supported and “as is”). I’m talking about dim_STAT. dim_STAT has a slightly different purpose than MEM as it is specialized to make some low level analysis of the impact of the workload on our MySQL Server instance (e.g. can profile mysqld process), and above all, permits offline metrics captures.

This blog post illustrates ” How to setup SSL enabled replication”

By default, mysql package installation creates SSL file in the data directory at the time of installation. If you would like to use different self-signed certificates then create them as described here.

Add SSL setting to my.cnf on all servers.

ssl=on
ssl-ca=/etc/sslcerts/ca.pem
ssl-cert=/etc/sslcerts/server-cert.pem
ssl-key=/etc/sslcerts/server-key.pem

Restart mysql server and verify the settings.

Example: client connections using SSL

#mysql -urpluser -p -P22403 --host 127.0.0.1 --ssl-cert=/etc/sslcerts/client-cert.pem --ssl-key=/etc/sslcerts/client-key.pem -e '\s'
Enter password:
--------------

Connection id: 5
Current database:
Current user: rpluser@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current …

In the MySQL ecosystem, orchestrator is the most popular and well-respected high availability and topology management tool, integrating well with other solutions such as ProxySQL. It facilitates automatic (or manual) discovery, refactoring and recovery of a replicated MySQL environment, and comes complete with both command-line (CLI) and web interfaces for both humans and machines to interact with.

As we all know, humans are prone to errors and as such accidents can happen, particularly when humans and computers interact with each other! Recently, one of these situations related to the web interface of orchestrator during topology refactoring with its drag-and-drop capabilities, where a drop occurred unintentionally and thus had an impact on replication.

When …

As you know, Oracle Cloud Infrastructure (aka OCI) becomes more and more popular. You can find OCI data centers almost everywhere and the price is very attractive.

Something very interesting is the possibility to have different availability domains but also different fault-domains in the same availability one !

Oracle provides a lot of modules to deploy your architecture on OCI using Terraform. Those are called oracle-quickstart repos. You can find them on Github.

There is an official Reference Architecture for MySQL InnoDB Cluster in OCI that I encourage you to read before deploying your MySQL HA solution …

If you’re a MySQL user trying ClickHouse, one thing which is likely to surprise – and annoy you – is the handling of Double Quotes. In MySQL, you can use both double quotes and single quotes to quote strings, and as an example, these two queries are equivalent:

mysql> select * from performance_schema.global_variables where variable_name='max_connections';
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 151            |
+-----------------+----------------+
1 row in set (0.01 sec)

mysql> select * from performance_schema.global_variables where variable_name="max_connections";
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 151            |
+-----------------+----------------+
1 row in set (0.00 sec)

This means that many of us tend to use single quotes and double quotes …