Fail to replicate if a table has more than one primary/unique key

[NoyException]

For example, I creates a table like this

create table account(
  id int, 
  name char(16) unique, 
  balance float, 
  primary key(id)
);
insert into account values(12500000, "name0", 514.35);

Then when I try to replica this from MariaDB to MyDuck, an error occurs:

ERROR: TGT: [worker000]: test@account@@0.tsv: MySQL Error 1105 (HY000): Binder Error: Conflict target has to be provided for a D0 UPDATE operation when the table has multiple UNIQUE/PRIMARY KEY constraints: LOAD DATA LOCAL INFILE 'memory/test@account@@0.tsv' REPLACE INTO TABLE `test`. `account` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '   ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`id`, `name`, `balance` )

This error disappeared after removing unique.

[fanyang01]

Does this occur in the invocation of mysqlsh? If so, can we bypass it by using the deferTableIndexes option? https://dev.mysql.com/doc/mysql-shell/8.4/en/mysql-shell-utils-copy.html#:~:text=restrictions%20and%20configuration.-,deferTableIndexes,-%3A%20%5B%20off%20%7C%20fulltext%20%7C%20all

[NoyException]

Does this occur in the invocation of mysqlsh? If so, can we bypass it by using the deferTableIndexes option? https://dev.mysql.com/doc/mysql-shell/8.4/en/mysql-shell-utils-copy.html#:~:text=restrictions%20and%20configuration.-,deferTableIndexes,-%3A%20%5B%20off%20%7C%20fulltext%20%7C%20all

Yes it happens when executing this in ./devtools/replica-setup/snapshot.sh:

output=$(mysqlsh -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -- util copy-instance "mysql://${MYDUCK_USER}:${MYDUCK_PASSWORD}@${MYDUCK_HOST}:${MYDUCK_PORT}" --users false --consistent false --ignore-existing-objects true --handle-grant-errors ignore --threads $THREAD_COUNT --bytesPerChunk 256M --ignore-version true)

And the error still occurs no matter adding --defer-table-indexes all or --defer-table-indexes fulltext.

[fanyang01]

Interesting. Maybe we should investigate why --defer-table-indexes all isn't working. Is there anything noteworthy in the MySQL Shell log?

[NoyException]

Interesting. Maybe we should investigate why --defer-table-indexes all isn't working. Is there anything noteworthy in the MySQL Shell log?

Still the same:

ERROR: TGT: [Worker007]: account_test@account@@0.tsv: MySQL Error 1105 (HY000): Binder Error: Conflict target has to be provided for a DO UPDATE operation when the tabl
e has multiple UNIQUE/PRIMARY KEY constraints: LOAD DATA LOCAL INFILE 'memory/account_test@account@@0.tsv' REPLACE INTO TABLE `account_test`.`account` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '   ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`id`, `name`, `balance`)
ERROR: TGT: Aborting load...

There's no other error reported in the log, but three warnings here may provide some clues.

WARNING: SRC: Failed to fetch value of @@GLOBAL.GTID_EXECUTED.
WARNING: SRC: The dumped value of gtid_executed is not guaranteed to be consistent
WARNING: TGT: Destination MySQL version is newer than the one where the dump was created. Source and destination have non-consecutive major MySQL versions. The 'ignoreVersion' option is enabled, so loading anyway.

[fanyang01]

You can run ./myduckserver --loglevel 6 to observe whether the secondary index creation is actually defered. If not, you can search deferTableIndexes in MySQL Shell's source code to find why it does not take effect.