MySQL Bugs: #57782: ndb_restore does not warn of possible duplicate keys when using UNIQUE indexes

Bug #57782	ndb_restore does not warn of possible duplicate keys when using UNIQUE indexes
Submitted:	27 Oct 2010 19:32	Modified:	19 Jan 2016 11:20
Reporter:	Daniel Smythe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-7.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	duplicate key, ndb_restore 7.1.6, unique

Description:
When using ndb_restore with unique keys in the table schema, it is possible to get duplicate key errors.

The facts are :
1) Each table fragment generates 'events' when rows are updated
2) These events from different fragments are combined when written to the Binlog or backup log file
3) It is guaranteed that all events for epoch n will be seen before any event for epoch n+1
4) Within an epoch, all events for a given primary key will be logged in the order they occurred.
5) Events for different primary keys can be logged in different orders than they 'occurred'.

As such, it is recommended to use the --disable-indexes and --rebuild-indexes options when processing backup files with schema that contains UNIQUE keys.

However, when restoring large data sets which can take hours to restore, if the --disable-indexes or --rebuild-indexes are not used, it is possible for the whole backup to abort due to 'duplicate keys'. This is frustrating and misleading, especially if the operator does not know about these options.

How to repeat:
Simple) Backup and restore an ndb table with UNIQUE keys. No warnings are printed.

Long) Run a debug version of cluster and execute the following:

#!/bin/bash

mysql -NBe "DROP TABLE IF EXISTS test.t1"
mysql -NBe "CREATE TABLE test.t1 ( a INT PRIMARY KEY NOT NULL, b INT NULL, UNIQUE KEY(b)) ENGINE=ndb"

for i in `seq 1 1000`; do
mysql -NBe "INSERT INTO test.t1 (a,b) VALUES ($i, $i)"
done

# To allow the backup to give time for logged queries.
ndb_mgm -e "ALL ERROR 10041"
ndb_mgm -e "START BACKUP $$" &

for i in `seq 1 1000`; do
mysql -NBe "UPDATE test.t1 SET b=b-1 ORDER BY b"
done

Attempt to restore this backup and it will fail with duplicate key errors.

Suggested fix:
Short) ndb_restore should check the schema which it is restoring first, and if it is found that there are UNIQUE indexes, ndb_restore should abort and demand usage of --disable-indexes and --rebuild-indexes, to prevent these errors and whole restoration failure, resulting in much wasted time.

Long) Improve ordering of events in logs generated to ensure causal dependencies are not violated.

Documented fix in the NDB 7.3.11 and 7.4.8 changelogs as follows:

    When ndb_restore was run without --disable-indexes or --rebuild-indexes
    on a table having a unique index, it was possible for rows to be
    restored in an order that resulted in duplicate values, causing it to
    fail with duplicate key errors. Running ndb_restore on such a table
    now requires using at least one of these options; failing to do so now
    results in an error.

Closed.

Also noted the change in the program's description in the NDB 7.3/7.4 documentation.

This fix was reverted in NDB 7.3.12 and 7.4.9.