2015年6月29日月曜日

MySQLのHandlerレイヤーが何をしているのか探る旅 at #ChugokuDB

過日、このイベントでしゃべってきました。

MySQL・PostgreSQLユーザーグループ(MyNA・JPUG)合同DB勉強会 in 東京 - 中国地方DB勉強会 | Doorkeeper





最初はスライドの副題の通り、主にInnoDB memcached PluginとNDB memcached Engineの違い、要は、memcachedプロトコルをしゃべるmysqldプロセスと、NDB APIをしゃべるmemcachedプロセスの違い…とかなんとかしゃべろうとしてたんですが、気が付いたら各daemon pluginがどの辺のレイヤーまで横取りしているのかを調べていました。

本当は「redisからデータを取り出すストレージエンジンがあってredisプロトコルをしゃべるdaemon pluginがあればほら! redis-cliでredisからデータが取り出せるMySQLのできあがり! 変態!」とかやりたかったんですけど、daemon pluginの壁は高かったです。残念。


このイベント、
MySQLとPostgreSQLのユーザ会の合同データベース勉強会なのに
セミナーは各データベースのNoSQL関連機能縛りという
ユニークなイベントになっています。
[mysql 16234] MyNA・JPUG合同DB勉強会 in 東京 のご案内


ということだったんですけど、MySQLって昔から(少なくとも俺が本格的に触り始めたころには)HandlerSocketがあったしMySQL Clusterのmemcached(NDB memcached Engine)もあったので、InnoDB memcached Pluginも「ああ、また出たのね」って感じだしMySQL HTTP Pluginも「ああ、また出たのね」って感じだし、MySQLにとってはよくあることなんじゃないかなーと思ってます。

世に出回っているdaemon pluginの出来のよさにもちょっと驚きました。MySQL HTTP Plugin *以外は*

あと、PostgreSQLはやっぱり真面目で、JSON(B)型とそれを入出力する関数や演算子の充実を丁寧にやっていて、あー、やっぱりMySQLとは違うんだなぁという感じがしました。

@soudaiさん お疲れ様でした!


【2015/06/29 12:59】

そういえば去年こんなやり取りがあって、



中国地方DB勉強会 in 東京なんていう不思議な勉強会で実現して初めて顔を合わせたの、感慨深い。

2015年6月25日木曜日

MYSQL HTTP PLUGIN確認 の補足

MySQL HTTP Plugin確認 | variable.jp [データベース,パフォーマンス,運用] を写経して失敗したので補足メモ。

2. my.cnfをマニュアルを見て編集
myhttp_default_mysql_user_name = http_sql_user
myhttp_default_mysql_user_passwd = sql_secret
myhttp_default_mysql_user_host = 127.0.0.1

* myhttp_default_mysql_user_nameは BASIC認証に成功した後に myhttpプラグインからMySQLに接続しに行くときに使うユーザー名。暗黙のデフォルトは "root"
* myhttp_default_mysql_user_passwdは BASIC認証に成功した後に myhttpプラグインからMySQLに接続しに行くときに使うパスワード。暗黙のデフォルトは ""(空文字列)

ということでこの2つは、`3. myhttpデータベースを検証用に作成しテスト用テーブルとユーザーを作成` の後ろの方でCREATE USERしたユーザー/パスワードと合わせる。

このホスト部分はcurlで指定したホスト部分に書き換わるので、↑の記事の通りcurl --url 192.168.56.x:8080 ..ならそのまま通るけど、curl --url 127.0.0.1:8080にした場合はhttp_sql_user@'192.168.56.0/255.255.255.0'に対してhttp_sql_user@127.0.0.1でログインしようとしちゃうので、curlが通らなくなる。罠い。


しかもタチの悪いことに、

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+%2A+FROM+simple"

$ tail data/error.log
..
2015-06-25T07:44:56.207437Z 2 [ERROR] Invalid user

HTTPレスポンス何か返せよ! エラーメッセージ雑だよ! せめて自分がmyhttpプラグインだって名乗れよ!

あと、

