2018年10月23日火曜日

MySQL 8.0.13の新機能でPRIMARY KEYのないテーブルを作成させない

TL;DR

  • sql_require_primary_key サーバー変数をONにすると、PRIMARY KEYのないテーブルを作ろうとした時にエラーにできる。
    • セッションスコープとグローバルスコープと両方あるやつで、実効値はセッションスコープなので注意。
    • ただし、 SET SESSION .. でも一般ユーザーでは値を変更することはできない( sql_log_bin とかもそうですね)
  • 超便利だ!! 秘伝のタレに入れる時は loose プレフィックスとかもいいと思うよ!!

取り敢えず基本的な使い方として、0(OFF)と1(ON)の時の動作の違い。
mysql80 9> SELECT @@sql_require_primary_key;
+---------------------------+
| @@sql_require_primary_key |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

mysql80 9> CREATE TABLE t1 (num int);
Query OK, 0 rows affected (0.04 sec)

mysql80 9> SET @@session.sql_require_primary_key= 1;
Query OK, 0 rows affected (0.00 sec)

mysql80 9> SELECT @@sql_require_primary_key;
+---------------------------+
| @@sql_require_primary_key |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

mysql80 9> CREATE TABLE t2 (num int);
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
おお、マニュアル通り。
Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting. にちょっとニヤリとするwww
しかし、セッションとグローバルと両方持ってるってことは一般ユーザーで値書き換えられちゃうんでは? と思ったけど、それができない類の変数になっていた(昔から sql_log_bin とかもセッション変数だけど一般ユーザーは変更できないのでそれと同じところを通ってるのであろう)
mysql80 10> SHOW GRANTS;
+--------------------------------------------------+
| Grants for yoku0825@%                            |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%`             |
| GRANT ALL PRIVILEGES ON `d1`.* TO `yoku0825`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 10> SET @@session.sql_require_primary_key= 1;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
これで勝手にPK必須が剥がれる心配もなく。
ちなみに、これがONの時にどれくらいPKを強制されるかというと
mysql80 11> OPTIMIZE TABLE t2;
+-------+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Op       | Msg_type | Msg_text                                                                                                                                                                                                                                                                                                      |
+-------+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| d1.t2 | optimize | note     | Table does not support optimize, doing recreate + analyze instead                                                                                                                                                                                                                                             |
| d1.t2 | optimize | error    | Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting. |
| d1.t2 | optimize | status   | Operation failed                                                                                                                                                                                                                                                                                              |
+-------+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql80 11> ALTER TABLE t2 ADD val varchar(32);
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

mysql80 11> ALTER TABLE t2 ADD KEY(num);
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

mysql80 11> INSERT INTO t2 VALUES (2);
Query OK, 1 row affected (0.01 sec)
ALTER TABLE 関連( OPTIMIZE TABLE もInnoDBでは ALTER TABLE にマッピングされるので同類とする)は全滅。
Sql_cmd_alter_table::execute -> mysql_alter_table -> create_table_impl -> mysql_prepare_create_table でエラーになるので、 ALTER TABLE だけが引っ掛かりそうだしDMLは影響を受けなさそう。
ちなみにストレージエンジン問わないので、ONにしたままだとCSVストレージエンジンは身動きが取れない(*ノ∀ノ)
mysql80 12> CREATE TABLE t3 (num int) Engine= CSV;
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

mysql80 12> CREATE TABLE t3 (num int, PRIMARY KEY(num)) Engine= CSV;ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

0 件のコメント :

コメントを投稿