%% MySQL/OTP – MySQL client library for Erlang/OTP
%% Copyright (C) 2014 Viktor Söderqvist
%% 2017 Piotr Nosek
%%
%% This file is part of MySQL/OTP.
%%
%% MySQL/OTP is free software: you can redistribute it and/or modify it under
%% the terms of the GNU Lesser General Public License as published by the Free
%% Software Foundation, either version 3 of the License, or (at your option)
%% any later version.
%%
%% This program is distributed in the hope that it will be useful, but WITHOUT
%% ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
%% FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
%% more details.
%%
%% You should have received a copy of the GNU Lesser General Public License
%% along with this program. If not, see .
%% @doc Eunit test cases for the mysql_protocol module.
%% Most of the hexdump tests are from examples in the protocol documentation.
%%
%% TODO: Use ngrep -x -q -d lo '' 'port 3306' to dump traffic using various
%% server versions.
-module(mysql_protocol_tests).
-include_lib("eunit/include/eunit.hrl").
-include("protocol.hrl").
-include("records.hrl").
resultset_test() ->
%% A query that returns a result set in the text protocol.
Query = <<"SELECT @@version_comment">>,
ExpectedReq = <<(size(Query) + 1):24/little, 0, ?COM_QUERY, Query/binary>>,
ExpectedResponse = hexdump_to_bin(
"01 00 00 01 01|27 00 00 02 03 64 65 66 00 00 00 .....'....def..."
"11 40 40 76 65 72 73 69 6f 6e 5f 63 6f 6d 6d 65 .@@version_comme"
"6e 74 00 0c 08 00 1c 00 00 00 fd 00 00 1f 00 00| nt.............."
"05 00 00 03 fe 00 00 02 00|1d 00 00 04 1c 4d 79 ..............My"
"53 51 4c 20 43 6f 6d 6d 75 6e 69 74 79 20 53 65 SQL Community Se"
"72 76 65 72 20 28 47 50 4c 29|05 00 00 05 fe 00 rver (GPL)......"
"00 02 00 ..."),
ExpectedCommunication = [{send, ExpectedReq},
{recv, ExpectedResponse}],
Sock = mock_tcp:create(ExpectedCommunication),
{ok, [ResultSet]} = mysql_protocol:query(Query, mock_tcp, Sock, infinity),
mock_tcp:close(Sock),
?assertMatch(#resultset{cols = [#col{name = <<"@@version_comment">>}],
rows = [[<<"MySQL Community Server (GPL)">>]]},
ResultSet),
ok.
resultset_error_test() ->
%% A query that returns a response starting as a result set but then
%% interupts itself and decides that it is an error.
Query = <<"EXPLAIN SELECT * FROM dual;">>,
ExpectedReq = <<(size(Query) + 1):24/little, 0, ?COM_QUERY, Query/binary>>,
ExpectedResponse = hexdump_to_bin(
"01 00 00 01 0a 18 00 00 02 03 64 65 66 00 00 00 ..........def..."
"02 69 64 00 0c 3f 00 03 00 00 00 08 a1 00 00 00 .id..?.........."
"00 21 00 00 03 03 64 65 66 00 00 00 0b 73 65 6c .!....def....sel"
"65 63 74 5f 74 79 70 65 00 0c 08 00 13 00 00 00 ect_type........"
"fd 01 00 1f 00 00 1b 00 00 04 03 64 65 66 00 00 ...........def.."
"00 05 74 61 62 6c 65 00 0c 08 00 40 00 00 00 fd ..table....@...."
"00 00 1f 00 00 1a 00 00 05 03 64 65 66 00 00 00 ..........def..."
"04 74 79 70 65 00 0c 08 00 0a 00 00 00 fd 00 00 .type..........."
"1f 00 00 23 00 00 06 03 64 65 66 00 00 00 0d 70 ...#....def....p"
"6f 73 73 69 62 6c 65 5f 6b 65 79 73 00 0c 08 00 ossible_keys...."
"00 10 00 00 fd 00 00 1f 00 00 19 00 00 07 03 64 ...............d"
"65 66 00 00 00 03 6b 65 79 00 0c 08 00 40 00 00 ef....key....@.."
"00 fd 00 00 1f 00 00 1d 00 00 08 03 64 65 66 00 ............def."
"00 00 07 6b 65 79 5f 6c 65 6e 00 0c 08 00 00 10 ...key_len......"
"00 00 fd 00 00 1f 00 00 19 00 00 09 03 64 65 66 .............def"
"00 00 00 03 72 65 66 00 0c 08 00 00 04 00 00 fd ....ref........."
"00 00 1f 00 00 1a 00 00 0a 03 64 65 66 00 00 00 ..........def..."
"04 72 6f 77 73 00 0c 3f 00 0a 00 00 00 08 a0 00 .rows..?........"
"00 00 00 1b 00 00 0b 03 64 65 66 00 00 00 05 45 ........def....E"
"78 74 72 61 00 0c 08 00 ff 00 00 00 fd 01 00 1f xtra............"
"00 00 05 00 00 0c fe 00 00 02 00 17 00 00 0d ff ................"
"48 04 23 48 59 30 30 30 4e 6f 20 74 61 62 6c 65 H.#HY000No table"
"73 20 75 73 65 64 s used"),
Sock = mock_tcp:create([{send, ExpectedReq}, {recv, ExpectedResponse}]),
{ok, [Result]} = mysql_protocol:query(Query, mock_tcp, Sock, infinity),
?assertMatch(#error{}, Result),
mock_tcp:close(Sock),
ok.
prepare_test() ->
%% Prepared statement. The example from "14.7.4 COM_STMT_PREPARE" in the
%% "MySQL Internals" guide.
Query = <<"SELECT CONCAT(?, ?) AS col1">>,
ExpectedReq = hexdump_to_bin(
"1c 00 00 00 16 53 45 4c 45 43 54 20 43 4f 4e 43 .....SELECT CONC"
"41 54 28 3f 2c 20 3f 29 20 41 53 20 63 6f 6c 31 AT(?, ?) AS col1"
),
ExpectedResp = hexdump_to_bin(
"0c 00 00 01 00 01 00 00 00 01 00 02 00 00 00 00| ................"
"17 00 00 02 03 64 65 66 00 00 00 01 3f 00 0c 3f .....def....?..?"
"00 00 00 00 00 fd 80 00 00 00 00|17 00 00 03 03 ................"
"64 65 66 00 00 00 01 3f 00 0c 3f 00 00 00 00 00 def....?..?....."
"fd 80 00 00 00 00|05 00 00 04 fe 00 00 02 00|1a ................"
"00 00 05 03 64 65 66 00 00 00 04 63 6f 6c 31 00 ....def....col1."
"0c 3f 00 00 00 00 00 fd 80 00 1f 00 00|05 00 00 .?.............."
"06 fe 00 00 02 00 ......"),
Sock = mock_tcp:create([{send, ExpectedReq}, {recv, ExpectedResp}]),
Result = mysql_protocol:prepare(Query, mock_tcp, Sock),
mock_tcp:close(Sock),
?assertMatch(#prepared{statement_id = StmtId,
param_count = 2,
warning_count = 0} when is_integer(StmtId),
Result),
ok.
bad_protocol_version_test() ->
Sock = mock_tcp:create([{recv, <<2, 0, 0, 0, 9, 0>>}]),
UndefSSLOpts = undefined,
?assertError(unknown_protocol,
mysql_protocol:handshake("foo", "bar", "db", mock_tcp, UndefSSLOpts, Sock, false)),
mock_tcp:close(Sock).
%% --- Helper functions for the above tests ---
%% Convert hex dumps to binaries. This is a helper function for the tests.
%% This function is also tested below.
hexdump_to_bin(HexDump) ->
hexdump_to_bin(iolist_to_binary(HexDump), <<>>).
hexdump_to_bin(<>, Acc) ->
hexdump_to_bin(Line, Rest, Acc);
hexdump_to_bin(<>, Acc) ->
%% last line (shorter than 70)
hexdump_to_bin(Line, <<>>, Acc);
hexdump_to_bin(<<>>, Acc) ->
Acc.
hexdump_to_bin(Line, Rest, Acc) ->
HexNums = re:split(Line, <<"[ |]+">>, [{return, list}, trim]),
Acc1 = lists:foldl(fun (HexNum, Acc0) ->
{ok, [Byte], []} = io_lib:fread("~16u", HexNum),
<>
end,
Acc,
HexNums),
hexdump_to_bin(Rest, Acc1).
hexdump_to_bin_test() ->
HexDump =
"0e 00 00 00 03 73 65 6c 65 63 74 20 55 53 45 52 .....select USER"
"28 29 ()",
Expect = <<16#0e, 16#00, 16#00, 16#00, 16#03, 16#73, 16#65, 16#6c,
16#65, 16#63, 16#74, 16#20, 16#55, 16#53, 16#45, 16#52,
16#28, 16#29>>,
?assertEqual(Expect, hexdump_to_bin(HexDump)).