Hatena::ブログ(Diary)

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

2017-09-15

MySQL で NOT NULL 制約のある列に複数行インサートするとその型のデフォルト値が入る

MySQLSQL モードが STRICT モードでない場合、NOT NULL 制約のある列に複数行インサートするとその型のデフォルト値(0とか空文字)が入る(1行インサートだとエラーで入らない)。


検証結果

  • Amazon Aurora with MySQL Compatibility に接続する
$ mysql -h ******.******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mydb;
Database changed
mysql> create table `not_null_test` (
    ->   `id` int(10) unsigned not null,
    ->   `int_col` int(10) unsigned not null,
    ->   `char_col` char(10)  not null,
    ->   `ts_col` timestamp not null,
    ->   primary key (`id`)
    -> ) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.05 sec)
  • SQL モードは設定されていない
mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.02 sec)
  • 1行インサートはエラーになる
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be null
  • 複数行インサートは成功する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null), (2, null, null, null);
Query OK, 2 rows affected, 4 warnings (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 4
mysql> select * from not_null_test;
+----+---------+----------+---------------------+
| id | int_col | char_col | ts_col              |
+----+---------+----------+---------------------+
|  1 |       0 |          | 2017-09-15 06:54:07 |
|  2 |       0 |          | 2017-09-15 06:54:07 |
+----+---------+----------+---------------------+
2 rows in set (0.02 sec)
  • SQLモードを STRICT_ALL_TABLES にする
mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.03 sec)
  • 1行インサートは失敗する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be null
  • 複数行インサートも失敗する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null), (2, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be nul

参考

単列インサートの場合はNOT NULLが指定されたカラムにNULL値が挿入されるとそのクエリはエラーとなって失敗するが、複数列インサートの場合は警告(warning)を発するものの、クエリは正常に受け付けられる。

その際、NULL値が指定された各カラムにはそれぞれのカラムのデータ型の暗黙的なデフォルト値が挿入される。(数値型なら0、文字列型なら空文字''、etc…)

MySQLにおけるNOT NULLカラムへのインサート時の挙動 - Sojiro’s Blog

NOT NULL として宣言されているカラムへの NULL の挿入。複数行の INSERT ステートメントまたは INSERT INTO ... SELECT ステートメントの場合、このカラムは、そのカラムデータ型の暗黙のデフォルト値に設定されます。これは、数値型では 0、文字列型では空の文字列 ('')、および日付と時間型では「0」の値です。サーバーは SELECT からの結果セットを検査して、それが単一行を返すかどうかを確認しないため、INSERT INTO ... SELECT ステートメントは複数行の挿入と同じ方法で処理されます。(単一行の INSERT の場合は、NULL が NOT NULL カラムに挿入されても警告は発生しません。代わりに、このステートメントがエラーで失敗します。)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5 INSERT 構文

明示的な DEFAULT 句のない NOT NULL カラムに対するデータエントリでは、INSERT または REPLACE ステートメントにカラムの値を含まれていない場合、または UPDATE ステートメントがカラムを NULL に設定する場合、MySQL はその時点で有効な SQL モードに従ってカラムを処理します。

(中略)

セクション5.1.7「サーバー SQL モード」を参照してください。

所定のテーブルに対して、SHOW CREATE TABLE ステートメントを使用すると、どのカラムに明示的な DEFAULT 句があるかを確認できます。

暗黙的なデフォルトは次のように定義されます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.6 データ型デフォルト値

厳密モードは、MySQL が INSERT や UPDATE などのデータ変更ステートメントで無効な値または欠落した値を処理する方法を制御します。値はいくつかの理由で無効になることがあります。たとえば、カラムに対して正しくないデータ型を持っていたり、範囲外であったりすることがあります。値の欠落が発生するのは、挿入される新しい行の非 NULL カラムに値が含まれておらず、そのカラムに明示的な DEFAULT 句が定義されていない場合です。(NULL カラムの場合、値が欠落しているときは NULL が挿入されます。)

厳密モードが有効でない場合、MySQL は無効または欠落した値に対して調整された値を挿入し、警告を生成します (セクション13.7.5.41「SHOW WARNINGS 構文」を参照してください)。厳密モードでは、INSERT IGNORE または UPDATE IGNORE を使用すると、この動作を実行できます。

データを変更しない SELECT などのステートメントの場合、厳密モードでは無効な値はエラーでなく警告を生成します。

厳密モードは、外部キー制約が検査されるかどうかに影響されません。foreign_key_checks を検査に使用できます。(セクション5.1.4「サーバーシステム変数」を参照してください。)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード

2017-09-10

Big Data Architecture Pattern (Polyglot/Lambda/Kappa)

仕事の都合で残念ながら聞けなかったが、db tech showcase 2017 での諸橋さんのセッション「polyglot data acces」の内容を polyglot data access - JPOUG in 15 minutes at db tech showcase Tokyo 2017 - wmo6hash::blog で拝見した。とても勉強になる内容だったのでメモ。

f:id:yohei-a:20170911012430p:image

An Enterprise Architect’s Guide to Big Data Reference Architecture Overview より


Big Data Architecture Patterns

Polyglot
  • 透過的にデータベースから外部のデータソースにアクセスする構成
Lambda
Kappa
  • LinkedIn(当時) の Jay 氏が挙げたアーキテクチャ*2
  • Lambda Architectureの複雑性に対して問題を提起
  • ストリーム処理システムでバッチと同様の 精度を保証する対処をして構成をシンプル化 した構成

今回の dbts で佐藤さんが紹介していた Apache Kudu*3 や Big Query の Stream Insert は Lambda アーキテクチャのような複雑が不要でシンプルだと思う。

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 に接続してセットアップする
$ 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)

