silviucpp / mysql_pool Goto Github PK

erlang: 26.2.1
mysql_pool: master

when i start up with rebar3 shell

[error] gen_server <0.639.0> terminated with reason: {error,{1105,<<"HY000">>,<<"Connections using insecure transport are prohibited. See https://docs.pingcap.com/tidbcloud/secure-connections-to-serverless-tier-clusters">>}}

config:
{mysql_pool, [
{pools, [
{pool_tidb, [
{size, 5},
{connection_options, [
{host, "xxx"},
{user, "root"},
{port, 4000},
{password, "123"},
{database, "dbdb"},
{ssl_ca, [{server_name_indication, disable},
{cacertfile, "/etc/ssl/cert.pem"},
{versions, ['tlsv1.2']}]}
]}
]}
]}
]}

start method:
in my _app.erl
start(_StartType, _StartArgs) ->
mysql_pool:start(),
xxx

There's an issue with mysql insert binaries:

CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `conversation_id` binary(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ConversationId = <<Account1:64/integer, Account2:64/integer>>.
<<0,0,0,0,0,37,175,84,0,0,0,0,0,37,175,134>>.

mysql_pool:query(pool_0, 
<<"INSERT INTO test(`id`, `conversation_id`) VALUES (?, ?)">>, [1, ConversationId]).
warning  <0.1984.0> Warning 1300: Invalid utf8mb4 character string: 'AF5400',
in INSERT INTO test(`id`, `conversation_id`) VALUES (1, '\0\0\0\0\0%¯T\0\0\0\0\0%¯')

So mysql_utils:quote escapes binary and mysql throws warnings on insert.

Hello:
I get following error every day, could you please help to let me know how to solve it, Thanks!

2019-06-05 06:36:24.485 [error] <0.636.0> Connection Id 1307 closing with reason: tcp_closed
2019-06-05 06:36:24.487 [error] <0.636.0> gen_server <0.636.0> terminated with reason: tcp_closed
2019-06-05 06:36:24.487 [error] <0.636.0> CRASH REPORT Process <0.636.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-05 06:36:24.488 [error] <0.630.0> Connection Id 1304 closing with reason: tcp_closed
2019-06-05 06:36:24.488 [error] <0.630.0> gen_server <0.630.0> terminated with reason: tcp_closed
2019-06-05 06:36:24.488 [error] <0.630.0> CRASH REPORT Process <0.630.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-05 06:36:24.488 [error] <0.626.0> Connection Id 1303 closing with reason: tcp_closed
2019-06-05 06:36:24.490 [error] <0.626.0> gen_server <0.626.0> terminated with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.626.0> CRASH REPORT Process <0.626.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-05 06:36:24.491 [error] <0.638.0> Connection Id 1308 closing with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.638.0> gen_server <0.638.0> terminated with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.638.0> CRASH REPORT Process <0.638.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-05 06:36:24.491 [error] <0.632.0> Connection Id 1305 closing with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.632.0> gen_server <0.632.0> terminated with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.632.0> CRASH REPORT Process <0.632.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-05 06:36:24.491 [error] <0.634.0> Connection Id 1306 closing with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.634.0> gen_server <0.634.0> terminated with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.634.0> CRASH REPORT Process <0.634.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-05 06:36:24.491 [error] <0.642.0> Connection Id 1310 closing with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.642.0> gen_server <0.642.0> terminated with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.642.0> CRASH REPORT Process <0.642.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-05 06:36:24.491 [error] <0.644.0> Connection Id 1311 closing with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.644.0> gen_server <0.644.0> terminated with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.644.0> CRASH REPORT Process <0.644.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-05 06:36:24.491 [error] <0.640.0> Connection Id 1309 closing with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.640.0> gen_server <0.640.0> terminated with reason: tcp_closed
2019-06-05 06:36:24.491 [error] <0.640.0> CRASH REPORT Process <0.640.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-05 06:36:24.491 [error] <0.646.0>@mysql_protocol:ping:120 gen_server <0.646.0> terminated with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-05 06:36:24.491 [error] <0.646.0>@mysql_protocol:ping:120 CRASH REPORT Process <0.646.0> with 0 neighbours crashed with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120

CREATE TABLE `groups` (
  `owner_id` bigint(20) NOT NULL,
  `contact_id` bigint(20) NOT NULL,
  `group_name` varchar(255) NOT NULL,
  PRIMARY KEY (`owner_id`,`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Let's start with simple mysql:

{ok, Pid} = mysql:start_link([
    {host, "localhost"}, {user, "root"}, {password, "PWD"}, {database, "chat"}
]).
mysql:query(Pid, <<"INSERT INTO groups(`owner_id`, `contact_id`, `group_name`) 
VALUES (14, 15, ?)">>, [unicode:characters_to_binary("привет")]). 
ok

SELECT group_name from groups where owner_id=14 and contact_id=15;
+--------------+
| group_name   |
+--------------+
| привет       |
+--------------+

Now with mysql_pool (1.9.0):

ok = mysql_pool:start(). 
mysql_pool:query(pool_one, <<"INSERT INTO groups(`owner_id`, `contact_id`, `group_name`) 
VALUES (15, 16, ?)">>, [unicode:characters_to_binary("привет")]).
ok

SELECT group_name from groups where owner_id=15 and contact_id=16;
+--------------------------+
| group_name               |
+--------------------------+
| пÑÐ¸Ð²ÐµÑ             |
+--------------------------+
1 row in set (0.001 sec)

Now let's rollback to mysql_pool 1.8.0:

ok = mysql_pool:start().
mysql_pool:query(pool_one, <<"INSERT INTO groups(`owner_id`, `contact_id`, `group_name`) 
VALUES (17, 18, ?)">>, [unicode:characters_to_binary("привет")]).
ok
MySQL [wg_chat]> select group_name from groups where owner_id=17 and contact_id=18;
+--------------+
| group_name   |
+--------------+
| привет       |
+--------------+

The hex.pm is very helpful to erlang ecosystem, and I think mysql_pool will be more popular if it is uploaded in hex.pm.

Pooler has not been updated for a long time and has lost compatibility with the current version of the Erlang.
epgsql/pooler#76
What do you think about this?
Is it supposed to support the latest versions of the Erlang?

mysql_pool:query(pid, <<"INSERT INTO table (column1, column2, column3), (?,?,?)">>, [<<"UNHEX("8588d8d02ade11e88708acbc328f652b")">>,null,1]

Fails with
"Data too long for column 'id' at row 1. See AnsiCode 22001"

Same command but this time the UNHEX function is not a param (but other two remain params)

mysql_pool:query(pid, <<"INSERT INTO table (column1, column2, column3), (UNHEX("8588d8d02ade11e88708acbc328f652b">,?,?)">>, [null,1]

success?!!