The latest from the MySQL community

Tweet

The other day, I was reading a blog by Magnus Hagander about tracking foreign keys throughout a schema in PostgreSQL. I thought it was a good idea, so I decided to look at how you can track foreign key in MySQL.

The way I decided to do it was to start out with a table, then find all tables referencing the table by a foreign key. From this basic (and simple as it will be shown) query, it is possible to create a chain of relations. The key table for the queries is …

The old adage that time is money does not give any hints about buying more time. A MySQL Enterprise subscription does allow you to purchase time. Your technical staff has to do more with less time than ever before and operational delays add up extremely quickly.  Having the right tools when things go critical means staff can do what they need to do, when they need to do it.

So how does MySQL Enterprise Edition buy you more time?

When things inevitably go wrong, searching for free answers to problems on web sites with many dubious or flat out wrong solutions is a waste of precious time.  Informational websites like are fine sources of general information for researching technical issues but not when mission critical services are degraded or down.  An online recommendation to do what very large Enterprise Company does may not make sense for your company if you are not …

MySQL 8.0 Code Contributions (Shutterstock)

An Oracle engineer thanked two Percona engineers by name, along with engineers from Facebook and elsewhere, for their recent MySQL 8.0 source code contributions. Oracle incorporated their work into its latest MySQL production release (8.0.13).

Percona’s Zsolt Parragi authored a patch for a rare replication bug that left locked mutexes in production builds following debug crashes (bug # …

Tweet

First of all, thanks to everyone who attended my sessions at the recent Oracle OpenWorld and Code One in San Francisco. It was a great privilege to be allowed to make a number of presentations.

All of the workbooks and scripts from the hands-on labs (HOL) and the slides from the talks have been made available at OpenWorld’s and Code One’s session catalogues. You can download the files by using the OpenWorld catalog searching for my name or the session number (see below). Click on the download icon for each of the presentations you are interested in.

…

Percona is glad to announce the release candidate of Percona XtraBackup 8.0-3-rc1 on October 31 2018. You can download it from our download site and apt and yum repositories.

This is a Release Candidate quality release and it is not intended for
production. If you want a high quality, Generally Available release, use the current stable version (the most recent …

Following the alpha release announced earlier, Percona announces the release candidate of Percona Server for MySQL 8.0.12-2rc1 on October 31, 2018. Download the latest version from the Percona website or from the Percona Software Repositories.

This release is based on MySQL 8.0.12 and includes all the bug fixes in it. It is a Release Candidate quality release and it is not intended for production. If you want a high quality, Generally Available release, use the current Stable version (the most recent stable release at the time of writing in the 5.7 series is 5.7.23-23).

Percona provides completely open-source and free software.

Installation

As this is a release candidate, installation is performed by enabling the testing repository and installing the software via …

Tweet

MySQL 8.0.13 was released this week. There are several exciting changes including functional indexes and using general expressions as the default value for your columns. So, I will recommend you to get MySQL 8.0.13 installed and try out the new features. You can read about changed in the release notes section of the MySQL documentation and in Geir’s release blog.

…

The short version

I created > one billion InnoDB tables in MySQL 8.0 (tables, not rows) just for fun. Here is the proof:

$ mysql -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1425329
Server version: 8.0.12 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select count(*) from information_schema.tables;
+------------+
| count(*)   |
+------------+
| 1011570298 |
+------------+
1 row in set (6 hours 57 min 6.31 sec)

Yes, it took 6 hours and 57 minutes to count them all!

Why does anyone need one billion tables?

In my previous blog post, I created and tested  …

So, if we’re applying GDPR to our system, and we’re already making use of MySQL Transparent Data Encryption / keyring, then here’s an example on how to migrate from filed-based keyring to the encrypted keyring. Online.

If you’re looking to go deeper into the TDE then I suggest reading the MySQL Server Team’s InnoDB Transparent Tablespace Encryption blog.

You’d already have your environment running, whereas I have to create one.. give me a minute please, 8.0.12 here we come:

mysqld --defaults-file=my_okv.cnf --initialize-insecure --user=khollman
mysqld --defaults-file=my_okv.cnf --user=khollman &

mysql …

Probably not well known but quite an important optimization was introduced in MySQL 5.6 – reduced overhead for “read only transactions”. While usually by a “transaction” we mean a query or a group of queries that change data, with transaction engines like InnoDB, every data read or write operation is a transaction.

Now, as a non-locking read operation obviously has less impact on the data, it does not need all the instrumenting overhead a write transaction has. The main thing that can be avoided, as described by documentation, is the transaction ID. So, since MySQL 5.6, a read only transaction does not have a transaction ID. Moreover, such a transaction is not visible in the SHOW ENGINE INNODB STATUS output, though I will not go deeper on what really that means under the hood in this article. The fact is that this optimization …