Project

General

Profile

Actions

QA #1085

open

DB間差異箇所の対応(mysql/mariadb, postgresql)

Added by 奈良 裕記 over 1 year ago. Updated 20 days ago.

Status:
新規
Priority:
通常
Assignee:
-
Category:
-
Target version:
-
Start date:
03/08/2020
Due date:
% Done:

0%

Estimated time:

Description

■現象/要望

Redmine稼働環境のDBMSとして、mysql/mariadb, postgresqlの2系統が主に利用されている。
DBMS間差異は殆どがRails側で吸収されているが、DBMS自体の動作差異により、完全に同一の動作とはなっていない。postgresqlでは問題無く、MYSQL環境でのみトラブル発生する場合もある。

DBMS差異による動作差異の発生個所、対処方法をまとめ、各現場で円滑に運用可能にしたい。

本チケットの対象範囲

・本チケットに対象範囲は mysql/mariadb, postgresqlの2系統とし、SQLite、MSSQLServerを除外する。
(理由は補足欄参照、関連情報のリンクはするが、積極的な調査対象とはしない。)
・上記2系統のバージョン間互換性問題は対象範囲内とする。

・Redmine各バージョン間の差異は、各バージョン側に掲載する。(重複掲載する場合有)
 ・Redmine4.x/Rails5分→ #755
 ・Redmine3.x/Rails4分→ #751

■解決策

DBMS間差異に関連すると思われる事例/対応策を記録。
(情報リンク/抜粋で良い)

Active Record マイグレーション(基本的なリファレンスとして)
https://railsguides.jp/active_record_migrations.html

対応作業事例

DBMS/Railsバージョンにより、Migration中で処理分割する場合

対処方法

DBMS/Railsバージョンにより、Migration中で処理分割する場合
if Redmine::Database.mysql?
if Redmine::Database.postgresql?
if Rails::VERSION::MAJOR == 4

詳細は下記参照
DB Migrationの実行を条件分けしたい(migration失敗回避)
https://redmine.tokyo/issues/844

PluginのDB migration中エラー(Mysql:外部キー存在時、インデックス削除失敗)

現象/発生条件

redmine_knowledgebase Redmine4.0対応(と思われるフォーク)にて、DB migration中に下記エラー発生する。

Cannot drop index 'index_taggings_on_tag_id': needed in a foreign key constraint: DROP INDEX `index_taggings_on_tag_id` ON `taggings`

現象発生フォーク
https://github.com/southbridgeio/redmine_knowledgebase

関連やりとり
https://twitter.com/y503Unavailable/status/1235222914850361345

発生原因

Mysqlで外部キーを作成すると、インデックスが自動的に追加される。(pgsqlでは追加されないと思われる)
Migration中でインデックスを削除しようとしても、外部キーが存在すると、インデックスの削除に失敗する。

http://kwski.net/mysql/602/

MySQLでは外部キーを設定すると自動的にインデックスが作成されるようです。
逆に外部キーが設定されたままインデックスを削除しようとするとエラーになります。

対処方法

(暫定対策)
DB Migration失敗後(問題となったテーブルの外部キー作成後)、手動で外部キーを削除してから再度実行する。
Migration完了後に再度外部キーを作成する。
これで一応通った。

SQLで外部キー制約を無視するコマンドを入力する事でマイグレーションが通るようになりました。
SET FOREIGN_KEY_CHECKS=0;

https://gourmet-technology-crypto.jp/%e6%8a%80%e8%a1%93/bitnami-redmine-windows-3-1-2%e2%86%924-1-0/

本来は、Rails側流儀に合った形で恒久対応必要

migration中での処理方法

Railsマイグレーションのindex、foreign_keyの設定
https://qiita.com/shitake4/items/2f08c0d9b13d090c6071
インデックス、外部キーの追加/削除
add_index、remove_index、add_foreign_key、remove_foreign_key

Railsの外部キー制約とreference型について
https://qiita.com/ryouzi/items/2682e7e8a86fd2b1ae47

Railsで外部キー制約のついたカラムを作る時のmigrationの書き方
https://qiita.com/publichtml/items/1fba15d8071fab66d043
add_reference

Migrationエラー Mysql2::Error: Incorrect datetime value: '0000-00-00 00:00:00' for column 'closed_on'

現象/発生条件

Migrationエラー Mysql2::Error: Incorrect datetime value: '0000-00-00 00:00:00' for column 'closed_on' at row 281: ALTER TABLE `issues` CHANGE `description` `description` longtext DEFAULT NULL

Mysql5.7.8以降で,Redmine3.4以降にアップデートする場合に発生する。
詳細→ https://gourmet-technology-crypto.jp/%e6%8a%80%e8%a1%93/bitnami-redmine-windows-3-1-2%e2%86%924-1-0/#0000-00-00_000000

発生原因

MySQL 5.7.8以降ではsql_modeのデフォルト値がNO_ZERO_DATEとなっている。
この状態では、issues テーブル closed_onカラム(datetime型)の 00:00:00 0000-00-00(チケット未終了状態)を許さないため。

MySQLのsql_modeのせいで'0000-00-00'登録時にエラーが出た昔話。
https://qiita.com/tentatsu/items/fd2177777412ebc2f6b9

対処方法

sql_modeのデフォルト値からNO_ZERO_DATE, NO_ZERO_IN_DATEを削除して実行する。

詳細→ https://gourmet-technology-crypto.jp/%e6%8a%80%e8%a1%93/bitnami-redmine-windows-3-1-2%e2%86%924-1-0/#0000-00-00_000000

