Writing to collection breaks synchronization and editing in Ankidroid

[catherning]

Hi,
First, thank you for this package! I wanted to edit notes in batch, I'm looking for different solutions, including developping my own Anki plugin, but Ankipandas looks like it could easily help me do my editing - this bug aside!

• Goal : I wanted to edit notes in batch to transform them from Cloze to basic cards. As the selected cards had the same question pattern, I could extract the fields from the cloze brackets using regex.
• OS : Windows 10
• Steps taken :
  1. In a testing profile in Anki, create a new type of note, and convert the notes (is it possible to change the note type with Ankipandas ?). The main field in the resulting note still has the cloze brackets
  2. In a notebook, run the following code that will extract the wanted information from the main field and populate and replace a new field and the main field with it

notes = col.notes
selection = notes.loc[notes["nmodel"]=='Tour de France winners'].copy()
selection.fields_as_columns(inplace=True)
selection["nfld_Year"] = selection["nfld_Winner"].str.extract(r'(\d{4})')
selection["nfld_Winner"] = selection["nfld_Winner"].str.extract(r'{{c\d::(\D+)::who\?}}')

col.notes.update(selection.fields_as_list())
col.write(modify=True) 

(For information, an example row value of selection["nfld_Winner"] is {{c1::Cadel Evans::who?}} won the {{c2::2011::year}} Tour de France. The extracted information are then 2011 and Cadel Evans respectively)

4. Export / import the collection from the testing to the main profile
5. The first time I ran into the issue, I did "Notes > Clear unused notes" and then "Tools > Check Database", because I had the flag issue Writing to collection destroys tags #50. But skipping this step also led to the same issue
6. Synchronize the collection with Ankiweb
7. On mobile (Ankidroid), try to edit a note. When clicking on the check button to save, the note has not saved the modification
8. Synchronize the collection again
9. On Anki, synchronize the collection, and try editing a note. When synchronizing, I have the following popup
   
10. Checking the database again doesn't work, I can't synchronize the collection anymore and the popup keeps appearing. Forcing the sync push to Ankiweb works, I get the update in Ankidroid then, but I still can't edit notes in Ankidroid

I hope this is clear enough!

[klieret]

Hi @catherning. Thank you for this detailed writeup! So you are saying that you are stuck and cannot get the collection to synchronize anymore? I'm very sorry to hear about this!

However, ankipandas always creates backups of the collection that you can use to restore the previous state. Do you still have the logs from the first time you ran ankipandas and wrote the changes to disk? It should tell you about the location of the database file and the backups.

Once you're there, maybe make a copy of the current collection file and then replace the collection file with the latest backup. This should restore the state before ankipandas was run.

Let me know if that gets you back to a working database/synchronization.

[klieret]

INFO: Backup created at C:\Users\TEMP.PKAPRIME.000.001\AppData\Roaming\Anki2\test\backups\backup-ankipandas-2023-02-14-21.16.29.292560.anki2

This should be your backup file

DEBUG: Opening Db from C:\Users\TEMP.PKAPRIME.000.001\AppData\Roaming\Anki2\test\collection.anki2

Your database file

[klieret]

Ah, but I just saw that you actually used an export/import workflow to get the changed notes into your main profile...

So maybe you would restore the test collection, check that everything works there, and then do another export/import from test to your main profile?

[catherning]

Hi @klieret, thanks for your quick reply!

For now, I have no issues restoring to previous backups, as I do a manual backup by exporting in addition to those ankipandas automatically creates.
But the main bugs are that

• the collection that was edited with ankipandas can't be edited anymore on Ankidroid
• I must choose "Preferences > On next sync, force changes in one direction" to sync the collection in Anki each time

I know the manipulation fonctions are still in alpha, I wanted to report this related issue.

[klieret]

OK, I'm relieved that you restored everything to a working state.

I believe these issues might be related to the "last modified" timestamps. Let me check.

[klieret]

This reddit thread seems to talk about a similar problem.

[catherning]

Thank you for looking into it! Indeed, it seems that I have a similar need and issue.
Looking back at the add-on docs, Anki advises to use the col.update_note method instead of directly accessing the database, but doesn't talk about note.flush. I'll try to look into these 2 methods if there's an additional step to take to mark notes for sync.

But I thought forcing the sync would mark all the notes for sync "once and for all"? The thing is, after editing with ankipandas, all the following syncs must be forced, and there's no possibilities to edit the notes in Ankidroid afterwards

[klieret]

Hi @catherning.

Looking back at the add-on docs, Anki advises to use the col.update_note method instead of directly accessing the database

Indeed. This is why writing support with AnkiPandas will probably always be a bit brittle: it doesn't contain any special logic that Anki does.

But I thought forcing the sync would mark all the notes for sync "once and for all"?

This is my understanding as well: Having operations that require a forced sync happens (even when just using Anki). But you only need to do it once and then go back to normal.

and there's no possibilities to edit the notes in Ankidroid afterwards

This is definitely a bug.

Actually, going back to your original report: You said you export and then import again, right? Could you produce a minimal example of the export file (only containing perhaps a single note) that triggers the issue? What's the format of the export?

Is it just CSV? Because I'd say that if you can break anki/ankidroid in this way by importing a CSV, then that's a bug on Anki's side (and I'd be very curious to see that CSV file).

Probably apkg, right?

[catherning]

Hello @klieret,
Actually I used colpkg which overwrites my current collection with the one in the file.

So, I have a few more details. On Ankidroid:

• I can't edit the notes
• I can delete a deck, or add new notes and these modifications are taken into account
  As these are modifications, I can then sync back to Ankiweb.
  In Anki, I can also add notes or delete decks. As long as I only do these two actions in Anki or Ankidroid, I can sync back and forth. But looks like as soon as I try to edit a note in Ankidroid or Anki, the collection is broken.

Other bugs I just got, using Ankiweb directly (to redo for confirmation):

• I add a new card on Ankiweb. I sync back in the software. The new card doesn't appear. But when I check the database, it says that it deleted a card with missing note, and then the new note appear
• In my main account having the broken collection with a single note, I import the apkg file that was exported from the test account. It says that the note was taken into account, but on Anki, I don't see the edit in the note. After syncing, on Ankidroid, I see duplicates of the note (still without the edits from the apkg file)

I hope these details are helpful.

Here's the collection with a single note.
collection-2023-02-27@22-06-27_single_note_bug.zip

[klieret]

Thanks again for providing more information. Unfortunately, I cannot find time at the moment to work on this. I'm pretty sure we could figure this out by reading the Anki source and finding out what they do precisely when they write into the SQL database. I have marked the issue as help wanted for now; perhaps there is someone out there with some time at their hands to work on this.

[dae]

I have seen multiple bug reports that turned out to be caused by this project. Please either fix the issue, or remove the broken functionality so that it doesn't create more work for us and the people using it.

The issue appears to be that the notes table is being created without a primary key when .write() is called.

[klieret]

Hi @dae. Thank you for reaching out, I'm very sorry to hear that my package has caused problems. I really appreciate Anki and certainly don't want to add additional work for you.

As I currently don't have the time to work more on this and haven't found a volunteer so far, I will make the write method throw a NotImplemented error and update the documentation accordingly.

The issue appears to be that the notes table is being created without a primary key when .write() is called.

Ah, that's very interesting. I still hope that this can be fixed in the future. Would it be OK to reach out with questions if more help with understanding the database internals was needed?

[dae]

Thank you for pushing out a fix quickly. Questions about the file format are best posted on forums.ankiweb.net, but I would recommend rather than directly reading/writing .anki2 files, you instead make use of Anki's Python API, as the file format was not written with external programs in mind.