ProxySQL Adding Extra 2 Bytes Length To MySQL Data

[JabirBaig9395]

Hi Team, I am Jabir Baig a MySQL Database Engineer, recently I started testing proxysql for my production.

Background: we have a database environment running on MySQL 5.7, and we want to upgrade it to MySQL 8. but we are using some keywords as table and column names (like rank, admin, empty) in our database, which is a hurdle to upgrading to MySQL 8, so we were testing proxysql to use query rules to add backticks to these column name (like rank, admin) while parsing the data by MySQL, so MySQL does not complain about keywords and consider them as normal string because of backtick.

`BEGIN /!/;

at 356

240628 6:01:46 server id 70256001 end_log_pos 696 CRC32 0x0a2cfb13 Query thread_id=465 exec_time=0 error_code=0

use information_schema/!/; SET TIMESTAMP=1719568906/!/; REPLACE INTO Databae.tablename (id, s_id, field, field_name, record, rank, rm, rmch, vn, vp) VALUES ('xxxxxxxxxxx', 'xxxxxx', 'vm.', 'xxxxxxxx', '0', '0', '0', '0', 'xxxxxxxxx', 'xxxxxxxxxxxxxxx') /!/;

at 696

240628 6:01:46 server id 70256001 end_log_pos 727 CRC32 0x15746c77 Xid = 46739166

COMMIT/!/;`

I am testing proxysql for this scenario and succeeded in adding backticks and upgrading MySQL from 5.7 to MySQL 8, but the actual issue started once we inserted data.

i am seeing an extra 2 bytes of length getting added to data after being inserted via proxysql like below

`+-------------+---------+-------------------+-------------------------------+--------------+---------------------------------------------+ | id | User_id | HEX(LEFT(data,4)) | CONV(HEX(LEFT(data,4)),16,10) | LENGTH(data) | tc | +-------------+---------+-------------------+-------------------------------+--------------+----------------------------------------------+ | 18928726128 | 881088 | 00002183 | 8579 | 8581 | 2024-02-26 09:45:22 |

` in the above example, you can see the actual data length should be 8579 but after data got inserted via proxysql it is 8581 this addition of an extra 2 bytes is confusing the application in that it is picking the wrong data and fails and retries repeatedly

when I looked deeper into the data inside the table, I saw some additions like the below we are talking about the BLOB column here so I have converted the data into a hexadecimal format for convenience Production - 0F0303000008360000083E000000AF0007150072616E6B000213000F03030000085100000141000000AF00051500726D000F03030000086700000428000000AF000F150073696E676C655 Test - 0F0303000008360000083E000000AF000715006072616E6B60000213000F03030000085100000141000000AF00051500726D000F03030000086700000428000000AF000F150073696E676C655 I could find an extra 6060 digits being added when comparing both prod and test (where I am using proxysql) data.

Can someone help me with a resolution to this

i have tested both with percona proxysql and binaries on Git Hub with the following versions proxysql2-2.6.3-1.1.el7.x86_64 proxysql-2.6.6-1.x86_64

OS - CentOS 7 MySQL Version - 5.7.43 and 8.0.33

Thanks, Jabir Baig

[JabirBaig9395]

I found the resolution to the above issue, we have a table that holds data of all the tables in the database in binary format, the rule for keyword rank which we have created in proxysql,is adding two backticks while inserting data in the table, this is adding extra 2 bytes of length to the data.

[JavierJF]

Hi @JabirBaig9395,

I'm glad you found the solution, if I read correctly you have find the resolution for the issue and it's expected behavior, since your rule is the one adding the extra bytes. If this is the case, could you please close the issue?

Thanks, Javier.