ぼくらが8.0にいたるまでのみちのり （未到） The road leading to MySQL 8.0(Not yet arrived) 2018/11/21 yoku0825 Oracle MySQL Innovation Day 2018 秋

Safe Harbor Statement この資料はyoku0825の独断と偏見によるものであり、所属 する組織、所属しない組織またはNULLの意見を一切代表す る訳がありません != 問題によりひどく酔っぱらっています 1/108

Congratulations for MySQL 8.0 GA Released!! 2/108

Released mysql80 160> SELECT CURDATE() /* JST */; +------------+ | CURDATE() | +------------+ | 2018-04-20 | +------------+ 1 row in set (0.00 sec) mysql80 160> SELECT @@version; +-----------+ | @@version | +-----------+ | 8.0.11 | +-----------+ 1 row in set (0.00 sec) 3/108

Today mysql80 160> SELECT CURDATE() /* JST */; +------------+ | CURDATE() | +------------+ | 2018-11-21 | +------------+ 1 row in set (0.00 sec) mysql80 160> SELECT @@version; +-----------+ | @@version | +-----------+ | 8.0.13 | +-----------+ 1 row in set (0.00 sec) 4/108

なんと By the way, ナッツシェル（ What is New in MySQL 8.0 の章）には $ curl -L -s https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshe ll.html | perl -nlE 'if ($_ =~ /(8\.0\.\d+)/) { say $1 }' | sort -n | uniq -c 1 8.0.0 2 8.0.12 6 8.0.13 5 8.0.14 3 8.0.2 3 8.0.3 2 8.0.4 5/108

なんか調べようと思ってナッツ シェルを見るたびに新しい項目 が増えている斬新なRDBMSで す Everytime I looked for nutshell, everytime new features have been added. 6/108

GA #とはな んだったのか What is GA 7/108

そんな 愉快 盛り だくさんな MySQL 8.0ですが That is MySQL 8.0 8/108

ちょっとアン ケート Survey 9/108

自分の検証用に突っ 込んだ人？ Is there a person who has introduced 8.0 into your machine? 10/108

テスト環境に突っ込ん だ人？ （ウチはｲﾏｺｺ） Is there a person who has introduced 8.0 into Test Environment? (I am) 11/108

手が挙がらなかった人はみん な既に本番に突っ込んだって ことでいいですか？ :D People who didn’t raise their hand, has already introduced 8.0 into Production Environment, isn’t it? :D 12/108

言えない人もいると 思いますので I knew there are people who are prohibited to tell 13/108

(俺以外の)全員がMySQL 8.0を既に本番に導入した ということにして進めます I assume all of you have introduced 8.0 into Production Environment. 14/108

思い出してく ださい Remember 15/108

8.0にいたるまで のみちのりを the road leading to MySQL 8.0 16/108

＼こんばんわ／ yoku0825＠GM〇メディア株式会社 オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 17/108

みちのりに思いを 馳せる前にちょっ と 復讐 復習 At first, 18/108

MySQL 8.0に求 めることとは？ What you ask to MySQL 8.0? 19/108

とあるDBAがMySQL 8.0に求めたこと A DBA says 新しい performance_schema お気に入りは variables_info と events_errors_summary_by_account_by_error, data_locks ‐ SELECT .. FOR UPDATE SKIP LOCKED CLONE {LOCAL\|REMOTE} DATA DIRECTORY .. JSON_TABLE 関数 Window関数群 Instant ADD COLUMN（最近気付いた） 20/108

開発エンジニアがMySQL 8.0に求めたこと？ But application developers say SELECT .. FOR UPDATE SKIP LOCKED （とはいえWEBの世界 で排他ロック…？） 再帰でないCTE（がすごく速いこと） JSON型（それ、5.7からあるねん…） SQLの互換性 接続ライブラリーを入れ替えても入れ替えなくても動く 21/108

ミスマッチ Mismatched opinions （再帰でない）CTEは便利だけど、それだけでびっくりする くらい性能が上がる訳ではない 書きやすくなったぶん、JOINの回数を減らせることに気付きやすく なったり、狙うインデックスがわかりやすくなったりってメリットは ある ‐ CREATE TEMPORARY TABLE .. ENGINE= MYISAM AS SELECT .. は相変わ らず最強 ‐ 再帰CTEとWindow関数はいいものだけど、そもそも今まで なかったのでSQLが書けない・使おうと思われない 22/108

ミスマッチ Mismatched opinions 一部、SQLの互換性が損なわれた（これ結構珍しい） RANK とか ROLE のキーワードのことですよ… ‐ GROUP BY の暗黙のソートとか ‐ 接続ライブラリー…ｳｯ caching_sha2_password はライブラリーがない以外にもSSLとか有効 化しないといけなかったり…？ ‐ 23/108

