Results 1 to 4 of 4

Thread: "Duplicate Entry" error restoring first incremental backup

  1. #1
    Join Date
    Feb 2014
    Posts
    1

    Default "Duplicate Entry" error restoring first incremental backup

    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:

    Code:
    140213  9:52:34	27931 Connect	zrmuser@localhost.localdomain on 
    		27931 Query	show /*!40003 GLOBAL */ variables
    		27931 Quit	
    		27932 Connect	zrmuser@localhost.localdomain 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	zrmuser@localhost.localdomain on 
    		27933 Query	select @@version_comment limit 1
    		27933 Query	show master status
    		27933 Quit	
    		27937 Connect	zrmuser@localhost.localdomain 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:

    Code:
    140213 10:22:56	30666 Connect	root@localhost.localdomain 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
    Last edited by Paolino; February 13th, 2014 at 01:23 AM.

  2. #2

    Default

    i have the error too, but reinstall and do again it working

  3. #3

    Default

    Even I faced the same problem,thanks for your information it helped me...

  4. #4

    Default

    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 [url=http://www.learnalanguage.com/learn-spanish/]learn a language - learn spanish[/url] dumps and tutorials with [url=http://www.learnalanguage.com/learn-english/]learnalanguage[/url] exam pass guarantee. Download our latest test king ccie [url=http://www.babbel.com/]Babbel[/url] questions to pass real exam of mcitp training in [url=http://www.learnamericanenglishonline.com/]Learnamericanenglishonline[/url]
    Last edited by Atifreree; May 4th, 2015 at 10:35 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •