MySQL 8.0.1でバイナリーログに original_commit_timestamp と immediate_commit_timestamp が追加された

See also New monitoring replication features and more! | MySQL High Availability

original_committed_timestampはマスターで実行された時のタイムスタンプが、immediate_commit_timestampはそのサーバーで実際に実行された時のタイムスタンプがそれぞれ入る。 単位はいずれもマイクロ秒。

マスターで実行した CREATE DATABASE d1 のバイナリーログ。

$ /usr/mysql/8.0.1/bin/mysqlbinlog master/data/mysql-bin.000002
..
#170428 17:54:25 server id 1  end_log_pos 226 CRC32 0xe0efd740  GTID    last_committed=0        sequence_number=1       original_committed_timestamp=1493369665593157 immediate_commit_timestamp=1493369665593157
# original_commit_timestamp=1493369665593157 (2017-04-28 17:54:25.593157 JST)
# immediate_commit_timestamp=1493369665593157 (2017-04-28 17:54:25.593157 JST)
/*!80001 SET @@session.original_commit_timestamp=1493369665593157*//*!*/;
SET @@SESSION.GTID_NEXT= '00012009-1111-1111-1111-111111111111:1'/*!*/;
# at 226
#170428 17:54:25 server id 1  end_log_pos 323 CRC32 0xc5704ebe  Query   thread_id=8     exec_time=0     error_code=0    Xid = 44
..
CREATE DATABASE d1
/*!*/;

それがレプリケートされたスレーブのバイナリーログ。

$ /usr/mysql/8.0.1/bin/mysqlbinlog node2/data/mysql-bin.000002
..
# at 154
#170428 17:54:25 server id 1  end_log_pos 233 CRC32 0xddb16cfd  GTID    last_committed=0        sequence_number=1       original_committed_timestamp=1493369665593157 immediate_commit_timestamp=1493369665643968
# original_commit_timestamp=1493369665593157 (2017-04-28 17:54:25.593157 JST)
# immediate_commit_timestamp=1493369665643968 (2017-04-28 17:54:25.643968 JST)
/*!80001 SET @@session.original_commit_timestamp=1493369665593157*//*!*/;
SET @@SESSION.GTID_NEXT= '00012009-1111-1111-1111-111111111111:1'/*!*/;
# at 233
#170428 17:54:25 server id 1  end_log_pos 330 CRC32 0x1ced03f5  Query   thread_id=8     exec_time=0     error_code=0    Xid = 11
..
CREATE DATABASE d1
/*!*/;

これに合わせて、 performance_schema.replication_applier_status_by_worker にも LAST_APPLIED_TRANSACTION_*_TIMESTAMP と APPLYING_TRANSACTION_*_TIMESTAMP が追加されてる。

これを使えば5.7とそれまでの Seconds_Behind_Master みたいにがんばって現在時刻との差とかを求めなくても良くなるようになる（んだと思う）

mysql> SELECT * FROM replication_applier_status_by_worker\G
*************************** 1. row ***************************
                                       CHANNEL_NAME:
                                          WORKER_ID: 0
                                          THREAD_ID: 39
                                      SERVICE_STATE: ON
                                  LAST_ERROR_NUMBER: 0
                                 LAST_ERROR_MESSAGE:
                               LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_APPLIED_TRANSACTION: 00012009-1111-1111-1111-111111111111:1
 LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2017-04-28 17:54:25.593157
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2017-04-28 17:54:25.593157
     LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2017-04-28 17:54:25.596175
       LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2017-04-28 17:54:25.645874
                               APPLYING_TRANSACTION:
     APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
    APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
         APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.01 sec)

ところで、スレーブのバイナリーログ上の immediate_commit_timestamp が p_s. replication_applier_status_by_worker の LAST_APPLIED_TRANSACTION_*_TIMESTAMP のどれとも合わないんだけど、これってこれでいいの…？

​

MySQL 8.0のDROP TABLEがアトミックになっているっぽい件

MySQL 5.7とそれ以前で、「存在するテーブルと存在しないテーブルを一緒にDROP TABLE」しようとすると

mysql57> CREATE TABLE t1 (num int);
Query OK, 0 rows affected (0.01 sec)

mysql57> CREATE TABLE t3 (num int);
Query OK, 0 rows affected (0.01 sec)

mysql57> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t1           |
| t3           |
+--------------+
2 rows in set (0.00 sec)

mysql57> DROP TABLE t1, t2, t3;
ERROR 1051 (42S02): Unknown table 'd1.t2'

mysql57> SHOW TABLES;
Empty set (0.00 sec)

エラーにはなるけど消せるものは消える。

MySQL 8.0だと

mysql80> CREATE TABLE t1 (num int);
Query OK, 0 rows affected (0.01 sec)

mysql80> CREATE TABLE t3 (num int);
Query OK, 0 rows affected (0.01 sec)

mysql80> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t1           |
| t3           |
+--------------+
2 rows in set (0.00 sec)

mysql80> DROP TABLE t1, t2, t3;
ERROR 1051 (42S02): Unknown table 'd1.t2'

mysql80> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t1           |
| t3           |
+--------------+
2 rows in set (0.00 sec)

おおおおお消えてない！ ちゃんと「エラーが返った = 操作は失敗している」が成立しているぞぞぞ。

mysql80> ALTER TABLE t1 ENGINE= MyISAM;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80> DROP TABLE t1, t2, t3;
ERROR 1051 (42S02): Unknown table 'd1.t2'

mysql80> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t1           |
| t3           |
+--------------+
2 rows in set (0.00 sec)

マイア勇 MyISAMに変えても同じ動きだった。ちょっとびっくり。

​

MySQLのDATETIME型で秒の小数部を扱うときのDEFAULT句

この CREATE TABLE ステートメントは転ける。

mysql57> CREATE TABLE t1 (num INT UNSIGNED NOT NULL, dt DATETIME(3) DEFAULT CURRENT_TIMESTAMP);
ERROR 1067 (42000): Invalid default value for 'dt'

CURRENT_TIMESTAMP関数 が DATETIME(0)型 を返すので、dtカラムの型である DATETIME(3)型 と合わないからだというわけで、

mysql57> CREATE TABLE t1 (num INT UNSIGNED NOT NULL, dt DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3));
Query OK, 0 rows affected (0.01 sec)

mysql57> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `dt` datetime(3) DEFAULT CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

これで通る。 なお、 ON UPDATE 句を書いた場合も同じ。

mysql57> CREATE TABLE t2 (num INT UNSIGNED NOT NULL, dt DATETIME(3) ON UPDATE CURRENT_TIMESTAMP);
ERROR 1294 (HY000): Invalid ON UPDATE clause for 'dt' column

mysql57> CREATE TABLE t2 (num INT UNSIGNED NOT NULL, dt DATETIME(3) ON UPDATE CURRENT_TIMESTAMP(3));Query OK, 0 rows affected (0.01 sec)

mysql57> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `num` int(10) unsigned NOT NULL,
  `dt` datetime(3) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

とても余談として、CURRENT_TIMESTAMP関数が NOW関数 のシノニムってことは、これひょっとして DEFAULT NOW() でもいけるのでは？ と思ったらいけた。しかも昔からだった。

mysql55> CREATE TABLE t1 (num INT UNSIGNED NOT NULL, dt TIMESTAMP DEFAULT NOW()); -- 5.5だからTIMESTAMP型じゃないとデフォルトを受けられない
Query OK, 0 rows affected (0.01 sec)

mysql55> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ちゃんとCURRENT_TIMESTAMPとして扱われている。 ただし、CURRENT_TIMESTAMPと違ってNOWはかっこは省略できない。

mysql55> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2017-04-20 15:07:50 |
+---------------------+
1 row in set (0.00 sec)

mysql55> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2017-04-20 15:08:03 |
+---------------------+
1 row in set (0.00 sec)

mysql55> SELECT NOW;
ERROR 1054 (42S22): Unknown column 'NOW' in 'field list'

mysql55> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2017-04-20 15:08:15 |
+---------------------+
1 row in set (0.00 sec)

​

MySQL 5.7.17からエラーログに出るようになった deprecated partition engine に関するNote

こんなやつのこと。

2017-04-18T23:54:08.224673+09:00 0 [Note] /usr/mysql/5.7.18/bin/mysqld: ready for connections.
Version: '5.7.18-log'  socket: '/usr/mysql/5.7.18/data/mysql.sock'  port: 64057  Source distribution
2017-04-18T23:54:08.224691+09:00 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.
2017-04-18T23:54:08.224696+09:00 0 [Note] Beginning of list of non-natively partitioned tables
2017-04-18T23:54:08.316219+09:00 0 [Note] End of list of non-natively partitioned tables

MySQL 8.0.0 で完全になくなることが決まった（というか8.0.0の時点でもうない） PARTITIONストレージエンジン を「使ってないよね？ チェックするぞ？」という機能が MySQL 5.7.17 に入った。

MySQLのパーティショニングはストレージエンジンとして実装されてい た （ ./configure --help や cmake -i を使ったことがあれば、 -DWITH_PARTITION_STORAGE_ENGINE=ON とかそういうのに憶えがあるかも知れない）

MySQL 5.7のInnoDBに関しては InnoDBネイティブパーティショニング といってPARTITIONストレージエンジンを使わずにInnoDBの内部でパーティションを表現するようになった。

「今後、パーティションを使っててもFOREIGN KEY制約がつけられるようになるかも知れない」というのは、このInnoDBネイティブパーティショニングによるもの。

InnoDB Native Partitioning – Early Access | MySQL Server Blog

フツーに mysql_upgrade をかましていれば5.7にアップグレードした時点で変換されるはずなのだが、 mysql_upgrade -s とか avoid_temporal_upgradeを使ってゴニョゴニョするとか をしている…あるいはいっこ飛ばして5.6から8.0へダイレクトジャンプするとかだと、 PARTITIONストレージエンジン によってパーティショニングされていたInnoDBのテーブルがそのまま残ってしまう。

Prior to MySQL 5.7.6, partitioned InnoDB tables used the generic ha_partition partitioning handler employed by MyISAM and other storage engines not supplying their own partitioning handlers; in MySQL 5.7.6 and later, such tables are created using the InnoDB storage engine's own (or “native”) partitioning handler. Beginning with MySQL 5.7.9, you can upgrade an InnoDB table that was created in MySQL 5.7.6 or earlier (that is, created using ha_partition) to the InnoDB native partition handler using ALTER TABLE ... UPGRADE PARTITIONING. (Bug #76734, Bug #20727344) This version of ALTER TABLE does not accept any other options and can be used only on a single table at a time. You can also use mysql_upgrade in MySQL 5.7.9 or later to upgrade older partitioned InnoDB tables to the native partitioning handler.

MySQL :: MySQL 5.7 Reference Manual :: 14.1.8 ALTER TABLE Syntax

PARTITIONストレージエンジン そのものがなくなってしまうMySQL 8.0とそれ以降ではそのテーブルはそのままでは生きていけない……というわけで、過渡期にあたるMySQL 5.7にこのログが追加されたのだ（と思う）

default_password_lifetimeのとき に We agree with the original bug reporter that the default of 360 is surprising for users upgrading from previous releases of MySQL. と言っていたので、おそらく多少 ショックの少なそうな方法 を模索した結果なんだと思う。

ちなみに PARTITIONストレージエンジン なテーブルがあってもリストされるだけ（だと思う）なので、 ALTER TABLE .. ENGINE = INNODB はセルフサービスでやる必要がある。

さて、 InnoDBネイティブパーティショニング なので、もちろんInnoDB以外のストレージエンジンはサポートされていない（というかInnoDBの実装なのだから他のストレージエンジンに手を出せるわけがない）ので、MyISAMでパーティショニングしているヤーツがもし万一あったら今のうちにInnoDBにしておくのがよろしいかと思います。

​

MySQL 8.0.1からJOIN_ORDERヒントが書ける

こんな、ORDER BY狙いのキーを使いたくなるクエリーがあるじゃろ？

mysql80> EXPLAIN SELECT Name, Language, Population, Percentage FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5;
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
| id | select_type | table           | partitions | type | possible_keys                | key     | key_len | ref                | rows | filtered | Extra                                        |
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | Country         | NULL       | ALL  | PRIMARY,index_code_continent | NULL    | NULL    | NULL               |  239 |    14.29 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | CountryLanguage | NULL       | ref  | PRIMARY,CountryCode          | PRIMARY | 3       | world.Country.Code |    4 |   100.00 | NULL                                         |
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

