MySQL 8.0 first impressions

MySQL 8.0.0 was released today. It has been some time in the making, shrouded in a veil of secrecy for over one year. We knew, from listening to the gossip and looking at the few available previews, some of what was going to bring. So, for the observant users, its main features may not come as a surprise. For the rest of you, here's a quick roundup:

Notable features

• No MyISAM tables anymore! The grant tables are now InnoDB, meaning that grant operations are now atomic.
• A real data dictionary. This change is less visible than the previous one. The data dictionary tables are hidden and only a subset of the data is available through information_schema views. The reason for the hidden tables is to allow a stable interface through several versions. I am not happy about this choice, but I understand the reason.
• The .frm files are gone. If you create a table using an engine other than InnoDB, the system creates a JSON file (.SDI) containing serialised data dictionary info.
• Roles! You can now define a set of privileges as a role, and then assign the role to a user. This feature greatly simplifies user management.
• You can set persistent variables from SQL (set persist variable_name=something) and they will survive a restart (except when they don't.
• There is a new plugin infrastructure of which I haven't seen any example yet.

Gotchas

• Some of the tables in the mysql database are gone. If you have tests that look for them (e.g. proc, event) they will fail, as these tables are now hidden in the data dictionary.
• Only InnoDB tables support partitioning! If you create MyISAM or Archive tables and try to partition them, you will get an error.
• The installation requires a new library (libnuma1 in Debian/Ubuntu, numactl-libs in CentOS). You need to install it before starting the MySQL server.

Unmatched expectations

• The installation still issues the same warnings reported for MySQL 5.7. I was thinking that by now the developers know how to set the initialisation without triggering a warning about TIMESTAMPS. I was mistaken.

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
++ 2016-09-11T17:42:06.471261Z 1 [Warning] InnoDB: New log files created, LSN=49311
++ 2016-09-11T17:42:06.548747Z 1 [Warning] InnoDB: Creating foreign key constraint system tables.
++ 2016-09-11T17:42:07.593428Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0f4a8ceb-7847-11e6-bd60-0242ac110002.
++ 2016-09-11T17:42:07.597075Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

• The monitoring features for replication are unchanged. There is nothing new about reporting GTID execution in performance_schema tables, except some minor additions for RBR operations.

Taking MySQL 8.0 for a spin

MySQL-Sandbox 3.2.01 can install the new version from a tarball.

If you are on Linux, you can even try the newest MySQL-Sandbox feature, which gets you reduced binaries for a given MySQL version with a single command. Just install MySQL::Sandbox 3.2.02 and run:

make_sandbox GET:8.0 -- --no_show
2016-09-12 17:38:40 URL:https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/available.txt [98/98] -> "/tmp/available.txt" [1]
wget -nv -O 8.0.0.tar.gz 'https://github.com/datacharmer/mysql-docker-minimal/blob/master/dbdata/8.0.0.tar.gz?raw=true'
    The MySQL Sandbox,  version 3.2.02
    (C) 2006-2016 Giuseppe Maxia
# Starting server
... sandbox server started
# Loading grants
Your sandbox server was installed in $HOME/sandboxes/msb_8_0_0

$  ~/sandboxes/msb_8_0_0/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 [localhost] {msandbox} ((none)) >  show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

At first sight, it looks like the usual MySQL. Same schemas, and almost the same tables. You will see a few tables related to roles in mysql, and a dozen new tables between information_schema and performance_schema. However, where the database has a different aspect is on the outside. Let's start with the data directory:

total 188468
-rw-r----- 1 root root       56 Sep 11 18:14 auto.cnf
-rw-r----- 1 root root     1813 Sep 11 18:14 ib_buffer_pool
-rw-r----- 1 root root 50331648 Sep 11 18:15 ib_logfile0
-rw-r----- 1 root root 50331648 Sep 11 18:15 ib_logfile1
-rw-r----- 1 root root 79691776 Sep 11 18:15 ibdata1
-rw-r----- 1 root root 12582912 Sep 11 18:15 ibtmp1
-rw-r----- 1 root root     4396 Sep 11 18:39 msandbox.err
drwxr-x--- 2 root root     4096 Sep 11 18:14 mysql
-rw-r----- 1 root root        4 Sep 11 18:15 mysql_sandbox8000.pid
-rw-rw---- 1 root root        3 Sep 11 18:15 mysqld_safe.pid
-rw-r----- 1 root root      225 Sep 11 18:14 performance_sche_3.SDI
drwxr-x--- 2 root root     4096 Sep 11 18:14 performance_schema
drwxr-x--- 2 root root     4096 Sep 11 18:14 sys
-rw-r----- 1 root root      210 Sep 11 18:14 sys_4.SDI
drwxr-x--- 2 root root     4096 Sep 11 18:15 test
-rw-r----- 1 root root      210 Sep 11 18:15 test_5.SDI

Notice that, for each schema except mysql, we have a .SDI file. For example:

cat performance_sche_3.SDI
{
    "sdi_version": 1,
    "dd_version": 1,
    "dd_object_type": "Schema",
    "dd_object": {
        "name": "performance_schema",
        "default_collation_id": 33,
        "created": 0,
        "last_altered": 0
    }
}

For tables other than innodb, we get a similar file, only more complex:

mysql [localhost] {msandbox} (test) > create table t1 (i int not null auto_increment primary key, c char(10) ) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

####

ls -l ./test
total 8
-rw-r----- 1 root root    0 Sep 11 18:49 t1.MYD
-rw-r----- 1 root root 1024 Sep 11 18:49 t1.MYI
-rw-r----- 1 root root 2706 Sep 11 18:49 t1_326.SDI

cat test/t1_326.SDI
{
    "sdi_version": 1,
    "dd_version": 1,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "t1",
        "mysql_version_id": 80000,
        "created": 20160911185434,
        "last_altered": 20160911185434,
        "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "i",
                "type": 4,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": true,
                "is_virtual": false,
                "hidden": false,
                "ordinal_position": 1,
                "char_length": 11,
                "numeric_precision": 10,
                "numeric_scale": 0,
                "datetime_precision": 0,
                "has_no_default": false,
                "default_value_null": false,
                "default_value": "AAAAAA==",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "",
                "column_key": 2,
                "column_type_utf8": "int(11)",
                "elements": [],
                "collation_id": 8
            },
            {
                "name": "c",
                "type": 29,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": false,
                "ordinal_position": 2,
                "char_length": 10,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "datetime_precision": 0,
                "has_no_default": false,
                "default_value_null": true,
                "default_value": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "",
                "column_key": 1,
                "column_type_utf8": "char(10)",
                "elements": [],
                "collation_id": 8
            }
        ],
        "schema_ref": "test",
        "hidden": false,
        "se_private_id": 18446744073709551615,
        "engine": "MyISAM",
        "comment": "",
        "se_private_data": "",
        "row_format": 1,
        "partition_type": 0,
        "partition_expression": "",
        "default_partitioning": 0,
        "subpartition_type": 0,
        "subpartition_expression": "",
        "default_subpartitioning": 0,
        "indexes": [
            {
                "name": "PRIMARY",
                "hidden": false,
                "is_generated": false,
                "ordinal_position": 1,
                "comment": "",
                "options": "flags=0;",
                "se_private_data": "",
                "type": 1,
                "algorithm": 2,
                "is_algorithm_explicit": false,
                "is_visible": true,
                "engine": "MyISAM",
                "elements": [
                    {
                        "ordinal_position": 1,
                        "length": 4,
                        "order": 2,
                        "column_opx": 0
                    }
                ]
            }
        ],
        "foreign_keys": [],
        "partitions": [],
        "collation_id": 8
    }
}

