A customer showed that a particular client reported a less-than-helpful error message when it tried to display some meta-data about a table.
I couldn’t repeat the behavior with just a copy of the schema, so I suspected it was because of the size of data in the customer’s server – somebody had used an int where they needed a long.
The customer’s data was quite large – many hundreds of GB – more than I could easily whip up on my laptop to test. But, I didn’t really need all that data, or even any data at all; I just needed MySQL to pretend it had all that data. Specifically, I needed information_schema to report a large data_length.
Enter Rewriter, the query rewrite plugin that ships with MySQL 5.7 and later.
First, the general query log gave the exact query sent by the client:
select * FROM information_schema.partitions WHERE TABLE_SCHEMA = 'schemaNameHere' AND TABLE_NAME = 'tableNameHere'
Create a copy of that table:
CREATE DATABASE debug_schema;
CREATE TABLE debug_schema.partitions LIKE information_schema.partitions;
INSERT INTO debug_schema.partitions SELECT * FROM information_schema.partitions;
Adjust the copy to report a different value:
UPDATE debug_schema.partitions SET data_length = POW(2, 32);
Install the Rewriter plugin, and add a rule to modify the schema name:
INSERT INTO query_rewrite.rewrite_rules(pattern, replacement) VALUES (
'select * FROM information_schema.partitions WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?',
'select * FROM debug_schema.partitions WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?'
);
CALL query_rewrite.flush_rewrite_rules();
Now when I try the client, it thinks information_schema has reported a huge data_length, and I get the same error which my customer had reported. Success!
Always nice to see people use the Rewriter plugin for things that I never thought of!
Comment by Martin — September 26, 2016 @ 9:28 am