Syncronizing MySQL database between Server and Client

This may well be a MySQL issue versus XoJo; however, I was wondering how some of you that use MySQL and have Server to Client syncro going on have accomplished this.

I have set mine up using instructions in the MySQL documentation and it does seem to work fairly well. A user updates a record on the server and that change is replicated (or sync’d) back to the clients immediately. The problem I’ve come across is that occasionally the sync mechanism gets “out of sync” on random client(s) and therefore they don’t see the updated data. I then have to go through checking the pos of the bin log and updating that info on the client which requires that nobody is working in the Server at the time. Kind of a PITA but at this time it’s the only way I know of, thus the reason for this post. Is there some other way??

My setup is such that when users query information, it’s done on the client but when they update it, it happens on the server.

Synchronization at the RDBMS level is a very advanced topic. You are bound to get more assistance from a MySQL-oriented site.

At first glance this seems kind of dangerous. Are the queries so large that querying locally saves a lot of time? Of course if you are replicating changes down to however many clients thats not computationally free either. I’m curious how you ended up with this particular architecture.

Just out of curiosity, are all of the clients on the same network?

If so, I used a system a number of years ago where the clients would broadcast a UDP packet (using AutoDiscovery) whenever a record got updated that the other clients might be interested in. It’s a little tricky to maintain (to avoid recursive notifications) but it does work quite well.

Greg, yes, all on same network. Think you could elaborate on that?

Phillip, I chose this “replication” to eliminate unnecessary network traffic. Most of the time our users are querying the data. It only gets updated occasionally. We are an inbound call center with 25-30 agents on the phone, each handling anywhere from 150-250 calls a shift so there’s a fair amount of querying going on.

Basically you get all of the clients to register themselves to the same broadcast group on your network. When one client makes a change to a customers record, you broadcast a message that says something like CUSTID:42. Then each of the receiving clients look at the message and determine if they care that the customer with id 42 just changed. If they do, you requery the database. The nice part is that you’re not constantly loading the database to see if something changed.

Sounds interesting. I’d like to explore that … got any links to “how to’s” as I’m not familiar with that.

Look at for autodiscovery.

[quote=98870:@Rick Yerex]This may well be a MySQL issue versus XoJo; however, I was wondering how some of you that use MySQL and have Server to Client syncro going on have accomplished this.
Are you using mysql replication with one server as the master & the rest as slaves ?


hmmmm odd since I can’t say I have that much experience with it BUT I’ve not experienced where it gets badly out of sync from a master to slaves and I’ve had to intervene manually

this is using mysql 5 ?

I have this working with PostgreSQL

I have one server it get the data from all clients and then he send to all client .
Cliente 01 send data sync with the server and then the server send data to client 02,03,04 …

Is workin perfect

I have 8 client pos and one server

Norman, yes it’s version 5. I hesitate to go to 5.5 as I don’t care much for the Admin console… it’s totally different and 5.0 has been very stable so I see no reason to upgrade.

For the most part, the sync works. Not sure why it got out of sync on the odd workstation. I switched everyone over to work off the server for the time being. I’m doing a major systems upgrade here (workstations, servers, etc) so I think once it’s all changed over and stable, I’ll switch the users back to server-client with sync and see how it goes.