I am working on a project in which the client requires an online as well as an offline application. Offline application data will be manually synced to online database linked to the web application.

Database is MySQL, some of the functions need to be available online as well as offline. Suppose any user who does not have internet access will use the offline application. Once he is done with his work, and has internet access, he will sync all his work/data to the online database with single click.

I know how to push the data to the online database, but the complexity here is how to maintain the indexes/primary keys of the records in sequence, without losing any data or mismatching any data.

How do I maintain the primary keys in sequence if a user syncs his offline data to online?

closed as too broad by mustaccio, Max Vernon, McNets, joanolo, Marco Apr 5 '17 at 7:27

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Have you found solution for this problem? I'm also looking what is the best approach for this. – leonardeveloper Oct 12 '18 at 23:30

Plan A: You charge your client an arm an leg to develop code to keep track of diffs, avoid dup keys, upload data, etc.

Plan B: Multi-source replication (available at least in MariaDB).

With Multi-source, each offline laptop can be a "Master". The "online server" is a Slave. All writes must be done on the Master(s). Not being able to write to the online Slave should not be an issue, just add a extra, centralized, offline Master in HQ if needed.

As for dup keys -- You must design the schema to avoid any issues like that. This probably means no AUTO_INCREMENT or FOREIGN KEYS. It may mean each offline Master has separate tables (or separate a database) for anything that can't live without things that will cause trouble. But then, that means having some sort of resolution code after each connect. (The upload is merely Replication doing its thing.)

Plan C: NDB Cluster. This is a significantly different Engine available under MySQL. The set up is complex. However, it provides a different model for replication -- "eventual consistency". Rather than hanging on a dup key (etc), you provide rules for what to do in such situations. These rules are automatically applied when replication leads to a conflict.

Plan D: Galera Cluster (built into MariaDB 10.x; manually installable on MySQL) -- With this clustering technology, "nodes" can come and go; when they reconnect they automatically get back in sync. But... There needs to always be a quorum, that is more than 50% online. Although you can write to any node, you cannot write to nodes that are not part of the quorum.

Plan E: Convince the client to stay connected. Or not do any writes when disconnected. In this case, the offline machine can be a readonly Slave. Writes must wait for connection to the remote Master.

Note: In any case, you should write the application with an explicit eye to what app actions need writes versus what can be readonly. Suggest you go as far as to have two separate connections -- one for writes and associated reads, one for plain reads. This will facilitate whatever Plan you pick.

Which do I recommend? I have not heard enough about your situation to say which.

Not the answer you're looking for? Browse other questions tagged or ask your own question.