Metadata file 'notes.sqlite' causes sync conflict with multiple clients

[brainchild0]

Expected behaviour

Multiple instances of client connect to same remote endpoint without any synchronization conflicts, except when the same note has been modified on both sides.

Actual behaviour

File notes.sqlite is reported as synchronization conflict.

Steps to reproduce

Configure an instances of QOwnNotes on each of two systems, to connect to the same location in the same Nextcloud instance. Configure Nextcloud clients on each system also to synchronize those locations.

Suggestion

Store tag, trash, and related metadata in a separate configuration directory. Also possible to use a hidden file file, although Nextcloud clients can be configured to synchronize hidden files, thereby reintroducing the same problem.

Workaround

Per site, clients can be configured to exclude files based on name patterns.

[pbek]

Is there something that can be done in QOwnNotes? Seems like either a Nextcloud behavior or a problem with or a "maximum open file handler" problem where the operating system doesn't report that the file notes.sqlite or its folder was modified. QOwnNotes would reload the note folder if it was modified.

[pbek]

Maybe it has something to do that the database file is opened by QOwnNotes (Qt Sqlite Engine). On Windows I have a settings where I attempt to close it after operations, but that was quite experimental. A lot of bad stuff can happen there...

https://github.com/pbek/QOwnNotes/blob/14948f105b26c50dd3f62b97edec47a016af3545/src/services/databaseservice.cpp#L323-L338

[pbek]

Also see https://github.com/pbek/QOwnNotes/issues/926

[brainchild0]

I think the central issue is that the file contains the metadata for all notes, not just one note. So for note contents, a conflict occurs only if the same note is modified in multiple locations, whereas the metadata file can conflict if any metadata is modified. Also, boundary cases can occur that users may not anticipate, creating a lower quality of experience. For example suppose a user configures a new QOwnNotes client and points it to an empty notes directory, 'then' configures the files synchronization cleint. If a metadata file is already created in the otherwise empty location, then it is a conflict. (I think that is what I observed, in my case. If you think I am mistaken then I can try to reproduce.)

[pbek]

Maybe you can start by posting the rest of the requirements of the issue template to see where you were testing. :wink:

[brainchild0]

I could try to reproduce with the log pane open, but the fact is I never witnessed QOwnNotes misbehave or even complain. The only bad thing that happened was the Nextcloud client complaining of a conflict.

[pbek]

Still, seeing the content of your Debug settings would be nice to help you... :smile:

[brainchild0]

I would like to provide further needed information, but I am not sure what to do. I consider the problem I describe is independent of whether QOwnNotes is even running. I don't know how to get QOwnNotes to report any problems, or why I would try.

Perhaps I did not adequately explain the problem.

If I configure the Nextcloud Desktop Client to synchronize a folder, and if both the client and server have files of the same name and location, but different data, from before the synchronization was configured, then the client reports a conflict. Such is the behavior of the client, and I do not consider this behavior incorrect, because it is the only way the client can avoid data loss in general.

Thus, if I have a server that is already synchronized against a client, and that client has created a database file, and then I install the application on a second client, and configure QOwnNotes before configuring the Nextcloud file synchronization, then QOwnNote will create a new database, and the server and the client will find conflicting files of the same name at the time of the first synchronization attempt. The client has a new database, and the server has preexisting one from the other client. As synchronization has never occurred before, Nextcloud client has no means to resolve which version is new and which out of date.

QOwnNotes may be smart enough to work with other copies of itself, but the user experience will be that Nextcloud reports an error.

If you feel this issue is not worth resolving, then I understand, but at least it is now reported.

[pbek]

Still, seeing the content of your Debug settings would be nice to help you... smile

Open the settings, click on Debug

[brainchild0]

I reproduced, and created a log recording the events surrounding the point when the Nextcloud client reports the conflict. I hope it is helpful to you.

qon.log

[pbek]

Thank you, but nothing very unusual is happening there. And I still don't know anything about your operating system and configuration.

