Getting a sync issue

[antnazNaz]

Used to sync (intranet library) fine via dropbox until something happened and now getting the same error on both workstations.

20191205180115:code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error
near "WHERE": syntax error
at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
at Qiqqa.DocumentLibrary.IntranetLibraryStuff.IntranetLibraryDB.PutBlob(String filename, Byte[] data)
at Qiqqa.Synchronisation.MetadataSync.SynchronisationExecutor_Intranet.DoUpload(Library library, SynchronisationState ss)
at Qiqqa.Synchronisation.MetadataSync.SynchronisationExecutor.DoUploads(Library library, Boolean restricted_metadata_sync, Dictionary2 historical_sync_file, SynchronisationAction synchronisation_action) at Qiqqa.Synchronisation.MetadataSync.SynchronisationExecutor.Sync(Library library, Boolean restricted_metadata_sync, Boolean is_readonly, Dictionary2 historical_sync_file, SynchronisationAction synchronisation_action)
at Qiqqa.Synchronisation.BusinessLogic.LibrarySyncManager.SynchronizeMetadata_INTERNAL_BACKGROUND(Library library, Boolean restricted_metadata_sync, Boolean is_readonly)

---

[GerHobbelt]

Which Qiqqa version are you running?

---

Looked at the master branch code and can't immediately tell what might be wrong there, so we may have to go and dig a bit. I'm very busy with other stuff that's AFK (Away From Keyboard), so turn-around time is slow. 😢

Two things to do:

1: can you go to the sync directory of this library and, if you don't mind sharing metadata of your library, package the Qiqqa.LibraryMetadata.s3db file from that dropbox sync directory and send it to me? (attach to this issue if you're okay with public access, otherwise private message with link to a WeShare link or some such, referencing this issue. My mail address is ger.at.hobbelt.dot.com - you know what to replace in there 😉 to get a legal mail address, I'm sure.) -- This might be some sqlite corruption...

2: archive/backup both your local library and dropbox library sync directory and then delete the Qiqqa.LibraryMetadata.s3db file in the dropbox sync directory, to give Qiqqa a chance to rebuild the sync status info from scratch.

Be warned: this is a dangerous action, so backup that entire DropBox library state AND your local library directory in Qiqqa library base directory first!

[GerHobbelt]

😢 Hit the wrong button there. Re-opened immediately. sorry.

[GerHobbelt]

Note to self = @GerHobbelt about SQLite on Dropbox and other share systems

As I read some ago about the potential problems of having an SQlite DB sitting in a DropBox, GoogleDrive or other cloud share software system, this issue brought that back to front of mind. Cannot find the original pages which put this notion in my mind, but did find this:

http://sqlite.1065341.n5.nabble.com/Sqlite-Dropbox-td95173.html

@GerHobbelt: note the first and second replay in that forum page in particular. Current Qiqqa technology *is using a live SQLite DB on the cloud share in the form of that .s3db file, which is updated using SQLite queries, hence cross-network atomicity is still a potential cause of DB corruption, which MAY occur when two or more machines happen to (auto?)sync at the same time.

TODO: irrespective of this issue's outcome, move Qiqqa onto a read-copy/edit/write-copy mechanism so that there is no live SQLite DB on the cloud drive, only a direct file copy of the latter.

Also, but info value estimate is low right now: http://sqlite.1065341.n5.nabble.com/SQLite-database-on-Dropbox-Google-Drive-MS-SkyDrive-Ubuntu-One-or-SAMBA-share-td69695.html

[antnazNaz]

Hey @GerHobbelt, sorry for the delay. Research took over my life recently. I sent an email with my s3db file. Still having issues with it...

[GerHobbelt]

@antnazNaz : got your s3db file, injected that one in my test collective, but haven't been able to reproduce that exact crash yet. 😢

I did uncover a few other wrongful bits in the code, but I'll have to try a few other things to see if I can make my Qiqqa behave like yours.

[GerHobbelt]

Copypasta email to @antnazNaz: fixed most glaring mobile typos:

Hi, it's getting way too late over here, but I suddenly can reproduce your issue just seconds ago: somehow it happens on the second sync, ie first I create a test library directory for qiqqa to discover on app start, then wait an aeon for the sync management dialog to show up via Show Sync Details, then hit Sync button to have Qiqqa copy the 4K+ remote entries from the S3DB into the test lib, no problem.
The crash happens when I go into qiqqa next, now with a filled local Qiqqa Library and doing that Sync process again.

Wrote this down as it may be a week before i will see my dev machine at home again and I discovered several other issues along the way, so having your s3db and this issue report is again helping to improve the software robustness.

When there's further process, I'll signal via the issue tracker.

Thanks and cheerio,

[GerHobbelt]

On the master dev vox:

20191222223936:code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error
near "WHERE": syntax error
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at Qiqqa.DocumentLibrary.IntranetLibraryStuff.IntranetLibraryDB.PutBlob(String filename, Byte[] data)
   at Qiqqa.Synchronisation.MetadataSync.SynchronisationExecutor_Intranet.DoUpload(Library library, SynchronisationState ss)
   at Qiqqa.Synchronisation.MetadataSync.SynchronisationExecutor.DoUploads(Library library, Boolean restricted_metadata_sync, Dictionary`2 historical_sync_file, SynchronisationAction synchronisation_action)
   at Qiqqa.Synchronisation.MetadataSync.SynchronisationExecutor.Sync(Library library, Boolean restricted_metadata_sync, Boolean is_readonly, Dictionary`2 historical_sync_file, SynchronisationAction synchronisation_action)
   at Qiqqa.Synchronisation.BusinessLogic.LibrarySyncManager.SynchronizeMetadata_INTERNAL_BACKGROUND(Library library, Boolean restricted_metadata_sync, Boolean is_readonly)

[GerHobbelt]

Turned out the UPDATE blob query was completely b0rked in the code.

😭 🤔 That must have been done a long time ago, maybe when I manually synced the WebLibraryDB and LibraryDB source files and maybe screwed up? 🤔

Hm......

[GerHobbelt]

Completed Packaging Qiqqa version 82.0.7265.2217 into \qiqqa\Qiqqa.Build\Packages\v82 - 20191223-011748

==> @antnazNaz :: there's a new v82 beta installer (v82pre7) available at https://github.com/GerHobbelt/qiqqa-open-source/releases/tag/v82.0.7265.2217 to try out.

[antnazNaz]

great work! Fix confirmed.