[debian-mysql] Bug#929707: mariadb-client-10.1: import of "mysqldump --all-databases" fails with "ERROR 1062 (23000): Duplicate entry"

[Matija Nalis]

Package: mariadb-client-10.1
Version: 10.1.38-0+deb9u1
Severity: normal

Dear Maintainer,

Doing "mysqldump --all-databases" on one mariadb 10.1 instance,
and trying to import it on another mariadb 10.1 instance fails with:

I would expect it to suceed without errors (like it did all the time in mysql in Jessie)

I traced it down and it happens if dump contains one db with tables with indexes, and mysql db.
machine1# mysqldump -uroot --max_allowed_packet=2147483648 --hex-blob --lock-all-tables --master-data --flush-privileges --databases video1 mysql > backup.sql
machine2# mysql < backup.sql
ERROR 1062 (23000) at line 796: Duplicate entry 'video1-test2-PRIMARY-n_diff_pfx01' for key 'PRIMARY'

Digging deeper it seems that bug happens when mysql tryies to restore mysql.innodb_index_stats table

I've found two workarounds:

1) modify order of databases, so mysql table is backed up (and restored) first, 
   and all other later; eg "mysqldump  ... --databases mysql video1"

2) before starting mysql for import, do "SET global innodb_stats_persistent=0" 
   (and restore it afterwards)

Perhaps one of those (or some other) workaround should be implemented
automatically (at least when using "mysqldump --flush-privileges" and/or
"--all-databases" which implies backup/import of mysql DB)? 

I would assume backup up and restoring whole mariadb server is one of
relatively common operations which should not involve such debugging.


-- System Information:
Debian Release: 9.9
  APT prefers stable-updates
  APT policy: (500, 'stable-updates'), (500, 'stable')
Architecture: amd64 (x86_64)

Kernel: Linux 4.9.0-9-amd64 (SMP w/24 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8), LANGUAGE=en_US:en (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Init: sysvinit (via /sbin/init)

Versions of packages mariadb-client-10.1 depends on:
ii  debianutils               4.8.1.1
ii  libaio1                   0.3.110-3
ii  libc6                     2.24-11+deb9u4
ii  libconfig-inifiles-perl   2.94-1
ii  libjemalloc1              3.6.0-9.1
ii  libstdc++6                6.3.0-18+deb9u1
ii  libsystemd0               232-25+deb9u11
ii  mariadb-client-core-10.1  10.1.38-0+deb9u1
ii  perl                      5.24.1-3+deb9u5
ii  zlib1g                    1:1.2.8.dfsg-5

Versions of packages mariadb-client-10.1 recommends:
ii  libdbd-mysql-perl     4.041-2
ii  libdbi-perl           1.636-1+b1
ii  libterm-readkey-perl  2.37-1

mariadb-client-10.1 suggests no packages.

-- no debconf information