On clouddb1019, this query is running every hour, and always ends up being killed by wmf-pt-kill after 3 hours (10800 sec):
select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id
fnegri@clouddb1019:~$ sudo zgrep "select cl_from" /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.* |tail -n 10 /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.4:# 2025-08-09T14:17:08 KILL 12895571 (Query 10806 sec) select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.4:# 2025-08-09T15:17:08 KILL 12905210 (Query 10807 sec) select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.4:# 2025-08-09T16:17:09 KILL 12915009 (Query 10807 sec) select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.4:# 2025-08-09T17:17:09 KILL 12924295 (Query 10808 sec) select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.4:# 2025-08-09T18:17:10 KILL 12934082 (Query 10808 sec) select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.4:# 2025-08-09T19:17:10 KILL 12944736 (Query 10808 sec) select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.4:# 2025-08-09T20:17:10 KILL 12954814 (Query 10809 sec) select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.4:# 2025-08-09T21:17:11 KILL 12964440 (Query 10809 sec) select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.4:# 2025-08-09T22:17:11 KILL 12973862 (Query 10809 sec) select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id /var/log/wmf-pt-kill/wmf-pt-kill-s4.log.4:# 2025-08-09T23:17:01 KILL 12983150 (Query 10800 sec) select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id
The query is running with user u1115 (@dschwen) and host 10.64.151.2 (cloudlb1001.eqiad.wmnet.)
*************************** 11. row ***************************
Id: 18406341
User: u1115
Host: 10.64.151.2:45334
db: commonswiki_p
Command: Query
Time: 771
State: Creating sort index
Info: select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=14 and page_title=cl_to order by page_id
Progress: 0.000While the query does not by itself cause table locks, it can end up in table locks if it runs during an ALTER TABLE, causing replication lag and queries stuck in Waiting for table metadata lock. Example from today:
| 36 | system user | | commonswiki | Slave_SQL | 21885 | Waiting for table metadata lock | ALTER TABLE categorylinks DROP cl_to, DROP cl_collation | 0.000 | [...] | 18311052 | u1115 | 10.64.151.2:55786 | commonswiki_p | Query | 10580 | Creating sort index | select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=1 | 0.000 | | 18316193 | u1115 | 10.64.151.2:57474 | commonswiki_p | Query | 6979 | Creating sort index | select cl_from, page_id, cl_type from categorylinks,page where cl_type!="page" and page_namespace=1 | 0.000 | [...] | 18323283 | s51786 | 10.64.151.2:46044 | commonswiki_p | Query | 1767 | Waiting for table metadata lock | SELECT count(*) FROM templatelinks INNER JOIN linktarget ON tl_target_id = lt_id WHERE lt_title = 'I | 0.000 |