A look at MySQL 5.7 DMR

So I figured it was about time I looked at MySQL 5.7. This is a high level overview, but I was looking over the MySQL 5.7 in a nutshell document:

• http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

So I am starting with a fresh Fedora 20 (Xfce) install.
Overall, I will review a few items that I found curious and interesting with MySQL 5.7. The nutshell has a lot of information so well worth a review.

I downloaded the MySQL-5.7.4-m14-1.linux_glibc2.5.x86_64.rpm-bundle.tar

The install was planned on doing the following

# tar -vxf MySQL-5.7.4-m14-1.linux_glibc2.5.x86_64.rpm-bundle.tar
# rm -f mysql-community-embedded*
]# ls -a MySQL-*.rpm
MySQL-client-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-embedded-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-shared-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-devel-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-server-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-test-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
# yum -y install MySQL-*.rpm
Complete!


While it said Complete I also noticed an error. It should have not finished the install if it found an error but ok....

FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db:
Data::Dumper


This error was confirmed .. 

# /etc/init.d/mysql start
Starting MySQL............ ERROR! The server quit without updating PID file
# tail /var/lib/mysql/fedora20mysql57.localdomain.err
ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
# /usr/bin/mysql_install_db
FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db:
Data::Dumper
# yum -y install perl-Data-Dumper
# /usr/bin/mysql_install_db
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
# chown -R mysql:mysql /var/lib/mysql/mysql/
# cat /root/.mysql_secret
# mysql -u root -p
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('somepassword');
Query OK, 0 rows affected (0.01 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.4-m14 |
+-----------+


A more robust process for upgrades and etc is documented here:
http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html
Check to ensure you have GLIBC_2.15 if you plan to install this on your OS.

OK so now that it is installed, what do we have.

• Security improvements
• Installation creates no anonymous-user accounts

mysql> select User , Host,plugin from mysql.user \G
*************************** 1. row ***************************
  User: root
  Host: localhost
plugin: mysql_native_password


• Installation creates no test database.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+


• Password Expiration Policy  

mysql> SELECT @@default_password_lifetime \G
*************************** 1. row ***************************
@@default_password_lifetime: 360


These are all long overdue improvements, and thank you all for the improvements.
So now to look over the rest, we at least want some kind of data and schema. So I will install the world database for the tests. 

# wget http://downloads.mysql.com/docs/world_innodb.sql.gz
# gzip -d world_innodb.sql.gz
# mysql -u root -p -e "create database world";
# mysql -u root -p world < world_innodb.sql


• Online
• ALTER TABLE.  ALTER TABLE now supports a RENAME INDEX clause that renames an index

# mysql -u root -p world
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB
mysql> ALTER TABLE City ALGORITHM=INPLACE, RENAME KEY CountryCode TO THECountryCode;
Query OK
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `THECountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB



• InnoDB enhancements 
  • VARCHAR size may be increased using an in-place ALTER TABLE
  
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `THECountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB
mysql> ALTER TABLE City ALGORITHM=COPY, CHANGE COLUMN Name Name VARCHAR(35);
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(35) DEFAULT NULL,
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `THECountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB 
mysql> ALTER TABLE City ALGORITHM=INPLACE, CHANGE COLUMN Name Name VARCHAR(255);
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `THECountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB
  • InnoDB temporary table metadata is no longer stored to InnoDB system tables. The table is created when the first SELECT statement is run against it.
  
mysql> select * from INNODB_TEMP_TABLE_INFO;
mysql> show create table INNODB_TEMP_TABLE_INFO;
CREATE TEMPORARY TABLE `INNODB_TEMP_TABLE_INFO` (
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(202) DEFAULT NULL,
  `N_COLS` int(11) unsigned NOT NULL DEFAULT '0',
  `SPACE` int(11) unsigned NOT NULL DEFAULT '0',
  `PER_TABLE_TABLESPACE` varchar(64) DEFAULT NULL,
  `IS_COMPRESSED` varchar(64) DEFAULT NULL
) ENGINE=MEMORY
CODE HERE

  • InnoDB now supports MySQL-supported spatial data types
  • This is HUGE. Because it has been one of the reasons people picked PostgreSQL over MySQL.
  • http://www.oracle.com/us/corporate/press/2180737
  • http://dev.mysql.com/doc/refman/5.7/en/mysql-spatial-datatypes.html
  • http://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html
  • http://www.mysqlperformanceblog.com/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/
  • There is now a separate tablespace for all non-compressed InnoDB temporary tables
  mysql> select @@innodb_temp_data_file_path \G
*************************** 1. row ***************************
@@innodb_temp_data_file_path: ibtmp1:12M:autoextend

• Condition handling ::
  GET STACKED DIAGNOSTICS

mysql> DROP TABLE test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'
mysql> GET DIAGNOSTICS CONDITION 1  @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
Query OK, 0 rows affected (0.45 sec)

mysql> SELECT @p1, @p2 \G
*************************** 1. row ***************************
@p1: 42S02
@p2: Unknown table 'test.no_such_table'
1 row in set (0.01 sec)

• Triggers
  The trigger limitation has been lifted and multiple triggers are permitted. Please see the documentation as they give a good example. I will demo it some here just to show that multiple triggers on a single table are possible.

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+
mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
    ->    FOR EACH ROW PRECEDES ins_sum
    ->    SET
    ->    @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
    ->    @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
mysql> SHOW triggers \G
*************************** 1. row ***************************
             Trigger: ins_transaction
               Event: INSERT
               Table: account
           Statement: SET
   @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
   @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0)
              Timing: BEFORE
             Created: 2014-05-14 21:23:49.66
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: ins_sum
               Event: INSERT
               Table: account
           Statement: SET @sum = @sum + NEW.amount
              Timing: BEFORE
             Created: 2014-05-14 21:22:47.91
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

