Skip to content

Instantly share code, notes, and snippets.

@gcleaves
Last active January 8, 2018 12:38
Show Gist options
  • Save gcleaves/5d033ee393393e54789af84f13abe1cd to your computer and use it in GitHub Desktop.
Save gcleaves/5d033ee393393e54789af84f13abe1cd to your computer and use it in GitHub Desktop.
Problematic Maxscale 2.1.2 regex filter
# This throws an error
[OfferRevenueSuspicion]
type=filter
module=regexfilter
options=ignorecase
match=INNER JOIN\s*\(\s*SELECT `fact_susp_conversions`.`offer_id` AS `offer_id`,\s*SUM\(`fact_susp_conversions`.`revenue`\) AS `__measure__0`\s*FROM `fact_susp_conversions`\s*LEFT JOIN `dim_conversion_suspicion` ON \(`fact_susp_conversions`.`impression_id` = `dim_conversion_suspicion`.`impression_id`\)\s*LEFT JOIN `dim_offer_suspicion` ON \(\(`fact_susp_conversions`.`offer_id` = `dim_offer_suspicion`.`offer_id`\) AND \(`dim_conversion_suspicion`.`suspicion_code` = `dim_offer_suspicion`.`suspicion_code`\)\)\s*LEFT JOIN `dim_advertiser_suspicion` ON \(\(`fact_susp_conversions`.`advertiser_id` = `dim_advertiser_suspicion`.`advertiser_id`\) AND \(`dim_conversion_suspicion`.`suspicion_code` = `dim_advertiser_suspicion`.`suspicion_code`\)\)\s*LEFT JOIN `dim_offer` ON \(`fact_susp_conversions`.`offer_id` = `dim_offer`.`natural_key`\)\s*LEFT JOIN `dim_advertiser` ON \(`fact_susp_conversions`.`advertiser_id` = `dim_advertiser`.`natural_key`\)\s*LEFT JOIN `dim_publisher` ON \(`fact_susp_conversions`.`publisher_id` = `dim_publisher`.`natural_key`\)\s*LEFT JOIN `dim_campaign` ON \(`fact_susp_conversions`.`campaign_id` = `dim_campaign`.`natural_key`\)\s*WHERE \(\(`dim_advertiser`.`last_version` = 1\) AND \(`dim_campaign`.`last_version` = 1\) AND \(`dim_publisher`.`last_version` = 1\) AND \(`dim_offer`.`last_version` = 1\) AND (.*)\)\s*GROUP BY 1
replace=INNER JOIN (SELECT `fact_susp_conversions`.`offer_id` AS `offer_id`, SUM(`fact_susp_conversions`.`revenue`) AS `__measure__0` FROM `fact_susp_conversions` WHERE $1 GROUP BY 1
# This does not. Note the leading (?s) in the match line
[OfferRevenueSuspicion]
type=filter
module=regexfilter
options=ignorecase
match=(?s)INNER JOIN\s*\(\s*SELECT `fact_susp_conversions`.`offer_id` AS `offer_id`,\s*SUM\(`fact_susp_conversions`.`revenue`\) AS `__measure__0`\s*FROM `fact_susp_conversions`\s*LEFT JOIN `dim_conversion_suspicion` ON \(`fact_susp_conversions`.`impression_id` = `dim_conversion_suspicion`.`impression_id`\)\s*LEFT JOIN `dim_offer_suspicion` ON \(\(`fact_susp_conversions`.`offer_id` = `dim_offer_suspicion`.`offer_id`\) AND \(`dim_conversion_suspicion`.`suspicion_code` = `dim_offer_suspicion`.`suspicion_code`\)\)\s*LEFT JOIN `dim_advertiser_suspicion` ON \(\(`fact_susp_conversions`.`advertiser_id` = `dim_advertiser_suspicion`.`advertiser_id`\) AND \(`dim_conversion_suspicion`.`suspicion_code` = `dim_advertiser_suspicion`.`suspicion_code`\)\)\s*LEFT JOIN `dim_offer` ON \(`fact_susp_conversions`.`offer_id` = `dim_offer`.`natural_key`\)\s*LEFT JOIN `dim_advertiser` ON \(`fact_susp_conversions`.`advertiser_id` = `dim_advertiser`.`natural_key`\)\s*LEFT JOIN `dim_publisher` ON \(`fact_susp_conversions`.`publisher_id` = `dim_publisher`.`natural_key`\)\s*LEFT JOIN `dim_campaign` ON \(`fact_susp_conversions`.`campaign_id` = `dim_campaign`.`natural_key`\)\s*WHERE \(\(`dim_advertiser`.`last_version` = 1\) AND \(`dim_campaign`.`last_version` = 1\) AND \(`dim_publisher`.`last_version` = 1\) AND \(`dim_offer`.`last_version` = 1\) AND (.*)\)\s*GROUP BY 1
replace=INNER JOIN (SELECT `fact_susp_conversions`.`offer_id` AS `offer_id`, SUM(`fact_susp_conversions`.`revenue`) AS `__measure__0` FROM `fact_susp_conversions` WHERE $1 GROUP BY 1
#However, the second filter does not match the following query in Maxscale but does on all online regex testers I've tried
SELECT `t0`.`__measure__0` AS `Calculation_5809854647870877696`,
SUM((`dim_conversion_suspicion`.`multiplier` * IFNULL(`dim_offer_suspicion`.`score`, IFNULL(`dim_advertiser_suspicion`.`score`, `dim_suspicions`.`default_points`)))) AS `TEMP(Calculation_3200089025480581121)(3886174145)(0)`,
`dim_offer`.`name_now` AS `name_now`,
`dim_offer`.`natural_key` AS `natural_key`,
COUNT(DISTINCT `fact_susp_conversions`.`impression_id`) AS `usr_Conversion count (copy)_ok`
FROM `fact_susp_conversions`
LEFT JOIN `dim_conversion_suspicion` ON (`fact_susp_conversions`.`impression_id` = `dim_conversion_suspicion`.`impression_id`)
LEFT JOIN `dim_suspicions` ON (`dim_conversion_suspicion`.`suspicion_code` = `dim_suspicions`.`code`)
LEFT JOIN `dim_offer_suspicion` ON ((`fact_susp_conversions`.`offer_id` = `dim_offer_suspicion`.`offer_id`) AND (`dim_conversion_suspicion`.`suspicion_code` = `dim_offer_suspicion`.`suspicion_code`))
LEFT JOIN `dim_advertiser_suspicion` ON ((`fact_susp_conversions`.`advertiser_id` = `dim_advertiser_suspicion`.`advertiser_id`) AND (`dim_conversion_suspicion`.`suspicion_code` = `dim_advertiser_suspicion`.`suspicion_code`))
LEFT JOIN `dim_offer` ON (`fact_susp_conversions`.`offer_id` = `dim_offer`.`natural_key`)
LEFT JOIN `dim_advertiser` ON (`fact_susp_conversions`.`advertiser_id` = `dim_advertiser`.`natural_key`)
LEFT JOIN `dim_publisher` ON (`fact_susp_conversions`.`publisher_id` = `dim_publisher`.`natural_key`)
LEFT JOIN `dim_campaign` ON (`fact_susp_conversions`.`campaign_id` = `dim_campaign`.`natural_key`)
INNER JOIN (SELECT `fact_susp_conversions`.`offer_id` AS `offer_id`,
SUM(`fact_susp_conversions`.`revenue`) AS `__measure__0`
FROM `fact_susp_conversions`
LEFT JOIN `dim_conversion_suspicion` ON (`fact_susp_conversions`.`impression_id` = `dim_conversion_suspicion`.`impression_id`)
LEFT JOIN `dim_offer_suspicion` ON ((`fact_susp_conversions`.`offer_id` = `dim_offer_suspicion`.`offer_id`) AND (`dim_conversion_suspicion`.`suspicion_code` = `dim_offer_suspicion`.`suspicion_code`))
LEFT JOIN `dim_advertiser_suspicion` ON ((`fact_susp_conversions`.`advertiser_id` = `dim_advertiser_suspicion`.`advertiser_id`) AND (`dim_conversion_suspicion`.`suspicion_code` = `dim_advertiser_suspicion`.`suspicion_code`))
LEFT JOIN `dim_offer` ON (`fact_susp_conversions`.`offer_id` = `dim_offer`.`natural_key`)
LEFT JOIN `dim_advertiser` ON (`fact_susp_conversions`.`advertiser_id` = `dim_advertiser`.`natural_key`)
LEFT JOIN `dim_publisher` ON (`fact_susp_conversions`.`publisher_id` = `dim_publisher`.`natural_key`)
LEFT JOIN `dim_campaign` ON (`fact_susp_conversions`.`campaign_id` = `dim_campaign`.`natural_key`)
WHERE ((`dim_advertiser`.`last_version` = 1) AND (`dim_campaign`.`last_version` = 1) AND (`dim_publisher`.`last_version` = 1) AND (`dim_offer`.`last_version` = 1) AND (`fact_susp_conversions`.`action_date` >= TIMESTAMP('2018-01-08 00:00:00')) AND (`fact_susp_conversions`.`action_date` < TIMESTAMP('2018-01-09 00:00:00')))
GROUP BY 1
) `t0` ON (`fact_susp_conversions`.`offer_id` = `t0`.`offer_id`)
WHERE ((`dim_advertiser`.`last_version` = 1) AND (`dim_campaign`.`last_version` = 1) AND (`dim_publisher`.`last_version` = 1) AND (`dim_offer`.`last_version` = 1) AND (`fact_susp_conversions`.`action_date` >= TIMESTAMP('2018-01-08 00:00:00')) AND (`fact_susp_conversions`.`action_date` < TIMESTAMP('2018-01-09 00:00:00')) AND (`dim_advertiser`.`account_manager_name_now_upper` = 'AM1'))
GROUP BY 1,
3,
4
MariaDB MaxScale /var/log/maxscale/maxscale.log Mon Jan 8 11:47:39 2018
----------------------------------------------------------------------------
2018-01-08 11:47:39 notice : MariaDB MaxScale 2.1.2 started
2018-01-08 11:47:39 notice : MaxScale is running in process 1
2018-01-08 11:47:39 notice : Configuration file: /etc/maxscale.cnf
2018-01-08 11:47:39 notice : Log directory: /var/log/maxscale
2018-01-08 11:47:39 notice : Data directory: /var/lib/maxscale
2018-01-08 11:47:39 notice : Module directory: /usr/lib64/maxscale
2018-01-08 11:47:39 notice : Service cache: /var/cache/maxscale
2018-01-08 11:47:39 notice : Loading /etc/maxscale.cnf.
2018-01-08 11:47:39 notice : /etc/maxscale.cnf.d does not exist, not reading.
2018-01-08 11:47:39 notice : [cli] Initialise CLI router module
2018-01-08 11:47:39 notice : Loaded module cli: V1.0.0 from /usr/lib64/maxscale/libcli.so
2018-01-08 11:47:39 notice : [readconnroute] Initialise readconnroute router module.
2018-01-08 11:47:39 notice : Loaded module readconnroute: V1.1.0 from /usr/lib64/maxscale/libreadconnroute.so
2018-01-08 11:47:39 notice : Loaded module regexfilter: V1.1.0 from /usr/lib64/maxscale/libregexfilter.so
2018-01-08 11:47:39 error : Unexpected parameter 'dim_publisher`.`natural_key`\)\s*LEFT JOIN `dim_campaign` ON \(`fact_susp_conversions`.`campaign_id`' for object 'OfferRevenueSuspicion' of type 'filter', or '`dim_campaign`.`natural_key`\)\s*WHERE \(\(`dim_advertiser`.`last_version` = 1\) AND \(`dim_campaign`.`last_version` = 1\) AND \(`dim_publisher`.`last_version` = 1\) AND \(`dim_offer`.`last_version` = 1\) AND (.*)\)\s*GROUP BY 1.*' is an invalid value for parameter 'dim_publisher`.`natural_key`\)\s*LEFT JOIN `dim_campaign` ON \(`fact_susp_conversions`.`campaign_id`'.
2018-01-08 11:47:39 error : Failed to open, read or process the MaxScale configuration file /etc/maxscale.cnf. Exiting.
2018-01-08 11:47:39 MariaDB MaxScale is shut down.
----------------------------------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment