2015年3月27日金曜日

MySQL 5.7でやっと(?)1テーブル複数トリガーが仕掛けられるようになった

As of MySQL 5.7.2, it is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you cannot have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a trigger_order clause that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing trigger. With PRECEDES, the new trigger activates before the existing trigger.
MySQL :: MySQL 5.7 Reference Manual :: 13.1.16 CREATE TRIGGER Syntax


試してみよう。

5.6のフツーの動作は

mysql56> CREATE TRIGGER before_insert_1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SET num = NEW.num, val = NEW.val;
Query OK, 0 rows affected (0.02 sec)

mysql56> INSERT INTO t1 VALUES (1, 'one');
Query OK, 1 row affected (0.03 sec)

mysql56> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql56> SELECT * FROM t2;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql56> CREATE TRIGGER before_insert_2 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

もちろんこう。


5.7.6だと

mysql57> CREATE TRIGGER before_insert_1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SET num = NEW.num, val = NEW.val;
Query OK, 0 rows affected (0.01 sec)

mysql57> INSERT INTO t1 VALUES (1, 'one');
Query OK, 1 row affected (0.01 sec)

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

mysql57> SELECT * FROM t2;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql57> CREATE TRIGGER before_insert_2 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num;
Query OK, 0 rows affected (0.01 sec)

mysql57> INSERT INTO t1 VALUES (2, 'two');
Query OK, 1 row affected (0.04 sec)

mysql57> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.00 sec)

mysql57> SELECT * FROM t2;
+-----+--------------------+
| num | val                |
+-----+--------------------+
|   1 | one                |
|   2 | 残念だったな       |
+-----+--------------------+
2 rows in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: before_insert_1
               Event: INSERT
               Table: t1
           Statement: INSERT INTO t2 SET num = NEW.num, val = NEW.val
              Timing: BEFORE
             Created: 2015-03-27 02:53:23.66
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: before_insert_2
               Event: INSERT
               Table: t1
           Statement: UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num
              Timing: BEFORE
             Created: 2015-03-27 02:53:47.85
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

当たり前だけど張れた。順番の制御は、FOR EACH ROWのあとにPRECEDESまたはFOLLOWSで既存のトリガーを指定する(省略時は最後のトリガーの更に次に追加されるのかな)


mysql57> DROP TRIGGER before_insert_2;
Query OK, 0 rows affected (0.01 sec)

mysql57> CREATE TRIGGER before_insert_3 BEFORE INSERT ON t1 FOR EACH ROW PRECEDES before_insert_1 UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num;
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t1 VALUES (3, 'three');
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> SELECT * FROM t2;
+-----+--------------------+
| num | val                |
+-----+--------------------+
|   1 | one                |
|   2 | 残念だったな       |
|   3 | three              |
+-----+--------------------+
3 rows in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: before_insert_3
               Event: INSERT
               Table: t1
           Statement: UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num
              Timing: BEFORE
             Created: 2015-03-27 02:56:45.90
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: before_insert_1
               Event: INSERT
               Table: t1
           Statement: INSERT INTO t2 SET num = NEW.num, val = NEW.val
              Timing: BEFORE
             Created: 2015-03-27 02:53:23.66
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

UPDATE t2が先に実行されるのでt2にレコードがなくて空ぶって、そのあとINSERT INTO t2でコピーされる。
ちょっと便利になるかも知れない。

2015年3月25日水曜日

2015年版 InnoDB Monitorの仲間たち(InnoDBエンジン本体のアレ)

日々の覚書: InnoDB Monitorの仲間たち(InnoDBエンジン本体のアレ) で"CREATE TABLE innodb_monitor .."的なものを紹介しました。あれから2年。

というかよく調べてみたら1年前。

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.4 (2014-03-31, Milestone 14)


InnoDB: New global configuration parameters, innodb_status_output and innodb_status_output_locks, allow you to dynamically enable and disable the standard InnoDB Monitor and InnoDB Lock Monitor for periodic output. Previously, enabling and disabling these monitors for periodic output required creating and dropping specially named tables (innodb_monitor and innodb_lock_monitor). For additional information, see InnoDB Monitors.


というわけで今後は"SET GLOBAL innodb_status_output{,_locks} = 1"で有効化ですね。
CREATE TABLE innodb_lock_monitor (a int); ってしたらフツーにテーブルが出来ただけで何も吐いてくれなくて困ったのでメモを残しておきます。

MySQL :: MySQL 5.7 Reference Manual :: 14.15.2 Enabling InnoDB Monitors


あ、デフォルトでエラーログに吐くのは相変わらずですが、エラーログでなくdatadir/innodb_status.pidファイルに吐くように設定できるオプションがあります。pid部分は文字列じゃなくてプロセスIDが入るので、自分で名前や場所を変えることは今のところできなさげ。

MySQL :: MySQL 5.7 Reference Manual :: 14.12 InnoDB Startup Options and System Variables

2015年3月23日月曜日

MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか

MySQL 5.7.6-m16で導入されたgenerated columnについてメモ。タイトルでしゃらっと関数インデックスとか言っているけれど、俺はその機能を知らない。

MySQL :: MySQL 5.7 Reference Manual :: 13.1.14 CREATE TABLE Syntax

generated columnはあるカラムの値に関数を噛ませた結果をカラムとして実体化できたり、実体化せずに毎回計算できたりするカラム定義のことらしくて、


mysql57> CREATE TABLE t1 (num int, val varchar(32)) Engine = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql57> INSERT INTO t1 SET num = 1, val = 'one';
Query OK, 1 row affected (0.00 sec)

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

$ od -c t1.MYD
0000000 003  \0  \n 006  \0 376 001  \0  \0  \0 003   o   n   e  \0  \0
0000020  \0  \0  \0  \0
0000024

こんなテーブルに対して


mysql57> ALTER TABLE t1 ADD new_val varchar(96) AS (REPEAT(val, 3)) STORED;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql57> ALTER TABLE t1 ADD KEY (new_val);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

"STORED"キーワードは演算結果を実体化してデータとして格納するオプション。STOREDオプションで作成されたgenerated columnにはインデックスを作成することができる。

mysql57> SELECT * FROM t1;
+-----+------+-----------+
| num | val  | new_val   |
+-----+------+-----------+
|   1 | one  | oneoneone |
+-----+------+-----------+
1 row in set (0.00 sec)

$ od -c t1.MYD
0000000 003  \0 024  \0  \0 374 001  \0  \0  \0 003   o   n   e  \t   o
0000020   n   e   o   n   e   o   n   e
0000030

$ od -c t1.MYI
..
*
0004000  \0 024  \0 001  \t   o   n   e   o   n   e   o   n   e  \0  \0
0004020  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
*
..

一応、インデックスとしても実体化されているぽい。


mysql57> INSERT INTO t1 SET num = 2, val = 'two';
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t1 SET num = 3, val = 'three';
Query OK, 1 row affected (0.00 sec)

mysql57> explain SELECT * FROM t1 WHERE new_val LIKE 'one%';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | new_val       | new_val | 99      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql57> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                         |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `d1`.`t1`.`num` AS `num`,`d1`.`t1`.`val` AS `val`,`d1`.`t1`.`new_val` AS `new_val` from `d1`.`t1` where (`d1`.`t1`.`new_val` like 'one%') |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

極めてフツーにカラムを作って、極めてフツーに値を突っ込んで、極めてフツーに検索しているのと同じ感じ(当たり前)

ただし、generated columnからフツーの(?)カラムに対する逆関数は定義できないので、


mysql57> INSERT INTO t1 SET num = 4, new_val = 'fourfourfour';
ERROR 3105 (HY000): The value specified for generated column 'new_val' in table 't1' is not allowed.

突っ込めない。
おお、噂(?)の3000番台エラーだ。


で、このgenerated columnでやりたいのって

mysql57> create table json (id int auto_increment, col1 varchar(1000), primary key(id));
Query OK, 0 rows affected (0.05 sec)

mysql57> INSERT INTO json(id, col1) VALUES (1, '{"id":1,"Name":"Farmer grandmas","price":50000,"Conditions":["farms",15]}');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO json(id, col1) VALUES (2, '{"id":2,"Name":"Worker grandmas","price":300000,"Conditions":["factories",15]}');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO json(id, col1) VALUES (3, '{"id":3,"Name":"Miner grandmas","price":1000000,"Conditions":["mines",15]}');
Query OK, 1 row affected (0.02 sec)

mysql57> INSERT INTO json(id, col1) VALUES (4, '{"id":4,"Name":"Yoshiaki Yamasaki"}');
Query OK, 1 row affected (0.01 sec)

