2016年9月13日火曜日

MySQL 8.0.0でついにヒストグラムがサポートされるらしい(が、自動で統計してくれるわけではない様子)

MySQL :: WL#8706: Persistent storage of Histogram data

ドキュメントが出てきてないので正直使い方とか全くわからないんだけれども。


mysql80> DESC mysql.column_stats;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| database_name | varchar(64) | NO   | PRI | NULL    |       |
| table_name    | varchar(64) | NO   | PRI | NULL    |       |
| column_name   | varchar(64) | NO   | PRI | NULL    |       |
| histogram     | json        | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

取り敢えずJSONで手で書くのか。。


と思ったら 8.0.0現在、こんなw

Note
Currently, the optimizer does not yet consult the column_stats table in the course of query execution plan construction.

MySQL :: MySQL 8.0 Reference Manual :: 9.9.6 Optimizer Statistics

MySQL 8.0.0 INVISIBLE KEYとやらはどうやって指定すればいいのか

INVISIBLEなるキーワードがインデックスに指定できるようになったので、取り敢えずADD KEYしてみたけれど…。


mysql80> INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql80> ALTER TABLE t1 ADD KEY (val) INVISIBLE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `val` (`val`) /*!50800 INVISIBLE */
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

mysql80> explain SELECT * FROM t1 WHERE val = 'two';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql80> explain SELECT * FROM t1 FORCE INDEX(val) WHERE val = 'two';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

