Maintenance script to cleanup querycache
Open, Needs TriagePublic
Actions

Description

On enwiki (at least), there are many rows on querycache with qc_type = '' (10,000)

This feels like a bug

I'm not sure if it's worth creating a maintenance script (and run it in the updater)... I'm guessing if it's an issue on WMF wikis, it's an issue on other non WMF wikis too

mysql:wikiadmin@db1083 [enwiki]> select * from querycache where qc_type = '' LIMIT 10;
+---------+----------+--------------+---------------------+
| qc_type | qc_value | qc_namespace | qc_title            |
+---------+----------+--------------+---------------------+
|         |        0 |            0 | !                   |
|         |        0 |            0 | !!                  |
|         |        0 |            0 | !'O-!khung_language |
|         |        0 |            0 | !=                  |
|         |        0 |            0 | !?                  |
|         |        0 |            0 | !?_(chess)          |
|         |        0 |            0 | !Gã!ne_language     |
|         |        0 |            0 | !Gã!nge_language    |
|         |        0 |            0 | !Hu_language        |
|         |        0 |            0 | !Hukwe_language     |
+---------+----------+--------------+---------------------+
10 rows in set (0.00 sec)

Other stuff...

'qc_type NOT IN ' . implode( ', ', array_map( function($a) { return $a[1]; }, QueryPage::getPages() ) )

^ to get rid of any old querycache rows. Which, will also do qc_type = '' too. Yay. We can do the same delete on querycachetwo and querycache_info for completeness

Details

Subject	Repo	Branch	Lines +/-
Delete cached data of non-cached querypages with updateSpecialPages.php	mediawiki/core	REL1_36	+33 -3
Delete cached data of non-cached querypages with updateSpecialPages.php	mediawiki/core	master	+33 -3
Add a maintenance script to cleanup invalid querycache rows	mediawiki/core	master	+92 -0

Customize query in gerrit

Related Objects
Search...

View Standalone Graph

This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Status	Subtype	Assigned	Task
			· · ·
Open	Feature	None	T18660 Database table cleanup (tracking)
Stalled		None	T146571 Add a primary key to querycache table
Open		None	T174513 Maintenance script to cleanup querycache
			· · ·

Event Timeline

Reedy created this task.Aug 29 2017, 10:12 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 29 2017, 10:12 PM

Reedy added a project: MediaWiki-Special-pages.Aug 29 2017, 10:12 PM

Reedy added a parent task: T18660: Database table cleanup (tracking).Aug 29 2017, 10:16 PM

Reedy updated the task description. (Show Details)Aug 29 2017, 11:09 PM

Quick poke around... Seems at least the "new" wikis I looked at after 2013 don't have this problem...

So I'm guessing it's just gonna be the oooold wikis....

524 out of 912 wikis...

I'm guessing it's an "old wiki" thing for sure. Definitely worth getting rid of it!

