Jump to content


Photo

Merging Separate mySQL Databases - Any Suggestions?


  • Please log in to reply
6 replies to this topic

#1 thomas

thomas
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 12 July 2006 - 11:47 AM

hi, i'm working on a php organizer project which includes an address book. it runs on a remote server (at my webhost), coded in php and mySQL.

i'm currently having a little hiccup - a remote server would mean that the organizer would only be accessible if i am connected to the internet. if i'm out on the road with my laptop but without a web connection, the address book data would become inaccessible.

the only workaround i've come up with (its quite stupid, actually) is to have a copy of the system and database running on my laptop, which would make the data accessible on the go. that worked, until I figured that the way i was synchronizing my databases would only allow me to keep my local data updated with remote, but would not allow me to add any data when i am offline, as the sync is one way (remote overwrites local) and whatever additional local information would be lost on overwrite.

is there any way to merge the two databases together?

or does anybody have a better suggestion on how i should program the application so that it can have full functionality both online and offline? ???



#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 July 2006 - 03:37 PM

Synchronization is always an issue if you need offline mode as well; "merging" can be done, but it's tedious... not sure you have much a choice, though.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Wildbug

Wildbug
  • Members
  • PipPipPip
  • Advanced Member
  • 1,149 posts

Posted 12 July 2006 - 03:41 PM

I've never used it, but there is a merge "engine" that allows two identical (same columns & index) tables to be used as one.  It seems like there are alot of restrictions on it, but it might be helpful in your situation if you maintain two tables -- one local, one remote -- and use them as one.

Or maybe the REPLACE command might be useful.  I think that this will work with new or updated data, but one possible problem is when a row has been updated on both databases.

I haven't used either of these personally, and maybe you've already considered and discarded them, but hopefully they'll help point you in the right direction.
Twice a day my clock works PERFECTLY!  I can't figure out what's wrong with it.

#4 cmgmyr

cmgmyr
  • Members
  • PipPipPip
  • Advanced Member
  • 1,278 posts
  • LocationUSA

Posted 12 July 2006 - 03:42 PM

What I uaually do is export the newer database, clear out the old one and import the newer on in the older one, so now they are both the same. It's kind of a pain...but it works and I haven't had any problems doing that. Unfortunately there is no "sync" button for MySQL.

-Chris

#5 thomas

thomas
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 12 July 2006 - 08:29 PM

thanks a million...

i've tried using the REPLACE command but i couldn't coax php into finding the changed fields (it was willing to find what was different, but not so much which one was updated), and the comparison took really long anyway.

the MERGE function looks good (thanks wildbug!), but even the mySQL ref manual "agrees" its really bug-infested.. don't think i would be working on that anytime soon. guess i have to keep the local version read-only for now.

:)


#6 SQL_F1

SQL_F1
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 12 July 2006 - 11:31 PM

I'm working with the REPLACE command right now.

Without using Replication, the best case solution is to restrict updates to one addressbook OR the other but not both.

When updating the addressbook that was "read-only", you could use a process such as:

1) Copy/dump the addressbook table to the target server as, say, sourceAddressBook.
2) execute a query such as :
REPLACE INTO addressBook (ID, firstName, lastName, homeTel )
SELECT ID, firstName, lastName, homeTel 
  FROM  sourceAddressBook
ON DUPLICATE KEY UPDATE
     ID = VALUES(ID), 
     firstName = VALUES(firstName), 
     lastName = VALUES(lastName), 
     homeTel  = VALUES(homeTel);

This assumes a primary (unique) key on ID.
So, if any record changed, it would update that record but if a new record were created, it would be inserted.

If you included a TIMESTAMP column  then you could limit the records copied from the modified database to those inserted/updated after a given TS.

Brian

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 July 2006 - 10:05 PM

Not a bad idea... if both are read/write, then even timestamps don't help much.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users