If the table has many columns, the structure becomes quite large.

Let's try some new features:

mysql [localhost] {root} ((none)) > create role role1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > grant all on test.* to role1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > create role role2;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} ((none)) > grant select on *.* to role2;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > select host, user from mysql.user;
+-----------+-------------+
| host      | user        |
+-----------+-------------+
| %         | role1       |
| %         | role2       |
| 127.%     | msandbox    |
| 127.%     | msandbox_ro |
| 127.%     | msandbox_rw |
| 127.%     | rsandbox    |
| localhost | msandbox    |
| localhost | msandbox_ro |
| localhost | msandbox_rw |
| localhost | mysql.sys   |
| localhost | root        |
+-----------+-------------+
11 rows in set (0.00 sec)

From this, we see that roles are users. In fact, you can assign a user to another user, as if it were a role.

mysql [localhost] {root} ((none)) > show grants for role1;
+-------------------------------------------------+
| Grants for role1@%                              |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `role1`@`%`               |
| GRANT ALL PRIVILEGES ON `test`.* TO `role1`@`%` |
+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql [localhost] {root} ((none)) > create user user1 identified by 'msandbox';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} ((none)) > grant role1 to user1;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} ((none)) > alter user user1 default role   role1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > show grants for user1;
+-----------------------------------+
| Grants for user1@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%` |
| GRANT `role1`@`%` TO `user1`@`%`  |
+-----------------------------------+
2 rows in set (0.00 sec)

mysql [localhost] {root} ((none)) > show grants for user1 using role1;
+-------------------------------------------------+
| Grants for user1@%                              |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%`               |
| GRANT ALL PRIVILEGES ON `test`.* TO `user1`@`%` |
| GRANT `role1`@`%` TO `user1`@`%`                |
+-------------------------------------------------+
3 rows in set (0.00 sec)

 create user user2 identified by 'msandbox';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} ((none)) > grant role2 to user2;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} ((none)) > show grants for user2;
