ToolsDB replication is broken
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	• Bstorm
	Oct 27 2020, 5:59 PM

Description

On Oct 27 14:09:54 UTC, replication stopped for a data drift error:

Could not execute Update_rows_v1 event on table s54518__mw.online; Can't find record in 'online', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log log.221984, end_log_pos 27719835

I filtered the table and restarted replication, but it simply failed on another table. After investigation, I found that the replica was set to read-write mode, which could explain the drift, though we don't advertise the existence of that server. Someone could have set their system to write to it for some reason, but this would be surprising.

Overall, this has blocked failover (T263679) until we can fix or rebuild this server.

Coordinating etherpad here: https://etherpad.wikimedia.org/p/toolsdb-2020-replica-rebuild

Details

Subject	Repo	Branch	Lines +/-
toolsdb: set the rsync script to be the whole data dir	operations/puppet	production	+2 -2
toolsdb: set up quickcopy to move the dump	operations/puppet	production	+26 -2
toolsdb: actually use the read_only parameter from the profiles	operations/puppet	production	+1 -1
toolsdb: Fix the my.cnf template to include parameter for socket	operations/puppet	production	+40 -15
toolsdb: Make socket a parameter so new servers might work right	operations/puppet	production	+2 -7

Customize query in gerrit

Related Objects

Mentioned In: T328693: [toolsdb] Migrate "s54518__mw" db to Trove
T322039: [toolsdb] clouddb1002 stopped replicating from clouddb1001
T301949: ToolsDB upgrade => Bullseye, MariaDB 10.4
T253738: ToolsDB: master crashed, replica having consistency issues
T257275: Database tables for checkwiki tool needs maintenance to prevent data loss
T257274: Database table for request tool needs maintenance to prevent data loss
T270337: "sql tools" returns "connection refused" from freebase tool
T256501: CopyPatrol has stopped
Mentioned Here: T212595: [Feature request] Database as a Service (Trove) for Cloud VPS projects
T107875: Use InnoDB engine for searchindex table in MySQL
P13255 MyISAM tables in ToolsDB
T263679: Plan and manage the required downtime for the clouddb-services VMs on cloudvirt1019 and cloudvirt1020

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Mentioned in SAL (#wikimedia-cloud) [2020-10-29T23:49:56Z] <bstorm> launching clouddb-toolsdb-03 (trying to make a better naming convention) to replace failed replica T266587

Change 637726 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] toolsdb: Make socket a parameter so new servers might work right

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

gerritbot added a project: Patch-For-Review.Oct 30 2020, 4:19 PM

Change 637726 merged by Bstorm:
[operations/puppet@production] toolsdb: Make socket a parameter so new servers might work right

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

Maintenance_bot removed a project: Patch-For-Review.Oct 30 2020, 6:11 PM

Change 637751 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] toolsdb: Fix the my.cnf template to include parameter for socket

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

gerritbot added a project: Patch-For-Review.Oct 30 2020, 6:25 PM

Change 637751 merged by Bstorm:
[operations/puppet@production] toolsdb: Fix the my.cnf template to include parameter for socket

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

Change 637763 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] toolsdb: actually use the read_only parameter from the profiles

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

Change 637763 merged by Bstorm:
[operations/puppet@production] toolsdb: actually use the read_only parameter from the profiles

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

Maintenance_bot removed a project: Patch-For-Review.Oct 30 2020, 8:10 PM

So trying to get a dump in order to rebuild replication is infuriatingly tricky because so much changes on ToolsDB.
Today, I got this far (48GB compressed dump):

root@clouddb1001:/srv/labsdb/backup1# mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A | gzip  > dump.sql.gz
mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `ch_fixed_links` at row: 0

I think I'm not going to be able to get around scheduling a read lock on the tables to take a solid backup to start replication from. With an announcement, we could possibly discourage changing table definitions for a day or so, and then my command might work. It takes so long, that I'm not sure that's worth it vs. the brute force method of locking the whole setup for a bit. I can give it another shot tomorrow.

I did not get a chance to give it another shot today. I suspect I really need to schedule a downtime, but I could always start another run just in case I get lucky one more time.

It did not work. Scheduling downtime.

