{"id":483,"date":"2016-09-20T19:43:08","date_gmt":"2016-09-21T00:43:08","guid":{"rendered":"http:\/\/thenoyes.com\/littlenoise\/?p=483"},"modified":"2016-10-06T11:50:23","modified_gmt":"2016-10-06T16:50:23","slug":"debugging-large-data-with-rewriter","status":"publish","type":"post","link":"https:\/\/thenoyes.com\/littlenoise\/?p=483","title":{"rendered":"Debugging Large Data with Rewriter"},"content":{"rendered":"

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.<\/p>\n

\"A<\/a><\/p>\n

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.<\/p>\n

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.<\/p>\n

Enter Rewriter<\/a>, the query rewrite plugin that ships with MySQL 5.7 and later.<\/p>\n

First, the general query log gave the exact query sent by the client:<\/p>\n

select * FROM information_schema.partitions WHERE TABLE_SCHEMA = 'schemaNameHere' AND TABLE_NAME = 'tableNameHere'<\/code><\/p>\n

Create a copy of that table:<\/p>\n

CREATE DATABASE debug_schema;
\nCREATE TABLE debug_schema.partitions LIKE information_schema.partitions;
\nINSERT INTO debug_schema.partitions SELECT * FROM information_schema.partitions;<\/code><\/p>\n

Adjust the copy to report a different value:<\/p>\n

UPDATE debug_schema.partitions SET data_length = POW(2, 32);<\/code><\/p>\n

Install the Rewriter plugin<\/a>, and add a rule to modify the schema name:<\/p>\n

INSERT INTO query_rewrite.rewrite_rules(pattern, replacement) VALUES (
\n\t'select * FROM information_schema<\/em>.partitions WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?',
\n\t'select * FROM debug_schema<\/em>.partitions WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?'
\n);<\/p>\n

CALL query_rewrite.flush_rewrite_rules();<\/code><\/p>\n

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!<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[4],"tags":[],"class_list":["post-483","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-7N","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=483"}],"version-history":[{"count":6,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/483\/revisions"}],"predecessor-version":[{"id":492,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/483\/revisions\/492"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}