Hatena::ブログ(Diary)

ablog このページをアンテナに追加 RSSフィード

2017-12-10

MySQL Connector/J(JDBC Driver) で接続時に任意の collation_connection をセットする

MySQL Connector/J(JDBC Driver) で接続時に任意の collation_connection をセットする には以下の用に JDBC URL に「connectionCollation=utf8mb4_bin」のように設定すれば良い。

jdbc:mysql://aurora01.cluster-*******.ap-northeast-1.rds.amazonaws.com:3306/mydb?connectionCollation=utf8mb4_bin

参考

MySQL クライアントプログラム mysql、mysqladmin、mysqlcheck、mysqlimport、および mysqlshow は、次のように、使用するデフォルトの文字セットを特定します。

C アプリケーションは、サーバーに接続する前に次のように mysql_options() を呼び出すことによって、OS 設定に基づいて文字セットの自動検出を使用できます。

mysql_options(mysql,
              MYSQL_SET_CHARSET_NAME,
              MYSQL_AUTODETECT_CHARSET_NAME);
MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.4 接続文字セットおよび照合順序

Use characterEncoding=utf8mb4& for jdbc url

jdbc:mysql://x.x.x.x:3306/db?useUnicode=true&characterEncoding=utf8mb4
java - utf8mb4 in MySQL Workbench and JDBC - Stack Overflow

connectionCollation

If set, tells the server to use this collation via 'set collation_connection'

Since version: 3.0.13

MySQL :: MySQL Connector/J 5.1 Developer Guide :: 5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J

2017-12-07

LOAD DATA ステートメントで "Row X was truncated; it contained more data than there were input columns" が発生する

事象

LOAD DATA ステートメントCSV ファイルをロードすると "Row X was truncated; it contained more data than there were input columns" というワーニングが発生し、全ての行がロードされていない。

mysql> LOAD DATA LOCAL INFILE 'test.csv' INTO TEST FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 9 rows affected, 9 warnings (0.04 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 9

Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns

原因

  • CSV ファイルの改行コードが CRLF になっていたため。

解決

  • CSV ファイルの改行コードを LF に変換してロードした。

環境

% mysql -V
mysql  Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using  EditLine wrapper

LOAD DATA ステートメントで発生したワーニングの内容を表示する

LOAD DATA で "Warnings: 48" のようにワーニングが発生した場合に、その内容を見たい場合は、

% mysql  --local-infile -h aurora01.cluster-******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
mysql> LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE TEST FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 34 rows affected, 48 warnings (0.06 sec)
Records: 34  Deleted: 0  Skipped: 0  Warnings: 48

MySQL クライアントで接続時に --show-warnings オプションをつけて接続すると表示される。

% mysql  --local-infile --show-warnings -h aurora01.cluster-******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
mysql> LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE TEST FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Warning (Code 1265): Data truncated for column 'COL1' at row 1
Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns

参考

You should startup mysqld with log-warnings (it should be on by default) and also the max error count

[mysqld]
log-warnings
max-error-count=9999999999
You may have to start the mysql client with
SET SQL_WARNINGS = 1;

This is not mysqld setting, it's a mysql client session setting.

You may want to add show-warnings to mysql client session

mysql -u... -p -hlocalhost --show-warnings
mysql - How do I show warnings when loading data created from mysqldump? - Database Administrators Stack Exchange

Aurora MySQL互換に LOAD コマンドで CSV ファイルをロードしようとすると "ERROR 1148 (42000)" で失敗する

Aurora MySQL互換というより MySQL の話です。


事象

% mysql  --local-infile -h aurora01.cluster-******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
mysql> LOAD DATA LOCAL INFILE '.test.csv' INTO TABLE TEST FIELDS TERMINATED BY ',' ENCLOSED BY '"';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

原因


解決

% mysql  --local-infile -h aurora01.cluster-******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
mysql LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE TEST FIELDS TERMINATED BY ',' ENCLOSED BY '"';

参考

LOAD DATA ステートメントの LOCAL バージョンのサポートに関しては、セキュリティーについての潜在的な問題が 2 つあります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 6.1.6 LOAD DATA LOCAL のセキュリティーの問題

You can specify that as an additional option when setting up your client connection:

mysql -u myuser -p --local-infile somedatabase

This is because that feature opens a security hole. So you have to enable it manually in case you really want to use it.

sql - ERROR 1148: The used command is not allowed with this MySQL version - Stack Overflow

mysql コマンド行クライアントの場合、--local-infile[=1] オプションを指定することによって LOAD DATA LOCAL を有効にするか、--local-infile=0 オプションを指定することによってこれを無効にします。mysqlimport の場合、ローカルデータファイルのロードはデフォルトでオフになっており、--local または -L オプションを使用してこれを有効にします。いずれの場合でも、ローカルロード操作を正常に使用するには、サーバーがこの操作を許可していることが必要。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 6.1.6 LOAD DATA LOCAL のセキュリティーの問題

2017-11-06

SSH のタイムアウトを防止する(Keep Alive を有効にする)

事象

例えば、EC2 の Amazon Linux に ssh で接続して実行時間の長い*1コマンドを実行しているとタイムアウトして以下のメッセージが出力される。

packet_write_wait: Connection to **.***106.21 port 22: Broken pipe

解決

クライアント(自分の場合は Mac)の ~/.ssh/config に以下を追記する。

ServerAliveInterval 60

上記の設定の場合、60秒間隔で Keep Alive のパケットが送信され、タイムアウトしなくなる。


環境


参考

     ServerAliveInterval
             Sets a timeout interval in seconds after which if no data has
             been received from the server, ssh(1) will send a message
             through the encrypted channel to request a response from the
             server.  The default is 0, indicating that these messages will
             not be sent to the server.

*1:かつ実行中に通信が発生しない

2017-09-10

Amazon RDS for Oracle で起動しているプロセスを確認する

Amazon RDS for Oracle で起動しているプロセスは V$PROCESS で確認できる。*1

$ export _JAVA_OPTIONS="-Duser.language=en -Duser.country=US"
$ sql awsuser@orcl.******.ap-northeast-1.rds.amazonaws.com:1521/orcl
Picked up _JAVA_OPTIONS: -Duser.language=en -Duser.country=US

SQLcl: Release 4.2.0 Production on Sun Sep 10 12:27:40 2017

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


	New version: 4.1.0 available to download

Password? (**********?) *************
Last Successful login time: Sun Sep 10 2017 12:27:49 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> set sqlformat ansiconsole
SQL> select * from v$process;
ADDR              PID  SOSID  SPID   STID   EXECUTION_TYPE  PNAME  USERNAME  SERIAL#  TERMINAL  PROGRAM                     TRACEID  TRACEFILE                                                        BACKGROUND  LATCHWAIT  LATCHSPIN  PGA_USED_MEM  PGA_ALLOC_MEM  PGA_FREEABLE_MEM  PGA_MAX_MEM  CON_ID
0000000344B0E280  1                         NONE                             0                  PSEUDO                               /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_ora_0.trc                                         0             0              0                 0            0
0000000344B0F028  2    12011  12011  12011  PROCESS         PMON   rdsdb     1        UNKNOWN   oracle@ip-10-7-2-38 (PMON)           /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_pmon_12011.trc  1                                 797036        974508         0                 974508       0
0000000344B0FDD0  3    12013  12013  12013  PROCESS         PSP0   rdsdb     1        UNKNOWN   oracle@ip-10-7-2-38 (PSP0)           /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_psp0_12013.trc  1                                 772748        949716         0                 949716       0
0000000344B10B78  4    12015  12015  12015  PROCESS         VKTM   rdsdb     1        UNKNOWN   oracle@ip-10-7-2-38 (VKTM)           /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_vktm_12015.trc  1                                 769508        949716         0                 949716       0
0000000344B11920  5    12019  12019  12019  PROCESS         GEN0   rdsdb     1        UNKNOWN   oracle@ip-10-7-2-38 (GEN0)           /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_gen0_12019.trc  1                                 778644        958084         0                 958084       0
(以下略)

参考

*1クライアントはSQLclを使用

2017-09-04

pgbench で RDS PostgreSQL のマスターに負荷をかけてリードレプリカのレプリケーションラグを計測する

pgbench とは

pgbenchとは

pgbenchはPostgreSQLに同梱されているシンプルなベンチマークツールです。最初のバージョンは筆者により作成され、日本のPostgreSQLメーリングリストで1999年に公開されました。その後pgbenchはcontribという付属追加プログラムとして、PostgreSQLのソースコードとともに配布されるようになりました。どのバージョンでPostgreSQLに取り込まれたのかはPostgreSQL付属のドキュメント(HISTORY)には書かれていないので定かではないのですが、コミットログを見ると、おそらく2000年にリリースされたPostgreSQL 7.0で導入されたと思われます。その後数多くの改良がたくさんの人によって行われ、現在に至っています。

(中略)

pgbenchが標準で実行するトランザクションはTPC-Bを想定しているものとはいえ、実際に使ってみると不都合なこともあります。特に問題なのは、上記ステップ4で、pgbench_branchesの行数がスケーリングファクタと同じ(つまりデフォルトでは10)しかないため、同時接続数が10を超えるとロック競合が発生して性能が出なくなるということです。現実のシステムではこのような設計は普通は行わないので、実際のシステムでの性能を推し量るという、ベンチマーク本来の目的にはあまりそぐわないことになります。

そこでpgbenchでは3と4の処理を省略したトランザクションのモードを用意しており、pgbenchを実行するときに"-N"を付けることによって実行できます。Webシステムのように、多数の同時接続を想定している場合は、こちらを使うことをお勧めします。

pgbenchの使いこなし | Let's Postgres

準備

RDS PostgreSQL のマスターとリードレプリカを作成する
EC2 から RDS PostgreSQL に接続してセットアップする
  • EC2 に psql と pgbench をインストールする。
$ sudo yum -y install postgresql
$ sudo yum -y install postgresql-contrib
  • データベースのセットアップ(テーブル作成、データ投入)
$ pgbench -i -s 1000 -U awsuser -h ******.******.ap-northeast-1.rds.amazonaws.com -d mydb
  • テーブルを確認する
$ psql "host=******.*******.ap-northeast-1.rds.amazonaws.com user=awsuser dbname=mydb port=5432"
Password:
mydb=> select * from pg_tables where schemaname='public';
 schemaname |    tablename     | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+------------------+------------+------------+------------+----------+-------------+-------------
 public     | pgbench_branches | awsuser    |            | t          | f        | f           | f
 public     | pgbench_tellers  | awsuser    |            | t          | f        | f           | f
 public     | pgbench_accounts | awsuser    |            | t          | f        | f           | f
 public     | pgbench_history  | awsuser    |            | f          | f        | f           | f
(4 rows)

mydb=> select count(1) from pgbench_branches;
 count
-------
  1000
(1 row)

mydb=> select count(1) from pgbench_tellers;
 count
-------
 10000
(1 row)

mydb=> select count(1) from pgbench_history;
 count
-------
     0
(1 row)

mydb=> select count(1) from pgbench_accounts;
   count
-----------
 100000000
(1 row)

ベンチマーク実施

  • マスターにトランザクションを発行して負荷をかける
    • N: pgbench_tellersとpgbench_branchesを更新しない。マスターでロック競合がボトルネックになるとトランザクションのスループットが伸びずレプリケーション意外のボトルネックで頭打ちになる可能性があるため。
    • r: ステートメント毎の平均レイテンシをレポートする
    • c: クライアント
    • j: ワーカースレッド数
    • t: トランザクション数
    • U: DBユーザー名
    • d: データベース名
$ pgbench -r -c 10 -j 10 -t 10000 -U awsuser -h ******.******.ap-northeast-1.rds.amazonaws.com -d mydb 

(中略)

transaction type: TPC-B (sort of)
scaling factor: 1000
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = ***.****** (including connections establishing)
tps = ***.****** (excluding connections establishing)
statement latencies in milliseconds:

(中略)

	*.********	BEGIN;
	*.********	UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
	*.********	SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
	*.********	UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
	*.********	UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
	*.********	INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
	*.********	END;
  • リードレプリカでレプリケーションラグを確認する
    • マネジメントコンソールの「レプリケーションの詳細」でも確認できる
    • 以下の単位は秒
$ psql "host=******.******.ap-southeast-1.rds.amazonaws.com user=awsuser dbname=mydb port=5432"
Password:

mydb=> SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;

 date_part
-----------
       0
(1 row)

mydb=> SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;
 date_part
-----------
       0
(1 row)
  • マネジメントコンソールの「モニタリング」の「トランザクションログの生成」でトランザクションログの生成量を確認する。

参考