JOINしているクエリーでORDER BY狙いのキーを効かせるにはORDER BY狙いのキーが駆動表側にないといけない ので、

1. STRAIGHT_JOIN に書き換えてORDER BYで使っているカラムを駆動表に固定する。ただしSTRAIGHT_JOINは内部結合なので、LEFT JOINは書き換えられない。
2. USE INDEXかFORCE INDEX でORDER BY狙いのキーを狙い撃つ。大概の場合はこれで上手く動くんだけれど、最悪の場合 内部表のままORDER BY狙いのキーを使ってインデックススキャンががががが

INNER JOINなら 1. + 2. （たまに、STRAIGHT_JOINでもORDER BY狙いのキーを取らないことがあったりした。最近少ない気がする）、そうでなければ 2. だけとしてORDER BY狙いのキーを押し込むことが多かったけれど、MySQL 8.0.1からは JOIN_ORDERのヒント句 が使えるようになった。

mysql80> EXPLAIN SELECT /*+ JOIN_ORDER (CountryLanguage, Country) */ Name, Language, Population, Percentage FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5;
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
| id | select_type | table           | partitions | type   | possible_keys                | key              | key_len | ref                               | rows | filtered | Extra       |
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
|  1 | SIMPLE      | CountryLanguage | NULL       | index  | PRIMARY,CountryCode          | index_percentage | 4       | NULL                              |    5 |   100.00 | Using index |
|  1 | SIMPLE      | Country         | NULL       | eq_ref | PRIMARY,index_code_continent | PRIMARY          | 3       | world.CountryLanguage.CountryCode |    1 |    14.29 | Using where |
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql80> EXPLAIN SELECT /*+ JOIN_PREFIX (Country) */ Name, Language, Population, Percentage FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5;
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
| id | select_type | table           | partitions | type | possible_keys                | key     | key_len | ref                | rows | filtered | Extra                                        |
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | Country         | NULL       | ALL  | PRIMARY,index_code_continent | NULL    | NULL    | NULL               |  239 |    14.29 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | CountryLanguage | NULL       | ref  | PRIMARY,CountryCode          | PRIMARY | 3       | world.Country.Code |    4 |   100.00 | NULL                                         |
+----+-------------+-----------------+------------+------+------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql80> EXPLAIN SELECT /*+ JOIN_SUFFIX (Country) */ Name, Language, Population, Percentage FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5;
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
| id | select_type | table           | partitions | type   | possible_keys                | key              | key_len | ref                               | rows | filtered | Extra       |
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
|  1 | SIMPLE      | CountryLanguage | NULL       | index  | PRIMARY,CountryCode          | index_percentage | 4       | NULL                              |    5 |   100.00 | Using index |
|  1 | SIMPLE      | Country         | NULL       | eq_ref | PRIMARY,index_code_continent | PRIMARY          | 3       | world.CountryLanguage.CountryCode |    1 |    14.29 | Using where |
+----+-------------+-----------------+------------+--------+------------------------------+------------------+---------+-----------------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

JOIN_ORDER(先に来るテーブル, 後に来るテーブル), または JOIN_PREFIX(先に来るテーブル), JOIN_SUFFIX(後に来るテーブル) の3つの書き方で指定できるぽい。

ORDER BY狙いのキーなら一番外側にあればそれでいいので、3つ以上の時も JOIN_ORDER より JOIN_PREFIX がいいのかな。

これでLEFT JOINでもORDER BY狙いのキーが狙いやすくなってすてきだ。

​

xtrabackupが実行中かどうかをSQLだけで確認する思考実験

はじまりは
畜生ペンギン@keny_lala のひとこと。

xtrabackupが今稼働中だよーってことがOSコマンドじゃなくてMySQLから分かる方法ありませんか？

— kentarokitagawa (@keny_lala) 2017年4月13日

Percona Serverには LOCK TABLES FOR BACKUP とかあったよなと思いつつ、たぶんPercona Serverじゃないので置いておく。

xtrabackup-2.4.6のソースコードをナナメに読んでいくと、 SET SESSION wait_timeout = 2147483 を押し込んでいる箇所があったので、ここで検出できないかなと思い付く。

