PDA

View Full Version : "Duplicate Entry" error restoring first incremental backup



Paolino
February 13th, 2014, 12:57 AM
When restoring the first incremental backup, we have a duplicate entry error.

We have checked the contents of the backup files and we comfirm that some entries in the full backup are present also in the first incremental backup.

Here is the query log taken during the backup:



140213 9:52:34 27931 Connect [email protected] on
27931 Query show /*!40003 GLOBAL */ variables
27931 Quit
27932 Connect [email protected] on
27932 Refresh
/usr/sbin/mysqld, Version: 5.5.34-31.1-log (Percona XtraDB Cluster (GPL), Release 31.1, wsrep_25.9.r3928). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
140213 9:52:35 27932 Quit
27933 Connect [email protected] on
27933 Query select @@version_comment limit 1
27933 Query show master status
27933 Quit
27937 Connect [email protected] on
27937 Query SELECT SQL_NO_FCACHE NOW()
27937 Query /*!40100 SET @@SQL_MODE='' */
27937 Query /*!40103 SET TIME_ZONE='+00:00' */
27937 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
27937 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
27937 Query UNLOCK TABLES
27937 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('xxx'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
27937 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('xxx')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
27937 Init DB xxx
27937 Query SHOW CREATE DATABASE IF NOT EXISTS `xxx`
27937 Query show tables
27937 Query show table status like 'xxx'
27937 Query SET SQL_QUOTE_SHOW_CREATE=1
27937 Query SET SESSION character_set_results = 'binary'
27937 Query show create table `xxx`
27937 Query SET SESSION character_set_results = 'utf8'
27937 Query show fields from `xxx`
27937 Query SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `xxx`
27937 Query SET SESSION character_set_results = 'binary'
27937 Query use `xxx`
27937 Query select @@collation_database
27937 Query SHOW TRIGGERS LIKE 'xxx'
27937 Query SET SESSION character_set_results = 'utf8'
27937 Quit


I think there is a possible bug in the backup phases.

From the query log it seems that the Refresh (FLUSH LOGS) command is executed before the START TRANSACTION command.

So it can happen that some writes in tables are performed in the instants between FLUSH LOGS and START TRANSACTION.

These writes will finish in the binlog used for the first incremental backup breaking the restore.

In fact, we have encountered the problem in a logging table that has a high write frequency.

The START TRANSACTION and FLUSH LOGS commands should be executed inside a global FLUSH TABLES WITH READ LOCK to ensure backup consinstency.

This is exactly what mysqldump does when called with: mysqldump --single-transaction --flush-logs:



140213 10:22:56 30666 Connect [email protected] on
30666 Query SELECT SQL_NO_FCACHE NOW()
30666 Query /*!40100 SET @@SQL_MODE='' */
30666 Query /*!40103 SET TIME_ZONE='+00:00' */
30666 Query FLUSH TABLES
30666 Query FLUSH TABLES WITH READ LOCK
30666 Refresh
/usr/sbin/mysqld, Version: 5.5.34-31.1-log (Percona XtraDB Cluster (GPL), Release 31.1, wsrep_25.9.r3928). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
30666 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
30666 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
30666 Query UNLOCK TABLES
30666 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('xxx'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
30666 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('xxx')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
30666 Init DB xxx
30666 Query SHOW CREATE DATABASE IF NOT EXISTS `xxx`
30666 Query show tables
30666 Query show table status like 'xxx'
30666 Query SET SQL_QUOTE_SHOW_CREATE=1
30666 Query SET SESSION character_set_results = 'binary'
30666 Query show create table `xxx`
30666 Query SET SESSION character_set_results = 'utf8'
30666 Query show fields from `xxx`
30666 Query SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `xxx`
30666 Query SET SESSION character_set_results = 'binary'
30666 Query use `xxx`
30666 Query select @@collation_database
30666 Query SHOW TRIGGERS LIKE 'xxx'
30666 Query SET SESSION character_set_results = 'utf8'
30666 Quit

thienthansanga
August 9th, 2014, 01:39 AM
i have the error too, but reinstall and do again it working :D

johnniewalk
December 30th, 2014, 03:35 AM
Even I faced the same problem,thanks for your information it helped me...

Atifreree
April 11th, 2015, 04:13 AM
Hi All,

I have Amanda server 2.6.1p2 installed on a CentOS 6.5 server. I have the other CentOS clients configured and amcheck checks out OK. However, a Windows Server 2008 R2 with zmanda 2.6.1p2 fails the amcheck. It returns Server validation Failed. Please register server with client.

I have elevated the log to level 5 and below is the log (broken into three posts):








_____________________
We are the pioneers in learn a language - learn spanish (http://www.learnalanguage.com/learn-spanish/) dumps and tutorials with learnalanguage (http://www.learnalanguage.com/learn-english/) exam pass guarantee. Download our latest test king ccie Babbel (http://www.babbel.com/) questions to pass real exam of mcitp training in Learnamericanenglishonline (http://www.learnamericanenglishonline.com/)