ProxySQL works in CLI, but not with Java client

[olafz]

We have a straight forward setup with two backend servers, each containing 1 schema:

presdb_TE1002 (on server 10.120.10.31) and trojka_d (on server 10.120.8.2). There exists one user that can read from both. On both backend servers, lower_case_table_names = 1 is set.

The version of ProxySQL is 1.3.1 and the setup is as follows:

mysql> select * from mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 19           | 10.120.10.31 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 41           | 10.120.8.2   | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

mysql> select * from mysql_query_rules;
+---------+--------+-------------+---------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+
| rule_id | active | username    | schemaname    | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | mirror_flagOUT | mirror_hostgroup | error_msg | log | apply | comment |
+---------+--------+-------------+---------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+
| 1       | 1      | trojka_ro_d | presdb_te1002 | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | NULL    | NULL            | 19                    | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 2       | 1      | trojka_ro_d | presdb_TE1002 | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | NULL    | NULL            | 19                    | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 1001    | 1      | trojka_ro_d | trojka_d      | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | NULL    | NULL            | 41                    | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
+---------+--------+-------------+---------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+
3 rows in set (0.00 sec)

mysql> select * from mysql_users;
+-------------+----------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username    | password                         | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-------------+----------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| trojka_ro_d | xxxx                             | 1      | 0       | 41                | trojka_d       | 0             | 0                      | 0            | 1       | 1        | 10000           |
+-------------+----------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

The setup works, as connecting to ProxySQL using the cli shows:

$ mysql -u trojka_ro_d -p -P 6033 -h 127.0.0.1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.6.34 (ProxySQL)

mysql> show tables;
+-----------------------+
| Tables_in_trojka_d    |
+-----------------------+
| databasechangelog     |
| databasechangeloglock |
| documents             |
+-----------------------+
3 rows in set (0.01 sec)

mysql> use trojka_d
Database changed

mysql> show tables;
+-----------------------+
| Tables_in_trojka_d    |
+-----------------------+
| databasechangelog     |
| databasechangeloglock |
| documents             |
+-----------------------+
3 rows in set (0.00 sec)

mysql> use presdb_TE1002
Database changed

mysql> show tables;
+-----------------------------+
| Tables_in_presdb_te1002     |
+-----------------------------+
| fd_finance_logs             |
| fd_invoice_row_finance_logs |
+-----------------------------+
2 rows in set (0.00 sec)

Now, when trying to do the same with a Java client, we get a SQL ERROR CODE: 1044, message: Access denied for user 'trojka_ro_d'@'10.120.%' to database 'presdb_te1002' on the last step.

We've written a simple java client that connects, changes schema and show the tables.

java -jar ProxySQL-1.0-SNAPSHOT-all.jar "jdbc:mysql://127.0.0.1:6033/?rewriteBatchedStatements=true&useServerPrepStmts=true&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8" "trojka_ro_d” “xxxx" "trojka_d"
databasechangelog
databasechangeloglock
documents

java -jar ProxySQL-1.0-SNAPSHOT-all.jar "jdbc:mysql://127.0.0.1:6033/?rewriteBatchedStatements=true&useServerPrepStmts=true&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8" "trojka_ro_d” "xxxx" "presdb_TE1002"
SQL ERROR CODE: 1044, message: Access denied for user 'trojka_ro_d'@'10.120.%' to database 'presdb_te1002'

We've created TCPdump(s) to show what happens:
First (works)

Second (fails)

If you're interested in the raw pcap file, let us know.

[renecannao]

Hi Olaf,
If you don't mind, please share the raw pcap file.
In the output above I see only the traffic between the client and the proxy, right? Could you also log the traffic between the proxy and the backends?
Thanks

[olafz]

@renecannao I created some more pcap files. Can I e-mail you the dumps?

[renecannao]

Absolutely yes!
Thank you.

[olafz]

You've got mail ;)

[renecannao]

@olafz : thank you for the pcap files.
To my surprise, Java connector doesn't use COM_INIT_DB to change schema, but sends a COM_QUERY with USE . Here a couple of examples from the files you sent me:

08:09:22.311065 IP 10.120.12.87.48384 > 10.120.12.63.6033: Flags [P.], seq 1073:1092, ack 1072, win 243, options [nop,nop,TS val 2820519259 ecr 2820701308], length 19
        0x0000:  4500 0047 2e37 4000 4006 def4 0a78 0c57  E..G.7@[email protected]
        0x0010:  0a78 0c3f bd00 1791 1eb3 9f58 88a1 0d45  .x.?.......X...E
        0x0020:  8018 00f3 08f2 0000 0101 080a a81d b55b  ...............[
        0x0030:  a820 7c7c 0f00 0000 0355 5345 2060 7472  ..||.....USE.`tr
        0x0040:  6f6a 6b61 5f64 60                        ojka_d`

08:09:48.397210 IP 10.120.12.87.49052 > 10.120.12.63.6033: Flags [P.], seq 1073:1097, ack 1072, win 243, options [nop,nop,TS val 2820525781 ecr 2820707828], length 24
        0x0000:  4500 004c a084 4000 4006 6ca2 0a78 0c57  E..L..@[email protected]
        0x0010:  0a78 0c3f bf9c 1791 7eaa 7320 b007 34d7  .x.?....~.s...4.
        0x0020:  8018 00f3 18ec 0000 0101 080a a81d ced5  ................
        0x0030:  a820 95f4 1400 0000 0355 5345 2060 7072  .........USE.`pr
        0x0040:  6573 6462 5f54 4531 3030 3260            esdb_TE1002`

08:56:00.644450 IP 10.120.12.87.43791 > 10.120.8.2.3306: Flags [P.], seq 1147:1166, ack 1059, win 243, options [nop,nop,TS val 2821218843 ecr 3167364672], length 19
        0x0000:  4508 0047 d6bd 4000 4006 3aa3 0a78 0c57  E..G..@.@.:..x.W
        0x0010:  0a78 0802 ab0f 0cea a487 b92e fef6 9fb9  .x..............
        0x0020:  8018 00f3 2982 0000 0101 080a a828 621b  ....)........(b.
        0x0030:  bcca 2640 0f00 0000 0355 5345 2060 7472  ..&@.....USE.`tr
        0x0040:  6f6a 6b61 5f64 60                        ojka_d`

It was already reported in #718 that Perl does that, but I wasn't expecting also Java using the same approach.
#718 is already fixed in version 1.4 .
Would you prefer to try 1.4 compiling it from source (is not released yet, probably in 2-3 weeks), or would you prefer this fix to go in 1.3.2 (ETA: end of this week) ?

Thanks

[olafz]

@renecannao Thanks for figuring this out!

Since it's no major change but a bugfix, I'd say to put the fix in 1.3.2. The sooner, the better ;)

[renecannao]

Ack! :)
Thanks!

[renecannao]

#718 applied to v1.3.2-dev

[jelmerterwal]

Any update on the v1.3.2 release?

[renecannao]

Version v1.3.2 is now released.
Thank you for your patience

[olafz]

Hi @renecannao, thanks for the release!

I immediately tried it, but unfortunately this issue is not solved. In fact, it's even worse. I created a new issue for that: #857