1

I have setup a master-slave replication in MySql. I have added additional columns to one of the tables in the slave database in order to store some additional information, which should not be an issue, since it is inline with the following rules: https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html

Now the issue that I am facing is that when I fill these additional columns, the data I fill them with is being lost once replication happens. By lost I mean that the data in these additional columns are reset to their default value.

Should this happen? I have not found anywhere where this behavior is documented and I can't understand why this is happening.

Does anybody can shed any light on this?

Thank you very much.

  • Are you using RBR or SBR? Are you using UPDATE or REPLACE or something else? – Rick James Feb 1 '17 at 16:01
  • To be honest I don't know which type of replication I am using, I have followed this guide in order to implement it: link, which does not mention anything regarding RBR or SBR. Yes I am using an update statement in order to update these additional columns only. Thanks – user32622 Feb 3 '17 at 9:23
  • Is there something coming through replication that modifies the row? That's the likely cause of the problem. – Rick James Feb 3 '17 at 18:24
  • Could be, I have finally went for the workaround you have suggested. Thanks for your help buddy, really appreciate it. – user32622 Feb 6 '17 at 8:55
1

If the table definitions are different, you are asking for trouble and need to be careful.

In particular, if replication is using Row Based Replication, I suspect the missing columns would get defaults, but I would not trust it.

If you are writing to the Slave, you are asking for trouble.

But this may be a workaround -- instead of having extra columns, have an extra table for the extra columns. The two table would have the the same PRIMARY KEY, except that the extra table would not say AUTO_INCREMENT.

  • Why if the table definitions are different I am asking for trouble? In the following document it states clearly that it is possible if you follow the rules stated: dev.mysql.com/doc/refman/5.7/en/… ... Also I am not inserting new records in the slave, I am just updating these additional columns, which I don't think it should be an issue because then what is the point of being able to have additional columns in the first place. Also I have setup my replication following this guide: link – user32622 Feb 1 '17 at 9:51
  • "Trouble" occurs for those who don't read that page. ;) – Rick James Apr 26 '18 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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