How could duplicate entry on unique key happen if only one master is active?

Question

Say, I we have master-master replication with only one master active at a time.

We have users table:

create table users (
 id int auto_increment,
 email varchar(255),

 primary key id(id),
 unique key email(email)
);

and web application cluster which inserts new record for new users.

Now we got error which stopped replication:

Error 'Duplicate entry 'something@example.com-' for key 'email'' on query. Default database: 'xxx'. Query: 'INSERT INTO users  ...

Question is: is that true that this error in this setup is impossible, and probable reason of error is that we had two masters active at same time?

Answer 1

Do not see the problem. When somebody enters the email address a second time then this happens. Nothing to do with replication. This is what the unique key is used for.

If this error would break the replication then I do not understand how replication could ever work. Every user could easily break the replication by, in this case, entering an email address 2 times.

I am not an MySQL expert but I think that the doc relates to errors other then maintaining data integrity. Perhaps somebody with more in-depth knowledge of clustering can help you on how to configure master-master clustering.