日々の覚書

2015/09/15

MySQL Fabricつらい（リモートのmysqlfabricサーバーにmysqlfabricコマンドで接続するのにやっと成功）

MySQL Fabric、そのまま全てを（Fabric対応コネクターに全てを任せて）使うのはつらすぎるとして、せめてお手軽にMySQLを監視したりSwitchoverさせたりする器として使えないかな、というのを前からずっと考えていて（mysqlrpladmin 使えって？知らない子ですね。。）

MySQL Fabricつらい Advent Calendar 2014 - Qiita

去年の12月に、「mysqlfabricをそのままクライアントとして使おうとするとlocalhostのmysqlfabricサーバーに接続しようとして転ける、リモートアドレス向けに接続しようとするとfabric.cfgの書き換えが必要でつらい」とか思ってばぐれぽしたのがこちら。
MySQL Bugs: #75308: mysqlfabric's --param option's syntax is not documented

実は4月くらいに返事が返ってきていたので、書いておこうかなと思います。

気が付いたらドキュメントにも上がってた。
MySQL :: MySQL Utilities :: 8.2.3.4 The Configuration Parameter (--param)

想定しているのは↓のような状態。
* 172.17.0.45なサーバーでバッキングストアのmysqldとmysqlfabricが起動している
* 172.17.0.46, 172.17.0.47なサーバーではmysqlfabricに管理されているmysqldが動いている。mysqlfabricコマンド(MySQL Utilities)はインストールされている。
* 172.17.0.47から172.17.0.45のmysqlfabricサーバーを叩いて情報を見たい

172.17.0.45$ mysqlfabric group lookup_servers myfabric1
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid          address    status      mode weight
------------------------------------ ---------------- --------- --------- ------
03a5981a-5ac9-11e5-9d85-0242ac11002f 172.17.0.47:3306 SECONDARY READ_ONLY    1.0
740fd00e-5ac8-11e5-9d81-0242ac11002e 172.17.0.46:3306 SECONDARY READ_ONLY    1.0

172.17.0.45からフツーに`mysqlfabric`をクライアントとして実行すると、この通り結果が返ってくる。

172.17.0.47$ mysqlfabric group lookup_servers myfabric1
Password for admin:
<urlopen error [Errno 111] Connection refused>

172.17.0.47$ strace -e connect mysqlfabric group lookup_servers myfabric1
--- SIGCHLD (Child exited) @ 0 (0) ---
--- SIGCHLD (Child exited) @ 0 (0) ---
Password for admin:
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_INET, sin_port=htons(32274), sin_addr=inet_addr("127.0.0.1")}, 16) = 0
connect(3, {sa_family=AF_INET6, sin6_port=htons(32274), inet_pton(AF_INET6, "::1", &sin6_addr), sin6_flowinfo=0, sin6_scope_id=0}, 28) = 0
connect(3, {sa_family=AF_INET6, sin6_port=htons(32274), inet_pton(AF_INET6, "::1", &sin6_addr), sin6_flowinfo=0, sin6_scope_id=0}, 28) = -1 ECONNREFUSED (Connection refused)
connect(3, {sa_family=AF_INET, sin_port=htons(32274), sin_addr=inet_addr("127.0.0.1")}, 16) = -1 ECONNREFUSED (Connection refused)
<urlopen error [Errno 111] Connection refused>

172.17.0.47で同じことをやろうとすると、当然ながらlocalhost向けに接続しに行こうとして転ける。
ここまではいい。ここまではいいんだけど、

（少なくとも当時）どうやってリモートのmysqlfabricに接続するのかが全くドキュメントに書いてない。。:(;ﾞﾟ'ωﾟ'):

取り敢えず正解は、[protocol.xmlrpc]セクションのaddressを書き換えること。ただしこのパラメーターはmysqlfabricをサーバーとして起動した時はbind-address的な意味を持つので、サーバーとしても動かしてるホストでこれをホイホイ書き換えるのは結構危険。
日々の覚書: MySQL Fabricつらい(クライアントとしてのmysqlfabricとサーバーとしてのmysqlfabricのオプションの扱いが一緒)

で、mysqlfabricには--paramがあるからそれでfabric.cfgいじらなくてもパラメーター上書きできるんじゃね？ => --paramの説明が全くドキュメントになくて記法が全然わからない

までが前回のあらすじ。

で、--paramの書き方は"--param=セクション.パラメータ=値"だそうだ。fabric.cfgに手を入れてない172.17.0.46からやってみる。