取り敢えず王道（？）として、 performance_schema.variables_by_thread で引いてみた。

mysql57> SELECT * FROM performance_schema.variables_by_thread WHERE variable_name = 'wait_timeout' AND variable_value = 2147483;
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
|        50 | wait_timeout  | 2147483        |
+-----------+---------------+----------------+
1 row in set (0.02 sec)

ビンゴ。 そんなにキリの良い数字じゃないと思うんだけど、なんで2147483でハードコードしてあるんだろう。 ともあれ、これならフツーのアプリが使うこともなさそうなのでこれで検出できそう。

@yoku0825 なるほど！しかし、57限定になってします。。あ、専用ユーザ作ってユーザ名で見ればなんとかなるか。。

— kentarokitagawa (@keny_lala) 2017年4月13日

:(；ﾞﾟ’ωﾟ’): あ、5.6もサポートしないとダメ？ ってかこのテーブル5.7で追加されたんだっけか。。。

如何にも爪痕を残しそうな PERCONA_SCHEMA.xtrabackup_history なるものをCREATEしている箇所があったけど、これはxtrabackupが終わった後に通るのだそう(´･ω･｀)

仕方ない、ユーザーロックするパッチ当てるか。。

*** storage/innobase/xtrabackup/src/backup_mysql.cc.orig        2017-02-27 16:47:06.000000000 +0900
--- storage/innobase/xtrabackup/src/backup_mysql.cc     2017-04-13 14:44:07.149003517 +0900
***************
*** 162,167 ****
--- 162,169 ----

        xb_mysql_query(connection, "SET SESSION wait_timeout=2147483",
                       false, true);
+         xb_mysql_query(connection, "SELECT get_lock('xtrabackup', @@wait_timeout)",
+                        false, true);

        return(connection);
  }
***************
*** 1697,1702 ****
--- 1699,1706 ----

        free(uuid);
        free(server_version);
+         xb_mysql_query(connection, "SELECT release_lock('xtrabackup')",
+                        false, false);

        return(true);
  }

https://gist.github.com/yoku0825/cf407f2d10ff27a04ff2f50fe16ddfa7

というわけで接続時に get_lock して終了時に release_lock するクエリーを入れ込んだ。

これなら is_used_lock 関数だけでSQLインターフェイスから確認できるし

mysql57> SELECT is_used_lock('xtrabackup');
+----------------------------+
| is_used_lock('xtrabackup') |
+----------------------------+
|                         25 |
+----------------------------+
1 row in set (0.00 sec)

ついでにxbの二重起動も防げる。

mysql57> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-----------+-----------------------------------------------+
| Id | User | Host      | db   | Command | Time | State     | Info                                          |
+----+------+-----------+------+---------+------+-----------+-----------------------------------------------+
| 25 | root | localhost | NULL | Sleep   |  691 |           | NULL                                          |
| 28 | root | localhost | NULL | Query   |    7 | User lock | SELECT get_lock('xtrabackup', @@wait_timeout) |
| 29 | root | localhost | NULL | Query   |    0 | starting  | SHOW PROCESSLIST                              |
+----+------+-----------+------+---------+------+-----------+-----------------------------------------------+
3 rows in set (0.00 sec)

よし、Feature Request出しに行くか？

​

MySQL 8.0.1の新顔、GROUPING集約関数

TL;DR

WITH ROLLUPの結果行をHAVING条件に書けるようすることができる。 それ以外の時には使わない。

MySQL :: MySQL 8.0 Reference Manual :: 13.20 Miscellaneous Functions

使い方。 そもそも WITH ROLLUP の使い方を知らないと楽しくもなんともないので WITH ROLLUP の説明から。

まずは WITH ROLLUP なしバージョン（SUM関数を噛ませてるのはあとで WITH ROLLUP した時のため）

mysql80> SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name;
+---------------+----------------------------------------------+------------+
| Continent     | Name                                         | Population |
+---------------+----------------------------------------------+------------+
| North America | Aruba                                        |     103000 |
| Asia          | Afghanistan                                  |   22720000 |
| Africa        | Angola                                       |   12878000 |
..
| Africa        | South Africa                                 |   40377000 |
| Africa        | Zambia                                       |    9169000 |
| Africa        | Zimbabwe                                     |   11669000 |
+---------------+----------------------------------------------+------------+
239 rows in set (0.00 sec)