ベンチマーク実施

$ 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)

参考

2017-08-13

ソースDBがASMの場合、DMS の CDC でレプリケーションするには Oracle Binary Reader を使う

DMS の CDC で Oracle Database からレプリケーションをする場合*1、REDOログを読む方法は以下の2種類があり、

データベースファイルを ASM に置いている場合は、Oracle Binary Reader を使う必要があるようだ。

具体的には Extra connection attributes で以下のように設定する必要がある。

useLogminerReader=N;asm_user=<asm_username>;asm_server=<first_RAC_server_ip_address>/+ASM

参考

AWS Database Migration Service User Guide (Version API Version 2016-01-01)
  • Using Oracle LogMiner or Oracle Binary Reader for Change Data Capture (CDC)

By default, AWS DMS uses Oracle LogMiner for change data capture (CDC). Alternatively, you can choose to use the Oracle Binary Reader. The Oracle Binary Reader bypasses LogMiner and reads the logs directly. To enable the Binary Reader, you need to modify your source connection to include the following extra connection parameters:

useLogminerReader=N; useBfile=Y                                                                                         

To enable Logminer, you need to modify your source connection to include the following extra connection parameter or leave the Extra Connection Attribute blank (Logminer is the default):

useLogminerReader=Y

If the Oracle source database is using Oracle ASM (Automatic Storage Management), the extra connection parameter needs to include the asm username and asm server address. The password field will also need to have both passwords, the source user password, as well as the ASM password.

useLogminerReader=N;asm_user=<asm_username>;asm_server=<first_RAC_server_ip_address>/+ASM

If the Oracle source database is using Oracle ASM (Automatic Storage Management), the endpoint password field needs to have both the Oracle user password and the ASM password, separated by a comma.

Example: <oracle_user_password>,<asm_user_password>
Using an Oracle Database as a Source for AWS Database Migration Service - AWS Database Migration Service

2017/08/13 太平洋上空にて

*1:ソースDBOracle Database の場合

2017-08-11

SQL Workbench/J で Amazon Redshift に接続する

SQL Workbench/J をインストールして Amazon Redshift に接続するまでの手順をメモ。SQL Workbench/J は Java で書かれた GUI および CUI から SQL を実行できるツールで、様々な RDBMS に使える。

SQL Workbench/J is a free, DBMS-independent, cross-platform SQL query tool. It is written in Java and should run on any operating system that provides a Java Runtime Environment.

Its main focus is on running SQL scripts (either interactively or as a batch) and export/import features. Graphical query building or more advanced DBA tasks are not the focus and are not planned

SQL Workbench/J -  Home

環境

  • macOS Sierra

インストール

SQL Workbench/J
Amazon Redshift JDBC Driver

設定

  • アプリケーションフォルダで SQLWorkbenchJ を開く。
  • [Select Connection Profile] で [Manager Drivers] を選択する。
  • 新しくドライバーの設定を以下の通り追加して [OK] をクリックする。
    • Name: Redshift (任意の名前を設定する)
    • Library: フォルダアイコンをクリックして RedshiftJDBC42-1.2.1.1001.jar を選択する。

Redshift クラスターに接続する

  • [Select Connection Profile] で以下の通り設定し、[OK]をクリックする。
    • Driver: Redshift(↑で設定した名前)を選択する
    • URL: jdbc:redshift://エンドポイント:ポート/データベース
      • 例) jdbc:redshift://***.***.ap-northeast-1.redshift.amazonaws.com:5439/mydb
      • AWSマネジメントコンソールで接続したいクラスターの[設定]タブ-[データベースのプロパティ]-[JDBC URL] を指定する。
    • Username: クラスター作成時に設定したユーザー名を指定する
    • Password: クラスター作成時に設定したパスワードを指定する

使ってみる

  • [Tools]-[ShowDbTree] を選択する。
  • 表などを選択して、右クリックして[Put Select Into]-[StatementN] を選択する。
  • SQLを選択して、実行ボタンをクリックする。

f:id:yohei-a:20170811175736p:image


補足

SQL Workbench/J can also be used from the command line without starting the GUI, e.g. when you only have a console window (Putty, SSH) to access the database. In that case you can either run scripts using the batch mode, or start SQL Workbench/J in console mode, where you can run statements interactively, similar to the GUI mode (but of course with less comfortable editing possibilities).

19. Using SQL Workbench/J in console mode
$ sudo find / -type f -name sqlworkbench.jar
/Applications/SQLWorkbenchJ.app/Contents/Java/sqlworkbench.jar

参考