読者です 読者をやめる 読者になる 読者になる

なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLでPerformance_Schemaから過去に実行したSQLを把握する

MySQL

MySQLに、OracleのSTATSPACK的なもの、ないの?というアレ。

Oracle文化が根強いSIerと一緒にMySQL案件に関わると質問される5つのこと - なからなLifeで触れた、アレのお話です。


「STATSPACK」に例えられる要件にもレベルがあって、私が質問を受ける文脈としては、「遅かったSQLを、誰が流したか、その時の実行計画も合わせて知りたい」というケースが多いので、STATSPACK level 6以上、という感じでしょうか。


「アドバイスも」なんて話もついてくるので、Diagnostics Packの「SQLチューニング・アドバイザ」もイメージした要求なのかもしれません。


いずれにせよ、MySQLのCommunity版を採用しておきながら、ムシのいい話です。


Enterprise版を採用すれば解決するのかどうか、私もまだ知らないんですけどね。



とにかく無償の範囲でできることを調べたいと思います。


今回も、いつものLinux版 MySQL5.6.31です。

過去に実行したSQLを参照する

Perfomance_Schemaで、setup_consumersから

  • events_statements_history (Thread毎に10件)
  • events_statements_history_long (Thread毎に10000件)

をENABLED=YESにすると、過去に実行されたSQLの中身が確認できるようになります。
(デフォルトではevents_statements_currentだけが有効です)


以下のような定義で、「SQL_TEXT列」に、実行したSQLが格納されています。

mysql> show create table events_statements_history_long\G
*************************** 1. row ***************************
       Table: events_statements_history_long
