1

My application that is running on a client uses a MySQL database running on a server. So multiple clients are connected to the same server. That works well when the server is online. But now I would like to enhance my application to be able to run in an offline mode.

                +--------------+
                |              |
    +-----------+   SERVER     +----------+
    |           |              |          |
    |           +-------+------+          |
    |                   |                 | 
+------+-------+   +-------+------+  +-------+------+
|              |   |              |  |              |
|  Client 1    |   |  Client 2    |  |  Client X    |
|              |   |              |  |              |
+--------------+   +--------------+  +--------------+

Now comes the problem: what happens when the client is offline? I need a copy of my MySQL database on each client too. By default the application interacts with the MySQL on the server. If this server is not accessible (for what reason ever: server is offline or client has no internet connection) it should use the MySQL running on the client. If the client/server connection is available again the databases need to be synched automatically.

My question is now: how to achieve this? First of all I checked the MySQL-replication, but in my scenario I have multiple "masters" and an unknown number of clients. So I afraid that replication is not my solution. Is it possible to solve my problem with MaxScale? I never worked with that so I really appreciate any help.

Improve this question
6
  • When the server is offline, what sort of database features do you need? I.e. just insert new rows, or do you also need to select, update and delete rows? Also, is this a fixed, small number of clients? Why do you think a client might be unable to reach the server - is that a likely event or are you just wanting to design something robust just in case?
    – dbdemon
    Sep 19, 2021 at 10:36
  • @dbdemon I would need the full set of operations (select, insert, update, delete). Currently, we are talking about 5-20 independent clients (a client by customer). The software is a timekeeping and sporting event mgmt software. So in some cases or places an internet connection can not be guaranteed. Therefore I would like to design it in the way that (by default) the server DB is used but if fails the client DB is used. If the internet connection is re-established the server/client should be synced. That's the background...
    – Lars
    Sep 19, 2021 at 10:43
  • I don't think there are products on the database layer that fulfil these requirements, at least not within the MariaDB/MySQL ecosystem. Replication is too fragile to cope with frequent loss of network, and would require manual intervention to make work again after an outage etc. I suspect you will need to implement the solution largely in the app layer. If the database isn't too big, you may be able to keep a read-only database copy in the clients for offline use. When offline, the client app could store writes for later synching, but you would need app logic for resolving write conflicts.
    – dbdemon
    Sep 19, 2021 at 11:29
  • @dbdemon app layer logic is something I would like to prevent (write conflicts) and unfortunately I cannot change my DB infrastructure. So it has to be MariaDB/MySQL. But what is with the MaxScale. After reading some MS docs: Isn't it possible to spawn a cluster over all of my clients. Doesn't MaxScale handle any replications?
    – Lars
    Sep 19, 2021 at 11:33
  • A multi-master cluster (e.g. MariaDB Galera cluster) would require reliable network to function, maybe even more so than with traditional asynch replication. (Google "geo-distributed Galera cluster" to learn more.) The no. nodes in a Galera cluster should also not really exceed 5 or so as it affects write speed. MaxScale is a database proxy which can, amongst other things, detect unavailable hosts and instead redirect queries to available nodes. But it doesn't resolve write conflicts - that is a problem which can be detected on the DB side and then must be handled by the app.
    – dbdemon
    Sep 19, 2021 at 12:21

3 Answers 3

Reset to default
1

You commented:

So there is no really "simple" solution to implement the scenario described? It's so strange because I don't want to believe that my scenario is so unusual...

It's not an unusual requirement, but being common doesn't make it simple or easy to solve.

The problem every project that has this requirement may have a different idea about how it should work. So there's no way for an out-of-the-box solution to be right for everyone.

Suppose a couple of your clients are offline, and they both write changes to their respective offline databases. Then the internet is restored and you want them to get back into sync. But they each updated some of the same rows in a given table. Which one should take priority? Should it be first come, first served? Should it be the last update wins? Should it be a merge conflict, requiring manual intervention to resolve (like a git merge conflict)? You might be able to answer this for your own project, but not for every other project.

Products like Galera handle this by allowing nodes to accept writes only if they are connected to the cluster. Offline nodes automatically become read-only.

I don't know much about MaxScale, but it apparently is a proxy to a traditional MariaDB replica set, with one writer and N read-only replicas.

Improve this answer
1

I think you're looking at your scenario the wrong way. Firstly, is your client application a mobile application? Because then that makes more sense why you want to implement an offline mode. It's unusual to implement offline database support for a desktop application where network connection is usually relatively reliable.

If it is a mobile application, then you need to use the right tool for the right job. MySQL isn't supported on mobile devices, but SQLite is and usually the general choice of a local database system that can handle offline mode. There are also a series of other mobile supported database systems that can be used instead of SQLite as well.

You would still want your main remote database system as the place to consolidate all the data once a device is back online, which can indeed be MySQL. But as mentioned in the comments by dbdemon, you need a way to handle resolving conflicts, especially if you allow multiple client applications to modify the same records of the database. Likely this would need to be handled in the application layer or a mix of the application layer and the database layer, but you would need to codify it yourself to a degree. If MySQL has some sort of change tracking feature then that could help greatly in determining what data is new since the last time a client device has synced. Perhaps this StackOverflow question regarding change tracking has some answers helpful to you.

If you are talking about a desktop application you're trying to support offline mode for then we'd probably need more information on why and what problem you're trying to solve in doing so, since it is an unusual use case.

