On testwiki_p, the query:
SELECT user_id, actor_id FROM user JOIN actor ON actor_user = user_id WHERE user_editcount > 0;
errors out with ERROR 1242 (21000): Subquery returns more than 1 row.
Variation using actor_user instead of actor similarly fails.
Appears to be caused by sanitization subquery AND ( select 1 from block join block_target on bt_id=bl_target where bl_deleted=1 AND bt_user=actor_user ) IS NULL.
| Subject | Author | Repo | Branch | Lines +/- | |
|---|---|---|---|---|---|
| maintain-views: fix filtering for actor view | SD0001 | operations/puppet | production | +2 -2 |
Simplified query with the same issue: SELECT actor_id from actor.
Reproducible on production when some of the sanitization logic is added:
mysql:research@dbstore1007.eqiad.wmnet [testwiki]> SELECT actor_id, actor_user, actor_name
-> FROM actor
-> WHERE exists( select 1 from user where user_id = actor_user )
-> AND ( select 1 from block join block_target on bt_id=bl_target where bl_deleted=1 AND bt_user=actor_user ) IS NULL;
ERROR 1242 (21000): Subquery returns more than 1 rowChanging AND (...) IS NULL to NOT EXISTS(...) fixes it.
Change #1187896 had a related patch set uploaded (by SD0001; author: SD0001):
[operations/puppet@production] maintain-views: fix filtering for actor view
Change #1187896 merged by FNegri:
[operations/puppet@production] maintain-views: fix filtering for actor view
I'm applying the change in https://gerrit.wikimedia.org/r/1187896 to all clouddb* hosts with:
sudo maintain-views --replace --all-databases --table actor
Does anything else need to be done? The actor view is still broken.
sd@tools-bastion-15:~$ sql testwiki MariaDB [testwiki_p]> select * from actor; ERROR 1242 (21000): Subquery returns more than 1 row
@SD0001 apologies, I think I did something wrong yesterday and the change was not applied to clouddb1017. I re-applied it now, and your query is now working fine.
I found the issue, I did run maintain-views too soon, before Puppet did synchronize the new yaml definition to all servers. I have re-run it on all clouddbs just to be sure all are in sync.
@SD0001 please confirm it's working as expected, and resolve the task if it does.