[Labs-l] SQL question: max_user_connections

Mon Jan 4 00:24:39 UTC 2016

I've contacted the user in a private mail.

On Mon, Jan 4, 2016 at 1:06 AM, Fæ <faebug at gmail.com> wrote:

> 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
>
> _______________________________________________
> Labs-l mailing list
> Labs-l at lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/labs-l
>

-- 
Jaime Crespo
<http://wikimedia.org>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20160104/15d29b7a/attachment.html>