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

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?

In T138517#2408761, @jcrespo wrote:

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.

@jcrespo Thanks for investigating!

@Multichill Was there a specific reason for chosing MyISAM?

In T138517#2465550, @Lokal_Profil wrote:

@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 :)

In T138517#2465555, @JeanFred wrote:

In T138517#2465550, @Lokal_Profil wrote:

@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.

In T138517#2465555, @JeanFred wrote:

@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?

In T138517#2796649, @Lokal_Profil wrote:

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.

In T138517#2796670, @Lokal_Profil wrote:

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

In T138517#2834395, @gerritbot wrote:

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.

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

In T138517#3040791, @jcrespo wrote:

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

Mentioned in SAL (#wikimedia-labs) [2017-04-20T15:24:46Z] <JeanFred> Deploy latest from Git master: ae1e775 (T138517), 2bd9781, 04a19e0 (T158911), e8dbe35, 4de8898, ea942d8, f956ab5, b63ebac, dd8c0c8, 63b3bc8, 8b15472

Mentioned in SAL (#wikimedia-labs) [2017-05-19T17:22:38Z] <JeanFred> Deploy latest from Git master: ae1e775 (T138517), 2bd9781, 04a19e0 (T158911), e8dbe35, 4de8898, ea942d8, f956ab5, b63ebac, dd8c0c8, 63b3bc8, 8b15472

@Lokal_Profil is there anything pending here?

In T138517#4193681, @Marostegui wrote:

@Lokal_Profil is there anything pending here?

I honestly cannot remember @JeanFred maybe?

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