[brainchild0]

I agree, nothing unusual is happening. My OS is Linux Mint. I don't know what features of my configuration interest you, but your emphasizing those details suggests to me that we are not communicating effectively.

The effects I have described are design features of Nextcloud. They are not OS-dependent. It is clear that if the user creates a synchronization folder on the client, and the folders on the client and desktop at that time have different versions of the same file, then the client reports a conflict.

This behavior is correct, but if conflicts with design of QOwnNotes.

[pbek]

I don't have this issue on KDE Neon or Manjaro with the Nextcloud sync client.

[brainchild0]

Once the synchronization is configured and active, then the issue would not occur. It is a special case at the time of configuring a new instance, and even then only when QOwnNotes is configured before the Nextcloud client is configured.

Have you tried the steps in this sequence, then reviewed the Nextcloud client log?

[pbek]

There can only be tho things happening.

• a lock on the sqlite database file because Qt keeps it open (see #926)
• too many files open by inotify

Both are hard to get around.

[brainchild0]

I don't understand why you say there can only be two things happening. How did you conclude that, especially when I explained exactly what is happening, and why it's happening, without invoking either of these cases?

Try an experiment. Create a folder on your Nextcloud server, populate it with a file called test.txt, and write "Hello" in the body of the file. Then create a folder on your desktop, add a file called test.txt (same name as on server), and write "Goodbye" in the body of the file. Now configure your Nextcloud client to synchronize the two folders.

Does the client report a conflict before finishing the synchronization? It should. Otherwise, which is correct? "Hello"? "Goodbye"? The client can't know.

I did this experiment earlier. It took only a few minutes.

[pbek]

I did this experiment earlier. It took only a few minutes.

I can, if you can tell me what it has to do with QOwnNotes. :laughing:

[brainchild0]

It is a lower quality of user experience if QOwnNotes is generating results that cause Nextcloud to complain.

If you change the above experiment by substituting test.txt with notes.sqlite, substituting "Hello" with a user's pre-existing database, and substituting "Goodbye" with the new database from the newly-configured client instance, then I think you understand how the issue is related to QOwnNotes.

You may feel that the boundary case is too insignificant to merit changing the design, but I still wanted to report the behavior to make it a known issue/limitation.

[pbek]

I'm sorry, I cannot change the design of how Qt works with sqlite databases and even bad things happened when I tried to close the database after queries (see #926).

[brainchild0]

The behavior occurs whether or not QOwnNotes is actively running. It is not a question of whether a database connection or file handle is open. It is a question of whether a version of the database is introduced into a shared folder that is not recognized by the client as a derivative of the one previously on the server.

Consider the following steps:

1. Create an empty directory called <notesdir>.
2. Create a clean installation of QOwnNotes.
3. Open the application and configure the notes location to <notesdir>. (The application immediately creates <notesdir>/notes.sqlite.)
4. Close the application.
5. Configure a share with the Nextcloud client to synchronize <notesdir> with an existing notes repository on the server.
6. Synchronize the share. (Nextcloud client complains about the conflicting versions of <notesdir>/notes.sqlite).

QOwnNotes is not running, but it has placed data on the file system in such a way that Nextcloud is unhappy.

[pbek]

Thank you for your description.

Close the application.

are you sure it is closed (as in quit)?

it has placed data on the file system in such a way that Nextcloud is unhappy

this is all done by Qt automatically and I have no idea why Nextcloud would complain about a file with no open file handler, I'll try to reproduce your issue if I have time at hand...

[brainchild0]

Two-way synchronization algorithms use various strategies, but they all take the following general form:

For each file that exists on both endpoints:

1. Since the last synchronization, has neither version, client or server, been modified? If so, then do nothing.
2. Otherwise, has the server version been modified but not the client version? If so, then overwrite the client version with the server version.
3. Otherwise, has the server version not been modified but the client version has been? If so, then overwrite the server version with the client version.
4. Otherwise, both the server and client versions have been modified. A conflict is identified, because neither the server or client versions may be overwritten without losing changes. (In some systems, no conflict is identified if the content of both files is identical.)

Algorithms use various strategies to detect whether a file has changed since the last synchronization, including timestamps and checksums, They also record data about each file during a synchronization operation, for a reference of comparison during the next operation.

In the special case of the first synchronization of a paired folder, no data from a previous operation is available. Therefore, any file that exists on both endpoints is considered to have changed, and to be in conflict (except if the files are identical, and the algorithm detects it).

During the first synchronization, if the client has previously created a version of notes.sqlite, and a different version already exists on the server (e.g. from other clients), then the system cannot resolve which version to keep on both sides and which to discard, so it reports a conflict.

This effect has no relation to whether an open file handle exists, or any other runtime state of the system, only how the files appear on the file system.

You generally do not see this effect after the first synchronization completes successfully, because when a change occurs on one client, it is synchronized to the server, and then to other clients, whose versions have not changed since the last synchronization operation, and so may be overwritten without conflict. Nextcloud desktop clients synchronize frequently, and your notes collection is likely modified only by you. Meanwhile you can only work on one client at a time, and synchronization completes faster than you switch clients.

Also as stated previously, if you synchronize the folder pair on a client before configuring your application on that client, then your application finds the copy of notes.sqlite that already exists from the synchronization, and modifies that copy, rather than creating a new file. Again, the client detects that the client version has changed, and that change is propagated to all endpoints faster than you can switch clients.

[gitowok]

Hi, I can confirm I'm also getting sync conflicts. I'm on various gentoo and ubuntu desktop pcs, running latest versions of QOwnNotes via NextCloud. Is it safe to just set a crontab to rm notes.sqlite if i'm not interested in any features other than editing raw text files? Is there any way to disable metadata and generation of this sqlite file? Can we set the path of this file to be outside of the notes folder structure, for example ~/.qownnotes/notes.sqlite. Let me know if you'd like debug info or anything else from me, and many thanks for your time.

[pbek]

The file holds information about tags (per note folder) and thus needs to be inside the note folder to be synced. If you delete it it will be generated again (which shouldn't be a problem)

[brainchild0]

If the file only holds tags and is not used by other applications, only QOwnNotes, then many users may not need it. Perhaps a configuration option would be beneficial to disable creation of the file. I did not see such an option in the preferences dialog box.

[pbek]

The trashed items are also listed in the database and the database is needed by QOwnNotes, so I cannot simple allow to "disable" it.

[brainchild0]

Maybe some users would want a toggle switch for disabling synchronization of the file, either by making it hidden or placing it in an external directory.

[pbek]

toggle switch for disabling synchronization of the file

they could do that in their sync client, like the Nextcloud sync client (QOwnNotes doesn't do any syncing)

but I would like it much better to find out what is causing the sync conflict... since the mechanic in QOwnNotes didn't change for a long time I only can think of some thing of a newer Qt version or some changes in the Nextcloud client.

[brainchild0]

Yes, a user could write a filter rule for the file name, but it would affect all shares. It is cleaner if the application does not write the file to the notes directory. Or if the file begins with ., then it is filtered automatically, on systems which consider such files to be hidden.

Reducing the incidence of conflicts could be achieved by synchronizing metadata for each note separately. The current design makes this behavior difficult to implement. However, the Nextcloud Notes app is under active development, and I understand that a RESTful API is planned. I expect that tags and other fields will be supported natively. Until this interface is specified and implemented, major revisions to the design on the client side may not be a good use of resources. Once the interface is available, I would assume it will be possible to synchronize metadata at a record or notes level of granularity in the application logic.

If the problem in the original report appeared recently, it could be because the desktop-client synchronization logic has matured. The behavior I have described is typical of 2-way synchronization algorithms, from what I understand.

[pbek]

19.3.4

• made an attempt to remedy notes.sqlite sync issues with Nextcloud

[pbek]

There now is a new release, could you please test it and report if anything changed with the syncing issue.

[brainchild0]

I can try. What are you expecting the new release to do differently with respect to this issue?

[pbek]

see https://github.com/pbek/QOwnNotes/commit/c17235d7d5e9faf3987aaf134092ef0c3fb61b77, I tried to do some things (that shouldn't be necessary) to not get the file locked.

[brainchild0]

Yes, I am just not sure how this change would relate or could relate to any change in the behavior I described, which as I explained appears to be determined by the state of the files on the file system, not the lock status. I have no indication that the NextCloud desktop client is aware of such locks, and as I also explained, the behavior reproduces even when NextCloud is not running.

[pbek]

Which states of the files on the file system did you experience?

[brainchild0]

Please review my earlier comments (e.g. 17 Jan), which explain synchronization algorithms. File checksum and date are primarily the information used.

[pbek]

I'm sorry, I still didn't grasp most of the things you were talking about in your big post on 17 Jan :smile: But if it's about the way the sqlite file is created we can't do a lot of it since it's done by Qt directly.

[brainchild0]

Well, you can consider adding an option to place the file outside of the location where NextCloud operates (or to make the file hidden following platform-specific conventions). This solution might help reduce problems for some users.

I'm not sure what to say about the earlier comments. I tried to be as thorough as possible in that posting, as well as in others that clarified the distinction between file metadata and lock state.

[pbek]

Well, you can consider adding an option to place the file outside of the location where NextCloud operates (or to make the file hidden following platform-specific conventions). This solution might help reduce problems for some users.

that wouldn't solve the problem, since the file is needed for QOwnNotes to operate and needs to be synced too for tagging and the trash to work. If anyone chooses not to sync it he/she can simply exclude it in the Nextcloud client directly.

[brainchild0]

Adding a filter rule to the NextCloud client may have unwanted side effects, because they apply globally. The client does not allow exclusion of specific files in the client-side folder, only creation of global name-based filter rules. Also this approach couples the user's Nextcloud configuration to the design choices of QOwnNotes, such as the name of the database file.

[pbek]

Adding a filter rule to the NextCloud client may have unwanted side effects, because they apply globally. The client does not allow exclusion of specific files in the client-side folder, only creation of global name-based filter rules. Also this approach couples the user's Nextcloud configuration to the design choices of QOwnNotes, such as the name of the database file.

that would be a feature request for the Nextcloud client

There now is a new release, could you please test it and report if anything changed with the syncing issue.

between two Linux Clients where I previously had sync issues with the notes.sqlite I now don't have any with version 19.3.4, at least I can't reproduce any currently. the database gets synced even if both computers are running and QOwnNotes is active on both. it also get synced if only one was running and the other is woken up from sleep

[brainchild0]

Adding a filter rule to the NextCloud client may have unwanted side effects, because they apply globally. The client does not allow exclusion of specific files in the client-side folder, only creation of global name-based filter rules. Also this approach couples the user's Nextcloud configuration to the design choices of QOwnNotes, such as the name of the database file.

that would be a feature request for the Nextcloud client

Someone could request per-file exclusion in Nextcloud client, but it is unlikely to be adopted because it breaks Nextcloud design, which presents the server content as a master copy, of which each client may select a subset. Thus, the server view may not be a subset of the client view.

However, that feature is not what I am suggesting currently,. I am suggesting that QOwnNotes support an option to change the location or attributes of the database file such that it would not be subject to syncing in Nextcloud. QOwnNotes is intended to sit above a Nextcloud client deployment, so features in QOwnNotes that align with NextCloud design would be a sensible approach.

[pbek]

I'm still not sure what features of QON are not in align with Nextcloud design.

I am suggesting that QOwnNotes support an option to change the location or attributes of the database file such that it would not be subject to syncing in Nextcloud.

implementing that would be a lot of work and does not solve anything since the file needs to be synced

and what attributes are you talking of? xattr? there are none (just checked)

do you still get those sync problems with the current version?

[brainchild0]

I'm still not sure what features of QON are not in align with Nextcloud design.

That the internal structure of QON-managed directories separates those files that the user wishes to synchronize from those he does not wish to synchronize, such that only files in the former category are in fact synchronized.

I am suggesting that QOwnNotes support an option to change the location or attributes of the database file such that it would not be subject to syncing in Nextcloud.

implementing that would be a lot of work and does not solve anything since the file needs to be synced

I see. The suggestion was meant to be simple. The minimal approach would be a single checkbox that determines whether the file is called notes.sqlite versus .notes.sqlite.

and what attributes are you talking of? xattr? there are none (just checked)

"Attributes" was not meant so literally. Primarily I meant file name, and/or hidden flag on platforms with support, such as Windows.

do you still get those sync problems with the current version?

While in principle I could perform this test, I had already invested some effort in isolating the problem to specific issues, which I then explained in detail, and which do not relate to the changes applied in the new version. While I am willing to help in ways that are likely to benefit the effort, I would prefer not to invest further effort in tests that cannot reasonably be expected to help.

I previously verified that the problem occurs when QON is not running. The hypothesis that the behavior would improve by merely changing the way QON uses locks while running is inconsistent with previous observations. Testing this hypothesis does not seem to be an activity that is likely to help.

[pbek]

isolating the problem to specific issues

the issue that the Nextcloud client seems to be unable to sync a file? I wonder why you still seek fault in QON (or trying to make me cripple it) if QON isn't even running in your test. If you can find out what is so special about the file on your machines then I (or Nextcloud) may be able to help you...

[brainchild0]

Nextcloud isn't failing to sync the file. It is identifying a conflict because neither the server or client version of the file can be identified as the correct version that should overwrite the other. In such cases, Nextcloud writes the version on the server to a new file on the client, with a similar name, and reports the event in the log. The user is expected to choose which version to use, or to create a new version by manually merging the content of the two. This behavior is entirely within the design of Nextcloud. It is an intrinsic limitation of merge algorithms that conflicts can occur.

Think of it like interacting with a remote Git repository. If you attempt to push a branch that was updated on the remote side after your most recent fetch, then the merge will fail. But it is not a case of Git behaving incorrectly, but rather of Git telling you that you are responsible for resolving the conflicts before the remote repository can be updated in a nondestructive way.

The file in question is not "special". The reason why the sync does not complete without a conflict is that in the particular case that I described, the file was modified in multiple places such that the conflict cannot be resolved automatically.

When a user has configured Nextcloud to keep a directory in sync with a server, the user will normally avoid creating conflicts. I suggest that QON similarly avoid creating conflicts with files it creates. This suggestion is not crippling QOwnNotes, but rather operating in a way that is compatible with the correct, expected behavior of Nextcloud.

[pbek]

The only thing I can see so far is that I didn't get any sync conflict between any of my computers so far (3 Linux, 1 Mac, 1 Windows) since 19.3.4.

[brainchild0]

Interesting that you have such a result.

A few items to check:

1. Originally, the local directory contains no notes.sqlite file.
2. The application then creates a notes.sqlite in that directory.
3. The server already contains a notes.sqlite in some directory.
4. Finally, after all of the above steps, the synchronization pair is configured for the two directories, local and remote, each containing distinct notes.sqlitefiles, and the synchronization task is initialized.

If the Nextcloud client log does not report a conflict, then are you able to determine whether either file, the one from the server or client, is overwriting the other? Or do the files remain out of sync?

[pbek]

I don't understand steps 2 and 3. Did you use QON in a new note folder? Why has the server already a notes.sqlite?

[brainchild0]

Did you use QON in a new note folder?

Yes, this detail is on the list of steps to reproduce.

Why has the server already a notes.sqlite?

Because the folder on the server was previously synchronized with a different desktop system that also runs QON.