Server-client DB synchronization

[Dhanya CM]

We have a data synchronization scenario wherein we want to send the data from master DB to client. The clients will have only a subset of master data depending on some rules which are calculated at the server level.

Here we have cases where the tables have relation among them and there can be "n" levels of such relation. ie I can have Table-1 whose data will determine what data from Table-2 should be sent which in turn will determine data of Table-3 be sent and so on. So if there is any change in any of these tables then all tables down the hierarchy should be recalculated and synchronized.

We used the complex query mode with query reference tables as suggested in the blog and some developers that we interacted but we are still facing issues. The related data is not sent to the client. O

Any suggestions on how to approach this case will be very helpful. We can sent logs if required.

[perv...@gmail.com]

Hi Dhanya,

We have got the logs through emails. Here was our reply:

The processing of complex subsetting could be slow. For your case, the processing reached a MySQL timeout so it never finished and thus you never got the updates to clients.

 

See section 7.2 of the Pervasync User's Guide to find out how to increase the MySQL "lock wait" timeout.

 

For your testing, you can monitor the pvsadmin.pvs$engine_history table to find out if a sync engine cycle has finished after you made a data change.

 

For your case I don't recommend using complex subsetting since your query is slow and you are subsetting based on user id. It would be better if the subsetting was based on group id -- users of a same group get the same subset.

 

So I recommend you convert the complex subsetting to simple subsetting. Here is the approach. Suppose a user is subscribed to lessons and lessons have topics. Use the topics table as an example. You create a subscription table for topics, say user_topics (user, topic_id). You have to do extra work to maintain this table. For example, when a new topic is inserted to the topics table, you (your app) need to find out which users should have this topic and insert records to the user_topics table. Same for deletes and updates of topics and when a user is subscribed/u-subscribed to a lesson.

 

Once you have the user_topics table, you can publish topics table using SIMPLE mode:

 

select topic_id from user_topics where user = ${USER}

 

Note that you don't need to publish the user_topics table.

Thanks,
Pervasync support