Can be only done after T217451: Remove RCFilters Guided Tours hits production, to avoid people re-seeing the tours.

Change #1027571 had a related patch set uploaded (by Urbanecm; author: Dreamy Jazz):

[mediawiki/extensions/WikimediaMessages@wmf/1.43.0-wmf.3] Backport several WikimediaMessages patches

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

Change #1027571 merged by jenkins-bot:

[mediawiki/extensions/WikimediaMessages@wmf/1.43.0-wmf.3] Backport several WikimediaMessages patches

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

Mentioned in SAL (#wikimedia-operations) [2024-05-06T12:25:42Z] <urbanecm@deploy1002> Started scap: Backport for [[gerrit:1027237|iglwiki: Enable GrowthExperiments (T364130)]], [[gerrit:1027571|Backport several WikimediaMessages patches (T217451 T362538 T364213 T315774 T364269)]]

Mentioned in SAL (#wikimedia-operations) [2024-05-06T12:45:38Z] <urbanecm@deploy1002> urbanecm: Backport for [[gerrit:1027237|iglwiki: Enable GrowthExperiments (T364130)]], [[gerrit:1027571|Backport several WikimediaMessages patches (T217451 T362538 T364213 T315774 T364269)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)

Mentioned in SAL (#wikimedia-operations) [2024-05-06T12:51:54Z] <urbanecm@deploy1002> Started scap: Backport for [[gerrit:1027237|iglwiki: Enable GrowthExperiments (T364130)]], [[gerrit:1027571|Backport several WikimediaMessages patches (T217451 T362538 T364213 T315774 T364269)]]

Mentioned in SAL (#wikimedia-operations) [2024-05-06T12:56:55Z] <urbanecm@deploy1002> urbanecm: Backport for [[gerrit:1027237|iglwiki: Enable GrowthExperiments (T364130)]], [[gerrit:1027571|Backport several WikimediaMessages patches (T217451 T362538 T364213 T315774 T364269)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)

Mentioned in SAL (#wikimedia-operations) [2024-05-06T13:15:55Z] <urbanecm@deploy1002> Finished scap: Backport for [[gerrit:1027237|iglwiki: Enable GrowthExperiments (T364130)]], [[gerrit:1027571|Backport several WikimediaMessages patches (T217451 T362538 T364213 T315774 T364269)]] (duration: 24m 01s)

Mentioned in SAL (#wikimedia-operations) [2024-05-06T13:35:56Z] <urbanecm> Run mwscript userOptions.php --wiki=testwiki --delete for "rcenhancedfilters-seen-tour", "wlenhancedfilters-seen-tour", "rcenhancedfilters-tried-highlight", "rcenhancedfilters-seen-highlight-button-counter" (T364269)

Okay...userOptions.php has a bug in itself:

[urbanecm@mwmaint1002 ~]$ mwscript userOptions.php --wiki=loginwiki --delete rcenhancedfilters-seen-tour
[...] # very long SQL query with a lot of user IDs
Function: UserOptionsMaintenance::deleteOptions
Error: 2006 MySQL server has gone away

#0 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/Database.php(1187): Wikimedia\Rdbms\Database->getQueryException('MySQL server ha...', 2006, 'DELETE FROM `us...', 'UserOptionsMain...')                                             
#1 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/Database.php(1161): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('MySQL server ha...', 2006, 'DELETE FROM `us...', 'UserOptionsMain...')                                       
#2 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/Database.php(652): Wikimedia\Rdbms\Database->reportQueryError('MySQL server ha...', 2006, 'DELETE FROM `us...', 'UserOptionsMain...', false)                                        
#3 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/Database.php(1735): Wikimedia\Rdbms\Database->query(Object(Wikimedia\Rdbms\Query), 'UserOptionsMain...')                                                                            
#4 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/DBConnRef.php(126): Wikimedia\Rdbms\Database->delete('user_properties', Array, 'UserOptionsMain...')                                                                                
#5 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/DBConnRef.php(583): Wikimedia\Rdbms\DBConnRef->__call('delete', Array)                                                                                                              
#6 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/querybuilder/DeleteQueryBuilder.php(229): Wikimedia\Rdbms\DBConnRef->delete('user_properties', Array, 'UserOptionsMain...')                                                                  
#7 /srv/mediawiki/php-1.43.0-wmf.3/maintenance/userOptions.php(289): Wikimedia\Rdbms\DeleteQueryBuilder->execute()
#8 /srv/mediawiki/php-1.43.0-wmf.3/maintenance/userOptions.php(80): UserOptionsMaintenance->deleteOptions()
#9 /srv/mediawiki/php-1.43.0-wmf.3/maintenance/includes/MaintenanceRunner.php(698): UserOptionsMaintenance->execute()
#10 /srv/mediawiki/php-1.43.0-wmf.3/maintenance/run.php(51): MediaWiki\Maintenance\MaintenanceRunner->run()
#11 /srv/mediawiki/multiversion/MWScript.php(158): require_once('/srv/mediawiki/...')
#12 {main}
[urbanecm@mwmaint1002 ~]$

Filled as T364311: userOptions.php fails to delete very large number of DB rows.

Mentioned in SAL (#wikimedia-operations) [2024-05-06T15:09:39Z] <urbanecm> mwmaint1002: mwscript userOptions.php --wiki=loginwiki --delete rcenhancedfilters-seen-tour # T364269

mysql:research@dbstore1007.eqiad.wmnet [loginwiki]> select count(*) from user_properties where up_property in ("rcenhancedfilters-seen-tour", "wlenhancedfilters-seen-tour", "rcenhancedfilters-tried-highlight", "rcenhancedfilters-seen-highlight-button-counter");
+----------+
| count(*) |
+----------+
| 41997588 |
+----------+
1 row in set (44.737 sec)

mysql:research@dbstore1007.eqiad.wmnet [loginwiki]> select count(*) from user_properties;
+----------+
| count(*) |
+----------+
| 59344056 |
+----------+
1 row in set (8.587 sec)

mysql:research@dbstore1007.eqiad.wmnet [loginwiki]>

this should save quite a lot of DB space.

Mentioned in SAL (#wikimedia-operations) [2024-05-06T15:16:01Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ mwscript userOptions.php --wiki=enwiki --delete rcenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T15:16:12Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s2 userOptions.php --delete rcenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T15:17:26Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s{3-8} userOptions.php --delete rcenhancedfilters-seen-tour # T364269

[urbanecm@mwmaint1002 ~]$ mwscript userOptions.php --wiki=loginwiki --delete rcenhancedfilters-seen-tour
The script is about to delete 'rcenhancedfilters-seen-tour' option for ALL USERS from user_properties table.
This action is IRREVERSIBLE.

Abort with control-c in the next five seconds....0
Done! Deleted 20935134 rows.
[urbanecm@mwmaint1002 ~]$

First property gone on loginwiki

Mentioned in SAL (#wikimedia-operations) [2024-05-06T18:18:28Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ mwscript userOptions.php --wiki=loginwiki --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T18:20:12Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s2 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T18:20:34Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s4 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T18:20:45Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s5 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T18:20:53Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s6 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:15:58Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s3 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:17:07Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s5 userOptions.php --delete rcenhancedfilters-tried-highlight # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:17:38Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s6 userOptions.php --delete rcenhancedfilters-tried-highlight # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:21:03Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s6 userOptions.php --delete rcenhancedfilters-seen-highlight-button-counter # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:23:30Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ mwscript userOptions.php --wiki=enwiki --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:25:10Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s5 userOptions.php --delete rcenhancedfilters-seen-highlight-button-counter # T364269

Tagging DBA for awareness. I'm dropping millions of rows from user_properties across all wikis. Quite a substantial number of rows is being dropped – on certain wikis (such as loginwiki), this accounts for as many as 90% of the rows. @Ladsgroup was already informed during the Hackathon.

FYI @jcrespo, in case this ends up firing some backup-related alerts.

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:46:26Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s7 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Probably worth optimizing this table once you are done. Can you let us know when all finished?

Thanks!

-----------------------------------------------------------------                                                                                                                                                                                    
metawiki                                                                                                                                                                                                                                             
-----------------------------------------------------------------                                                                                                                                                                                    
metawiki:  The script is about to delete 'rcenhancedfilters-seen-tour' option for ALL USERS from user_properties table.                                                                                                                              
metawiki:  This action is IRREVERSIBLE.                                                                                                                                                                                                              
metawiki:                                                                                                                                                                                                                                            
metawiki:  Abort with control-c in the next five seconds....0                                                                                                                                                                                        
metawiki:  Done! Deleted 20694833 rows.

Meta is going to have an impressive drop (to ~65% of its size) as well. Not as impressive as loginwiki, but still a big difference.

In T364269#9775691, @Marostegui wrote:

Probably worth optimizing this table once you are done. Can you let us know when all finished?

Absolutely, I finished dropping the rows today. Please feel free to ping me if there is anything else that's needed from me here.

For the Growth team: Moving to Blocked for the lack of a better column; this is essentially handed over to the DBAs for the moment. Can likely be moved off-sprint if so desired.

Stupid q: Did you drop it in loginwiki and metawiki or all wikis? That changes where we need to optimize the tables

In T364269#9786259, @Ladsgroup wrote:

Stupid q: Did you drop it in loginwiki and metawiki or all wikis? That changes where we need to optimize the tables

I dropped it on all wikis, but the impact was lower on most wikis (on enwiki, it made a 10% difference, for example).