+-----------------------------------+
| Grants for user2@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `user2`@`%` |
| GRANT `role2`@`%` TO `user2`@`%`  |
+-----------------------------------+
2 rows in set (0.00 sec)

mysql [localhost] {root} ((none)) > show grants for user2 using role2;
+------------------------------------+
| Grants for user2@%                 |
+------------------------------------+
| GRANT SELECT ON *.* TO `user2`@`%` |
| GRANT `role2`@`%` TO `user2`@`%`   |
+------------------------------------+
2 rows in set (0.01 sec)

Now we connect with user1

mysql [localhost] {user1} ((none)) > use mysql
ERROR 1044 (42000): Access denied for user 'user1'@'%' to database 'mysql'
mysql [localhost] {user1} ((none)) > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql [localhost] {user1} (test) > show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

That seems to be correct, as user1 can only access the test database. Let's try user2:

mysql [localhost] {user2} ((none)) > show tables from test;
ERROR 1044 (42000): Access denied for user 'user2'@'%' to database 'test'
mysql [localhost] {user2} ((none)) > show grants;
+-----------------------------------+
| Grants for user2@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `user2`@`%` |
| GRANT `role2`@`%` TO `user2`@`%`  |
+-----------------------------------+
2 rows in set (0.00 sec)

mysql [localhost] {user2} ((none)) > set role role2;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {user2} ((none)) > show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.01 sec)

Here we have something that, unless we read the documentation carefully, we may miss. A role is ineffective if is not assigned. For user1, we did an ALTER USER and set the default role. Since we didn't do it for user2, we need to set the role explicitly before using its powers.

We can also assign to a user several roles, and activate them all:

mysql [localhost] {root} ((none)) > create user user3 identified by 'msandbox';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > grant role1 to user3;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} ((none)) > grant role2 to user3;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} ((none)) > alter user user3 default role   role1,role2;
Query OK, 0 rows affected (0.00 sec)

Now, when we connect with user3 we can see all tables (SELECT grant from role2) and create or modify tables in test (all privileges on test database from role1.)

One final observation, just because it became crucial when I was starting to test these new binaries. Look at the size of the latest major versions of MySQL:

$ ls -lh mysql-5.5.52-linux2.6-x86_64.tar.gz
-rw-rw-r-- 1 root root 178M Aug 26 15:38 mysql-5.5.52-linux2.6-x86_64.tar.gz

$ du -sh mysql-5.5.52-linux2.6-x86_64/
690M     mysql-5.5.52-linux2.6-x86_64/

$ ls -lh mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz
-rw-rw-r-- 1 root root 300M Aug 26 12:52 mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz

$ du -sh mysql-5.6.33-linux-glibc2.5-x86_64/
1.1G     mysql-5.6.33-linux-glibc2.5-x86_64/

$ ls -lh mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz
-rw-rw-r-- 1 root root 612M Aug 25 13:08 mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz

$ du -sh mysql-5.7.15-linux-glibc2.5-x86_64/
2.5G     mysql-5.7.15-linux-glibc2.5-x86_64/

$ ls -lh mysql-8.0.0-dmr-linux-glibc2.12-x86_64.tar.gz
-rwxr-xr-x 1 root root 961M Aug 26 17:37 mysql-8.0.0-dmr-linux-glibc2.12-x86_64.tar.gz

$ du -sh mysql-8.0.0-dmr-linux-glibc2.12-x86_64/
3.6G     mysql-8.0.0-dmr-linux-glibc2.12-x86_64/