When talking about the benefits and use cases of ProxySQL with clients, one feature I generally reference is the query rewrite engine. This is a great feature that is often used for sharding (I’ve written about this in the past at Horizontal Scaling in MySQL – Sharding Followup). Another use case I reference is “temporary application fixes.” While this is definitely a valid use case, I hadn’t personally come across an issue in the wild where the application fix wasn’t trivial.
Recently, a client hit a case where pt-archiver wasn’t able to archive rows from a table that had a bit column as part of a primary key. This is certainly an edge case, but we had hoped the fix was trivial. Unfortunately, the root of the issue was around how the Perl DBI library quotes and handles the bit data type by default.
When executing the query, Perl was trying to quote an empty string instead of the bit representation of 0, causing the tool to fail (note this is output from the locally reproduced dummy schema/data):
1 2 3 | 2022-10-18T14:19:02 0 0 DBD::mysql::st execute failed: Truncated incorrect DOUBLE value: '' [for Statement "DELETE FROM `myd_test`.`gerald` WHERE (`id` = ? AND `val` = ?)" with ParamValues: 0=2, 1='.'] at /usr/bin/pt-archiver line 6795. 2022-10-18T14:19:02.698906Z 3001 Query DELETE FROM `myd_test`.`gerald` WHERE (`id` = '2' AND `val` = '') |
The bug was reported, and engineering began working on a fix, but the table was still growing and needed to be purged (in a non-locking manner) quickly.
Looking at the query, we could identify that a simple CAST around the bit column had achieved the result we were looking for in this edge case (since BIT(1) is either 0 or 1). Unfortunately, the version of MySQL running didn’t have the ability to rewrite DML queries (this was introduced in 8.0.12: https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin.html). Finally, a case where the application fix wasn’t trivial, and the query rewrite engine in ProxySQL was needed to solve an immediate issue!
To quickly set up the environment, ProxySQL was installed locally on the host running pt-archiver and set up with a single backend (the target testbed server). With this setup, you simply connect pt-archiver to the localhost and allow the query rewrites to flow through ProxySQL to the backend servers. Please note – there was nothing elaborate or special done in this setup – just a single server backend in a single hostgroup.
Once ProxySQL was up and running, I ran pt-archiver, waited for it to fail, and then reviewed the query digest IDs recorded in the stats_mysql_query_digest table. Here were the main queries and the associated digest hashes for my testbed:
- Get first chunk: 0x0025B6A9E5F76E311SELECT SQL_NO_CACHE `id`,`val` FROM `test`.`gerald` FORCE INDEX(`PRIMARY`) WHERE ((val) in (select a.val from test.gerald a where id > ?)) ORDER BY `id`,`val` LIMIT ?
- Get next chunk: 0x3128ED8C7A53F4011SELECT SQL_NO_CACHE `id`,`val` FROM `test`.`gerald` FORCE INDEX(`PRIMARY`) WHERE ((val) in (select a.val from test.gerald a where id > ?)) AND ((`id` > ?) OR (`id` = ? AND `val` >= ?)) ORDER BY `id`,`val` LIMIT ?
- Delete chunk: 0x3CE028A9657E611F1DELETE FROM `test`.`gerald` WHERE (`id` = ? AND `val` = ?)
With the digest hash for each, I then added three query rewrite rules to cast the bit column to an unsigned int like this:
1 2 3 4 5 6 7 8 | INSERT INTO mysql_query_rules (rule_id, active, digest, match_pattern, replace_pattern, apply) VALUES (125, 1, "0x0025B6A9E5F76E31", "`id`,`val` FROM", "`id`,cast(`val` as unsigned) as `val` FROM", 1); INSERT INTO mysql_query_rules (rule_id, active, digest, match_pattern, replace_pattern, apply) VALUES (126, 1, "0x3128ED8C7A53F401", "`id`,`val` FROM", "`id`,cast(`val` as unsigned) as `val` FROM", 1); INSERT INTO mysql_query_rules (rule_id, active, digest, match_pattern, replace_pattern, apply) VALUES (127, 1, "0x3CE028A9657E611F", "AND `val`", "AND cast(`val` as unsigned)", 1); |
Notice the key columns here – the match_pattern and the replace_pattern. This is where we can dynamically rewrite the problematic portion of the query. For the delete chunk query form, we are rewriting the DELETE query to look like this:
1 | DELETE FROM `test`.`gerald` WHERE (`id` = ? AND cast(`val` as unsigned) = ?) |
With the new rule in place, re-running pt-archiver through the local ProxySQL cleaned up the table correctly! While this was not a long-term solution (and the pt-archiver fix has been released to handle this edge case), it shows a real-world example using ProxySQL to fix an application that can’t be updated in a timely manner.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
A few years ago a HW vendor changed its serial number scheme from all numbers to HEX based, so the nice but old monitoring tool (same vendor 🙂 was not able to add such a serial number.
As the tool was fixed to host/port we moved the MySQL to a different port, install proxysql to listen on 3306 and just redirect all traffic to the MySQL.
In a second step we changed the column datatype from number to char to be able to accept the new serial number format.
As strings need ‘ around them we generated a rewrite rules for INSERT into that table to add ‘ around the value.
Every things else done by the monitoring tool worked fine, just adding a new box (its HEX based serial number) needed this work around.