今までMySQLのバージョンアップでやられたこと？ What were issues in previous versionups? 4.0とそれ以前 ⇒ 4.1とそれ以降 timestamp型の変更、varchar型が文字コードをサポートしたことによる地 獄 5.5 ⇒ 5.6 datetime, time, timestamp型の内部の変更、InnoDBのパラメーター変 遷、ICPが積極的に選ばれて憤死、良かれと思って設定した explicit_defaults_for_timestamp が略、 mysql_old_password 認証プラグ インがデフォルトでOFFられててあぼん 5.6 ⇒ 5.7 Condition Fanout Filterが変に選ばれて略、sql_modeの暗黙のデフォルト を変え忘れて死 24/108

sql_modeを弱い方に倒 せば「クエリーが通らな い！」で苦労した記憶はな かった There’s no problem if setting sql_mode empty :D 25/108

(4.0を除く) Expect of 4.0 26/108

が 27/108

8.0へのレプリケーションがガンガン止まる But versionup to 8.0 with replication, stop sql_thread sometimes.. あっ、ハイ、8.0でなくなるやつですよね NO_AUTO_CREATE_USER で5.7上でもエラーにできればいいのに…！ ‐ Last_Errno: 1064 Last_Error: Error 'You have an error in your SQL syntax; check th e manual that corresponds to your MySQL server version for the ri ght syntax to use near 'IDENTIFIED WITH 'mysql_native_password' A S '*4266488C892EA7950486FEC0A1CFFC1BD95' at line 1' on query. Def ault database: ''. Query: 'GRANT USAGE ON *.* TO 'yoku0825'@'%' I DENTIFIED WITH 'mysql_native_password' AS '*4266488C892EA7950486F EC0A1CFFC1BD9543F7B'' 28/108

8.0へのレプリケーションがガンガン止まる But versionup to 8.0 with replication, stop sql_thread sometimes.. あっ予約語だ！ binlog_format= ROW にする？ （これまでは MIXED ） ‐ 片っ端からオブジェクト名をバッククォートして回る？ 生SQLでなければこんな心配要らない予感 ‐ Last_SQL_Errno: 1064 Last_SQL_Error: Error 'You have an error in your SQL syntax; chec k the manual that corresponds to your MySQL server version for th e right syntax to use near 'rank) VALUES (4)' at line 1' on query . Default database: 'd1'. Query: 'INSERT INTO t1 (rank) VALUES (4 )' 29/108

8.0へのレプリケーションがガンガン止まる But versionup to 8.0 with replication, stop sql_thread sometimes.. 止まるかな？ と思って打ったら止まった。今は反省してい る。 FLUSH ステートメントの一部はバイナリーログに記録されるのです ‐ Last_SQL_Errno: 1064 Last_SQL_Error: Error 'You have an error in your SQL syntax; chec k the manual that corresponds to your MySQL server version for th e right syntax to use near 'QUERY CACHE' at line 1' on query. Def ault database: ''. Query: 'FLUSH QUERY CACHE' 30/108

orz 31/108

感想 My impression is あんまり長期間マスター5.7, スレーブ8.0の運用はしたくな い気分 8.0マスターならちゃんとエラーになるやつばっかり ‐ binlog_format= ROW に変えちゃったからもうわからないけ ど、予約語以外のDMLはフツーに流れていた いい加減（他の8.0関係ないところでも） ROW に変えよう… ‐ 32/108

感想 My impression is MySQL Shellの checkForServerUpgrade はやっておいて 損はない MySQL 5.6とそれ以前に対しても実行できる非公式なPerl5実装があ るらしいよ yoku0825/p5-mysql-upgrade-checker: Translation of MySQL Shell Upgrade Checker into Perl5 ‐ 33/108

ちょっと痛い目を見た のでちゃんとドキュメ ントを読んでみる Let’s read document.. 34/108

なくなった機能 のチェック Checking removed features. 35/108

なくなった機能 Removed features. Using GRANT to create users ✓ Using GRANT to modify account properties other than privilege assignments ✓ IDENTIFIED BY PASSWORD ‘hash_string’ syntax ✓ The PASSWORD() function. ✓ The old_passwords system variable. ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 36/108

なくなった機能 Removed features. The query cache was removed. ✓ These deprecated compatibility SQL modes have been removed ✓ The deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. ✓ The ENCODE() and DECODE() functions. The ENCRYPT() function. ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 37/108

なくなった機能 Removed features. the deprecated functions are removed to leave only the corresponding ST_ and MBR functions ✓ The mysql_install_db program has been removed ✓ The generic partitioning handler was removed ✓ Support for placing table partitions in shared InnoDB tablespaces was removed ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 38/108

なくなった機能 Removed features. The deprecated INFORMATION_SCHEMA INNODB_LOCKS and INNODB_LOCK_WAITS tables have been removed. ✓ Support for setting user variables in statements other than SET was deprecated in MySQL 8.0.13 ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 39/108