* myhttp_default_mysql_user_hostは BASIC認証に成功した後に myhttpプラグインからMySQLに接続しに行くときの…ホスト…。

  * ってことは、myhttpプラグインがインストールされているマシンじゃないMySQLにもつなぎにいけるってことか…この変態め…。
  * と思ったけど、127.0.0.1以外の何かに書き換えてもダメ。どうやら、curlでリクエストしたホスト名に置き換えているぽい。

$ curl -s --user basic_auth_user:basic_auth_passwd --url "http://172.17.0.2:8080/sql/myhttp/SELECT+%2A+FROM+simple" | jq .

Breakpoint 1, my_da_sql_init_security_ctx (thd=0x7fdf80005780, user=0x2eeb148 "http_sql_user",
    host=0x7fdf800055a8 "172.17.0.2", db=0x7fdf80005668 "myhttp")
    at /export/home/pb2/build/sb_0-13293843-1411506406.69/mysql-5.7.5-labs-http/sql/sql_da_sql.cc:146
146         char * user_copy= my_strdup(key_memory_Security_context, user, MYF(MY_WME));


$ curl -s --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+%2A+FROM+simple" | jq .

Breakpoint 1, my_da_sql_init_security_ctx (thd=0x7fdf780040e0, user=0x2eeb148 "http_sql_user",
    host=0x7fdf7800e378 "127.0.0.1", db=0x7fdf78001988 "myhttp")
    at /export/home/pb2/build/sb_0-13293843-1411506406.69/mysql-5.7.5-labs-http/sql/sql_da_sql.cc:146
146         char * user_copy= my_strdup(key_memory_Security_context, user, MYF(MY_WME));

一応ホスト名とか受け取れるけど、ロードバランサーとかの下に入れるとどういう動きするんでしょうねこれ。。


curl --userで指定するBASIC認証部分のユーザー名とパスワードはそれぞれ

* myhttp_basic_auth_user_name .. 暗黙のデフォルト "basic_auth_user"
* myhttp_basic_auth_user_passwd .. 暗黙のデフォルト "basic_auth_passwd"


地雷ヤーズのためのメモでした。

2015年6月19日金曜日

現代のMySQLはもう不要にINをEXISTSに書き換えない

漢(オトコ)のコンピュータ道: なぜMySQLのサブクエリは遅いのか。

この記事は 2009/3/25 に書かれたもののようである。
2009年3月といえばMySQL 5.1がGAになってわずか半年、MySQL 6.0.10-alphaがリリースされた頃で、MariaDBもまだ姿を見せていない頃だ。


時は流れて2015年、MySQL 5.6がGAになって早2年半、5.7のGAマダァ-? (・∀・ )っ/凵⌒☆チンチン

な頃なので、もういい加減誰か言ってくれてもいいんじゃないかと思う。


もうMySQL(5.6)は不要にINをEXISTSに書き換えたりしないんだよって


mysql51> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
| id | select_type        | table   | type           | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
|  1 | PRIMARY            | Country | ALL            | NULL          | NULL        | NULL    | NULL |  222 | Using where |
|  2 | DEPENDENT SUBQUERY | City    | index_subquery | CountryCode   | CountryCode | 3       | func |    9 | Using where |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
2 rows in set (0.00 sec)


mysql55> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
| id | select_type        | table   | type           | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
|  1 | PRIMARY            | Country | ALL            | NULL          | NULL        | NULL    | NULL |  226 | Using where |
|  2 | DEPENDENT SUBQUERY | City    | index_subquery | CountryCode   | CountryCode | 3       | func |    7 | Using where |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
2 rows in set (0.02 sec)


mysql56> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------------------------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref             | rows | Extra                            |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------------------------------+
|  1 | SIMPLE      | Country | ALL  | PRIMARY       | NULL        | NULL    | NULL            |  239 | Using where                      |
|  1 | SIMPLE      | City    | ref  | CountryCode   | CountryCode | 3       | d1.Country.Code |    9 | Using where; FirstMatch(Country) |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------------------------------+
2 rows in set (0.00 sec)

mysql57> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+-------------+---------+------------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref             | rows | filtered | Extra                            |
+----+-------------+---------+------------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
|  1 | SIMPLE      | Country | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL            |  239 |    14.29 | Using where                      |
|  1 | SIMPLE      | City    | NULL       | ref  | CountryCode   | CountryCode | 3       | d1.Country.Code |   18 |    33.33 | Using where; FirstMatch(Country) |
+----+-------------+---------+------------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql57> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'd1.Country.Population' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `d1`.`Country`.`Code` AS `Code`,`d1`.`Country`.`Name` AS `Name`,`d1`.`Country`.`Continent` AS `Continent`,`d1`.`Country`.`Region` AS `Region`,`d1`.`Country`.`SurfaceArea` AS `SurfaceArea`,`d1`.`Country`.`IndepYear` AS `IndepYear`,`d1`.`Country`.`Population` AS `Population`,`d1`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`d1`.`Country`.`GNP` AS `GNP`,`d1`.`Country`.`GNPOld` AS `GNPOld`,`d1`.`Country`.`LocalName` AS `LocalName`,`d1`.`Country`.`GovernmentForm` AS `GovernmentForm`,`d1`.`Country`.`HeadOfState` AS `HeadOfState`,`d1`.`Country`.`Capital` AS `Capital`,`d1`.`Country`.`Code2` AS `Code2` from `d1`.`Country` semi join (`d1`.`City`) where ((`d1`.`City`.`CountryCode` = `d1`.`Country`.`Code`) and (`d1`.`Country`.`Continent` = 'Asia') and (`d1`.`City`.`Population` > (`d1`.`Country`.`Population` / 2)))
2 rows in set (0.00 sec)

クエリーは先頭にリンクを張った、御大のブログ記事のものと一緒。world_innodb.sqlを流してから、3つのテーブルに対してANALYZE TABLEをかけてある。5.1.73, 5.5.44, 5.6.25, 5.7.7の比較だ。

最後の5.7のワーニングは、5.7のデフォルトのEXPLAINがEXPLAIN EXTENDEDになったので、オプティマイズした結果をワーニングバッファに突っ込んでいる。SHOW WARNINGSでオプティマイザーがどうクエリーを書き換えたかが見えるのでよく見ると。

select .. from `d1`.`Country` semi join (`d1`.`City`) where ..


semi-join最適化は5.6.5からの実装なので、MySQL 5.6のGAバージョンならこれは5.6でも同じように最適化できる。
MySQL :: MySQL 5.6 Reference Manual :: 8.2.1.18 Subquery Optimization

もちろんすべてのINが良しなにされるとは限らない(本当に相関サブクエリーが必要なケースは相関サブクエリーになるし、5.7になっても相関サブクエリーは遅い)が、MySQLのオプティマイザーがバカなせいでつらい目を見た過去の俺とか俺とか俺とかにはとても嬉しい。

日本よ、これがMySQL 5.6だッ!! (c) nippondanji


ちなみに、

MariaDB [d1]> SELECT @@version;
+----------------+
| @@version      |
+----------------+
| 5.3.12-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [d1]> EXPLAIN EXTENDED SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref             | rows | filtered | Extra                            |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
|  1 | PRIMARY     | Country | ALL  | PRIMARY       | NULL        | NULL    | NULL            |  269 |   100.00 | Using where                      |
|  1 | PRIMARY     | City    | ref  | CountryCode   | CountryCode | 3       | d1.Country.Code |    1 |   100.00 | Using where; FirstMatch(Country) |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
2 rows in set, 2 warnings (0.00 sec)

MariaDB [d1]> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'd1.Country.Population' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select `d1`.`Country`.`Code` AS `Code`,`d1`.`Country`.`Name` AS `Name`,`d1`.`Country`.`Continent` AS `Continent`,`d1`.`Country`.`Region` AS `Region`,`d1`.`Country`.`SurfaceArea` AS `SurfaceArea`,`d1`.`Country`.`IndepYear` AS `IndepYear`,`d1`.`Country`.`Population` AS `Population`,`d1`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`d1`.`Country`.`GNP` AS `GNP`,`d1`.`Country`.`GNPOld` AS `GNPOld`,`d1`.`Country`.`LocalName` AS `LocalName`,`d1`.`Country`.`GovernmentForm` AS `GovernmentForm`,`d1`.`Country`.`HeadOfState` AS `HeadOfState`,`d1`.`Country`.`Capital` AS `Capital`,`d1`.`Country`.`Code2` AS `Code2` from `d1`.`Country` semi join (`d1`.`City`) where ((`d1`.`City`.`CountryCode` = `d1`.`Country`.`Code`) and (`d1`.`Country`.`Continent` = 'Asia') and (`d1`.`City`.`Population` > (`d1`.`Country`.`Population` / 2)))
2 rows in set (0.00 sec)

MariaDBはもっと前からできるんだけどな! :)
(5.3で試してるけど、もとは5.2くらいだったような気がする。ただし裏は取ってない)

ところでsemi joinって聞くたびに蝉ジョインとか脳内変換されるんだけどこれなんとかなりませんか。

2015年6月9日火曜日

MySQL 5.7.4で導入されたdefault_password_lifetimeがじわじわくる(MySQL 5.7.11でFIX!!)

【2016/01/13 10:12】
MySQL 5.7.11でdefault_password_lifetimeのデフォルトは0に変更になりました!
それ以降のバージョンであればこの記事の内容は気にする必要はありません。

日々の覚書: MySQL 5.7.11でdefault_password_lifetimeのデフォルトが0になるらしい!



TL;DR

default_password_lifetime= 0 を秘伝のmy.cnfに入れておくつもり。



MySQL :: MySQL 5.7 Reference Manual :: 5.1.4 Server System Variables


パラメーターの意味は読んで字のごとく、「最後にパスワードが更新されてからこの期間が経つと、パスワードをEXPIREする」。暗黙のデフォルトは360で、単位は日。つまり何もいじらないと、新規作成したユーザーは360日後にパスワードがEXPIREされてアプリが止まる。。


mysql57> CREATE USER yoku0825 IDENTIFIED WITH mysql_native_password AS '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA';
Query OK, 0 rows affected (0.03 sec)

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 2015-06-09 11:06:33
    password_lifetime: NULL
       account_locked: N
1 row in set (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+

こんなユーザーを作ってみる。360日待ってはいられないので、password_last_changedを過去に戻すことで状況を再現させてみる。


mysql57> UPDATE mysql.user SET password_last_changed= '1999-07-31 00:00:00' WHERE user= 'yoku0825'\G
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: NULL
       account_locked: N
1 row in set (0.00 sec)

mysql57> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

うむ、これはいい。余計なお世話だけど、これはまだいい。
この時にmysql.userの中身を見てみても、


mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: NULL
       account_locked: N
1 row in set (0.00 sec)

password_expiredは'N'のままだし、password_filetimeはNULLのままだ。

ということは、


mysql57> SET GLOBAL default_password_lifetime= 36500;
Query OK, 0 rows affected (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+

mysql57> SET GLOBAL default_password_lifetime= 0;
Query OK, 0 rows affected (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+

default_password_lifetimeを大きくしてやればそのまま通るし、default_password_lifetime= 0はデフォルトのライフタイムを無効化する値なのでこちらでも通る。
これ、アカウントの作成時に判定しているのではなくて、ログインのたびに check_password_lifetime関数 の中で判定している。password_lifetimeが設定されていればその値を、NULLならdefault_password_lifetimeを、そして *0なら* 決してEXPIREしない。


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

いったんもとに戻して、ALTER USERをいろいろ叩いてみた。
MySQL :: MySQL 5.7 Reference Manual :: 13.7.1.1 ALTER USER Syntax


mysql57> ALTER USER yoku0825 PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: 0
       account_locked: N
1 row in set (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+

EXPIRE NEVERにすると、password_lifetimeカラムの値が0になる。default_password_lifetimeは評価されない。


mysql57> ALTER USER yoku0825 PASSWORD EXPIRE DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: NULL
       account_locked: N
1 row in set (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

EXPIRE DEFAULTにするとpassword_lifetimeがNULLになり、default_password_lifetimeの影響を受ける。


mysql57> ALTER USER yoku0825 PASSWORD EXPIRE INTERVAL 36000 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: N
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: 36000
       account_locked: N
1 row in set (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
+----------------+
| current_user() |
+----------------+
| yoku0825@%     |
+----------------+

EXPIRE INTERVAL .. DAYでライフタイムを決め打ちすると、そちらが優先される(nullでないのでdafault_password_lifetimeは読まない)


mysql57> ALTER USER yoku0825 PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT user, host, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'\G
*************************** 1. row ***************************
                 user: yoku0825
                 host: %
               plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
     password_expired: Y
password_last_changed: 1999-07-31 00:00:00
    password_lifetime: 36000
       account_locked: N
1 row in set (0.00 sec)

$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

EXPIREだけで他に何もつけない状態だと、password_expiredが'Y'に更新される。この場合、password_last_changedは評価されず(check_password_lifetime関数の中には入るけど、最初のif文ですぐ抜ける)、EXPIRE状態になる。この状態になると、password_expired= 'N'に戻すためにSET PASSWORDで変更するか、UPDATEしてFLUSH PRIVILEGESするしかない。

思いもよらず360日後にこの罠に襲われる人が、1人でも少なくなりますように。


【2015/06/09 18:47】
デフォルトは0にするかワーニングとか出して、ってFeature Requestを上げました。
ダウンロードする時に「No thanks」を知らずにアカウント作っちゃった人とか、"Affects Me"で応援していただけるとありがたいです。

MySQL Bugs: #77277: default_password_lifetime should be set 0 as implicit default value

2015年6月4日木曜日

MySQL 5.7からデフォルトになるSTRICT_TRANS_TABLEはMyISAMにも影響を及ぼす

恥ずかしながら完全に誤解してた。

MySQL :: MySQL 5.6 Reference Manual :: 5.1.7 Server SQL Modes

For nontransactional tables, the behavior is the same for either mode if the bad value occurs in the first row to be inserted or updated: The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict mode is enabled:

For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 11.6, “Data Type Default Values”.


strictとして扱われるかかどうかは (STRICT_ALL_TABLES | STRICT_TRANS_TABLES) で決まり、ストレージエンジンがトランザクション対応かどうかは関係ない。

つまり、

mysql56> CREATE TABLE t1 (val varchar(1)) Engine= MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql56> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.01 sec)

mysql56> INSERT INTO t1 VALUES ('ab');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql56> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'val' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql56> SELECT * FROM t1;
+------+
| val  |
+------+
| a    |
+------+
1 row in set (0.01 sec)

sql_modeが空っぽならワーニングで入るけど、


mysql56> CREATE TABLE t2 (val varchar(1)) Engine= MyISAM;
Query OK, 0 rows affected (0.10 sec)

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

mysql56> INSERT INTO t2 VALUES ('ab');
ERROR 1406 (22001): Data too long for column 'val' at row 1

mysql56> SELECT * FROM t2;
Empty set (0.00 sec)

STRICT_TRANS_TABLESだと入らない。


更に、

mysql56> INSERT INTO t3 VALUES ('a'), ('ab'), ('abc');
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql56> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1406 | Data too long for column 'val' at row 2 |
| Warning | 1406 | Data too long for column 'val' at row 3 |
+---------+------+-----------------------------------------+
2 rows in set (0.01 sec)

mysql56> SELECT * FROM t3;
+------+
| val  |
+------+
| a    |
| a    |
| a    |
+------+
3 rows in set (0.01 sec)

1個目が正しいデータで、2個目移行が間違ったデータを突っ込もうとすると、warningが起こるがINSERTはされる。


mysql56> INSERT INTO t4 VALUES ('abc'), ('ab'), ('a');
ERROR 1406 (22001): Data too long for column 'val' at row 1

mysql56> SELECT * FROM t4;
Empty set (0.00 sec)

1個目が正しくなかった場合、あとに正しいデータが入っていてもそれもabortされる。


STRICT_TRANS_TABLESは操作対象がトランザクション対応の時にstrict_modeになって、トランザクション非対応な時はnon-strict_modeだと完全に誤解していた。。

2015年6月2日火曜日

MySQL 5.7時代のユーザー作成について

日々の覚書: MySQL 5.7.6でCREATE USERせずにGRANTステートメントを叩くとワーニング で、結局どうすればいいのか全く書いてなかったので書き直し。

* パスワード未設定のユーザーをGRANTで作成できなくなった。
* CREATE USERでユーザー作ってからGRANTする。

mysql57> GRANT ALL ON db.* TO grant_style@localhost; -- ユーザー未作成, パスワード未指定のGRANTが転ける
ERROR 1133 (42000): Can't find any matching row in the user table

mysql57> CREATE USER create_style@localhost; -- パスワード未指定のCREATE USERは通る
Query OK, 0 rows affected (0.00 sec)

mysql57> GRANT ALL ON db.* TO create_style@localhost; -- ユーザーが存在するとGRANTが通る
Query OK, 0 rows affected (0.01 sec)


* GRANT .. IDENTIFIED BY '..'('..'は生パスワード)の構文だとワーニングが出る。
* ワーニングは出るけれど、取り敢えずまだ使える。
* ↑と同じで推奨方法は CREATE USERで作ってからGRANT。


mysql57> GRANT ALL ON db.* TO grant_style@localhost IDENTIFIED BY 'test';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql57> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql57> CREATE USER create_style@localhost IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

mysql57> GRANT ALL ON db.* TO create_style@localhost;
Query OK, 0 rows affected (0.04 sec)


* IDENTIFIED BY PASSWORD '*..'('*..'は41桁ハッシュされたパスワード)の構文でもワーニングが出る。
* ワーニングは出るけれど取り敢えずまだ使える。
* IDENTIFIED WITH mysql_native_password AS '*..'構文を使う(Authentication Pluginとか使ってて他のプラグイン使う場合はプラグイン名読み替える)
* 最終的な推奨としては、CREATE USER .. IDENTIFIED WITH mysql_native_password AS '*..'
* ちなみにASはBYでもいいらしい。ワーニングメッセージに合わせてみただけ。

mysql57> GRANT ALL ON db.* TO grant_style@localhost IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql57> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                               |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'IDENTIFIED BY PASSWORD' is deprecated and will be removed in a future release. Please use IDENTIFIED WITH <plugin> AS <hash> instead |
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.    |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql57> GRANT ALL ON db.* TO grant_style@localhost IDENTIFIED WITH mysql_native_password AS '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql57> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql57> CREATE USER create_style@localhost IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql57> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                               |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'IDENTIFIED BY PASSWORD' is deprecated and will be removed in a future release. Please use IDENTIFIED WITH <plugin> AS <hash> instead |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


特にバージョンをまたいでユーザーを移す時など、わたしは SHOW GRANTSの出力をほげほげして食わせるのが大好き なんですが、MySQL 5.7未満 => 5.7より未来でdeprecatedからremovedになると、これが出来なくなるんだよなぁ。。

$ client/mysqldump --grants -S /usr/mysql/5.6.25/data/mysql.sock --all-databases -x
..
--
-- Dumping grants
--
GRANT USAGE ON *.* TO 'create_style'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'; -- これがワーニング。
GRANT ALL PRIVILEGES ON `db`.* TO 'create_style'@'localhost';
GRANT USAGE ON *.* TO 'grant_style'@'localhost'; -- これはパスワードなしでエラー。
GRANT ALL PRIVILEGES ON `db`.* TO 'grant_style'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION; -- これもエラー。
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION; -- これもエラー。

( ´-`).oO(パスワード設定してないのが悪いだけか。


更に5.7からはSHOW GRANTSの結果にハッシュ済みのパスワードを含めてくれなくなったので、

mysql57> SHOW GRANTS FOR create_style@localhost;
+--------------------------------------------------+
| Grants for create_style@localhost                |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'create_style'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql57> SELECT authentication_string FROM mysql.user WHERE (user, host)= ('create_style', 'localhost');
+-------------------------------------------+
| authentication_string                     |
+-------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-------------------------------------------+
1 row in set (0.00 sec)


お手製の mysqldump --grantsパッチ も使えなくなってしまうのだなぁ。。

2015年6月1日月曜日

何も考えずに真っ新なCentOS 6.6にMySQL 5.7をyumで叩き込むメモ

主にバグの再現確認に使う用途。yumでもいいからクリーンな状態のCentOS 6.6にMySQL 5.7を入れたいときの。

TL;DR

コマンドはこれ。

$ sudo yum install -y https://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
$ sudo yum install -y --enablerepo=mysql57-community-dmr mysql-community-server
$ sudo service mysqld start
$ sudo grep password /var/log/mysqld.log
$ mysql -uroot -p


【2015/11/05 10:05】
MySQL 5.7のGAリリースに伴い、mysql-community-releaseが新しくなっています。
2015/11/05現在だと、mysql-community-releaseは mysql57-community-release-el6-7.noarch.rpm になっており、こちらはデフォルトでmysql57-communityリポジトリーが有効になっています(ので、--enablerepoいらない)



1. MySQL :: Download MySQL Yum Repository からCentOS 6.x用(いや、本当はRHEL/Oracle Linux用のだけど)のrpmパッケージをインストールする(と、2015/06/01現在では↓の5つのリポジトリーが登録される)

| repository                 | enabled |
|----------------------------|---------|
| mysql-connectors-community | yes     |
| mysql-tools-community      | yes     |
| mysql55-community          | no      |
| mysql56-community          | yes     |
| mysql57-community-dmr      | no      |


2. enabled= noになっているmysql57-community-dmrを--enablerepoで指定してmysql-community-serverをインストールする。


3. mysqldサービスを起動(今までOracleビルドのMySQLのrpmパッケージ .. ファイル名が MySQL-server-*.rpm になっているようなやつ .. はサービス名が "mysql" だったのが、yumリポジトリーの場合は"mysqld"になっている。たぶん、過去のRHELのBaseリポジトリーと命名規則を合わせたんではないだろうか)

/var/lib/mysqlはこの時点でmysqld --initializeで初期化される。MySQL-server-*.rpmはインストール時に初期化(= 5.6までしかMySQL-server-*.rpmは存在しないので、5.6当時でmysql_install_db)が走るが、mysql-community-server-*.rpmはservice mysqld startの中でdatadirの中身をチェックし、空っぽならば初期化が走るようになっている。


4. mysqld --initialize ということは、だ。初期パスワードは /root/.mysql_secret には *ない* 。エラーログに吐いている。デフォルトでは/var/log/mysqld.logだ。罠い。
日々の覚書: MySQL 5.7.6でデータベースの初期化が変わる mysql_install_dbからmysqld --initialize


$ sudo service mysqld start
Initializing MySQL database:  2015-06-01T09:35:30.031754Z 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:

デフォルトパスワードの在り処なんてビタ1文字何も教えてくれない。罠い。


$ grep password /var/log/mysqld.log
2015-06-01T09:35:31.070842Z 1 [Warning] A temporary password is generated for root@localhost: l#KCEX1;iypk
2015-06-01T09:35:37.208351Z 2 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)

そしてなんか謎のUNKWOWN_MYSQL_USERとかいうのがログイン試行してる。謎い。


110         # Spin for a maximum of N seconds waiting for the server to come up;
    111         # exit the loop immediately if mysqld_safe process disappears.
    112         # Rather than assuming we know a valid username, accept an "access
    113         # denied" response as meaning the server is functioning.
    114         ret=0
    115         TIMEOUT="$STARTTIMEOUT"
    116         while [ $TIMEOUT -gt 0 ]; do
    117             RESPONSE=$(/usr/bin/mysqladmin --no-defaults --socket="$adminsocket" --user=UNKNOWN_MYSQL_USER ping 2>&1)         && break
    118             echo "$RESPONSE" | grep -q "Access denied for user" && break
    119             if ! /bin/kill -0 $safe_pid 2>/dev/null; then
    120                 echo "MySQL Daemon failed to start."
    121                 ret=1
    122                 break
    123             fi
    124             sleep 1
    125             let TIMEOUT=${TIMEOUT}-1
    126         done

/etc/init.d/mysqldの中身を覗いてみると、ちゃんと起動したかどうかを敢えてエラーになりそうなユーザーでmysqladmin pingして"Access denied"が返されることで起動したことを確認しているぽい。


でもこれ、UNKNWON_MYSQL_USER@localhostがいたら成功しちゃって"Access denied"が返らないんじゃね? と思ったら


mysql> create user UNKNOWN_MYSQL_USER;
ERROR 1470 (HY000): String 'UNKNOWN_MYSQL_USER' is too long for user name (should be no longer than 16)

長さ制限に引っかかってそんなユーザーは作れなかった。なるほど。


何の話だかよくわからなくなってきたのでこれまで。