手が挙がらなかった人はみん な既に本番に突っ込んだって ことでいいですか？ :D People who didn’t raise their hand, has already introduced 8.0 into Production Environment, isn’t it? :D 12/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
感想 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
なくなった機能 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 [email protected]'%' IDENTIFIED BY 'plain_password'; mysql> GRANT DRINK beer.* TO [email protected]'%'; mysql> ALTER USER [email protected]'%' IDENTIFIED WITH mysql_native_passw ord AS '*hashed_password'; mysql> SET PASSWORD FOR [email protected]'%' = '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
なくなった機能 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 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
なくなった機能 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
話題になった認証プラグイン 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
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
多くは語るまい 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
もういちど 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
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 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 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
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
今まで Before 「すいません、新機能はスキーマを分けて開発することに なったので、既存のアカウントが新スキーマにもアクセスで きるように設定してください」 ( ﾟдﾟ) えっ mysql> GRANT ALL ON new_db.* TO [email protected]; mysql> GRANT ALL ON new_db.* TO [email protected]; mysql> GRANT ALL ON new_db.* TO [email protected]; .. 91/108
今まで Before 「すいません、新機能はサーバーもわけることになりまし た。既存のアカウントは今まで通りap_dbだけ、新サーバー 用のアカウントはap_dbの読み取りとnew_dbの読み書き権 限を」 ( ﾟдﾟ) えっえっ mysql> REVOKE ALL ON new_db.* FROM [email protected]; mysql> REVOKE ALL ON new_db.* FROM [email protected]; mysql> REVOKE ALL ON new_db.* FROM [email protected]; .. mysql> CREATE USER [email protected]; mysql> GRANT ALL ON new_db.* TO [email protected]; mysql> GRANT SELECT ON ap_db.* TO [email protected]; .. 92/108
ROLEがあると After introducing role mysql> CREATE ROLE ap_rw; mysql> GRANT ALL ON ap_db.* TO ap_rw; mysql> CREATE USER [email protected] DEFAULT ROLE ap_rw; mysql> CREATE USER [email protected] DEFAULT ROLE ap_rw; mysql> CREATE USER [email protected] 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 [email protected] DEFAULT ROLE new_rw; .. 95/108
ROLE IPアドレス単位でアカウントを作成している現場にはとても 便利だと思う MySQLアカウントのIPアドレスの制限をせずに、セキュリティーグ ループとかで制限する環境には特に嬉しくもないかも ‐ ちなみに内部的にユーザーとロールはあんまり区別されてな いっぽいので色々試してると面白い GRANT [email protected] TO yoku0825 とかやっておくと、 SET ROLE [email protected] とかできる ‐ ついさっきレポートした MySQL Bugs: #93263: DROP ROLE username should be rejected ‐ 96/108
yoku0825
masayaaoyama
os1ma
fabpot
blackbracken
77web
speed1313
mthiroshi00excite
yutootsuka
utgwkk
codehex
yuhei_fujita
taclose
jonrohan
sugarenia
lauravandoore
shpigford
revolveconf
sferik
colly
frogandcode
jennybc
destraynor
danielanewman
jasonvnalue