なくなった機能 Removed features. Using GRANT to create users ✓ Using GRANT to modify account properties other than privilege assignments ✓ IDENTIFIED BY PASSWORD ‘hash_string’ syntax ✓ The PASSWORD() function. ✓ The old_passwords system variable. ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 40/108

GRANT で直接ユーザーを作れなくなった No longer create user by GRANT statement 5.7からワーニングは出てたじゃろ？ GRANT でパスワードを変えたり MAX_USER_CONNECTIONS を変えたりも できなくなった ‐ IDENTIFIED BY PASSWORD は IDENTIFIED WITH .. AS .. に変わる ‐ SET PASSWORD 構文はもうイコールの後ろに PASSWORD() 関数を噛ま せられない ‐ mysql> CREATE USER yoku0825@'%' IDENTIFIED BY 'plain_password'; mysql> GRANT DRINK beer.* TO yoku0825@'%'; mysql> ALTER USER yoku0825@'%' IDENTIFIED WITH mysql_native_passw ord AS '*hashed_password'; mysql> SET PASSWORD FOR yoku0825@'%' = 'plain_password'; 41/108

なくなった機能 Removed features. The query cache was removed. ✓ These deprecated compatibility SQL modes have been removed ✓ The deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. ✓ The ENCODE() and DECODE() functions. The ENCRYPT() function. ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 42/108

なくなった機能 Removed features. The query cache was removed. ✓ These deprecated compatibility SQL modes have been removed ✓ The deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. ✓ The ENCODE() and DECODE() functions. The ENCRYPT() function. ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 43/108

互換SQLモード Compatibility SQL mode まあ知ってる人の方が少ないとは思う 最近MariaDBが sql_mode = oracle を使ってOracle互換の構文を～ とかやってた気がする ‐ mysql57 5> SET sql_mode = 'oracle'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql57 5> SELECT @@sql_mode; +------------------------------------------------------------------------------------------------ ----------------------+ | @@sql_mode | +------------------------------------------------------------------------------------------------ ----------------------+ | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTION S,NO_AUTO_CREATE_USER | +------------------------------------------------------------------------------------------------ ----------------------+ 1 row in set (0.00 sec) mysql80 15> SET sql_mode = 'oracle'; ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'oracle' 44/108

なくなった機能 Removed features. The query cache was removed. ✓ These deprecated compatibility SQL modes have been removed ✓ The deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. ✓ The ENCODE() and DECODE() functions. The ENCRYPT() function. ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 45/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause こんなデータがあるじゃろ？ mysql80 165> SELECT * FROM t1 LIMIT 10; +-----+-----+ | n | m | +-----+-----+ | 2 | 18 | | 4 | 7 | | 6 | 85 | | 1 | 69 | | 4 | 66 | | 7 | 52 | | 3 | 25 | | 2 | 19 | | 1 | 61 | | 5 | 80 | +-----+-----+ 10 rows in set (0.01 sec) 46/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause 今まではこうだったんじゃ mysql57 11> SELECT n, COUNT(m) FROM t1 GROUP BY n; +-----+----------+ | n | COUNT(m) | +-----+----------+ | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 100 | | 5 | 100 | | 6 | 100 | | 7 | 100 | | 8 | 100 | | 9 | 100 | | 10 | 100 | +-----+----------+ 10 rows in set (0.00 sec) 47/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause これからはこうなるんじゃ mysql80 165> SELECT n, COUNT(m) FROM t1 GROUP BY n; +-----+----------+ | n | COUNT(m) | +-----+----------+ | 2 | 100 | | 4 | 100 | | 6 | 100 | | 1 | 100 | | 7 | 100 | | 3 | 100 | | 5 | 100 | | 10 | 100 | | 9 | 100 | | 8 | 100 | +-----+----------+ 10 rows in set (0.00 sec) 48/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause 並べ替えのためにこんな構文ができたんじゃ mysql57 11> SELECT n, COUNT(m) FROM t1 GROUP BY n ASC; +-----+----------+ | n | COUNT(m) | +-----+----------+ | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 100 | | 5 | 100 | | 6 | 100 | | 7 | 100 | | 8 | 100 | | 9 | 100 | | 10 | 100 | +-----+----------+ 10 rows in set (0.00 sec) 49/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause 並べ替えのためにこんな構文ができたんじゃ mysql57 11> SELECT n, COUNT(m) FROM t1 GROUP BY n DESC; +-----+----------+ | n | COUNT(m) | +-----+----------+ | 10 | 100 | | 9 | 100 | | 8 | 100 | | 7 | 100 | | 6 | 100 | | 5 | 100 | | 4 | 100 | | 3 | 100 | | 2 | 100 | | 1 | 100 | +-----+----------+ 10 rows in set (0.00 sec) 50/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause 並べ替えをさせない（ソートのオーバーヘッドをかっ飛ば す）ために ORDER BY NULL なんてテクニックもあったのじゃ mysql57 11> SELECT n, COUNT(m) FROM t1 GROUP BY n ORDER BY NULL; +-----+----------+ | n | COUNT(m) | +-----+----------+ | 2 | 100 | | 4 | 100 | | 6 | 100 | | 1 | 100 | | 7 | 100 | | 3 | 100 | | 5 | 100 | | 10 | 100 | | 9 | 100 | | 8 | 100 | +-----+----------+ 10 rows in set (0.00 sec) 51/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause SQL的には自然な方になったんだけれども レポート系で1ファイルにベロっと吐き出してるのとかは 「順番がああああ」ってなりそうだけど、スプレッドシート （ｳｯ）とかに詰めてるなら問題ないかしらん…と思っていま す 単に「MySQLでSQLを覚えたのでそういうもんだと思って 生きてきた」人が多いんじゃないかなあという気がする 仕様だとまでは思ってなかったけど、そうでなくなると違和 感をおぼえる、みたいな？ 52/108

