Page MenuHomePhabricator

mysqldump is timing out preventing all tables from being included in the dump
Open, HighPublic

Description

The dump is not including all tables due to the following...

The command is

mysqldump --host=tools-db --single-transaction s51138__heritage_p > monuments_db-new.sql

from update_monuments.log:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `monuments_all` at row: 1004392

Event Timeline

Restricted Application added subscribers: Zppix, JeanFred, Aklapper. · View Herald TranscriptJun 23 2016, 4:56 PM

grepping a bit more:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `monuments_all` at row: 963899
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `monuments_all` at row: 1004392
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `monuments_all` at row: 946161
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `monuments_all` at row: 902877
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `statistics` at row: 530451
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `monuments_all` at row: 1186987
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `monuments_all` at row: 1224850

Looking around, seems beyond my DBA knowledge. What shall we do? @jcrespo, would you have any advice for this?

Which server is this? Is this toolsdb?

JeanFred updated the task description. (Show Details)Jun 30 2016, 6:07 PM

Which server is this? Is this toolsdb?

Thanks for chiming in − and sorry for giving you very little context :)

Yes, this is s51138__heritage_p on tools-db

Upping the priority since:
a) it means there is no actual backup of the data,
b) there is no way to access the source tables for someone who doesn't have access to toollabs,
c) it makes it impossible to test new code through (the current setup of) docker-compose.

There is no timeout or process killing connections on toolsdb (and that actually may be the indirect case, the absence of limits, as there is high load on that server). This could be several things:

  1. something on the mysqldump configuration (or the lack of it)- I've seen this many times for things like max-package-size for very specific data (e.g. large blobs). E.g: rOPUP77db92e9efaf720fe6677c43c352983bb7e6fa78
  2. grants- mysqldump may require lock tables or something else that as a regular user you may not have- it usually requires specific options. I remember another users having an issue with this, and it was identified as an option change (but I think it was only on recovery).
  3. Excessive load on that server? I see some heavy users, but nothing to justify breakage

As root, I have tested running:
mysqldump --single-transaction s51138__heritage_p | pigz -c > monuments_db-new.sql.gz

and in a few seconds I have generated a 148MB compressed file monuments_db-new.sql.gz , with no errors:

