How to rebuild the search index in the desktop application?

[sergei_mipt]

my database has got broken somehow.

on the picture 1 below it is seen that I have the note titled 'mail.ru webdav'. on the picture 2 below it is seen that as a result of the search query "webdav" the existing note has not been found, that is, search doesn't work as it should.

the note is text-only, small, opens normally in joplin gui, opens normally as a file in the sync target.

i need to rebuild the search index. how can this be done? if not possible using GUI, any command is ok.

i waited for about 6 hours to let the application rebuild the search index in background but the problem remains.

pic1

pic2

LOG
log.txt (27.8 KB)
(i removed manually the notes body content from the log)

joplin version 2.9.17
os arch linux

UPDATE
i successfully rebuilt the index by using the option in gui "delete local data and redownload from sync target". however, it took a very long time since I have 11k notes + 7k resources. so, a method how to rebuild the search index on request without deleting local data would be appreciated.

this is what I had after the fix:

thus, two more notes have appeared: the one called 'mail.ru webdav' and another one called 'joplin webdav'

[roman_r_m]

You could try to export all your data, delete the profile, and import back again. Not 100% sure if this will rebuild the index though.

[sergei_mipt]

@roman_r_m hi, Roman. thanks for the advice.
this is what I did and it fixed the problem.
however, this is a very costly fix in terms of time i spent (i have 11k notes).

during my evernote times, i had a similar problem: some notes that were in my database could not be retrieved via correct search query. i contacted evernote support and they conducted a rebuild of my search index. the problem had been solved with this.

the conclusion is

1. database sometimes gets corrupted
2. there should be a command the user may execute that rebuilds the index.

by the way, such a command exists, i guess, in the mobile version of joplin. it is called 'fix the search index'.

so, a command a user may run that rebuilds the index would be very helpful, imho

[kai452]

Hi,
what's about "DB Browser for SQlite"?
Menu - Tools: There are different points to try

[sergei_mipt]

@kai452
hi Kai, thanks for your effort

this is an interesting direction!

i installed both sqlite and sqlitebrowser

i opened the corrupted database (i saved it)

if i query the 'notes' table it finds the missing note:

if i query the 'notes_normalized' table it doesn't find the missing note:

so, the problem is possibly with this table 'notes_normalized', it needs to be rebuilt

another question: i didn't find the index of title vs note_id (see the pic below). where is it? and how can these be rebuilt from scratch (sqlite command)?

UPDATE
i found out that the missing note with the id 'f3b902b4ff744679a3fa4be65f4b92af' is present in 'notes' table, and is NOT present in 'notes_normalized' table

[kai452]

Hi Sergei,

You'r welcome.

Most such problems with database are results of broken indexes.
So the tool will fix the entire database for You.

You asked, how to find the ID for a title:
The ID is what shown on first picture, table notes, same as the note-ID shown in Joplin self.

To find an ID for a certain title, just insert the search-phrase, also part of it, into the field above header of title:

[sergei_mipt]

@kai452
Kai, thanks

i am not a database engineer, so, let me explain my problem in plain language:

1. the note with the id 'f3b902b4ff744679a3fa4be65f4b92af' is MISSING in the table 'notes_normalized' of database.sqlite but is PRESENT in the table 'notes' of database.sqlite.
   this is a bug. how can it be fixed?

2. what should i do to be able to find the note using Joplin GUI (without use of sqlitebrowser)?
   at present, Joplin doesn't work as expected: it doesn't find the note which does exist in the database (in the table 'notes' in database.sqlite as shown in the pic in the previous comment)

[kai452]

Hi Sergei,

if You have already tested the tools insite Sqlite Browser then take this as a new chance:
https://joplin-utils.rxliuli.com/web/joplin-batch-web/#/notFoundResource

[sergei_mipt]

@kai452 Kai, thanks. I'll have a look.