なくなった機能 Removed features. the deprecated functions are removed to leave only the corresponding ST_ and MBR functions ✓ The mysql_install_db program has been removed ✓ The generic partitioning handler was removed ✓ Support for placing table partitions in shared InnoDB tablespaces was removed ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 53/108

mysql_install_dbからmysqld –initializeへ これも5.7からdeprecated warningが出ていたはず これは5.7時代から結構人口に膾炙していたのではなかろうか ‐ 日々の覚書: MySQL 5.7.6でデータベースの初期化が変わる mysql_install_dbからmysqld –initialize ‐ $ mysqld --no-defaults --initialize .. 2018-11-07T09:32:45.375558Z 4 [Note] [MY-010454] [Server] A tempo rary password is generated for root@localhost: lP?G,yjh+9eX 2018-11-07T09:32:47.029929Z 0 [System] [MY-013170] [Server] /usr/ mysql/8.0.13/bin/mysqld (mysqld 8.0.13) initializing of server ha s completed 54/108

なくなった機能 Removed features. the deprecated functions are removed to leave only the corresponding ST_ and MBR functions ✓ The mysql_install_db program has been removed ✓ The generic partitioning handler was removed ✓ Support for placing table partitions in shared InnoDB tablespaces was removed ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 55/108

なくなった機能 Removed features. The deprecated INFORMATION_SCHEMA INNODB_LOCKS and INNODB_LOCK_WAITS tables have been removed. ✓ Support for setting user variables in statements other than SET was deprecated in MySQL 8.0.13 ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 56/108

なくなった機能 Removed features. The deprecated INFORMATION_SCHEMA INNODB_LOCKS and INNODB_LOCK_WAITS tables have been removed. ✓ Support for setting user variables in statements other than SET was deprecated in MySQL 8.0.13 ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 57/108

SET 以外でユーザー変数をセットすること Setting user variables in statements other than SET 計算のタイミングが保障されないからサイレントに事故るこ とがあったり、ステートメントベースでレプリケーションア ンセーフだったりするからだと思うけど。 mysql80 15> SELECT @a := @a + 1; +--------------+ | @a := @a + 1 | +--------------+ | 2 | +--------------+ 1 row in set, 1 warning (0.00 sec) mysql80 15> SHOW WARNINGS; +---------+------+----------------------------------------------------------------------------------------------------------------- --------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------- --------------------------------------+ | Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Please set vari ables in separate statements instead. | +---------+------+----------------------------------------------------------------------------------------------------------------- --------------------------------------+ 1 row in set (0.00 sec) 58/108

SET 以外でユーザー変数をセットすること Setting user variables in statements other than SET INTO は良いっぽい まあ INTO は結果セットが複数行だとエラーになるしな ‐ mysql80 15> SELECT @a + 1 INTO @a; Query OK, 1 row affected (0.00 sec) mysql80 15> SELECT @a; +-----+ | @a | +-----+ | 3 | +-----+ 1 row in set (0.00 sec) 59/108

SET 以外でユーザー変数をセットすること Setting user variables in statements other than SET というわけで↓こんなのは今後エラーになる…と 便利だったんだけどなぁ(´･ω･｀) 日々の覚書: MySQL 5.7.6のPerformance SchemaでInnoDBのALTER TABLE進 捗どうですか ‐ って言ってもアプリケーションコードで使うことはそう無いか ‐ 同じ行の中で計算してるやつはアプリケーションコードに、 複数行にまたがってなんかしてるやつはCTEかWindow関数 に SELECT thread_id, event_name, sql_text, @progress:= (work_completed / work_estimated) * 100 AS progress, @elapsed:= (timer_current - timer_start) / power(10, 12) AS elapsed, @elapsed * (100 / @progress) - @elapsed AS estimated FROM .. 60/108

それ以外 61/108

話題になった認証プラグイン Changed default_authentication_plugin デフォルトが mysql_native_password から caching_sha2_password に変更 MySQL 5.7.22とそれ以前のクライアントはこのプラグインを持って いない 5.7.23とそれ以降はクライアントはこのプラグインを持っている（サーバーは 持っていない） MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.23 (2018-07-27, General Availability) ‐ $ mysql56 -S /usr/mysql/8.0.13/data/mysql.sock ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/mysql/5.6.42/lib/plugin/caching_sha2_pass word.so: cannot open shared object file: No such file or director y 62/108

