2014年2月28日金曜日

MySQLで「任意のテーブルのDROPは許可したい」けれど、「スキーマはDROPさせたくない」

MySQL分補充エントリー。

MyNA 15987のメール でそんな話題があっていろいろ考えてネタにしようと思ってたんですが、気が付けばこれ去年の7月じゃないですか。。

やりたいこと。

mysql56> SHOW GRANTS;
+------------------------------------------------+
| Grants for user1@localhost                     |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost'      |
| GRANT DROP ON `user1`.* TO 'user1'@'localhost' |
+------------------------------------------------+
2 rows in set (0.00 sec)
なユーザーに

mysql56> DROP TABLE user1.t1;
Query OK, 0 rows affected (0.07 sec)

は(マニュアルにあるとおり)成功させたいけれど、

mysql56> DROP DATABASE user1;
Query OK, 0 rows affected (0.15 sec)

は(フツーやると成功するところを)失敗させたい。


思い付いたやり方は2つ。

*** sql/sql_db.cc.orig  2014-01-15 00:38:00.000000000 +0900
--- sql/sql_db.cc       2014-02-28 17:13:19.383810416 +0900
***************
*** 44,49 ****
--- 44,51 ----
  #endif
  #include "debug_sync.h"

+ #include "sql_parse.h"    // check_global_access
+
  #define MAX_DROP_TABLE_Q_LEN      1024

  const char *del_exts[]= {".frm", ".BAK", ".TMD", ".opt", ".OLD", NullS};
***************
*** 772,777 ****
--- 774,782 ----

  bool mysql_rm_db(THD *thd,char *db,bool if_exists, bool silent)
  {
+   if (check_global_access(thd, SUPER_ACL))
+     DBUG_RETURN(0);
+
    ulong deleted_tables= 0;
    bool error= true;
    char        path[2 * FN_REFLEN + 16];

安定の、いつものパターン。

mysql56> DROP TABLE user1.t1;
Query OK, 0 rows affected (0.02 sec)

mysql56> DROP DATABASE user1;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

書き足したのもシンプルだし、俺にしては珍しく(!)ちゃんとmtrやったので、どこかで使う機会があったら使ってもいいなくらいの出来。というか必要があったら使うわこのパッチ。

あともう1つ、ソースコードをいじることなく出来る汚い手としては、

$ pwd
/usr/mysql/5.6.16/data/user1

$ touch drop_database_prevention

$ ll
合計 112
-rw-rw---- 1 yoku0825 yoku0825    61  2月 28 17:36 2014 db.opt
-rw-rw-r-- 1 yoku0825 yoku0825     0  2月 28 17:43 2014 drop_database_prevention
-rw-rw---- 1 yoku0825 yoku0825  8558  2月 28 17:42 2014 t1.frm
-rw-rw---- 1 yoku0825 yoku0825 98304  2月 28 17:42 2014 t1.ibd

mysql56> DROP TABLE user1.t1;
Query OK, 0 rows affected (0.02 sec)

mysql56> DROP DATABASE user1;
ERROR 1010 (HY000): Error dropping database (can't rmdir './user1/', errno: 17)

$ ll
合計 0
-rw-rw-r-- 1 yoku0825 yoku0825     0  2月 28 17:43 2014 drop_database_prevention

DROP DATABASEがMySQLのテーブルに関するファイルを全部消したあとにrmdirを呼んでいるのを逆手に取った嫌がらせ 小ネタでした。

2014年2月14日金曜日

デブサミ2014のコミュニティーLTしてきました

ずさー。





日本MySQLユーザ会のひととしてLTさせてもらいました。
みんな、自分のコミュニティーにまつわるStory(発祥からの経緯とか、最近の活動とか)とか話してたのに、俺だけ自分のStory語っててやっぱりすべりました。どうしてこうなった。

すべり駆動コミュニティーでもいいじゃないみたいな話になってますが、出会い厨でコミュニティー参加しても楽しいもんですよ、という話をしたかったです本当は。

Chiba.pmにはすたじおさんに会いたくて行ったし
Groongaを囲む夕べ には斯波さんに会いたくてGroonga使ってないのに行ったし
MyNA会 2013年3月 には SH2さん に会いたくて(強引にネタを作って)行ったし
JAWS-UG(第何回か忘れた…) にはSH2さん(again)とこんまめさんに会いたくて行ったし(でもこんまめさんのセッション聞かずに帰った…orz)
MySQL Cluster Casual Talks は「企画したら誰か会えるかなー」と思っていたらみんな来てくれて嬉しかったし
OracleのMySQL Tech Tour Tokyoはやまさきさんに会いに行ってるようなもんだし

不純な動機ではじめても、結構楽しいものですよ :)
Have fun!

2014年2月13日木曜日

MySQLでUDFを含んだクエリーをクエリーキャッシュに載せるライフハック

kazeburoさん のツイートを見てふとやってみたくなった。
反省はしていない。



取りあえずmroonga_snippetで試してみようと思って、mroonga 2.07のリリースノート をまるっとテストケースにする。

mysql56> CREATE TABLE snippet_test (id int NOT NULL, text text, PRIMARY KEY(id), FULLTEXT KEY(text)) Engine= mroonga;
Query OK, 0 rows affected (0.10 sec)

mysql56> INSERT INTO snippet_test (id, text) VALUES (1, 'An open-source fulltext search engine and column store.');
Query OK, 1 row affected (0.01 sec)

mysql56> INSERT INTO snippet_test (id, text) VALUES (2, 'An open-source storage engine for fast fulltext search with MySQL.');
Query OK, 1 row affected (0.01 sec)

mysql56> INSERT INTO snippet_test (id, text) VALUES (3, 'Tritonn is a patched version of MySQL that supports better fulltext search function with Senna.');
Query OK, 1 row affected (0.00 sec)

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

mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql56> SELECT * FROM snippet_test;
+----+-------------------------------------------------------------------------------------------------+
| id | text                                                                                            |
+----+-------------------------------------------------------------------------------------------------+
|  1 | An open-source fulltext search engine and column store.                                         |
|  2 | An open-source storage engine for fast fulltext search with MySQL.                              |
|  3 | Tritonn is a patched version of MySQL that supports better fulltext search function with Senna. |
+----+-------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql56> SELECT sql_cache * FROM snippet_test;
+----+-------------------------------------------------------------------------------------------------+
| id | text                                                                                            |
+----+-------------------------------------------------------------------------------------------------+
|  1 | An open-source fulltext search engine and column store.                                         |
|  2 | An open-source storage engine for fast fulltext search with MySQL.                              |
|  3 | Tritonn is a patched version of MySQL that supports better fulltext search function with Senna. |
+----+-------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

query_cache_type= 2なので、sql_cacheオプションをつけたときだけクエリーキャッシュに入る。

mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql56> SELECT sql_cache id, mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '...
',
    ->                                        'fulltext', '', '',
    ->                                        'MySQL', '', '',
    ->                                        'search', '', '')
    -> FROM snippet_test WHERE MATCH(text) AGAINST('fulltext');
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '...
',
                                       'fulltext', '', '',
                                       'MySQL', '', '',
                       |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | ...fulltext...
... search ...
                                                                                                                                                                             |
|  2 | ...fulltext...
... search ...
                                                                                                                                                                             |
|  3 | ...f MySQL ...
...fulltext...
                                                                                                                                                                             |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

おー、ホントだ、載らない。

昔取った杵柄 で、何とかすれば何とでもなりそうな気配がしたので、キューピー3分ハッキングといきます。

まずはsql/sql_cache.cc の中のどこかで、UDFを使ったクエリーはキャッシュしない、と判定されていいるはずなのでそこを探す。たぶん、is_cacheable とかいう名前の何かがあったはず。
mysql-5.6.16/sql/sql_cache.cc
..
3707 TABLE_COUNTER_TYPE
3708 Query_cache::is_cacheable(THD *thd, size_t query_len, const char *query,
3709                           LEX *lex,
3710                           TABLE_LIST *tables_used, uint8 *tables_type)
3711 {
3712   TABLE_COUNTER_TYPE table_count;
3713   DBUG_ENTER("Query_cache::is_cacheable");
3714
3715   if (query_cache_is_cacheable_query(lex) &&
3716       (thd->variables.query_cache_type == 1 ||
3717        (thd->variables.query_cache_type == 2 && (lex->select_lex.options &
3718                                                  OPTION_TO_QUERY_CACHE))))
3719   {
..
ここかなー。
thd->variables.query_cache_typeはSET SESSION query_ache_type= ..で指定するやつだし、lex->select_lex.optionsは"sql_cache", "sql_no_cache"とかを持つところだから違うし、明らかにquery_cache_is_cacheable_queryが怪しい。
mysql-5.6.16/sql/sql_cache.h
..
562 #define query_cache_is_cacheable_query(L) \
563   (((L)->sql_command == SQLCOM_SELECT) && (L)->safe_to_cache_query && \
564    !(L)->describe)
..
lex->safe_to_cache_queryかな?
mysql-5.6.16/sql/item_create.cc
..
2733 Item*
2734 Create_udf_func::create(THD *thd, udf_func *udf, List<Item> *item_list)
2735 {
..
2826   thd->lex->safe_to_cache_query= 0;
2827   DBUG_RETURN(func);
2828 }
..
ここっぽいので取り敢えず= 1に書き換えてmakeする。


mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql56> SELECT sql_cache id, mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '...
',
    ->                                        'fulltext', '', '',
    ->                                        'MySQL', '', '',
    ->                                        'search', '', '')
    -> FROM snippet_test WHERE MATCH(text) AGAINST('fulltext');
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '...
',
                                       'fulltext', '', '',
                                       'MySQL', '', '',
                       |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | ...fulltext...
... search ...
                                                                                                                                                                             |
|  2 | ...fulltext...
... search ...
                                                                                                                                                                             |
|  3 | ...f MySQL ...
...fulltext...
                                                                                                                                                                             |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

載った。満足。