mysql57> ALTER TABLE json ADD price int unsigned AS (json_extract(col1, 'price')) STORED;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql57> ALTER TABLE json ADD KEY (price);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM json;
+----+--------------------------------------------------------------------------------+---------+
| id | col1                                                                           | price   |
+----+--------------------------------------------------------------------------------+---------+
|  1 | {"id":1,"Name":"Farmer grandmas","price":50000,"Conditions":["farms",15]}      |   50000 |
|  2 | {"id":2,"Name":"Worker grandmas","price":300000,"Conditions":["factories",15]} |  300000 |
|  3 | {"id":3,"Name":"Miner grandmas","price":1000000,"Conditions":["mines",15]}     | 1000000 |
|  4 | {"id":4,"Name":"Yoshiaki Yamasaki"}                                            |    NULL |
+----+--------------------------------------------------------------------------------+---------+
4 rows in set (0.00 sec)

mysql57> SELECT * FROM json WHERE price > 500000;
+----+----------------------------------------------------------------------------+---------+
| id | col1                                                                       | price   |
+----+----------------------------------------------------------------------------+---------+
|  3 | {"id":3,"Name":"Miner grandmas","price":1000000,"Conditions":["mines",15]} | 1000000 |
+----+----------------------------------------------------------------------------+---------+
1 row in set (0.00 sec)

こーゆーことで合ってますかね? :)


( ´-`).oO(サンプルのJSONは 日本語JSON UDFの一番詳しいスライド から拝借した


ところでADD price int unsigned AS (..) STORED KEYってやるとPRIMARYつけてなくてもPRIMARY KEYにしたがるんだけどたぶんバグだよな。。

【2015/03/23 19:44】
とりあえずばぐれぽしてみた。
MySQL Bugs: #76450: generated column with "KEY" makes PRIMARY KEY


【2015/03/24 16:34】
もともと、カラム定義に"KEY"キーワードを与えた場合はPRIMARY KEYを作る動作なんだと教えてもらった(´・ω・`)


KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.


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

2015年3月20日金曜日

MySQL 5.7でLOAD DATA INFILEに失敗する時に疑うこと(--secure-file-privの暗黙のデフォルトが少し変わった)

日々の覚書: MySQL 5.7.6は--secure-file-privを設定してないとWarningを吐くようになった のちょっと続き。

--secure-file-priv そのものについては↑の記事を読んでいただけると幸い。LOAD DATA INFILEやSELECT .. INTO OUTFILEの動きを制限する。


ドキュメントのsysvarのところに書いてある けれど、
* -DINSTALL_LAYOUT=STANDALONE, WIN でビルドされた場合は暗黙のデフォルトなし
* -DINSTALL_LAYOUT=DEB, RPM, SLES, SVR4 でビルドされた場合は"/var/lib/mysql-files"
* それ以外の設定の場合、"CMAKE_INSTALL_PREFIX(MySQLから見るとbasedir)の下のmysql-files"
が暗黙のデフォルトになる。

ソースはこのへん。
mysql-server/install_layout.cmake at 35e498b12e4e64e639cb8096d1a7362fd3272fa6 · mysql/mysql-server


INSTALL_LAYOUTの取りうる値はこのへんなので、FREEBSD, OSX, TARGZあたりがbasedir/mysql-filesになるのだろうか(ところで、Oracle公式のLinux Genericのバイナリー.tar.gzってSTANDALONEでビルドしてあるんだけど、他のプラットフォーム用のでTARGZ使ってるところもあるのかな)
mysql-server/install_layout.cmake at 35e498b12e4e64e639cb8096d1a7362fd3272fa6 · mysql/mysql-server



取り敢えず、バイナリーの.tar.gzは未設定なのと、Oracle Linux 6用のrpm bundleに入ってるやつはこの通りになるのを確認した。OSXとか誰か試してほしい。

あと、新しくsecure-file-privにディレクトリパスでなくて"NULL"を指定すると、いかなるディレクトリーに対してもFile_privがらみの操作を行えなくできるんだけど、


$ bin/mysqld_safe --no-defaults &

mysql> SELECT @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT LOAD_FILE('/etc/hosts');
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOAD_FILE('/etc/hosts')                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 172.17.0.3    0a8104f4882d
127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



$ bin/mysqld_safe --no-defaults --secure-file-priv=NULL &

mysql> SELECT @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT LOAD_FILE('/etc/hosts');
+-------------------------+
| LOAD_FILE('/etc/hosts') |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set (0.00 sec)

:(;゙゚'ω゚'): 区別がつかねぇ。。


【2015/03/20 12:44】
区別が付かない件はばぐれぽしました。

MySQL Bugs: #76401: Can't distinguish secure_file_priv = NULL and ""

2015年3月13日金曜日

MySQL 5.7.6からSSL周りのセットアップが扱いやすくなった

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.6 (2015-03-09, Milestone 16) から抜粋。

MySQL Server from Community Edition distributions now tries to deploy with SSL support enabled automatically if no SSL options are specified explicitly and it finds any of the ca.pem, server-cert.pem, and server-key.pem files in the data directory. In this case, clients can use a secure connection merely by specifying --ssl on the command line.

今までは--ssl指定するとついでに--ssl-ca, --ssl-cert, --ssl-keyを指定してやらないといけなかったのが、datadirからその辺のファイルが見つかれば指定しなくてもよしなにやってくれるらしい。

あと、サーバー側の--sslはデフォルトで有効状態に。

というわけで、 前回 mysqld --initializeがSSL証明書を作ろうとして失敗したと思っていたもの は、実はSSLを有効化しようとしていた(そして、証明書がdatadirにないので失敗していた)だけだった。


mysqld --initializeで作ったdatadirで起動すると、*.pemファイルはないので、

$ bin/mysqld --no-defaults --initialize-insecure
..

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

$ bin/mysqld_safe --no-defaults

mysql> SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.00 sec)

$ bin/mysql --no-defaults -uroot --ssl
ERROR 2026 (HY000): SSL connection error: SSL is required but the server doesn't support it

使えない。

mysql_install_dbで作ったdatadirには*.pemがあるので(ただし、mysql_install_db --insecureだと作られない)


$ ./bin/mysql_install_db --no-defaults --basedir=./ --datadir=./data
..

$ ll data/*.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:27 data/ca-key.pem
-rw-r--r-- 1 mysql mysql 1082 Mar 13 13:27 data/ca.pem
-rw-r--r-- 1 mysql mysql 1086 Mar 13 13:27 data/client-cert.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:27 data/client-key.pem
-rw------- 1 mysql mysql 1675 Mar 13 13:27 data/private_key.pem
-rw-r--r-- 1 mysql mysql  451 Mar 13 13:27 data/public_key.pem
-rw-r--r-- 1 mysql mysql 1086 Mar 13 13:27 data/server-cert.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:27 data/server-key.pem

$ bin/mysqld_safe --no-defaults

mysql> SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.00 sec)

$ bin/mysql --no-defaults -uroot --ssl
mysql> status
--------------
bin/mysql  Ver 14.14 Distrib 5.7.6-m16, for Linux (x86_64) using  EditLine wrapper

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.6-m16 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 53 sec

Threads: 1  Questions: 13  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.245
--------------

何の面倒なこともなしにSSLが有効化できる。これはいい。

mysql_install_dbからは mysql_ssl_rsa_setup というコマンドを呼んでいるだけなので、これを手で叩いてやれば他の方法でセットアップしてもOK。


$ bin/mysql_ssl_rsa_setup
OpenSSL 1.0.1e-fips 11 Feb 2013
Generating a 2048 bit RSA private key
...............+++
..........+++
writing new private key to 'ca-key.pem'
-----
writing RSA key
Signature ok
subject=/CN=MySQL_Server_5.7.6-m16_Auto_Generated_CA_Certificate
Getting Private key
Generating a 2048 bit RSA private key
...........................................................................+++
...........................................................................+++
writing new private key to 'server-key.pem'
-----
writing RSA key
Signature ok
subject=/CN=MySQL_Server_5.7.6-m16_Auto_Generated_Server_Certificate
Getting CA Private Key
Generating a 2048 bit RSA private key
.................+++
...................+++
writing new private key to 'client-key.pem'
-----
writing RSA key
Signature ok
subject=/CN=MySQL_Server_5.7.6-m16_Auto_Generated_Server_Certificate
Getting CA Private Key
server-cert.pem: OK
client-cert.pem: OK
Generating RSA private key, 2048 bit long modulus
............................................................................................................................+++
............+++
e is 65537 (0x10001)
writing RSA key

$ ll data/*.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:33 data/ca-key.pem
-rw-r--r-- 1 mysql mysql 1082 Mar 13 13:33 data/ca.pem
-rw-r--r-- 1 mysql mysql 1086 Mar 13 13:33 data/client-cert.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:33 data/client-key.pem
-rw------- 1 mysql mysql 1675 Mar 13 13:34 data/private_key.pem
-rw-r--r-- 1 mysql mysql  451 Mar 13 13:34 data/public_key.pem
-rw-r--r-- 1 mysql mysql 1086 Mar 13 13:33 data/server-cert.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:33 data/server-key.pem

SSLのセットアップがラクになっていい :)

2015年3月12日木曜日

MySQL 5.7.6でmysql_install_db(とmysqld --initialize)がSSL証明書のファイルを作っているぽいのであとで調べたい

MySQL 5.7.5のmysql_install_dbだと


$ bin/mysql_install_db --no-defaults --basedir=./ --datadir=./test_575 -v
2015-03-12 11:23:45 [NOTE]    Creating data directory ./test_575
2015-03-12 11:23:45 [NOTE]    Generating random password to /root/.mysql_secret...done.
2015-03-12 11:23:45 [NOTE]    Executing /opt/mysql/5.7.5/bin/mysqld --no-defaults --bootstrap --datadir=./test_575 --lc-messages-dir=./share --lc-messages=en_US --basedir=.
2015-03-12 11:23:46 [NOTE]    Creating system tables...done.
2015-03-12 11:23:46 [NOTE]    Filling system tables with data...done.
2015-03-12 11:23:47 [NOTE]    Filling help table with data...done.
2015-03-12 11:23:47 [NOTE]    Creating default user root@localhost
2015-03-12 11:23:47 [NOTE]    Creating default proxy root@localhost
2015-03-12 11:23:48 [NOTE]    Success!

.mysql_secretを作ってシステムテーブルを作ってroot@localhostを作るだけだったのが、5.7.6のmysql_install_dbでは


$ bin/mysql_install_db --no-defaults --basedir=./ --datadir=./test_mysql_install_db -v
2015-03-12 11:25:04 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2015-03-12 11:25:04 [NOTE]    Creating data directory ./test_mysql_install_db
2015-03-12 11:25:04 [NOTE]    Generating random password to /home/ttanaka/.mysql_secret...done.
2015-03-12 11:25:04 [NOTE]    Executing /usr/mysql/5.7.6/bin/mysqld --no-defaults --bootstrap --datadir=./test_mysql_install_db --lc-messages-dir=./share --lc-messages=en_US --basedir=.
2015-03-12 11:25:06 [NOTE]    Creating system tables...done.
2015-03-12 11:25:06 [NOTE]    Filling system tables with data...done.
2015-03-12 11:25:07 [NOTE]    Filling help table with data...done.
2015-03-12 11:25:07 [NOTE]    Creating default user root@localhost
2015-03-12 11:25:07 [NOTE]    Creating default proxy root@localhost
2015-03-12 11:25:07 [WARNING] The bootstrap log isn't empty:
2015-03-12 11:25:07 [WARNING] mysqld:
2015-03-12 11:25:07 [NOTE]    Generating SSL Certificates
OpenSSL 1.0.1e-fips 11 Feb 2013
server-cert.pem: OK
client-cert.pem: OK

$ ll test_mysql_install_db
合計 110640
-rw------- 1 mysql mysql     1679  3月 12 11:25 2015 ca-key.pem
-rw-r--r-- 1 mysql mysql     1082  3月 12 11:25 2015 ca.pem
-rw-r--r-- 1 mysql mysql     1086  3月 12 11:25 2015 client-cert.pem
-rw------- 1 mysql mysql     1679  3月 12 11:25 2015 client-key.pem
-rw-r----- 1 mysql mysql 50331648  3月 12 11:25 2015 ib_logfile0
-rw-r----- 1 mysql mysql 50331648  3月 12 11:25 2015 ib_logfile1
-rw-r----- 1 mysql mysql 12582912  3月 12 11:25 2015 ibdata1
drwxr-x--- 2 mysql mysql     4096  3月 12 11:25 2015 mysql
drwxr-x--- 2 mysql mysql     4096  3月 12 11:25 2015 performance_schema
-rw------- 1 mysql mysql     1675  3月 12 11:25 2015 private_key.pem
-rw-r--r-- 1 mysql mysql      451  3月 12 11:25 2015 public_key.pem
-rw-r--r-- 1 mysql mysql     1086  3月 12 11:25 2015 server-cert.pem
-rw------- 1 mysql mysql     1679  3月 12 11:25 2015 server-key.pem

そのあとにサーバー用, クライアント用のSSL証明書を作っているぽい。
( ´-`).oO(mysqld: で止まってるワーニングメッセージがあるんだけど、これ MeCabのやつ と一緒でOpenSSLのライブラリーから戻ってきた何かをうまくハンドルできてないんじゃないか。。

そして5.7.6のmysqld --initialize。


$ bin/mysqld --no-defaults --initialize-insecure --datadir=./test_mysqld --log-error-verbosity=3
2015-03-12T02:25:54.901505Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-12T02:25:54.901602Z 0 [Note] Ignoring --secure-file-priv value as server is running with --initialize(-insecure) or --bootstrap.
2015-03-12T02:25:54.901846Z 0 [Note] bin/mysqld (mysqld 5.7.6-m16) starting as process 23958 ...
2015-03-12T02:25:54.938092Z 0 [Note] Creating the data directory /usr/mysql/5.7.6/test_mysqld/
..
2015-03-12T02:25:56.202015Z 0 [Note] Plugin 'FEDERATED' is disabled.
2015-03-12T02:25:56.202631Z 0 [Warning] Failed to setup SSL
2015-03-12T02:25:56.202643Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2015-03-12T02:25:56.203497Z 1 [Note] Creating the system database
2015-03-12T02:25:56.203521Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2015-03-12T02:25:56.204099Z 1 [Note] Creating the system tables
2015-03-12T02:25:57.234370Z 1 [Note] Filling in the system tables, part 1
2015-03-12T02:25:57.235332Z 1 [Note] Filling in the system tables, part 2
2015-03-12T02:25:57.235347Z 1 [Note] Filling in the mysql.help table
2015-03-12T02:25:57.969635Z 1 [Note] Bootstrapping complete
2015-03-12T02:25:57.969924Z 0 [Note] Giving 0 client threads a chance to die gracefully
2015-03-12T02:25:57.969945Z 0 [Note] Shutting down slave threads
2015-03-12T02:25:57.969955Z 0 [Note] Forcefully disconnecting 0 remaining clients
2015-03-12T02:25:57.970165Z 0 [Note] Binlog end
2015-03-12T02:25:57.970630Z 0 [Note] InnoDB: FTS optimize thread exiting.
2015-03-12T02:25:57.970950Z 0 [Note] InnoDB: Starting shutdown...
2015-03-12T02:25:59.100867Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2321861
2015-03-12T02:25:59.104330Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

$ ll test_mysqld/
合計 110604
-rw-r----- 1 mysql mysql 50331648  3月 12 11:25 2015 ib_logfile0
-rw-r----- 1 mysql mysql 50331648  3月 12 11:25 2015 ib_logfile1
-rw-r----- 1 mysql mysql 12582912  3月 12 11:25 2015 ibdata1
drwxr-x--- 2 mysql mysql     4096  3月 12 11:25 2015 mysql
drwxr-x--- 2 mysql mysql     4096  3月 12 11:25 2015 performance_schema

同じくなんかSSL証明書を作ろうとしてるけど失敗してる。全く同じ動作ってわけではないぽい。ちょっと調べようか。


【2015/03/13 13:36】
調べてみたところ、作ろうとしているのではなく、使おうとして(証明書がないので)失敗しているのであった。
日々の覚書: MySQL 5.7.6からSSL周りのセットアップが扱いやすくなった

2015年3月10日火曜日

MySQL 5.7.6のInnoDB日本語全文検索 ngram

日々の覚書: プレビュー "MySQL 5.7.6のInnoDB日本語全文検索 MeCab Plugin" に引き続き、今度はngramなトークナイザー。ドキュメントは ここ

MeCab Pluginと違って特にINSTALL PLUGINとかせずに即使える。


mysql> ALTER TABLE articles ADD FULLTEXT KEY (title, content) WITH PARSER ngram;
Query OK, 0 rows affected (6 hours 25 min 20.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

( д ) ゚ ゚ 6時間半!? しょうがないとはいえ、MeCabのときは1時間半でお釣りが来てたのにと思うとちょっとしょんぼり。

時間もさることながら、このやり方(データをインポートしてからALTER TABLEでインデックス追加)だと容量がかなり持っていかれる。


[root@v157-7-235-254 mysql]# while true ; do
> date ; df -h
> echo ""
> sleep 60
> done
Tue Mar 10 10:13:41 JST 2015
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       97G   16G   76G  18% /
tmpfs                 499M     0  499M   0% /dev/shm
/dev/vda1             485M   32M  428M   7% /boot
..
Tue Mar 10 16:12:45 JST 2015
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       97G   74G   18G  81% /
tmpfs                 499M     0  499M   0% /dev/shm
/dev/vda1             485M   32M  428M   7% /boot
..
Tue Mar 10 16:44:51 JST 2015
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       97G   26G   66G  28% /
tmpfs                 499M     0  499M   0% /dev/shm
/dev/vda1             485M   32M  428M   7% /boot


# lsof -p `pidof mysqld`
COMMAND  PID  USER   FD   TYPE             DEVICE    SIZE/OFF    NODE NAME
mysqld  3049 mysql  cwd    DIR              253,0        4096  919114 /usr/local/mysql/data
mysqld  3049 mysql  rtd    DIR              253,0        4096       2 /
mysqld  3049 mysql  txt    REG              253,0   193624979  919090 /usr/local/mysql/bin/mysqld
mysqld  3049 mysql  mem    REG              253,0        5624  914553 /lib64/libaio.so.1.0.1
mysqld  3049 mysql  DEL    REG              253,0              914859 /lib64/libfreebl3.so
mysqld  3049 mysql  mem    REG              253,0    49199027 2231548 /usr/local/mysql/lib/mecab/dic/ipadic_utf-8/sys.dic
mysqld  3049 mysql  mem    REG              253,0     3463716 2231541 /usr/local/mysql/lib/mecab/dic/ipadic_utf-8/matrix.bin
mysqld  3049 mysql  mem    REG              253,0      262496 2231546 /usr/local/mysql/lib/mecab/dic/ipadic_utf-8/char.bin
mysqld  3049 mysql  mem    REG              253,0     3988451 2231491 /usr/local/mysql/lib/plugin/libpluginmecab.so
mysqld  3049 mysql  mem    REG              253,0       65928  914006 /lib64/libnss_files-2.12.so
mysqld  3049 mysql  DEL    REG              253,0              913936 /lib64/libc-2.12.so
mysqld  3049 mysql  DEL    REG              253,0              915115 /lib64/libgcc_s-4.4.7-20120601.so.1.#prelink#.JjQM5q
mysqld  3049 mysql  DEL    REG              253,0              913990 /lib64/libm-2.12.so
mysqld  3049 mysql  DEL    REG              253,0              263139 /usr/lib64/libstdc++.so.6.0.13.#prelink#.htv5XJ
mysqld  3049 mysql  DEL    REG              253,0              914081 /lib64/librt-2.12.so
mysqld  3049 mysql  DEL    REG              253,0              913976 /lib64/libdl-2.12.so
mysqld  3049 mysql  DEL    REG              253,0              913940 /lib64/libcrypt-2.12.so.#prelink#.3QGWZ8
mysqld  3049 mysql  DEL    REG              253,0              913960 /lib64/libpthread-2.12.so.#prelink#.xdy3QH
mysqld  3049 mysql  DEL    REG              253,0              913930 /lib64/ld-2.12.so
mysqld  3049 mysql  mem    REG              253,0        5690 2231545 /usr/local/mysql/lib/mecab/dic/ipadic_utf-8/unk.dic
mysqld  3049 mysql    0r   CHR                1,3         0t0    3788 /dev/null
mysqld  3049 mysql    1w   REG              253,0       15868  919127 /usr/local/mysql/data/error.log
mysqld  3049 mysql    2w   REG              253,0       15868  919127 /usr/local/mysql/data/error.log
mysqld  3049 mysql    3uW  REG              253,0    79691776  919158 /usr/local/mysql/data/ibdata1
..
mysqld  3049 mysql   45uW  REG              253,0 11286872064 1305615 /usr/local/mysql/data/wikipedia/articles.ibd
mysqld  3049 mysql   46uW  REG              253,0       98304 1305604 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_1.ibd
mysqld  3049 mysql   47uW  REG              253,0       98304 1305607 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_2.ibd
mysqld  3049 mysql   48uW  REG              253,0       98304 1305611 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_3.ibd
mysqld  3049 mysql   49uW  REG              253,0       98304 1305613 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_4.ibd
mysqld  3049 mysql   50uW  REG              253,0       98304 1305614 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_5.ibd
mysqld  3049 mysql   51uW  REG              253,0       98304 1305616 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_6.ibd
mysqld  3049 mysql   52uW  REG              253,0       98304 1305603 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_DELETED.ibd
mysqld  3049 mysql   53uW  REG              253,0       98304 1305605 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_DELETED_CACHE.ibd
mysqld  3049 mysql   54uW  REG              253,0       98304 1305606 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_BEING_DELETED.ibd
mysqld  3049 mysql   55uW  REG              253,0       98304 1305610 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_BEING_DELETED_CACHE.ibd
mysqld  3049 mysql   56uW  REG              253,0       98304 1305612 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_CONFIG.ibd
mysqld  3049 mysql   58u   REG              253,0 10842275840 1046745 /data/tmp/ibDDeVbj (deleted)
mysqld  3049 mysql   59u   REG              253,0   696254464 1046746 /data/tmp/ibaNLTUS (deleted)
mysqld  3049 mysql   60u   REG              253,0  1790967808 1046747 /data/tmp/ibNm7Vmi (deleted)
mysqld  3049 mysql   61u   REG              253,0  1623195648 1046748 /data/tmp/ibl17OJP (deleted)
mysqld  3049 mysql   62u   REG              253,0  8006926336 1046749 /data/tmp/ibBoSvqQ (deleted)
mysqld  3049 mysql   63u   REG              253,0  8006926336 1046750 /data/tmp/ibudnROb (deleted)
mysqld  3049 mysql   64u   REG              253,0 10904141824 1046751 /data/tmp/ibNbFRxL (deleted)
mysqld  3049 mysql   65u   REG              253,0   698351616 1046752 /data/tmp/ibYchSgl (deleted)
mysqld  3049 mysql   66u   REG              253,0  1802502144 1046753 /data/tmp/ibYYJGHW (deleted)
mysqld  3049 mysql   67u   REG              253,0  1631584256 1046754 /data/tmp/ibQOsAMA (deleted)
mysqld  3049 mysql   68u   REG              253,0  8050966528 1046755 /data/tmp/ibiSAO6i (deleted)
mysqld  3049 mysql   69u   REG              253,0  8050966528 1046756 /data/tmp/ibGrGYaE (deleted)
mysqld  3049 mysql   70u   REG              253,0   361758720 1046757 /data/tmp/ibQvfudp (deleted)
mysqld  3049 mysql   71u   REG              253,0   359661568 1046758 /data/tmp/ibN0BTAJ (deleted)

最終的に元に戻るとはいえ、tmpdirに数GB単位のテンポラリーファイルをいくつもつくられてしかもそれが途中で解放されないのは結構つらい。。


まあそこはそれとして


mysql> CREATE TABLE t1 (val varchar(32));
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO t1 VALUES ('にっこにっこにー');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM t1;
+--------------------------+
| val                      |
+--------------------------+
| にっこにっこにー         |
+--------------------------+
1 row in set (0.00 sec)

こんなサンプルがあるじゃろ?


mysql> SELECT @@ngram_token_size;
+--------------------+
| @@ngram_token_size |
+--------------------+
|                  2 |
+--------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 ADD FULLTEXT KEY idx_2gram(val) WITH PARSER ngram;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET GLOBAL innodb_ft_aux_table = 'd1/t1';
Query OK, 0 rows affected (0.00 sec)

mysql> OPTIMIZE TABLE t1;
+-------+----------+----------+-------------------------------------------------------------------+
| Table | Op       | Msg_type | Msg_text                                                          |
+-------+----------+----------+-------------------------------------------------------------------+
| d1.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| d1.t1 | optimize | status   | OK                                                                |
+-------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.36 sec)

mysql> SELECT * FROM information_schema.innodb_ft_index_cache ORDER BY position;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| にっ   |            2 |           2 |         1 |      2 |        0 |
| っこ   |            2 |           2 |         1 |      2 |        3 |
| こに   |            2 |           2 |         1 |      2 |        6 |
| っこ   |            2 |           2 |         1 |      2 |        9 |
| にっ   |            2 |           2 |         1 |      2 |        9 |
| こに   |            2 |           2 |         1 |      2 |        9 |
| にー   |            2 |           2 |         1 |      2 |       18 |
+--------+--------------+-------------+-----------+--------+----------+
7 rows in set (0.02 sec)

mysql> SELECT * FROM information_schema.innodb_ft_config;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 0     |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+
4 rows in set (0.03 sec)

ngram_token_size変数でトークンのサイズを制御できる。この変数はオンライン変更できないので、my.cnfに設定してからmysqld再起動。


mysql> SET GLOBAL innodb_ft_aux_table = 'd1/t1';
Query OK, 0 rows affected (0.00 sec)

mysql> OPTIMIZE TABLE t1;
+-------+----------+----------+-------------------------------------------------------------------+
| Table | Op       | Msg_type | Msg_text                                                          |
+-------+----------+----------+-------------------------------------------------------------------+
| d1.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| d1.t1 | optimize | status   | OK                                                                |
+-------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.31 sec)

mysql> SELECT * FROM information_schema.innodb_ft_index_cache ORDER BY position;
+-----------+--------------+-------------+-----------+--------+----------+
| WORD      | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-----------+--------------+-------------+-----------+--------+----------+
| にっこ    |            2 |           2 |         1 |      2 |        0 |
| っこに    |            2 |           2 |         1 |      2 |        3 |
| こにっ    |            2 |           2 |         1 |      2 |        6 |
| にっこ    |            2 |           2 |         1 |      2 |        9 |
| っこに    |            2 |           2 |         1 |      2 |        9 |
| こにー    |            2 |           2 |         1 |      2 |       15 |
+-----------+--------------+-------------+-----------+--------+----------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.innodb_ft_config;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 0     |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+
4 rows in set (0.01 sec)

あ、あれ? 再作成しなくても切りなおされるの?;