• Globalization improvements
• MySQL 5.7.4 includes a gb18030 character set that supports the China National Standard GB18030 character set.
• http://dev.mysql.com/doc/refman/5.7/en/charset-examples.html

mysql> CREATE TABLE t1
    -> (  c1 CHAR(10) CHARACTER SET latin1
    -> ) DEFAULT CHARACTER SET gb18030 COLLATE gb18030_chinese_ci;
Query OK


• Changing the replication master without STOP SLAVE
• http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
• Read more about this on via the two links above. This could be another very nice improvement. The documentation gives many examples.


• HANDLER with partitioned tables

mysql> HANDLER City OPEN AS city_handle;
mysql> HANDLER city_handle READ FIRST;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+
mysql> HANDLER city_handle READ NEXT LIMIT 3;
+----+-----------+-------------+---------------+------------+
| ID | Name      | CountryCode | District      | Population |
+----+-----------+-------------+---------------+------------+
|  5 | Amsterdam | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag      | NLD         | Zuid-Holland  |     440900 |
+----+-----------+-------------+---------------+------------+
mysql> CREATE TABLE `t2` (
    ->   `t2_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `inserttimestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   `somevalue` int(10) unsigned DEFAULT NULL,
    ->   `rowLastUpdateTime` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`t2_id`,`inserttimestamp`)
    -> ) ENGINE=InnoDB;
mysql> ALTER TABLE t2
    ->  PARTITION BY RANGE ( TO_DAYS(inserttimestamp) ) (
    ->      PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
    ->      PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
    ->      PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
    ->      PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
    ->      PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
    ->      PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
    ->      PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
    ->      PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
    ->      PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
    ->      PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
    ->      PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
    ->      PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
    ->      PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
    ->  );
mysql> INSERT INTO t2 VALUES (NULL,NOW(),1,NOW());
mysql> HANDLER t2 OPEN AS t_handle;
mysql> HANDLER t_handle READ FIRST;
+-------+---------------------+-----------+---------------------+
| t2_id | inserttimestamp     | somevalue | rowLastUpdateTime   |
+-------+---------------------+-----------+---------------------+
|     1 | 2014-05-14 21:53:28 |         1 | 2014-05-14 21:53:28 |
+-------+---------------------+-----------+---------------------+


• Database name rewriting with mysqlbinlog 
• Renaming of databases by mysqlbinlog
• This is a feature that Percona has had for ages. 
• http://www.percona.com/doc/percona-server/5.1/release-notes/Percona-Server-5.1.62-13.3.html#5.1.62-13.3

mysql> select @@binlog_format\G
*************************** 1. row ***************************
@@binlog_format: ROW

# mysqlbinlog --database=world  mysql-bin.000002 | grep world | wc -l
22543# mysqlbinlog --rewrite-db='world->renameddb'  mysql-bin.000002 | grep renameddb | wc -l
22542