Welcome, Guest. Please login or register.

Author Topic: Keeping two databases synchronised  (Read 1426 times)

Description:

0 Members and 1 Guest are viewing this topic.

Offline motorollinTopic starter

  • Hero Member
  • *****
  • Join Date: Nov 2005
  • Posts: 8669
    • Show only replies by motorollin
Keeping two databases synchronised
« on: November 04, 2009, 05:31:55 PM »
Hi all

I needed an app to help me manage my (ever-expanding) to-do list, but none of the existing apps quite worked how I wanted. So I wrote my own. It currently runs off the MySQL database on my MacBook, which is fine.

I also want to write a web front-end so I can view and manage my to-do list on my iPhone. This means I need to host a copy of the database on my Web host's MySQL server, but I also want to keep the local copy on my MacBook so I can still use the application when I don't have an Internet connection.

So, I'm looking for ideas on how to keep the data synchronised. Specifically, I'm having trouble working out how the rows would be matched in order to compare them and then update one or the other. Perhaps an example would help. My table has 5 columns:

id (integer, primary key)
itemname (varchar)
duedate (integer)
note (varchar)
lastmodified (datetime)

Lets say that both copies of the database contain identical data comprising 5 rows. On my MacBook, I add one item (id=6). Later, on my iPhone, I add a different item (id=6 again). When I next go online with my MacBook, the two new entries both have the same value for id, but the one created on the iPhone is more recent, so would overwrite the one created on the MacBook. In actual fact, I want to keep them both.

Any thoughts on how I could get around this?
Code: [Select]
10  IT\'S THE FINAL COUNTDOWN
20  FOR C = 1 TO 2
30     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NAAAA
40     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NA-NA-NAAAAA
50  NEXT C
60  NA-NA-NAAAA
70  NA-NA NA-NA-NA-NA-NAAAA NAAA-NAAAAAAAAAAA
80  GOTO 10
 

Offline Karlos

  • Sockologist
  • Global Moderator
  • Hero Member
  • *****
  • Join Date: Nov 2002
  • Posts: 16867
  • Country: gb
  • Thanked: 4 times
    • Show only replies by Karlos
Re: Keeping two databases synchronised
« Reply #1 on: November 04, 2009, 06:33:47 PM »
Sounds like you need MySQL cluster :lol:

Seriously though, don't ever run two concurrent modifiable instances of the same database if you intend to synchronise them, it really isn't a good idea.

In fact, what you could to do is forego the primary key alltogether on these tables and rely instead on a non-unique index. You might capture some other data (an identifier for the source of the row, eg your phone, macbook etc) that in conjunction with the non-unique index is used as a unique index.
int p; // A
 

Offline motorollinTopic starter

  • Hero Member
  • *****
  • Join Date: Nov 2005
  • Posts: 8669
    • Show only replies by motorollin
Re: Keeping two databases synchronised
« Reply #2 on: November 04, 2009, 06:54:53 PM »
Quote from: Karlos;528365
Sounds like you need MySQL cluster :lol:

Overkill? ;)

Quote from: Karlos;528365
Seriously though, don't ever run two concurrent modifiable instances of the same database if you intend to synchronise them, it really isn't a good idea.

I'm assuming it's at least possible, since this happens all the time with things like PDAs, which can have calendar data on both the PDA and the user's computer and then synchronise them. Maybe they use the solution you posted below...

Quote from: Karlos;528365
In fact, what you could to do is forego the primary key alltogether on these tables and rely instead on a non-unique index. You might capture some other data (an identifier for the source of the row, eg your phone, macbook etc) that in conjunction with the non-unique index is used as a unique index.

That could work. I need to work through an example to make sure I understand the logic behind it. Would you then store a log of all of the MySQL queries carried out, and then run them on the other database when you sync?
Code: [Select]
10  IT\'S THE FINAL COUNTDOWN
20  FOR C = 1 TO 2
30     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NAAAA
40     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NA-NA-NAAAAA
50  NEXT C
60  NA-NA-NAAAA
70  NA-NA NA-NA-NA-NA-NAAAA NAAA-NAAAAAAAAAAA
80  GOTO 10