Experimenting with the new Data Dictionary Labs Release

Labs releases are intended to provide early access to new features. As Gopal notes in his recent blog post About the Data Dictionary Labs Release, there is a notable restriction where upgrading from any previous MySQL database version is not supported.

Today, I thought I would demonstrate how to get the data dictionary lab up and running on a fresh Ubuntu 14.04 installation:

# Download from labs.mysql.com
wget http://downloads.mysql.com/snapshots/pb/mysql-5.7.5-labs-dd/mysql-5.7.5-labs-dd-linux-el6-x86_64.tar.gz
# extract it to /usr/local/mysql
# more or less following instructions in INSTALL-BINARY
groupadd mysql
useradd -r -g mysql mysql
tar -xzf mysql-5.7.5-labs-dd-linux-el6-x86_64.tar.gz
mv mysql-5.7.5-labs-dd-linux-el6-x86_64 /usr/local/
ln -s /usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64 /usr/local/mysql
cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .

At this point, the next step in the instructions actually failed for me:

bin/mysql_install_db --user=mysql
2014-10-15 09:22:34 [ERROR]   The data directory needs to be specified.

This behavior change appears to be the result of mysql_install_db being rewritten in C++. Specifying the datadir addresses this, but also results in another error:

bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
2014-10-15 09:24:15 [ERROR]   The child process terminated prematurely. Errno= 32
2014-10-15 09:24:15 [ERROR]   Failed to execute /usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64/bin/mysqld --no-defaults --install-server --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US
-- server log begin --

Error 32 is a broken pipe. From the description, we can see that mysql_install_db tried to call a subprocess (mysqld –install-server), but it exited prematurely. The description here doesn’t show why it exited prematurely, but we can execute this command directly to find out:

/usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64/bin/mysqld --no-defaults --install-server --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US
/usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

OK! We don’t have the async IO dependency installed on this system, which is used by InnoDB:

apt-get install libaio1
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  linux-headers-3.13.0-36 linux-headers-3.13.0-36-generic
  linux-image-3.13.0-36-generic linux-image-extra-3.13.0-36-generic
Use 'apt-get autoremove' to remove them.
The following NEW packages will be installed:
0 upgraded, 1 newly installed, 0 to remove and 3 not upgraded.
Need to get 6,364 B of archives.
After this operation, 53.2 kB of additional disk space will be used.
Get:1 http://mirrors.digitalocean.com/ubuntu/ trusty/main libaio1 amd64 0.3.109-4 [6,364 B]
Fetched 6,364 B in 0s (319 kB/s)
Selecting previously unselected package libaio1:amd64.
(Reading database ... 116531 files and directories currently installed.)
Preparing to unpack .../libaio1_0.3.109-4_amd64.deb ...
Unpacking libaio1:amd64 (0.3.109-4) ...
Setting up libaio1:amd64 (0.3.109-4) ...
Processing triggers for libc-bin (2.19-0ubuntu6.3) ...

Now to try the original install command again:

bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data

It should return no errors now. To follow the remaining steps in the INSTALL-BINARY instructions:

chown -R root .
chown -R mysql data
bin/mysqld_safe --user=mysql &

In MySQL 5.7, installation is secure by default. This means that a root password is automatically generated, and stored in ~/.mysql_secret:

cat ~/.mysql_secret
# Password set for user 'root@localhost' at 2014-10-15 09:32:09

It is possible to change this password with the MySQL command line client:

./bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.5-labs-dd
Copyright (c) 2000, 2014, 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
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Query OK, 0 rows affected (0.00 sec)

As Gopal mentioned in his blog post, the labs release runs completely without frm files:

ls /usr/local/mysql/data/mysql
catalogs.ibd                  help_relation.ibd       slave_relay_log_info.ibd
character_sets.ibd            help_topic.ibd          slave_worker_info.ibd
collations.ibd                index_column_usage.ibd  slow_log.CSM
columns.ibd                   indexes.ibd             slow_log.CSV
columns_priv.MYD              index_partitions.ibd    table_partitions.ibd
columns_priv.MYI              index_stats.ibd         table_partition_values.ibd
column_type_elements.ibd      innodb_index_stats.ibd  tables.ibd
db.MYD                        innodb_table_stats.ibd  tablespace_files.ibd
db.MYI                        ndb_binlog_index.MYD    tablespaces.ibd
db.opt                        ndb_binlog_index.MYI    tables_priv.MYD
engine_cost.ibd               parameters.ibd          tables_priv.MYI
event.MYD                     plugin.ibd              table_stats.ibd
event.MYI                     proc.MYD                time_zone.ibd
events.ibd                    proc.MYI                time_zone_leap_second.ibd
foreign_key_column_usage.ibd  procs_priv.MYD          time_zone_name.ibd
foreign_keys.ibd              procs_priv.MYI          time_zone_transition.ibd
func.MYD                      proxies_priv.MYD        time_zone_transition_type.ibd
func.MYI                      proxies_priv.MYI        triggers.ibd
general_log.CSM               routines.ibd            user.MYD
general_log.CSV               schemata.ibd            user.MYI
gtid_executed.ibd             server_cost.ibd         view_table_usage.ibd
help_category.ibd             servers.ibd
help_keyword.ibd              slave_master_info.ibd

information_schema is a now also a set of views on top of real InnoDB tables (stored in the mysql schema). As a VIEW we can explain it, and since extended explain is always enabled in 5.7, SHOW WARNINGS will show the rewritten form:

mysql> SELECT * FROM information_schema.tables WHERE table_name = 'user'\G
*************************** 1. row ***************************
   TABLE_SCHEMA: mysql
     TABLE_NAME: user
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 1
    CREATE_TIME: 2014-10-15 09:32:10
    UPDATE_TIME: 2014-10-15 09:32:10
 CREATE_OPTIONS: stats_persistent=1
  TABLE_COMMENT: Users and global privileges
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM information_schema.tables WHERE table_name = 'user'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cat
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: name
      key_len: 194
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sch
   partitions: NULL
         type: ref
possible_keys: PRIMARY,catalog_id
          key: catalog_id
      key_len: 8
          ref: mysql.cat.id
         rows: 3
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
   partitions: NULL
         type: eq_ref
possible_keys: schema_id
          key: schema_id
      key_len: 202
          ref: mysql.sch.id,const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: col
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.tbl.collation_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: stat
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 388
          ref: mysql.sch.name,const
         rows: 1
     filtered: 100.00
        Extra: NULL
5 rows in set, 1 warning (0.00 sec)
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,`tbl`.`engine` AS `ENGINE`,`tbl`.`version` AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,ifnull(`stat`.`table_rows`,1) AS `TABLE_ROWS`,`stat`.`avg_row_length` AS `AVG_ROW_LENGTH`,`stat`.`data_length` AS `DATA_LENGTH`,`stat`.`max_data_length` AS `MAX_DATA_LENGTH`,`stat`.`index_length` AS `INDEX_LENGTH`,`stat`.`data_free` AS `DATA_FREE`,ifnull(`stat`.`auto_increment`,1) AS `AUTO_INCREMENT`,`tbl`.`created` AS `CREATE_TIME`,`tbl`.`last_altered` AS `UPDATE_TIME`,'' AS `CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,`stat`.`checksum` AS `CHECKSUM`,get_dd_create_options(`tbl`.`options`) AS `CREATE_OPTIONS`,`tbl`.`comment` AS `TABLE_COMMENT` from `mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` left join `mysql`.`collations` `col` on((`col`.`id` = `tbl`.`collation_id`)) left join `mysql`.`table_stats` `stat` on(((`stat`.`schema_name` = `sch`.`name`) and (`stat`.`table_name` = 'user'))) where ((`tbl`.`schema_id` = `sch`.`id`) and (`sch`.`catalog_id` = `cat`.`id`) and (`tbl`.`name` = 'user') and can_access_table(`sch`.`name`,`tbl`.`name`)) order by `sch`.`name`,`tbl`.`name`
1 row in set (0.00 sec)

That’s it for today. Enjoy testing the data dictionary, and please send in your feedback!