I am happy that the MySQL team is, during the last years, blogging about each major feature that MySQL Server is getting; for example, the series on Recursive Common Table Expressions. Being extremely busy myself, …

Today, let’s have a look at the TOP 10 new features in MySQL 8.0 that will improve DBA’s life.

To shrink the list to 10 only items wasn’t an easy task, but here is the top 10:

1. Temporary Tables Improvements
2. Persistent global variables
3. No more MyISAM System Tables
4. Reclaim UNDO space from large transactions
5. UTF8 performance
6. Removing Query Cache
7. Atomic DDLs
8. Faster & More Complete Performance Schema (Histograms, Indexes, …) and Information Schema
9. ROLES
10. REDO & UNDO logs encrypted if tablespace is encrypted

Temporary Tables Improvements

Since 5.7, all internal temporary tables are created in a unique shared tablespace called “ …

MySQL 8.0 RC2 has just been released with a lot of new features compared to MySQL 5.7. In this blog post, I will list the top 10 new features that should excite developers.

These functionalities are presented in descending order of preference of our MySQL Community.

TOP 10

1. MySQL Document Store
2. Default to utf8mb4
3. JSON enhancements
4. CTEs
5. Window Functions
6. Descending Indexes
7. Better Optimizer Cost Model
8. MySQL Server Components
9. Improvement in GIS
10. InnoDB NO WAIT & SKIP LOCKED

MySQL Document Store

This is the most expected and liked feature in MySQL 8.0 … and …

You may already know that MySQL 8.0 is coming with a nice requested feature : ROLES

A role is a named collection of privileges. When you watch some slidedeck about MySQL 8.0 and ROLES, some times you can see a graph illustrating the ROLES and their eventual hierarchy. From the documentation, it seems those graphs are made using the ROLES_GRAPHML() function.

I tried it… and my first try is not really what I was expecting…

Input file

To create the input file I used the following command:

mysql> SELECT ROLES_GRAPHML() into outfile '/var/lib/mysql-files/test_roles.graphml';
Query OK, 1 row affected (0.19 sec)

Then I used yEd to …

Changing configuration settings in MySQL wasn’t always easy. Of course it’s possible to change things (hopefully), but keeping track of everything is not always obvious. This is where configuration management systems like puppet, chef, ansible, … excels in making our life easier.

/etc/my.cnf

With MySQL 8.0, we worked in making your life easier (and the life of configuration management systems easier too).

Let’s first illustrate the problematic very naively:

As you can see, we can modify this configuration variable, but after a restart of MySQL, this change is lost, this behavior, of course is something known by all MySQL DBAs. The usual solution is then to also modify the configuration file (/etc/my.cnf in most cases).

SET PERSIST …

As you may already know,  since MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is completely removed in MySQL 8.0.

So now, in MySQL 5.7, the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines do.

MySQL 5.7 supports generic partitions & native partitions

What does that imply for users using partitions in an earlier version MySQL  migrating to 5.7 ?

As the documentation describes it, the generic partitioning handler is still supported in all MySQL 5.7 releases, although it is deprecated in 5.7.17 and later. (Note that “deprecated” is not the same …

With MySQL 8.0, one key feature is the new Data Dictionary.

The system tables that were previously in MyISAM are now replaced by new protected ones in the DD.

My friend Giuseppe already explained how you could see those tables using sandbox  and he also warned you that you should not mess up with them in this post too.

I’ll explain you how you can see those tables and their actual content. But will also explain why we decided to protect them and why it should stay like that.

DD protected internal tables list

The easiest way …

As you may already know, there are many different places where a MySQL configuration variables can be initialized.

In MySQL 8.0, we added in performance_schema a table allowing you to easily find where a variable was defined.

Let’s check this in action with max_connections for example.

I started mysqld and now I check the value of max_connections:

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

We can also use the performance.schema table called variables_info to get some more details about it:

mysql> SELECT t1.*, VARIABLE_VALUE 
       FROM performance_schema.variables_info t1 
       JOIN performance_schema.global_variables t2 
         ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
      WHERE …

As you probably already know, MySQL 8.0 supports Unicode 9.0 and our default charset moved from latin1 to utf8mb4.

Recently, Gabi (certainly working on an amazing presentation for PHPWorld) asked me something about how the client deals with the emojis while checking Morgo’s post about 8.0 RC1.

I didn’t know the answer and had to play with it, here is the outcome of these tests.

First let’s try to use the standard MySQL client and paste the dophin emoji from emojipedia:

In MySQL 8.0, we have now a new way of storing tables definitions: the Data Dictionary  !

Thanks to the Data Dictionary, DDLs in MySQL 8.0 are now Atomic. This means tat we have some changes in the behavior of the following DDLs for engines supporting  atomic DDL:

1. DROP TABLES <tables> – All tables will be dropped, or none are dropped
2. DROP SCHEMA – All entities in the schema will be dropped, or none are dropped
3. DROP VIEW – All views are dropped, or none are dropped
4. CREATE USER – All users are created, or none are created
5. DROP USER – All users are dropped, or none are dropped
6. GRANT – All users/roles in list are granted privileges, or none

This was detailed in …