caching_sha2_password 「最初の1回」はSSL接続、ソケット接続、RSA鍵でパス ワード交換などの「セキュアなコネクション」で接続しない といけない FLUSH PRIVILEGES や mysqld の再起動の都度「最初の1回」 がやってくるため、これに切り替えたらもうAPサーバーは 常に「セキュアなコネクション」で接続するようにしないと いけない caching_sha2_password vs. mysql_native_password はそん なにコネクション速度劣化はないけど、 SSL vs. 非SSL の間 には大きな溝が横たわっている 63/108

caching_sha2_password MYSQL_OPT_GET_SERVER_PUBLIC_KEY は便利だけど実装に波が ある コネクターのバージョンが先行するまでは default_authentication_plugin= mysql_native_password かな… 64/108

MySQL 8.0 client libraries language module Using Connecor/ C? caching_sha2_pa ssword? MYSQL_OPT_GET _SERVER_PUBLI C_KEY? Perl DBD::mysql Yes Yes Yes PHP mysqlnd No Yes No Ruby mysql2 Yes Yes Yes Python PyMySQL No Yes Yes Python MySQLdb Yes Yes No 公式系は割愛した 65/108

utf8mb4 我々日本人にはほとんど影響はないはず 吊るしのまま character_set_server= latin1 とかそんなにないじゃ ろ？ ‐ 3バイトの utf8mb3(utf8) のままだったのはままありそう 3バイトの utf8mb3 はワーニング出るようになったのでちゃんと拾ってあげてね ‐ $ perror ER_DEPRECATED_UTF8_ALIAS MySQL error code MY-003719 (ER_DEPRECATED_UTF8_ALIAS): 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8 MB4 in order to be unambiguous. 66/108

ちなみにutf8mb3とutf8mb4を混ぜると Attention for mixing utf8mb3 and utf8mb4 そのカラムを使ってJOINするとインデックスが使えなく なったりするので慎重に 結果は変わらない。だが、実行計画は大きく違う。 これはデータが少ないうちは気づきにくく、データ量が 増えてから「あーーー！！！」となるパターン。 テーブルやカラムのCollationは揃えよう - 41から始めまし た 67/108

kamipoの「ハハ=パパ」問題 kamipo’s father = mother issue こっちはデフォルトのままだと影響アリアリ mysql> SELECT * FROM families; +-----+--------+--------------+ | id | name | relationship | +-----+--------+--------------+ | 1 | ユイ | 本人 | | 2 | キリト | パパ | | 3 | アスナ | ハハ | +-----+--------+--------------+ 68/108

kamipoの「ハハ=パパ」問題 kamipo’s father = mother issue SELECT * FROM families WHERE relationship = 'パパ'; +-----+--------+--------------+ | id | name | relationship | +-----+--------+--------------+ | 2 | キリト | パパ | | 3 | アスナ | ハハ | +-----+--------+--------------+ 69/108

多くは語るまい That had been already discussed enough collation mysql = MySQL ハハ != パパ びょういん ! = びようい ん != MySQL = Ｍ ｙＳＱＬ = != ≠ utf8mb4 _genera l_ci o o o x x o utf8mb4 _0900_ ai_ci o x x o o x utf8mb4 _ja_090 0_as_cs x o o o o o utf8mb4 _bin x o o o x o 70/108

新顔のいこーるいこーるのっといこーる問題 EQUAL-symbol equals NOT_EQUAL-symbol mysql80 32> SELECT '=' = '≠'; +-------------+ | '=' = '≠' | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) MySQL Bugs: #92899: Incorrect uniqueness determination with equality symbols もちろん “Not a bug” ハハ = パパに比べればまだ笑える（のか？） ‐ 71/108

もういちど Once again collation mysql = MySQL ハハ != パパ びょういん ! = びようい ん != MySQL = Ｍ ｙＳＱＬ = != ≠ utf8mb4 _genera l_ci o o o x x o utf8mb4 _0900_ ai_ci o x x o o x utf8mb4 _ja_090 0_as_cs x o o o o o utf8mb4 _bin x o o o x o 72/108

MySQL 8.0を起動すると開かれる謎のポート Unknown listening port..? # ss -ltpn | grep mysql LISTEN 0 128 :::3306 :::* users:(("mysqld",pid=10363,fd=30)) LISTEN 0 70 :::33060 :::* users:(("mysqld",pid=10363,fd=33)) # mysqld --help --verbose | grep 33060 mysqlx-port 3306 0 73/108

MySQL 8.0ではポー ト番号が11倍にアッ プ！（？） MySQL 8.0 increases port- number x11 times! 74/108

MySQLXがデフォルトでLISTENするようになってる mysqlx listens 33060 by default 1つのサーバーに複数のMySQL 8.0を起動しようとするなら OFFにするかポートを変えておかないとバッティングしちゃ う 最近ソケットも作るようになった… ‐ ちなみにバッティングしてもエラー吐くだけで起動する 2018-11-15T10:09:35.131783Z 0 [System] [MY-010931] [Server] /usr/ mysql/8.0.13/bin/mysqld: ready for connections. Version: '8.0.13' socket: '/usr/mysql/8.0.13/data/mysql.sock' port: 64080 Sourc e distribution. 2018-11-15T10:09:36.372022Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of bind-address: '*' port: 33060 failed, `bind()` failed with error: Address already in use (98). Do you already have another mysqld server running with Mysqlx ?' 2018-11-15T10:09:36.372089Z 0 [System] [MY-011323] [Server] X Plu gin ready for connections. Socket: '/tmp/mysqlx.sock' 75/108

LOAD DATA LOCAL INFILE これ結構ググって日々の覚書にたどり着く人が多いようなん ですけどみんな意外と使ってるんですね mysql80 125> LOAD DATA LOCAL INFILE '/tmp/aaa' INTO TABLE t1; ERROR 1148 (42000): The used command is not allowed with this MyS QL version 76/108

LOAD DATA LOCAL INFILE LODA DATA LOCAL INFILE を実行するには2つの条件が必 要で、 LOAD DATA LOCAL INFILE を実行するコネクションに CLIENT_LOCAL_FILES ケーパビリティー（オプションだと思っ て）が設定されていること ‐ サーバー側で opt_local_infile が設定されていること ‐ 日々の覚書: MySQL 8.0でLOAD DATA LOCAL INFILEが “ERROR 1148 (42000): The used command is not allowed with this MySQL version” で失敗する時 77/108

innotop 78/108

innotop run innotop with MySQL 8.0.3 ✓ press any keys for switching to InnoDB modes,such as D for InnoDB Deadlocks ✓ innotop will crash with error Use of uninitialized value $text in pattern match (m//) at /usr/bin/ innotop line 620. ✓ Switching to InnoDB modes causes to crash with MySQL 8.0.3 · Issue #162 · innotop/innotop 79/108

innotop 前にMySQL 5.7対応ができてないことに気付いてブログ書 いた時、結構「使ってる」「困る」みたいな反応をTwitter で見かけた 日々の覚書: innotopが最近息してないなーと思ったんだ ‐ 大体にして俺が困る 5.7対応だって俺が困るからパッチ書いたわけだし ‐ 80/108

innotop Innotop for MySQL 8.0 – lefred’s blog: tribulations of a MySQL Evangelist Support MySQL 8.0 by yoku0825 · Pull Request #167 · innotop/innotop あー、epelへのパッケージングリクエスト出さないとな… 81/108

innotop 82/108

innotop 83/108

mikasafabri略 SHOW GRANTS の結果が変わった件で8.0で動かなかったんだ けど直した 日々の覚書: MySQL 8.0.4の SHOW GRANTS の結果が想像したの とちょっと違う ‐ gmo-media/mikasafabric: mikasafabric for MySQL is fork product of MySQL Fabric. ‐ 84/108

ここまで乗り越 えて Beyond the problems 85/108

取り敢えず無事 動いてます Stability running at now. 86/108

取り敢えず無事に（？）動いてます Stability..? schema1(slave) schema2(slave) schema1(slave) schema2(slave) schema3(master) schema1(master) schema2(master) 56_slave 80_master 56_master 87/108

だいぶやっち まった感… Oops.. 88/108

やっちまったけど（既に）これは便利だったもの Oops but these are useful features I already used. ROLE JOIN_ORDERヒント INSTANT Add column 89/108

今まで Before 某密林の弾性電算雲とか使ってればセキュリティーグループ で制限かけられるんだけど… mysql> CREATE USER apuser@apserver1; mysql> GRANT ALL ON ap_db.* TO apuser@apserver1; mysql> CREATE USER apuser@apserver2; mysql> GRANT ALL ON ap_db.* TO apuser@apserver2; mysql> CREATE USER apuser@apserver3; mysql> GRANT ALL ON ap_db.* TO apuser@apserver3; .. 90/108

今まで Before 「すいません、新機能はスキーマを分けて開発することに なったので、既存のアカウントが新スキーマにもアクセスで きるように設定してください」 ( ﾟдﾟ) えっ mysql> GRANT ALL ON new_db.* TO apuser@apserver1; mysql> GRANT ALL ON new_db.* TO apuser@apserver2; mysql> GRANT ALL ON new_db.* TO apuser@apserver3; .. 91/108

今まで Before 「すいません、新機能はサーバーもわけることになりまし た。既存のアカウントは今まで通りap_dbだけ、新サーバー 用のアカウントはap_dbの読み取りとnew_dbの読み書き権 限を」 ( ﾟдﾟ) えっえっ mysql> REVOKE ALL ON new_db.* FROM apuser@apserver1; mysql> REVOKE ALL ON new_db.* FROM apuser@apserver2; mysql> REVOKE ALL ON new_db.* FROM apuser@apserver3; .. mysql> CREATE USER apuser@apserver11; mysql> GRANT ALL ON new_db.* TO apuser@apserver11; mysql> GRANT SELECT ON ap_db.* TO apuser@apserver11; .. 92/108

今まで Before 「すいませんやっぱりスキーマの名前を」 ( д ) ﾟ ﾟ ﾌｧｰ 93/108

ROLEがあると After introducing role mysql> CREATE ROLE ap_rw; mysql> GRANT ALL ON ap_db.* TO ap_rw; mysql> CREATE USER apuser@apserver1 DEFAULT ROLE ap_rw; mysql> CREATE USER apuser@apserver2 DEFAULT ROLE ap_rw; mysql> CREATE USER apuser@apserver3 DEFAULT ROLE ap_rw; .. 94/108

ROLEがあると After introducing role mysql> GRANT ALL ON new_db.* TO ap_rw; mysql> REVOKE ALL ON new_db.* FROM ap_rw; mysql> CREATE ROLE ap_ro; mysql> GRANT SELECT ON ap_db.* TO ap_ro; mysql> CREATE ROLE new_rw; mysql> GRANT ALL ON new_db.* TO new_rw; mysql> GRANT ap_ro TO new_rw; mysql> CREATE USER apuser@apserver11 DEFAULT ROLE new_rw; .. 95/108

ROLE IPアドレス単位でアカウントを作成している現場にはとても 便利だと思う MySQLアカウントのIPアドレスの制限をせずに、セキュリティーグ ループとかで制限する環境には特に嬉しくもないかも ‐ ちなみに内部的にユーザーとロールはあんまり区別されてな いっぽいので色々試してると面白い GRANT root@localhost TO yoku0825 とかやっておくと、 SET ROLE root@localhost とかできる ‐ ついさっきレポートした MySQL Bugs: #93263: DROP ROLE username should be rejected ‐ 96/108

JOIN_ORDERヒント mysql80 18> EXPLAIN SELECT /*+ JOIN_ORDER (CountryLanguage, Country) */ Name, Language, Population, Percent age -> FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode -> WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5; +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+---- -------------------------------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+---- -------------------------------+------+----------+----------------+ | 1 | SIMPLE | CountryLanguage | NULL | ALL | PRIMARY,CountryCode | NULL | NULL | NUL L | 984 | 100.00 | Using filesort | | 1 | SIMPLE | Country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | wor ld.CountryLanguage.CountryCode | 1 | 14.29 | Using where | +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+---- -------------------------------+------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec) 97/108

JOIN_ORDERヒント mysql80 18> EXPLAIN SELECT /*+ JOIN_ORDER (Country, CountryLanguage) */ Name, Language, Population, Percent age -> FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode -> WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5; +-----+-------------+-----------------+------------+------+---------------------+------+---------+------+-- ----+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | r ows | filtered | Extra | +-----+-------------+-----------------+------------+------+---------------------+------+---------+------+-- ----+----------+----------------------------------------------------+ | 1 | SIMPLE | Country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2 39 | 14.29 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | CountryLanguage | NULL | ALL | PRIMARY,CountryCode | NULL | NULL | NULL | 9 84 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +-----+-------------+-----------------+------------+------+---------------------+------+---------+------+-- ----+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) 98/108

FIXED_JOIN_ORDERヒント mysql80 18> EXPLAIN SELECT /*+ JOIN_FIXED_ORDER() */ Name, Language, Population, Percentage -> FROM Country, CountryLanguage -> WHERE Country.Code= CountryLanguage.CountryCode AND Country.continent = 'Asia' ORDER BY Percenta ge LIMIT 5; +-----+-------------+-----------------+------------+------+---------------------+------+---------+----- -+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-----+-------------+-----------------+------------+------+---------------------+------+---------+----- -+------+----------+----------------------------------------------------+ | 1 | SIMPLE | Country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 14.29 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | CountryLanguage | NULL | ALL | PRIMARY,CountryCode | NULL | NULL | NULL | 984 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +-----+-------------+-----------------+------------+------+---------------------+------+---------+----- -+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) 99/108

FIXED_JOIN_ORDERヒント mysql80 18> EXPLAIN SELECT /*+ JOIN_FIXED_ORDER() */ Name, Language, Population, Percentage -> FROM CountryLanguage, Country -> WHERE Country.Code= CountryLanguage.CountryCode AND Country.continent = 'Asia' ORDER BY Percenta ge LIMIT 5; +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+ -----------------------------------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+ -----------------------------------+------+----------+----------------+ | 1 | SIMPLE | CountryLanguage | NULL | ALL | PRIMARY,CountryCode | NULL | NULL | NULL | 984 | 100.00 | Using filesort | | 1 | SIMPLE | Country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.CountryLanguage.CountryCode | 1 | 14.29 | Using where | +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+ -----------------------------------+------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec) 100/108

