Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
2.1.16
-
Ubuntu 16.04.4 LTS
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 |