cp932利用のmysql8への更新にて発生のトラブルについて

[mokkouyou]

お世話になっております。mokkouyouです。

dbfluteでmysql8に変更したところ、
以下問題が発生するプロジェクトがありました。（DBA機能だけ利用しています）

前提）
mysql5.6⇒8.0.23
文字コードcp932を利用
8への移行にあたって極力緩いモードを引き継ぐ。パラメータはまだ試行錯誤中
※character_set_xxxはcp932に変更済みを確認(filesystem/systemは除く)

ドライバーはlibフォルダで以下差し替え済み
mysql-connector-java-8.0.30.jar

dbflute1.2.2(1.2.6でも同様)

databaseInfoMapのurlは以下パラメータを指定
?useUnicode=true&characterEncoding=MS932&connectionCollation=cp932_japanese_ci&useJDBCCompliantTimezoneShift=false&useLegacyDatetimeCode=true&serverTimezone=Asia/Tokyo

replaceSchemaMapで以下は指定
; sqlFileEncoding = MS932

現象1
replace-schemaなどにおいて、FunctionやTriggerなど、-- #df:begin#を使うケースにおいて、
本文中に日本語コメントを入れるとエラー（外のコメントは問題なし）
※VIEWなどは問題発生しませんし、文字列として'日本語'を使う場合問題なし
※mysqlコマンドで接続して直接発行するのも問題はなし

Caused by: java.sql.SQLException: Invalid utf8 character string: '93FA96'
UTF8固定の処理などがある？



-- #df:begin#
～～
BEGIN
-- 日本語コメント←これがNG
return '日本語' || '日本語'←こういうのはOK

END
-- #df:end#

ファイルはMS932です。
コメント削除すれば通りますので最悪それでもいいのですが・・・

現象2
alter-checkにおいて、
VIEWで||で文字結合しているカラムについて必ず以下の相違が発生して成功しなくなる
※save_previous直後でも同様

Default -> null
Not Nulltrue -> false


データに引きずられるのか？と以下を指定しても変わらず。
; isSchemaOnlyAlterCheck = true

このあたり仕組み把握していませんが、前後比較の際の処理の相違(それぞれの接続や実行において）などがあたりして、
それをパラメータで回避出来たりするのでしょうか？


objectTypeTargetListでVIEWを除外してチェック対象から抜くというのも１つではあるのですが、
出来ればチェックは入っていてほしいところではあります。

何か対応策等ありましたらご教授いただけると幸いです。

[mokkouyou]

すいません

現象2は一旦保留とさせてください。

検証ミスでsave_previous直後でも同様というのが、間違いでした。

※mysql8更新以外の影響ある可能性が捨てきれませんでした

2024年1月29日月曜日 12:44:10 UTC+9 mokkouyou:

[mokkouyou]

mokkouyouです。度々すいません

現象1については、

・mysqlコマンド(source)で実行した場合、英語、日本語によらず、show create~で見たりするとコメントは消えていました。（行は残る）

・ツール（a5m2)で実行した場合でも日本語のコメント部分が同様にエラーとなりました。英語コメントに変更するとshow create~で見るとコメントは残ってました。

というわけで、dbfluteだから云々でもなく、

こういったケース（Triggerとかの中）ではutf8以外の文字コードをコメントとして扱えないという結論でよさそうですね。

※安全としてはutf8のコメントではない場合（もしくは雑にコメント自体）消して実行みたいなの推奨

ソースのみコメントみたいな方式があるといいなと思いましたが、ソースざっと見る限りなさそうですし、

そもそもファイルがutf8じゃない場合のニッチな問題かと思いますので、こちらも忘れていただければと思います。

現象2について

VIEWに関連するテーブルへの変更がある場合におかしくなるようです。

１．save previous後にalterはselect⇒問題なし

２．save previous後に独立した関係なさそうなテーブルへのalter⇒問題なし

３．save previous後に直接、間接問わず関係しそうなテーブルへのalter⇒問題発生

コンパイルとかその手の影響ですかね。

とはいえ、ALTER CHECK後に定義確認してみると、alter-checkでエラーとなった部分は別にそのようなこともなく、

not null trueのままだったりしますので、タイミングとか関係するのかな？といった感じでもやっとしますが、

通常はVIEWもチェックするけど、VIEW除外するenv(bat)も用意して運用対応的なことを考えようかなと思います。

お騒がせしました。何かアドバイスあれば引き続きよろしくお願いいたします。

2024年1月29日月曜日 13:36:06 UTC+9 mokkouyou:

[kubo]

jfluteです。

mokkouyouさん、こんにちは。色々とフィードバックありがとうございます！

現象1了解です。

そもそも MySQ-8.0.x において、UTF-8以外のエンコーディングのときに、

Functionのコード上の日本語コメントがエラーになるので要注意！というところと...

DBFluteでそれをカバーするような機能があればベターだけれども、

UTF-8以外の状況というのがレアなので「気をつけましょう共有」だけして現状のまま...

という感じですかね。

丁寧に状況説明を書いて頂き、ありがとうございます。

Google Groupはなんだかんだネット上に残りますから、

いざってときのとても良い知見になるので助かります。

(DBFlute Slackの方で過去の投稿が見えない問題があるのでなおさら＞＜)

現象2, もうちょっとこちらでも検証して確認してみますね。

[kubo]

jfluteです。

現象2のAlterCheckについて、こちらでも再現してみました。

{ @SQL

create view SUMMARY_PRODUCT as
select product.PRODUCT_ID
     , product.PRODUCT_NAME || product.PRODUCT_HANDLE_CODE as SEA_STRING
...

}

こういう感じで連結させると、そもそもメタデータ的にはこうなりました。

SEA_STRING :: INT型、NotNull, defaultが0

(元のカラム両方ともNotNull)

NotNullはまあ良いとしても、INT型ってのとdefaultが0っておかしいですね。。。

AlterCheck以前に変な状態と言えますね。

そして、片方 (PRODUCT_NAMEの方) でNotNull制約を外すと、

SEA_STRING :: INT型、Nullable, defaultがnull
(片方のカラムがNullable)

になりますね。

これでAlterCheckしたら確かにVIEW側で差分が出てしまいますね。

(HistoryHTMLでも同じですね)

ただ、MySQLって元々 "||" 記号による文字列連結ってデフォルトではサポートしてなくて...

// 5.1.11 サーバー SQL モード | MySQL 8.0 リファレンスマニュアル

https://dev.mysql.com/doc/refman/8.0/ja/sql-mode.html

{ @引用

PIPES_AS_CONCAT
|| を、OR のシノニムとしてではなく (CONCAT() と同様に) 文字列連結演算子として扱います。

}

というように、SQLモードで明示的に指定して初めて利用できるものではあります。

いま自分のローカルでは指定してないので、逆に "||" が通ったのが不思議なのですが...

(何かビット演算っぽい扱いされているのかも？)

ちょっと環境の確認です。

そちらのMySQL環境だと、PIPES_AS_CONCATを指定しているってことでしょうか？

(以前のMySQLでも、新しいMySQL8でも)

また、その文字列連結しているVIEWのメタデータ上では、

そのカラムのデータ型はどうなっていますでしょうか？

(SchemaHTMLで確認ができるはずです)

[mokkouyou]

jfluteさん

ありがとうございます。

PIPES_AS_CONCATを指定しています。

で、すいません、ツールで確認して差がないとしていたのですが、

information_schema.COLUMNSを確認したところ、

replace-schemaの段階だとIS_NULLABLE=YES

alter-checkでこけた後はNOに。

replace-schemaを再度実行し（next ddlだけど)ALTER文を直接を投げるとやはりYES⇒NOになりました。

※また、普段schemahtml出していませんでしたが、（21->22と流したところ、）

それぞれの状態で、同様の出力になっていましたのでdbflute的に問題はなさそうです。

問題はmysqlでalterでカラムの変更かけたらなんかIS_NULLABLE判断に影響が出るという方向の調査をしようかとおもいます。

とはいえ実際にはDBA的に変更とかデータ管理しているだけですので、チェック以外に影響なかったりしますので、

先ほどのように運用的に回避したいと思います。

＃Viewの比較で、有無とかカラム一覧はチェック出来るけどこの手の本来あいまいな部分は無視するみたいなオプションあったら教えていただけると幸いです。

2024年1月29日月曜日 16:45:40 UTC+9 jflute:

[kubo]

jfluteです。

なるほど。MySQL内部のメタデータが変わってるっぽいとのことですね。

DB側の気まぐれによる差分であればDBFluteで無視できるようにしないとですね。

今ところさすがにピンポイントでignoreするような機能はないので、作らないとってところですが...

自分の方で再現したいなと思って試しているのですが、なかなか再現しません。

この「現象2」は、MySQLのバージョン関係しますでしょうか？

(MySQL-5.6のときは発生しなかったけどMySQL-8.xにしたら発生するようになった？)

こちらの環境で、ローカルのMySQL-8.0.36を用意してPIPES_AS_CONCAT設定して...

DBFluteクライアント側は mysql-connector-j-8.0.32.jar でアクセスするようにして...

create view SUMMARY_PRODUCT as
select product.PRODUCT_ID
     , product.PRODUCT_NAME || product.PRODUCT_HANDLE_CODE as SEA_STRING

...

↑のVIEWを作成してみたところSEA_STRINGカラムはNotNull制約は付きませんでした。

(連結してるカラムはどちらもNotNull)

カラム定義やVIEWのSQLやalter文など、もう少し具体的に教えて頂けないでしょうか？

近い状況を作ってみたいなと。その上で、それをignoreできる機能を検討できたらと考えています。

[mokkouyou]

jfluteさん

mokkouyouです。

■古い環境(発生せず)

mysql5.6.28

mysql5.7.26

※いずれもcp932指定

connectorは5.1.33接続文字列は?useUnicode=true&characterEncoding=cp932、ドライバーは昔ながら(cjつかない)

■新しい環境(発生)

mysql8.0.36（8.0.23も同様)

※いずれもcp932指定

connectorは8.0.30

接続文字列は以下

useUnicode=true&characterEncoding=MS932&connectionCollation=cp932_japanese_ci&useJDBCCompliantTimezoneShift=false&useLegacyDatetimeCode=true&serverTimezone=Asia/Tokyo

ドライバはcom.mysql.cj.jdbc.Driver

古い環境では発生していません。変更前後いずれもIS NULLABLE=NO（NotNull)です。

理論的にもこの状態が正しいです。

というわけでalterというよりも

replaceSchema(Alterの初回)でIS NULLABLE=YES(NULL許可)となってしまうのが問題といった感じでしょうか。

なお||に関してはnullが混じるとnullになるということで、各種ifnullを入れたりしています。

また、確認したカラムとしては以下のようなシンプルな挙動

A || '_' || B

それぞれ、以下のような定義です。

A int NOT NULL
B int NOT NULL

※CAST(A AS CHAR)など挟んでも変わらず。

また以下のケースでも同様の挙動となりました。

FORMAT(カラム,0)

対象カラム
numeric(15,4) DEFAULT 0 NOT NULL

変更をかけたテーブル（桁拡張）については、VIEWで結合してはいますが、該当のカラムでは利用していません。

また、

replace_schemaの後の段階でis nullable=yesでしたが、

この状態でそのままcreate or replaceで該当のview作り直したりするとnoになりましたので、

データがやはり関係するのかもしれません（もともとalter後が問題だと思っていたのですが、前述のとおり、replaceSchemaの初回の結果が予想外）

2024年1月31日水曜日 12:06:34 UTC+9 jflute:

[kubo]

jfluteです。

mokkouyouさん、詳しい状況ありがとうございます！

ローカルで全く同じ現象ではないですが、ちょっと近い変な現象が発生したので共有します。

-- HANGARとMYSTICが想定されるカラム

create table SEA_PRODUCT(
    SEA_PRODUCT_ID INTEGER AUTO_INCREMENT NOT NULL,
    HANGAR numeric(15,4) DEFAULT 0 NOT NULL,
    MYSTIC numeric(15,4) DEFAULT 0 NOT NULL,
    PRIMARY KEY (SEA_PRODUCT_ID)
) COMMENT='SEA商品';

-- HANGARとMYSTICを想定される形でくっつけてみた (STAGE_SHOW)

create view VIEW_SEA_PRODUCT as
select product.SEA_PRODUCT_ID
     , product.HANGAR
     , product.MYSTIC
     , product.HANGAR || '_' || product.MYSTIC as STAGE_SHOW
  from SEA_PRODUCT product

information_schema.columns の IS_NULLABLE は...

HANGAR :: NO (NotNull)

MYSTIC :: NO (NotNull)

STAGE_SHOW :: YES (Nullable)

ということで、STAGE_SHOW が NO (NotNull) にならないのでうーむーという感じなのですが...

(ifnullとかもやってみたりしましたが変わらず)

VIEWのSQLで「とある変更」をすると変わりました。

-- select句に相関サブクエリを入れてみた (LATEST_PURCHASE_DATETIME)

create view VIEW_SEA_PRODUCT as
select product.SEA_PRODUCT_ID
     , product.HANGAR
     , product.MYSTIC
     , product.HANGAR || '_' || product.MYSTIC as STAGE_SHOW
     , (select max(purchase.PURCHASE_DATETIME)
          from PURCHASE purchase
         where purchase.PRODUCT_ID = product.SEA_PRODUCT_ID
       ) as LATEST_PURCHASE_DATETIME
  from SEA_PRODUCT product

すると IS_NULLABLE が全部 YES になってしまいました。

単にselect句の項目を増やしただけなのに。既存の項目は変えてないのに。

そしていま、ローカルのMySQL-5.6の方でも試してみたら...

別にサブクエリを入れようが入れまいが HANGAR や MYSTIC などの固定項目は、

IS_NULLABLE が NO のままで変化ないですね。

8.0だとVIEWのカラムのIS_NULLABLE判定がちょっとへんてこりんであることは確実っぽいですね。

(お互い発生してる現象が違うにしても)

引き続き、ちょっと検証してみます。

(今度はAlterCheckの流れでやってみよう)

[kubo]

jfluteです。

> 引き続き、ちょっと検証してみます。
> (今度はAlterCheckの流れでやってみよう)

自分のケースの場合はVIEW自身の修正なので、結局 alter-schema.sql は...

drop view VIEW_SEA_PRODUCT;

create view VIEW_SEA_PRODUCT as
select product.SEA_PRODUCT_ID
     , product.HANGAR
     , product.MYSTIC
     , product.HANGAR || '_' || product.MYSTIC as STAGE_SHOW
     , (select max(purchase.PURCHASE_DATETIME)
          from PURCHASE purchase
         where purchase.PRODUCT_ID = product.SEA_PRODUCT_ID
       ) as LATEST_PURCHASE_DATETIME
  from SEA_PRODUCT product

;

って、作り直すことで差分解消はしました。

ただ、HistoryHTMLでNotNullの差分が出るのがへんてこりんにはなりますね。

さらに、mokkouyouさんの方だと、VIEW自体には修正をしてないのに、

参照されてる元のテーブルで何か修正をした場合にVIEWに差分が生まれて、

しかも解消不可能みたいな状態になってしまうわけですね。

何にせよ、VIEWのNotNull情報(やDefault値)は気まぐれで変わってしまいやすいということなので、

何かしら差分を無視できるような仕組みをちょっと考えてみたいと思います。

汎用的な指定ができると理想ですが、ちょっと実装が大変そうなので、

ピンポイントで「VIEWのNotNullとdefault値を差分除外する」みたいな

プロパティが現実的かなと考えています。

