Wednesday, May 14, 2014

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:
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.
mysql> select User , Host,plugin from mysql.user \G
*************************** 1. row ***************************
  User: root
  Host: localhost
plugin: mysql_native_password
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
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
# 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


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
mysql> CREATE TABLE t1
    -> (  c1 CHAR(10) CHARACTER SET latin1
    -> ) DEFAULT CHARACTER SET gb18030 COLLATE gb18030_chinese_ci;
Query OK
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 |
+-------+---------------------+-----------+---------------------+
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

No comments:

Post a Comment

@AnotherMySQLDBA