おっと… GROUP BYが暗黙のソートをしなくなった件 を垣間見ることもできた。 5.7とそれ以前と出力結果を一緒にするためには、 ORDER BY Continent, Name も追加する必要がある。

ともあれ、こんなフツーの GROUP BY なクエリーに WITH ROLLUP を足してやると

mysql80> SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name WITH ROLLUP;
+---------------+----------------------------------------------+------------+
| Continent     | Name                                         | Population |
+---------------+----------------------------------------------+------------+
| Asia          | Afghanistan                                  |   22720000 |
| Asia          | Armenia                                      |    3520000 |
| Asia          | Azerbaijan                                   |    7734000 |
..
| Asia          | Yemen                                        |   18112000 |
| Asia          | NULL                                         | 3705025700 |
| Europe        | Albania                                      |    3401200 |
..
| Europe        | Yugoslavia                                   |   10640000 |
| Europe        | NULL                                         |  730074600 |
| North America | Anguilla                                     |       8000 |
..
| South America | Venezuela                                    |   24170000 |
| South America | NULL                                         |  345780000 |
| NULL          | NULL                                         | 6078749450 |
+---------------+----------------------------------------------+------------+
247 rows in set (0.00 sec)

こうなる。 Continent単位で合計したものが Name IS NULL として集約行が作られて、全てを合計した値で Continent IS NULL, Name IS NULL として集約行が作られる。

日々の覚書: information_schemaでちょこちょこ使えるTIPS とかで使うのが気持ちいい。

これ、NULLになったカラムはSQLの中から条件指定が不可能（WHEREはGROUP BYが処理される前のフィルターだし、HAVINGフィルターよりも更に後に集約行が作成されるのでダメらしい（ MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.2 GROUP BY 修飾子 ）

なので、この集約行にだけアクセスしたい場合（最初からそこでGROUP BYしろよとは思うけれどなんでなのか俺もやりたがった記憶がある）、アプリケーションの中で結果セットを受け取ってからカラムがNULLかどうかチェックして集約行判定をしなければならなかった。 たとえばこんな風に（ !(defined($_->{Continent})) && !(defined(_->{Name})) ）

my $conn= DBI->connect("dbi:mysql:world;mysql_socket=/usr/mysql/8.0.1/data/mysql.sock", "root", "");

my $sql= "SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name WITH ROLLUP";
foreach (@{$conn->selectall_arrayref($sql, {Slice => {}})})
{
  print Dumper $_ if !(defined($_->{Continent})) && !(defined($_->{Name}));
}

=pod
$VAR1 = {
          'Continent' => undef,
          'Name' => undef,
          'Population' => '6078749450'
        };
=cut

で、これをHAVINGの中で言及できるようにする関数が GROUPING らしい。

mysql80> SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name WITH ROLLUP HAVING GROUPING(Continent) AND GROUPING(Name);
+-----------+------+------------+
| Continent | Name | Population |
+-----------+------+------------+
| NULL      | NULL | 6078749450 |
+-----------+------+------------+
1 row in set (0.00 sec)

mysql80> SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name WITH ROLLUP HAVING GROUPING(Name);
+---------------+------+------------+
| Continent     | Name | Population |
+---------------+------+------------+
| Asia          | NULL | 3705025700 |
| Europe        | NULL |  730074600 |
| North America | NULL |  482993000 |
| Africa        | NULL |  784475000 |
| Oceania       | NULL |   30401150 |
| Antarctica    | NULL |          0 |
| South America | NULL |  345780000 |
| NULL          | NULL | 6078749450 |
+---------------+------+------------+
8 rows in set (0.00 sec)

最初っからそこで GROUP BY しなよ感があるけれど、GROUPING が1か0を返すからORDER BYで集約行を先に持ってこられたらいいかな？ と思った。

が

mysql80> SELECT Continent, Name, SUM(Population) AS Population FROM country GROUP BY Continent, Name WITH ROLLUP HAVING GROUPING(Name) ORDER BY GROUPING(Name);
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY

WITH ROLLUPとORDER BYが同時に使えないという制約があるので並べ替えには使えなかった。 残念。。

​