[mokkouyou]

jfluteさん

ありがとうございます。

私の方は前述の通りですが、viewを除外するalterも用意して運用的に対応しようと思います。

ちなみにalterではなくreplaceSchemaの方に絞って色々パターン確認

■データが影響？
viewをalter-checkからtake-finallyに変更

⇒変わらず問題発生（null許可）

■作り替えの際に再計算してくれる？
replace-schemaで1度作って、take-finallyでcreate or replace

⇒変わらず

■JDBCが悪い？コネクションの共有などが問題？
viewを作成除外、replaceSchema後にツールで作成

⇒期待通り（NotNull)

dbfluteではなく、javaのjdbc使ってview作るコードでview生成

⇒変わらず問題発生

どうやらjdbc経由でVIEW作成するとこうなっちゃうようですね

jdbcのオプション/mysqlの設定/そもそも？などはちょっとわかりませんが。

2024年1月31日水曜日 20:34:01 UTC+9 jflute:

[mokkouyou]

あ、一応ですが、私の認識としては

replace-schemaの流れで作成したVIEWのメタデータ（NotNull)がおかしい

おそらくJDBC経由で作成した場合に発生

alterの際に関連テーブルの変更によりVIEWの定義の見直しが発生しメタデータが正しくなる

⇒結果として差分として認識されてしまう

そもそもでいうと、

replaceshemaしてschemahtml出力した場合、

正しくない定義情報が出てしまう。とはいえ、mysqlのもってるメタ情報とは一致しているので話がなかなか面倒。

まぁviewのそのあたりは当てにしないのが無難・・・

ですね。

というわけで

普段は通常通りのalterCheckだが、該当事象が発生した時用にviewを無視する環境変数(バッチ)を用意して対応しようかなと。

ちょっと話をシンプルに

①以下テストのテーブルとviewで、データなし。replaceSchema実行

create table test1(
ID int NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'ID',
score int NOT NULL COMMENT 'score'
)

create or replace VIEW testv AS SELECT
FORMAT(score,0) AS score,
concat("ID:", ID ) AS id
from test1
;

作成後いずれもis nullable YES

②適当ですが、該当テーブルにカラム追加（ツール等で実行）
alter table test1 add name varchar(20);

この処理後いずれもis nullable NOに

③そもそも①の手順でテーブルのみ作成、VIEWをJDBC以外(ツール)で作成すると

IS NULLABLE NOの状態で出来る

2024年2月1日木曜日 9:47:54 UTC+9 mokkouyou:

[kubo]

jfluteです。

mokkouyouさん、ありがとうございます。

なるほど、JDBCでcreate viewすると...っての、後でこっちの実験でも試してみたいと思います。

あと、頂いたシンプルなtestテーブルも後で試してみたいと思います。

[kubo]

jfluteです。

> なるほど、JDBCでcreate viewすると...っての、後でこっちの実験でも試してみたいと思います。

> あと、頂いたシンプルなtestテーブルも後で試してみたいと思います。

頂いたDDLでだいたい再現しました。

ツールでVIEWを作ったり作り直すとnot nullになって...

JDBCでVIEWを作ったり作り直すとnullableになって...

ってところですかね。

元テーブルにalterをすることで、VIEWを作り直すきっかけになってるのではないかと想像します。

「VIEWの参照元のテーブルが変わったからVIEWを作り直さなきゃ」ってMySQLがやってくれてるのかなと。

なんにせよ、mokkouyouさんのパターンとぼくのパターンと色々なパターンでnot nullが揺れるので、

DBFluteのオプションで差分除外できるようにしないといけないかなと考えています。

(本来はデフォルトで差分除外しても良いのかもですが...コントロールできない情報ということで)

[mokkouyou]

jfluteさん確認ありがとうございました。

なんだかな～という状況ではありますよね。

JDBCドライバのバグなのかなぁ・・・とか。オプション見てみたけどそれっぽいのも見つからず。

とはいえ、なんだかんだ、こういう状況ってないこともなさそうだな～という気もしますので、

バランスとりつつ検討していただければと思います。

#なんだかすべてが結果として微妙なところに着地したな～といった感じですね

2024年2月13日火曜日 13:32:55 UTC+9 jflute:

[kubo]

jfluteです。

mokkouyouさん、DBFluteでVIEWのNotNullの差分を無視するオプションを追加しました。

//  DBFlute Engine: option to ignore VIEW's not null constraint differences #200

https://github.com/dbflute/dbflute-core/issues/200

documentMap.dfprop にて以下を追加すると無視されるようになります。

    # /- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    # o isIgnoreViewNotNullDiff (NotRequired - Default false)
    #  Does it ignore differences of view's not null constraint?
    #
    # test of view's not null constraint (2024/02/13)
    # https://github.com/dbflute/dbflute-core/issues/200
    #
    ; isIgnoreViewNotNullDiff = true
    # - - - - - - - - - -/

// dbflute-test-dbms-mysql での Example

https://github.com/dbflute-test/dbflute-test-dbms-mysql/blob/master/dbflute_maihamadb/dfprop/documentMap.dfprop#L159

DBFlute-1.2.7のパッチにて反映していますので、

すでに1.2.7の場合は、パッチ当てすることで利用できます。

// DBFlute Engineのパッチ

https://dbflute.seasar.org/ja/environment/upgrade/index.html#patch

MySQL側でJDBCあれこれありそうですが、

どのみちVIEWのNotNullはこちらでコントロールできるものではないですし、

その情報で開発側が何か判断することもほとんどないと想定されるので、

差分としては無視するでも良いのではないかと思いました。

(デフォルトで無視でもいいかもしれないくらいですが、取り急ぎオプションで対応)

[mokkouyou]

jfluteさん

ありがとうございます。

not nullは確認出来ました。

当初の問題としてnot nullがtrue->falseになることによって、関連してdefaultも変更されるケースもあります。

Default -> null
Not Nulltrue -> false

今回のオプションについてnot nullは無視してくれるんですが、

デフォルトは無視してくれないのでaltercheck自体は成功しないんですよね・・・こちらも対応いただけますと助かります。

2024年2月25日日曜日 13:19:21 UTC+9 jflute:

[kubo]

jfluteです。

> 当初の問題としてnot nullがtrue->falseになることによって、関連してdefaultも変更されるケースもあります。

あっ、そうでしたね、忘れてました（＞＜。

そっちも同じ理論で、コントロールできないし、VIEWはinsertしないからなおさら意味のない項目なのでやります。

[kubo]

jfluteです。

取り急ぎ、ほぼ同じような感じでVIEWのdefault制約を無視するオプションを入れてみました。

//  DBFlute Engine: option to ignore VIEW's default constraint differences #203

https://github.com/dbflute/dbflute-core/issues/203

    # /- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    # o isIgnoreViewDefaultDiff (NotRequired - Default false)
    #  Does it ignore differences of view's default constraint?
    #
    # test of view's not null constraint (2024/02/26)
    # https://github.com/dbflute/dbflute-core/issues/203
    #
    ; isIgnoreViewDefaultDiff = true

    # - - - - - - - - - -/

パッチに反映しています。

ちょと実装入れただけのアップで申し訳ないですが、

良ければ確認して頂けると嬉しいです。

[kubo]

jfluteです。

↓コメントが一個間違っていたので直しました。(以下、直したもの)

    # /- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    # o isIgnoreViewDefaultDiff (NotRequired - Default false)
    #  Does it ignore differences of view's default constraint?
    #

    # test of view's default constraint (2024/02/26)

[mokkouyou]

jfluteさん

対応いただいた2つの設定で無事altercheck通りました。

ありがとうございました。

2024年2月26日月曜日 17:34:37 UTC+9 jflute:

[kubo]

jfluteです。

mokkouyouさん、確認ありがとうございます！

とても良いフィードバックで助かりました。

また何かありましたら、ぜひよろしくお願いします。