MySQL 8.0.14 came with Lateral Derived Tables as a new feature.  But what is that and how do you use them?

Let's start what a derived table is.  According to the manual (link below) A derived table is an expression that generates a table within the scope of a query FROM clause.   You are probably used to using a subquery or JSON_TABLE where that query feeds data into another query.

Derived Tables

Derived tables can return a scalar, column, row, or table. But there are limits to their use.
A derived table cannot be a correlated subquery and a derived table cannot contain references to other tables of the same SELECT. And previous to MySQL 8.0.14, a derived table cannot contain outer references.

Okay, so what is an 'outer reference'? The SQL-92 standard …

MySQL 8.0 brought enormous changes and modifications that were pushed by the Oracle MySQL Team. Physical files have been changed. For instance, *.frm, *.TRG, *.TRN, and *.par no longer exist. Tons of new features have been added such as CTE (Common Table Expressions), Window Functions, Invisible Indexes, regexp (or Regular Expression)--the latter has been changed and now provides full Unicode support and is multibyte safe. Data dictionary has also changed. It’s now incorporated with a transactional data dictionary that stores information about database objects. Unlike previous versions, dictionary data was stored in …

FOSDEM is coming up. I do not have a presentation in the MySQL and Friends devroom this year, but it reminded me that I had planned to post a follow-up to my presentation from last year.

As part of the presentation, I showed how you can inspect the content of a histogram using the information schema table column_statistics. For example, the following query will show the content of the histogram for the column l_linenumber in the table lineitem of the dbt3_sf1 database:

SELECT JSON_PRETTY(histogram)
FROM information_schema.column_statistics
WHERE schema_name = …

MySQL InnoDB is more and more popular. The adoption of it is even faster than I expected. Recently, during my travel in Stockholm, Sweden, a customer asked me what was the best practice to backup a cluster.

Since my interlocutor was a customer, the obvious choice is to use MySQL Enterprise Backup (known as MEB). Of course any other physical backup should be also fine.

The customer told me he was using cron to schedule his backup and was only using full backups… That’s perfect. So I told him that there is nothing complicated and that the cron job should something like:

mysqlbackup --with-timestamp --backup-dir /backup  backup

Of course, I do not recommend the use of --user clusteradmin --password=xxxxxin the cronjob but configure your crendentials using …

Percona is glad to announce the release of Percona Server for MySQL 5.6.43-84.3 on January 29, 2019 (Downloads are available here and from the Percona Software Repositories).

This release merges changes of MySQL 5.6.43, including all the bug fixes in it. Percona Server for MySQL 5.6.43-84.3 is now the current GA release in the 5.6 series. All of Percona’s software is open-source and free.

Bugs Fixed

• A sequence of LOCK TABLES FOR BACKUP and STOP SLAVE …

Replication topologies, whether master-slave or group replication setups, may be composed of servers using different MySQL versions.

In MySQL 8.0.14, each transaction’s immediate and original server versions are now visible in the binary log as session variables. These two new variables, fully managed by the replication infrastructure, are used to support cross-version replication by transmitting the MySQL server release numbers associated with each transaction through the replication topology:

• original_server_version stores the MySQL Server release number of the server where a transaction was originally committed (for example, 80014 for a MySQL 8.0.14 server instance).

…

Whenever we talk about the scalability of databases its end up with a lot of discussions and effort to implement. Some of you may even argue that it is a bad idea to auto scale the transactional databases. But the pace of innovation in databases — particularly on a world with public cloud, is breathtaking. AWS Aurora is a game changer database engine in DBaaS for Open Source Databases. It provides performance, reliability, availability, and Scalability. With Aurora features like custom endpoints and loadbalancing across replicas, on can explore some interesting use cases. In this post, we will discuss how we solved a customer’s problem by using scalability features of Aurora. We will focus on provisioned Aurora on AWS, not Aurora Serverless.

We have a customer who was doing all the reporting and massive read …

Please join Percona’s Senior Technical Manager, Alkin Tezuysal, and Percona’s Percona XtraDB Cluster Lead, Krunal Bauskar as they present their talk, Percona XtraDB Cluster: Failure Scenarios and their Recovery on Wednesday, January 30th, 2019, at 8:00 AM PST (UTC-8) / 11:00 AM EST (UTC-5).

Register Now

Percona XtraDB Cluster (a.k.a PXC) is an open source, multi-master, high availability MySQL clustering solution. PXC works with your MySQL / …

There are over 250 new features in MySQL 8.0. The MySQL Manual is very good, but verbose. This is a list of new features in short bullet form. We have tried very hard to make sure each feature is only mentioned once.…

Facebook Twitter Google+ LinkedIn

The latest release of MySQL 8.0 introduces a new dynamic system variable @@innodb_buffer_pool_in_core_file which lets you omit the Buffer Pool’s memory content when generating a core file.

This change is an adaptation of a patch contributed by the Facebook team. We would like to thank and acknowledge this important and timely contribution by Facebook.…

Facebook Twitter Google+ LinkedIn