Oracle認定 MySQL 5.6 Developer 合格体験記
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
動機
- OSS-DBはgoldまで取ってあるが、業務ではもっぱらMySQLしか使わなそうなので
- 「PostgreSQLだったらこれがあるのに」的なのをなくしたい。MySQLを深く知ることでMySQLを愛せるようになるという期待
- 新CCNAの公式試験対策本の下巻がコロナ禍の影響でいつまで経っても届かないので、届くまでにちゃちゃっと取るか〜という気持ち
勉強期間
5/22~6/6の2週間くらい
勉強時間
45時間くらい
- 「基本的な最適化」でだいぶ時間を溶かしてしまった
- DBA寄りのところまで踏み込みすぎた感
- そのうちAdministrator資格も取るので無駄ではない
- 実行計画改善まわりでサンプルデータの用意に難儀して時間を浪費した。
generate_series
関数が恋しい
- DBA寄りのところまで踏み込みすぎた感
勉強方法
- まともな対策本や教材の類が見つからなかったので、試験公式の「試験内容 チェックリスト」と5.6公式ドキュメントを突き合せながら、実際にSQLコマンドを打つ形で勉強した
- PostgreSQLとの対比をしながら学ぶことで、効果的に学習し、理解を深めることができたように思う。似て非なるものの食べ比べ、大事ですね
所感
(CCNA本が)届くまでにちゃちゃっと取るか〜という気持ち
普通にクソムズでした。普段MySQLを結構使ってる人も、ちゃんと対策しないと落ちるんじゃないでしょうか
- 「試験内容 チェックリスト」から逸脱した出題は(当然)なかったように思う
- OSS-DBのsilver/goldほどアプリケーションエンジニアとDBAで分かれていないなぁ、という印象
- まだMySQL 5.7 Administrator受けてないんですけどね
- けっこうDBA的な知識が問われたように思う
- 「きみ、重箱の隅つつくのうまいねぇ〜」という感じの問題も多め
- 実際に手を動かしていないとなかなか覚えていないような事柄も訊かれる
今後
- MySQL 5.7 Administrator、MySQL 8.0 Administratorも取りたいですね
- DBA的なこともできるようになりたい
- バージョン違いの食べ比べ大事
CCNA試験対策 下巻ch2: Basic IPv4 Access Control Lists
https://www.ciscopress.com/store/ccna-200-301-official-cert-guide-volume-2-9781587147135www.ciscopress.com
- IPv4 Access Control List Basics
IPv4 Access Control List Basics
- source IP address, destination IP address, port numberなどに基づいてパケットを特定する
- もっとも多いユースケースはパケットフィルタリング
- 他の用途にも使える
- QoSなど
ACL Location and Direction
Matching Packets
- source IP address, destination IP address, TCP/UDP port numberなどに基づいてパケットのマッチングを行う
Taking Action When a Match Occurs
- 本書ではフィルタリングのみ扱う
- deny/permit
Types of IP ACLs
Numbered | Named | |
---|---|---|
Standard | ||
Extended |
- 4つに大別される
- 本章ではStandard/Extended Numberedを学ぶ
Standard Numbered IPv4 ACLs
- source IPにのみマッチするやつ
- 1-99, 追加1300-1999
List Logic with IP ACLs
- first-matchなので、狭いのから広げていく
10.1.1.1 permit 10.1.1.x deny 10.x.x.x permit
- 1行でもコマンドが登録されていて何にもマッチしない場合、最後の暗黙のdeny anyにマッチする
Matching Logic and Command Syntax
- グローバルコンフィグでACLを作成
Router(config)#access-list ? <1-99> IP standard access list <100-199> IP extended access list Router(config)#access-list 99 ? deny Specify packets to reject permit Specify packets to forward remark Access list entry comment Router(config)#access-list 99 deny ? A.B.C.D Address to match any Any source host host A single host address Router(config)#access-list 1 deny any
- インタフェースコンフィグでinbound/outbound別にACLの番号を指定する
Router(config-if)#ip access-group 1 ? in inbound packets out outbound packets Router(config-if)#ip access-group 1 in
- コンフィグ確認
Router#show running-config | section access-list access-list 1 deny any Router#show running-config | section interface interface GigabitEthernet0/0/0 ip address 10.1.1.254 255.255.255.0 ip access-group 1 in duplex auto speed auto
- 疎通のできないこと確認
R2#ping 10.1.1.254 Type escape sequence to abort. Sending 5, 100-byte ICMP Echos to 10.1.1.254, timeout is 2 seconds: UUUUU Success rate is 0 percent (0/5)
- 【補】debugにも映らなくなる
Router#debug ip icmp ICMP packet debugging is on Router#
Matching the Exact IP Address
Matching a Subset of the Address with Wildcards
Binary Wildcard Masks
Finding the Right Wildcard Mask to Match a Subnet
Router#show running-config | section access-list access-list 1 permit host 10.1.1.2 access-list 1 deny 10.1.0.0 0.0.255.255
- 10.1.1.2からのpingは通る
host 10.1.1.2
にマッチし、permitされる
R2(config-if)#ip add 10.1.1.2 255.255.255.0 R2(config-if)#end R2#ping 10.1.1.254 Type escape sequence to abort. Sending 5, 100-byte ICMP Echos to 10.1.1.254, timeout is 2 seconds: !!!!! Success rate is 100 percent (5/5), round-trip min/avg/max = 0/1/5 ms
- 10.1.1.3からのpingは通らない
10.1.0.0 0.0.255.255
にマッチし、denyされる
R2(config-if)#ip add 10.1.1.3 255.255.255.0 R2(config-if)#end R2#ping 10.1.1.254 Type escape sequence to abort. Sending 5, 100-byte ICMP Echos to 10.1.1.254, timeout is 2 seconds: UUUUU Success rate is 0 percent (0/5)
0.0.255.255
: wildcard mask (WC mask)- OSPFとかで使ったやつ
- subnetの論理反転
- 変化していいよ、という部分のビットが立っている
Matching Any/All Addresses
- any
Router(config)#access-list 1 deny ? A.B.C.D Address to match any Any source host host A single host address
Implementing Standard IP ACLs
Router#show ip access-lists Standard IP access list 1 10 permit host 10.1.1.2 (10 match(es)) 20 deny 10.1.0.0 0.0.255.255 (5 match(es))
- 統計情報も見られる
Standard Numbered ACL Example 1
- 略
Standard Numbered ACL Example 2
- 「サーバー1からPC-Aへのパケットはpermitして」
- 「サーバー1からPC-Bへのパケットはdenyして」
- standard numbered ACLではこれらを両立できない
- source IP addressでしかマッチングできない
- first-matchなので、「サーバー1から」のコマンドは1つしか適用されない
- extended numbered ACLを使う必要がある
- destination IP addressも込みでルールを記述できる
- 100-199, 追加2000-2699
Router(config)#access-list ? <1-99> IP standard access list <100-199> IP extended access list Router(config)#access-list 101? <100-199> Router(config)#access-list 101 ? deny Specify packets to reject permit Specify packets to forward remark Access list entry comment Router(config)#access-list 101 permit ? ahp Authentication Header Protocol eigrp Cisco's EIGRP routing protocol esp Encapsulation Security Payload gre Cisco's GRE tunneling icmp Internet Control Message Protocol ip Any Internet Protocol ospf OSPF routing protocol tcp Transmission Control Protocol udp User Datagram Protocol Router(config)#access-list 101 permit ip ? A.B.C.D Source address any Any source host host A single source host Router(config)#access-list 101 permit ip 10.1.1.1 ? A.B.C.D Source wildcard bits Router(config)#access-list 101 permit ip 10.1.1.1 0.0.0.0 ? A.B.C.D Destination address any Any destination host host A single destination host Router(config)#access-list 101 permit ip 10.1.1.1 0.0.0.0 10.2.2.2 ? A.B.C.D Destination wildcard bits Router(config)#access-list 101 permit ip 10.1.1.1 0.0.0.0 10.2.2.2 0.0.0.0 ? dscp Match packets with given dscp value precedence Match packets with given precedence value <cr>
- remarkでACLにドキュメントを添えられる
Router(config)#access-list 1 ? deny Specify packets to reject permit Specify packets to forward remark Access list entry comment
Router#show running-config | section access-list access-list 1 permit host 10.1.1.2 access-list 1 deny 10.1.0.0 0.0.255.255 access-list 1 remark sample acl
Troubleshooting and Verification Tips
access-list
コマンド末尾にlog
を追記することでdeny/permit時のログを出力できるらしい- Packet Tracerで動作せず
CCNA試験対策 下巻ch1: Introduction to TCP/IP Transport and Applications
https://www.ciscopress.com/store/ccna-200-301-official-cert-guide-volume-2-9781587147135www.ciscopress.com
TCP/IP Layer 4 Protocols: TCP and UDP
- Application層にいろいろなサービスを提供する
- 例: Error recovery(TCPのみ)
- packet (L3PDU)はrouterで諸々の理由で破棄されることがある
- 誤り
- 輻輳
- 正しいrouteがわからない
- L2でもフレームが失われることあり
- packet (L3PDU)はrouterで諸々の理由で破棄されることがある
- TCPは機能面にすぐれ、UDPは非機能(性能)面にすぐれる
Function | UDP | TCP |
---|---|---|
Multiplexing using ports | o | o |
Error recovery | o | |
Flow control using windowing | o | |
Connection establishment and termination | o | |
Ordered data transfer and data segmentation | o |
Transmission Control Protocol
Multiplexing Using TCP Port Numbers
- どのアプリケーションにデータを渡せばいいかをport numberで識別
- port number
Popular TCP/IP Applications
Port Number | Protocol | Application |
---|---|---|
20 | TCP | FTP data |
21 | TCP | FTP control |
22 | TCP | SSH |
23 | TCP | Telnet |
25 | TCP | SMTP |
53 | UDP,TCP | DNS |
67 | UDP | DHCP Server |
68 | UDP | DHCP Server |
69 | UDP | TFTP |
80 | TCP | HTTP(WWW) |
110 | TCP | POP3 |
161 | UDP | SNMP |
443 | TCP | SSL |
514 | UDP | Syslog |
Connection Establishment and Termination
- three-way connection establishment (three-way handshake)
- コネクションの終了はfour-way
- ニーモニックの意味
- SYN
- synchronize the sequence numbers
- sequence number
- TCPヘッダ20バイトのうち4バイト(4-8)をしめる
- 順番通り届くこと、欠落のなきことの保証につかう
- ACK
- acknowledgment
- FIN
- finished
- SYN
Error Recovery and Reliability
- acknowlegment number:「ここからのデータくれ」
- three-way handshakeで立てるACKフラグとは別物
- sequence numberの抜けに対して問い合わせる
- 最後まで受信したら「次のくれ」と問い合わせる
- forward acknowledgment
- 「Nバイト目まで受け取ったよ」ではなく「N+1バイト目からくれ」という問い合わせ方
- 4バイトのacknowledgmentフィールドを使用する
Flow Control using Windowing
User Datagram Protocol
- UDPヘッダはsource portとdestination port 2バイトずつの計4バイト。超簡素
- したがってport numberによるmultiplexing以外のサービスは提供しない
TCP/IP Applications
Uniform Resource Identifiers
http://www.certskills.com/blog
- 正式な名前
scheme://authority/path
- Webページの例
protocol://server'sname/web page
Finding the Web Server Using DNS
しってるので略
Transferring Files with HTTP
- HTTP/1.0ではrequest/responseごとにTCP接続/切断がなされる
- HTTP/1.1ではConnection: Closeヘッダを指定しない限りTCP接続は保持される
- keep-aliveという
How the Receiving Host Identifiers the Correct Receiving Application
MySQL 5.6 Developer試験対策 6 MySQLの一般的な構文_3
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
MySQLのデータ型の特徴と適切な使用方法を識別する
数値型
整数型
CREATE TABLE tbl (a INT UNSIGNED, b INT); INSERT INTO tbl VALUES (3, 2);
mysql> SELECT a-b FROM tbl; +------+ | a-b | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> SELECT b-a FROM tbl; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '("sample"."tbl"."b" - "sample"."tbl"."a")'
- 一方がunsignedだと結果もunsigned
- この場合、
2-3
をunsignedに格納しようとしてエラーになる NO_UNSIGNED_SUBTRACTION
SQLモードを設定することでこの挙動を変えることができる
mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT b-a FROM tbl; +------+ | b-a | +------+ | -1 | +------+ 1 row in set (0.00 sec)
M
,ZEROFILL
mysql> CREATE TABLE tbl(col INT(10) ZEROFILL); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO tbl VALUES (3); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tbl; +------------+ | col | +------------+ | 0000000003 | +------------+ 1 row in set (0.00 sec)
BOOL
,BOOLEAN
はTINYINT(1)
のシノニム
mysql> CREATE TABLE tbl (col BOOL); Query OK, 0 rows affected (0.01 sec) mysql> DESC tbl; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | col | tinyint(1) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec)
TRUE
,FALSE
自体1
/0
のシノニム
SELECT TRUE,FALSE FROM DUAL; +------+-------+ | TRUE | FALSE | +------+-------+ | 1 | 0 | +------+-------+ 1 row in set (0.00 sec)
- すべての演算は符号付きBIGINT値またはDOUBLE値を用いて行われる
- 型一覧
name | synonym | length (bit) |
---|---|---|
BIT(M) | M (1-64) | |
BOOL | TINYINT(1) | |
TINYINT(M) | 8 | |
SMALLINT(M) | 16 | |
MEDIUMINT(M) | 24 | |
INT(M) | 32 | |
INTEGER(M) | INT(M) | |
BIGINT(M) | 64 | |
SERIAL | BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE |
固定小数点数
DECIMAL[(M[,D])]
- 正確な値を投入するには文字列で
mysql> CREATE TABLE tbl (col DEC(6,4)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO tbl VALUES ('3.1415'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tbl; +--------+ | col | +--------+ | 3.1415 | +--------+ 1 row in set (0.00 sec)
DEC
,NUMERIC
,FIXED
シノニムあり
浮動小数点数
FLOAT[(M,D)]
- IEEE754 単精度 準拠
- H/WやOSによって実際の範囲が狭まることはある
- 演算はDOUBLE値で行われることに注意する
=
使うな的な話
DOUBLE[(M,D)]
- IEEE754 倍精度 準拠
- H/WやOSによって実際の範囲が狭まることはある
- REAL
- DOUBLE(デフォルト),またはFLOATのシノニム
- 'REAL_AS_FLOAT' SQLモードで切り替え
- DOUBLE(デフォルト),またはFLOATのシノニム
日付と時間型
- TIMEは時刻(time point, 00:00:00から23:59:59.fspまで)だけでなく時間(duration)を表現できる
- 838時間まで
0x7FFFFF
のDEC表現8388607
に由来する
- 838時間まで
文字列型
CHAR/VARCHAR
- VARCHARはバイト長を保持するために1-2バイト余計に消費する
- 255バイトまでは+1, 256バイトからは+2
- CHARの末尾空白の挙動
- 一律でカラム定義の桁数だけ消費する
- 基本的に空白を切り詰めたものと区別しない
sql_mode
非依存- 例外: LIKEのみ空白を区別する
mysql> CREATE TABLE tbl(c CHAR(4)); Query OK, 0 rows affected (0.01 sec) mysql> drop table tbl; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE tbl(c CHAR(4) PRIMARY KEY); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO tbl VALUES ('a'); Query OK, 1 row affected (0.00 sec) mysql> SELECT c='a', c='a ', c LIKE 'a', c LIKE 'a ' FROM tbl; +-------+----------+------------+---------------+ | c='a' | c='a ' | c LIKE 'a' | c LIKE 'a ' | +-------+----------+------------+---------------+ | 1 | 1 | 1 | 0 | +-------+----------+------------+---------------+ 1 row in set (0.00 sec)
BINARY/VARBINARY
- バイナリ文字列を格納する
- cf.
CHAR BINARY
は非バイナリ文字列を格納し、バイナリ商号順序を用いる
- cf.
- BINARYは
\0
でパディングされる
mysql> CREATE TABLE tbl (c BINARY(3)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO tbl SET c = 'a'; Query OK, 1 row affected (0.01 sec) mysql> SELECT HEX(c), c='a', c='a\0\0' FROM tbl; +--------+-------+-----------+ | HEX(c) | c='a' | c='a\0\0' | +--------+-------+-----------+ | 610000 | 0 | 1 | +--------+-------+-----------+ 1 row in set (0.00 sec)
BLOB/TEXT
- VARBINARY/VARCHARとの違い
- BLOB/TEXTは、インデックス作成時、プレフィックス長の指定が必要
- BLOB/TEXTは、DEFAULT値をふくめられない
- 長さ
値の長さ | 値の長さ管理バイト長 | 合計 | |
---|---|---|---|
TINYBLOB/TINYTEXT | L < 2^8 | 1 | L+1 |
BLOB/TEXT | L < 2^16 | 2 | L+2 |
MEDIUMBLOB/MEDIUMTEXT | L < 2^24 | 3 | L+3 |
LONGBLOB/LONGTEXT | L < 2^32 | 4 | L+4 |
ENUM/SET
- 文字列のなかま
- SET型は数値コンテキストで取得するとビットセットを得る
- ゆえに64要素まで(64ビット)
mysql> CREATE TABLE tbl(col SET('a','b','c','d')); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO tbl VALUES ('a,c'); Query OK, 1 row affected (0.00 sec) mysql> SELECT col, col+0, BIN(col+0) FROM tbl; +------+-------+------------+ | col | col+0 | BIN(col+0) | +------+-------+------------+ | a,c | 5 | 101 | +------+-------+------------+ 1 row in set (0.00 sec)
- a = 0b0001, c = 0b0101
データ型のストレージ要件
- テーブルの内部表現の最大行サイズは 65,535 バイト
- TEXTやBLOBは外出しされるので9-12バイトしか関与しない
- 【補】PostgreSQLのTOAST(The Oversized-Attribute Storage Technique)みたいな
MySQL 5.6 Developer試験対策 9 MySQL アプリケーションの開発 -- のこり
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
MySQLの標準的なドライバを使用するPHP、Java、および.NET開発の主要な特徴、機能およびオプションを識別する
PHP
- 2つ(古いもの含め3つ)ある
- PHPにはコネクションプールはない
- 単に持続的に接続しているだけ
- 【所感】2020/06/06現在、業務で普段遣いしているのはPDO
- LaravelのConnectionの内部で使われている
Java
- たぶんこれ
.NET
- たぶんこれ
MySQlのエラー・メッセージを解釈する
- エラーメッセージ言語を設定できる
mysqld --lc_messages_dir=/usr/share/mysql --lc_messages=fr_FR
- 上記起動オプションで起動したならば:
- fr_FRが
french
にマッピングされる /usr/share/mysql/french
でエラーメッセージが検索される
- fr_FRが
サーバーのエラーコードおよびメッセージ
エラー: 1045 SQLSTATE: 28000 (ER_ACCESS_DENIED_ERROR) メッセージ: ユーザー '%s'@'%s' のアクセスは拒否されました (使用パスワード: %s)
こういうやつ
クライアントのエラーコードおよびメッセージ
エラー: 2001 (CR_SOCKET_CREATE_ERROR) メッセージ: UNIX のソケット (%d) を作成できません
こういうやつ
使用可能な診断情報を収集する
mysql> DROP TABLE test.no_such_table; ERROR 1051 (42S02): Unknown table 'test.no_such_table'
- テーブルを使用するため診断領域クリア
- 条件が発生したので移入する
mysql> SHOW WARNINGS; +-------+------+------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------+ | Error | 1051 | Unknown table 'test.no_such_table' | +-------+------+------------------------------------+ 1 row in set (0.00 sec)
- 診断情報抽出
GET DIAGNOSTICS CONDITION 1 @p1 = MYSQL_ERRNO,@p2 = RETURNED_SQLSTATE, @p3 = MESSAGE_TEXT; SELECT @p1 AS MYSQL_ERRNO , @p2 AS RETURNED_SQLSTATE , @p3 AS MESSAGE_TEXT FROM DUAL;
+-------------+-------------------+------------------------------------+ | MYSQL_ERRNO | RETURNED_SQLSTATE | MESSAGE_TEXT | +-------------+-------------------+------------------------------------+ | 1051 | 42S02 | Unknown table 'test.no_such_table' | +-------------+-------------------+------------------------------------+ 1 row in set (0.00 sec)
- テーブル不使用、警告も生成しない場合、診断領域はそのまま
mysql> SET @x = 1; Query OK, 0 rows affected (0.00 sec) mysql> SHOW WARNINGS; +-------+------+------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------+ | Error | 1051 | Unknown table 'test.no_such_table' | +-------+------+------------------------------------+ 1 row in set (0.00 sec)
- テーブルを使用せず、エラー生成する場合
- 診断領域をクリアして移入
mysql> SET @x = @@x; ERROR 1193 (HY000): Unknown system variable 'x' mysql> SHOW WARNINGS; +-------+------+-----------------------------+ | Level | Code | Message | +-------+------+-----------------------------+ | Error | 1193 | Unknown system variable 'x' | +-------+------+-----------------------------+ 1 row in set (0.00 sec)
- 条件番号が1までしかないのに2の抽出を試みてみる
- テーブルを触らず、警告発生
- 診断領域はクリアされず、条件が移入される
mysql> GET DIAGNOSTICS CONDITION 2 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec) SHOW WARNINGS; +-------+------+-----------------------------+ | Level | Code | Message | +-------+------+-----------------------------+ | Error | 1193 | Unknown system variable 'x' | | Error | 1758 | Invalid condition number | +-------+------+-----------------------------+ 2 rows in set (0.00 sec)
- 2つめが入ったので今度はちゃんと動く
mysql> GET DIAGNOSTICS CONDITION 2 @p = MESSAGE_TEXT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @p; +--------------------------+ | @p | +--------------------------+ | Invalid condition number | +--------------------------+ 1 row in set (0.00 sec)
- 警告の場合でも、テーブルを触ると診断領域はクリアされる
mysql> DROP TABLE no_such_table; ERROR 1051 (42S02): Unknown table 'sample.no_such_table' mysql> SHOW WARNINGS; +-------+------+--------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------+ | Error | 1051 | Unknown table 'sample.no_such_table' | +-------+------+--------------------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE tbl (d timestamp); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO tbl VALUES ('2039-01-01 00:00:00'); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SHOW WARNINGS; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1264 | Out of range value for column 'd' at row 1 | +---------+------+--------------------------------------------+ 1 row in set (0.00 sec)
診断領域をクリアするか否か
テーブル触る | テーブル触らない | |
---|---|---|
警告 | クリア | |
エラー | クリア | クリア |
- CONDITIONの数はNUMBERで取れる
mysql> DROP TABLE no_such_table; ERROR 1051 (42S02): Unknown table 'sample.no_such_table' mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected (0.00 sec) mysql> GET DIAGNOSTICS @n = NUMBER; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @n; +------+ | @n | +------+ | 3 | +------+ 1 row in set (0.00 sec)
- 関連するシステム変数
mysql> SELECT @@max_error_count,@@warning_count,@@error_count,@@sql_notes; +-------------------+-----------------+---------------+-------------+ | @@max_error_count | @@warning_count | @@error_count | @@sql_notes | +-------------------+-----------------+---------------+-------------+ | 64 | 3 | 3 | 1 | +-------------------+-----------------+---------------+-------------+ 1 row in set (0.00 sec)
max_error_count
を超えて追加しようとした分は暗黙理に捨てられる- RESIGNALによって追加された場合はつねに診断領域に追加され、ところてん式に古いものが捨てられる
warning_count
,error_count
は捨てられた分も数える- 例:
max_error_count
を2にしてみる
mysql> SET @@max_error_count = 2; Query OK, 0 rows affected (0.01 sec)
- 警告を4つ発生させる
mysql> DROP TABLE no_such_table; ERROR 1051 (42S02): Unknown table 'sample.no_such_table' mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec)
- NUMBER (診断領域に実際に格納されている件数)は2
- 上限にかかった
mysql> GET DIAGNOSTICS @n = NUMBER; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @n; +------+ | @n | +------+ | 2 | +------+ 1 row in set (0.00 sec)
- いちばん古い
Unknown table 'sample.no_such_table'
が残っている = 新しいものが捨てられている
mysql> SHOW WARNINGS; +-------+------+--------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------+ | Error | 1051 | Unknown table 'sample.no_such_table' | | Error | 1758 | Invalid condition number | +-------+------+--------------------------------------+ 2 rows in set (0.00 sec)
- システム変数確認
mysql> SELECT @@max_error_count,@@warning_count,@@error_count,@@sql_notes; +-------------------+-----------------+---------------+-------------+ | @@max_error_count | @@warning_count | @@error_count | @@sql_notes | +-------------------+-----------------+---------------+-------------+ | 2 | 4 | 4 | 1 | +-------------------+-----------------+---------------+-------------+ 1 row in set (0.00 sec)
MySQL 5.6 Developer試験対策 9 MySQL アプリケーションの開発 -- memcached API
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
memcached API
【補】環境構築
こんな感じに環境を用意する
5.6だとすぐ使えなそうだったので5.7にしちゃった
docker-compose.yml
version: "3" services: mysql5.7: build: context: ./image/5.7 environment: - "MYSQL_ROOT_PASSWORD=root" tty: true
Dockerfile
FROM mysql:5.7 ADD ./1_setup_memcacmed.sh /docker-entrypoint-initdb.d
1_setup_memcached.sh
#!/bin/sh echo 'setup' mysql -u root -proot -vvv < /usr/share/mysql/innodb_memcached_config.sql echo 'setup done' echo 'install' mysql -u root -proot -vvv <<EOF INSTALL PLUGIN daemon_memcached soname "libmemcached.so"; SELECT 'OK' FROM DUAL; EOF echo 'install done'
- 起動ログ
... mysql5.7_1 | 2020-06-06T00:09:22.282706Z 0 [Note] mysqld: ready for connections. mysql5.7_1 | Version: '5.7.27' socket: '/var/run/mysqld/mysqld.sock' port: 0 MySQL Community Server (GPL) mysql5.7_1 | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it. mysql5.7_1 | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it. mysql5.7_1 | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it. mysql5.7_1 | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it. mysql5.7_1 | mysql5.7_1 | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/1_setup_memcacmed.sh mysql5.7_1 | setup mysql5.7_1 | mysql: [Warning] Using a password on the command line interface can be insecure. mysql5.7_1 | -------------- mysql5.7_1 | create database innodb_memcache mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 1 row affected (0.00 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | CREATE TABLE IF NOT EXISTS `cache_policies` ( mysql5.7_1 | `policy_name` VARCHAR(40) PRIMARY KEY, mysql5.7_1 | `get_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled') mysql5.7_1 | NOT NULL , mysql5.7_1 | `set_policy` ENUM('innodb_only', 'cache_only','caching','disabled') mysql5.7_1 | NOT NULL , mysql5.7_1 | `delete_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled') mysql5.7_1 | NOT NULL, mysql5.7_1 | `flush_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled') mysql5.7_1 | NOT NULL mysql5.7_1 | ) ENGINE = innodb mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 0 rows affected (0.01 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | CREATE TABLE IF NOT EXISTS `containers` ( mysql5.7_1 | `name` varchar(50) not null primary key, mysql5.7_1 | `db_schema` VARCHAR(250) NOT NULL, mysql5.7_1 | `db_table` VARCHAR(250) NOT NULL, mysql5.7_1 | `key_columns` VARCHAR(250) NOT NULL, mysql5.7_1 | `value_columns` VARCHAR(250), mysql5.7_1 | `flags` VARCHAR(250) NOT NULL DEFAULT "0", mysql5.7_1 | `cas_column` VARCHAR(250), mysql5.7_1 | `expire_time_column` VARCHAR(250), mysql5.7_1 | `unique_idx_name_on_key` VARCHAR(250) NOT NULL mysql5.7_1 | ) ENGINE = InnoDB mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 0 rows affected (0.02 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | CREATE TABLE IF NOT EXISTS `config_options` ( mysql5.7_1 | `name` varchar(50) not null primary key, mysql5.7_1 | `value` varchar(50)) ENGINE = InnoDB mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 0 rows affected (0.01 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | INSERT INTO containers VALUES ("aaa", "test", "demo_test", mysql5.7_1 | "c1", "c2", "c3", "c4", "c5", "PRIMARY") mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 1 row affected (0.00 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | INSERT INTO cache_policies VALUES("cache_policy", "innodb_only", mysql5.7_1 | "innodb_only", "innodb_only", "innodb_only") mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 1 row affected (0.01 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | INSERT INTO config_options VALUES("separator", "|") mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 1 row affected (0.00 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | INSERT INTO config_options VALUES("table_map_delimiter", ".") mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 1 row affected (0.00 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | CREATE DATABASE IF NOT EXISTS test mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 1 row affected (0.00 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | CREATE TABLE demo_test (c1 VARCHAR(32), mysql5.7_1 | c2 VARCHAR(1024), mysql5.7_1 | c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1)) mysql5.7_1 | ENGINE = INNODB mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 0 rows affected (0.01 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | INSERT INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0) mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | Query OK, 1 row affected (0.00 sec) mysql5.7_1 | mysql5.7_1 | Bye mysql5.7_1 | setup done mysql5.7_1 | install mysql5.7_1 | mysql: [Warning] Using a password on the command line interface can be insecure. mysql5.7_1 | -------------- mysql5.7_1 | INSTALL PLUGIN daemon_memcached soname "libmemcached.so" mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | InnoDB MEMCACHED: Memcached uses atomic increment mysql5.7_1 | Query OK, 0 rows affected (0.00 sec) mysql5.7_1 | mysql5.7_1 | -------------- mysql5.7_1 | SELECT 'OK' FROM DUAL mysql5.7_1 | -------------- mysql5.7_1 | mysql5.7_1 | +----+ mysql5.7_1 | | OK | mysql5.7_1 | +----+ mysql5.7_1 | | OK | mysql5.7_1 | +----+ mysql5.7_1 | 1 row in set (0.00 sec) mysql5.7_1 | mysql5.7_1 | Bye mysql5.7_1 | install done mysql5.7_1 | ...
- やっていること
- コンフィグテーブルとデモテーブルの確認
mysql> SELECT * FROM innodb_memcache.containers\G *************************** 1. row *************************** name: aaa db_schema: test db_table: demo_test key_columns: c1 value_columns: c2 flags: c3 cas_column: c4 expire_time_column: c5 unique_idx_name_on_key: PRIMARY 1 row in set (0.00 sec) mysql> SELECT * FROM test.demo_test; +----+--------------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +----+--------------+------+------+------+ | AA | HELLO, HELLO | 8 | 0 | 0 | +----+--------------+------+------+------+ 1 row in set (0.00 sec)
innodb_memcache.containers
には、どのスキーマのどのテーブルをどのように解釈するかの情報が格納されるtest.demo_test
の定義と照らす
mysql> DESC test.demo_test; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | c1 | varchar(32) | NO | PRI | NULL | | | c2 | varchar(1024) | YES | | NULL | | | c3 | int(11) | YES | | NULL | | | c4 | bigint(20) unsigned | YES | | NULL | | | c5 | int(11) | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
key_columns: c1 value_columns: c2 flags: c3 cas_column: c4 expire_time_column: c5
- PK, VARCHARなキーが必要
- 3つの管理カラムが必要
- フラグ
- 連番
- expiration timestamp
動作確認
root@db45fbb2747e:/# telnet localhost 11211 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'.
AA
キーによるデータ取得
get AA VALUE AA 8 12 HELLO, HELLO END
BB
キーによるデータ挿入
set BB 10 0 16 GOODBYE, GOODBYE STORED
- SQLで読み取ってみる
mysql> SELECT * FROM test.demo_test; +----+------------------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +----+------------------+------+------+------+ | AA | HELLO, HELLO | 8 | 0 | 0 | | BB | GOODBYE, GOODBYE | 10 | 1 | 0 | +----+------------------+------+------+------+ 2 rows in set (0.00 sec)
set BB 10 0 16 GOODBYE, GOODBYE
BB
キーのレコードの値にGOODBYE, GOODBYE
をセット- 10: 操作のフラグ
- memcachedには無視される
- 0: expiration time (TTL)
16: ブロックバイト長
expiration timeのテスト
set CC 10 10 15 EXPIRATION DEMO STORED
- 10秒すると読めなくなる
get CC VALUE CC 10 15 EXPIRATION DEMO END get CC VALUE CC 10 15 EXPIRATION DEMO END get CC END
- SQLで読むと
mysql> SELECT * FROM test.demo_test; +----+------------------+------+------+------------+ | c1 | c2 | c3 | c4 | c5 | +----+------------------+------+------+------------+ | AA | HELLO, HELLO | 8 | 0 | 0 | | BB | GOODBYE, GOODBYE | 10 | 1 | 0 | | CC | EXPIRATION DEMO | 10 | 2 | 1591403745 | +----+------------------+------+------+------------+ 3 rows in set (0.00 sec)
c4
: 連番- setするたびに増える
c5
: EXPIREする時刻のUNIXタイムスタンプ
【補】memcached APIのトランザクション分離レベル
- READ UNCOMMITTED.
- Dirty Readがおきるので注意する
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO test.demo_test VALUES('DD', 'INSERTED VIA SQL', 12, 8, 0); Query OK, 1 row affected (0.00 sec)
get DD VALUE DD 12 16 INSERTED VIA SQL END
- rollbackするともちろん読めなくなる
mysql> ROLLBACK; Query OK, 0 rows affected (0.01 sec)
get DD END
MySQL 5.6 Developer試験対策 6 MySQLの一般的な構文_2
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
- 大文字と小文字の区別、修飾名、別名、予約語の使用など、MySQLの識別子の実装について説明する
- コメントの構文を識別し、使用する
- プリペアード・ステートメントについて説明する。プリペアード・ステートメントを使用する
大文字と小文字の区別、修飾名、別名、予約語の使用など、MySQLの識別子の実装について説明する
言語構造
スキーマオブジェクト名
- PostgreSQLでいう「データベースオブジェクト」のこと
- スペ終わりは駄目:
mysql> CREATE TABLE `tbl `; ERROR 1103 (42000): Incorrect table name 'tbl '
- クォートを標準SQL準拠にしている場合は
""
でも可
mysql> CREATE TABLE "interval" (begin INT, end INT); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"interval" (begin INT, end INT)' at line 1 mysql> SET sql_mode='ansi_quotes'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE "interval" (begin INT, end INT); Query OK, 0 rows affected (0.01 sec)
1e
とかを使うのはやめようねという話
CREATE TABLE tbl(`1e` INT); INSERT INTO tbl VALUES (2); SELECT `1e`+1,1e+1 FROM tbl;
+--------+------+ | `1e`+1 | 1e+1 | +--------+------+ | 3 | 10 | +--------+------+ 1 row in set (0.00 sec)
- 識別子の長さ上限
識別子の修飾子
schema_name.table.name.column_name
みたいなやつ
mysql> CREATE TABLE .tbl(col INT); Query OK, 0 rows affected (0.01 sec)
- ODBC互換のためにテーブル名に
.
を前置することが許可されている- デフォルトデータベース
関数名の構文解析と解決
mysql> SELECT COUNT(*) FROM tbl; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT (*) FROM tbl; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM tbl' at line 1 mysql> SET sql_mode='IGNORE_SPACE'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COUNT (*) FROM tbl; +-----------+ | COUNT (*) | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec)
- デフォルトで関数名の後にスペースを空けることは認められない
SET sql_mode='IGNORE_SPACE';
すると空けられるようになるSET sql_mode='ANSI;
等、コンポジットモードに含まれている場合でも可
mysql> SET sql_mode='ANSI'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COUNT (*) FROM tbl; +-----------+ | COUNT (*) | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec)
予約語
- 予約語を識別子として使いたい場合はbacktickで囲む
mysql> CREATE TABLE interval (begin INT, end INT); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval (begin INT, end INT)' at line 1 mysql> CREATE TABLE `interval` (begin INT, end INT); Query OK, 0 rows affected (0.02 sec)
- ピリオドの後は予約語でもクォート不要
mysql> CREATE TABLE sample.interval (begin INT, end INT); Query OK, 0 rows affected (0.01 sec)
- 歴史的経緯により例外的に大丈夫なものもある
- 多くの人が使っちゃってたので
mysql> CREATE TABLE date(d date); Query OK, 0 rows affected (0.01 sec)
- 大丈夫なやつ
- ACTION
- BIT
- DATE
- ENUM
- NO
- TEXT
- TIME
- TIMESTAMP
式の構文
NOT
と!
の優先順位の話- デフォルトで
!
はNOT
よりも優先順位が高い
mysql> SELECT NOT ! 1; +---------+ | NOT ! 1 | +---------+ | 1 | +---------+ 1 row in set (0.01 sec) mysql> SELECT NOT NOT 1; +-----------+ | NOT NOT 1 | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT ! ! 1; +-------+ | ! ! 1 | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) mysql> SELECT ! NOT 1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT 1' at line 1
- ので、
!
がNOT
と結合してエラーになる模様 - これを避けるには、
sql_mode
にHIGH_NOT_PRECEDENCE
を設定して!
とNOT
の優先順位を同じにする
mysql> SET sql_mode = 'high_not_precedence'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT NOT ! 1; +---------+ | NOT ! 1 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> SELECT NOT NOT 1; +-----------+ | NOT NOT 1 | +-----------+ | 1 | +-----------+ 1 row in set (0.01 sec) mysql> SELECT ! ! 1; +-------+ | ! ! 1 | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) mysql> SELECT ! NOT 1; +---------+ | ! NOT 1 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec)
コメントの構文を識別し、使用する
SELECT 1+1; #hogehoge SELECT 1+1; -- piyopiyo SELECT 1+ /* multiple-line comment */ 1;
これは駄目:
mysql> SELECT 1+1; --fugafuga +-----+ | 1+1 | +-----+ | 2 | +-----+ 1 row in set (0.00 sec) -> ; ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--fugafuga' at line 1
--
の後はスペースを入れないと単項マイナス2つと解釈される
mysql> SELECT --1; +-----+ | --1 | +-----+ | 1 | +-----+ 1 row in set (0.00 sec)
- MySQLでのみ実行されるコメント
CREATE TABLE tbl(id INT, col INT); INSERT INTO tbl VALUES (1,1),(2,1),(3,2),(4,NULL);
SELECT t1.id AS t1id , t2.id AS t2id , t2.col FROM tbl t1 /*! LEFT */ JOIN tbl t2 ON (t1.id = t2.col);
+------+------+------+ | t1id | t2id | col | +------+------+------+ | 1 | 1 | 1 | | 1 | 2 | 1 | | 2 | 3 | 2 | | 3 | NULL | NULL | | 4 | NULL | NULL | +------+------+------+ 5 rows in set (0.00 sec)
- あまりいい例じゃない
- 普通はMySQL特有のキーワードに対して
/*! */
コメントを付ける- 例:
STRAIGHT_JOIN
,HIGH_PRIORITY
など
- 例:
プリペアード・ステートメントについて説明する。プリペアード・ステートメントを使用する
ユーザー定義変数
こういうやつ:
mysql> SELECT @x; +------+ | @x | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> SET @x = 1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x; +------+ | @x | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> SELECT @x := @x+1; +------------+ | @x := @x+1 | +------------+ | 2 | +------------+ 1 row in set (0.00 sec)
プリペアドステートメント
- ユーザー定義変数を使う
mysql> SET @s = 'SELECT ? FROM DUAL'; Query OK, 0 rows affected (0.00 sec) mysql> PREPARE stmt FROM @s; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> SET @a = 1; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE stmt USING @a; +---+ | ? | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.00 sec)