@Marostegui Just wanted a second opinion based on my comments above. I believe that all means I need to take a backup with a read lock in place. Correct?

In T266587#6603944, @Bstorm wrote:

@Marostegui Just wanted a second opinion based on my comments above. I believe that all means I need to take a backup with a read lock in place. Correct?

You could also set the host with read_only set global read_only=ON if there is nothing writing as root.

Not sure if you'll be using mysqldump or mydumper. If mysqldump, make sure tu use --master-data=2 so you get the binlog coordinates to start replication from on the .sql file

Although if you are planning to set the host as read only, if you do a show master status, the position shouldn't be changing, do you can grab it anytime before setting the host writable again.

Just out of curiosity I checked for MyISAM tables:

/https/phabricator.wikimedia.org/P13255">P13255 MyISAM tables in ToolsDB

MariaDB [(none)]> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MyISAM'; ---------------------------+--------+ | TABLE_NAME | ENGINE | ---------------------------+--------+ | heartbeat | MyISAM | | column_stats | MyISAM | | columns_priv | MyISAM | | db | MyISAM | | event | MyISAM | | func | MyISAM | | help_category | MyISAM | | help_keyword | MyISAM | | help_relation | MyISAM | | help_topic | MyISAM | | host | MyISAM | | index_stats | MyISAM | | ndb_binlog_index | MyISAM | | plugin | MyISAM | | proc | MyISAM | | procs_priv | MyISAM | | proxies_priv | MyISAM | | roles_mapping | MyISAM | | servers | MyISAM | | table_stats | MyISAM | | tables_priv | MyISAM | | time_zone | MyISAM | | time_zone_leap_second | MyISAM | | time_zone_name | MyISAM | | time_zone_transition | MyISAM | | time_zone_transition_type | MyISAM | | user | MyISAM | | articles | MyISAM | | contests | MyISAM | | contribs | MyISAM | | fulltexts | MyISAM | | notifications | MyISAM | | prizes | MyISAM | | schemachanges | MyISAM | | users | MyISAM | | fulltexts | MyISAM | | trr | MyISAM | | commonscat | MyISAM | | id_dump | MyISAM | | image | MyISAM | | monuments-old_ch_(de) | MyISAM | | monuments-old_ch_(fr) | MyISAM | | monuments-old_ch_(it) | MyISAM | | monuments_by_(be-x-old) | MyISAM | | monuments_de-nrw_(de) | MyISAM | | monuments_hk-hb_(en) | MyISAM | | monuments_pl-old_(pl) | MyISAM | | monuments_ru-old_(ru) | MyISAM | | monuments_se-arbetsliv_(sv) | MyISAM | | monuments_se-fornminne_(sv) | MyISAM | | prox_search | MyISAM | | wlpa_at_(de) | MyISAM | | wlpa_es-ct_(ca) | MyISAM | | wlpa_fi_(fi) | MyISAM | | wlpa_il_(en) | MyISAM | | searchindex | MyISAM | class="o">-rtl | searchindex | MyISAM | | zhwp_dykarticle | MyISAM | | zhwp_updatequeue | MyISAM | | searchindex | MyISAM | | foosearchindex | MyISAM | | bad_cats | MyISAM | | no_wd | MyISAM | | feed | MyISAM | | item_cache | MyISAM | | searchindex | MyISAM | | suggestions | MyISAM | | wiki | MyISAM | | searchindex | MyISAM | | articles | MyISAM | | stats | MyISAM | | articles | MyISAM | | stats | MyISAM | | articles | MyISAM | | stats | MyISAM | | dab_hof | MyISAM | | ores_dump | MyISAM | | revision | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | comentors | MyISAM | | logging | MyISAM | | mentee | MyISAM | | mentee_articles | MyISAM | | mentee_mentor | MyISAM | | mentor | MyISAM | | delete---searchindex | MyISAM | | mw_searchindex | MyISAM | | ow2_searchindex | MyISAM | | ow3_searchindex | MyISAM | | tst001_searchindex | MyISAM | | xx02_searchindex | MyISAM | | xx02_translate_tmf | MyISAM | | zuzuzu_searchindex | MyISAM | | config_history | MyISAM | | pop_Jun16 | MyISAM | | links | MyISAM | | stations | MyISAM | | ipp_changes | MyISAM | | ipp_changes_old | MyISAM | | ipp_vandalen | MyISAM | | ipp_vandalen_old | MyISAM | | npp_nb_data | MyISAM | | npp_nb_words | MyISAM | | npp_new_pages | MyISAM | | npp_old_pages | MyISAM | | npp_success | MyISAM | | photocoords | MyISAM | | auxiliary | MyISAM | | catalog | MyISAM | | entry | MyISAM | | log | MyISAM | | user | MyISAM | | user_edits | MyISAM | | vw_common_names | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | ircbot_wikipages | MyISAM | | ircbot_wikiusers | MyISAM | | FriendlySessions | MyISAM | | pmid_id | MyISAM | | language | MyISAM | | namespacename | MyISAM | | servermapping | MyISAM | | wiki | MyISAM | | language | MyISAM | | namespace | MyISAM | | namespace_test | MyISAM | | namespacename | MyISAM | | servermapping | MyISAM | | wiki | MyISAM | | searchindex | MyISAM | | DenkmalGemeinde | MyISAM | | monuments_at_(de) | MyISAM | | wle_at_wp | MyISAM | | wle_at_wp_objects | MyISAM | | bad_images | MyISAM | | daily | MyISAM | | genderless_people | MyISAM | | item_pairs | MyISAM | | people_no_date | MyISAM | | people_no_dob | MyISAM | | people_no_dod | MyISAM | | potential_alma_mater | MyISAM | | potential_commonscat | MyISAM | | potential_disambig | MyISAM | | potential_genre | MyISAM | | potential_nationality | MyISAM | | potential_new_pages | MyISAM | | potential_occupation | MyISAM | | potential_people | MyISAM | | scores | MyISAM | | users | MyISAM | | 2015_fotos | MyISAM | | 2015_fotos_commons | MyISAM | | 2015_jury | MyISAM | | 2015_points | MyISAM | | 2015_v_jury | MyISAM | | 2015_v_votes | MyISAM | | 2015_votes | MyISAM | | mon_pics | MyISAM | | mon_pics_commons | MyISAM | | feed_matches | MyISAM | | worldadmin98 | MyISAM | | coords | MyISAM | | existing_media | MyISAM | | search_entry | MyISAM | | search_region | MyISAM | | alig_equiv | MyISAM | | alig_pages | MyISAM | | alig_values | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | parsec_questions | MyISAM | | dir_ft | MyISAM | | searchindex | MyISAM | | ratings | MyISAM | | sessions | MyISAM | | settings | MyISAM | | users | MyISAM | | answer | MyISAM | | unit | MyISAM | | user | MyISAM | | catalogue | MyISAM | | catalogue_meta | MyISAM | | fact | MyISAM | | fact_meta | MyISAM | ivewiki | searchindex | MyISAM | estwiki | searchindex | MyISAM | | trr | MyISAM | | searchindex | MyISAM | | wlm_2015_fotos | MyISAM | | wlm_2015_jury | MyISAM | | wlm_2015_points | MyISAM | | wlm_2015_v_jury | MyISAM | | wlm_2015_v_votes | MyISAM | | wlm_2015_votes | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | store | MyISAM | | Bilderwunsch_Geo | MyISAM | | edit_log | MyISAM | | review_filter | MyISAM | | review_opt_in | MyISAM | | cvu | MyISAM | | fnbot_edit_log | MyISAM | | log_filter | MyISAM | | logfilter_user | MyISAM | | rev_query | MyISAM | | sumdisc_log | MyISAM | | thanks | MyISAM | | thanks_dump | MyISAM | | thanks_offset | MyISAM | | thanks_user | MyISAM | | log | MyISAM | | optin_list | MyISAM | | rev_log | MyISAM | | thx_list | MyISAM | | alvaro_searchindex | MyISAM | | searchindex | MyISAM | | mw_searchindex | MyISAM | | trr | MyISAM | | tmp_pages | MyISAM | | tmp_redirects | MyISAM | class="o">-test | searchindex | MyISAM | | searchindex | MyISAM | | lime_answers | MyISAM | | lime_assessments | MyISAM | | lime_boxes | MyISAM | | lime_conditions | MyISAM | | lime_defaultvalues | MyISAM | | lime_expression_errors | MyISAM | | lime_failed_login_attempts | MyISAM | | lime_groups | MyISAM | | lime_labels | MyISAM | | lime_labelsets | MyISAM | | lime_map_tutorial_users | MyISAM | | lime_notifications | MyISAM | | lime_participant_attribute | MyISAM | | lime_participant_attribute_names | MyISAM | | lime_participant_attribute_names_lang | MyISAM | | lime_participant_attribute_values | MyISAM | | lime_participant_shares | MyISAM | | lime_participants | MyISAM | | lime_permissions | MyISAM | | lime_plugin_settings | MyISAM | | lime_plugins | MyISAM | | lime_question_attributes | MyISAM | | lime_questions | MyISAM | | lime_quota | MyISAM | | lime_quota_languagesettings | MyISAM | | lime_quota_members | MyISAM | | lime_saved_control | MyISAM | | lime_sessions | MyISAM | | lime_settings_global | MyISAM | | lime_settings_user | MyISAM | | lime_survey_213756 | MyISAM | | lime_survey_213756_timings | MyISAM | | lime_survey_links | MyISAM | | lime_survey_url_parameters | MyISAM | | lime_surveymenu | MyISAM | | lime_surveymenu_entries | MyISAM | | lime_surveys | MyISAM | | lime_surveys_groups | MyISAM | | lime_surveys_languagesettings | MyISAM | | lime_template_configuration | MyISAM | | lime_templates | MyISAM | | lime_tutorial_entries | MyISAM | | lime_tutorial_entry_relation | MyISAM | | lime_tutorials | MyISAM | | lime_user_groups | MyISAM | | lime_user_in_groups | MyISAM | | lime_users | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | ace2018 | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | mw_searchindex | MyISAM | | demoniemen | MyISAM | | log | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | ce-previews | searchindex | MyISAM | | searchindex | MyISAM | | adjektive | MyISAM | | nomen | MyISAM | | verben | MyISAM | | w_searchindex | MyISAM | | fd_framadate_migration | MyISAM | | mailbox | MyISAM | | quotes | MyISAM | | searchindex | MyISAM | | translate_tmf | MyISAM | | searchindex | MyISAM | | translate_tmf | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | translate_tmf | MyISAM | | searchindex | MyISAM | | translate_tmf | MyISAM | | searchindex | MyISAM | | translate_tmf | MyISAM | | searchindex | MyISAM | | translate_tmf | MyISAM | | Taxonomy | MyISAM | | Users | MyISAM | | searchindex | MyISAM | | wp9c_loginizer_logs | MyISAM | | wp9c_loginizer_logs | MyISAM | | searchindex | MyISAM | | smwsearchindex | MyISAM | | h_attachments | MyISAM | | h_banned_emails | MyISAM | | h_banned_ips | MyISAM | | h_categories | MyISAM | | h_custom_fields | MyISAM | | h_custom_statuses | MyISAM | | h_kb_articles | MyISAM | | h_kb_attachments | MyISAM | | h_kb_categories | MyISAM | | h_logins | MyISAM | | h_mail | MyISAM | | h_notes | MyISAM | | h_online | MyISAM | | h_pipe_loops | MyISAM | | h_replies | MyISAM | | h_reply_drafts | MyISAM | | h_reset_password | MyISAM | | h_service_messages | MyISAM | | h_std_replies | MyISAM | | h_ticket_templates | MyISAM | | h_tickets | MyISAM | | h_users | MyISAM | | searchindex | MyISAM | | searchindex | MyISAM | | Certificate | MyISAM | | Queue | MyISAM | | Response | MyISAM | | Users | MyISAM | | Workshop | MyISAM | | pagetriagesearchindex | MyISAM | | mw_searchindex | MyISAM | | P569 | MyISAM | | P570 | MyISAM | | searchindex | MyISAM | | cxsearchindex | MyISAM | | coord_afwiki | MyISAM | | coord_alswiki | MyISAM | | coord_arwiki | MyISAM | | coord_astwiki | MyISAM | | coord_azwiki | MyISAM | | coord_bewiki | MyISAM | | coord_bgwiki | MyISAM | | coord_bpywiki | MyISAM | | coord_cawiki | MyISAM | | coord_cebwiki | MyISAM | | coord_commonswiki | MyISAM | | coord_cswiki | MyISAM | | coord_dawiki | MyISAM | | coord_dewiki | MyISAM | | coord_elwiki | MyISAM | | coord_enwiki | MyISAM | | coord_eowiki | MyISAM | | coord_eswiki | MyISAM | | coord_etwiki | MyISAM | | coord_euwiki | MyISAM | | coord_fawiki | MyISAM | | coord_fiwiki | MyISAM | | coord_frwiki | MyISAM | | coord_fywiki | MyISAM | | coord_gawiki | MyISAM | | coord_glwiki | MyISAM | | coord_hewiki | MyISAM | | coord_hiwiki | MyISAM | | coord_hrwiki | MyISAM | | coord_htwiki | MyISAM | | coord_huwiki | MyISAM | | coord_hywiki | MyISAM | | coord_idwiki | MyISAM | | coord_ilowiki | MyISAM | | coord_itwiki | MyISAM | | coord_jawiki | MyISAM | | coord_kawiki | MyISAM | | coord_kkwiki | MyISAM | | coord_kowiki | MyISAM | | coord_kuwiki | MyISAM | | coord_lawiki | MyISAM | | coord_lbwiki | MyISAM | | coord_ltwiki | MyISAM | | coord_lvwiki | MyISAM | | coord_mkwiki | MyISAM | | coord_mlwiki | MyISAM | | coord_mswiki | MyISAM | | coord_ndswiki | MyISAM | | coord_newwiki | MyISAM | | coord_nlwiki | MyISAM | | coord_nnwiki | MyISAM | | coord_nowiki | MyISAM | | coord_nvwiki | MyISAM | | coord_ocwiki | MyISAM | | coord_oswiki | MyISAM | | coord_pamwiki | MyISAM | | coord_plwiki | MyISAM | | coord_pmswiki | MyISAM | | coord_ptwiki | MyISAM | | coord_rowiki | MyISAM | | coord_ruwiki | MyISAM | | coord_shwiki | MyISAM | | coord_simplewiki | MyISAM | | coord_skwiki | MyISAM | | coord_slwiki | MyISAM | | coord_srwiki | MyISAM | | coord_svwiki | MyISAM | | coord_swwiki | MyISAM | | coord_tawiki | MyISAM | | coord_tewiki | MyISAM | | coord_thwiki | MyISAM | | coord_tlwiki | MyISAM | | coord_trwiki | MyISAM | | coord_ukwiki | MyISAM | | coord_uzwiki | MyISAM | | coord_vecwiki | MyISAM | | coord_viwiki | MyISAM | | coord_vowiki | MyISAM | | coord_warwiki | MyISAM | | coord_zhwiki | MyISAM | | principle_links | MyISAM | | projectbanner | MyISAM | | redlinks_enwiki_p | MyISAM | | region_cache | MyISAM | | task | MyISAM | | task_page | MyISAM | | worldadmin98 | MyISAM | -----------------------------+--------+ in set (0.28 sec)

I would contact the tools' owner and check if there is an specific reason for them to be MyISAM, and if not, encourage them to convert them to InnoDB.

In T266587#6615352, @Marostegui wrote:

I would contact the tools' owner and check if there is an specific reason for them to be MyISAM, and if not, encourage them to convert them to InnoDB.

s54524__mediawiki.searchindex (notwikilambda tool) is MyISAM because it’s a MediaWiki table that needs fulltext indexes. (Compare T107875.) The same is probably true for a handful of other tables in that list.

Mentioned in SAL (#wikimedia-cloud) [2020-11-10T16:24:20Z] <bstorm> set toolsdb to read-only T266587

Mentioned in SAL (#wikimedia-cloud) [2020-11-10T16:37:58Z] <arturo> icinga downtime toolschecker for 2h becasue toolsdb maintenance (T266587)

Mentioned in SAL (#wikimedia-cloud) [2020-11-10T16:41:21Z] <arturo> set paws in sqlite mode because T266587 (kubectl --namespace prod edit configmap hub-config)

Change 640483 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] toolsdb: set up quickcopy to move the dump

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

gerritbot added a project: Patch-For-Review.Nov 10 2020, 6:43 PM

Change 640483 merged by Bstorm:
[operations/puppet@production] toolsdb: set up quickcopy to move the dump

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

Dump is still progressing. It's taking a very long time, but it got much further in read-only mode than it did in full operation already. (now at 60GB compressed)

• Bstorm updated the task description. (Show Details)Nov 10 2020, 10:30 PM

Mentioned in SAL (#wikimedia-cloud) [2020-11-11T06:06:42Z] <bstorm> dump completed, transferring to replica to start things up again T266587

In T266587#6615352, @Marostegui wrote:

I would contact the tools' owner and check if there is an specific reason for them to be MyISAM, and if not, encourage them to convert them to InnoDB.

It seems weird to me that the mysql tables are MyISAM. Is that something we can fix on the fly?

I can spawn tickets to try to get users to switch. I see some of these are ones that have crashed replication before which isn't surprising, and I've been really hoping to remove things that don't replicate rather than the other way around.

Transfer complete. Loading the database on the new replica

Benjavalero subscribed.Nov 11 2020, 7:08 AM

Mentioned in SAL (#wikimedia-cloud) [2020-11-11T08:20:38Z] <bstorm> loading dump on the replica T266587

Zache subscribed.Nov 11 2020, 11:43 AM

First, thank you for fixing this. Do you have any best/worst guess when the tooldb is up again? There's WLM jurying going using http://montage.toolforge.org which is down and we need to inform the jurors of when the reviewing the photos is expected to continue.

CptViraj subscribed.Nov 11 2020, 2:54 PM

@Zache At this point I am sure it will be done today (US day). I don't have a better estimate yet. The dump took 14 hours. The load will hopefully be quicker. I started it last night, and compression is not happening in the middle of it. I worry the primary will drift too far if I turn off read-only just now.

If the load also takes 14 hours, which I do have hope that it won't, it started at around 830 UTC. That would put the completion at 2230 UTC. I hope it will be faster, as I said.

Beetstra subscribed.Nov 11 2020, 4:57 PM

Chicocvenancio subscribed.Nov 11 2020, 6:17 PM

For future reference, while this imports, the compressed size of a full dump of ToolsDB at this point in time is 110GB. The uncompressed size is more like 1TB.

That means that if we get much growth (like 500GB) without introducing cinder or T212595 (which I believe also depends on cinder), this will become impossible in the future. Good to know.

Daniel_Mietchen subscribed.Nov 11 2020, 11:15 PM

This is taking much longer than my estimate. As is, I'm able to see that it is still recreating some of the very large databases.

M2k_dewiki subscribed.Nov 11 2020, 11:18 PM

Atsirlin subscribed.Nov 12 2020, 1:34 AM

It's still rebuilding. The real disadvantage of this monolithic design is that the few really huge databases in there hold up progress for lots of tiny ones. It'll get there. No errors or anything so far.

In trying to calculate better time frames for how quickly this is likely to work. It seems to have succeeded in a quarter of the data imported in 28 hours. In light of that, we've decided to take the risk and let data continue to flow while the base import continues. As long as it doesn't take too long, it should still really should be able to pick up replication when it is all set.

Fingers crossed that we don't have to start all over again, but since there's a good chance we don't need to keep it readonly until this is done, it's unlocked now. In the event this proves wrong, we will have to start over at a later time.

Slaporte awarded a token.Nov 12 2020, 4:34 AM

Zache awarded a token.Nov 12 2020, 7:46 AM

1997kB subscribed.Nov 12 2020, 7:46 AM

William_Avery subscribed.Nov 12 2020, 9:24 AM

Beetstra added a subscriber: Billinghurst.Nov 12 2020, 7:06 PM

The import died with ERROR 2006 (HY000) at line 617487: MySQL server has gone away
Trying to find out why now.

• Bstorm added a comment.Nov 15 2020, 7:23 PM

This comment was removed by • Bstorm.

I've restarted the import while I look for some reason that might have happened.

Mentioned in SAL (#wikimedia-cloud) [2020-11-15T19:36:04Z] <bstorm> set max_allowed_package to 64MB on clouddb-toolsdb-03 T266587

Removed comment on replication since that was actually just old logs. Nothing current about that. It is entirely possible that there are larger-than-default inserts in there that used a larger max_allowed_packet (not package...sorry for the typo in the log) in the session variables. By doubling what they were (32MB), it might succeed.

That increases the chances that we will fall too far behind to start replication. Really hoping not :(

Mentioned in SAL (#wikimedia-cloud) [2020-11-15T19:45:45Z] <bstorm> restarting the import to clouddb-toolsdb-03 with --max-allowed-packet=1G to rule out that as a problem entirely T266587

Since it seems very likely that is why mariadb hung up on me, setting the session variable to the maximum to try to prevent this from happening again.

In T266587#6618389, @Bstorm wrote:

In T266587#6615352, @Marostegui wrote:

I would contact the tools' owner and check if there is an specific reason for them to be MyISAM, and if not, encourage them to convert them to InnoDB.

It seems weird to me that the mysql tables are MyISAM. Is that something we can fix on the fly?

That's the default until 10.4.
From 10.4 they use Aria, which is supposedly crash-safe.

I can spawn tickets to try to get users to switch. I see some of these are ones that have crashed replication before which isn't surprising, and I've been really hoping to remove things that don't replicate rather than the other way around.

Nintendofan885 subscribed.Nov 17 2020, 7:38 PM

The database load died again. This time it was:
ERROR 1030 (HY000) at line 663214: Got error 175 "File too short; Expected more data in file" from storage engine Aria
@Marostegui any ideas? Is my dump bad somehow? I'll google around a bit in case there's something I can do. I'd rather not spend another 14 hours in read-only mode. I also wonder if I can get away with throwing this in read-only and copying the data directory. I have a solid rsync setup.

At this point, I wouldn't try the mysqldump (or mydumper again). What you suggest is what I would do.

Set the host read only.
Once you are sure it is read only run: show master status\G (run it a few times to make sure it doesn't change, that would mean no writes are happening). Write down those coordinates.
Stop MySQL.
Copy the directory to the new replica.
Once it is done, on the new replica do: systemctl set-environment MYSQLD_OPTS="--skip-slave-start" and start mysql (make sure the directory privileges are good, or otherwise mysql will not start).
Run on the new replica reset replication: mysql -e "stop slave; reset slave all" and also do a mysql_upgrade just in case.
Configure replication on the new replica: change master to master_host='master.fqdn', master_user='theuseryouuse', master_ssl=1, master_password='itspassword', master_log_file='the log file the above show master status showed', master_log_pos='the position grabbed from show master status'; and then start slave;.
Start mysql on the master, and the slave will reconnect and start replicating if it all went fine

Sounds good. I'll set another time for it. Based on the rsync speeds I saw last time with the dump, I am hopeful that this will be a comparatively short read-only period.

@Bstorm keep it in mind that it won't be a read-only time. It will be a "down" time. As mysql needs to be switched off.
Not sure if in your environment you can use transfer.py and try to use xtrabackup for it: https://wikitech.wikimedia.org/wiki/Transfer.py if you can, then you need read-only and you won't need to stop mysql.

Thank you again! I'll poke around and see.

FYI this is on toolsdb secondary server

aborrero@clouddb1002:~$ sudo run-puppet-agent
Info: Using configured environment 'production'
Info: Retrieving pluginfacts
Info: Retrieving plugin
Info: Retrieving locales
Info: Loading facts
Error: Could not retrieve catalog from remote server: Error 500 on SERVER: Server Error: Evaluation Error: Error while evaluating a Resource Statement, Function lookup() did not find a value for the name 'profile::wmcs::services::toolsdb::rebuild_primary' (file: /etc/puppet/modules/profile/manifests/wmcs/services/toolsdb_secondary.pp, line: 5) on node clouddb1002.clouddb-services.eqiad.wmflabs
Warning: Not using cache on failed catalog
Error: Could not retrieve catalog; skipping run

Sorry if this is not the right ticket to report this.

In T266587#6655335, @aborrero wrote:

FYI this is on toolsdb secondary server

aborrero@clouddb1002:~$ sudo run-puppet-agent
Info: Using configured environment 'production'
Info: Retrieving pluginfacts
Info: Retrieving plugin
Info: Retrieving locales
Info: Loading facts
Error: Could not retrieve catalog from remote server: Error 500 on SERVER: Server Error: Evaluation Error: Error while evaluating a Resource Statement, Function lookup() did not find a value for the name 'profile::wmcs::services::toolsdb::rebuild_primary' (file: /etc/puppet/modules/profile/manifests/wmcs/services/toolsdb_secondary.pp, line: 5) on node clouddb1002.clouddb-services.eqiad.wmflabs
Warning: Not using cache on failed catalog
Error: Could not retrieve catalog; skipping run

Sorry if this is not the right ticket to report this.

I know. I broke that. I'll add a value there so it will not keep erroring.

Outage scheduled for 2020-12-16@1700 UTC.

Also:

clouddb1002 is a Cloud user database secondary (role::wmcs::db::toolsdb_secondary)
The last Puppet run was at Tue Nov 10 21:48:10 UTC 2020 (40400 minutes ago).

I seem to have left that broken for way longer than I should have 😅
Puppet is fixed there now.

Change 649893 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] toolsdb: set the rsync script to be the whole data dir

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

Change 649893 merged by Bstorm:
[operations/puppet@production] toolsdb: set the rsync script to be the whole data dir

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

Mentioned in SAL (#wikimedia-cloud) [2020-12-16T17:06:14Z] <bstorm> switching the secondary config back to clouddb1002 in order to minimize concerns about affecting ceph performance T266587

Mentioned in SAL (#wikimedia-cloud) [2020-12-16T17:24:28Z] <bstorm> settings toolsdb to readonly to prepare for shutdown T266587

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
 File: log.225791
 Position: 66155872
 Binlog_Do_DB:
 Binlog_Ignore_DB:

Mentioned in SAL (#wikimedia-cloud) [2020-12-16T17:28:08Z] <bstorm> shutdown toolsdb T266587

Mentioned in SAL (#wikimedia-cloud) [2020-12-16T17:29:48Z] <bstorm> stopped mariadb on the replica T266587

Mentioned in SAL (#wikimedia-cloud) [2020-12-16T17:31:49Z] <bstorm> sync started from toolsdb to its replica server T266587

Mentioned in SAL (#wikimedia-cloud) [2020-12-16T18:34:40Z] <bstorm> restarted sync from toolsdb to its replica server after cleanup to prevent disk filling T266587

JJMC89 mentioned this in T256501: CopyPatrol has stopped.Dec 16 2020, 7:28 PM

JJMC89 mentioned this in T270337: "sql tools" returns "connection refused" from freebase tool.Dec 16 2020, 7:49 PM

Mentioned in SAL (#wikimedia-cloud) [2020-12-17T01:10:38Z] <bstorm> the sync is done and we have a good copy of the toolsdb data, proceeding with the upgrades and stuff to that hypervisor while configuring replication to work again T266587

Mentioned in SAL (#wikimedia-cloud) [2020-12-17T02:14:19Z] <bstorm> toolsdb is back and so is the replica T266587

Mentioned in SAL (#wikimedia-cloud) [2020-12-17T02:22:16Z] <bstorm> Set PAWS hub back to using mariadb T266587

1997kB awarded a token.Dec 17 2020, 2:27 AM

It's still replicating this morning. This is done and also is a fine archive of what we have to do if ToolsDB does this again. At least we know it will only take a day or so.

• Bstorm mentioned this in T257274: Database table for request tool needs maintenance to prevent data loss.Dec 17 2020, 3:10 PM

• Bstorm mentioned this in T257275: Database tables for checkwiki tool needs maintenance to prevent data loss.

• Bstorm mentioned this in T253738: ToolsDB: master crashed, replica having consistency issues.

fnegri mentioned this in T301949: ToolsDB upgrade => Bullseye, MariaDB 10.4.Oct 14 2022, 3:55 PM

fnegri mentioned this in T322039: [toolsdb] clouddb1002 stopped replicating from clouddb1001.Oct 31 2022, 3:28 PM

fnegri mentioned this in T328693: [toolsdb] Migrate "s54518__mw" db to Trove.Mar 27 2023, 5:05 PM

Maintenance_bot removed a project: Patch-For-Review.Mar 27 2023, 5:11 PM