1

I have a data logger application that puts data into MySQL database. That database has a replica. My data logger is connected to the internet via 4G connection which is unreliable sometimes.

It is not a critical service, I'm using that replica to work with data when the connection over 4G is slow.

Yesterday we had bad weather (snowstorm) and my replication stopped. Error logs on my replica showed that I have duplicate keys. It looks like the replica was able to get data, and insert it, and then something happened (probably 4G connection died for a moment) and the server was not notified about these successful transactions.

I had to stop the replica, manually delete records that were sent again by the master, and start the replica. Fortunately, only two tables are replicated, so it wasn't much work, but I don't want to do this every time when we have bad weather.

Is there some way to avoid situations like this?

I saw somewhere that there is ignore errors option for replica. Could this help? What kind of problems can I have if I turn this on?

Improve this question
5
  • Replication intermittent connections shouldn't cause errors. It should just resume where it left off. Even if the replica crashes, it should remember where it left off because MySQL supports crash-safe replication since MySQL 5.6. See hackmysql.com/post/crash-safe-mysql-replication-a-visual-guide
    – Bill Karwin
    Feb 5 at 22:51
  • Well, thats pretty weird... I will see that link, thanks.
    – Kamil
    Feb 6 at 1:06
  • Is SHOW CREATE TABLE identical between Primary and Replica?
    – Rick James
    Feb 27 at 19:38
  • Does any connection to the Replica do writes?
    – Rick James
    Feb 27 at 19:39
  • @RickJames No. There are no writes on the replica. Replica is used only for Grafana and the account used for this has only SELECT permission.
    – Kamil
    Feb 27 at 20:33

1 Answer 1

Reset to default
1

Some tips that will help:

  • Use ENGINE=InnoDB.
  • Let the Replica restart automatically.
  • If it is down "too long", only do START REPLICA; (Do not use RESET.)
Improve this answer
3
  • I'm using InnoDB. My replica didn't restarted automatically and I have tried START REPLICA; but I still had replication error in SHOW REPLICA STATUS.
    – Kamil
    Feb 6 at 1:05
  • @Kamil - Please add the output from that STATUS to your Question.
    – Rick James
    Feb 27 at 20:44
  • I will do that when it will happen next time.
    – Kamil
    Feb 27 at 21:14

Your Answer

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

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