[Labs-l] SQL question: max_user_connections

Mon Jan 4 00:06:02 UTC 2016

The reports that Faebot was generating for more than a year, fail with
the error:
_mysql_exceptions.OperationalError: (1226, "User 's51457' has exceeded
the 'max_user_connections' resource (current value: 1)")

This seems to be a change in the config for the wiki database. Is
there a setting to get around it,
or is this something that is might sort itself out?

While I'm here, the following SQL used to run in under a minute, but
now never gets to complete, leaving the very useful BLP/images report
broken for a couple of months now. See
http://quarry.wmflabs.org/query/6612 - seems to run on Quarry, but
never completes on labs.
Has the database structure been changed in some way? Tips welcome!

SELECT
        log_title AS image,
        DATE_FORMAT(log_timestamp, "%Y-%m-%dT%H:%i:%s" ) AS timestamp,
        COUNT(*) AS "numb BLPs",
        IF( abs( img_timestamp - log_timestamp ) < 8, "Current",
"Replaced" ) AS top,
        log_comment,
        oi_timestamp
FROM commonswiki_p.logging_logindex
INNER JOIN commonswiki_p.globalimagelinks ON log_title = gil_to AND
gil_wiki = 'enwiki'
LEFT OUTER JOIN commonswiki_p.image ON img_name = log_title
INNER JOIN enwiki_p.categorylinks ON gil_page = cl_from
AND enwiki_p.categorylinks.cl_to IN
('Living_people','Possibly_living_people', '2015_deaths',
'2014_deaths', '2013_deaths')
INNER JOIN commonswiki_p.user ON log_user = user_id AND user_editcount < 1000
LEFT OUTER JOIN enwiki_p.user u2 ON log_user_text = u2.user_name
LEFT OUTER JOIN commonswiki_p.oldimage ON img_name = oi_name
WHERE log_type = 'upload' AND
        log_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -8 DAY),
"%Y%m%d%H%i%s") AND
        log_action = 'overwrite' AND
        oi_user != img_user AND
        (u2.user_editcount < 2000 OR u2.user_editcount IS NULL) AND
        NOT img_minor_mime = "svg+xml"
GROUP BY log_timestamp, log_title
HAVING oi_timestamp = MIN(oi_timestamp)
ORDER BY log_timestamp DESC, oi_timestamp DESC
LIMIT 1000;

Fae

On 1 January 2016 at 23:16, Fæ <faewik at gmail.com> wrote:
> The reports that Faebot was generating for more that a year, fail
> consistently with the error:
> _mysql_exceptions.OperationalError: (1226, "User 's51457' has exceeded
> the 'max_user_connections' resource (current value: 1)")
>
> This seems to be a change in the config for the wiki database. How are
> bot/report writers supposed to get around it, or is this something
> that is about to sort itself out?
>
> Thanks,
> Fae
> --
> faewik at gmail.com https://commons.wikimedia.org/wiki/User:Fae

--
faewik at gmail.com https://commons.wikimedia.org/wiki/User:Fae
Personal and confidential, please do not circulate or re-quote.

-- 
faebug at gmail.com http://j.mp/faewm