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

multi-masterMySQLreplication

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?

Best Answer

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.

Related Question