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:
  libaio1
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
Wpckgmde+U,o

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
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET PASSWORD=PASSWORD('acdc');
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_CATALOG: def
   TABLE_SCHEMA: mysql
     TABLE_NAME: user
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 1
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-10-15 09:32:10
    UPDATE_TIME: 2014-10-15 09:32:10
     CHECK_TIME:
TABLE_COLLATION: utf8_bin
       CHECKSUM: NULL
 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)

mysql> SHOW WARNINGS\G
*************************** 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!

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • Muhammad Dawud

    hi.. have you tried http plugin of mysql? I’ve finally could make it run, but I have been through some trial & error that I can’t explain. :3

  • Simon J Mudd

    A brief comment. I had a very quick look at this labs release version a month or so ago. The requirement to start from an empty instance makes it much harder to try out than would otherwise be the case, and the place where this would be useful is on servers which have a large number of tables.

    Providing some sort of “import script” which could scan over a stopped mysql set of files and build the data dictionary tables would be nice. This doesn’t need to be done with mysqld being online. mysqldump + load is really not practical on the servers where the DD would be useful.

    Some very brief testing showed that for a small number of tables performance seemed better with the standard MySQL. I guess that’s expected as this hasn’t been optimised yet, but when a larger number of tables were being scanned things were better. That said the EXPLAIN output seemed to imply very heavy joins of the new DD tables, when in some cases I think the joins were perhaps unnecessary.

    So in short, it’s hard to test this if I can’t test it with real data and some sort of “real workload”. I can clone/copy a system using snapshots quite easily but dumping and loading the whole database on these type of systems is not really practical.

    However, it is good to see work in this direction. It’s clear it won’t make it into 5.7 but it would be great to see this in 5.8.

    • Hi Simon,

      To clarify (mostly for other readers here) importing data from earlier MySQL releases will be supported. I have another post here with our planned support path:

      http://www.tocker.ca/2014/12/01/new-data-dictionary-upgrading-from-earlier-mysql-releases.html

      The ‘deal’ with labs is that the functionality is not quite complete, but we are putting the code out there because we’ve reached a point where we are ready for feedback. I apologize that in this cases the functionality missing does make it harder to test.

      • Simon J Mudd

        I understand that the DD support is still early and work in progress. I also assume that there will be a way to import data etc. So my initial assessment (and I want to have time to look at it further) is it does work but seems slower when the number of tables is reasonably small ( 5.7 DD-less -> 5.7 DD I do not see a big deal. So I am perfectly happy with doing a 5.6 to 5.7 upgrade (which is already scripted), and then following that by a 5.7 DD-less to 5.7 DD upgrade to get the setup working for testing. If the 5.7 DD is not going to make it into 5.7 GA then we can at least continue testing this as newer builds come out and give you guys feedback. Adding the complexity of checking for backward compatibility for upgrades from < 5.7 seems to be making the whole process harder than necessary.