I had a sneaking feeling I hadn't gotten all banned IPs, and indeed the syntax required to capture banned ranges turned out to be different. It was also necessary to exclude explicitly-allowed ranges and addresses.
Here's how it looked, for the morbidly curious. An unconstrained query that processed all masks at once ended up as a 40KB regular expression that was too long for the server to handle, hence the 'mask LIKE' clauses to make it work without overflowing the stack:
SELECT * FROM `votingapi_vote` WHERE
`vote_source` RLIKE (
SELECT CONCAT( '^(' ,
REPLACE( REPLACE( REPLACE(
GROUP_CONCAT(mask SEPARATOR '|'),
'.', '\\.'), '_', '.'), '%', '.*')
, ')$')
FROM `access` WHERE type = 'host' AND status = '0' and mask LIKE '9%')
AND NOT
`vote_source` RLIKE (
SELECT CONCAT( '^(' ,
REPLACE( REPLACE( REPLACE(
GROUP_CONCAT(mask SEPARATOR '|'),
'.', '\\.'), '_', '.'), '%', '.*')
, ')$')
FROM `access` WHERE type = 'host' AND status = '1' and mask LIKE '9%')
Repeat it for each LIKE prefix to capture (IPs starting 1-9 respectively) and you're golden, to the tune of 1995 extra votes. Most of them likely the result of bots mindlessly trying links to see if they can post an ad. Constrain to uid = 0 for visitors.
I had a sneaking feeling I hadn't gotten all banned IPs, and indeed the syntax required to capture banned ranges turned out to be different. It was also necessary to exclude explicitly-allowed ranges and addresses.
Here's how it looked, for the morbidly curious. An unconstrained query that processed all masks at once ended up as a 40KB regular expression that was too long for the server to handle, hence the 'mask LIKE' clauses to make it work without overflowing the stack:
SELECT * FROM `votingapi_vote` WHERE `vote_source` RLIKE ( SELECT CONCAT( '^(' , REPLACE( REPLACE( REPLACE( GROUP_CONCAT(mask SEPARATOR '|'), '.', '\\.'), '_', '.'), '%', '.*') , ')$') FROM `access` WHERE type = 'host' AND status = '0' and mask LIKE '9%') AND NOT `vote_source` RLIKE ( SELECT CONCAT( '^(' , REPLACE( REPLACE( REPLACE( GROUP_CONCAT(mask SEPARATOR '|'), '.', '\\.'), '_', '.'), '%', '.*') , ')$') FROM `access` WHERE type = 'host' AND status = '1' and mask LIKE '9%')Repeat it for each LIKE prefix to capture (IPs starting 1-9 respectively) and you're golden, to the tune of 1995 extra votes. Most of them likely the result of bots mindlessly trying links to see if they can post an ad. Constrain to uid = 0 for visitors.