JOIN_ORDERヒント句 ついに `OUTER JOIN` でも `ORDER BY狙いのキー` を狙 いやすく…！ WHERE 句で外部表のカラム（↑の場合は Country.continent ）を評価してて、実質 INNER JOIN 相当 になっている時だけだった…_|￣|○ 実質 INNER JOIN になってない場合はナチュラルに無視される ‐ USE INDEX と違って存在しない名前を指定しても無視されるだけでエ ラーにはならない ‐ 101/108

INSTANT ADD COLUMN 100万行, 100MBくらいのテーブルに対して mysql80 20> ALTER TABLE t1 ADD c1 int, ALGORITHM= INSTANT; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql80 20> ALTER TABLE t1 ADD c2 int, ALGORITHM= INPLACE; Query OK, 0 rows affected (3.43 sec) Records: 0 Duplicates: 0 Warnings: 0 102/108

INSTANT ADD COLUMN ALGORITHM = INSTANT ----system---- ----total-cpu-usage---- -dsk/total- ---system-- ------mem ory-usage----- --io/total- | time | usr sys idl wai hiq siq | read writ | int csw | used buff cach free | read writ | | 20-11 16:44:56 | 2 1 97 1 0 0 | 0 6409k | 877 996 | 692M 0 172M 127M | 0 222 | | 20-11 16:44:57 | 3 1 96 0 0 0 | 128k 394k | 1545 2509 | 692M 0 172M 127M | 8.00 63.0 | | 20-11 16:44:58 | 2 0 98 0 0 0 | 0 6410k | 901 982 | 692M 0 172M 127M | 0 225 | | 20-11 16:44:59 | 2 1 97 1 0 0 | 3260k 972k | 908 927 | 692M 0 175M 124M | 54.0 41.0 | | 20-11 16:45:00 | 2 0 98 0 0 0 | 0 0 | 641 772 | 692M 0 175M 124M | 0 0 | 17338 buffer_pages_written 2018-11-20 16:44:56 17347 buffer_pages_written 2018-11-20 16:44:57 17547 buffer_pages_written 2018-11-20 16:44:58 17577 buffer_pages_written 2018-11-20 16:44:59 17577 buffer_pages_written 2018-11-20 16:45:00 103/108

INSTANT ADD COLUMN ALGORITHM = INPLACE ----system---- ----total-cpu-usage---- -dsk/total- ---system-- ------memory-usage----- --io/total- | time | usr sys idl wai hiq siq | read writ | int csw | used buff ca ch free | read writ | | 20-11 16:45:00 | 2 0 98 0 0 0 | 0 0 | 641 772 | 692M 0 17 5M 124M | 0 0 | | 20-11 16:45:01 | 35 5 49 10 0 0 | 14M 78M | 14k 34k | 692M 0 17 5M 124M | 897 2150 | | 20-11 16:45:02 | 13 5 49 32 0 0 | 37M 51M | 9577 16k | 701M 0 20 5M 85.1M | 1213 3318 | | 20-11 16:45:03 | 19 8 49 23 0 0 | 14M 51M | 12k 18k | 693M 0 20 6M 91.7M | 245 5287 | | 20-11 16:45:04 | 3 2 85 10 0 0 | 6028k 31M | 6985 13k | 693M 0 21 1M 87.7M | 142 3098 | | 20-11 16:45:05 | 2 0 98 0 0 0 | 0 419k | 681 817 | 693M 0 21 1M 87.7M | 0 20.0 | 17577 buffer_pages_written 2018-11-20 16:45:00 18047 buffer_pages_written 2018-11-20 16:45:01 19139 buffer_pages_written 2018-11-20 16:45:02 21147 buffer_pages_written 2018-11-20 16:45:03 21591 buffer_pages_written 2018-11-20 16:45:04 21604 buffer_pages_written 2018-11-20 16:45:05 104/108

INSTANT ADD COLUMN どういう黒魔術使ってるんだってくらい速い ちなみに「メタデータだけゴニョって、実際に値がUPDATEされるま でデータはメタデータの中にしかない」状態にする黒魔術 ‐ バッファプールも全然触らなくてすごい 楽しくて笑えてくるレベル ‐ ENUM型の定義変更も ALGORITHM = INSTANT でできるらしい ですよ！！！１ 105/108

（実務に）試せてないけど使いたい Maybe useful, but I didn’t use yet 式インデックス 式デフォルト リソースグループ JSON_TABLE ヒストグラム 106/108

おしらせ Information まだまだお席に余裕がございます Tomas, Mike and Luis? Kajiyama-san and Yamasaki-san have a duty. ‐ MySQL Casual Advent Calendar 2018 \- Qiita 107/108

Any Questions and/or Suggestions? 108/108