Proxysql: Problem with Decimal Columns among ProxySQL and Hibernate

[williamsantanamoip]

Hello @renecannao could you help me please ?

I'm facing problems with decimal columns when use ProxySQL with Hibernate, appears that Hibernate for some reason is putting 142 zeros on the final of number like print bellow. This behavior only happens when we use ProxySQL.

This behavior don't happen if we access ProxySQL via mysql client on terminal.

Hibernate version: 3.3.0.ga
Spring version: 2.5.5
Tomcat version: 5.5.12

I tried found the problem myself with tcpdump but I couldn't find. If you need tcpdump I can send to your email, tcpdump was running on the App side.

Thanks a lot.

Hibernate + ProxySQL wrong behavior

Hibernate + MySQL Direct correct behavior

[renecannao]

@williamsantanamoip , I found a bug that can potentially result in this issue.
Please feel free to send me an email with tcpdump.
Thanks

[williamsantanamoip]

@renecannao Nice to hear that you found the bug, thanks so much to answer me.

I sent the tcpdump for your email.

Again, thanks so so much for great work, ProxySQL is awesome.

[williamsantanamoip]

Hello @renecannao I installed version 1.4.4-11-g447f17f and you are on the right way, bellow follow new print, now proxysql put 14 more zeros. Thanks so much for you support.

[renecannao]

Thank your for the tcpdump files.
ProxySQL is sending incorrect values for column_length and decimals.
An example of correct values:

        0x0070:  0670 6179 636f 6d09 6163 636f 756e 7430  .paycom.account0
        0x0080:  5f07 6163 636f 756e 7407 6964 3133 5f36  _.account.id13_6
        0x0090:  5f02 6964 0c3f 0014 0000 0008 2342 0000  _.id.?......#B..

Incorrect values:

        0x0070:  0670 6179 636f 6d09 6163 636f 756e 7430  .paycom.account0
        0x0080:  5f07 6163 636f 756e 7407 6964 3133 5f36  _.account.id13_6
        0x0090:  5f02 6964 0c3f 0000 0000 0008 23c2 0000  _.id.?......#...

[williamsantanamoip]

Thanks so much for sharing Rene, sorry to not realized the incorrect values.

[renecannao]

@williamsantanamoip : please retry and let me know.
Thanks

[williamsantanamoip]

@renecannao The problem persists, ProxySQL continue put more 14 zeros =/ .. I have a new tcpdump if you need I can send for you.
I compared with tcpdump that I sent for you with a new, bellow follow some differences:

`
mysql> select @@version;
+-------------------+
| @@version |
+-------------------+
| 1.4.4-13-g225e1f1 |
+-------------------+
1 row in set (0.00 sec)

mysql>
`

Now proxysql have the same result like mysql:

0x0070: 0670 6179 636f 6d09 6163 636f 756e 7430 .paycom.account0
0x0080: 5f07 6163 636f 756e 7407 6964 3133 5f36 _.account.id13_6
0x0090: 5f02 6964 0c3f 0014 0000 0008 2342 0000 _.id.?......#B..

This slice of new tcpdump is equal old tcpdump for ProxySQL:

0x0090: 574b 0005 0000 5afe 0000 0200 WK....Z.....

On MySQL:

0x0090: 574b 0005 0000 5afe 0000 0100 WK....Z.....

Thanks so much for your support.

[renecannao]

@williamsantanamoip : thank you for the tcpdump files.
The two output in tcpdump (mysqld vs proxysql) seems almost identical, they only mismatch in the statement id (that is expected) and proxysql incorrectly reporting autocommit is on, while it should be off (that is a different issue).
Other than that, the output is completely identical. I am not sure why Hibernate incorrect reports 14 zeros

[williamsantanamoip]

@renecannao Thanks so much for your support, I changed the decimals columns to bigint how test and Hibernate continues put 14 zeros, if I found something new I will keep in touch.

Again, thanks so much,

[williamsantanamoip]

Hello @renecannao sorry to bother you with the same issue. I runned a new tcpdump on Database side and found some differences, but I don't know if really a problem. This new tcpdump is of another part of the App, but shows the same problems.

I realized that when MySQL send the answer to ProxySQL they "talks" in MySQL Protocol, while MySQL "talks" in TCP when answer direct to the APP.

Let me know if you would like the new files of tcpdump (Database side and App Side)

Tcpdump DB side:

t.v23_13_.v2_integration_level.?...........J....def.paycom.account0_.account.negotiated24_13_.negotiated_tax.?..................".j.........1...............1000005.00..0.00.121
.67.0.00.0.00.0.00......37000127733............MPA-5XF0THJL91WK........".

MySQL
t.v23_13_.v2_integration_level.?...........J....def.paycom.account0_.account.negotiated24_13_.negotiated_tax.?.................. .j.........1...............1000005.00..0.00.121
.67.0.00.0.00.0.00......37000127733............MPA-5XF0THJL91WK........ .

22 00 6a 00 00 1b 00 00    a0 0c 01 e5 31 00 00 00    ".j.........1...

MySQL
20 00 6a 00 00 1b 00 00    a0 0c 01 e5 31 00 00 00     .j.........1...

ProxySQL
05 00 00 1c fe 00 00 22    00                         .......".

MySQL
05 00 00 1c fe 00 00 20    00                         ....... .

Tcpdump APP side:

t.v23_13_.v2_integration_level.?...........J....def.paycom.account0_.account.negotiated24_13_.negotiated_tax.?..................".j.........1...............1000005.00..0.00.121
.67.0.00.0.00.0.00......37000127733............MPA-5XF0THJL91WK........".

MySQL
t.v23_13_.v2_integration_level.?...........J....def.paycom.account0_.account.negotiated24_13_.negotiated_tax.?.................. .j.........1...............1000005.00..0.00.121
.67.0.00.0.00.0.00......37000127733............MPA-5XF0THJL91WK........ .

22 00 6a 00 00 1b 00 00    a0 0c 01 e5 31 00 00 00    ".j.........1...

MySQL
20 00 6a 00 00 1b 00 00    a0 0c 01 e5 31 00 00 00     .j.........1...

ProxySQL
05 00 00 1c fe 00 00 22    00                         .......".

MySQL
05 00 00 1c fe 00 00 20    00                         ....... .

Thanks so much for your time and support.

[mhumpula]

Hi @williamsantanamoip,

I've had the same problem with PHPStorm. Turns out, that only modification you have to do is to send same server flags in initial MySQL packet. Look for mysql-server_capabilities variable. For my MySQL instance (percona 5.6.37) setting following fixed the problem

update global_variables set variable_value=63487 where variable_name = "mysql-server_capabilities";
LOAD MYSQL VARIABLES TO RUNTIME;

[dungdm93]

Thank you @mhumpula
Can you explain the meaning of the number 63487

[mhumpula]

see https://dev.mysql.com/doc/dev/mysql-server/8.0.0/group__group__cs__capabilities__flags.html

[ismailkuruca]

FYI, We have faced a similar problem regarding these capabilities flags. When we used 'useServerPrepStmts=true', mysql java driver would lose the decimal part while converting to BigDecimal type. (e.g 720.11 would be parsed as 720).

After changing the capabilities the problem went away.

[renecannao]

Added flag CLIENT_LONG_FLAG to CapabilityFlags.
Certain libraries do not need that (C API for example), but others seems to rely on it.

[Dieken]

https://youtrack.jetbrains.com/issue/DBE-5704

The patch was only applied to v2.0.x and v1.4.12, not >= v1.4.13.

On MySQL wire protocol, the MySQL response contains 1 byte or 2 bytes column flags which is followed by 1 byte column decimals:

https://github.com/mysql/mysql-connector-j/blob/fe1903b1ecb4a96a917f7ed3190d80c049b1de29/src/com/mysql/jdbc/MysqlIO.java#L708

If CLIENT_LONG_FLAG is disabled but MySQL server returns two bytes "field flags" and ProxySQL passes it through, then the client will wrongly regard second byte of column flags as decimals, that's why integers show many zeros after decimal.

CREATE TABLE  t (a int primary key,  b tinyint unique,  c bigint);
INSERT INTO t VALUES (3, 2, 1);

The second bytes of column flags of a and b are not zero, the second byte of column flag of c happens to be zero, so DataGrip wrongly renders a and b but not c.

I checked git log origin/v1.4.16..origin/v1.4.XX and found some fixes in old branches were not included in latest v1.4.16, especially v1.4.12, is this intended? @renecannao

[Dieken]

https://github.com/sysown/proxysql/commit/447f17fd24f67df510ba2973f4db3e4effe62223

@renecannao Should this commit be reverted?