MySQL Bugs: #71814: persistent stats activity conflicts with mysqldump import of same info.

Bug #71814	persistent stats activity conflicts with mysqldump import of same info.
Submitted:	24 Feb 2014 7:18	Modified:	2 Mar 2016 3:45
Reporter:	Ramiro Cavalcanti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.6.10, 5.6.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,
i'm trying to setup a SLAVE server at Amazon EC2.

I get mysqldump from Master server without any problem.

I also could restore this dump at 2 differents servers and created 2 SLAVE's without any trouble.

One slave is a virtualized CentOS 6.5 64 Bits at my office.
The other slave is a baremetal CentOS 5.10 32 Bits at home.

All these tests were performed by the same mysql version.

The problem with the generated dump ONLY occurs with CentOS at Amazon EC2.

/// Master Server:
Virtualized with XenServer
CentOS 6.5 64 Bits
MySQL 5.6.10

/// Slave Server at Amazon EC2:
CentOS 6.5 64 Bits
Tryed: MySQL 5.6.10 and 5.6.16

How to repeat:
Make a mysqldump with table lock, events and routinges to prepare to setup a SLAVE server.

Then, start the slave with --skip-slave-start and try to import the dump.

MySQL imports all my 362 databases.

The error occur when it tries to import "mysql" database.

It shows:

ERROR 2013 (HY000) at line 2731895: Lost connection to MySQL server during query

or something like:

Duplicate 'PRIMARY-n_diff_pfx01' for key 'primary' for database 'xxxx98'.

I found that this "n_diff_pfx01" is located at "mysql.innodb_index_stats".

I searched for duplicated rows there. But everythings seems to be fine.

Suggested fix:
To fix (im unsure if it fixed):

I've got the whole "dump.sql" (2 Gb) after the error occurs.

Then i edited this and removed all my personal databases inserts.

Just left at this file the import for system databases, like "mysql" database and schemas.

I ran this partial dump again, and it went ok.

mysql -u root -S /tmp/mysql_agoraos.sock < all_novo.sql

ERROR 1062 (23000) at line 2870584: Duplicate entry 'agoraosdb_db98-equipamento-PRIMARY-n_diff_pfx01' for key 'PRIMARY'

Another thing is.

Everytime i run this dump, it shows the same error for this database number 98, but in a different table of it.

Line 2870584 is:
INSERT INTO `innodb_index_stats` VALUES ('agoraosdb_db89','finan_classificacao','PRIMARY','2013-05-06 00:47:13','n_diff_pfx01',4,1,'id'),('agoraosdb_db89','finan_classificacao','PRIMARY','2013-05-06 00:47:13','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('agoraosdb_db89','finan_classificacao','PRIMARY','2013-05-06 00:47:13','size',1,NULL,'Number of pages in the index'),('agoraosdb_db89','finan_classificacao','thread_class_gerencial','2013-05-06 00:47:13','n_diff_pfx01',1,1,'thread_class_gerencial'),('agoraosdb_db89','finan_classificacao','thread_class_gerencial','2013-05-06 00:47:13','n_diff_pfx02',4,1,'thread_class_gerencial,id'),('agoraosdb_db89','finan_classificacao','thread_class_gerencial','2013-05-06 00:47:13','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('agoraosdb_db89','finan_classificacao','thread_class_gerencial','2013-05-06 00:47:13','size',1,NULL,'Number of pages in the index'),('agoraosdb_db89','finan_classificacao','thread_grupo_finan','2013-05-06 00:47:13','n_diff_pfx01',2,1,'thread_grupo_finan')... and it goes.

I have DROPped database 'agoraosdb_db98' from Master.

Then, made mysqldump again and tried to import. And, guess what.

Warning: Using a password on the command line interface can be insecure.
 ERROR 1062 (23000) at line 12449729: Duplicate entry 'agoraosdb_db93-usuario_email-PRIMARY-n_diff_pfx01' for key 'PRIMARY'

Now it shows error at 'agoraosdb_db93', that was fine before.

Im getting crazy with this.

Another news.

I get the dump that mysql says that has erros and made this and split it in 3 different files. Each file with it's own headers and bottom (to disable foregin, etc... from the original dump).

3 Files:

1. Has all my databases, with routines and events (363 total)

2. Has all views for my databases (363 total * 3 views)

3. Has 'mysql' database.

---

Im imported following the sequence above.

1. No errors reported (all my databases were imported)
2. No errors reported (all my views were imported)
3. No errors reported ('mysql' database, users permissons, etc were imported).

So, can anyone explain me why is this happing?

If i try to import all this 3 steps together, step 3 fails (the original dump file).

Thank you for the report.

Most likely this is not a bug, but either issue with network connection to EC machine (if you run client locally) or small (for your data) max_allowed_packet. To consider this as a bug in MySQL code we need repeatable test case: dump which causes wrong behavior and MySQL server options from the EC machine.

I will verify this.  The problem happens in this scenario for me:
Persistent innodb statistics are on, and the autorecalc thread available (by default)

o)  there is existing select workload running on server.
o)  i'm importing a mysqldump of all databases.

The problem is a "race condition" between the importing of mysql database and the background innodb stats thread.

Dumpfile has :

DROP TABLE IF EXISTS `innodb_index_stats`;
..
CREATE TABLE `innodb_index_stats` (
...
LOCK TABLES `innodb_index_stats` WRITE;
...
INSERT INTO `innodb_index_stats` VALUES (

On a busy server,  persistent or background stats thread can insert rows between the CREATE and LOCK TABLE of the dump!!

Another likely way to hit this is if user did mysqldump with --skip-lock-tables.

As a workaround, you can first disable this stats before importing:

SET GLOBAL innodb_stats_auto_recalc=0;
SET GLOBAL innodb_stats_persistent=0;

Personally, I don't like that mysqldump dumps the content of these tables that should be auto-generated.

.

Attachment: bug71814_some_infos.txt (text/plain), 1.69 KiB.

Hi Shane Bester,
you were correct.

I've made the workaround you suggested and it worked.

Since the report on 2014, i've doing mysqldump for all databases and individual dumps also. Now i know how to restore the all databases dump when this erro occurs.

Thanks!