0

I want to sync data from one table to another(only from 1 column) to another table.

Table A (
   col1 varchar(50) DEFAULT NULL,
   date_in timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
)

Table B (
   col1 varchar(50) DEFAULT NULL,
)

My strategry is to sync data that changed in timeframes of 5 minutes .

I sync with accuracy of 1 sec. Now there can be a situation where I want to sync data since 2020-05-03 00:05:00 until now (2020-05-03 00:10:00) and I receive x rows, but there are a few rows which are inserted to the database with for example date 2020-05-03 00:09:59 but they are not committed at the time I execute a SELECT query, so I miss them.

  • Plan A

Add some grace time, for example 10 seconds to my SELECT query.

  • Plan B

Have another thread which runs once a day and sync the "missed rows".

Are this plans legit? it there a better way of acomplishing this?

0

Plan Sound Cool but Depending on Overload on the Server for every 5 minutes will be huge. not to mention an overkill.

Use Triggers on Bulk or Inserts on The Table.

A trigger in SQL is a procedural code that is automatically executed in response to certain events on a specified table. It is important to understand how these small codes make such a huge difference in database performance.  

what you need is a DML Trigger on table on Each insert or delete.

here's a mention. https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/

1
  • Thats an option, but yet again, if using triggers, then i would preffer inserting to both tables in a single transcation instead
    – omri
    May 4 '20 at 6:18
0

pt-online-schema change does this trick when it copies rows from the original table to the new one. It creates three triggers: on insert, on update and on delete.

Actually, pt-online-schema can even create a copy table and leave it in that state.

pt-online-schema-change \
    --no-drop-triggers \
    --new-table-name actor_copy \
    --no-drop-new-table \
    --no-swap-tables \
    --alter "ENGINE InnoDB" \
    --execute D=sakila,t=actor

After this command the original table:

mysql> desc actor;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| first_name  | varchar(45)          | NO   |     | NULL              |                             |
| last_name   | varchar(45)          | NO   | MUL | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

A copy of the original table:

mysql> desc actor_copy;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| first_name  | varchar(45)          | NO   |     | NULL              |                             |
| last_name   | varchar(45)          | NO   | MUL | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

Now, changes to the original tables are reflected in the copy:

mysql> select * from actor where actor_id = 200;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from actor_copy where actor_id = 200;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> delete from actor where actor_id = 200;
Query OK, 1 row affected (0.01 sec)

mysql> select * from actor_copy where actor_id = 200;
Empty set (0.00 sec)

mysql> INSERT INTO actor VALUES (200, 'THORA', 'TEMPLE', '2006-02-15 04:34:33');
Query OK, 1 row affected (0.00 sec)

mysql> select * from actor where actor_id = 200;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from actor_copy where actor_id = 200;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.01 sec)
2
  • that not solving my situation, pt-online-schema-change are taking a snapshot when you execute it and working on it. My situation is over time, repeatingly copping data from one table to another, and avoiding writing latency
    – omri
    May 4 '20 at 6:17
  • I feel you missed the point. Normally, yes, pt-osc swaps tables and drops triggers. In my example note it doesn’t. You however, asked to mirror only selected fields. pt-osc mirrors all fields. If that doesn’t work for you, you’d need to create corresponding triggers manually. I hope you get the idea.
    – akuzminsky
    May 4 '20 at 13:17

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.