Cannot build database after the update

[hyan46]

Description

Cannot build the org-roam database after the update

Steps to Reproduce

1. Load Emacs
2. Run org-roam-db—clear
3. Run org-roam-db-build-caches

Shows an error:
“EmacSQL had an unhandled condition: "near line 985: UNIQUE constraint failed: headlines.id"”

Backtrace

Debugger entered--Lisp error: (emacsql-error "near line 985: UNIQUE constraint failed: headlines.id")
signal(emacsql-error ("near line 985: UNIQUE constraint failed: headlines.id"))
#f(compiled-function (conn) #<bytecode 0x1136095>)(#<emacsql-sqlite3-connection emacsql-sqlite3-connec$
apply(#f(compiled-function (conn) #<bytecode 0x1136095>) #<emacsql-sqlite3-connection emacsql-sqlite3-$
emacsql-parse(#)
#f(compiled-function (connection sql &rest args) "Send SQL s-expression to CONNECTION and return the r$
apply(#f(compiled-function (connection sql &rest args) "Send SQL s-expression to CONNECTION and return$
emacsql(# [:insert :into headlines :values $v1]$
apply(emacsql # [:insert :into headlines :value$
org-roam-db-query([:insert :into headlines :values $v1] ((["_60q30c1g60o30e1i60o4ac1g60rj8gpl88rj2c1h8$
org-roam-db-build-cache(nil)
funcall-interactively(org-roam-db-build-cache nil)
call-interactively(org-roam-db-build-cache record nil)
command-execute(org-roam-db-build-cache record)
counsel-M-x-action("org-roam-db-build-cache")
ivy-call()
ivy-read("M-x " ("org-roam-db--clear" "toggle-debug-on-error" "org-roam-db-build-cache" "org-roam-find$
counsel-M-x()
funcall-interactively(counsel-M-x)

Expected Results

Actual Results

Environment

• Emacs: GNU Emacs 26.3 (build 1, x86_64-pc-linux-gnu, GTK+ Version 3.22.$
  of 2019-12-02

• Framework: Doom

• Org: Org mode version 9.4 (nil @ /home/haoyan/.emacs.d/.local/straight/$

• Org-roam: 1.2.0

• Org-roam commit: 2eb0aac

[progfolio]

First thing I would check is if you have any duplicate ID's in your files.
Try evaluating this in the *scratch* buffer:

(let ((org-id-locations org-roam-directory)
      org-agenda-files)
  (org-id-update-id-locations))

If you have duplicate IDs, the *Warnings* buffer should display something similar to:

Warning (emacs): WARNING: 1 duplicate IDs found, check *Messages* buffer

As the warning indicates, *Messages* buffer will also display duplicate IDs:

Duplicate ID  (UUID)

[hyan46]

I think that might be related since I sometimes run the org-roam from another computer and not sure if this is related or may duplicate the ID?

I copied everything in the *scratch* buffer, and run eval-print-last-sexp and get something like

#s(hash-table size 1 test equal rehash-size 1.5 rehash-threshold 0.8125 data ())

Not sure if this is related but haven't seen the messages. I don't find a *Warning* buffer and the *Messages* buffer has:

0 files scanned, 0 files contains IDs, and 0 IDs found.

If I tried to build the org-roam-db-build-caches, it always shows the:

EmacSQL had an unhandled condition: "near line 985: UNIQUE constraint failed: headlines.id"

Am I doing correctly?

[zaeph]

You might need to force the rebuild of the file with C-u M-x org-roam-db-build-cache. Your collection might have been left in an unstable state.

…Which makes me wonder if we should catch those errors during the builds and wipe the DB when that happens. 🤔

[hyan46]

Thank you for the comments. I just moved to org-roam and Emacs a month ago, so I still can not locate the problem myself. So I would just report the results from your suggestions.

For example, when I run the c-u M-x org-roam-db-build-cache.
It has the following warning:

EmacSQL had an unhandled condition: "near line 1003: UNIQUE constraint failed: headlines.id"

Previously it was on Line 985 so something does change but org-roam-find still can not find anything. I tried this on my Ubuntu and Mac OS and it seems both have the same issue. I am on Doom emacs for both machines.

[zaeph]

Following @progfolio's recommendation, could you try to eval this:

(let ((org-id-files (org-roam--list-files org-roam-directory))
      org-agenda-files)
  (org-id-update-id-locations))

[hyan46]

Thanks, the results shown is that:

Warning (emacs): WARNING: 35 duplicate IDs found, check *Messages* buffer

In the *Messages*, it shows many

Finding ID locations "xxx"
Duplicate ID "xxxxx"
Undo-Fu-Session discarding undo data: file length mismatch
81 files scanned, 1 files contains IDs, and 44 IDs found.

So that may be related to the duplicate IDs. How to solve this issue?
I am not sure how the duplicate ID actually occurs, since my org files seem not duplicate.

[zaeph]

I'm afraid you're going to have to manually go through your :ID: properties to know which ones are duplicates. I recommend you use a tool like ripgrep to search the IDs which are mentioned in your *Messages* buffer, and that you ask yourself whether you need to keep them. Depending on whether you actually used those IDs before, you might be better off wiping them off entirely and starting afresh.

[hyan46]

Thanks! Found all the problems are due to the org-gcal somehow duplicate events and IDs. Just fixed that and everything works perfect.

[das-s]

If someone runs into this issue due to having lots of org-IDs in their files, this is a quick and dirty way to remove the IDs (backup your files first!):

rg --files-with-matches ':ID:' '/home/das-s/org' | xargs sed -i '/:ID:/d' 

[cpbotha]

I just ran into this issue. In my case it turned out to be dropbox file conflicts I had accumulated over the past 6 years of org files, and the fact that I often attach files to headings, which adds the :ID: property.

The code in @zaeph 's comment #811 (comment) was exactly what I needed to figure out the issue and fix. Thanks!

[qualiaa]

I'm also having this problem, with org-roam-db-build-cache reporting the error:

EmacSQL had an unhandled condition: "near line 2095: UNIQUE constraint failed: headlines.id"

I first assumed that the database required the first top-level headline for each file to be unique (not true of my files), but changing these headlines has not fixed the issue.

I do not have many files containing an :ID: tag, the output of

(let ((org-id-files (org-roam--list-files org-roam-directory))
      org-agenda-files)
  (org-id-update-id-locations))

yields

351 files scanned, 7 files contains IDs and in total 6 IDs found.
#s(hash-table size 14 test equal rehash-size 1.5 rehash-threshold 0.8125 data (
   ; list of files...
))

This has rendered org-roam unusable for about two weeks :/

[jcintasr]

Hello, I know it is closed but just in case it's useful for anybody landing in the same issue:
I had org-roam linked to my org-journal too, my problem was that for some reason an entry for a day had two :ID: properties. I solved it erasing the org-roam.db, re-doing the entry for that day and rebuilding the database with org-roam-db-build-caches.

To solve it, the code from #811 (comment) was key to point me out to my error.