Improve this answer
8
  • thank you for your answer. To your question: it's not a mobile application. Today it is an Angular app using a MySQL DB hosted on a server. But there is also a middleware (Raspberry) that is connected to timekeeping hardware on client's side. Some customer have the wish to run the app local too. They afraid the Internet breaks or it runs on a place w/o connectivity etc. That's why I'm looking for a solution now. But running on server is the primary method. Starting the app from the middleware should be an option. That's a reason why synch is required.
    – Lars
    Sep 19, 2021 at 14:48
  • @Lars I'm not an expert on Angular, but I believe it's only a web technology. So even if you solved the problem with the database being offline, the website still wouldn't accessible to interact with by the end users. A mobile application hat supports offline is usually the best route in this case.
    – J.D.
    Sep 19, 2021 at 16:23
  • no no maybe my explanation was not clear ;-) Each client require its own middleware computer (Raspberry). A web-server for the frontend (Angular), a node.js based backend and a MySQL DB can run on this device. So the middleware is in the local network and I don't have problems with connectivity at this point. Next: the Angular application is not public available but for the client user only. It's like a program like MS Word that is installed on my local laptop.
    – Lars
    Sep 19, 2021 at 16:34
  • 1
    @Lars Yea unfortunately I don't believe MySQL has as much change tracking support as other modern RDMBS. If you were open to switching, PostgreSQL and Microsoft SQL Server both offer a multitude of ways to do so automatically.
    – J.D.
    Sep 19, 2021 at 17:42
  • 1
    I'm not sure what kind of change tracking you're thinking of, but MariaDB (which seems to be what @Lars is using) has support for Temporal Tables (System-Version, Application-Time Period and Bitemporal) since version 10.3 - 10.4.
    – dbdemon
    Sep 25, 2021 at 21:45
0

You might want to think outside the box here. Your main issue isn't related to a database solution that will somehow replicate the clients that are unable to connect to the central database, but more of a conceptual issue of how to replicate database clients that can be OFFLINE (not connected to the main server) or ONLINE (connected to the main server) and/or the data that is modified OFFLINE or ONLINE.

The (Basic) Requirements

For the pseudo concept I'm thinking of implementing a CRM (customer relationship management) tool, where salespeople can insert and modify data on their laptops. The data has to be synced with a master database (looks like what you are trying to achieve).

Locations

So I'll have a centralized database and databases on the clients. The clients are located all over the world. Some report directly with the main headquarters in California (USA), other salespeople will be located in different locations like London (UK), Paris (FR), etc.

Questions to Ask

Will the data be stored in the decentralized locaitons?
Will the data be stored only in the central locaiton?
Will people be modifying data when on the road?
Will people at the decentralized locations be modifying data directly?
On which database/system?
What if the link to California goes down?
How will the assistants work with the CRM data?

Data Sovereignty

If changes are made to the data, who takes precedence? Is the salesperson the master of modifications? The assistant? The person at Headquarters in California?

Could the data be split so as to allow for multi-side modification? HQ is allowed to change the address of the customer, but the salesperson is responsible for the actual (personal) contact information of the company representatives.

Do local decentralized locations (e.g. London, Paris) know more about the local companies than the HQ in California? Would it be better for them to have a decentralized country database? If so should certain countries be linked to the decentralized databases, rather than allowing HQ to be the master of all data?

Online / Offline

Is OFFLINE actually OFFLINE or just OFFLINE from a (de-)centralized main database? Is ONLINE the connection to the (de-)centralized main database?

Possible Concepts

  • Could you envision a main / satellite / client setup where each location has its own database? (Could be anything from MySQL to Microsoft SQL Server to Centura/Gupta).

  • You could have markers on certain tables (two columns) that determine when a record was last modified. This could be a date column lastmodified and a changedby column 00000000000000000000000..... which contains the position of the last person to modify the data.

Contents of changedby Column in Contact Person Table

111000000000000000000100000000000000010000000000....
|||                  |               |
|||                  |               +---------: Changed by Salesperson Levrat in Paris
|||                  |
|||                  |
|||                  +-------------------------: Changed by Salesperson Murry at HQ
|||
|||
|||
|||
||+--------------------------------------------: Changed in Paris
|+---------------------------------------------: Changed in London
+----------------------------------------------: Changed at HQ

When the decentralized server in Paris syncs with the HQ it overwrites the changes made at the HQ, because Paris knows better what Contact Person information is relevant for the customer in Bordeaux.

When the salesperson Levrat comes back into the office, then his information overwrites the data in Paris. Which subsequently is resynced with the database at HQ.

Each synchronization resets the flags.

Building a Solution

Now this is just a basic example and if you add the lastmodified column, then you could possible build a synchronisation the permits the last changed record to overwrite anything up or down the line, depending on your requirements.

Add another column and you have could have certain data only synchronized to the satellite locations.

Add a pulldown button to the applicaiton login and the client can login to the central (HQ), decentralized (PARIS) or local database.

Add a push-button in the client applicaiton and the client is asked to synchronize with Paris (default) or HQ (optional).

Add the split between main customer data and customer representatives and you can sync some data in one direction and other data in the other direction depending on who has data sovereignty.

Requirements Engineering

First you need to know what your current requirements are and what your future requirement could be. Then you can start to think about implementing a tool (out of the box), a complex solution or your own custom tailored build.

It's a long journey. If you take the time to analyze your requirements first and to invest a lot of time before you even start programming or looking for a solution, then you'll pass the finishing line. Otherwise you may fail to reach the finishing line....

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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