reedy@terbium:~$ grep count qc_type.log | grep -v "\=> 0" -c
524
reedy@terbium:~$ grep count qc_type.log | grep -v "\=> 0"
aawiktionary:      [count(*)] => 266
abwiki:      [count(*)] => 117
abwiktionary:      [count(*)] => 972
afwiki:      [count(*)] => 11000
afwikibooks:      [count(*)] => 30
afwiktionary:      [count(*)] => 1936
akwiktionary:      [count(*)] => 8
alswiki:      [count(*)] => 6040
alswiktionary:      [count(*)] => 974
amwiki:      [count(*)] => 1265
amwiktionary:      [count(*)] => 856
angwiki:      [count(*)] => 9351
angwikibooks:      [count(*)] => 456
angwikiquote:      [count(*)] => 51
angwiktionary:      [count(*)] => 2720
anwiki:      [count(*)] => 3473
anwiktionary:      [count(*)] => 409
arwiki:      [count(*)] => 11000
arwikibooks:      [count(*)] => 260
arwikinews:      [count(*)] => 130
arwikiquote:      [count(*)] => 162
arwikisource:      [count(*)] => 280
arwiktionary:      [count(*)] => 3572
astwiki:      [count(*)] => 5690
astwiktionary:      [count(*)] => 324
aswiki:      [count(*)] => 88
aswiktionary:      [count(*)] => 853
avwiki:      [count(*)] => 20
avwiktionary:      [count(*)] => 26
aywiki:      [count(*)] => 319
aywiktionary:      [count(*)] => 939
azwiki:      [count(*)] => 1745
azwikibooks:      [count(*)] => 53
azwikiquote:      [count(*)] => 54
azwiktionary:      [count(*)] => 877
bawiki:      [count(*)] => 180
be_x_oldwiki:      [count(*)] => 5302
bewiktionary:      [count(*)] => 1170
bgwiki:      [count(*)] => 11000
bgwikibooks:      [count(*)] => 276
bgwikinews:      [count(*)] => 71
bgwikiquote:      [count(*)] => 8147
bgwiktionary:      [count(*)] => 11000
bhwiki:      [count(*)] => 55
bhwiktionary:      [count(*)] => 853
biwiki:      [count(*)] => 77
biwiktionary:      [count(*)] => 856
bmwiki:      [count(*)] => 390
bmwiktionary:      [count(*)] => 6
bnwiki:      [count(*)] => 331
bnwiktionary:      [count(*)] => 878
boardwiki:      [count(*)] => 63
bowiki:      [count(*)] => 396
bowiktionary:      [count(*)] => 853
brwiki:      [count(*)] => 5487
brwiktionary:      [count(*)] => 37
bswiki:      [count(*)] => 11000
bswikiquote:      [count(*)] => 626
bswiktionary:      [count(*)] => 1779
bugwiki:      [count(*)] => 80
cawiki:      [count(*)] => 11000
cawikibooks:      [count(*)] => 534
cawikiquote:      [count(*)] => 272
cawiktionary:      [count(*)] => 4207
cebwiki:      [count(*)] => 403
cewiki:      [count(*)] => 60
chrwiki:      [count(*)] => 230
chrwiktionary:      [count(*)] => 746
chwiki:      [count(*)] => 20
chwiktionary:      [count(*)] => 6
chywiki:      [count(*)] => 20
commonswiki:      [count(*)] => 10000
cowiki:      [count(*)] => 1095
cowikibooks:      [count(*)] => 20
cowiktionary:      [count(*)] => 3656
crwiktionary:      [count(*)] => 6
csbwiki:      [count(*)] => 811
csbwiktionary:      [count(*)] => 1070
cswiki:      [count(*)] => 11000
cswikibooks:      [count(*)] => 200
cswikiquote:      [count(*)] => 377
cswiktionary:      [count(*)] => 11000
cvwiki:      [count(*)] => 2256
cvwikibooks:      [count(*)] => 20
cywiki:      [count(*)] => 10688
cywikibooks:      [count(*)] => 7
cywikisource:      [count(*)] => 70
cywiktionary:      [count(*)] => 1579
dawiki:      [count(*)] => 11000
dawikibooks:      [count(*)] => 357
dawikiquote:      [count(*)] => 30
dawikisource:      [count(*)] => 135
dawiktionary:      [count(*)] => 4218
dewiki:      [count(*)] => 10000
dewikibooks:      [count(*)] => 6955
dewikinews:      [count(*)] => 1097
dewikiquote:      [count(*)] => 4447
dewikisource:      [count(*)] => 4065
dewikiversity:      [count(*)] => 100
dewiktionary:      [count(*)] => 11000
dvwiki:      [count(*)] => 176
dvwiktionary:      [count(*)] => 9
dzwiki:      [count(*)] => 55
dzwiktionary:      [count(*)] => 853
elwiki:      [count(*)] => 11000
elwikibooks:      [count(*)] => 59
elwikiquote:      [count(*)] => 387
elwikisource:      [count(*)] => 884
elwiktionary:      [count(*)] => 4722
enwiki:      [count(*)] => 10000
enwikibooks:      [count(*)] => 9000
enwikinews:      [count(*)] => 9000
enwikiquote:      [count(*)] => 9000
enwikisource:      [count(*)] => 9000
enwiktionary:      [count(*)] => 9000
eowiki:      [count(*)] => 10000
eowikibooks:      [count(*)] => 81
eowikiquote:      [count(*)] => 135
eowiktionary:      [count(*)] => 3082
eswiki:      [count(*)] => 10000
eswikibooks:      [count(*)] => 5457
eswikinews:      [count(*)] => 7405
eswikiquote:      [count(*)] => 3355
eswikisource:      [count(*)] => 2120
eswiktionary:      [count(*)] => 10000
etwiki:      [count(*)] => 10000
etwikibooks:      [count(*)] => 636
etwiktionary:      [count(*)] => 4648
euwiki:      [count(*)] => 8728
euwiktionary:      [count(*)] => 1900
fawiki:      [count(*)] => 10000
fawikibooks:      [count(*)] => 271
fawikiquote:      [count(*)] => 121
fawikisource:      [count(*)] => 63
fawiktionary:      [count(*)] => 1020
ffwiki:      [count(*)] => 36
fiu_vrowiki:      [count(*)] => 418
fiwiki:      [count(*)] => 10000
fiwikibooks:      [count(*)] => 532
fiwikiquote:      [count(*)] => 307
fiwiktionary:      [count(*)] => 10000
fjwiki:      [count(*)] => 30
fjwiktionary:      [count(*)] => 818
foundationwiki:      [count(*)] => 3054
fowiki:      [count(*)] => 3215
fowiktionary:      [count(*)] => 880
frwiki:      [count(*)] => 9000
frwikibooks:      [count(*)] => 3266
frwikinews:      [count(*)] => 3410
frwikisource:      [count(*)] => 9000
frwiktionary:      [count(*)] => 10000
furwiki:      [count(*)] => 378
fywiki:      [count(*)] => 7244
fywiktionary:      [count(*)] => 917
gawiki:      [count(*)] => 6681
gawikiquote:      [count(*)] => 27
gawiktionary:      [count(*)] => 1002
gdwiki:      [count(*)] => 3312
gdwiktionary:      [count(*)] => 822
glwiki:      [count(*)] => 10000
glwikibooks:      [count(*)] => 90
glwikiquote:      [count(*)] => 152
glwikisource:      [count(*)] => 115
glwiktionary:      [count(*)] => 5161
gnwiki:      [count(*)] => 110
gnwiktionary:      [count(*)] => 850
gotwiki:      [count(*)] => 121
grantswiki:      [count(*)] => 27
guwiki:      [count(*)] => 1350
guwiktionary:      [count(*)] => 10000
gvwiki:      [count(*)] => 57
gvwiktionary:      [count(*)] => 778
hawiki:      [count(*)] => 50
hawiktionary:      [count(*)] => 778
hawwiki:      [count(*)] => 81
hewiki:      [count(*)] => 10000
hewikibooks:      [count(*)] => 1980
hewikinews:      [count(*)] => 296
hewikiquote:      [count(*)] => 3726
hewikisource:      [count(*)] => 2712
hewiktionary:      [count(*)] => 4214
hiwiki:      [count(*)] => 3546
hiwiktionary:      [count(*)] => 10000
hrwiki:      [count(*)] => 10000
hrwikisource:      [count(*)] => 639
hrwiktionary:      [count(*)] => 1150
htwiki:      [count(*)] => 183
huwiki:      [count(*)] => 10000
huwikibooks:      [count(*)] => 1423
huwikiquote:      [count(*)] => 480
huwiktionary:      [count(*)] => 10000
hywiki:      [count(*)] => 328
hywikibooks:      [count(*)] => 49
hywiktionary:      [count(*)] => 1088
iawiki:      [count(*)] => 4745
iawiktionary:      [count(*)] => 4498
idwiki:      [count(*)] => 10000
idwikibooks:      [count(*)] => 164
idwikiquote:      [count(*)] => 186
idwikisource:      [count(*)] => 87
idwiktionary:      [count(*)] => 1998
iewiki:      [count(*)] => 157
iewikibooks:      [count(*)] => 27
iewiktionary:      [count(*)] => 1038
iiwiki:      [count(*)] => 18
ikwiki:      [count(*)] => 60
ikwiktionary:      [count(*)] => 774
ilowiki:      [count(*)] => 422
internalwiki:      [count(*)] => 211
iowiki:      [count(*)] => 2813
iowiktionary:      [count(*)] => 4707
iswiki:      [count(*)] => 10000
iswikibooks:      [count(*)] => 54
iswikisource:      [count(*)] => 124
iswiktionary:      [count(*)] => 1036
itwiki:      [count(*)] => 10000
itwikibooks:      [count(*)] => 1036
itwikinews:      [count(*)] => 797
itwikiquote:      [count(*)] => 6292
itwikisource:      [count(*)] => 816
itwiktionary:      [count(*)] => 10000
iuwiki:      [count(*)] => 220
iuwiktionary:      [count(*)] => 782
jawiki:      [count(*)] => 14000
jawikibooks:      [count(*)] => 1015
jawikinews:      [count(*)] => 1332
jawikiquote:      [count(*)] => 1253
jawikisource:      [count(*)] => 2383
jawiktionary:      [count(*)] => 10000
jbowiki:      [count(*)] => 1175
jvwiki:      [count(*)] => 688
jvwiktionary:      [count(*)] => 826
kawiki:      [count(*)] => 2861
kawiktionary:      [count(*)] => 9990
kiwiki:      [count(*)] => 27
kkwiki:      [count(*)] => 278
kkwiktionary:      [count(*)] => 2412
klwiki:      [count(*)] => 70
klwiktionary:      [count(*)] => 778
kmwiki:      [count(*)] => 240
kmwiktionary:      [count(*)] => 894
knwiki:      [count(*)] => 3256
knwiktionary:      [count(*)] => 9974
kowiki:      [count(*)] => 14000
kowikibooks:      [count(*)] => 410
kowikiquote:      [count(*)] => 72
kowikisource:      [count(*)] => 483
kowiktionary:      [count(*)] => 3275
kswiki:      [count(*)] => 500
kswiktionary:      [count(*)] => 826
kuwiki:      [count(*)] => 10000
kuwikibooks:      [count(*)] => 90
kuwikiquote:      [count(*)] => 410
kuwiktionary:      [count(*)] => 10000
kvwiki:      [count(*)] => 36
kwwiki:      [count(*)] => 4385
kwwiktionary:      [count(*)] => 15
kywiki:      [count(*)] => 60
kywiktionary:      [count(*)] => 790
ladwiki:      [count(*)] => 287
lawiki:      [count(*)] => 10000
lawikibooks:      [count(*)] => 45
lawikiquote:      [count(*)] => 301
lawikisource:      [count(*)] => 661
lawiktionary:      [count(*)] => 5278
lbwiki:      [count(*)] => 9000
lbwiktionary:      [count(*)] => 108
liwiki:      [count(*)] => 7849
liwiktionary:      [count(*)] => 12
lmowiki:      [count(*)] => 399
lnwiki:      [count(*)] => 765
lnwiktionary:      [count(*)] => 794
lowiki:      [count(*)] => 96
lowiktionary:      [count(*)] => 778
ltwiki:      [count(*)] => 10000
ltwikibooks:      [count(*)] => 108
ltwikiquote:      [count(*)] => 100
ltwiktionary:      [count(*)] => 1388
lvwiki:      [count(*)] => 10000
lvwiktionary:      [count(*)] => 922
mediawikiwiki:      [count(*)] => 129
metawiki:      [count(*)] => 10000
mgwiki:      [count(*)] => 250
mgwiktionary:      [count(*)] => 980
miwiki:      [count(*)] => 2244
miwiktionary:      [count(*)] => 872
mkwiki:      [count(*)] => 3800
mkwiktionary:      [count(*)] => 854
mlwiki:      [count(*)] => 1766
mlwikibooks:      [count(*)] => 18
mlwiktionary:      [count(*)] => 860
mnwiki:      [count(*)] => 689
mnwiktionary:      [count(*)] => 790
mowiki:      [count(*)] => 1028
mowiktionary:      [count(*)] => 782
mrwiki:      [count(*)] => 5156
mrwiktionary:      [count(*)] => 786
mswiki:      [count(*)] => 14000
mswiktionary:      [count(*)] => 1228
mtwiki:      [count(*)] => 726
mtwiktionary:      [count(*)] => 42
mywiki:      [count(*)] => 140
mywiktionary:      [count(*)] => 778
nahwiki:      [count(*)] => 2319
nahwiktionary:      [count(*)] => 866
napwiki:      [count(*)] => 1406
nawiki:      [count(*)] => 659
nawikibooks:      [count(*)] => 14
nawiktionary:      [count(*)] => 950
ndswiki:      [count(*)] => 4630
ndswiktionary:      [count(*)] => 700
newiki:      [count(*)] => 230
newiktionary:      [count(*)] => 780
ngwiki:      [count(*)] => 18
nlwiki:      [count(*)] => 10000
nlwikibooks:      [count(*)] => 1276
nlwikimedia:      [count(*)] => 477
nlwikinews:      [count(*)] => 539
nlwikiquote:      [count(*)] => 277
nlwikisource:      [count(*)] => 1441
nlwiktionary:      [count(*)] => 10000
nnwiki:      [count(*)] => 9000
nnwikiquote:      [count(*)] => 36
nnwiktionary:      [count(*)] => 249
nostalgiawiki:      [count(*)] => 9000
nowiki:      [count(*)] => 10000
nowikibooks:      [count(*)] => 360
nowikinews:      [count(*)] => 1664
nowikiquote:      [count(*)] => 269
nowiktionary:      [count(*)] => 5135
nvwiki:      [count(*)] => 36
ocwiki:      [count(*)] => 5903
ocwikibooks:      [count(*)] => 39
ocwiktionary:      [count(*)] => 824
omwiki:      [count(*)] => 70
omwiktionary:      [count(*)] => 782
orwiki:      [count(*)] => 70
orwiktionary:      [count(*)] => 778
oswiki:      [count(*)] => 742
pamwiki:      [count(*)] => 216
pawiki:      [count(*)] => 131
pawiktionary:      [count(*)] => 778
pihwiki:      [count(*)] => 18
piwiktionary:      [count(*)] => 6
plwiki:      [count(*)] => 10000
plwikibooks:      [count(*)] => 1975
plwikimedia:      [count(*)] => 63
plwikinews:      [count(*)] => 2013
plwikiquote:      [count(*)] => 2865
plwikisource:      [count(*)] => 3168
plwiktionary:      [count(*)] => 10000
pswiki:      [count(*)] => 213
pswiktionary:      [count(*)] => 778
ptwiki:      [count(*)] => 10000
ptwikibooks:      [count(*)] => 1308
ptwikinews:      [count(*)] => 4722
ptwikiquote:      [count(*)] => 6670
ptwikisource:      [count(*)] => 507
ptwiktionary:      [count(*)] => 6481
quwiki:      [count(*)] => 1200
quwiktionary:      [count(*)] => 826
rmwiki:      [count(*)] => 347
rmwiktionary:      [count(*)] => 786
rnwiki:      [count(*)] => 40
rnwiktionary:      [count(*)] => 48
roa_rupwiki:      [count(*)] => 198
roa_rupwiktionary:      [count(*)] => 672
rowiki:      [count(*)] => 10000
rowikibooks:      [count(*)] => 162
rowikinews:      [count(*)] => 725
rowikiquote:      [count(*)] => 179
rowikisource:      [count(*)] => 1529
rowiktionary:      [count(*)] => 2043
ruwiki:      [count(*)] => 10000
ruwikibooks:      [count(*)] => 624
ruwikinews:      [count(*)] => 533
ruwikiquote:      [count(*)] => 2272
ruwikisource:      [count(*)] => 4665
ruwiktionary:      [count(*)] => 5047
rwwiki:      [count(*)] => 60
rwwiktionary:      [count(*)] => 790
sawiki:      [count(*)] => 720
sawiktionary:      [count(*)] => 870
scnwiki:      [count(*)] => 9000
scnwiktionary:      [count(*)] => 27
scowiki:      [count(*)] => 1817
scwiki:      [count(*)] => 295
scwiktionary:      [count(*)] => 2456
sdwiki:      [count(*)] => 85
sdwiktionary:      [count(*)] => 783
sewiki:      [count(*)] => 441
sgwiki:      [count(*)] => 40
sgwiktionary:      [count(*)] => 778
shwiki:      [count(*)] => 10000
shwiktionary:      [count(*)] => 782
simplewiki:      [count(*)] => 10000
simplewikibooks:      [count(*)] => 76
simplewikiquote:      [count(*)] => 783
simplewiktionary:      [count(*)] => 1862
siwiki:      [count(*)] => 150
siwiktionary:      [count(*)] => 778
skwiki:      [count(*)] => 10000
skwikibooks:      [count(*)] => 81
skwikiquote:      [count(*)] => 172
skwiktionary:      [count(*)] => 4196
slwiki:      [count(*)] => 10000
slwikibooks:      [count(*)] => 90
slwikiquote:      [count(*)] => 761
slwiktionary:      [count(*)] => 5113
smwiki:      [count(*)] => 130
smwiktionary:      [count(*)] => 802
snwiki:      [count(*)] => 50
snwiktionary:      [count(*)] => 792
sourceswiki:      [count(*)] => 9012
sowiki:      [count(*)] => 70
sowiktionary:      [count(*)] => 778
specieswiki:      [count(*)] => 9000
sqwiki:      [count(*)] => 10000
sqwikiquote:      [count(*)] => 18
sqwiktionary:      [count(*)] => 10000
srwiki:      [count(*)] => 10000
srwikibooks:      [count(*)] => 115
srwikinews:      [count(*)] => 229
srwikiquote:      [count(*)] => 106
srwikisource:      [count(*)] => 441
srwiktionary:      [count(*)] => 3374
sswiki:      [count(*)] => 40
sswiktionary:      [count(*)] => 48
stwiki:      [count(*)] => 137
stwiktionary:      [count(*)] => 52
suwiki:      [count(*)] => 4466
suwikibooks:      [count(*)] => 45
suwiktionary:      [count(*)] => 182
svwiki:      [count(*)] => 9000
svwikibooks:      [count(*)] => 900
svwikinews:      [count(*)] => 2013
svwikiquote:      [count(*)] => 276
svwikisource:      [count(*)] => 129
svwiktionary:      [count(*)] => 10000
swwiki:      [count(*)] => 946
swwiktionary:      [count(*)] => 9978
tawiki:      [count(*)] => 5586
tawikibooks:      [count(*)] => 57
tawiktionary:      [count(*)] => 979
testwiki:      [count(*)] => 295
tewiki:      [count(*)] => 2092
tewikibooks:      [count(*)] => 72
tewiktionary:      [count(*)] => 862
tgwiki:      [count(*)] => 90
tgwiktionary:      [count(*)] => 790
thwiki:      [count(*)] => 14000
thwikibooks:      [count(*)] => 828
thwikinews:      [count(*)] => 198
thwikiquote:      [count(*)] => 351
thwiktionary:      [count(*)] => 4086
tiwiki:      [count(*)] => 40
tiwiktionary:      [count(*)] => 778
tkwiki:      [count(*)] => 354
tkwiktionary:      [count(*)] => 778
tlwiki:      [count(*)] => 10000
tlwiktionary:      [count(*)] => 866
tnwiki:      [count(*)] => 70
tnwiktionary:      [count(*)] => 778
towiki:      [count(*)] => 130
towiktionary:      [count(*)] => 778
tpiwiki:      [count(*)] => 345
tpiwiktionary:      [count(*)] => 845
trwiki:      [count(*)] => 10000
trwikibooks:      [count(*)] => 66
trwikiquote:      [count(*)] => 340
trwikisource:      [count(*)] => 39
trwiktionary:      [count(*)] => 10000
tswiki:      [count(*)] => 50
tswiktionary:      [count(*)] => 778
ttwiki:      [count(*)] => 3987
ttwikibooks:      [count(*)] => 18
ttwiktionary:      [count(*)] => 824
tumwiki:      [count(*)] => 36
twwiki:      [count(*)] => 50
twwiktionary:      [count(*)] => 774
tywiki:      [count(*)] => 36
udmwiki:      [count(*)] => 116
ugwiki:      [count(*)] => 160
ugwiktionary:      [count(*)] => 778
ukwiki:      [count(*)] => 10000
ukwikibooks:      [count(*)] => 119
ukwikinews:      [count(*)] => 153
ukwikiquote:      [count(*)] => 63
ukwiktionary:      [count(*)] => 2882
urwiki:      [count(*)] => 1203
urwiktionary:      [count(*)] => 854
uzwiki:      [count(*)] => 485
uzwiktionary:      [count(*)] => 826
vecwiki:      [count(*)] => 366
viwiki:      [count(*)] => 10000
viwikibooks:      [count(*)] => 273
viwikiquote:      [count(*)] => 108
viwiktionary:      [count(*)] => 3694
vowiki:      [count(*)] => 200
vowikiquote:      [count(*)] => 18
vowiktionary:      [count(*)] => 1772
warwiki:      [count(*)] => 1374
wawiki:      [count(*)] => 10000
wawiktionary:      [count(*)] => 762
wikimania2005wiki:      [count(*)] => 859
wowiki:      [count(*)] => 70
wowiktionary:      [count(*)] => 778
xhwiki:      [count(*)] => 50
xhwiktionary:      [count(*)] => 782
yiwiki:      [count(*)] => 6317
yiwiktionary:      [count(*)] => 3889
yowiki:      [count(*)] => 65
yowiktionary:      [count(*)] => 778
zawiki:      [count(*)] => 63
zawiktionary:      [count(*)] => 6
zh_min_nanwiki:      [count(*)] => 7435
zh_min_nanwiktionary:      [count(*)] => 453
zhwiki:      [count(*)] => 10000
zhwikibooks:      [count(*)] => 1107
zhwikiquote:      [count(*)] => 1114
zhwikisource:      [count(*)] => 6446
zhwiktionary:      [count(*)] => 10000
zuwiki:      [count(*)] => 70
zuwiktionary:      [count(*)] => 782

Reedy mentioned this in T146571: Add a primary key to querycache table.Aug 30 2017, 1:00 PM

Change 376072 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/core@master] Add a maintenance script to cleanup invalid querycache rows

https://gerrit.wikimedia.org/r/376072

gerritbot added a project: Patch-For-Review.Sep 5 2017, 6:47 PM

Mentioned in SAL (#wikimedia-operations) [2017-09-05T19:05:43Z] <Reedy> deleted querycache rows where qc_type = '' on all wikis T174513

Reedy renamed this task from Cleanup querycache where qc_type = '' to Maintenance script to cleanup querycache.Sep 5 2017, 8:41 PM

Reedy updated the task description. (Show Details)

Which means, on enwiki...

> var_dump( array_map( function($a) { return $a[1]; }, QueryPage::getPages() ) );
array(42) {
  [0]=>
  string(12) "Ancientpages"
  [1]=>
  string(15) "BrokenRedirects"
  [2]=>
  string(12) "Deadendpages"
  [3]=>
  string(15) "DoubleRedirects"
  [4]=>
  string(19) "FileDuplicateSearch"
  [5]=>
  string(19) "ListDuplicatedFiles"
  [6]=>
  string(10) "LinkSearch"
  [7]=>
  string(13) "Listredirects"
  [8]=>
  string(11) "Lonelypages"
  [9]=>
  string(9) "Longpages"
  [10]=>
  string(15) "MediaStatistics"
  [11]=>
  string(10) "MIMEsearch"
  [12]=>
  string(14) "Mostcategories"
  [13]=>
  string(10) "Mostimages"
  [14]=>
  string(14) "Mostinterwikis"
  [15]=>
  string(20) "Mostlinkedcategories"
  [16]=>
  string(19) "Mostlinkedtemplates"
  [17]=>
  string(10) "Mostlinked"
  [18]=>
  string(13) "Mostrevisions"
  [19]=>
  string(15) "Fewestrevisions"
  [20]=>
  string(10) "Shortpages"
  [21]=>
  string(23) "Uncategorizedcategories"
  [22]=>
  string(18) "Uncategorizedpages"
  [23]=>
  string(19) "Uncategorizedimages"
  [24]=>
  string(22) "Uncategorizedtemplates"
  [25]=>
  string(16) "Unusedcategories"
  [26]=>
  string(12) "Unusedimages"
  [27]=>
  string(16) "Wantedcategories"
  [28]=>
  string(11) "Wantedfiles"
  [29]=>
  string(11) "Wantedpages"
  [30]=>
  string(15) "Wantedtemplates"
  [31]=>
  string(14) "Unwatchedpages"
  [32]=>
  string(15) "Unusedtemplates"
  [33]=>
  string(16) "Withoutinterwiki"
  [34]=>
  string(16) "UnconnectedPages"
  [35]=>
  string(15) "PagesWithBadges"
  [36]=>
  string(11) "EntityUsage"
  [37]=>
  string(11) "GadgetUsage"
  [38]=>
  string(23) "MostGloballyLinkedFiles"
  [39]=>
  string(19) "GloballyWantedFiles"
  [40]=>
  string(19) "DisambiguationPages"
  [41]=>
  string(23) "DisambiguationPageLinks"
}

Which then becomes...

> echo implode( ', ', array_map( function($a) { return "'{$a[1]}'"; }, QueryPage::getPages() ) )
'Ancientpages', 'BrokenRedirects', 'Deadendpages', 'DoubleRedirects', 'FileDuplicateSearch', 'ListDuplicatedFiles', 'LinkSearch', 'Listredirects', 'Lonelypages', 'Longpages', 'MediaStatistics', 'MIMEsearch', 'Mostcategories', 'Mostimages', 'Mostinterwikis', 'Mostlinkedcategories', 'Mostlinkedtemplates', 'Mostlinked', 'Mostrevisions', 'Fewestrevisions', 'Shortpages', 'Uncategorizedcategories', 'Uncategorizedpages', 'Uncategorizedimages', 'Uncategorizedtemplates', 'Unusedcategories', 'Unusedimages', 'Wantedcategories', 'Wantedfiles', 'Wantedpages', 'Wantedtemplates', 'Unwatchedpages', 'Unusedtemplates', 'Withoutinterwiki', 'UnconnectedPages', 'PagesWithBadges', 'EntityUsage', 'GadgetUsage', 'MostGloballyLinkedFiles', 'GloballyWantedFiles', 'DisambiguationPages', 'DisambiguationPageLinks'

Reedy added a parent task: T146571: Add a primary key to querycache table.Sep 5 2017, 9:11 PM

On enwiki, after already removing qc_type == '', we still have 14039 that the update above will deleted out of 116366 total rows.. Which is over 10%

Reedy mentioned this in T175084: `DisambiguationPages` not being updated in querycache.Sep 5 2017, 9:56 PM

So, only expensive query pages should be in the cache... That removes 10 more

> var_dump( array_filter( array_map( function( $a ) { if ( SpecialPageFactory::getPage( $a[1] )->isExpensive() ) { return $a[1]; } }, QueryPage::getPages() ) ) );
array(32) {
  [0]=>
  string(12) "Ancientpages"
  [1]=>
  string(15) "BrokenRedirects"
  [2]=>
  string(12) "Deadendpages"
  [3]=>
  string(15) "DoubleRedirects"
  [5]=>
  string(19) "ListDuplicatedFiles"
  [7]=>
  string(13) "Listredirects"
  [8]=>
  string(11) "Lonelypages"
  [10]=>
  string(15) "MediaStatistics"
  [12]=>
  string(14) "Mostcategories"
  [13]=>
  string(10) "Mostimages"
  [14]=>
  string(14) "Mostinterwikis"
  [16]=>
  string(19) "Mostlinkedtemplates"
  [17]=>
  string(10) "Mostlinked"
  [18]=>
  string(13) "Mostrevisions"
  [19]=>
  string(15) "Fewestrevisions"
  [21]=>
  string(23) "Uncategorizedcategories"
  [22]=>
  string(18) "Uncategorizedpages"
  [23]=>
  string(19) "Uncategorizedimages"
  [24]=>
  string(22) "Uncategorizedtemplates"
  [25]=>
  string(16) "Unusedcategories"
  [26]=>
  string(12) "Unusedimages"
  [27]=>
  string(16) "Wantedcategories"
  [28]=>
  string(11) "Wantedfiles"
  [29]=>
  string(11) "Wantedpages"
  [30]=>
  string(15) "Wantedtemplates"
  [31]=>
  string(14) "Unwatchedpages"
  [32]=>
  string(15) "Unusedtemplates"
  [33]=>
  string(16) "Withoutinterwiki"
  [37]=>
  string(11) "GadgetUsage"
  [38]=>
  string(23) "MostGloballyLinkedFiles"
  [39]=>
  string(19) "GloballyWantedFiles"
  [41]=>
  string(23) "DisambiguationPageLinks"
}

So...

> echo $db->makeList( array_filter( array_map( function( $a ) { if ( SpecialPageFactory::getPage( $a[1] )->isExpensive() ) { return $a[1]; } }, QueryPage::getPages() ) ) );
'Ancientpages','BrokenRedirects','Deadendpages','DoubleRedirects','ListDuplicatedFiles','Listredirects','Lonelypages','MediaStatistics','Mostcategories','Mostimages','Mostinterwikis','Mostlinkedtemplates','Mostlinked','Mostrevisions','Fewestrevisions','Uncategorizedcategories','Uncategorizedpages','Uncategorizedimages','Uncategorizedtemplates','Unusedcategories','Unusedimages','Wantedcategories','Wantedfiles','Wantedpages','Wantedtemplates','Unwatchedpages','Unusedtemplates','Withoutinterwiki','GadgetUsage','MostGloballyLinkedFiles','GloballyWantedFiles','DisambiguationPageLinks'

mysql:wikiadmin@db1080 [enwiki]> select count(*) from querycache where qc_type NOT IN ('Ancientpages','BrokenRedirects','Deadendpages','DoubleRedirects','ListDuplicatedFiles','Listredirects','Lonelypages','MediaStatistics','Mostcategories','Mostimages','Mostinterwikis','Mostlinkedtemplates','Mostlinked','Mostrevisions','Fewestrevisions','Uncategorizedcategories','Uncategorizedpages','Uncategorizedimages','Uncategorizedtemplates','Unusedcategories','Unusedimages','Wantedcategories','Wantedfiles','Wantedpages','Wantedtemplates','Unwatchedpages','Unusedtemplates','Withoutinterwiki','GadgetUsage','MostGloballyLinkedFiles','GloballyWantedFiles','DisambiguationPageLinks');
+----------+
| count(*) |
+----------+
|    18039 |
+----------+
1 row in set (0.01 sec)

in comparison to the version listed before...

mysql:wikiadmin@db1080 [enwiki]> select count(*) from querycache where qc_type NOT IN ('Ancientpages', 'BrokenRedirects', 'Deadendpages', 'DoubleRedirects', 'FileDuplicateSearch', 'ListDuplicatedFiles', 'LinkSearch', 'Listredirects', 'Lonelypages', 'Longpages', 'MediaStatistics', 'MIMEsearch', 'Mostcategories', 'Mostimages', 'Mostinterwikis', 'Mostlinkedcategories', 'Mostlinkedtemplates', 'Mostlinked', 'Mostrevisions', 'Fewestrevisions', 'Shortpages', 'Uncategorizedcategories', 'Uncategorizedpages', 'Uncategorizedimages', 'Uncategorizedtemplates', 'Unusedcategories', 'Unusedimages', 'Wantedcategories', 'Wantedfiles', 'Wantedpages', 'Wantedtemplates', 'Unwatchedpages', 'Unusedtemplates', 'Withoutinterwiki', 'UnconnectedPages', 'PagesWithBadges', 'EntityUsage', 'GadgetUsage', 'MostGloballyLinkedFiles', 'GloballyWantedFiles', 'DisambiguationPages', 'DisambiguationPageLinks');
+----------+
| count(*) |
+----------+
|    14039 |
+----------+
1 row in set (0.01 sec)

Which means these are the ones thrown out

> var_dump( array_diff( array_map( function($a) { return $a[1]; }, QueryPage::getPages() ), array_filter( array_map( function( $a ) { if ( SpecialPageFactory::getPage( $a[1] )->isExpensive() ) { return $a[1]; } }, QueryPage::getPages() ) ) ) );
array(10) {
  [4]=>
  string(19) "FileDuplicateSearch"
  [6]=>
  string(10) "LinkSearch"
  [9]=>
  string(9) "Longpages"
  [11]=>
  string(10) "MIMEsearch"
  [15]=>
  string(20) "Mostlinkedcategories"
  [20]=>
  string(10) "Shortpages"
  [34]=>
  string(16) "UnconnectedPages"
  [35]=>
  string(15) "PagesWithBadges"
  [36]=>
  string(11) "EntityUsage"
  [40]=>
  string(19) "DisambiguationPages"
}

Jdforrester-WMF mentioned this in T175088: Re-assess which "expensive" query pages are run on cron for Wikimedia sites.Sep 5 2017, 10:28 PM

Liuxinyu970226 subscribed.Apr 21 2018, 11:40 AM

Krinkle moved this task from Untriaged to Usage problem on the MediaWiki-libs-Rdbms board.Aug 14 2018, 3:08 AM

Reedy mentioned this in T221449: Redesign querycache* tables.Apr 22 2019, 11:44 AM

Krinkle edited projects, added Wikimedia-database-issue, MediaWiki-Maintenance-system; removed MediaWiki-libs-Rdbms.Jul 18 2019, 8:14 PM

Krinkle moved this task from Untriaged to Bad data & Corruption on the Wikimedia-database-issue board.

Krinkle moved this task from To triage to Maintenance reports on the MediaWiki-Special-pages board.

DannyS712 moved this task from Backlog to Requested scripts on the MediaWiki-Maintenance-system board.Nov 28 2019, 2:02 AM

Krinkle removed a project: MediaWiki-Maintenance-system.Apr 3 2020, 3:12 PM

RhinosF1 subscribed.Jan 11 2021, 9:54 PM

Change 670563 had a related patch set uploaded (by Umherirrender; owner: Umherirrender):
[mediawiki/core@master] Delete non-cached query page data from updateSpecialPages

https://gerrit.wikimedia.org/r/670563

In T174513#6902144, @gerritbot wrote:

Change 670563 had a related patch set uploaded (by Umherirrender; owner: Umherirrender):
[mediawiki/core@master] Delete non-cached query page data from updateSpecialPages

https://gerrit.wikimedia.org/r/670563

This is an idea how to handle this case, but it does not include the empty query type, which seems special.

Is there an entry in the querycacheinfo table? Than it is possible to see how old the data are.

The empty qc_type issue on enwiki seems to have fixed itself (or was fixed by some action itself?) in the 3.5+ years since I filed the bug

MariaDB [enwiki]> select count(*) from querycache where qc_type = '';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.002 sec)

Looking for guideance and code review on the above. Not sure what the priority is or should be, depends on the impact I suppose.

BPirkle edited projects, added Platform Team Workboards (External Code Reviews); removed Platform Engineering.Mar 30 2021, 8:18 PM

Change 670563 merged by jenkins-bot:

[mediawiki/core@master] Delete cached data of non-cached querypages with updateSpecialPages.php

https://gerrit.wikimedia.org/r/670563

Change 681739 had a related patch set uploaded (by Reedy; author: Umherirrender):

[mediawiki/core@REL1_36] Delete cached data of non-cached querypages with updateSpecialPages.php

https://gerrit.wikimedia.org/r/681739

Reedy updated the task description. (Show Details)Apr 22 2021, 1:27 AM

Reedy updated the task description. (Show Details)Apr 22 2021, 1:39 AM

ReleaseTaggerBot added a project: MW-1.37-notes (1.37.0-wmf.3; 2021-04-27).Apr 22 2021, 2:00 AM

Change 681739 merged by jenkins-bot:

[mediawiki/core@REL1_36] Delete cached data of non-cached querypages with updateSpecialPages.php

https://gerrit.wikimedia.org/r/681739

ReleaseTaggerBot added a project: MW-1.36-notes.Apr 24 2021, 4:00 AM

Please update the list, hopefully some of the entries are deleted with the last change to the script.

In T174513#7123159, @Umherirrender wrote:

Please update the list, hopefully some of the entries are deleted with the last change to the script.

See also T175088#7123217 for enwiki and wikidata (based on the timestamps in querycache_info)

Jdforrester-WMF subscribed.May 29 2021, 8:07 PM

Umherirrender unsubscribed.Jan 2 2022, 8:15 PM

Krinkle edited projects, added Wikimedia-database-issue (Bad data); removed Wikimedia-database-issue.Apr 9 2022, 6:06 PM

TheresNoTime removed a subscriber: RhinosF1.Dec 15 2022, 11:36 PM

Maintenance script to cleanup querycacheOpen, Needs TriagePublicActions

Description

Details

Related ObjectsSearch...

Event Timeline

Maintenance script to cleanup querycache
Open, Needs TriagePublic
Actions

Related Objects
Search...