補足

なお、本エラーは、issuesのdescruptionをlongextに変更する処理(Redmine3.4.0への更新)にて発生している。
https://www.redmine.org/issues/20127
手元のMariaDB10.4では、未完了チケットのclosed_onはNULLになっていた。(デフォルトNULL)

issuesの他のカラムはdatetime型ではないので無関係。但しmessagesにもdatetime型あり。

Mysql5.6でRedmine3.4.0に移行済の場合、同じ問題を内在している可能性は無いか?
(issuesテーブルの設計更新時にエラー発生するなど)
と思ったが、手元の環境ではNULLに変換されていた。
どこかで明示的にNULLに変換していたと思われる。

(テンプレ)現象タイトル

現象/発生条件

発生原因

対処方法

■補足情報

SQLite/MSSQLServerを除外する背景

・SQLiteはローカルの検証環境で利用される場合があるが、中規模以上の実運用環境では性能上無理がある。
・Redmine構築用パッケージ(ansible)/配布VM/コンテナでは、mysql/mariadb, postgresqlの利用が大半である。
・MSSQLServerは、2020/3現在、Redmine4.0で動作していない(Redmine本家30285)
 利用も少ないと思われる。(Windows環境ではBitnami利用が大半)

MySQL→PostgreSQLへの移行

Redmineの応答性能面では、MysqlよりPostgresqlの方が高い。
移行する場合はpgloaderにより可能

RedmineのデータベースをMySQLからPostgreSQLへ移行した
https://qiita.com/ryouma_nagare/items/c4ba5298dd283333bb85

pgloader 3.4.1でMySQLからPostgreSQLへスマートに移行しよう(翻訳)
https://techracho.bpsinc.jp/hachi8833/2017_07_20/43380

pgLoaderを使用してMySQLデータベースを
https://www.codeflow.site/ja/article/how-to-migrate-mysql-database-to-postgres-using-pgloader

yaml_dbを使ってMySQLからPostgreSQLにRedmineを移行した
https://hnron.hatenablog.com/entry/2015/08/18/012738

DB内含め動作トラブル時の対応

SQLインポート時にエラー発生

・mysqlの -f コマンドで、エラーを無視して実行

・SQL_MODEを設定して調整する

Mariadb10.4 SQL_MODE
https://runebook.dev/ja/docs/mariadb/sql-mode/index

MariaDB本家 SQL_MODEドキュメント
https://mariadb.com/kb/en/sql-mode/
https://mariadb.com/kb/en/old-mode/
SQL_MODEとOLD_MODEがある
>SQL_MODE is used for getting MariaDB to emulate behavior from other SQL servers,
>while OLD_MODE is used for emulating behavior from older MariaDB or MySQL versions.

MariaDB(MySQL)をアップグレードしたらINSERTに失敗。[SQL_MODE]
https://onoredekaiketsu.com/mariadb-sql-mode/
↑バージョン別SQL_MODE一覧表、不具合が起きる可能性のあるケース、対処方法

SET sql_mode = '';
SET SESSION sql_mode='';
STRICT_TRANS_TABLES

設定状況確認
SELECT @global.sql_mode;
SELECT
@session.sql_mode;

・mysql起動時にSQL_MODEを設定して対応する
my.cnfにて設定、

その他

mysqldump 簡易まとめ
https://qiita.com/take-piro/items/fe695f9a0f759d72d713

よく使うmysqldumpのオプションと使用例
https://qiita.com/ryounagaoka/items/7be0479a36c97618907f

MySQL (MariaDB) でハマった仕様
https://kamocyc.hatenablog.com/entry/2020/02/18/104031
sql_modeの設定、その他多数

[備忘録]redmine-centos-ansibleでインストールに失敗したときの対処法(create locale ja_JP.UTF-8でこけた編)
https://qiita.com/Aqua_Marinne/items/64bc3f1a62d82747bdd3
pgsql character map file `UTF-8' が無くエラーになった場合の対応


Related issues

Related to Unofficial Redmine Cooking - QA #844: DB Migrationの実行を条件分けしたい(migration失敗回避)回答済奈良 裕記07/17/2018

Actions
Actions #1

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #2

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #3

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #4

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #5

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #6

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #7

Updated by 奈良 裕記 over 1 year ago

  • Related to QA #844: DB Migrationの実行を条件分けしたい(migration失敗回避) added
Actions #8

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #9

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #10

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #11

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #12

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #13

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #14

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #15

Updated by 奈良 裕記 over 1 year ago

  • Description updated (diff)
Actions #16

Updated by 奈良 裕記 4 months ago

  • Description updated (diff)
Actions #17

Updated by 奈良 裕記 4 months ago

  • Description updated (diff)
Actions #18

Updated by 奈良 裕記 about 2 months ago

  • Description updated (diff)
Actions #19

Updated by 奈良 裕記 about 1 month ago

  • Description updated (diff)
Actions #20

Updated by 奈良 裕記 about 1 month ago

  • Description updated (diff)
Actions #21

Updated by 奈良 裕記 about 1 month ago

  • Description updated (diff)
Actions #22

Updated by 奈良 裕記 about 1 month ago

  • Description updated (diff)
Actions #23

Updated by 奈良 裕記 about 1 month ago

  • Description updated (diff)
Actions #24

Updated by 奈良 裕記 20 days ago

  • Description updated (diff)
Actions

Also available in: Atom PDF