172.17.0.46$ mysqlfabric --param=protocol.xmlrpc.address=172.17.0.45:32274 group lookup_servers myfabric1
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid          address    status      mode weight
------------------------------------ ---------------- --------- --------- ------
03a5981a-5ac9-11e5-9d85-0242ac11002f 172.17.0.47:3306 SECONDARY READ_ONLY    1.0
740fd00e-5ac8-11e5-9d81-0242ac11002e 172.17.0.46:3306 SECONDARY READ_ONLY    1.0

( ﾟдﾟ) ﾎﾟｶｰﾝ。。
今までの苦労が何だったんだくらいの勢いで簡単。

172.17.0.46$ mysqlfabric --param=protocol.xmlrpc.address=172.17.0.45:32274 --param=protocol.xmlrpc.password=xxxx group lookup_servers myfabric1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid          address    status      mode weight
------------------------------------ ---------------- --------- --------- ------
03a5981a-5ac9-11e5-9d85-0242ac11002f 172.17.0.47:3306 SECONDARY READ_ONLY    1.0
740fd00e-5ac8-11e5-9d81-0242ac11002e 172.17.0.46:3306 SECONDARY READ_ONLY    1.0

passwordを渡してやるとプロンプトも出なくなる。

172.17.0.46$ mysqlfabric --param=protocol.xmlrpc.address=172.17.0.45:32274 --param=protocol.xmlrpc.password=xxxx group promote myfabric1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
1d048c45-bd68-4e79-b77d-0c9bb22a7431        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.44228e+09 Triggered by .
    4       2   1.44228e+09                      Executing action (_define_ha_operation).
    5       2   1.44228e+09                       Executed action (_define_ha_operation).
    3       2   1.44228e+09 Triggered by .
    4       2   1.44228e+09                      Executing action (_find_candidate_fail).
    5       2   1.44228e+09                       Executed action (_find_candidate_fail).
    3       2   1.44228e+09 Triggered by .
    4       2   1.44228e+09                     Executing action (_check_candidate_fail).
    5       2   1.44228e+09                      Executed action (_check_candidate_fail).
    3       2   1.44228e+09 Triggered by .
    4       2   1.44228e+09                          Executing action (_wait_slave_fail).
    5       2   1.44228e+09                           Executed action (_wait_slave_fail).
    3       2   1.44228e+09 Triggered by .
    4       2   1.44228e+09                      Executing action (_change_to_candidate).
    5       2   1.44228e+09                       Executed action (_change_to_candidate).


172.17.0.46$ mysqlfabric --param=protocol.xmlrpc.address=172.17.0.45:32274 --param=protocol.xmlrpc.password=xxxx group lookup_servers myfabric1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid          address    status       mode weight
------------------------------------ ---------------- --------- ---------- ------
03a5981a-5ac9-11e5-9d85-0242ac11002f 172.17.0.47:3306   PRIMARY READ_WRITE    1.0
740fd00e-5ac8-11e5-9d81-0242ac11002e 172.17.0.46:3306 SECONDARY  READ_ONLY    1.0

この通り、promoteもできる。
XML-RPCでつつけばいいんだけど、それだと何故か認証を無効にしないと通せなかったので、これを食ってシェル芸でiptablesで向き先変えるのとかやってみるかな。。

日々の覚書: MySQL Fabricつらい(XML-RPCでつついてみる編)

2015/09/08

MySQL 5.7.9のinnodb_default_row_formatがまた何か企んでいるようです

