Saturday, March 01, 2008

How to sync two tables in MySQL

A question pops out quite often on Devshed forums, "How do I keep table x of my local database in sync with a remote copy?"
Usually replication is the suggested answer, but it might be a little overkill, think of those who just want to push the new product catalogue from a local server to their hosted site? No permanent connection and so on ...
In this case the right tool might be a mix of the new MySQL features, federated tables, extended insert synthax, stored procedures, events, triggers ... quite a fest.
Say you have a local catalogue table which holds the products on sale:

  • CREATE TABLE `test`.`catalogue` (
  • `product_code` CHAR(10) NOT NULL,
  • `product_name` VARCHAR(45) NOT NULL,
  • `product_desc` VARCHAR(500) NOT NULL,
  • `product_weight` DECIMAL NOT NULL,
  • `product_colour` VARCHAR(45) NOT NULL,
  • PRIMARY KEY (`product_code`)
  • )


  • Load it with a sample row

    1. INSERT INTO
    2. catalogue
    3. VALUES
    4. ('AA12F', 'Sm. Widget', 'This is a small widget', 5, 'Red');

    In this sample I'll suppose that a similar table exists in the remote database, no need to check.

    First of all we need to be able to issue queries against that table right from this server, we'll use federated tables:

  • DROP TABLE IF EXISTS `test`.`remote_catalogue`;
  • CREATE TABLE `test`.`remote_catalogue` (
  • `product_code` CHAR(10) NOT NULL,
  • `product_name` varchar(45) NOT NULL,
  • `product_desc` varchar(500) NOT NULL,
  • `product_weight` decimal(10,0) NOT NULL,
  • `product_colour` varchar(45) NOT NULL,
  • PRIMARY KEY (`product_code`)
  • )
  • ENGINE=FEDERATED
  • DEFAULT CHARSET=latin1
  • CONNECTION='mysql://root:nt300jk@127.0.0.1:3306/remote_test/catalogue';
  • --don't use a privileged user in real life!!!

  • Now we have our federated table pointing to the remote server, let's set up the most basic sync, one query will be enough

    REPLACE INTO remote_catalogue SELECT * FROM catalogue 


    A quick test:

    mysql> use remote_test
    Database changed
    mysql> select * from catalogue;
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    | product_code | product_name | product_desc | product_weight | produc
    t_colour |
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    | AA12F | Sm. Widget | This is a small widget | 5 | Red
    |
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    1 row in set (0.01 sec)

    mysql>

    After a while data in our local database changes, a new product is added and the existing one is updated

    1. INSERT INTO
    2. catalogue
    3. VALUES
    4. ('AB12G', 'Lg. Widget', 'A large widget', 34, 'blue');
    5. UPDATE
    6. catalogue
    7. SET
    8. product_weight = 4
    9. WHERE
    10. product_code = 'AA12F';

    we are ready to push the changes to remote site with the query used before, reissueing the REPLACE leads to this on the remote server

    mysql> select * from catalogue;
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    | product_code | product_name | product_desc | product_weight | produc
    t_colour |
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    | AA12F | Sm. Widget | This is a small widget | 4 | Red
    |
    | AB12G | Lg. Widget | A large widget | 34 | blue
    |
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    2 rows in set (0.00 sec)

    So now the two tables hold the same values!
    Enough for now! I'll show you the rest later

    BTW, while writing this I filed two bug reports (34973 and 34971) hope those will turn into errors on my side ...

    MySQL's support guys took care of the bug reports, the outcome is:

    Bug #34973 is not a bug (it's a duplicate of closed bug 25511 which says that's it's fine for INSERT ... SELECT ... ON DUPLICATE KEY UPDATE to fail with federated tables), however bug 25511 suggests that REPLACE doesn't work too, but, as you can see it works fine.

    Bug #34971 is verified, from Miguel's comments it looks like it's fixed in 5.1.24 (not released yet)

    1 comment:

    MySQL said...
    This comment has been removed by a blog administrator.