1182485 - Datasource should remove extra whitespace when reading in stored SQL statements

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect, P2)

Description

[Ed Morley [:emorley]]

(This bug is just facepalm that we're even having to think about this; but oh well. Grr Datasource. Grr binlogs. Grr unreasonable DB disk quotas.)

The stored procs look like:

        "set_series_signature":{
            "sql":"INSERT INTO `series_signature` (`signature`, `property`, `value`)
                   SELECT ?,?,?
                   FROM DUAL WHERE NOT EXISTS (
                        SELECT `signature`, `property`, `value`
                        FROM `series_signature`
                        WHERE `signature` = ? AND `property` = ? AND `value` = ?
                   )",
            "host_type":"master_host"
        },

It would seem that the datasource package is not stripping the extra whitespace when reading in the files, and so makes queries that contain excessive whitespace.

eg from the binlog dumps I've been doing for bug 1179223:

Count: 42120  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  at N
  #N N:N:N server id N  end_log_pos N CRC32 N   Query   thread_id=N     exec_time=N     error_code=N
  SET TIMESTAMP=N/*!*/;
  INSERT INTO `series_signature` (`signature`, `property`, `value`)                    SELECT 'S','S','S'
     FROM DUAL WHERE NOT EXISTS (                         SELECT `signature`, `property`, `value`
  FROM `series_signature`                         WHERE `signature` = 'S' AND `property` = 'S' AND `value` = 'S'
            )
  /*!*/


The SQL statements in the binlogs are not compressed from what I can tell, so I imagine that stripping this whitespace would actually reduce their size by a reasonable amount.

Comment 1

[Ed Morley [:emorley]]

Plus it would just make the queries there and everywhere else (eg when viewing New Relic's query analysis page, or in MySQLWorkbench when looking at open connections) easier to read.

Comment 2

[Ed Morley [:emorley]]

Attachment: Strip leading/trailing whitespace from datasource files

Comment 3

[Ed Morley [:emorley]]

Thanks :-)

https://github.com/jeads/datasource/commit/04778683d227a03ceb916641901e56fad8da127e

Comment 4

[Ed Morley [:emorley]]

Bumped datasource to v0.8:
https://github.com/jeads/datasource/releases/tag/v0.8

Comment 5

[Treeherder GitHub Bugbot]

Commit pushed to master at https://github.com/mozilla/treeherder

https://github.com/mozilla/treeherder/commit/50ca297bdd92e7e1e2bd4b4d030f099136ddd33c
Bug 1182485 - Update to datasource v0.8

To pick up the whitespace-stripping change, amongst others:
https://github.com/jeads/datasource/compare/v0.7...v0.8

Comment 6

[Ed Morley [:emorley]]

This doesn't appear to be working, eg the SQL listed on:
https://rpm.newrelic.com/accounts/677903/applications/4180461/datastores?tw%5Bend%5D=1447171579&tw%5Bstart%5D=1447169779#/overview/All/trace/?id=3fca17-8e43377a-87c4-11e5-900f-b82a72d22a14&metric=Datastore%2Fstatement%2FMySQL%2Fjob%2Fselect

Comment 7

[Ed Morley [:emorley]]

Attachment: Do the same as the previous PR but on the non-test-only codepath

The tests pass locally, but Travis is still not enabled properly on the datasource repo (Jeads needs to flick the switch, I can't since not owner).

Comment 8

[Treeherder GitHub Bugbot]

Commit pushed to master at https://github.com/mozilla/treeherder

https://github.com/mozilla/treeherder/commit/169a3d8b854bb3ce5b9914f35aa2011d2d1a1763
Bug 1182485 - Update datasource to v0.9.1 for the whitespace fix

Gives us the fix in:
https://github.com/jeads/datasource/pull/36

https://github.com/jeads/datasource/compare/v0.9...v0.9.1

Comment 9

[Ed Morley [:emorley]]

Working well on stage,

Before:

SELECT signature,                           state,                           avg_sec,                           median_sec,                           min_sec,                           max_sec,                           std,                           sample_count,                           MAX(submit_timestamp)                     FROM job_eta                     WHERE signature IN (%s)                     GROUP BY signature, state                     ORDER BY submit_timestamp DESC

After:

SELECT signature, state, avg_sec, median_sec, min_sec, max_sec, std, sample_count, MAX(submit_timestamp) FROM job_eta WHERE signature IN (%s) GROUP BY signature, state ORDER BY submit_timestamp DESC