MySQL 5.7.9では innodb_default_row_format というサーバー変数が追加される（らしい。5.7.9はリリース前なので試せない）
オンライン変更可能なグローバル変数なので、`SET GLOBAL innodb_default_row_format= ..'で変更も可能。暗黙のデフォルトは"Dynamic"。

名前と値から察せられる通り、kamipoさんの悲願をかなえる類のもの…なんだけれども、

MySQL(InnoDB) で "Index column size too large. The maximum column size is 767 bytes." いわれるときの対策 - かみぽわーる

これ、

The innodb_file_format configuration option is ignored if a table is created or altered to use ROW_FORMAT=DYNAMIC. For example, innodb_file_format=Antelope is ignored if you create a table with a DYNAMIC row format. The Barracuda file format is used instead.

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_default_row_format

CREATE TABLEの時は良い。kamipo時代の到来という感じで、基本的にはメリットになる（や、ページが強制的に分割されるから、innodb_fill_factor とかいじってる人にはそうじゃないだろうけど）

ただしALTER TABLE、テメーはダメだ。勝手にROW_FORMAT= Dynamicとか押し込むな。頼む。

Change ROW_FORMAT property
Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.

MySQL :: MySQL 5.7 Reference Manual :: 14.10.1 Overview of Online DDL

オンラインでALTER TABLEできるけど、テーブルの再構築が走るので重くなる。
ROW_FORMAT= Compactなテーブルは、ALTER TABLEする時にROW_FORMAT= Compactを明示的に指定しないといけない。

何故ALTER TABLEにも適用するようにしたんだこのオプション。。

ばぐれぽ読む限りは、割と希望が見える。

I have analyzed your bug and the reasoning for the deprecation of the variable and I do agree with your findings.

I am now handing over your request to developers for the decision-making.

Hopefully, we shall hear from them soon.

MySQL Bugs: #78347: innodb_default_row_format: Undesireable new behaviour

さあみんなであふぇくつみー。

【2015/09/08 13:53】

innodb_default_row_formatについて1月ぐらいに予言してた https://t.co/bzPGsH9F52
— Ryuta Kamizono (@kamipo) 2015, 9月 8

innodb_default_row_formatの取りうる値は"Redundant"(ふるーいやつ), "Compact"(今のデフォルト), "Dynamic"(新しいデフォルト)でCompressedはありません。
— yoku0825 (@yoku0825) 2015, 9月 8

しかもこれ、CompactをデフォルトにしたところでDynamicなテーブルがCompactにされてしまうリスクがあるな。
— yoku0825 (@yoku0825) 2015, 9月 8

2015/09/01

論理削除Casual Talks #ronsakucasual でMySQLで論理削除する話をしてきた

論理削除 Casual Talks #1 : ATND に行ってきました。

アプリケーション方面では色々あるし、DBAから見ても良いことはないはず…と思ってましたが、 *ちゃんとMySQLの都合に沿ってやれば* 意外と忌避する理由もないことにふと気付きました。途中で3回くらいテーマ変更して最終的にこの形に落ち着いたカタチです。はふん。

ごめんなさい、当日流していたスライドに致命的な誤り（5.5以降ではなく、5.5以降 *ではない*）がありました。。まとめてくれた方ごめんなさい…＞＜

MySQLで論理削除と正しく付き合う方法 from yoku0825

DBAっぽく、という背景があったので、「論理削除？それUPDATEじゃん」というのが割と前提にあります。「DELETEじゃなくてUPDATEなんだから、削除とか言わずにスーパー非表示フラグでいいじゃん、システム的に *ちゃんとMySQLの都合に沿ってやれば* はそこまで変わらないし」というのが個人の見解です。

しゃらっと *ちゃんとMySQLの都合に沿ってやれば* という感じで流してますが、要はつまり

status <> :絶版とかやられるとMySQLは死ぬ #ronsakucasual
— Ryuta Kamizono (@kamipo) 2015, 8月 31

deleted_at IS NULL とかやられるとMySQLは死ぬ #ronsakucasual
— Ryuta Kamizono (@kamipo) 2015, 8月 31

どう設計してくれてもいいがMySQLの命だけは守りたい #ronsakucasual
— Ryuta Kamizono (@kamipo) 2015, 8月 31

こういう話で、bool(MySQLだとboolがないのでENUMかTINYINTかな）だと必ず"="演算子の等価比較に持ち込める（そして持ち込めないクエリーはユーザートラフィックが吐いてはいけない）ので好きとか、カーディナリティーがどうこうというより必ず全てのセカンダリーインデックスの先頭につけろでないとインデックスだけで完結できないぞとかそんな話です。

( ´-｀).oO(や、別に先頭じゃなくてもいいんですが、万人が正しいセカンダリーインデックスを考えてくれるかというとそうではないわけで、打ち漏らされるくらいなら全部先頭につけろって感じです。自信のある人は正しいところにつけてください。。

@t_wadaさんと色々お話できて、「こんなすごい人でも闇に対しては少しずつ立ち向かうしかないのだなぁ」とか「あー、SQLアンチパターンの本持ってくるの忘れた…（サインほしかった）」とか「エンタープライズ闇 Casual」とか楽しかったです。

もし次があるなら、Oracle(DBの方)の重課金してる人たちがスーパー非表示についてどう思ってるのかとか聞きたいですね。「スーパー非表示？え、Flashbackじゃだめなの？」みたいな。

@kenchanくんさんお疲れ様でした！

ちなみにスライド冒頭のInnoDBさんの「よくやるよくやる」はチェンジバッファのことですのでご安心ください（？）
論理削除（ライフサイクルを終えるまでスーパー非表示）と非同期削除（一時的にスーパー非表示＆非同期パージ）の間には少し溝があるような気がしました。

2015/08/22

#yapcasia でMySQL 5.7の罠についてLTしてきました

YAPC::Asia Tokyo 2015 お疲れ様でした！

2日目のライトニングトークでしゃべらせていただきました。

MySQL 5.7の罠があなたを狙っている from yoku0825

ネタ的には発掘するたび書き溜めてきたブログ記事から ~~笑いが取れそうなものを~~ 大事そうなもののみをピックアップして紹介した感じです。

知らないと致命傷、でも知ってれば予防できる（はず）
MySQL 5.7で不幸になる人が1人でも少なくなってくれることを願っています。

さて、今年のYAPC::Asiaはメイントラックもトークを応募していたのですが見事に落選したので、1日目は完全にリラックスして過ごしました。LTの採否、当日になるまでわかんないのか大変だなーとか、他人事だったんですが、

1日目のLT見るじゃないですか。

面白いじゃないですか。

俺もしゃべりたくなるじゃないですか。

なったんですよ！！１

が、翌朝になってLTの応募ページをたどってみると

"Currently talk submissions are disabled" Σ(ﾟдﾟlll)
— yoku0825 (@yoku0825) 2015, 8月 22

昨日のうちにやっておくべきだった。。
— yoku0825 (@yoku0825) 2015, 8月 22

しかしその後復活していたので

駆け込みで応募！！ / “MySQL 5.7の罠があなたを狙っている - YAPC::Asia Tokyo 2015” http://t.co/HEpibPCozW
— yoku0825 (@yoku0825) 2015, 8月 22

申し込み！

本日のLTです！ご確認ください！倍率3倍以上っておかしい！ http://t.co/hMllgT89Ew
— yapcasia (@yapcasia) 2015, 8月 22

その1時間後にはもう締め切られていた！（あるいは、情けで応募させてくれたのかも。。）
とてもありがたいことに採択してもらえましたが、こういう悪ノリが出来るのもYAPCだからなのかなって思います。
（わたしはChiba.pm出身なので、たぶん結構ノリが近くて）

YAPCで「1年ぶりー」って挨拶した人もいっぱいいて、なんかこう同窓会っぽくて楽しいね、なんて話をしてたりもしました。

ありがとうYAPC::Asia 2015！！

【2015/09/07 10:40】
結構前ですが、sakaikさんからツッコミをもらっていたのを思い出しました。。

MySQL を好きな人が、MySQLのちょっと変なところを、少しばかり強調しておもしろおかしく伝えている面はあるので、その変な部分だけが一人歩きして多くの人に理解されてしまうことを危惧はしています

10回目の最後のYAPCに2回目の参加をしてきた - sakaikの日々雑感～(T)編

うぐぅ。。気を付けます。。

2015/08/12

An idea for using MySQL 5.7's generated column like CHECK constraint

This is translation for my early post in Japanese.

As of MySQL 5.7.6, generated column was introduced as 5.7's new feature.
I found that generated column could behave like CHECK constraint.
Let's start :)

mysql57> CREATE TABLE t1 (num int primary key, val varchar(32)) Engine= InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (1, '2015-08-06');
Query OK, 1 row affected (0.01 sec)

mysql57> SELECT * FROM t1;
+-----+------------+
| num | val        |
+-----+------------+
|   1 | 2015-08-06 |
+-----+------------+
1 row in set (0.00 sec)

First, there's the bad designed table which has DATE-string in its VARCHAR column.

mysql57> ALTER TABLE t1 ADD check_val datetime AS (CAST(val AS datetime));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Second, add VIRTUAL generated column `check_val` which calculates DATETIME from `val`'s DATE-string.

mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-31');
ERROR 1292 (22007): Incorrect datetime value: '2015-09-31'

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
+-----+------------+---------------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-30');
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
+-----+------------+---------------------+
2 rows in set (0.00 sec)

After that, you can see INSERT statement with incorrect DATE-string fails because of `check_val`'s CAST function raises warning and strict sql_mode can't allow that warning.

mysql57> SET sql_mode= '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql57> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (3, '2015-11-31');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql57> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2015-11-31' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
|   3 | 2015-11-31 | NULL                |
+-----+------------+---------------------+
3 rows in set, 1 warning (0.00 sec)

This way depends on sql_mode is strict or not.
Then I tried another way using IF function, which returns NULL when check condition is false, with NOT NULL constraint.

mysql57> CREATE TABLE t2 (num int primary key, val varchar(32), check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) NOT NULL) Engine= InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (1, 'Tokyo');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (2, 'Osaka');
Query OK, 1 row affected (0.01 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (3, 'Nara');
ERROR 1048 (23000): Column 'check_val' cannot be null

mysql57> SELECT * FROM t2;
+-----+-------+-----------+
| num | val   | check_val |
+-----+-------+-----------+
|   1 | Tokyo |         1 |
|   2 | Osaka |         1 |
+-----+-------+-----------+
2 rows in set (0.00 sec)

I seem this approach is better.
NOT NULL constraint doesn't depend on sql_mode.

This generated column feature can be introduced into tables which have already been filled data.

mysql57> SELECT * FROM t3; -- Invalid data has already been inserted.
+-----+-------+
| num | val   |
+-----+-------+
|   1 | Tokyo |
|   2 | Osaka |
|   3 | Nara  |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) NOT NULL; -- Add VIRTUAL generated column.
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+-------+-----------+
| num | val   | check_val |
+-----+-------+-----------+
|   1 | Tokyo |         1 |
|   2 | Osaka |         1 |
|   3 | Nara  |         0 |
+-----+-------+-----------+
3 rows in set (0.00 sec)

mysql57> INSERT INTO t3 (num, val) VALUES (4, 'Kyoto');
ERROR 1048 (23000): Column 'check_val' cannot be null

With VIRTUAL generated column, the data which is already stored don't be affected by fake CHECK constraint.

VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers.

http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns

VIRTUAL generated `check_val` doesn't calculate at the time of ALTER TABLE, thus this case doesn't affect stored data but new data are under constraint.

mysql57> ALTER TABLE t3 DROP check_val;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | Tokyo |
|   2 | Osaka |
|   3 | Nara  |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) STORED NOT NULL;
ERROR 1048 (23000): Column 'check_val' cannot be null

The other hand, with STORED generated column, all exist data are calculated and checked during ALTER TABLE.
If the table already has invalid (for fake CHECK constraint) data, ALTER TABLE fails.

This is a stage of idea, is not practice in production environment.
But this idea maybe satisfy some case of "OMG, doesn't MySQL have CHECK constraint!?" situation.

2015/08/07

MySQL 5.7のgenerated columnでついにCHECK制約っぽいことを実現できる

generated columnそのものの説明はこのへんに。
日々の覚書: MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか

というわけでやってみましょう。

mysql57> CREATE TABLE t1 (num int primary key, val varchar(32)) Engine= InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (1, '2015-08-06');
Query OK, 1 row affected (0.01 sec)

mysql57> SELECT * FROM t1;
+-----+------------+
| num | val        |
+-----+------------+
|   1 | 2015-08-06 |
+-----+------------+
1 row in set (0.00 sec)

こんなテーブルがあったとするじゃろ？

mysql57> ALTER TABLE t1 ADD check_val datetime AS (CAST(val AS datetime));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

valの中身をdatetimeにキャストする関数を食わせたgenerated columnを作るじゃろ？

mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-31');
ERROR 1292 (22007): Incorrect datetime value: '2015-09-31'

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
+-----+------------+---------------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-30');
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
+-----+------------+---------------------+
2 rows in set (0.00 sec)

なんと、日付として不正な値を入れると、キャストに失敗してエラーが返るんじゃよ！

mysql57> SET sql_mode= '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql57> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (3, '2015-11-31');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql57> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2015-11-31' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
|   3 | 2015-11-31 | NULL                |
+-----+------------+---------------------+
3 rows in set, 1 warning (0.00 sec)

もちろん、sql_modeがSTRICT_TRANS_TABLESかSTRICT_ALL_TABLESでないと、キャストの失敗を丸めちゃうので台無しになる。まさかもう非strict_modeとか選ばない…よね？
datetime型の場合はNOT NULL制約をつけても0000-00-00 00:00:00で丸めちゃうからアレだけど、IFをかませてtinyintとかにすると

mysql57> CREATE TABLE t2 (num int primary key, val varchar(32), check_val tinyint AS (IF(val IN ('東京', '大阪'), 1, NULL)) NOT NULL) Engine= InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (1, '東京');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (2, '大阪');
Query OK, 1 row affected (0.01 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (3, '奈良');
ERROR 1048 (23000): Column 'check_val' cannot be null

mysql57> SELECT * FROM t2;
+-----+--------+-----------+
| num | val    | check_val |
+-----+--------+-----------+
|   1 | 東京   |         1 |
|   2 | 大阪   |         1 |
+-----+--------+-----------+
2 rows in set (0.00 sec)

おおおこれ結構良くない？このIF関数で無理矢理NULLを返す方式だと、NOT NULL制約の問題なので非strict_modeでもちゃんとエラーになってくれる。わーい（死）

ちなみに、既に格納されている値に対して後からこの似非CHECK制約を追加する場合、

mysql57> SELECT * FROM t3;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | 東京   |
|   2 | 大阪   |
|   3 | 奈良   |
+-----+--------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF (val IN ('東京', '大阪'), 1, NULL)) NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+--------+-----------+
| num | val    | check_val |
+-----+--------+-----------+
|   1 | 東京   |         1 |
|   2 | 大阪   |         1 |
|   3 | 奈良   |         0 |
+-----+--------+-----------+
3 rows in set (0.00 sec)

mysql57> INSERT INTO t3 (num, val) VALUES (4, '京都');
ERROR 1048 (23000): Column 'check_val' cannot be null

generated columnの種類を指定しない場合、暗黙のデフォルトとしてVIRTUALになるので、
「VIRTUALなgenerated columnの場合は、今までのものは無視されて、新たに更新するものに対して有効」（VIRTUALなgenerated columnはALTER TABLEの時には何もせず、SELECTされた時に都度関数が走るから）になり、

mysql57> ALTER TABLE t3 DROP check_val;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | 東京   |
|   2 | 大阪   |
|   3 | 奈良   |
+-----+--------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF (val IN ('東京', '大阪'), 1, NULL)) STORED NOT NULL;
ERROR 1048 (23000): Column 'check_val' cannot be null

「STOREDなgenerated columnの場合は、今までのもので似非CHECK制約を通らない行がある場合、そもそもALTER TABLEが通らない」（STOREDなgenerated columnはALTER TABLEの時に関数を通って計算され、それがデータに格納されるから）になる。

おおお面白いんじゃないこれ？

【2015/08/12 10:52】
I translated this post into English, See An idea for using MySQL 5.7's generated column like CHECK constraint

2015/08/04

MySQL 5.7.8以降のrpmパッケージではvalidate_passwordプラグインがデフォルトで有効になっている

というよりは、今までが「デフォルトで入っているつもりが入っていなかった」ので、そのバグをFIX、ということらしい。

The validate_password plugin was not installed by RPM packages for platorms using systemd or SysV-style initialization scripts. (Bug #18438833)

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.8 (2015-08-03)

やったね！初心者殺しの罠が一つ増えたよ！ orz

validate_passwordプラグインについては5.6で導入された機能なので、日本語マニュアルが既にある。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 6.1.2.6 パスワード検証プラグイン

これが有効になっているということはどういうことかというと、

$ /etc/init.d/mysqld start
Initializing MySQL database:  2015-08-03T15:33:57.341022Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

$ grep password /var/log/mysqld.log
2015-08-03T15:33:57.971473Z 1 [Warning] A temporary password is generated for root@localhost: =w?e&SnQf0aK
2015-08-03T15:34:03.509853Z 2 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)

$ mysql -p
Enter password:

mysql> SET PASSWORD= '';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

まあ、こういうことですよね。。

mysql> SHOW GLOBAL VARIABLES LIKE 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set (0.00 sec)

デフォルトでは8文字以上、英大文字小文字数字記号の4種類を含む必要がある。
この条件を満たしたパスワードに変更するまで、初期セットアップ状態だとパスワードがEXPIREされた状態なので、SHOWコマンドもSETコマンドも効かない。ははは。

そんな都合の良い文字列パッと思い付かねーよ！って場合は、もとのパスワードをそのまま入れてしまえばいい。なんと通る。
それか、"Do_you_love_MySQL57?"もこの条件を満たすので使ってもいいですよ :)

mysql> SET PASSWORD= '=w?e&SnQf0aK';
Query OK, 0 rows affected (0.01 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL                 | GPL     |
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.01 sec)

あとはSET GLOBALで強度を弱めるなり、UNINSTALL PLUGINするなりご自由に…という感じ。

初期のランダムパスワードが/var/log/mysqld.logにあることと合わせて、インストールでくじけさせてくれる新機能でした。

登録: 投稿 ( Atom )

GA