|
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
|
|