Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-29640

Complex queries may exceed schema limitations of table QInfo at Qserv czar

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: Qserv
    • Labels:
      None

      Description

      The Bug

      There have been cases when processing queries on the wide tables failed due to MySQL size constraints in table qservMeta.QInfo. The problems were reported as:

      Exception in call to czar method: Error from mysql: (1406) Data too long for column 'qTemplate' at row 1
       Unable to execute query: INSERT INTO QInfo (qType, czarId, user, query, qTemplate, qMerge, status,
       messageTable, resultLocation) VALUES ('SYNC', 1, 'anonymous',
       'SELECT dc2_object_run2_2i_dr6_wfd.dpdd_forced.coord_dec,dc2_object_run2_2i_dr6_wfd.dpdd_forced.coord_ra,dc2_object_run2_2i_dr6_wfd.dpdd_forced.g_base_ClassificationExtendedness_flag,dc2_object_run2_2i_dr6_wfd.dpdd_forced.g_base_ClassificationExt
      ...
      

      Apparently, this is caused by the MySQL type TEXT used in definitions of table's columns:

        `query` text NOT NULL COMMENT 'Original query text as was submitted by client.',
        `qTemplate` text NOT NULL COMMENT 'Query template, string used to build final per-chunk query.',
        `qMerge` text DEFAULT NULL COMMENT 'Merge (or aggregate) query to be executed on results table, result of this query is stored in merge table. If NULL then it is equivalent to SELECT *.',
        `resultQuery` text DEFAULT NULL COMMENT 'Query to be used by mysqlproxy to get final results.',
      

      The maximum length of a string to be stored in columns of this type is limited by 64 KB.
      Hence a goal is to replace the type for all three columns to MEDIUMTEXT in the Qserv schema initialization files, procedures, etc. This would raise the limit up to 16 MB which is supposed to cover all present and foreseeable use cases.

      Implementation

      • Upgrade schema definition file
      • Upgrade the smig definition files to facilitate schema upgrade of existing instances

      The temporary in situ solution

      The proposed fix has been tested and confirmed to solve the problem in production databases.
      The schema can be upgraded using the following sequence of the SQL statements:

      USE qservMeta;
      SHOW CREATE TABLE QInfo\G
      LOCK TABLE QInfo WRITE;
      ALTER TABLE QInfo MODIFY COLUMN `query` mediumtext NOT NULL COMMENT 'Original query text as was submitted by client.';
      ALTER TABLE QInfo MODIFY COLUMN `qTemplate` mediumtext NOT NULL COMMENT 'Query template, string used to build final per-chunk query.';
      ALTER TABLE QInfo MODIFY COLUMN `qMerge` mediumtext DEFAULT NULL COMMENT 'Merge (or aggregate) query to be executed on results table, result of this query is stored in merge table. If NULL then it is equivalent to SELECT *.';
      ALTER TABLE QInfo MODIFY COLUMN `resultQuery` mediumtext DEFAULT NULL COMMENT 'Query to be used by mysqlproxy to get final results.';
      UNLOCK TABLES;
      SHOW CREATE TABLE QInfo\G
      

      NOTE: locking the table is recommended on the live instances of Qserv to prevent Qserv czar from operating on the inconsistent state of the table while it's being migrated.

      Do NOT upgrade schema version in the database before a new version (as per this ticket) of Qserv is deployed in the production instances. Otherwise, Qserv will stop working. The number should only be upgraded before deploying the new version of Qserv using:

      UPDATE `qservMeta`.`QMetadata` SET`value`='4' WHERE `metakey`='version';
      

      The schema can also be upgraded using smig.

        Attachments

          Activity

          Show
          gapon Igor Gaponenko added a comment - PR: https://github.com/lsst/qserv/pull/626
          Hide
          salnikov Andy Salnikov added a comment -

          Looks OK, bu I think migrate script needs small update. Did it work when you tested it (or did you test it)?

           

          Show
          salnikov Andy Salnikov added a comment - Looks OK, bu I think migrate script needs small update. Did it work when you tested it (or did you test it)?  

            People

            Assignee:
            gapon Igor Gaponenko
            Reporter:
            gapon Igor Gaponenko
            Reviewers:
            Andy Salnikov
            Watchers:
            Andy Salnikov, Fabrice Jammes, Fritz Mueller, Igor Gaponenko
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: