- 投稿日：2019-07-24T15:31:23+09:00
MySQL 5.5 -> 5.7 でつまづいたこと
MySQL 5.5 を使っていた Java アプリを MySQL 5.7 に上げた際に、いくつか問題が発生したので、原因と対処を残しておきます。
Datetime Field Overflow
事象
datetime型のカラムを持つテーブルに、最大値の9999/12/31 23:59:59を含むレコードを INSERT したところ、Datetime Field Overflow が発生して失敗した。原因
MySQL 5.6 で、
datetime型の小数点以下が切り捨て (floor) から四捨五入 (round) になったため。
→ 公式ドキュメント対処
java.util.Calendar でインスタンス化したオブジェクトのミリ秒を取得していた箇所について、ミリ秒をリセット (
.000) する処理を追加した。beforeCalendar cal = Calendar.getInstance(); cal.set(9999, 11, 31, 23, 59, 59); cal.getTime();↓
afterCalendar cal = Calendar.getInstance(); cal.set(9999, 11, 31, 23, 59, 59); cal.clear(Calendar.MILLISECOND); cal.getTime();オブジェクトとしては
Calendar.getInstance()したタイミングでのミリ秒を取得することから、実行タイミングごとに変化するのだが、このとき、ミリ秒が.499未満ならセーフだが.500以上だとアウトとなる。
これまでは切り捨てられていたので、どのタイミングのミリ秒でもセーフだった。教訓
この件に限らず、アプリケーションは DB 仕様に依存した実装にしないことが大切。
ちなみに、この変更は SQL 標準に従ったものらしい。（公式ドキュメントより）
No warning or error is given when such rounding occurs. This behavior follows the SQL standard, and is not affected by the server sql_mode setting.
DISTINCT 指定の SQL で構文エラー
事象
DISTINCT を指定した SQL 実行時に構文エラーが発生した。
原因
MySQL 5.7.5 から、デフォルトの sql_mode に
ONLY_FULL_GROUP_BYが指定されたため。
→ 公式ドキュメントsql_mode は、構文の妥当性をチェックするいわばバリデーションのような機能で、いくつか種類がある。
このうちONLY_FULL_GROUP_BYは「GROUP BY で集計するとき、SELECT 句や ORDER BY 句で指定するカラムが GROUP BY 句に含まれているか」と「ORDER BY 句のカラムが DISTINCT のカラムリストに含まれているか」をチェックするもので、今回は後者に違反していた。対処
発行する SQL を修正した。
beforeSELECT DISTINCT HOGE, FUGA FROM T_USER ORDER BY USER_ID DESC↓
afterSELECT DISTINCT HOGE, FUGA, USER_ID FROM T_USER ORDER BY USER_ID DESC教訓
発行する SQL が、現在の sql_mode 設定に違反していないか、あるいは違反せざるを得ない場合は該当の sql_mode をオフにしているかを気にしておく。
以上
- 投稿日：2019-07-24T14:20:47+09:00
Spring + Hibernate + MySQL なアプリをレプリケーション対応させた話
概要
10年くらい前のスタンダード構成なレガシー Java アプリを、DB のレプリケーション化に対応させたときの知見をまとめておきます。
使用技術とバージョンは以下の通りです。
- Spring 4.1.6.RELEASE
- Hibernate 4.3.10.Final
- MySQL Connector/J 5.1.36
- MySQL (InnoDB) 5.7.x
- Master
- Failover Replica
- Replica
レプリケーションについて
具体的な内容に入る前に、レプリケーションについておさらいしておきます。
データベースのレプリケーションは多くのデータベース管理システムが採用している概念で、データベースのオリジナルとコピーのマスタースレーブ関係を意味する。マスター側は更新を記録し、それがスレーブ群に通知される。スレーブ側は更新を正しく受け取ったというメッセージを送り、次の更新を受け付けられる状態であることを通知する。
出典 : Wikipedia
参照および更新が可能なマスターと、常にマスターと同期されており1、参照のみ可能なスレーブで DB を構成することをレプリケーションと言います。
INSERTやUPDATEといった更新処理をマスターに対して行い、SELECTのような参照処理をスレーブに対して行うことで、DB の負荷分散を図ったり、何らかの理由でマスターがダウンした際に、マスターの代わりとなるフェイルオーバーレプリカを用意しておくことで、ダウンタイムを極小化して高可用性を実現できます。
また、マスターのコピーが存在することになるので、データを冗長化できます。レプリケーション構成にすることで様々なメリットを享受できますが、分散問い合わせや同期タイムラグ対策など、アプリケーションも相応のつくりになっている必要があります。
実装
Spring の
@TransactionalでreadOnlyオプションを指定することにより、トランザクションごとに問い合わせ先を決定するようにしました。先述したように、実装として必要な要件は「分散問い合わせ」と「同期タイムラグ対策」でした。
このうち後者については、トランザクションの範囲を広げることで対応しました。
具体的には、これまでの実装は、トランザクションが Repository 層（＝ 1 問い合わせ）ごとに作成されていたのを、Service 層（＝複数問い合わせ）ごとに作成するよう変更しました。
これはつまり、複数問い合わせの中に更新系、参照系が混在していても、1 回のトランザクションごとに問い合わせ先をマスターかスレーブか決定するということです。
スレーブは Readonly なので、更新系が混在している場合は必然的にマスターへの問い合わせになります。
したがって、更新系が混在するトランザクションでは参照系もマスターへの問い合わせになり、厳密な負荷分散はできていないことになりますが、代わりに同期タイムラグを考慮しなくてよいことになりますので、負荷の大きさと実装の複雑さを天秤にかけた結果、実装をシンプルにする選択をしました。
観点 1 トランザクション / 1 問い合わせ 1 トランザクション / n 問い合わせ 負荷分散 更新系と参照系で厳密に分散できる 厳密には分散できない（更新系がある場合はすべてマスター、更新系がない場合はすべてスレーブ） 実装のシンプルさ 同期タイムラグの考慮が必要で複雑になる（更新→参照の場合は参照時に最新のデータであることを考慮） 同期タイムラグの考慮が不要でシンプル（更新→参照の場合でも必ず最新データになる） そのため、この記事で書くことは「分散問い合わせ」のために行ったこと、になります。
@TransactionalについてSpring の
@Transactionalを使う上で、これについて知っておく必要があったので少し調べました。→ 公式ドキュメント
@Transactionalは、これまでソースコードに入り込んでいたbeginやcommitといったトランザクション管理に関係するコードを、アノテーションとしてメソッドに付与することで、Spring AOP により、そのメソッド開始時にbeginされ、終了時にcommitが行われることで、ソースコードからトランザクション管理を追い出して見通しを良くできる機能です。この
@Transactionalには様々なオプションを設定することができますが、今回使用するreadOnlyはtrue / false (default)を指定することができ、これにより問い合わせ先をマスターかスレーブか決定できます。
デフォルトはfalseで、マスターへの問い合わせとなりますが、trueを指定することでスレーブへの問い合わせにできます。使い方
Spring コンテナで Bean 管理されている（＝Spring AOP が実行できる）クラスならば、以下のようにメソッドに付与するだけです。
また、クラス自体に付与することもでき、その場合はクラスのメソッドすべてに付与したのと同義となります。UserService.javapublic class UserService implements Service { @Transactional(readOnly = false) // このメソッドが呼び出された段階で begin され、抜けるときに commit される public User createUser() { // INSERT } @Transactional(readOnly = true) // 参照系なのでスレーブへの問い合わせ public User getUser() { // SELECT } @Transactional(readOnly = false) // 更新系なのでマスターへの問い合わせ public void updateUser() { // UPDATE } @Transactional(readOnly = false) public void deleteUser() { // DELETE } }Spring の設定ファイルに以下のような記述を追記し、アノテーションを有効にしておきます。
Spring.xml<tx:annotation-driven transaction-manager="txManager"/> <bean id="txManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager"> <property name="sessionFactory" ref="sessionFactory"/> </bean> <bean id="dataSourceTxManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean>MySQL Connector/J によってコネクションを行いますが、接続先として DataSource を以下のように記述します。
→ 公式ドキュメント最初にマスターのホストを書き、以降、カンマ区切りでスレーブホストを書きます。
Spring.xml<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="com.mysql.jdbc.ReplicationDriver"/> <property name="url" value="jdbc:mysql:replication://127.0.0.1:3306,127.0.0.1:13306"/> <property name="username" value="user"/> <property name="password" value="password"/> </bean>まとめ
レガシーなアプリへの対応だったので、具体的な部分は今後あまり参考にならなさそうですが、レプリケーション自体はスタンダードな構成だと思うので、概念や、どのような対応が必要かを知れたことは収穫だと思います。
DB はほとんどのアプリで使われるものだと思いますので、今回得た知見を活かしたい次第です。
実際には数ミリ秒の同期遅延が発生するので、アプリ側での考慮が必要な場合がある ↩
- 投稿日：2019-07-24T12:50:18+09:00
MySQLで大文字と小文字を区別したい場合
- 投稿日：2019-07-24T01:40:34+09:00
秘密鍵を使ったSSHでPDO接続
はじめに
業務で踏み台経由でDBへ接続する必要があった時やったこと。
PDOはSSH接続する方法はないっぽいので、SSHトンネルをバックグランドで実行しといて、
それ宛にPDOで接続する。バックグランド実行の方法はこちら
環境
- Mac
- PHP5.1
[クライアント] ---- [ 踏み台サーバー] ---- [データベース] ・Mac ・ユーザー ・アクセスは踏み台からのみ許可 ・PHP ・パスワード ・秘密鍵コード
バックグラウンドでSSHトンネリングの実行
-N 指定してリモートでコマンド実行できないようにしています。
停止のときに使うため、変数に格納しています。php$cmd = "ssh -f -N -L 13306:dbhost:3306 sshuser@sshhost -i key/id_rsa"; exec($cmd);[クライラント] -> [このトンネルへ13306で接続] -> [sshuser@sshhostとしてデータベースの3306へ転送] -> [データベース]PDO接続
SSHトンネリングを実行した状態で。
php//ローカルの13306へ向けて $dsn = 'mysql:dbname=db;host=127.0.0.1;port=13306'; $user = 'root'; $password = 'root'; try { $dbh = new PDO($dsn, $user, $password); echo "接続成功\n"; } catch (PDOException $e) { echo "接続失敗: " . $e->getMessage() . "\n"; exit(); }SSHトンネリングの停止
php//開始時に使ったコマンドを元に調べる $_cmd = "ps aux | grep '[0-9] ".$cmd."' | awk '{print $2}'"; exec( $_cmd ,$output ,$return_var ); //$ouput[0]にPID入っているのでkill exec('kill '.$output[0]);実装
DB設定やらポート設定はかくじで。
SSHTunneling.phpclass SSHTunneling{ private static $pids = array(); private static $cmd; public static function start(){ self::$cmd = 'ssh -f -N -L '.$tunnel_port.':'.DBConfig::$host_name.':'.DBConfig::$port.' '.$ssh_user.'@'.$ssh_host.' -i '.$private_key; echo self::$cmd.PHP_EOL; echo "SSHトンネリングを開始します。".PHP_EOL; exec( self::$cmd ,$output ,$return_var ); if( $return_var != 0) throw new Exception( $output[0] ); self::set_pid(); } private function set_pid(){ $_cmd = "ps aux | grep '[0-9] ".self::$cmd."' | awk '{print $2}'"; exec( $_cmd ,$outputs ,$return_var ); if( $return_var != 0) throw new Exception( $outputs[0] ); foreach ((array)$outputs as $output) { echo "SSH PID : ".$output.PHP_EOL; self::$pids[] = $output; } } public static function stop(){ echo "SSHトンネリングを終了します".PHP_EOL; foreach (self::$pids as $pid) { $_cmd = 'kill '.$pid; echo $_cmd.PHP_EOL; exec( $_cmd ,$output ,$return_var ); if( $return_var != 0) throw new Exception( $output[0] ); } } }php$dsn = 'mysql:dbname=db;host=127.0.0.1;port='.$tunnel_port; $db_user = 'root'; $db_password = 'root'; try { SSHTunneling::start(); $dbh = new PDO($dsn, $db_user , $db_password); echo "接続成功\n"; SSHTunneling::stop(); } catch (PDOException $e) { echo "接続失敗: " . $e->getMessage() . "\n"; SSHTunneling::stop(); exit(); }
- 投稿日：2019-07-24T01:11:09+09:00
DB(MySQL)から、さらっとデータを取得したい
pythonスクリプトを起動するだけで、DBからデータを取得できるようにしたい。それも簡単に
最近仕事の終わりに広告活動の一環で会社のTwitterに投稿する業務？をしている。私が主にやるのは定型文のツイートだが、そこに載せるデータは毎日変わるため毎回データを手動で参照してツイートを作って投稿してきた。
いや、Botにしろよ・・・。という指摘はもっともだが、やったことないしプロジェクト３つくらい走ってるし、ぶっちゃけコードを書く時間が取れない。
DB周りの接続の仕方さえわかればこっちのものなのでお家で勉強がてら調査してみることにした。
とりあえず、第一段階として、自動で文を生成するようにしたい。そのためにはDBから最新のデータを取得したい。
環境
- MacOS Mojave
- python 3.7.3
- mysql-connector-python 8.0.16
mysql-connector-python のインストール
今回は難しいことせずに、さらっとDBから値を取得するのが目的なので簡単そうだった、mysql-connector-pythonを使う。
$ pip install mysql-connector-python上記ではpipでインストールしたが、私はPyCharmを使っているので
Preferences > Project:yourappname > Project Interpreter > (左下の+ボタン)
から上記packageを検索してinstallして使っている。
ソースコード
test.pyimport mysql.connector def main(): connection = mysql.connector.connect( host='0.0.0.0', # 接続先 port='3306', user='username', # mysqlのuser password='password', # mysqlのpassword database='databasename', use_pure=True, ) # コネクションが切れたときに再接続してくれるように設定 connection.ping(reconnect=True) # 接続できているかの確認 print(connection.is_connected()) cur = connection.cursor() cur.execute("SELECT * from test_table") # 全てのデータを取得 rows = cur.fetchall() for row in rows: print(row) # DB操作が終わったらコネクションを閉じる cur.close() connection.close() if __name__ == '__main__': main()結果
テスト用に作ったDBから値を取得できていることが確認できた
$ python test.py (1, '山田太郎', None, None) (2, '山本太郎', None, None)ここまでできれば、自動化もすぐできそう。
参考