Myaql: Master Master replication

I have Master-master replication setup.
I am getting the errors 1032/1062 . knowing that I don't have an auto_increment column key on any table => I didn't set the variables for auto_increment_offset and auto_increment_increment.

I have "log_slave_updates" set in my.cnf  file on both masters

could this ("log_slave_updates" )  be what causes replication to break, since both masters writes to their bin log files and they get updated from each other.

I appreciate your help.

Thanks
CCVOIPAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phil DavidsonCommented:
You may be on to something.  To reduce errors, some people recommend getting the MySQL master database fully backed up.  In a MASTER-MASTER configuration, there can be more problems.  It isn't as cut-and-dried with two master nodes.  I would see if you could reproduce the configuration in a Dev or QA environment.
CCVOIPAuthor Commented:
It is the same problem on Dev and QA environment ( I have the same configuration there too) !
Phil DavidsonCommented:
I would modify my.cnf (my.conf?) file in a Dev environment.

introduce this line:
slave-skip-errors=1062,1032

Bounce the mysql server services

# service mysql restart

Verify if replication starts and works

If you can do testing, you may want to remove the slave-skip-errors=1062,1032 stanza from the configuration file.  Then bounce the services again.  The errors may go away completely yet errors 1062 and 1032 will still alert you in the future.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

CCVOIPAuthor Commented:
Exactly, I have done all of that.

I just want to know if "log_slave_updates" is required in master-master replication?

Thanks
CCVOIPAuthor Commented:
Hi all,

After more digging on the duplicate issue, I found that at some point there was deletes that did not make it to the slave.

I checked if they are logged in "mysql-bin log files" and they were there, but the slave did not pickup the deletes, and when there is another insert for the same unique key, it gets inserted on the master and throw the error of duplicate unique key on the slave.

Any idea why relay-logs (or slave) did not execute the deletes?

Any help would be appreciated.

Thanks
Phil DavidsonCommented:
Possible causes: There is a "BEFORE DELETE" trigger on the slave database.  Maybe there is some no delete constraint on the slave database tables.

Would a redesign of the tables be possible?  A "disabled" column could be used to mark the databases for deletion.  Then you could periodically delete any column that is disabled on both instances.

Can you run this?

SHOW SLAVE STATUS\G

Open in new window


The results may or may not show something relevant (e.g., the relay file isn't working correctly).

I think the log that is getting replicated over isn't from the binary log.  That is normally how replication works.  But the deletes aren't working.

I'm curious what would happen if you purged the binary logs in dev and started over.  I'm also curious if you could truncate the table in the slave in dev and started over.
CCVOIPAuthor Commented:
there is no triggers on the slave side. both hosts are designed in the same way and contains the exact same schema.

SHOW SLAVE STATUS \G

Open in new window

==> usually gives  errors  for a duplicate unique key. and when I tracked it, I found out that those records got deleted from the master but the slave didn't pickup the changes.

I usually bring back the replication by tacking a dump from the active host to slave and then reset the replication parameters.

This issue happened many times with the same behavior, for this I want to track the root cause!!

Note: we have Keepalived installed to manage a switch over to the slave if something should happen on the active host. we noticed that keepalived switches even though mysql on the active host up and running fine, and when the switch happens there is some active session that are left behind(keep in mind that we have active/active replication) . should this be the problem.

Thanks
Phil DavidsonCommented:
Do the deletes happen through manually written SQL statements?  No matter what table they are run on, they never get replicated?  But other inserts and updates do get replicated?  If the deletes happened via a stored procedure, that may indicate that there is a setting to ignore/override the binary log file on the master mysql database.

A potential root cause is that the session wherein a delete statement is issued happens with this setting governing the scope of each and every delete statement you are issuing:

SET SESSION SQL_LOG_BIN = 0

Open in new window


Can you run this command and report the results?

select @@session.sql_log_bin

Open in new window


Can you run this command and report the results (unless they are too sensitive)?

show session variables like 'sql_log_bin';

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.