Prepared Statement의 패킷3

Placeholder Packet

Placeholder Packet에는 placeholder에 관한 정보가 들어있다. 

이 레이아웃은 현재에는 Field Packet하고 같은 구성으로 되어있다. 

그렇지만 아래와 같이 거의가 고정값으로 되어있다. 

Placeholder Packet

패킷헤더: 4바이트

카탈로그명: LCS(4바이트고정)    ex> 0x03 64 65 66("def") 고정

데이터베이스명: LCS(1바이트고정)  , 0x00고정

데이터베이스명: LCS(1바이트고정)  , 0x00고정

테이블명의 Alias:  LCS(1바이트 고정), 0x00고정

테이블명: LCS(1바이트고정), 0x00고정

컬럼명의 Alias: LCS(2바이트고정) ex>0x01  3f 고정

컬럼명: LCS(1바이트고정) , 0x00고정

 filler:1바이트, 0x0c고정

캐릭터셋번호: 2바이트, 0x3f 00고정

표시크기: 4바이트, 0x00 00 00 00 고정

데이터형: 1바이트, 0xfd고정

데이터형특성: 2바이트, 0x80 00(BINARY)고정

 Decimal형의 스케일: 1바이트, 0x00고정( Decimal형이어도 0x00고정)

 filler: 2바이트, 0x00 00고정

※Field Packet하고 기본적으로 같은 포맷

EOF Packet하고 FieldPacket의 구성은 보통 검색용 SQL문의 경우하고 같다. 

갱신용 SQL문의 경우에는 Placeholder Packet다음에 그냥 EOF Packet으로 끝난다.

Prepared Statement의 패킷2

Prepare Header Packet

Prepare 명령을  올바르게 수신했다라는 신호로써 선두 1바이트는 0x00이 된다. 

statement ID라는 것은 Prepared statement오브젝트를 유일하게 식별하기 위한 번호이다. 

이후의 Prepared statement처리에는 클라이언트와 서버의 사이에 이 statement ID가 주고받게 되는 것이다. 

컬럼수는 실행하는 SQL문이 반환하는 컬럼 수를 말한다. 

이 값이 Field Packet의 수하고 일치하게 된다. 

이것은 Result Set Header Packet하고도 같다.  

갱신용의 경우 결과셋을 반환하지 않기 때문에 0(0x00)이 된다. 

PlaceHolder수는 SQL문중에 지정한 placeholder(「?」)의 수가 몇개있는지 나타내고 있다. 

이 수는 앞으로 이야기하게 될 Placeholder Packet의 수하고 일치한다. 

Prepare Header Packet

패킷헤더: 4바이트

OK통지: 1바이트 , 0x00고정

statement ID: 4바이트,  mysqld내부에 관리하고 있는 Prepared statement오브젝트를 유일하게 식별하기 위한 번호

컬럼수: 2바이트, 반환하는 컬럼수

placeholder수: 2바이트

filler: 1바이트, 0x00고정

경고수: 2바이트

Prepared Statement의 패킷1

Prepare Command Packet

Prepared statement에서는 실제의 SQL문을 실행하지 전에 Prepare명령을 실행한다. 

이 시점에서 클라이언트에서 서버로 Command Packet이 보내어진다. 

명령어번호는 0x16(COM_STMT_PREPARE)로 되고, 보통의 SQL문의 0x03(COM_QUERY)하고 다르다. 

그 후에는 SQL문이 그대로 기술되어진다. 

Prepared statement에는 통상 placeholder(「?」)이 포함되지만  이것은 「?」라는 문자열(0x3f)가 그대로 보내지고 특별한 취급은 하지 않는다. 

Prepare Command Packet

패킷헤더: 4바이트

명령어번호: 1바이트 , Prepare때는 0x16(COM_STMT_PREPARE)

 SQL문본체: n바이트,  Prepare때의 SQL문이 그대로 보내어진다. 

   ex> SELECT * FROM tbl1 WHERE col1 <= ? AND col2 =?;

     -->53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 74 62 6c 31 20 57 48 45 52 45 20  63 6f 6c 31 20 3c 3d 20 3f 20 41 4e 44 20 63 6f 6c 32 20 3d 20 3f

서버에서 클라이언트로는 검색용의 경우는 다음의 구성으로 패킷이 반환된다. 

 Prepare Header Packet 1패킷

 Placeholder Packets  placeholder갯수만큼의 패킷

 EOF Packet 1패킷

 Field Packets 컬럼수만큼의 패킷

 EOF Packet  1패킷

갱신용(결과셋이 반환되지 않는 명령)의 경우는 Field Packet이후는 보내어지지 않지만 Prepare Header Packet, Placeholder Packets, EOF Packet는 보내어진다. 

Prepared Statement의 패킷

Prepared Statement의 패킷(Prepare할 때)

Prepared Statement의 송수신시 패킷은 보통 SQL문 실행할 때와 상당히 다르다.

보통 SQL에서는 한번 왕복으로 완료되지만 Prepared Statment의 경우에는 Prepare할 때, Execute할 때에 각각의 패킷이 주고 받아진다 .

패킷 flow

클라이언트 -> 서버 ①Prepare Command Packet(mysql_stmt_prepare())

서버->클라이언트 ②Prepare Packet(_Header Packet, _Placeholder Packets, _EOF Packet, _Field Packets, _EOF Packet)

클라이언트->서버 ③Execute Command Packet(mysql_stmt_execute())

서버->클라이언트 ④Execute Packet(_ResultSet Header Packet, _Field Packets, _EOF Packet, PreparedStatement Row Data Packets, _EOF Packet)

Prepare할 때(MySQL C API에서는 mysql_stmt_prepare()에 해당한다. )에 어떤 패킷들이 주고 받아지는지 알아보자.

검색용 SQL문 실행시의 패킷4

EOF Packet

컬럼수만큼의 Field Packet뒤에 EOF Packet이 보내어진다. 

이것은 OK Packet하고 비숫하지만 패킷헤더 직후의 값이  0xfe로 고정되어 있는 점, affected rows하고 insert_id가 없는점, Warning Count하고 Server Status의 순서가 어떤이유에서인지 뒤바뀌어 있다는 점이 다르다. 

EOF Packet

패킷헤더: 4바이트

Field Count:1바이트, 0xfe고정

Warning Count: 2바이트

Status Flags: 2바이트

Row Data Packet

Row Data Packet에는 컬럼값의 정보가 포함되어 있다. 

컬럼값은 Length Coded String표현이다. 이것은  수치형이어도, 날짜/시각형, 바이너리형이어도 같다. 

Row Data Packet1패킷은 1레코드에 해당된다. 

1패킷안에 컬럼수만큼의 정보가 반복포함되어 있다. 

나중에 설명하게 되는 Prepared Statement의 경우에도 검색용 처리라면 같은 형식으로 Row Data Packet을 반환하지만 포맷이 약간 다르다. 

레코드수 만큼의 Row Data Packet이 보내어진 다음에 EOF Packet이 1바이트 추가된다. 

여기에서 검색용 패킷은 완료되고 OK Packet은 사용되지 않는다. 

또 SQL구문에러등이 발생한 경우에는 검색용 패킷 대신에 Error Packet이 보내어진다. 

Row Data Packet

패킷헤더: 4바이트

컬럼수 : LCS   , 문자열크기+컬럼값 (수치여도 LCS표기)

*첫번째컬럼값이 「1」,  두번째 컬럼이 「abc」, 세번째 컬럼이 「2008-12-30 16:18:17」 인 경우 패킷예   

01 31 03 61 62 63 13 32 30 30 38 2d 31 32 2d 33 30 20 31 36 3a 31 38 3a 31 37

검색용 SQL문 실행시의 패킷3

Field Packet

Field Packet에서는 컬럼정보가 반환된다.  구체적으로는 컬럼명, 컬럼명의 Alias,컬럼이 속해있는 테이블명, 데이터베이스명, 캐릭터셋, 표시길이, 데이터형, 정합성제약등이 포함된다. 

하나의 컬럼이 하나의 Field Packet에 대응된다. 

카타로그명은 현재 「def」라는 문자열로 고정되어 있다. Length Coded String이므로 문자열 크기를 나타내는 1바이트가 선두에 붙어서 합계 4바이트가 된다. 

데이터베이스명, 테이블명의 alias, 테이블명, 컬럼의 alias, 컬럼명도 같은 형태로 Length Coded String 표기로 반환된다. 

표시길이라는 것은 명령어라인툴 mysql등에서 컬럼값이 화면에 표시될 때의 폭에 사용된다. 

INTEGER형이라면 기본값으로 INT(11)로 정의되고 이 경우 화면표시 폭은 11이다. 

이럴 때  0x0b(11)이 반환된다. 

데이터형은 문자그대로 컬럼의 데이터형을 말한다. 

CHAR/VARCHAR/TINYTEXT...등의 어플리케이션에서 지정하는 데이터형하고는 다소 다른 분류를 가지고 있다. 

데이터형 특성이라는 것은  유일(UNIQUE) 키 제약이 있는지 없는지 , NOT NULL제약이 있는지 어떤지 등의 정보를 말한다. 

정합성제약이외에도 대문자, 소문자를 구별하는 BINARY속성이 있는지 어떤지 하는 정보도 포함된다. 

이외에도 DECIMAL형의 경우에는 정도(스케일)를 나타내는 정보도 반환된다. 

또, 이 패킷은 한개의 패킷이 완료될 때마다 반환되는 것이 아니다.  그런 무식한 코딩을 할 이유가 없기 때문이다. 

당연하지만 버퍼에 쌓여 서버측 처리가 완결된 시점이나 버퍼가 꽉 찼을 때에 비로소 처음으로 클라이언트에 보내진다. 이것은 다른 패킷도 마찬가지이다. 

*Field Packet

패킷헤더: 4바이트

카타로그명: LCS(4바이트고정), 현재는 def고정(문자열크기+ def)합계 4바이트

데이터베이스명:LCS, 문자열크기+ 데이터베이스명 

데이블명의 alias: LCS, 문자열크기+데이블명의 alias

테이블명: LCS, 문자열크기 + 원래의 테이블명

컬럼명의 alias: LCS, 문자열크기 + 컬럼명의 alias

컬럼명: LCS, 문자열크기+ 원래의 컬럼명

filler:1바이트 , 0x0c고정

캐릭터셋번호: 2바이트, 16진수의 정수표현

표시길이: 4바이트, 화면표시길이 

데이터형: 1바이트, 컬럼의 데이터형

데이터형특성: 2바이트, xx_FLAG

Decimal형의 스케일: 1바이트,  Decimal형이 아닌 경우에는 0x00

filler: 2바이트, 0x00 00고정

기본값: LCS, 기본값제약의 값. COM_FIELD_LIST명령어를 수신했을 경우에 한해서 부가되어짐.

*데이터형의 종류

MYSQL_TYPE_DECIMAL   0(10진수)

MYSQL_TYPE_TINY 1(10진수)

MYSQL_TYPE_SHORT 2(10진수)

MYSQL_TYPE_LONG 3(10진수)

MYSQL_TYPE_FLOAT 4(10진수)

MYSQL_TYPE_DOUBLE 5(10진수)

MYSQL_TYPE_NULL 6(10진수)

MYSQL_TYPE_TIMESTAMP 7(10진수)

MYSQL_TYPE_LONGLONG 8(10진수)

MYSQL_TYPE_INT24 9(10진수)

MYSQL_TYPE_DATE 10(10진수)

MYSQL_TYPE_TIME 11(10진수)

MYSQL_TYPE_DATETIME 12(10진수)

MYSQL_TYPE_YEAR 13(10진수)

MYSQL_TYPE_NEWDATE 14(10진수)

MYSQL_TYPE_VARCHAR 15(10진수)

MYSQL_TYPE_BIT 16(10진수)

MYSQL_TYPE_NEWDECIMAL 246(10진수)

MYSQL_TYPE_ENUM 247(10진수)

MYSQL_TYPE_SET 248(10진수)

MYSQL_TYPE_TINY_BLOB 249(10진수)

MYSQL_TYPE_MEDIUM_BLOB 250(10진수)

MYSQL_TYPE_LONG_BLOB 251(10진수)

MYSQL_TYPE_BLOB 252(10진수)

MYSQL_TYPE_VAR_STRING 253(10진수)

MYSQL_TYPE_STRING 254(10진수)

MYSQL_TYPE_GEOMETRY 255(10진수)

*데이터형특성의 종류

NOT_NULL_FLAG 2^0 NOT NULL

PRI_KEY_FLAG 2^1 주키의 일부

UNIQUE_KEY_FLAG 2^2 유일(UNIQUE)키의 일부

MULTIPLE_KEY_FLAG 2^3 멀티컬럼인텍스를 구성함

BLOB_FLAG 2^4 BLOB형

UNSIGNED_FLAG 2^5 UNSIGNED형

ZEROFILL_FLAG 2^6 0으로 채움

BINARY_FLAG 2^7 BINARY형

ENUM_FLAG 2^8 ENUM형

AUTO_INCREMENT_FLAG 2^9 AUTO_INCREMENT

TIMESTAMP_FLAG 2^10 TIMESTAMP형

SET_FLAG 2^11 SET형

NO_DEFAULT_VALUE_FLAG 2^12 DEFAULT제약이 없음

NUM_FLAG 2^15 NUMERIC형

검색용 SQL문 실행시의 패킷2

ResultSet Header Packet

여기에서는 반환되는 칼럼수(레코드수가 아니라)가 몇개인가를 나타낸다. 

이 컬럼수하고 같은 수만큼 Field Packet이 반복되어진다. 

이것은 Length Coded Binary표기법으로 SELECT col1 FROM tal1이면 0x01, 컬럼이 2개면 0x02가 된다.  대부분의 경우에는 반환되는 컬럼수가 255을 넘어서지 않으므로 1바이트로 컬럼수를 표현하게 된다.

ResultSet Header Packet

패킷헤더 : 4바이트 

Field Count: 1-9바이트, 컬럼수. Length Coded Binary표기 

 

검색용 SQL문실행시의 패킷1

여기에서는 다음과같은 SELECT문을 실행했을 경우에 주고받는 패킷을 알아보자.

*SELECT문의 실행예

mysql>SELECT * FROM tbl1 where col1 <= 3 AND col2= 'abc';

+-----------------------------+

col1   | col2  | col3

1        | abc | 2008-01-15 20:00:01

2        | abc   | 2008-01-17 20:00:02

+-----------------------------+

2 rows in set( 0.00 sec)

클라이언트에서 서버에 보내지는 Command Packet에 대해서는 기본적으로 INSERT문하고 비숫하다.

Command Packet(SELECT문)

패킷헤더 :4바이트

명렁어번호: 1바이트, 보통 SQL문이면 0x03(COM_QUERY)

 SQL문본체: n바이트,  SQL문 본체. 0x00으로 끝나지 않음. 

 

서버에서 클라이언트에 대해서는 결과셋이 반환되기 때문에 단순히 OK/Error Packet이 되지는 않는다. 다음과 같은 구성으로 반환된다. 

ResultSet Header Packet  1패킷

Field Packets  컬럼수 분의 패킷

EOF Packet  1패킷

Row Data Packets 레코드수 분의 패킷

EOF Packet 1패킷

갱신용 SQL문 실행시의 패킷2

OK/Error Packet

서버에서 클라이언트에 되돌려 보내지는 패킷은 인증시와 마찬가지로 OK Packet또는 Error Packet이 된다. OK Packet은 다음과 같다. 

OK Packet(INSERT문 실행시)

  패킷헤더 : 4바이트 

  필드수: 1바이트     , 0x00으로 고정(SELECT문같은 결과셋을 반환하는 것이 아니니까)

  affected rows: 1-9바이트, 처리된 레코드수 Length Coded Binary표기

  insert_id: 1-9바이트, 세션변수insert_id의 값. Length Coded Binary표기

  server status: 2바이트

  warning count: 2바이트 

affected rows하고 insert_id는 Length Coded Binary라는 표기로 되어있다. 

이것은 선두 1~9바이트로 수치를 표현하는 방법이다. Length Coded String하고 비슷하지만 뒤에 문자열이 계속되지 않는 다는 것이 다른 점이라 할 수 있다. 

 1행이 갱신(INSERT)되었을 경우에는 affected rows는 0x01 00이 된다. 

앞에서 이야기 한적이 있지만 real endian표기가 됨으로 0x00 01이 되지는 않는다.  

또 AUTO_INCREMENT속성이 없는 데이블에 대한 갱신인 경우에는 insert_id에는 영향이 없다. 

갱신용 SQL문 실행시의 패킷1

계속해서 SQL문을 실행했을 때 송수신되는 패킷에 대해서 이야기해보자. 

갱신용하고 검색용 패킷은 꽤 다르지만 갱신용이 간단하니까 이것부터 설명한다. 

갱신용 SQL문에서는 클라이언트에서 Command Packet을 서버에 송신하고, 서버는 OK Packet또는 Error Packet을 리턴하는 흐름을 가진다. 

Command Packet

클라이언트에서 서버에 대해서 SQL문을 송출하는 것이다. 이때 패킷에는 SQL문이외에도 서버에 대한 명령(커맨드)을 나타내는 바이트열도 포함된다. 

그런 의미도 있어서 이 패킷을 「Command Packet」이라고 부른다. 

다음과 같은 insert문을 실행한 경우를 알아보자. 

INSERT문의 실행예

 mysql> INSERT INTO tbl1 VALUES(5, 'xyz', NOW() );

Query OK, 1 row affected ( 0.16 sec)

Command Packet(INSERT문)

패킷헤더:  4바이트

커맨드번호: 1바이트 ,  보통의 SQL문장이면 0x03(COM_QUERY)

SQL문본체: n바이트, SQL문본체로써 NULL(0x00)으로 끝나지는 않는다. 

             위의 예제의 경우 49  4e 53 45 52 54 20 49 4e 54 4f 20.... (INSERT INTO )

패킷헤더의 바로 뒤 1바이트는 「커맨드번호」이다.  그 뒤에 실제로 발행되는 SQL문이 그대로 전송된다. 커맨드번호라는 것은 명령의 종류를 나타내는 것으로 보통 SQL문은 전부 COM_QUERY(0x03) 커맨드이다. 

(CREATE TABLE등의 DDL문, SELECT/INSERT/UPDATE/DELETE문, SET문등)

현재 데이터베이스를 변경하는 경우라든지 prepared statement를 실행하는 경우등은 다른 커맨드번호가 할당된다. 

서버측에서는 커맨드번호에 대해서 적절한 처리를 실행한다.  커맨드종류에는 다음과 같은게 있다. 

COM_SLEEP     

COM_QUIT          mysql_close

COM_INIT_DB    mysql_select_db

COM_QUERY       mysql_query

COM_FIELD_LIST     mysql_list_fields

COM_CREATE_DB      mysql_create_db

COM_DROP_DB     mysql_drop_db

COM_REFRESH     mysql_reload, mysql_refresh

COM_SHUTDOWN      mysql_shutdown

COM_STATISTICS      mysql_stat

COM_PROCESS_INFO  mysql_list_processes

COM_CONNECT

COM_PROCESS_KILL     mysql_kill

COM_DEBUG    mysql_dump_debug_info

