Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-1822

multiline query not matched using readwritesplit router

    XMLWordPrintable

    Details

      Description

      Hi,
      using the readwritesplit router, I catch any incoming "CREATE TABLE" SQL query and replace the MyISAM engine with INNODB.

      I created a regexfilter filter matching the following PCRE2 regex :

      ^(CREATE TABLE.*)(ENGINE\s*=\s*(?:MyISAM|MEMORY))
      

      When a multi-line query which should match the regex is caught, log tells:

      "No match ^(CREATE TABLE.*)(ENGINE\s*=\s*(?:MyISAM|MEMORY)): [CREATE TABLE `ARTICLE` ("

      If I replace all "\r" in the multiline query, it matches (Matched ^(CREATE TABLE.)(ENGINE\s=\s*(?:MyISAM|MEMORY))).

      I found this kind of issue was resolved, but it seems to happen again : https://groups.google.com/forum/#!msg/maxscale/VkTUv83E_44/TgM1VIpPCgAJ

      The buggy multiline query :

      CREATE TABLE `ARTICLE` (
        `ID_ARTICLE`                        BIGINT(20) NOT NULL AUTO_INCREMENT,
        `TITRE`                             VARCHAR(255)        DEFAULT '',
        `CODE_RUBRIQUE`                     VARCHAR(255)        DEFAULT '',
        `CODE_RATTACHEMENT`                 VARCHAR(64)         DEFAULT '',
        `ID_VIGNETTE`                       BIGINT(20)          DEFAULT NULL,
        `ORDRE`                             INT(11)             DEFAULT NULL,
        `CHAPEAU`                           TEXT,
        `CORPS`                             MEDIUMTEXT,
        `CODE`                              VARCHAR(64)         DEFAULT '',
        `META_KEYWORDS`                     TEXT,
        `META_DESCRIPTION`                  TEXT,
        `TITRE_ENCADRE`                     VARCHAR(32)         DEFAULT '',
        `CONTENU_ENCADRE`                   MEDIUMTEXT,
        `ENCADRE_RECHERCHE`                 VARCHAR(4)          DEFAULT '',
        `ENCADRE_RECHERCHE_BIS`             VARCHAR(4)          DEFAULT '',
        `DATE_ALERTE`                       DATE                DEFAULT '1970-01-01',
        `MESSAGE_ALERTE`                    TEXT,
        `DATE_CREATION`                     DATETIME            DEFAULT '1970-01-01 00:00:00',
        `DATE_PROPOSITION`                  DATETIME            DEFAULT '1970-01-01 00:00:00',
        `DATE_VALIDATION`                   DATETIME            DEFAULT '1970-01-01 00:00:00',
        `DATE_MODIFICATION`                 DATETIME            DEFAULT '1970-01-01 00:00:00',
        `CODE_REDACTEUR`                    VARCHAR(64)         DEFAULT '',
        `CODE_VALIDATION`                   VARCHAR(64)         DEFAULT '',
        `LANGUE`                            VARCHAR(4)          DEFAULT '',
        `ETAT_OBJET`                        VARCHAR(4)          DEFAULT '',
        `NB_HITS`                           BIGINT(20)          DEFAULT NULL,
        `DIFFUSION_PUBLIC_VISE`             TEXT,
        `DIFFUSION_MODE_RESTRICTION`        CHAR(1)             DEFAULT '0',
        `DIFFUSION_PUBLIC_VISE_RESTRICTION` TEXT,
        `SOUS_TITRE`                        TEXT,
        `DATE_ARTICLE`                      DATE                DEFAULT '1970-01-01',
        `THEMATIQUE`                        VARCHAR(255)        DEFAULT '',
        PRIMARY KEY (`ID_ARTICLE`),
        KEY `IDX_DIFFUSION_PUBLIC_VISE` (`DIFFUSION_PUBLIC_VISE`(100)),
        KEY `IDX_DIFFUSION_MODE_RESTRICTION` (`DIFFUSION_MODE_RESTRICTION`),
        KEY `IDX_DIFFUSION_PUBLIC_VISE_RESTRICTION` (`DIFFUSION_PUBLIC_VISE_RESTRICTION`(100)),
        KEY `IDX_CODE` (`CODE`),
        KEY `IDX_THEMATIQUE` (`THEMATIQUE`),
        KEY `IDX_DATE_ARTICLE` (`DATE_ARTICLE`),
        KEY `IDX_CODE_RUBRIQUE` (`CODE_RUBRIQUE`),
        KEY `IDX_CODE_RATTACHEMENT` (`CODE_RATTACHEMENT`)
      )
        ENGINE = MyISAM
        CHARACTER SET utf8
        COLLATE utf8_general_ci
      

      \r stripped working query :

      CREATE TABLE `ARTICLE` (  `ID_ARTICLE`                        BIGINT(20) NOT NULL AUTO_INCREMENT,  `TITRE`                             VARCHAR(255)        DEFAULT '',  `CODE_RUBRIQUE`                     VARCHAR(255)        DEFAULT '',  `CODE_RATTACHEMENT`                 VARCHAR(64)         DEFAULT '',  `ID_VIGNETTE`                       BIGINT(20)          DEFAULT NULL,  `ORDRE`                             INT(11)             DEFAULT NULL,  `CHAPEAU`                           TEXT,  `CORPS`                             MEDIUMTEXT,  `CODE`                              VARCHAR(64)         DEFAULT '',  `META_KEYWORDS`                     TEXT,  `META_DESCRIPTION`                  TEXT,  `TITRE_ENCADRE`                     VARCHAR(32)         DEFAULT '',  `CONTENU_ENCADRE`                   MEDIUMTEXT,  `ENCADRE_RECHERCHE`                 VARCHAR(4)          DEFAULT '',  `ENCADRE_RECHERCHE_BIS`             VARCHAR(4)          DEFAULT '',  `DATE_ALERTE`                       DATE                DEFAULT '1970-01-01',  `MESSAGE_ALERTE`                    TEXT,  `DATE_CREATION`                     DATETIME            DEFAULT '1970-01-01 00:00:00',  `DATE_PROPOSITION`                  DATETIME            DEFAULT '1970-01-01 00:00:00',  `DATE_VALIDATION`                   DATETIME            DEFAULT '1970-01-01 00:00:00',  `DATE_MODIFICATION`                 DATETIME            DEFAULT '1970-01-01 00:00:00',  `CODE_REDACTEUR`                    VARCHAR(64)         DEFAULT '',  `CODE_VALIDATION`                   VARCHAR(64)         DEFAULT '',  `LANGUE`                            VARCHAR(4)          DEFAULT '',  `ETAT_OBJET`                        VARCHAR(4)          DEFAULT '',  `NB_HITS`                           BIGINT(20)          DEFAULT NULL,  `DIFFUSION_PUBLIC_VISE`             TEXT,  `DIFFUSION_MODE_RESTRICTION`        CHAR(1)             DEFAULT '0',  `DIFFUSION_PUBLIC_VISE_RESTRICTION` TEXT,  `SOUS_TITRE`                        TEXT,  `DATE_ARTICLE`                      DATE                DEFAULT '1970-01-01',  `THEMATIQUE`                        VARCHAR(255)        DEFAULT '',  PRIMARY KEY (`ID_ARTICLE`),  KEY `IDX_DIFFUSION_PUBLIC_VISE` (`DIFFUSION_PUBLIC_VISE`(100)),  KEY `IDX_DIFFUSION_MODE_RESTRICTION` (`DIFFUSION_MODE_RESTRICTION`),  KEY `IDX_DIFFUSION_PUBLIC_VISE_RESTRICTION` (`DIFFUSION_PUBLIC_VISE_RESTRICTION`(100)),  KEY `IDX_CODE` (`CODE`),  KEY `IDX_THEMATIQUE` (`THEMATIQUE`),  KEY `IDX_DATE_ARTICLE` (`DATE_ARTICLE`),  KEY `IDX_CODE_RUBRIQUE` (`CODE_RUBRIQUE`),  KEY `IDX_CODE_RATTACHEMENT` (`CODE_RATTACHEMENT`))  ENGINE = MyISAM  CHARACTER SET utf8  COLLATE utf8_general_ci
      

        Attachments

          Activity

            People

            Assignee:
            markus makela markus makela
            Reporter:
            xsquare Xavier Square
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.