syncing two mysql db table

Question

So, I have two mysql db tables: "table_original" and "table_copy"

Here is my current setup:

Every night, table_original gets updated from another server with information. It either removes the old db row or adds a new line it with unique post_id (there won't be no two identical post_id even if row is removed).

Then, certain information along with the post_id is copied into table_copy. Thus two are linked by the post_id.

So far, the table_original has ~20,000 rows while table_copy has ~40,000.

I just noticed that table_copy has not been being updated from the first table but only have been adding rows instead of removing anything that was removed from the first table.

In order to sync them, my initial approach was to check each row from table_original against the table_copy then if post_id exists, then do nothing, if not, remove the row from the table_copy.

My concern is that, the table_original will get updated every night: either older post_id row will be removed and/or add new rows. Unfortunately there is no way for me to know what is being done before it is done. Meaning, I have to wait till the db is updated to see what was removed and added.

Then, every time the first table is updated, then I have to check every rows against the second table to update them. The table will only get bigger and I am concerned that this approach might not be the best.

What do you guys suggest that I can do?

Thanks!

put a trigger on the "original" table to set a flag somewhere else. That way when the updates come in, that flag will go up, and you can detect that elsewhere. when you're doing with your "post processing", you reset the flag. — Marc B
– Marc B, Commented Oct 6, 2016 at 21:59
Hmm, I am not sure if I fully understand what you meant by it. But thank you for the reply. I will see if I can elaborate on it. Thanks. — Steve Kim
– Steve Kim, Commented Oct 6, 2016 at 22:12

Bill Karwin · Accepted Answer · 2016-10-07 00:10:07Z

4

To delete the rows in table_copy that are not present in table_original:

DELETE t1.* FROM table_copy AS t1 
LEFT OUTER JOIN table_original AS t2 USING (post_id)
WHERE t2.post_id IS NULL;

I recommend you create a dummy table with a few rows so you can experiment with this kind of query and increase your confidence with it before you run it against your real data!

See http://dev.mysql.com/doc/refman/5.7/en/delete.html for more docs on the multi-table DELETE statement.

To keep them in sync every night automatically, use triggers:

CREATE TRIGGER copy_on_ins AFTER INSERT ON table_original
FOR EACH ROW
  INSERT INTO table_copy SET post_id = NEW.post_id, other_columns = NEW.other_column;

CREATE TRIGGER copy_on_upd AFTER UPDATE ON table_original
FOR EACH ROW
  UPDATE table_copy SET other_column = NEW.other_column
  WHERE post_id = NEW.post_id;

CREATE TRIGGER copy_on_del AFTER DELETE ON table_original
FOR EACH ROW
  DELETE FROM table_copy WHERE post_id = OLD.post_id;

See http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

Re your comment:

Knowing which post_ids were deleted is tricky. Since they are no longer in the database, you'd either need to:

Infer that they were once there, if you find a gap between post_id values that are still in the database. But this is no guarantee, because id values may never be used if an insert fails.
Check a log of some kind. Some people use a trigger to append to an audit table. Or else code their application that deletes posts to create a log.
Read through deletion events using the mysqlbinlog tool. This is kind of advanced.

edited Oct 7, 2016 at 0:10

answered Oct 6, 2016 at 22:14

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steve Kim Over a year ago

Thank you for the through description. Much appreciate it ! =)

Steve Kim Over a year ago

Just a quick question. Is there a way for me to know what post_id rows were deleted? (So I can do further clean up such as file deletion with the same post_id) Thanks!

Collectives™ on Stack Overflow

syncing two mysql db table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related