COM_PING     mysql_ping

COM_TIME

COM_DELAYED_INSERT    

COM_CHANGE_USER     mysql_change_user

COM_BINLOG_DUMP   (replication용)

COM_TABLE_DUMP      (replication용)

COM_CONNECT_OUT   (replication용)

COM_REGISTER_SLAVE (replication용)

COM_STMT_PREPARE  mysql_stmt_prepare

COM_STMT_EXECUTE  mysql_stmt_execute

COM_STMT_SEND_LONG_DATA   mysql_stmt_send_long_data

COM_STMT_CLOSE   mysql_stmt_close

COM_STMT_RESET   mysql_stmt_prepare(reset_stmt_handle)

COM_SET_OPTION  mysql_set_server_option

COM_STMT_FETCH  mysql_stmt_fetch

COM_DAEMON

COM_END

SQL문본체에는 구분자인 세미콜론(;)은 포함되지 않는다. 또 다음과 같은 복수의 SQL문을 합쳐서 실행하는 경우는 복수의 SQL문발행패킷로 분할되어 송신된다. 

복수의 SQL문을 실행

mysql> SELECT 1; SELECT 2;

이런 경우는 SELECT 1을 실행하는 패킷하고 SELECT 2를 실행하는 패킷이 각각 생성되어 그 순서로 송신되게 된다. 

MySQL통신프로토콜 - 인증시의 패킷5

Error Packet

Error Packet이라는 것은 처리가 실패했다라는 것을 서버에서 클라이언트에 통지하는 것이다. 

인증시 실패했을 경우 이외에 SQL구문에러가 된 경우에도 송신된다. 

에러 코드, SQL State코드, 에러 메세지가 포함되어 있다. 

클라이언트는 패킷헤더의 뒤쪽 선두 1바이트가 0xff인지 아닌지 확인하는 것으로 Error Packet인지 판단가능하다. 

Error Packet

패킷헤더 : 4바이트

필드수  : 1바이트 , oxff로 고정

MySQL에러코드: 2바이트,  MySQL고유의 에러코드, 16진수표기

sql state marker: 1바이트, 항상 0x23(#)

sqlstate의 값: 5바이트, 문자열표기(1자리당 1바이트에 대응)

에러메세지: n바이트, 에러메세지본체.  0x00로 끝나지 않음(패킷헤더가 있기 때문에 사이즈확인가능)

  ex>Access denied for user 'user1'@'localhost' (using password: YES)

  41 63 63 65 73 73 20 

  64 65 6e 69 65 64 20 

  66 6f 72 20 

  75 73 65 72 20

  27 75 73 65 72 31 27 

  40 27 6c 6f 63 61 6c 68 6f 73 74 27 20

  28 75 73 69 6e 67 20

  70 61 73 73 77 6f 72 64 3a 20

  59 45 53 29 

  

  

  

MySQL통신프로토콜 - 인증시의 패킷4

OK Packet

OK Packet이라는 것은 서버에서 클라이언트에 대해서 처리가 성공했다는 것을 통지하는 것이다. 

인증에 성공했을 경우이외에 갱신계 SQL문(결과셋을 반환하지 않는 SQL문)에서도 사용된다. 

이를 위해서 affected rows, insert_id등 갱신할 때 사용되는 정보도 정의에는 포함된다. 

인증시에는 사용되지않기때문에 이 수치는 전부 0x00이 된다. 

클라이언트는 패킷헤더뒤의첫번째 1바이트가 0x00인가 아닌가를 확인하는 것으로 

OK Packet인가아닌가 판단하는 것이 가능하다. 

OK Packet

페킷헤더  4바이트

필드수 1바이트 ox00로 고정(select문같은 결과셋을 반환하지 않으므로)

affected rows  1-9바이트  처리된 레코드수, 인증하는 경우등은 0x00

insert_id 1-9바이트  세션변수insert_id의 값, 인증하는 경우등은 0x00

server status 2바이트

warning count 2바이트