おお、ホントだ FORCE INDEXでも使われない!!
けど、これってどうやって使わせるの…? たぶん SELECT /*+ .. */ スタイルの構文で食わせそうな気がするんだけど、まだ情報が見当たらない…(´・ω・`)

テーブルの1/3をフェッチするからコスト的に選ばれてないアレじゃないよね? と思ってフツーのINDEXも作ったけど、こっちはちゃんと選ばれる。ということはちゃんとインビジボーなのだね。

mysql80> ALTER TABLE t1 ADD KEY (val);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql80> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `val` (`val`) /*!50800 INVISIBLE */,
  KEY `val_2` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql80> explain SELECT * FROM t1 WHERE val = 'two';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | val_2         | val_2 | 131     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

で、どうやって選ばせればいいの…。ドキュメントはよ…。

MySQL 8.0.0現在で追加されているperformance_schema

MySQL 8.0.0時代のmy.cnfの探り方 で出てきたvariables_info の他にも events_errors_summary_* というテーブルが増えてる。


$ diff <(mysql80 -sse "SHOW TABLES FROM p_s") <(mysql57 -sse "SHOW TABLES FROM p_s")
3,7d2
< events_errors_summary_by_account_by_error
< events_errors_summary_by_host_by_error
< events_errors_summary_by_thread_by_error
< events_errors_summary_by_user_by_error
< events_errors_summary_global_by_error
93d87
< variables_info

中身はこんな感じ。


mysql80> SELECT * FROM events_errors_summary_global_by_error LIMIT 10;
+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
| ERROR_NUMBER | ERROR_NAME              | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |
+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
|         NULL | NULL                    | NULL      |                0 |                 0 | NULL       | NULL      |
|         1002 | ER_NO                   | HY000     |                0 |                 0 | NULL       | NULL      |
|         1003 | ER_YES                  | HY000     |                0 |                 0 | NULL       | NULL      |
|         1004 | ER_CANT_CREATE_FILE     | HY000     |                0 |                 0 | NULL       | NULL      |
|         1005 | ER_CANT_CREATE_TABLE    | HY000     |                0 |                 0 | NULL       | NULL      |
|         1006 | ER_CANT_CREATE_DB       | HY000     |                0 |                 0 | NULL       | NULL      |
|         1007 | ER_DB_CREATE_EXISTS     | HY000     |                0 |                 0 | NULL       | NULL      |
|         1008 | ER_DB_DROP_EXISTS       | HY000     |                0 |                 0 | NULL       | NULL      |
|         1010 | ER_DB_DROP_RMDIR        | HY000     |                0 |                 0 | NULL       | NULL      |
|         1012 | ER_CANT_FIND_SYSTEM_REC | HY000     |                0 |                 0 | NULL       | NULL      |
+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
10 rows in set (0.00 sec)

エラーコード単位で、何回起こったかとfirst_seen, last_seenを記録してくれるっぽい。
取り敢えず何かエラーにしてみようか。


mysql80> DROP DATABASE non_exist_database_name;
ERROR 1008 (HY000): Can't drop database 'non_exist_database_name'; database doesn't exist

mysql80> SELECT * FROM events_errors_summary_global_by_error LIMIT 10;
+--------------+-------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| ERROR_NUMBER | ERROR_NAME              | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+--------------+-------------------------+-----------+------------------+-------------------+---------------------+---------------------+
|         NULL | NULL                    | NULL      |                0 |                 0 | NULL                | NULL                |
|         1002 | ER_NO                   | HY000     |                0 |                 0 | NULL                | NULL                |
|         1003 | ER_YES                  | HY000     |                0 |                 0 | NULL                | NULL                |
|         1004 | ER_CANT_CREATE_FILE     | HY000     |                0 |                 0 | NULL                | NULL                |
|         1005 | ER_CANT_CREATE_TABLE    | HY000     |                0 |                 0 | NULL                | NULL                |
|         1006 | ER_CANT_CREATE_DB       | HY000     |                0 |                 0 | NULL                | NULL                |
|         1007 | ER_DB_CREATE_EXISTS     | HY000     |                0 |                 0 | NULL                | NULL                |
|         1008 | ER_DB_DROP_EXISTS       | HY000     |                1 |                 0 | 2016-09-09 12:28:01 | 2016-09-09 12:28:01 |
|         1010 | ER_DB_DROP_RMDIR        | HY000     |                0 |                 0 | NULL                | NULL                |
|         1012 | ER_CANT_FIND_SYSTEM_REC | HY000     |                0 |                 0 | NULL                | NULL                |
+--------------+-------------------------+-----------+------------------+-------------------+---------------------+---------------------+
10 rows in set (0.00 sec)

記録された。


mysql80> SELECT * FROM events_errors_summary_by_account_by_error WHERE SUM_ERROR_RAISED <> 0;
+------+-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| USER | HOST      | ERROR_NUMBER | ERROR_NAME                | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+------+-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| root | localhost |         1008 | ER_DB_DROP_EXISTS         | HY000     |                1 |                 0 | 2016-09-09 12:28:01 | 2016-09-09 12:28:01 |
| root | localhost |         1049 | ER_BAD_DB_ERROR           | 42000     |                1 |                 0 | 2016-09-09 12:21:11 | 2016-09-09 12:21:11 |
| root | localhost |         1329 | ER_SP_FETCH_NO_DATA       | 02000     |                2 |                 2 | 2016-09-09 12:21:27 | 2016-09-09 12:21:34 |
| root | localhost |         3554 | ER_NO_SYSTEM_TABLE_ACCESS | HY000     |              142 |                 0 | 2016-09-09 12:18:41 | 2016-09-09 12:29:11 |
+------+-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+
4 rows in set (0.01 sec)

ここまでやるなら、 events_errors_summary_by_digest_by_error テーブル(エラーになったステートメントと紐付く)もあったら嬉しかったりするんだけどな。。
レプリケーション関連のテーブル (replication_*)は今のところ動きがなさげ(つまりまだ Seconds_behind_masterとかは取れない)

MySQL 8.0.0のdatadirにあるなんか変なSDIファイル

取り敢えず mysqld --initialize したdatadirを覗いてみると、見慣れないSDIファイルがあることと見慣れた.frmファイルが **ない** ことに気付いた。


$ ll data/mysql/*.frm
ls: cannot access data/mysql/*.frm: No such file or directory

$ ll data/*.SDI
-rw-r----- 1 yoku0825 yoku0825 225 Sep  5 10:17 data/performance_sche_3.SDI
-rw-r----- 1 yoku0825 yoku0825 210 Sep  5 10:17 data/sys_4.SDI

SDIファイルは他にもいっぱいあって、 `find -name "*.SDI"` とかやるとごろっと出てくる。
Seralized Dictionary Informationの略らしい。New Data Dictionaryの一環で、.frmファイルからSDIファイルに変わったらしい。

中身はJSONで、いかにもメタデータっぽいのが詰まってる。
スキーマ用のSDIとテーブル用のSDIで(それぞれ持ってるメタデータの量が全然違うからだけど)中身は全然違う。

$ cat data/sys_4.SDI  ### Schema's SDI
{
    "sdi_version": 1,
    "dd_version": 1,
    "dd_object_type": "Schema",
    "dd_object": {
        "name": "sys",
        "default_collation_id": 33,
        "created": 0,
        "last_altered": 0
    }
}

$ cat ./data/mysql/slow_log_103.SDI ### Table's SDI
{
    "sdi_version": 1,
    "dd_version": 1,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "slow_log",
        "mysql_version_id": 80000,
        "created": 20160905101714,
        "last_altered": 20160905101714,
        "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "start_time",
                "type": 18,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": false,
                "ordinal_position": 1,
                "char_length": 26,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "datetime_precision": 6,
                "has_no_default": false,
                "default_value_null": false,
                "default_value": "AAAAAAAAAA==",
                "default_option": "CURRENT_TIMESTAMP(6)",
                "update_option": "CURRENT_TIMESTAMP(6)",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "",
                "column_key": 1,
                "column_type_utf8": "timestamp(6)",
                "elements": [],
                "collation_id": 8
            },
            .. snip ..
        ],
        "schema_ref": "mysql",
        "hidden": false,
        "se_private_id": 18446744073709551615,
        "engine": "CSV",
        "comment": "Slow log",
        "se_private_data": "",
        "row_format": 2,
        "partition_type": 0,
        "partition_expression": "",
        "default_partitioning": 0,
        "subpartition_type": 0,
        "subpartition_expression": "",
        "default_subpartitioning": 0,
        "indexes": [],
        "foreign_keys": [],
        "partitions": [],
        "collation_id": 33
    }
}

ちなみにこれ、何故かInnoDBのテーブルには存在しない。


mysql80> CREATE DATABASE d1;
Query OK, 1 row affected (0.01 sec)

mysql80> CREATE TABLE d1.t1 (num serial, val varchar(32)) Engine= InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql80> CREATE TABLE d1.t2 (num serial, val varchar(32)) Engine= MyISAM;
Query OK, 0 rows affected (0.00 sec)


$ ll data/d1*
-rw-r----- 1 yoku0825 yoku0825  209 Sep  5 11:05 data/d1_6.SDI

data/d1:
total 136
-rw-r----- 1 yoku0825 yoku0825 131072 Sep  5 11:05 t1.ibd
-rw-r----- 1 yoku0825 yoku0825   3787 Sep  5 11:05 t2_325.SDI
-rw-r----- 1 yoku0825 yoku0825      0 Sep  5 11:05 t2.MYD
-rw-r----- 1 yoku0825 yoku0825   1024 Sep  5 11:05 t2.MYI

作ったスキーマに対する d1_*.SDI (数字部分はスキーマを通した連番?)と、MyISAMのテーブルに対する t2_*.SDI (こっちはテーブル単位で連番?)はあるけど、InnoDBテーブルのt1.ibdに対するSDIファイルはない。

停止してinnochecksumを叩いてみると、


$ bin/innochecksum -S data/d1/t1.ibd

File::data/d1/t1.ibd
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
       1        Index page
       2        SDI Index page
       0        Undo log page
       1        Inode page
       0        Insert buffer free list page
       2        Freshly allocated page
       1        Insert buffer bitmap
       0        System page
       0        Transaction system page
       1        File Space Header
       0        Extent descriptor page
       0        BLOB page
       0        Compressed BLOB page
       0        Subsequent Compressed BLOB page
       0        SDI BLOB page
       0        Compressed SDI BLOB page
       0        Other type of page
===============================================
Additional information:
Undo page type: 0 insert, 0 update, 0 other
Undo page state: 0 active, 0 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other

$ bin/innochecksum -D >(cat -) data/d1/t1.ibd


Filename::data/d1/t1.ibd
==============================================================================
        PAGE_NO         |               PAGE_TYPE                       |       EXTRA INFO
==============================================================================
#::       0             |               File Space Header               |       -
#::       1             |               Insert Buffer Bitmap            |       -
#::       2             |               Inode page                      |       -
#::       3             |               SDI Index page                  |       index id=18446744073709551615 (copy_num=0), page level=0, No. of records=0, garbage=0, -
#::       4             |               SDI Index page                  |       index id=18446744073709551614 (copy_num=1), page level=0, No. of records=0, garbage=0, -
#::       5             |               Index page                      |       index id=143, page level=0, No. of records=0, garbage=0, -
#::       6             |               Freshly allocated page          |       -
#::       7             |               Freshly allocated page          |       -

"SDI Index page" なる新しいページタイプが2ページ記録されているので、ここに入っているぽい。InnoDBだけは、ibdファイル(Not ibdata1)にSDIを記録することで、SDIファイルを必要とせず、しかもクラッシュセーフにできる…ってことなのかな。
ちなみにちょっと期待していた DROP TABLE のロールバックは


mysql80> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80> DROP TABLE t1;
Query OK, 0 rows affected (0.02 sec)

mysql80> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t2           |
+--------------+
1 row in set (0.01 sec)

mysql80> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql80> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t2           |
+--------------+
1 row in set (0.00 sec)

まだ、なのか、「そんなことする必要ないでしょ」なのか、できなかった。
DDLのトランザクション化というよりは、DDLのクラッシュセーフ化って感じな気がする。

MySQL 8.0.0現在の文字コードについて

Planning the defaults for MySQL 5.8 | MySQL Server Blog の時点で

In addition to utf8mb4, we are also considering switching the default collation to be utf8mb4_unicode_520_ci.

と地雷宣言が為されていた文字コード問題。
(utf8mb4_unicode_520_ci は🍣と🍺を区別するけど、ハハとパパを区別してくれないヤーツ)

|                    | utf8mb4_bin | utf8mb4_general_ci | utf8mb4_unicode_ci | utf8mb4_unicode_520_ci|
|--------------------|-------------|--------------------|--------------------|-----------------------|
| Hiragana-Katakana  | cs (unkind) | cs (unkind)        | ci (good)          | ci(good)              |
| Youon              | cs (good)   | cs (good)          | ci (critical)      | ci(critical)          |
| Dakuten-Handakuten | cs (good)   | cs (good)          | ci (critical)      | ci(critical)          |
| Wide-Narrow        | cs (unkind) | cs (unkind)        | ci (good)          | ci(good)              |
| Sushi-Beer         | cs          | ci                 | ci                 | cs                    |

MySQL Bugs: #79977: utf8mb4_unicode_520_ci don't make sense for Japanese FTS


かみぽさんが「せっかくActiveRecordで直したのに今度はMySQLでデフォルトになるん…」みたいなことを言っていたやつ。

取り敢えず手元の8.0.0現在では、

$ bin/mysqld --no-defaults --help --verbose | egrep '(character-set|collation)-server'
mysqld: Can't change dir to '/usr/local/mysql/data/' (Errcode: 2 - No such file or directory)
2016-09-05T04:05:23.602489Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-09-05T04:05:23.602584Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2016-09-05T04:05:23.602946Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
  -C, --character-set-server=name
  --collation-server=name
character-set-server                                         latin1
collation-server                                             latin1_swedish_ci

文字コード/照合順序は5.7と同じくlatin1/latin1_swedish_ci。
その一方で、


mysql80> SHOW COLLATION LIKE 'utf8mb4%';
+----------------------------+---------+-----+---------+----------+---------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen |
+----------------------------+---------+-----+---------+----------+---------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 |         | Yes      |       8 |
| utf8mb4_bin                | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_croatian_ci        | utf8mb4 | 245 |         | Yes      |       8 |
| utf8mb4_cs_0900_ai_ci      | utf8mb4 | 266 |         | Yes      |       8 |
| utf8mb4_czech_ci           | utf8mb4 | 234 |         | Yes      |       8 |
| utf8mb4_danish_ci          | utf8mb4 | 235 |         | Yes      |       8 |
| utf8mb4_da_0900_ai_ci      | utf8mb4 | 267 |         | Yes      |       8 |
| utf8mb4_de_pb_0900_ai_ci   | utf8mb4 | 256 |         | Yes      |       8 |
| utf8mb4_eo_0900_ai_ci      | utf8mb4 | 273 |         | Yes      |       8 |
| utf8mb4_esperanto_ci       | utf8mb4 | 241 |         | Yes      |       8 |
| utf8mb4_estonian_ci        | utf8mb4 | 230 |         | Yes      |       8 |
| utf8mb4_es_0900_ai_ci      | utf8mb4 | 263 |         | Yes      |       8 |
| utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 |         | Yes      |       8 |
| utf8mb4_et_0900_ai_ci      | utf8mb4 | 262 |         | Yes      |       8 |
| utf8mb4_general_ci         | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_german2_ci         | utf8mb4 | 244 |         | Yes      |       8 |
| utf8mb4_hr_0900_ai_ci      | utf8mb4 | 275 |         | Yes      |       8 |
| utf8mb4_hungarian_ci       | utf8mb4 | 242 |         | Yes      |       8 |
| utf8mb4_hu_0900_ai_ci      | utf8mb4 | 274 |         | Yes      |       8 |
| utf8mb4_icelandic_ci       | utf8mb4 | 225 |         | Yes      |       8 |
| utf8mb4_is_0900_ai_ci      | utf8mb4 | 257 |         | Yes      |       8 |
| utf8mb4_latvian_ci         | utf8mb4 | 226 |         | Yes      |       8 |
| utf8mb4_la_0900_ai_ci      | utf8mb4 | 271 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci      | utf8mb4 | 236 |         | Yes      |       8 |
| utf8mb4_lt_0900_ai_ci      | utf8mb4 | 268 |         | Yes      |       8 |
| utf8mb4_lv_0900_ai_ci      | utf8mb4 | 258 |         | Yes      |       8 |
| utf8mb4_persian_ci         | utf8mb4 | 240 |         | Yes      |       8 |
| utf8mb4_pl_0900_ai_ci      | utf8mb4 | 261 |         | Yes      |       8 |
| utf8mb4_polish_ci          | utf8mb4 | 229 |         | Yes      |       8 |
| utf8mb4_romanian_ci        | utf8mb4 | 227 |         | Yes      |       8 |
| utf8mb4_roman_ci           | utf8mb4 | 239 |         | Yes      |       8 |
| utf8mb4_ro_0900_ai_ci      | utf8mb4 | 259 |         | Yes      |       8 |
| utf8mb4_sinhala_ci         | utf8mb4 | 243 |         | Yes      |       8 |
| utf8mb4_sk_0900_ai_ci      | utf8mb4 | 269 |         | Yes      |       8 |
| utf8mb4_slovak_ci          | utf8mb4 | 237 |         | Yes      |       8 |
| utf8mb4_slovenian_ci       | utf8mb4 | 228 |         | Yes      |       8 |
| utf8mb4_sl_0900_ai_ci      | utf8mb4 | 260 |         | Yes      |       8 |
| utf8mb4_spanish2_ci        | utf8mb4 | 238 |         | Yes      |       8 |
| utf8mb4_spanish_ci         | utf8mb4 | 231 |         | Yes      |       8 |
| utf8mb4_sv_0900_ai_ci      | utf8mb4 | 264 |         | Yes      |       8 |
| utf8mb4_swedish_ci         | utf8mb4 | 232 |         | Yes      |       8 |
| utf8mb4_tr_0900_ai_ci      | utf8mb4 | 265 |         | Yes      |       8 |
| utf8mb4_turkish_ci         | utf8mb4 | 233 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci     | utf8mb4 | 246 |         | Yes      |       8 |
| utf8mb4_unicode_ci         | utf8mb4 | 224 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci      | utf8mb4 | 247 |         | Yes      |       8 |
| utf8mb4_vi_0900_ai_ci      | utf8mb4 | 277 |         | Yes      |       8 |
+----------------------------+---------+-----+---------+----------+---------+
47 rows in set (0.00 sec)

utf8mb4の照合順序は結構増えてる。0900_ai_ciはUnicode 9.0.0ベースのAccentInsensitiveだろうか。どうも各言語ごとに0900_ai_ciが生えるような形をしてる。

MySQL :: WL#9108: Add language specific case insensitive collations of utf8mb4


「ai_ciが追加されるからunicode_ciはAccentSensitiveでいいよね」って流れになってもらわないと、このままutf8mb4_unicode_ciがデフォルトになる ( Planning the defaults for MySQL 5.8 | MySQL Server Blog ) のはつらい。WL#9108 見ても、japanese_ciを作る予定はなさそうだし(なさそうだしそもそも、罠いのが **暗黙のデフォルトになる** のが嫌なのだ俺は。。)

mysql80> SELECT 'ハハ' = 'パパ' COLLATE utf8mb4_unicode_ci;
+------------------------------------------------+
| 'ハハ' = 'パパ' COLLATE utf8mb4_unicode_ci     |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql80> SELECT 'ハハ' = 'パパ' COLLATE utf8mb4_general_ci;
+------------------------------------------------+
| 'ハハ' = 'パパ' COLLATE utf8mb4_general_ci     |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql80> SELECT 'ハハ' = 'パパ' COLLATE utf8mb4_unicode_520_ci;
+----------------------------------------------------+
| 'ハハ' = 'パパ' COLLATE utf8mb4_unicode_520_ci     |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.00 sec)



今後どうなるのか(特に 暗黙のデフォルトが 何になるのか)注目しておいた方がよさげ。

MySQL 8.0.0のmysqlコマンドラインクライアントでは `--ssl` オプションの名前が変わった

実は(?) GA後の MySQL 5.7.11でdeprecatedになっていたヤーツ。

The client-side --ssl option is deprecated as of MySQL 5.7.11 and is removed in MySQL 8.0. For client programs, it is preferable to use --ssl-mode instead:
Use --ssl-mode=REQUIRED instead of --ssl=1 or --enable-ssl.
Use --ssl-mode=DISABLED instead of --ssl=0, --skip-ssl, or --disable-ssl.
No explicit --ssl-mode option is equivalent to no explicit --ssl option.
The server-side --ssl option is not deprecated.

MySQL :: MySQL 5.7 Reference Manual :: 7.4.5 Command Options for Secure Connections

結構盛大に変わる。。


$ mysql80 --ssl
mysql: [ERROR] unknown option '--ssl'

$ mysql80 --help | grep ssl
  --ssl-mode=name     SSL connection mode.
  --ssl-ca=name       CA file in PEM format.
  --ssl-capath=name   CA directory.
  --ssl-cert=name     X509 cert in PEM format.
  --ssl-cipher=name   SSL cipher to use.
  --ssl-key=name      X509 key in PEM format.
  --ssl-crl=name      Certificate revocation list.
  --ssl-crlpath=name  Certificate revocation list path.
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-crl                           (No default value)
ssl-crlpath                       (No default value)

`--help` が不完全な気がする(デフォルトを出す方に載ってない)
あと、取り得る値とかも出してくれないのでちょっと不親切。。
引用の通り、今までの `--ssl` と同じように使うには `--ssl-mode=REQUIRED` で良いみたい。

ついでに気づいちゃったんだけど、


$ mysql80 -h127.0.0.1 -P64057 --ssl-mode=required -sse "SHOW SESSION STATUS LIKE 'Ssl_cipher'"
Ssl_cipher      DHE-RSA-AES256-SHA

$ mysql80 -h127.0.0.1 -P64057 --ssl-mode=req -sse "SHOW SESSION STATUS LIKE 'Ssl_cipher'"
Ssl_cipher      DHE-RSA-AES256-SHA

$ mysql80 -h127.0.0.1 -P64057 --ssl-mode=r -sse "SHOW SESSION STATUS LIKE 'Ssl_cipher'"
Ssl_cipher      DHE-RSA-AES256-SHA

$ mysql80 -h127.0.0.1 -P64057 --ssl-mode=v -sse "SHOW SESSION STATUS LIKE 'Ssl_cipher'"         ### vだけだとverify_caとverify_identityの両方があり得るのでエラー
Unknown option to ssl-mode: v
Alternatives are: 'DISABLED','PREFERRED','REQUIRED','VERIFY_CA','VERIFY_IDENTITY'

$ mysql80 -h127.0.0.1 -P64057 --ssl-mode=verify_i -sse "SHOW SESSION STATUS LIKE 'Ssl_cipher'"  ### verify_iでverify_identity相当
ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure

$ mysql80 -h127.0.0.1 -P64057 --ssl-mode=verify_c -sse "SHOW SESSION STATUS LIKE 'Ssl_cipher'"  ### verify_cでverify_ca相当
Ssl_cipher      DHE-RSA-AES256-SHA

あ、あー、これ前方一致で補完するやつだ!
5.7.14の時点でそうだった。

MySQL 8.0.0で追加されたROLEの仕組み

正直なんの情報もなくてすごく困ったんだけど、何故か自分のブログ記事に助けられた。

日々の覚書: MariaDB 10.0.5で実装されたROLEを試す


ほぼこの時と同じ。MariaDB 10.0の時はロールを割り当てるユーザーが存在しなくても割り当てられたけど、MySQL 8.0は先にCREATE USERしておかないと割り当てられなかったことくらい。

mysql80> CREATE ROLE sys_select;
Query OK, 0 rows affected (0.00 sec)

mysql80> GRANT SELECT ON mysql.* TO sys_select;
Query OK, 0 rows affected (0.00 sec)

mysql80> SELECT * FROM mysql.user WHERE user = 'sys_select'\G
*************************** 1. row ***************************
                  Host: %
                  User: sys_select
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: Y
 password_last_changed: 2016-09-05 11:55:04
     password_lifetime: NULL
        account_locked: Y
      Create_role_priv: N
        Drop_role_priv: N
1 row in set (0.00 sec)

MariaDB 10.0では is_role なるカラムでロールかどうかを識別していたけど、MySQL 8.0にはそれっぽいカラムはなさげ。account_lockedで代用している気配。


mysql80> GRANT sys_select TO yoku0825;
Query OK, 0 rows affected (0.00 sec)

mysql80> SELECT * FROM default_roles;
Empty set (0.00 sec)

mysql80> SELECT * FROM role_edges;
+-----------+------------+---------+----------+-------------------+
| FROM_HOST | FROM_USER  | TO_HOST | TO_USER  | WITH_ADMIN_OPTION |
+-----------+------------+---------+----------+-------------------+
| %         | sys_select | %       | yoku0825 | N                 |
+-----------+------------+---------+----------+-------------------+
1 row in set (0.00 sec)

ロールのマッピングはmysql.role_edgesテーブルに記録される。


$ mysql80 -uyoku0825

mysql80> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| yoku0825@%     |
+----------------+
1 row in set (0.00 sec)

mysql80> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

mysql80> SHOW GRANTS;
+------------------------------------------+
| Grants for yoku0825@%                    |
+------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%`     |
| GRANT `sys_select`@`%` TO `yoku0825`@`%` |
+------------------------------------------+
2 rows in set (0.00 sec)

mysql80> SELECT user, host FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'yoku0825'@'localhost' for table 'user'

mysql80> SET ROLE sys_select;
Query OK, 0 rows affected (0.00 sec)

mysql80> SELECT current_role();
+------------------+
| current_role()   |
+------------------+
| `sys_select`@`%` |
+------------------+
1 row in set (0.00 sec)

mysql80> SELECT user, host FROM mysql.user;
+------------+-----------+
| user       | host      |
+------------+-----------+
| sys_select | %         |
| yoku0825   | %         |
| mysql.sys  | localhost |
| root       | localhost |
+------------+-----------+
4 rows in set (0.00 sec)

まるっきり MariaDBのとき と同じで助かるというか捗るというか。

MariaDBは10.1からとされていた(10.1使ってないから本当にされたのか知らない。。)デフォルトロールも実装されている。
勘に任せてALTER USERしてみた。


mysql80> ALTER USER yoku0825 DEFAULT ROLE sys_select;
Query OK, 0 rows affected (0.01 sec)

mysql80> SELECT * FROM default_roles;
+------+----------+-------------------+-------------------+
| HOST | USER     | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+----------+-------------------+-------------------+
| %    | yoku0825 | %                 | sys_select        |
+------+----------+-------------------+-------------------+
1 row in set (0.00 sec)

$ mysql80 -uyoku0825
mysql80> SELECT current_role();
+------------------+
| current_role()   |
+------------------+
| `sys_select`@`%` |
+------------------+
1 row in set (0.00 sec)

なるほど大正解。しかしこれ、ユーザーアカウントとROLEを区別してないってことは、もしかして逆もできるのかしら。


mysql80> GRANT yoku0825 TO sys_select;
Query OK, 0 rows affected (0.01 sec)

mysql80> SELECT * FROM role_edges;
+-----------+------------+---------+------------+-------------------+
| FROM_HOST | FROM_USER  | TO_HOST | TO_USER    | WITH_ADMIN_OPTION |
+-----------+------------+---------+------------+-------------------+
| %         | sys_select | %       | yoku0825   | N                 |
| %         | yoku0825   | %       | sys_select | N                 |
+-----------+------------+---------+------------+-------------------+
2 rows in set (0.00 sec)

( д ) ゚ ゚ やっぱりできた
アカウント同士でも試してみたけど


mysql80> GRANT root@localhost TO yoku0825;
Query OK, 0 rows affected (0.00 sec)

$ mysql80 -uyoku0825
mysql80> SET ROLE root@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql80> SET GLOBAL innodb_buffer_pool_dump_now= 1;
Query OK, 0 rows affected (0.00 sec)

おおおおおお…ユーザーとロールは区別した方がいいと思うんだけど、設定次第でこうも出来ると…。
ロール機能自体はウェルカムですねべんり。