zgrep "CREATE TABLE" monuments_db-new.sql.gz
CREATE TABLE `admin_tree` (
CREATE TABLE `commonscat` (
CREATE TABLE `id_dump` (
CREATE TABLE `image` (
CREATE TABLE `monuments-old_ch_(de)` (
CREATE TABLE `monuments-old_ch_(en)` (
CREATE TABLE `monuments-old_ch_(fr)` (
CREATE TABLE `monuments-old_ch_(it)` (
CREATE TABLE `monuments_ad_(ca)` (
CREATE TABLE `monuments_all` (
CREATE TABLE `monuments_am_(hy)` (
CREATE TABLE `monuments_aq_(en)` (
CREATE TABLE `monuments_ar_(es)` (
CREATE TABLE `monuments_at_(de)` (
CREATE TABLE `monuments_az_(az)` (
CREATE TABLE `monuments_be-bru_(nl)` (
CREATE TABLE `monuments_be-vlg_(en)` (
CREATE TABLE `monuments_be-vlg_(fr)` (
CREATE TABLE `monuments_be-vlg_(nl)` (
CREATE TABLE `monuments_be-wal_(en)` (
CREATE TABLE `monuments_be-wal_(fr)` (
CREATE TABLE `monuments_be-wal_(nl)` (
CREATE TABLE `monuments_bo_(es)` (
CREATE TABLE `monuments_by_(be-tarask)` (
CREATE TABLE `monuments_by_(be-x-old)` (
CREATE TABLE `monuments_ca_(en)` (
CREATE TABLE `monuments_ca_(fr)` (
CREATE TABLE `monuments_ch2_(de)` (
CREATE TABLE `monuments_ch3_(de)` (
CREATE TABLE `monuments_ch_(de)` (
CREATE TABLE `monuments_ch_(fr)` (
CREATE TABLE `monuments_ch_(it)` (
CREATE TABLE `monuments_cl_(es)` (
CREATE TABLE `monuments_cm_(fr)` (
CREATE TABLE `monuments_cn_(en)` (
CREATE TABLE `monuments_co_(es)` (
CREATE TABLE `monuments_cz_(cs)` (
CREATE TABLE `monuments_de-by_(de)` (
CREATE TABLE `monuments_de-he_(de)` (
CREATE TABLE `monuments_de-nrw-bm_(de)` (
CREATE TABLE `monuments_de-nrw-k_(de)` (
CREATE TABLE `monuments_de-nrw_(de)` (
CREATE TABLE `monuments_dk-bygninger_(da)` (
CREATE TABLE `monuments_dk-fortidsminder_(da)` (
CREATE TABLE `monuments_dz_(ar)` (
CREATE TABLE `monuments_ee_(et)` (
CREATE TABLE `monuments_es-ct_(ca)` (
CREATE TABLE `monuments_es-gl_(gl)` (
CREATE TABLE `monuments_es-vc_(ca)` (
CREATE TABLE `monuments_es_(ca)` (
CREATE TABLE `monuments_es_(es)` (
CREATE TABLE `monuments_fr-object_(fr)` (
CREATE TABLE `monuments_fr_(ca)` (
CREATE TABLE `monuments_fr_(fr)` (
CREATE TABLE `monuments_gb-eng_(en)` (
CREATE TABLE `monuments_gb-nir_(en)` (
CREATE TABLE `monuments_gb-sct_(en)` (
CREATE TABLE `monuments_gb-wls_(en)` (
CREATE TABLE `monuments_gh_(en)` (
CREATE TABLE `monuments_hk-hb_(en)` (
CREATE TABLE `monuments_hk_(en)` (
CREATE TABLE `monuments_hu_(hu)` (
CREATE TABLE `monuments_ie_(en)` (
CREATE TABLE `monuments_il_(he)` (
CREATE TABLE `monuments_in_(en)` (
CREATE TABLE `monuments_ir_(fa)` (
CREATE TABLE `monuments_it-88_(ca)` (
CREATE TABLE `monuments_it-bz_(de)` (
CREATE TABLE `monuments_it_(it)` (
CREATE TABLE `monuments_jo_(ar)` (
CREATE TABLE `monuments_jp-nhs_(en)` (
CREATE TABLE `monuments_ke_(en)` (
CREATE TABLE `monuments_lu_(lb)` (
CREATE TABLE `monuments_mt_(de)` (
CREATE TABLE `monuments_mx_(es)` (
CREATE TABLE `monuments_nl-aw_(en)` (
CREATE TABLE `monuments_nl-aw_(nl)` (
CREATE TABLE `monuments_nl-gem_(nl)` (
CREATE TABLE `monuments_nl-prov_(nl)` (
CREATE TABLE `monuments_nl_(nl)` (
CREATE TABLE `monuments_no_(no)` (
CREATE TABLE `monuments_np_(en)` (
CREATE TABLE `monuments_pa_(es)` (
CREATE TABLE `monuments_ph_(en)` (
CREATE TABLE `monuments_pk_(en)` (
CREATE TABLE `monuments_pl-old_(pl)` (
CREATE TABLE `monuments_pl_(pl)` (
CREATE TABLE `monuments_pt_(pt)` (
CREATE TABLE `monuments_ro_(ro)` (
CREATE TABLE `monuments_rs_(sr)` (
CREATE TABLE `monuments_ru-old_(ru)` (
CREATE TABLE `monuments_ru_(ru)` (
CREATE TABLE `monuments_se-arbetsl_(sv)` (
CREATE TABLE `monuments_se-arbetsliv_(sv)` (
CREATE TABLE `monuments_se-bbr_(sv)` (
CREATE TABLE `monuments_se-fornmin_(sv)` (
CREATE TABLE `monuments_se-fornminne_(sv)` (
CREATE TABLE `monuments_se-ship_(sv)` (
CREATE TABLE `monuments_sk_(de)` (
CREATE TABLE `monuments_sk_(sk)` (
CREATE TABLE `monuments_sv_(es)` (
CREATE TABLE `monuments_th_(th)` (
CREATE TABLE `monuments_tn_(fr)` (
CREATE TABLE `monuments_ua_(uk)` (
CREATE TABLE `monuments_us-ca_(en)` (
CREATE TABLE `monuments_us_(en)` (
CREATE TABLE `monuments_uy_(es)` (
CREATE TABLE `monuments_ve_(es)` (
CREATE TABLE `monuments_za_(en)` (
CREATE TABLE `prox_search` (
CREATE TABLE `statistics` (
CREATE TABLE `statisticsct` (
CREATE TABLE `wlpa_at_(de)` (
CREATE TABLE `wlpa_es-ct_(ca)` (
CREATE TABLE `wlpa_fi_(fi)` (
CREATE TABLE `wlpa_il_(en)` (

Please have a look at our default mysqldump configuration on puppet to see our tunings.

I see you are using ENGINE=MyISAM for many or your tables. While you can do anything you want (that is the reason for these servers!), --single-transaction will not "work" for myisam tables, and I would not be surprised if that caused issues when locking or not locking these tables. MyISAM tables may also be accidentally locked by other users easily, and that could block mysqldump (unlike InnoDB tables). That are not the only issues of MyISAM, but that is another story.

As a last resort, given that this is an important tool, we can puppetize the dumps generation- please send a commit to operations/puppet and I will be glad to integrate it with our infrastructure, if quality standards are met.

Lokal_Profil added a subscriber: Multichill.EditedJul 15 2016, 11:11 AM

@jcrespo Thanks for investigating!

@Multichill Was there a specific reason for chosing MyISAM?

@jcrespo Thanks for investigating!

Seconded, much appreciated!

@Multichill Was there a specific reason for chosing MyISAM?

I assume this is a leftover from the Toolserver days? It’s cheap enough to change and recreate the tables :)

jcrespo moved this task from Triage to Backlog (help welcome) on the DBA board.

@Multichill Was there a specific reason for chosing MyISAM?

I assume this is a leftover from the Toolserver days? It’s cheap enough to change and recreate the tables :)

If we are not using any special feature of it then I would say we simply change.

I do not think MyISAM is the root cause here, as I could create a (potentially corrupted, but successful) dump.

@Multichill Was there a specific reason for chosing MyISAM?

I assume this is a leftover from the Toolserver days? It’s cheap enough to change and recreate the tables :)

It does seem a leftover from the initial version: rTHER255ccd3eecaf8ee99e1e1836c2ab581b5be9b9b7

I did a quick local change to "InnoDB" and attempted a harvest + fill_table_monuments_all in docker and got the following error

ERROR 1214 (HY000) at line 15: The used table type doesn't support FULLTEXT indexes

Is this maybe the reason why MyISAM was used?

I did a quick local change to "InnoDB" and attempted a harvest + fill_table_monuments_all in docker and got the following error
ERROR 1214 (HY000) at line 15: The used table type doesn't support FULLTEXT indexes
Is this maybe the reason why MyISAM was used?

Looks like newer InnoDB supports FULLTEXT according to this SO post. I don't know enough about MariaDB/MySQL to figure out if the support exists there and if so from what version.

@Lokal_Profil, by pure bad luck, your schema happens to be on the only database server on all our infrastructure that doesn't support FULLTEXT InnoDB. An upgrade (requiring maintenance) will be done on January T123731#2779984 , enabling this feature on all database servers finally.

ERROR 1214 (HY000) at line 15: The used table type doesn't support FULLTEXT indexes
Is this maybe the reason why MyISAM was used?

That must be it! I had later been thinking over that I tried changing those dbs to InnoDB years ago, but for some reason that I couldn't remember I hadn't.

Change 324396 had a related patch set uploaded (by Lokal Profil):
[Blocked] Change DB engine to InnoDB

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

Change 324396 had a related patch set uploaded (by Lokal Profil):
[Blocked] Change DB engine to InnoDB
https://gerrit.wikimedia.org/r/324396

So this is blocked on T123731#2779984 but at least this way we have it ready to roll out.

jcrespo moved this task from Backlog (help welcome) to Done on the DBA board.Feb 20 2017, 2:23 PM

Toolsdb were finally upgraded to MariaDB 10/jessie at T157358 last week , so the version bump is not longer a blocker.

Toolsdb were finally upgraded to MariaDB 10/jessie at T157358 last week , so the version bump is not longer a blocker.

Thanks. I've marked https://gerrit.wikimedia.org/r/324396 as non-blocked

Change 324396 merged by jenkins-bot:
Change DB engine to InnoDB

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

@Lokal_Profil is there anything pending here?

Marostegui closed this task as Resolved.Jun 8 2018, 2:17 PM

Going to resolve this as per T138517#3051262.
Feel free to reopen if needed

@Lokal_Profil is there anything pending here?

I honestly cannot remember @JeanFred maybe?

Multichill reopened this task as Open.Jun 10 2018, 11:50 AM

tools.heritage@tools-bastion-02:~/logs$ date
zo jun 10 11:44:09 UTC 2018
tools.heritage@tools-bastion-02:~/logs$ ls -alt update_monuments.log
-rw-rw---- 1 tools.heritage tools.heritage 96412359 jun 10 11:44 update_monuments.log
tools.heritage@tools-bastion-02:~/logs$ grep mysqldump update_monuments.log
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table monuments_am_(hy) at row: 13534
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table monuments_be-vlg_(fr) at row: 19365
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table monuments_be-vlg_(fr) at row: 56932

But that's because the log hasn't been rotated for ages. Still failing every once in a while:

2018-06-03_16:57:45 Dump database...
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table monuments_be-vlg_(fr) at row: 17667
2018-06-03_17:00:22 Replace the sql file atomically...

2018-06-05_15:26:23 Dump database...
mysqldump: Got error: 2003: "Can't connect to MySQL server on 'tools-db' (111)" when trying to connect
2018-06-05_15:26:23 Replace the sql file atomically...
2018-06-05_15:26:23 Refill prox_search table...
Could not open input file: ./prox_search/fill_table_prox_search.php
2018-06-05_15:26:23 Done with the update!

2018-06-09_14:55:40 Dump database...
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table monuments_all at row: 1357249
2018-06-09_14:58:21 Replace the sql file atomically...
2018-06-09_14:58:21 Refill prox_search table...
Could not open input file: ./prox_search/fill_table_prox_search.php
2018-06-09_14:58:21 Done with the update!

This is still happening:

2018-09-04_19:24:21 Dump database...
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `monuments_all` at row: 1466428

It was not that though ;-)

Can you try the mysqldump adding --skip-extended-insert to the original command?

This will make the data dump and the load slower, but if it works at least we would have a mysqldump :-)

Huh, looks like that just succeeded, in 6 minutes:

2019-09-02_16:31:06 Dump database...
2019-09-02_16:37:34 Replace the sql file atomically...

Excellent news!
@JeanFred can this task be closed then?

Thanks