Page MenuHomePhabricator
Create Task
Maniphest T364311

userOptions.php fails to delete very large number of DB rows
Closed, ResolvedPublicBUG REPORT
Actions

Description

When dropping rcenhancedfilters-seen-tour from loginwiki (~21 millions of DB rows) via userOptions.php, I encountered the following issue:

[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 ~]$

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
userOptions.php: Actually batch deletionmediawiki/coremaster+4 -0
userOptions.php: Actually batch deletionmediawiki/corewmf/1.43.0-wmf.3+4 -0
Customize query in gerrit

Related Objects
Search...

Event Timeline

Urbanecm_WMF created this task.May 6 2024, 1:42 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 6 2024, 1:42 PM
Urbanecm_WMF claimed this task.May 6 2024, 1:52 PM
Urbanecm_WMF mentioned this in T364269: Drop user properties related to RC tours.
Urbanecm_WMF added a project: Growth-Team (Sprint 13 (Growth Team)).
Urbanecm_WMF added a parent task: T364269: Drop user properties related to RC tours.
gerritbot added a comment.May 6 2024, 1:56 PM

Change #1028500 had a related patch set uploaded (by Urbanecm; author: Urbanecm):

[mediawiki/core@master] userOptions.php: Actually batch deletion

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

gerritbot added a project: Patch-For-Review.May 6 2024, 1:56 PM
Urbanecm_WMF added a comment.May 6 2024, 2:02 PM

The actual issue is that the batching implemented in userOptions.php for deletion does not work. So, we've asked the DB server to execute a command that looks like DELETE FROM user_properties WHERE up_property='...' AND up_user IN (20 millions of user IDs), which obviously cannot work.

Fortunately, the fix is fairly quick.

Urbanecm_WMF moved this task from Incoming to Code Review on the Growth-Team (Sprint 13 (Growth Team)) board.May 6 2024, 2:02 PM
gerritbot added a comment.May 6 2024, 2:38 PM

Change #1027744 had a related patch set uploaded (by Urbanecm; author: Urbanecm):

[mediawiki/core@wmf/1.43.0-wmf.3] userOptions.php: Actually batch deletion

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

Urbanecm_WMF triaged this task as High priority.May 6 2024, 2:42 PM
Urbanecm_WMF moved this task from Code Review to Test in Production on the Growth-Team (Sprint 13 (Growth Team)) board.

(will verify in prod once backport finishes)

gerritbot added a comment.May 6 2024, 3:00 PM

Change #1028500 merged by jenkins-bot:

[mediawiki/core@master] userOptions.php: Actually batch deletion

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

gerritbot added a comment.May 6 2024, 3:03 PM

Change #1027744 merged by jenkins-bot:

[mediawiki/core@wmf/1.43.0-wmf.3] userOptions.php: Actually batch deletion

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

Stashbot added a comment.May 6 2024, 3:03 PM

Mentioned in SAL (#wikimedia-operations) [2024-05-06T15:03:45Z] <urbanecm@deploy1002> Started scap: Backport for [[gerrit:1027744|userOptions.php: Actually batch deletion (T364311)]]

Urbanecm_WMF closed this task as Resolved.May 6 2024, 3:10 PM

Now it works.

Stashbot added a comment.May 6 2024, 3:20 PM

Mentioned in SAL (#wikimedia-operations) [2024-05-06T15:20:36Z] <urbanecm@deploy1002> Finished scap: Backport for [[gerrit:1027744|userOptions.php: Actually batch deletion (T364311)]] (duration: 16m 51s)

Maintenance_bot removed a project: Patch-For-Review.May 6 2024, 3:31 PM
ReleaseTaggerBot added a project: MW-1.43-notes (1.43.0-wmf.3; 2024-04-30).May 6 2024, 4:00 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits