2

Recently We meet a mysql error on slave, because a transaction can not be executed on slave.

We found two records have the same unique in master, but inserted successfully.

I have post two screenshot of my situation, Could you give me any clues about how can this be happened?

enter image description here enter image description here

Improve this question
1

2 Answers 2

Reset to default
3

My thought is that the constraints were disabled during the load. This allows for faster insert of data but can cause issues like this.

If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.

Improve this answer
1
  • Even more, all indexes can be disabled at once by ALTER TABLE 'mytable' DISABLE KEYS for significant speedup. mysqldump has an option to insert disable/enable statements around the INSERTs.
    – Kondybas
    Commented Sep 2, 2018 at 15:31
0

(Diff issue) Here's a guess as to why the second SELECT shows only one row...

The optimizer notices when a column (or set of columns) is declared UNIQUE. In that case it can stop scanning rows after the first one it finds.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.