Create Table: CREATE TABLE `events_statements_history_long` (
  `THREAD_ID` bigint(20) unsigned NOT NULL,
  `EVENT_ID` bigint(20) unsigned NOT NULL,
  `END_EVENT_ID` bigint(20) unsigned DEFAULT NULL,
  `EVENT_NAME` varchar(128) NOT NULL,
  `SOURCE` varchar(64) DEFAULT NULL,
  `TIMER_START` bigint(20) unsigned DEFAULT NULL,
  `TIMER_END` bigint(20) unsigned DEFAULT NULL,
  `TIMER_WAIT` bigint(20) unsigned DEFAULT NULL,
  `LOCK_TIME` bigint(20) unsigned NOT NULL,
  `SQL_TEXT` longtext,
  `DIGEST` varchar(32) DEFAULT NULL,
  `DIGEST_TEXT` longtext,
  `CURRENT_SCHEMA` varchar(64) DEFAULT NULL,
  `OBJECT_TYPE` varchar(64) DEFAULT NULL,
  `OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
  `OBJECT_NAME` varchar(64) DEFAULT NULL,
  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned DEFAULT NULL,
  `MYSQL_ERRNO` int(11) DEFAULT NULL,
  `RETURNED_SQLSTATE` varchar(5) DEFAULT NULL,
  `MESSAGE_TEXT` varchar(128) DEFAULT NULL,
  `ERRORS` bigint(20) unsigned NOT NULL,
  `WARNINGS` bigint(20) unsigned NOT NULL,
  `ROWS_AFFECTED` bigint(20) unsigned NOT NULL,
  `ROWS_SENT` bigint(20) unsigned NOT NULL,
  `ROWS_EXAMINED` bigint(20) unsigned NOT NULL,
  `CREATED_TMP_DISK_TABLES` bigint(20) unsigned NOT NULL,
  `CREATED_TMP_TABLES` bigint(20) unsigned NOT NULL,
  `SELECT_FULL_JOIN` bigint(20) unsigned NOT NULL,
  `SELECT_FULL_RANGE_JOIN` bigint(20) unsigned NOT NULL,
  `SELECT_RANGE` bigint(20) unsigned NOT NULL,
  `SELECT_RANGE_CHECK` bigint(20) unsigned NOT NULL,
  `SELECT_SCAN` bigint(20) unsigned NOT NULL,
  `SORT_MERGE_PASSES` bigint(20) unsigned NOT NULL,
  `SORT_RANGE` bigint(20) unsigned NOT NULL,
  `SORT_ROWS` bigint(20) unsigned NOT NULL,
  `SORT_SCAN` bigint(20) unsigned NOT NULL,
  `NO_INDEX_USED` bigint(20) unsigned NOT NULL,
  `NO_GOOD_INDEX_USED` bigint(20) unsigned NOT NULL,
  `NESTING_EVENT_ID` bigint(20) unsigned DEFAULT NULL,
  `NESTING_EVENT_TYPE` enum('STATEMENT','STAGE','WAIT') DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


公式ドキュメントでいうと、このへんの話です。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 22.9.6 パフォーマンススキーマステートメントイベントテーブル


なお、完全なSQL文を見る場合は、「SQL_TEXT」列を見ます。
「DIGEST_TEXT」列もSQL文が見えますが、Where条件に指定した値などは?で表示されます。

実行タイミングの確認とsys.format_time関数

TIMER_START、TIMER_ENDという列があるのですが、ここに出てくるのは、「サーバー起動からの経過時間」、しかもデフォルトで「ピコ秒(1兆分の1秒) 」です。


sysスキーマをセットアップしておくと、format_timeというストアドファンクションがありますので、これを使うと見やすい単位で返してくれます。
どんな単位で値を返してくるかは、実際にformat_time関数に渡されるピコ秒の値次第で変化します。


これはソースを見るとよく分かります。

mysql> show create function sys.format_time\G
*************************** 1. row ***************************
            Function: format_time
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`dbauser`@`%` FUNCTION `format_time`(
        
        
        picoseconds TEXT
    ) RETURNS text CHARSET utf8
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT '\n             Description\n             -----------\n\n             Takes a raw picoseconds value, and converts it to a human readable form.\n             \n             Picoseconds are the precision that all latency values are printed in \n             within Performance Schema, however are not user friendly when wanting\n             to scan output from the command line.\n\n             Parameters\n             -----------\n\n             picoseconds (TEXT): \n               The raw picoseconds value to convert.\n\n             Returns\n             -----------\n\n             TEXT\n\n             Example\n             -----------\n\n             mysql> select format_time(342342342342345);\n             +------------------------------+\n             | format_time(342342342342345) |\n             +------------------------------+\n             | 00:05:42                     |\n             +------------------------------+\n             1 row in set (0.00 sec)\n\n             mysql> select format_time(342342342);\n             +------------------------+\n             | format_time(342342342) |\n             +------------------------+\n             | 342.34 us              |\n             +------------------------+\n             1 row in set (0.00 sec)\n\n             mysql> select format_time(34234);\n              +--------------------+\n             | format_time(34234) |\n             +--------------------+\n             | 34.23 ns           |\n             +--------------------+\n             1 row in set (0.00 sec)\n            '
BEGIN
  IF picoseconds IS NULL THEN RETURN NULL;
  ELSEIF picoseconds >= 604800000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 604800000000000000, 2), ' w');
  ELSEIF picoseconds >= 86400000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 86400000000000000, 2), ' d');
  ELSEIF picoseconds >= 3600000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 3600000000000000, 2), ' h');
  ELSEIF picoseconds >= 60000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 60000000000000, 2), ' m');
  ELSEIF picoseconds >= 1000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000000, 2), ' s');
  ELSEIF picoseconds >= 1000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000, 2), ' ms');
  ELSEIF picoseconds >= 1000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000, 2), ' us');
  ELSEIF picoseconds >= 1000 THEN RETURN CONCAT(ROUND(picoseconds / 1000, 2), ' ns');
  ELSE RETURN CONCAT(picoseconds, ' ps');
  END IF;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.06 sec)

サーバ起動からの経過時間を「時刻」に直したい

残念ながら、ダイレクトに時刻に直す方法が見当たりませんでした。


ということで、現在日時時点でのサーバ起動からの経過時間を元に、その差異から、そのSQLの実行時刻を算出することにします。

現在の日時

現在の日時はsysdate関数やnow関数を使えばわかります。
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_sysdate
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_now
now関数とsysdate関数の違いについて触れると本題からそれるので、ドキュメントを参照してください。

サーバ起動からの経過時間

現時点の「サーバ起動からの経過時間」は、サーバーステータス変数「Uptime」に保持していますので、「SHOW GLOBAL STATUS LIKE 'Uptime'」で確認できます。
単位は「秒」です。

サーバーが作動している秒数。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.6 サーバーステータス変数

なお、「SHOW GLOBAL STATUS」は、「information_schema.GLOBAL_STATUS」から取得できます。


SQLの中で計算させたいので、SHOWコマンドよりもSELECTで引いてこれる方が都合がいいですね。

mysql> show create table GLOBAL_STATUS\G
*************************** 1. row ***************************
       Table: GLOBAL_STATUS
Create Table: CREATE TEMPORARY TABLE `GLOBAL_STATUS` (
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SELECT * FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime'; SHOW GLOBAL STATUS LIKE 'Uptime';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| UPTIME        | 513            |
+---------------+----------------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 513   |
+---------------+-------+
1 row in set (0.00 sec)

VARIABLE_NAMEが大文字統一だったり、小文字混じりだったり。
そして、どっちでも検索にヒットしてしまう。


うーん、この。。。



SQL実行時の時刻を導出する

材料は揃いました。
SQLの実行時刻[[年月日時分秒]
= 現在時刻[年月日時分秒] - (現時点のサーバ起動からの経過時間[秒] - SQL実行開始時点のサーバ起動からの経過時間[ピコ秒])


が、単位を揃えなくてはいけません。


sys.format_time関数は、戻り値の単位が安定しないので使えません。素直に1兆分の1(/10^12)して秒に換算しましょう。


現時点のサーバ起動からの経過時間[秒] - SQL実行開始時点のサーバ起動からの経過時間[秒]は、現在時点の何秒前にSQLを実行したか、になります。



SQLの実行時刻[[年月日時分秒]
= 現在時刻[年月日時分秒] - (現時点のサーバ起動からの経過時間[秒] - SQL実行開始時点のサーバ起動からの経過時間[ピコ秒]/10^12)
= 現在時刻[年月日時分秒] - 現在時点の何秒前にSQLを実行したか


時刻に対する加算減算は、DATE_ADD関数が使えるようです。

  • DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

これらの関数は日付演算を実行します。date 引数は、開始日付値または開始日付時間値を指定します。expr は、開始日付から加算または減算される間隔値を指定する式です。expr は文字列であり、負の間隔の場合は 「-」 で始めることができます。unit は、式を解釈する際の単位を示すキーワードです。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

これに倣って
SQLの実行時刻[[年月日時分秒]
= 現在時刻[年月日時分秒] - (現時点のサーバ起動からの経過時間[秒] - SQL実行開始時点のサーバ起動からの経過時間[ピコ秒]/10^12)
= 現在時刻[年月日時分秒] - 現在時点の何秒前にSQLを実行したか[秒]
= DATE_ADD(現在時刻[年月日時分秒] INTERVAL 現在時点の何秒前にSQLを実行したか[秒] SECOND)
= DATE_ADD(SYSDATE() INTERVAL -現在時点の何秒前にSQLを実行したか[秒] SECOND)

とすると、

SELECT DATE_ADD(SYSDATE(),INTERVAL -((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime') - eshl.TIMER_START*POW(10,-12)) SECOND) as result
FROM performance_schema.events_statements_history_long eshl

といった感じになりました。


もう少しラクにならないんでしょうか!?

ていうか、最初からシステム日付で持ってていただけませんかね。(パフォーマンスを考慮してピコ秒管理にした、という記述がマニュアル上にあるのは知ってます)


あと、「誰が」を取る方法が未解決です。
昔のTHREAD_IDがあっても、その時点のTHREAD-PROCESS-HOST-USERに紐付ける情報がないのに、それが何の役に立つというのか。。。


「誰が」のところは、OracleのSTATSPACKでも対応してないっけ?


ニーズは尽きないのですが、

  • 「いつ」の要件を「初めて実行された日時、最後に実行された日時」
  • 「どんなSQL」を、可変部分(Where条件)をサマリしてよい
  • 「誰が」はわからなくてもよい

など、条件を緩めると、もっと簡単に情報が取れるテーブルがあります。


「performance_schema.events_statements_summary_by_digest」

yoku0825さん絶賛のテーブルです。
日々の覚書: MySQLのperformance_schemaでどれくらいの情報が見られるのか


テーブル定義は以下のとおり。

mysql> show create table events_statements_summary_by_digest\G
*************************** 1. row ***************************
       Table: events_statements_summary_by_digest
Create Table: CREATE TABLE `events_statements_summary_by_digest` (
  `SCHEMA_NAME` varchar(64) DEFAULT NULL,
  `DIGEST` varchar(32) DEFAULT NULL,
  `DIGEST_TEXT` longtext,
  `COUNT_STAR` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `SUM_LOCK_TIME` bigint(20) unsigned NOT NULL,
  `SUM_ERRORS` bigint(20) unsigned NOT NULL,
  `SUM_WARNINGS` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_AFFECTED` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_SENT` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_EXAMINED` bigint(20) unsigned NOT NULL,
  `SUM_CREATED_TMP_DISK_TABLES` bigint(20) unsigned NOT NULL,
  `SUM_CREATED_TMP_TABLES` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_FULL_JOIN` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_FULL_RANGE_JOIN` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_RANGE` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_RANGE_CHECK` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_SCAN` bigint(20) unsigned NOT NULL,
  `SUM_SORT_MERGE_PASSES` bigint(20) unsigned NOT NULL,
  `SUM_SORT_RANGE` bigint(20) unsigned NOT NULL,
  `SUM_SORT_ROWS` bigint(20) unsigned NOT NULL,
  `SUM_SORT_SCAN` bigint(20) unsigned NOT NULL,
  `SUM_NO_INDEX_USED` bigint(20) unsigned NOT NULL,
  `SUM_NO_GOOD_INDEX_USED` bigint(20) unsigned NOT NULL,
  `FIRST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LAST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

とりあえず、このテーブルを参照して、「AVG_TIMER_WAIT」「MAX_TIMER_WAIT」に著しく大きい値が出ているようであれば、異常に時間がかかるSQLが実行されたということが推測できますね。


表示単位はデフォルトではナノ秒です。


performance_schema.setup_timersに、テーブルの種類毎にTIMER系の列にどの単位で管理するかを設定してあります。

mysql> select * from performance_schema.setup_timers;
+-----------+-------------+
| NAME      | TIMER_NAME  |
+-----------+-------------+
| idle      | MICROSECOND |
| wait      | CYCLE       |
| stage     | NANOSECOND  |
| statement | NANOSECOND  |
+-----------+-------------+
4 rows in set (0.00 sec)


タイマー系以外にも、パフォーマンス悪化につながりそうなイベントの発生回数を「SUM_」で集計してあります。


「COUNT_STAR」が、そのDIGEST(≒SQL)に集約集計された件数なので、この数字で割れば平均となります。


「FIRST_SEEN列」「LAST_SEEN」列に、「YYYY-MM-DD HH24:MI:SS」形式で表示されるのもありがたいですね。



MySQLの統計情報は、「起動後からの経過時間」が基準

これが原則なんですよね。


なので、「STATSPACKっぽいの」っていう話の文脈に「ある2つの時点を指定した、その時間帯の中で」っていう要件を含まれると、結構辛い。


多分、どこかでスケジューラを噛ませて、performance_schemaやinformation_schemaの情報を保存するようなものを作って、さらに比較処理~レポート生成という大物を作らないとだよね。



まだまだ調べることがたくさんありそう。

MariaDB&MySQL全機能バイブル

MariaDB&MySQL全機能バイブル

MySQLでのNull、空文字、関連する関数など

MySQL

先人はいるけれど、調べてみた。

比較演算子については、こちらにまとまってますね。
d.hatena.ne.jp



公式ドキュメントでは

いきなり、ちょっとキツ目の表現でスタートします。

NULL 値の概念については、NULL が空の文字列 '' と同じであると考えがちな SQL の初心者が混乱することがよくあります。これらは同一ではありません。たとえば、次の 2 つのステートメントは完全に異なります。

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');
両方のステートメントで phone カラムに値が挿入されていますが、最初のステートメントは NULL 値を挿入しており、2 番目のステートメントは空の文字列を挿入しています。最初のステートメントの意味は「電話番号がわからない」、2 番目のステートメントの意味は「この人は電話を持っていないため、電話番号がない」と見なすことができます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: B.5.5.3 NULL 値に関する問題

それでは実験

まずは準備

いつもどおり、環境はLinux上のMySQL 5.6.31です。
こんな感じでテーブルとデータを用意しておきます。

mysql> CREATE DATABASE test_null DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `test_tbl` (
    ->   `col_id` char(1) COLLATE utf8mb4_bin NOT NULL,
    ->   `col_char1` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.17 sec)

mysql> show create table test_tbl\G
*************************** 1. row ***************************
       Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
  `col_id` char(1) COLLATE utf8mb4_bin NOT NULL,
  `col_char1` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> INSERT INTO test_tbl (col_id,col_char1) values 
    -> ('a','a'),
    -> ('b','bb'),
    -> ('c','ccc'),
    -> ('d','dddd'),
    -> ('e','eeeee'),
    -> ('f',''),
    -> ('g',' '),
    -> ('h','  '),
    -> ('i','   '),
    -> ('j','    '),
    -> ('k','     '),
    -> ('l',null),
    -> ('m','m    '),
    -> ('n','nn   '),
    -> ('o','ooo  '),
    -> ('p','pppp ');
Query OK, 16 rows affected (0.03 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> SELECT col_id,length(col_char1) FROM test_tbl;
+--------+-------------------+
| col_id | length(col_char1) |
+--------+-------------------+
| a      |                 1 |
| b      |                 2 |
| c      |                 3 |
| d      |                 4 |
| e      |                 5 |
| f      |                 0 |
| g      |                 1 |
| h      |                 2 |
| i      |                 3 |
| j      |                 4 |
| k      |                 5 |
| l      |              NULL |
| m      |                 5 |
| n      |                 5 |
| o      |                 5 |
| p      |                 5 |
+--------+-------------------+
16 rows in set (0.00 sec)

スペースだけを詰め込んでも、ちゃんと桁数として認識しています。
ここ、あとで重要ですので、今後の検証用SQLも、LENGTH結果を一緒に表示しておきたいと思います。


ISNULL関数

ISNULL(expr)
expr が NULL の場合、ISNULL() は 1 を返し、それ以外の場合は 0 を返します。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.3.2 比較関数と演算子

ということで、明示的にNullをInsertしたのは1行だけですから、その行だけが1=Trueとして認識されることが期待されます。

mysql> SELECT col_id,col_char1,ISNULL(col_char1),LENGTH(col_char1) FROM test_tbl;
+--------+-----------+-------------------+-------------------+
| col_id | col_char1 | ISNULL(col_char1) | LENGTH(col_char1) |
+--------+-----------+-------------------+-------------------+
| a      | a         |                 0 |                 1 |
| b      | bb        |                 0 |                 2 |
| c      | ccc       |                 0 |                 3 |
| d      | dddd      |                 0 |                 4 |
| e      | eeeee     |                 0 |                 5 |
| f      |           |                 0 |                 0 |
| g      |           |                 0 |                 1 |
| h      |           |                 0 |                 2 |
| i      |           |                 0 |                 3 |
| j      |           |                 0 |                 4 |
| k      |           |                 0 |                 5 |
| l      | NULL      |                 1 |              NULL |
| m      | m         |                 0 |                 5 |
| n      | nn        |                 0 |                 5 |
| o      | ooo       |                 0 |                 5 |
| p      | pppp      |                 0 |                 5 |
+--------+-----------+-------------------+-------------------+
16 rows in set (0.00 sec)

期待通りです。
長さ0の文字列「''」を入れた行は、Nullではない、と認識されています。


IFNULL関数

IFNULL(expr1,expr2)
expr1 が NULL でない場合、IFNULL() は expr1 を返し、それ以外の場合は expr2 を返します。IFNULL() は、使用されているコンテキストに応じて、数値または文字列値を返します。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.4 制御フロー関数

ということで、明示的にNullをInsertしたのは1行だけですから、その行だけが1=Trueとして認識され、変換後の文字列が表示されることが期待されます。

mysql> SELECT col_id,col_char1,IFNULL(col_char1,'null_value!'),LENGTH(col_char1) FROM test_tbl;
+--------+-----------+---------------------------------+-------------------+
| col_id | col_char1 | IFNULL(col_char1,'null_value!') | LENGTH(col_char1) |
+--------+-----------+---------------------------------+-------------------+
| a      | a         | a                               |                 1 |
| b      | bb        | bb                              |                 2 |
| c      | ccc       | ccc                             |                 3 |
| d      | dddd      | dddd                            |                 4 |
| e      | eeeee     | eeeee                           |                 5 |
| f      |           |                                 |                 0 |
| g      |           |                                 |                 1 |
| h      |           |                                 |                 2 |
| i      |           |                                 |                 3 |
| j      |           |                                 |                 4 |
| k      |           |                                 |                 5 |
| l      | NULL      | null_value!                     |              NULL |
| m      | m         | m                               |                 5 |
| n      | nn        | nn                              |                 5 |
| o      | ooo       | ooo                             |                 5 |
| p      | pppp      | pppp                            |                 5 |
+--------+-----------+---------------------------------+-------------------+
16 rows in set (0.00 sec)

これも期待通り。



NULLIF関数

ちょっと特殊な関数ですね。
expr1もexpr2も、Nullであるかどうかは関係がなく、その評価結果としてNullを返す、というものです。

NULLIF(expr1,expr2)
expr1 = expr2 が true の場合は NULL を返し、それ以外の場合は expr1 を返します。これは、CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END と同じです。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.4 制御フロー関数

ここの評価にNullや長さ0の文字列「''」を入れたら、どうなるでしょうか。

mysql> SELECT col_id,col_char1,NULLIF(col_char1,null),LENGTH(col_char1) FROM test_tbl;
+--------+-----------+------------------------+-------------------+
| col_id | col_char1 | NULLIF(col_char1,null) | LENGTH(col_char1) |
+--------+-----------+------------------------+-------------------+
| a      | a         | a                      |                 1 |
| b      | bb        | bb                     |                 2 |
| c      | ccc       | ccc                    |                 3 |
| d      | dddd      | dddd                   |                 4 |
| e      | eeeee     | eeeee                  |                 5 |
| f      |           |                        |                 0 |
| g      |           |                        |                 1 |
| h      |           |                        |                 2 |
| i      |           |                        |                 3 |
| j      |           |                        |                 4 |
| k      |           |                        |                 5 |
| l      | NULL      | NULL                   |              NULL |
| m      | m         | m                      |                 5 |
| n      | nn        | nn                     |                 5 |
| o      | ooo       | ooo                    |                 5 |
| p      | pppp      | pppp                   |                 5 |
+--------+-----------+------------------------+-------------------+
16 rows in set (0.00 sec)

Nullと比較した場合、NullをInsertした1行だけをNullとしてを正しく検出しています。

mysql> SELECT col_id,col_char1,NULLIF(col_char1,''),LENGTH(col_char1) FROM test_tbl;
+--------+-----------+----------------------+-------------------+
| col_id | col_char1 | NULLIF(col_char1,'') | LENGTH(col_char1) |
+--------+-----------+----------------------+-------------------+
| a      | a         | a                    |                 1 |
| b      | bb        | bb                   |                 2 |
| c      | ccc       | ccc                  |                 3 |
| d      | dddd      | dddd                 |                 4 |
| e      | eeeee     | eeeee                |                 5 |
| f      |           | NULL                 |                 0 |
| g      |           | NULL                 |                 1 |
| h      |           | NULL                 |                 2 |
| i      |           | NULL                 |                 3 |
| j      |           | NULL                 |                 4 |
| k      |           | NULL                 |                 5 |
| l      | NULL      | NULL                 |              NULL |
| m      | m         | m                    |                 5 |
| n      | nn        | nn                   |                 5 |
| o      | ooo       | ooo                  |                 5 |
| p      | pppp      | pppp                 |                 5 |
+--------+-----------+----------------------+-------------------+
16 rows in set (0.01 sec)

しかし、長さ0の文字列''と比較させた場合、以下のものがごっちゃになっています。

  • Nullを入れたもの。
  • 長さ0の文字列''を入れたもの。
  • 半角スペースだけを入れたもの。


ちょっと困りましたね。
Nullと''の判別がつかないだけでなく、半角スペースは全部トリミングしてしまっているようです。



公式ドキュメントに「CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 ENDと同じ」っていう記載がありましたね。
なので、演算子の動きも確認しておく必要がありそうです。。
冒頭で参照したブログで扱っている「=」、「!=」の他に、「<=>」という演算子もあるので、それらを確認することにします。

「=」演算子と「!=」演算子
mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = '';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| f      |           |                 0 |
| g      |           |                 1 |
| h      |           |                 2 |
| i      |           |                 3 |
| j      |           |                 4 |
| k      |           |                 5 |
+--------+-----------+-------------------+
6 rows in set (0.00 sec)

いきなり大本命ですが、長さ0の文字列「''」~半角スペースだけでフル桁がヒットしています。
つまり、半角スペースは、何桁保存されていても、比較時にトリミングされてしまっているような挙動となります。


一方、Nullはヒットしません。

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 != '';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| a      | a         |                 1 |
| b      | bb        |                 2 |
| c      | ccc       |                 3 |
| d      | dddd      |                 4 |
| e      | eeeee     |                 5 |
| m      | m         |                 5 |
| n      | nn        |                 5 |
| o      | ooo       |                 5 |
| p      | pppp      |                 5 |
+--------+-----------+-------------------+
9 rows in set (0.00 sec)

Nullないしスペースのみが格納されたもの以外が抽出されました。


念のため、半角スペースを検出できるか、半角スペースの桁数ごとの違いを認識できるか、チェックしておきます。

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = ' ';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| f      |           |                 0 |
| g      |           |                 1 |
| h      |           |                 2 |
| i      |           |                 3 |
| j      |           |                 4 |
| k      |           |                 5 |
+--------+-----------+-------------------+
6 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = '  ';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| f      |           |                 0 |
| g      |           |                 1 |
| h      |           |                 2 |
| i      |           |                 3 |
| j      |           |                 4 |
| k      |           |                 5 |
+--------+-----------+-------------------+
6 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = '   ';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| f      |           |                 0 |
| g      |           |                 1 |
| h      |           |                 2 |
| i      |           |                 3 |
| j      |           |                 4 |
| k      |           |                 5 |
+--------+-----------+-------------------+
6 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = '    ';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| f      |           |                 0 |
| g      |           |                 1 |
| h      |           |                 2 |
| i      |           |                 3 |
| j      |           |                 4 |
| k      |           |                 5 |
+--------+-----------+-------------------+
6 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = '     ';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| f      |           |                 0 |
| g      |           |                 1 |
| h      |           |                 2 |
| i      |           |                 3 |
| j      |           |                 4 |
| k      |           |                 5 |
+--------+-----------+-------------------+
6 rows in set (0.00 sec)

残念、まったく区別できませんでした。


この仕様についての回避策については、「BINARY」句をつければいいって、先人の方が言っておりました。
qiita.com

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY '';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| f      |           |                 0 |
+--------+-----------+-------------------+
1 row in set (0.01 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY ' ';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| g      |           |                 1 |
+--------+-----------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY '  ';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| h      |           |                 2 |
+--------+-----------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY '   ';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| i      |           |                 3 |
+--------+-----------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY '    ';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| j      |           |                 4 |
+--------+-----------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 = BINARY '     ';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| k      |           |                 5 |
+--------+-----------+-------------------+
1 row in set (0.00 sec)

はい。全部区別できました。

「<=>」演算子

あまり見慣れない演算子です。「NULL 安全等価演算子」って呼ぶらしいです。

<=>
NULL - 安全等価。この演算子では、= 演算子のように等価比較が実行されますが、両方のオペランドが NULL であれば、NULL でなく 1 が返され、一方のオペランドが NULL の場合は、NULL でなく 0 が返されます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.3.2 比較関数と演算子

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 <=> '';
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| f      |           |                 0 |
| g      |           |                 1 |
| h      |           |                 2 |
| i      |           |                 3 |
| j      |           |                 4 |
| k      |           |                 5 |
+--------+-----------+-------------------+
6 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 <=> NULL;
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| l      | NULL      |              NULL |
+--------+-----------+-------------------+
1 row in set (0.00 sec)

Nullと長さ0の文字列''を明確に区別してくれていますね。
ただし、半角スペースは、例によってトリミングされてしまうのです。


「IS NULL」と「IS NOT NULL」演算子

Nullの評価と言ったら、これですよね。

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 IS NULL;
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| l      | NULL      |              NULL |
+--------+-----------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT col_id,col_char1,LENGTH(col_char1) FROM test_tbl WHERE col_char1 IS NOT NULL;
+--------+-----------+-------------------+
| col_id | col_char1 | LENGTH(col_char1) |
+--------+-----------+-------------------+
| a      | a         |                 1 |
| b      | bb        |                 2 |
| c      | ccc       |                 3 |
| d      | dddd      |                 4 |
| e      | eeeee     |                 5 |
| f      |           |                 0 |
| g      |           |                 1 |
| h      |           |                 2 |
| i      |           |                 3 |
| j      |           |                 4 |
| k      |           |                 5 |
| m      | m         |                 5 |
| n      | nn        |                 5 |
| o      | ooo       |                 5 |
| p      | pppp      |                 5 |
+--------+-----------+-------------------+
15 rows in set (0.00 sec)

期待通り、NullとしてInsertした1行だけを、明確に区別してくれています。


まとめ

  • Nullと長さ0の文字列「''」は別物。
  • Nullを評価する必要がある場合、「=」、「!=」は使わない!
  • 長さ0の文字列「''」の評価にも、「=」、「!=」は使わないほうが安全。Nullと区別できないから。使うなら「BINARY」を使って!
  • 内部的に「=」、「!=」を使っている関数がある。ここにNullや長さ0の文字列「''」を評価させない!

MariaDB&MySQL全機能バイブル

MariaDB&MySQL全機能バイブル

Oracle文化が根強いSIerと一緒にMySQL案件に関わると質問される5つのこと

MySQL 小ネタ

タイトル通りですが、とりあえず列挙

STATSPACK的なもの、ないの?

ありません。
価格差考えてください。

TuningPack的なもの、ないの?

(ry

過去に実行したSQL、誰がいつ実行したか追えないの?

いわゆる監査ログってやつ。
無料でやりたいなら、generarl_logで全SQL吐いてください。
あとは有償/無償でプラグイン追加すればできるようになりますよ。
I/Oなり、なんらかのリソースを食うけどね。
監査やらない前提で必要スペック見積もっておいて、事故ってから騒ぐのやめてください。


クエリ遅いんだけど!

Oracleと同じ感覚で一文でバッチリ取ってくる数百行のクエリ書いて悦に入ってないで、実行計画まず見てくれ。
MySQLで相関サブクエリが死ぬほど遅いってことは、一度でも自分で調べる意思があってググッていれば、嫌でも目に入るだろ。
まさにggrks(死語?


この相談を受けた時の、インフラ屋的、模範解答。
「このSQLの元になった業務用件わからないんで詳細にこう直せって示せないんですけど、とにかく、相関サブクエリを取り除く方向で頑張ってください(棒」


CSVデータがロードできないんだけど!

読み込むファイルの置き場所と、LODA DATAのオプションの関係、あってますか?
クライアントにファイル置いてるのに、LOCALオプションいれてないんじゃないですか?
ブログで適当なサンプル拾ってコピペするだけじゃなく、マニュアルにバッチリ書いてあるので、ちゃんと読んでください。



ていうか、マニュアル読まないエンジニア多すぎじゃねえか?

公式サイトに日本語でもがっつり書いてあるんだから、まず読もうや。
安いからって提案にブッこむ前に、まず無償公開されてる研修資料くらい眺めようや。
MySQL案件にアサインされそうってわかった時点で、自分の知っているプロダクトと何が違うか、ポイントだけでも抑えようや。


ガチでMySQL案件初めてなのに、こんな問い合わせを毎日相手してる。
この程度の質問に答えてるだけで重宝されるの、ある意味おいしいけどな。



最後に

MySQLで使えるOracle ADR/ADDMみたいなパフォーマンスレポート、誰か作ってくれたりしないのー?
特にRDSでも使える(DBサーバOSにログイン・インストールができなくても使える)ヤツ希望。


エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

MySQLの「ALTER TABLE ADD INDEX」と「CREATE INDEX」

MySQL

ALTER権限とALTER TABLEの検証作業の中で。。。

atsuizo.hatenadiary.jp
ここで、ALTERしか付与してないのに、INDEXのADD/DROPが成功してたことが、どうにも引っかかってまして。


公式ドキュメント上は。。。

表 13.1 GRANT および REVOKE に対して許容可能な権限

権限 意味と付与可能なレベル
(略)
INDEX インデックスの作成または削除を有効にします。レベル: グローバル、データベース、テーブル。
(略)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.1.4 GRANT 構文

CREATE INDEX は、インデックスを作成するために ALTER TABLE ステートメントにマップされます。セクション13.1.7「ALTER TABLE 構文」を参照してください。CREATE INDEX を使用して PRIMARY KEY を作成することはできません。代わりに ALTER TABLE を使用します。インデックスの詳細は、セクション8.3.1「MySQL のインデックスの使用の仕組み」を参照してください。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.13 CREATE INDEX 構文

とまあ、こんな感じです。



CREATE INDEXを試してみる。

前回同様、ALTERしか権限を持っていないユーザで接続し、CREATE INDEXを実行してみましょう。
引き続きMySQLは5.6.31を使用しています。

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| alttest@%      |
+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for alttest@%                                                |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alttest'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALTER ON `alttest_db`.* TO 'alttest'@'%'                      |
+---------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> CREATE INDEX alttest_idx ON alttest_tbl (col_2);
ERROR 1142 (42000): INDEX command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> ALTER TABLE alttest_tbl ADD INDEX idx_col2 (col_2);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`),
  KEY `idx_col2` (`col_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

えっ!?


CREATE INDEXコマンドは、権限不足で拒否られました。
ALTER TABLE ADD INDEXは、前回同様、さっくり通りました。


なんだこれ?INDEX権限が独立している意味が皆無www



同様に、

mysql> DROP INDEX alttest_idx ON alttest_tbl;
ERROR 1142 (42000): INDEX command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> ALTER TABLE alttest_tbl DROP INDEX idx_col2;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)


DROP INDEXコマンドは、権限不足で拒否られました。
ALTER TABLE DROP INDEXは、前回同様、さっくり通りました。



うーん、この。


逆に、INDEX権限しか持っていなかったらどうなるんだろか。気になります。


気になったら、やってみる。INDEX権限しかないユーザの場合。

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| idxtest@%      |
+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for idxtest@%                                                |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'idxtest'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT INDEX ON `alttest_db`.* TO 'idxtest'@'%'                      |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> CREATE INDEX alttest_idx ON alttest_tbl (col_2);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`),
  KEY `alttest_idx` (`col_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> DROP INDEX alttest_idx ON alttest_tbl;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl ADD INDEX idx_col2 (col_2);
ERROR 1142 (42000): ALTER command denied to user 'idxtest'@'localhost' for table 'alttest_tbl'

mysql> show index from alttest_tbl;
ERROR 1142 (42000): SELECT command denied to user 'idxtest'@'localhost' for table 'alttest_tbl'


予想通り、って言えば予想通りな結果です。
INDEX権限のみの場合、「CREATE/DROP INDEX」はできるけど、「ALTER TABLE ADD/DROP INDEX」はできませんでした。


おまけでSHOW INDEXコマンドも実験しましたが、これは、インデックスの付与先テーブルに対するSELECT権限がないと、やっぱり拒否されました。

まとめ

  • 「ALTER TABLE ADD INDEX」と「CREATE INDEX」は、それぞれ「ALTER権限」「INDEX権限」に対応している。
  • INDEX権限がなくても、ALTER権限があれば、ALTER TABLE からインデックスの操作は可能。
  • ALTER権限がなくても、INDEX権限があれば、CREATE/DROP INDEXは可能
  • CREATE INDEXは、プライマリキーの操作には使えない。

INDEX権限がなくてもALTER権限があればインデックス操作はできるため、INDEX操作を保護する目的でINDEX権限の付与剥奪を利用することはできませんでした。


INDEX権限だけを付与するケースって、ALTERでは権限過剰なケース、「テーブルの定義は絶対にいじらせたくないけど、インデックスのメンテナンスはさせたい」みたいなケースでしょうか。


そんなニーズがどれほどあるのか、よくわかりませんが。

MariaDB&MySQL全機能バイブル

MariaDB&MySQL全機能バイブル

MySQLの「ALTER TABLE RENAME」と「RENAME TABLE」

MySQL

RENAME TABLEなんてコマンド、知らなかったよ。。。

テーブル名の変更といえば、「ALTER TABLE old_table_name RENAME TO new_table_name」だと思っていたOracle厨でしたが、MySQLの案件に絡んでいたら「RENAME TABLE old_table_name TO new_table_name」なんてものがあると知りました。


調べていくと、どうやら「テーブル名の変更」については、ANSI/ISO SQL Standardの中で定義されていなくて、各RDBMSの実装に委ねられているみたいです。

処理は一瞬

MySQL 5.6.31で実験してみましたが、データ量に関係なく、処理は一瞬でした。

mysql> select count(*) from test_touch_x;
+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (3 min 7.98 sec)

mysql> ALTER TABLE test_touch_x  RENAME TO test_touch_y;
Query OK, 0 rows affected (0.12 sec)

mysql> RENAME TABLE test_touch_y TO test_touch_z;
Query OK, 0 rows affected (0.12 sec)

わざわざ1600万件、ibdファイルのサイズで9.3GBに相当するテーブルを作ってから試したのですが、名前変更にはデータサイズは何の影響もありませんでした。

違いは何?

MySQL固有の話、バージョンにも依存するのだと思いますが、違いは、構文および指定可能なオプションの違いに着目すると見えてきます。


RENAME TABLEの場合

構文は以下のとおり。

RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.32 RENAME TABLE 構文


一文で複数の処理ができ、先に書いた処理が先行する、というルールがあって、以下のように、テーブル名の入れ替えを1文で実行できます。

このステートメントで複数のテーブルの名前を変更する場合、名前の変更操作は左から右に実行されます。2 つのテーブル名をスワップする場合は、次のように実行できます (tmp_table はまだ存在していないと仮定します)。
RENAME TABLE old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.32 RENAME TABLE 構文


そして、複数同時に処理させている途中にエラーが起きた時、全部巻き戻されます。

複数テーブルの名前変更で何らかのエラーが発生した場合、MySQL はすべてをその元の状態に戻すために、名前変更されたすべてのテーブルに対して逆方向の名前変更を実行します。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.32 RENAME TABLE 構文


なお、VIEWに対しては実行できる一方、TEMPORARYテーブルには実行できない、という制約があります。



ここ一連のエントリで紹介している、
MariaDB&MySQL全機能バイブル
には

なお、RENAME TABLE文の実行中はテーブルがロックされるので、他のセッションからはアクセスできません。

という一文があるのですが、とにかく普通に名前変更するだけだと、前述の通り処理が速いので、ほとんど気にならないと思います。


同じ本のなかでは更に

データベースを跨いで、テーブルを移動することも可能です。ただし、テーブルにトリガが設定されている場合は移動することができません。

という記述があります。


これは「RENAME TO」の解説の中で記述があるのですが、だけではなく、「RENAME TO」だけでなく「ALTER TABLE RENAME」でも対応できます。


そして、処理自体は、やはり一瞬で終わります。


ここまでずっと「INNODBストレージエンジン、innodb_file_per_table=1(テーブル毎にデータファイルを分ける)」という前提で勝手に語ってましたが、この設定ではディレクトリ構造上、データベース名と同じディレクトリの下に、「テーブル名.frm」「テーブル名.ibd」が存在するわけで、リネーム処理の背後では、この2つのファイルに対して、LinuxのOSコマンドでいう「mv old_name new_name」を実行しているようです。


確かに、ディレクトリを跨ぐmvコマンド発行しても、ファイルサイズで処理時間が変わるという記憶ないな。。。

ALTER TABLE RENAMEの場合

ALTER TABLEのターゲットは1つしか指定できないので、RENAME TOによる名前変更自体は1つずつしかできませんが、代わりに、ADD COLUMNとか「他のALTER TABLE操作」と同時に実行が可能です。

一部の操作では、一時テーブルを必要としないインプレース ALTER TABLE が可能です。


ALTER TABLE tbl_name RENAME TO new_tbl_name をほかのオプションなしで実行すると、MySQL は単純に、コピーを作成することなく、テーブル tbl_name に対応するすべてのファイルの名前を変更します。(RENAME TABLE ステートメントを使用してテーブルの名前を変更することもできます。セクション13.1.32「RENAME TABLE 構文」を参照してください。)名前変更されたテーブル専用に付与された権限は、どれも新しい名前には移行されません。それらは、手動で変更する必要があります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

ALTER TABLEとALGORITHMオプションの話に入ると深みにハマるのですが、テーブルの再構成が不要な操作についてはコピーを生成せずに済ませてくれるINPLACEモードで処理してくれます。


RENAME TO単独でもCPOYモード指定できますが無意味ですね。それよりも、RENAME TOと同時にADD COLUMNなど再構成が必要な他の操作を組み合わせると、COPYモード発動で時間がかかるようになるので要注意です。



同様に、LOCKのオプションも指定可能です。




その辺の話も、ここで散々引用している公式ドキュメントのページに書いてあります。


まとめ

  • テーブル名の変更は、SQL標準にないので、RDBMS次第。
  • MySQLの場合、「ALTER TABLE RENAME」と「RENAME TABLE」どちらも使えて、処理速度に大差なし。
  • RENAME TABLEの場合、複数テーブルの変更が1文で可能。複数指定時の処理順序も先に書いた方からで一定。
  • ALTER TABLE RENAMEは、1テーブルずつしか名前変更できないが、同じ対象テーブルに対する他のALTER TABLE操作を1文の中で同時実行可能。


MariaDB&MySQL全機能バイブル

MariaDB&MySQL全機能バイブル

MySQLで、「ALTER権限だけ」でできることは何か?(2/2)

MySQL

おさらい

前のエントリでは「ALTER権限だけ」でできることをひたすら確認してきました。
こちらのエントリで検証する「パーティション操作以外」でいうと、「RENAME」以外のALTER TABLE操作は全部できる!という状況でした。


では、続きまして、パーティション操作についてのALTER TABLEが、どこまでできるか確認していきます。

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
(略)
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| REMOVE PARTITIONING

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

なお、使用するユーザは前のエントリと同じです。

パーティション操作

パーティション操作をやるのに数値型の方が色々ラクなので、それ用のテーブルを別途つくることにします。
メインは「alttest_tbl_prt」で、EXCHANGE_PARTATIONの対象として「alttest_tbl_prt_ex」を用意します。

mysql> CREATE TABLE `alttest_tbl_prt` (
    ->   `col_1` int COLLATE utf8mb4_bin NOT NULL,
    ->   `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
    ->   PRIMARY KEY (`col_1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO alttest_tbl_prt (col_1,col_2) values (1,'aaaa'),(101,'bbbb'),(201,'cccc'),(301,'dddd');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from alttest_tbl_prt;
+-------+-------+
| col_1 | col_2 |
+-------+-------+
|     1 | aaaa  |
|   101 | bbbb  |
|   201 | cccc  |
|   301 | dddd  |
+-------+-------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE `alttest_tbl_prt_ex` (
    ->   `col_1` int COLLATE utf8mb4_bin NOT NULL,
    ->   `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
    ->   PRIMARY KEY (`col_1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from alttest_tbl_prt_ex;
Empty set (0.01 sec)

このテーブルに対し、ALTERしか権限を持っていないユーザで、ALTER TABLEでパーティション操作系のコマンドを投げてみることにします。

mysql> ALTER TABLE alttest_tbl_prt
    -> PARTITION BY RANGE (col_1) (
    -> PARTITION p0 VALUES LESS THAN (100),
    -> PARTITION p1 VALUES LESS THAN (200),
    -> PARTITION p2 VALUES LESS THAN (300),
    -> PARTITION p3 VALUES LESS THAN (400));
Query OK, 4 rows affected (0.21 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY RANGE (col_1)
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl_prt ADD PARTITION (PARTITION p4 VALUES LESS THAN (500));
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY RANGE (col_1)
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (500) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl_prt TRUNCATE PARTITION p4;
ERROR 1142 (42000): DROP command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt ANALYZE PARTITION p4; 
ERROR 1142 (42000): SELECT, INSERT command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt CHECK PARTITION p4; 
ERROR 1142 (42000): SELECT command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt OPTIMIZE PARTITION p4; 
ERROR 1142 (42000): SELECT, INSERT command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt REBUILD PARTITION p4; 
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE alttest_tbl_prt REPAIR PARTITION p4; 
ERROR 1142 (42000): SELECT, INSERT command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt EXCHANGE PARTITION p4 WITH TABLE alttest_tbl_prt_ex;
ERROR 1142 (42000): INSERT, CREATE, DROP command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt DROP PARTITION p4; 
ERROR 1142 (42000): DROP command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt REORGANIZE PARTITION p0 INTO(
    ->         PARTITION n0 VALUES LESS THAN (50),
    ->         PARTITION n1 VALUES LESS THAN (100)
    -> );
Query OK, 1 row affected (0.13 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY RANGE (col_1)
(PARTITION n0 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (500) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl_prt REMOVE PARTITIONING; 
Query OK, 4 rows affected (0.17 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl_prt PARTITION BY HASH(col_1)  PARTITIONS 8;
Query OK, 4 rows affected (0.31 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY HASH (col_1)
PARTITIONS 8 */
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl_prt COALESCE PARTITION 2;
Query OK, 4 rows affected (0.30 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY HASH (col_1)
PARTITIONS 6 */
1 row in set (0.00 sec)


パーティション操作では、
できるのは、
PARTITION BY:新規構成。非パーティションパーティション
REBUILD:再構成。データ再格納(デフラグ効果)
ADD:パーティションの追加
REORGANIZE:分割。データを失わずに、パーティションを分ける。
REMOVE:解除。パーティション構造自体の取りやめ
COALESCE:合体。HASHパーティションの分割数を減らす。


できないのは、
TRUNCATE:切り捨て。DROP権限が必要。
ANALYZE:キー分布を読み取って格納。SELECT権限と、その結果を保持するためのINSERT権限が必要。
CHECK:パーティションのエラーをチェック。読み取りのためのSELECT権限が必要。
OPTIMIZE:未使用領域を解放したりパーティションデータファイルをデフラグ。SELECT権限と、綺麗にしたデータのINSERT権限が必要。
REPAIR:修復。読み取って修復して保存する、ということで、SELECT, INSERT権限が必要。
EXCHANGE:パーティションとテーブルの交換。INSERT, CREATE, DROPが必要。
DROP:削除(データごと消す)。DROP権限が必要。


ということになりました。

ENGINE

最後に、ALTER TABLEのリファレンスページ上に記載がないけど重要な、ストレージエンジンの変更、および、デフラグに使用する「ENGINE」句。

mysql>  ALTER TABLE alttest_tbl ENGINE=MyISAM;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql>  ALTER TABLE alttest_tbl ENGINE=INNODB;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

あっさり動きました。


これこそ、「裏でテーブル作ってデータの格納構造を変更したあとに、ポインタを切り替えている処理」だと思うのですが、DROP&CREATEの処理とはまた異なるようです。



まとめ

ALTER」権限単独では、「RENAME」と「PARTITIONの一部」が実行不可能です。
内部的に他の権限(SELECT・INSERT・DROP・CREATE)を必要とする処理が行われているかどうかが分かれ目です。


個人的には、この検証で初めて叩いたコマンド、何に使うんだこれ?ってコマンドもあったので、面白かったです。


MariaDB&MySQL全機能バイブル

MariaDB&MySQL全機能バイブル

MySQLで、「ALTER権限だけ」でできることは何か?(1/2)

MySQL

テーブルのRENAMEができない!と言われたことが発端で。。。

ALTERは与えてあるけど、ALTER TABLEの中には、他の権限を持ってないとできないことも混在してまして、このRENAMEも、裏でDROP&CREATEしているらしく、ALTERだけではできない操作の1つでした。

ドキュメント上の記述

こんなかんじ。

MySQL によってサポートされる権限

次の表は、GRANT および REVOKE ステートメントに対して指定できる許可される priv_type 権限タイプと、各権限を付与できるレベルについて要約したものです。これらの権限の詳細は、セクション6.2.1「MySQL で提供される権限」を参照してください。

表 13.1 GRANT および REVOKE に対して許容可能な権限

権限 意味と付与可能なレベル
ALL [PRIVILEGES] GRANT OPTION を除き、指定されたアクセスレベルにあるすべての権限を付与します
ALTER ALTER TABLE の使用を有効にします。レベル: グローバル、データベース、テーブル。
・・・ (略)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.1.4 GRANT 構文

ALTER TABLE を使用するには、このテーブルに対する ALTER、CREATE、および INSERT 権限が必要です。テーブルを名前変更するには、古いテーブル側で ALTER および DROP と、新しいテーブル側で ALTER、CREATE、および INSERT 権限が必要です。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

やってみた

準備

権限の強いユーザで接続し、以下のようにテスト用DB、ユーザ、テーブル、データを作成。

mysql> create user alttest identified by 'alttest';
Query OK, 0 rows affected (0.02 sec)

mysql> create database alttest_db;
Query OK, 1 row affected (0.00 sec)

mysql> grant alter on alttest_db.* to 'alttest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for alttest;
+--------------------------------------------------------------------------------------------------------+
| Grants for alttest@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alttest'@'%' IDENTIFIED BY PASSWORD '*4F9F63FFDE2301BFE25928CA7AAFA2B6E044993F' |
| GRANT ALTER ON `alttest_db`.* TO 'alttest'@'%'                                                         |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (2.10 sec)

mysql> CREATE TABLE alttest_db.alttest_tbl (
    ->   `col_1` char(10) PRIMARY KEY,
    ->   `col_2` char(10)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.88 sec)

mysql> INSERT INTO alttest_db.alttest_tbl VALUES ('aaaa','bbbb');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO alttest_db.alttest_tbl VALUES ('cccc','dddd');
Query OK, 1 row affected (0.02 sec)

準備はここまで。

ユーザを作った時点で、接続のためのグローバルレベル権限「USAGE」が付き、データベースレベル権限のALTERを付与しているので、SHOW GRANTSをすると2行表示されます。

準備運動

ここからは、準備の中で作った「altuser」で接続して、どのように見えているか確認です。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| alttest_db         |
+--------------------+
2 rows in set (0.00 sec)

mysql> use alttest_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_alttest_db |
+----------------------+
| alttest_tbl          |
+----------------------+
1 row in set (0.00 sec)

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)


本題に入る前に、序の口から。

「SHOW DATABASE権限」は与えていないので、GRANT ALTERでアクセス先に指定した「alttest_tbl」と、デフォルトで参照可能な「information_schema」だけが見えているのは想定通り。


次に、DML。SELECT、INSERT、UPDATE、DELETEを試してみます。

mysql> SELECT * FROM alttest_tbl;
ERROR 1142 (42000): SELECT command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> INSERT INTO alttest_tbl VALUES ('eeee','ffff');
ERROR 1142 (42000): INSERT command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> UPDATE alttest_tbl SET col2='zzzz' where col1='aaaa';
ERROR 1142 (42000): UPDATE command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> DELETE FROM alttest_tbl where col1 = 'cccc';
ERROR 1142 (42000): DELETE command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

これらも、権限を与えていないので、正しく拒否しています。


そしてDDL。とりあえず、焦らしで、CREATE TABLEとDROP TABLEを。

mysql> CREATE TABLE alttest_tbl_2 (
    -> `col_1` char(10) PRIMARY KEY,
    -> `col_2` char(10)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
ERROR 1142 (42000): CREATE command denied to user 'alttest'@'localhost' for table 'alttest_tbl_2'

mysql> DROP TABLE alttest_tbl;
ERROR 1142 (42000): DROP command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

これも、想定通り拒否しています。

そしてようやく本丸。「ALTER TABLE」へ。

ALTER TABLEと一言に言っても、色々なことができますので、おさらいしておきましょう。

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO|AS] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| FORCE
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| REMOVE PARTITIONING

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

これ、全部やるのかな、俺。。。

COLUMN(列)

基本的なところ、列の増減からやってみます。

mysql> ALTER TABLE alttest_tbl ADD COLUMN `col_3` char(10);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE alttest_tbl;
ERROR 1142 (42000): SELECT command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `col_3` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl MODIFY COLUMN `col_3` varchar(100);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `col_3` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl CHANGE COLUMN `col_3` `col_4` varchar(200);
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `col_4` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl DROP COLUMN `col_4`;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

できました。


そして、想定外の、「DESCRIBE」コマンドが権限不足で弾かれるという事態。
テーブル定義は「SHOW CREATE TABLE」でも確認できるので、こちらでどうぞ。


INDEX(インデックス)

利用頻度の高い、インデックスの追加削除。

mysql> ALTER TABLE alttest_tbl ADD INDEX idx_col2 (col_2);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from alttest_tbl;
ERROR 1142 (42000): SELECT command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

mysql> show creat table alttest_tbl;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat table alttest_tbl' at line 1

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`),
  KEY `idx_col2` (`col_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl DROP INDEX idx_col2;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

できました。


あれ?「INDEX」って権限は別に存在していて、付与していないんだけどな。

表 13.1 GRANT および REVOKE に対して許容可能な権限

権限 意味と付与可能なレベル
・・・ (略)
INDEX インデックスの作成または削除を有効にします。レベル: グローバル、データベース、テーブル。
・・・ (略)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.1.4 GRANT 構文

さらに、ここでもまさかの、「SHOW INDEX」コマンドが権限不足で拒否られた。

ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...

はINDEXの一種ってことで、権限一覧みても独立したそれらしきものが見当たらないので、飛ばします。(単にFULLTEXT用の準備するのが面倒くさい)

CONSTRAINTS(制約)

とりあえずUNIQUE KEYの追加・削除してみます。

mysql> ALTER TABLE alttest_tbl ADD CONSTRAINT UNIQUE UQ_COL2(col_2);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`),
  UNIQUE KEY `UQ_COL2` (`col_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl DROP KEY UQ_COL2;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

全然問題なし。


ADDの時はCONSTRAINTだけど、DROPのときはKEYなんだな。
制約自体を消さずに有効無効のコントロールする「ENABLE KEY/DISABLE KEY」も省略で。
追加削除できるのに、これができないとか想像できない。

RENAME

弾かれたから、今回の調査が始まっているわけで、実験するまでも無いのですが、一応。

mysql> ALTER TABLE alttest_tbl RENAME TO alttest_tbl_rn;
ERROR 1142 (42000): DROP command denied to user 'alttest'@'localhost' for table 'alttest_tbl'

失敗しました。
「DROP command denied」って言ってます。
冒頭で触れたとおり、「DROP」が必要でした。内部的に、新しい名前でテーブルをCREATEし、INSERT SELECTして、古いテーブルをDROPしているってことで。


CONVERT TO CHARACTER SET

MySQLではテーブルごとに指定できるので、それを変更するコマンドも当然のように用意されています。

mysql> ALTER TABLE alttest_tbl CONVERT TO CHARACTER SET latin1;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) NOT NULL,
  `col_2` char(10) DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 2 rows affected (0.36 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)


できました。
内部的な入れ替えは無し、保存済データについては放置、ってことでしょうね。


COLLATEも一緒に指定しないと、消えてしまうようです。


DISCARD TABLESPACE・IMPORT TABLESPACE

これ自体、初めてやるのですが、テーブルの実体ファイルの差し替え用のコマンドです。
Oracleでは多少聞き慣れた「トランスポータブル表領域」というヤツ。
なので、innodb_file_per_table=1(ON)のモードのみ有効です。



事前に、MySQLを停止した状態から、実験対象のテーブルのibdファイルをcpコマンドでバックアップしておきます。
でないと、DISCARDしたときに削除されてしまいますので。

mysql> ALTER TABLE alttest_tbl DISCARD TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show tables;
+----------------------+
| Tables_in_alttest_db |
+----------------------+
| alttest_tbl          |
+----------------------+
1 row in set (0.00 sec)

(バックアップしておいたalttest_tbl.ibdファイル)を、本来の場所・名前で配置する)

mysql> ALTER TABLE alttest_tbl IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.09 sec)

できました。SHOW TABLESに表示されていることからも分かる通り、テーブル定義のDROP/CREATEはしないようです。


今回のテストユーザはALTER権限でしか持っていないので、他にSELECT権限を持っているユーザからSELECTを発行すると、以下のようになります。

mysql> select * from alttest_tbl;
ERROR 1814 (HY000): Tablespace has been discarded for table 'alttest_tbl'


こんな感じで、テーブルの定義はあるけど、テーブルスペースが無いよ、って怒られます。
実際にOSレベルで確認してみると、DISCARDが成功すると、frmファイルは残っていますが、ibdファイルは消えてしまっています。


長くなったので、パーティション操作関係のコマンド検証をまるっと次のエントリへ。

MariaDB&MySQL全機能バイブル

MariaDB&MySQL全機能バイブル