41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

MySQL Server8.0.29では何が起きたのか?(リリースノートを眺める)

これは MySQL Advent Calendar 2022 8 日目の記事です。

はじめに

MySQL Server 8.0.29はGAであるにもかかわらず、今はダウンロードすることができません。

(なんかそれ自体闇歴史の最後を飾っても良い話な気がしてきた。きっと誰かが書くだろうw)

それは8.0.29でALGORITHM=INSTANTを使用して追加(削除も含む)されたカラムを持つInnoDBテーブルのデータが正しく解釈されないという重大な問題があるためです。

具体的にはどのような話なのでしょう?

バグの詳細については多くがセキュリティの点から非公開となっているので、公開されているリリースノート等で見ていきましょう。

(リリースノートの和訳についてはDeepLを使ってそのままのものを記載してます。

 わかりにくいからと言ってどういうことかを説明すると書いてはいけないことを

 書いてしまうかもしれませんので、申し訳ないですが英語か翻訳から感じ取ってください)

リリースノートを眺める

現在、リリースノートは8.0.29以降では8.0.308.0.31のものが出ています。

8.0.30のリリースノートより

InnoDB: A TRUNCATE TABLE operation failed to remove data dictionary entries for columns that were dropped using ALGORITHM=INSTANT. Thanks to Marcelo Altmann for the contribution. (Bug #34302445)

訳:TRUNCATE TABLE操作で、ALGORITHM=INSTANTを使用して削除された列のデータ辞書エントリーを削除できない。

ちなみにMarcelo AltmannはOracle ACEでPercona社の人です。この8.0.29とPercona XtraBackupの非互換性の記事や、このバグについての話も書いてますね。

InnoDB: An incorrect nullable column calculation on tables with instantly added columns caused data to be interpreted incorrectly. (Bug #34243694)

訳:即座に追加されるカラムを持つテーブルで、不正なNullableカラムの計算が行われ、データが不正に解釈されることがありました。

InnoDB: After upgrading to MySQL 8.0.29, a failure occurred when attempting to access a table with an instantly added column. (Bug #34233264)

訳:MySQL 8.0.29 にアップグレードした後、即座に追加されたカラムを持つテーブルにアクセスしようとすると失敗することがありました。

InnoDB: Only the physical position of instantly added columns was logged, which was not sufficient for index recovery. The logical position of columns was also required. (Bug #34181432)

訳:即座に追加された列の物理的な位置のみが記録され、インデックスの復元には不十分であった。カラムの論理位置も必要であった。

8.0.31のリリースノートより

InnoDB: After upgrading to a release that supports row versions for columns added or dropped using ALGORITHM-INSTANT, a failure occurred during an instant ADD COLUMN operation on a table with a nullable column and an instantly added column. (Bug #34488482)

訳:ALGORITHM-INSTANT を使用して追加または削除された列の行バージョンをサポートするリリースにアップグレードすると、NULL 可能な列とINSTANTで追加される列を持つテーブルで(ALGORITHM)INSTANT ADD COLUMN 操作中に失敗が発生しました。

InnoDB: A column added using ALGORITHM=INSTANT was visible in read views created before the DDL operation that added the column. (Bug #33937504)

訳:ALGORITHM=INSTANT を使用して追加された列が、列を追加した DDL 操作の前に作成された読み取りビューに表示されていました。

今回の騒動を受けて僕個人が思う事

これはあくまで個人的に思ってることで、会社は一切関係無いんでそこのところだけはご理解ください。

機能追加はマイナーバージョンでするもんじゃない(少なくともGAになってからは)

今回のことがあるまではMySQL何でこんなアップグレード時に機能ぶっこんでくるんだよ!バグが生まれ続けるじゃねーか…、

などと思いつつも、便利になるんなら仕方ないのかなとも思ってたんです。

でも、リリースしたものをダウンロードできなくしたって言うのはあまりにもカッコ悪い。

やはり「機能追加はマイナーバージョンでするもんじゃない」です。(他のRDBMSではあまり見ないですしね)

最新=Bestじゃなくなった

そのせいで気軽にアップグレードしてくださいを言いにくくなりましたよ。

もちろん、今は8.0.31まであるんで特定のバグを解消する方法がアップグレードするしかないんならそれをお勧めしますよ。

しかし新機能が追加されてたら、そいつのせいでまた何か起きるんじゃないかとユーザーは疑心暗鬼になるし、僕としても絶対に無いとは言えないのでそれについてはどうしようもない。

信用をこれ以上失わないためにも今後は新機能追加とか勘弁願いたいし、やるなら8.1だとか9.0だとか別バージョン切ってやって欲しいですわ。

次の8.0.32は1月頃でしょうかね。そこでも何らかの情報は上がってくるかもしれませんが、改良はしても新機能追加だけは勘弁。

yoku0825さん、あなたはホンマに凄い!

この先見の明と野生の勘に中の飼育員もアッパレでした。

ぼくらが選んだ次のMySQL 8.0 / MySQL80 Which We Choose - Speaker Deck

INSTANT DROP COLUMN(と、その影響で任意の個所にINSTANT ADD COLUMNできるようになった)はヤバい

最後に

意外と8.0.29を使っている人を問合せで見かけます。

それは氷山の一角で、サポート外の人たちを含めたら相当数なんじゃないかと思ってます。

早急にアップグレードを検討することをお勧めします。

すぐにはアップグレードが難しければALGORITHM=INSTANT を使用してカラム追加は避けてください。

明日は そんな先見の明と野生の勘を持つ、【現役Oracle ACE】の yoku0825 さんです!

Workbench 8.0.23でPerformance ReportsとPerformance Schema Setupが見えなくなった

いきなり結論

MySQL Workbench 8.0.23以降では以下の画面が表示できません。

  • Performance Reports
  • Performance Schema Setup

まだ直ってなくて、使いたい場合は8.0.22以下にする必要があります。

そもそもPerformance Reportsって?

以下のドキュメントに詳しく書いてあります。

dev.mysql.com

sysスキーマから情報引っ張ってパフォーマンス情報を引っ張ってこれるっていう代物です。

実際に見てみる

8.0.22だと

8.0.22のPR

表示されます

8.0.23以降だと

8.0.23のPR

見えません(なんでやねん!)

解決方法

今のところ8.0.31でも見えないですね…。

バグ登録は既に行ってますが、いつ直るかは不明です。

(ってか、気づかれてなかったってことは誰もこの機能使ってなかったんだな…)

最初に書きましたが、今のところは8.0.22に戻すしか無いです。

まあ、ツヨツヨな人には不要な機能なんでしょうね。

明日は @bluerabbit さんです。

8.0.29では空間インデックスが壊れます

はじめに

このエントリは、RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2022 の4日目です。

MySQLGIS関連のお話です。

簡単なネタですが書かせてもらいました。

MySQL Server 8.0.29は色々と問題のあるバージョンで、今はダウンロードすることは出来ず、もし現在使用している人は早急に8.0.30にアップグレードすることを強くお勧めします。

その問題はなんとGIS界にも及んでしまいました。

何が起きた?

8.0.29では空間インデックス(Spatial Indexes)を使うはずのクエリが、フルテーブルスキャンになってしまいます。

具体例はバグレポート(107320)を見てください。

対応方法

8.0.28以下の場合

このバグには当たらないので大丈夫です

8.0.29の場合

8.0.30以上にアップグレードしましょう。

but...

そんなすぐにはアップグレードできないよーって人はFORCE INDEXを指定して対象のインデックスを強制的に使わせましょう。

8.0.30以上の場合

8.0.30で修正されたので大丈夫です

Upgrading to MySQL 8.0.29 led to issues with existing spatial indexes (see Creating Spatial Indexes). The root cause of the problem was a change in how geographic area computations were performed by the included Boost library, which was upgraded to version 1.77.0 in MySQL 8.0.29. We fix this by ensuring that we accommodate the new method whenever such computations are performed. (Bug #107320, Bug #34184111)

最後に

いつも仕事がらアップグレードをお願いしてるんですが、8.0.29のせいでうかつに最新にしろ!って言いにくくなりました。

でも、いつまでも古いバージョンを使っているとさすがにバグが直ってないままですので、皆さんは少し様子を見つつもなるべく新しいのを使うようにしてくださいね。

明日は @yyamasaki1 さんです。

MDSでダンプ・インポートするときの小ネタ

大した話では無いんですが、ドキュメントやヘルプ見てもよくわからないと思うので共有です。

エクスポート時にバケット内のフォルダ配下にエクスポートしたい場合

例えばdumpSchemas()ならこんな風に書きます。

JS > util.dumpSchemas(["test"], "home/test", {"osBucketName": "bucket-xxxxx", "osNamespace": "xxxxxx", ...)

大事なのは2個目の"home/test"のところ。

osBucketNameで指定したbucket-xxxxxのところに home > test というフォルダができ、その下にエクスポートファイル群が格納されます。

ここで/home/testなんてやると / > home > test という階層になるので注意w

インポートをバケット内の特定のフォルダ配下から行いたい場合

インポート時も同じような感じ。

JS > util.loadDump("home/test", {threads: 8, osBucketName: "bucket-xxxxx", osNamespace: "xxxxxx"...})

includeTablesやexcludeTablesでデータベース名も一緒に指定したい

loadDump()でexcludeTablesとincludeTablesというオプションがあります。

includeTablesはダンプファイルから指定されたテーブルのみをロードし、 excludeTablesは指定したテーブルをインポートから除外するものです。

しかし、

JS > util.loadDump("home/20220310", {threads: 8, osBucketName: "bucket-xxxxx", osNamespace: "xxxxxx", includeTables:["test.t1"]})

と書くと上手くインポートされません。

ERROR: [Worker000] While executing DDL script for `test`.`t1`: Unknown database 'test'
ERROR: Aborting load...

が出たり、事前にtestデータベースを作っていても対象のt1テーブルは作成されないままです。

この場合、データベース名とテーブル名それぞれにバッククォートをつけてあげてください。

JS > util.loadDump("home/20220310", {threads: 8, osBucketName: "bucket-xxxxx", osNamespace: "xxxxxx", includeTables:["`test`.`t1`"]})

ドキュメント

MySQL :: MySQL Shell 8.0 :: 8.5 インスタンスダンプユーティリティ、スキーマダンプユーティリティおよびテーブルダンプユーティリティ

MySQL :: MySQL Shell 8.0 :: 8.6 ダンプロードユーティリティ

8.0のテーブルを5.7のFEDERATEDテーブルから参照するとエラー

タイトルの件

MySQL8.0に作られたテーブルをMySQL5.7のFEDERATEDテーブルから参照すると以下のようなエラーが出る場合があります。

ERROR 1429 (HY000): Unable to connect to foreign data source: SSL connection error: socket layer receive error
ERROR 1429 (HY000): Unable to connect to foreign data source: SSL connection error: unknown error number
ERROR 2026 (HY000): SSL connection error: socket layer receive error

オチから言うと、これもまたcaching_sha2_passwordによる影響です。

ドキュメント参照

ドキュメントのcaching_sha2_password-Compatible クライアントおよびコネクタを見てみると、以下のような記載があります。

caching_sha2_password について知るために更新されたクライアントまたはコネクタが使用可能な場合は、caching_sha2_password をデフォルトの認証プラグインとして構成された MySQL 8.0 サーバーに接続するときに互換性を確保するための最善の方法です。

次のクライアントおよびコネクタは、caching_sha2_password をサポートするようにアップグレードされています:

  • MySQL 8.0 (8.0.4 以上) の libmysqlclient クライアントライブラリ。 mysql や mysqladmin などの標準 MySQL クライアントは libmysqlclient ベースであるため、互換性もあります。

  • MySQL 5.7 (5.7.23 以上) の libmysqlclient クライアントライブラリ。 mysql や mysqladmin などの標準 MySQL クライアントは libmysqlclient ベースであるため、互換性もあります。

つまり、5.7.22以下(正確には8.0.3以下も含むが、使う人いないだろうし)のMySQL ServerでFEDERATEDテーブルを作った場合、そこから8.0のテーブルを参照時にcaching_sha2_passwordの影響でエラーが出ます。

回避方法

以下のいずれかになります。

  • 接続ユーザーをmysql_native_password認証に変更する
  • MySQL 5.7.22以下のMySQL Serverを5.7.23以上にアップグレードする(こっちのほうがbetter)

セキュリティ的にmysql_native_password認証を使うのをお勧めするのもなあ…と思いつつ、でも、既存環境をできるだけいじれない人はしょうがないですね。

その場合はFEDERATEDテーブルを作り直す必要があります。

アップグレードの場合は敷居高いかもしれませんが、安全に使うためにはご検討ください。

レプリケーションのセットアップ(非GTID)

レプリケーションのセットアップ(非GTID)

以下の手順で行う

  1. ソース(マスター)側の設定
    • server_idの設定
    • log_bin(バイナリログ)の有効化
    • レプリカ(スレーブ)接続用ユーザ作成
  2. レプリカ(スレーブ)へデータコピー
    • ソースでダンプ取得
    • ソースからレプリカへファイルコピー
    • レプリカにインポート
  3. レプリカ(スレーブ)側の設定

ソース(マスター)側の設定

server_idの設定

my.cnfに server_id=1 などを追記して再起動

log_bin(バイナリログ)の有効化

my.cnfに log_bin を追記して再起動

レプリカ(スレーブ)接続用ユーザ作成

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

(REPLICATION SLAVE権限は相変わらずSLAVEって言ってるのがなんだかなーとは思うが…)

MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.2.2 MySQL で提供される権限

レプリカ(スレーブ)へデータコピー

ソースでダンプ取得

$ mysqldump --user=root --password=xxxx  --single-transaction --master-data=2 --flush-logs --all-databases> /tmp/mysql_dump.sql

トリガーや、ストアドなどもつけるなら --triggers --routines --events オプションも入れとく。

ソースからレプリカへファイルコピー

もしくはダンプ時にリモート取得でもOK

レプリカにインポート

$ mysql --user=root --password=xxxx < /tmp/mysql_dump.sql

レプリカ(スレーブ)側の設定

server_idの設定

my.cnfに server_id=2 などを追記して再起動

レプリケーションの設定

8.0.22までと23以降でコマンドが異なる。

MySQL 8.0.22以前

mysql> CHANGE MASTER TO
  MASTER_HOST='source.example.com',
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl_pass',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='source-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

詳しくはここ

MySQL 8.0.23以降

mysql> CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source.example.com',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='Repl_pass',
  SOURCE_PORT=3306,
  SOURCE_LOG_FILE='source-bin.001',
  SOURCE_LOG_POS=4,
  SOURCE_CONNECT_RETRY=10;

詳しくはここ

レプリケーションの開始

MySQL 8.0.22以前

mysql> START SLAVE;

MySQL 8.0.23以降

mysql> START REPLICA;

詳しくはここ

レプリケーション確認

MySQL 8.0.22以前

mysql> SHOW SLAVE STATUS\G

MySQL 8.0.23以降

mysql> SHOW REPLICA STATUS\G

Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. エラーが出てたらとりあえず一回replユーザーでつないどけばOK。

$ mysql -urepl -pRepl_pass -h127.0.0.1 -P3306

MySQL8.0を再起動するとアプリからつながらなくなる理由 - 41から始めました

で根本対応しておくのがベター。

レプリケーションステートメント

その他色々やりたい場合はここから探す

MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.4 レプリケーションステートメント

オプティマイザトレースの使い方

オプティマイザトレースの使い方

オプティマイザトレースの使い方は以下の通り

  1. オプティマイザトレースを有効にする
  2. 解析したいクエリの実行
  3. トレースの表示

1. オプティマイザトレースを有効にする

mysql> SET optimizer_trace='enabled=on';

ちなみにSET GLOBALにするとそのセッションでは無効なので注意。

2. 解析したいクエリの実行

mysql> SELECT ... FROM xxx WHERE ..... ;

処理に時間がかかるようなクエリならEXPLAINでもOK

mysql> EXPLAIN SELECT ... FROM xxx WHERE ..... ;

3. トレースの表示

mysql> SELECT * FROM information_schema.optimizer_trace\G;