EmacSQL had an unhandled condition

Troubleshooting
1

I have been using the package fine (currently the newest version). However, today when I invoke org-roam-node-find, error shows up as EmacSQL had an unhandled condition. I traced with it and here is the output:

Debugger entered--Lisp error: (emacsql-error)
  signal(emacsql-error nil)
  #f(compiled-function (connection message) #<bytecode 0x3004cbfdd254da9>)(#<emacsql-sqlite-builtin-connection emacsql-sqlite-builtin-connection-10239f1ff29c> "\nSELECT\n  title,\n  aliases,\n\n  id,\n  file,\n  filetitle,\n  \"level\",\n  todo,\n\n  pos,\n  priority ,\n  scheduled ,\n  deadline ,\n  properties ,\n\n  olp,\n  atime,\n  mtime,\n  '(' || group_concat(tags, ' ') || ')' as tags,\n  refs\nFROM\n  (\n  SELECT\n    id,\n    file,\n    filetitle,\n    \"level\",\n    todo,\n    pos,\n    priority ,\n    scheduled ,\n    deadline ,\n    title,\n    properties ,\n    olp,\n    atime,\n    mtime,\n    tags,\n    '(' || group_concat(aliases, ' ') || ')' as aliases,\n    refs\n  FROM\n    (\n    SELECT\n      nodes.id as id,\n      nodes.file as file,\n      nodes.\"level\" as \"level\",\n      nodes.todo as todo,\n      nodes.pos as pos,\n      nodes.priority as priority,\n      nodes.scheduled as scheduled,\n      nodes.deadline as deadline,\n      nodes.title as title,\n      nodes.properties as properties,\n      nodes.olp as olp,\n      files.atime as atime,\n      files.mtime as mtime,\n      files.title as filetitle,\n      tags.tag as tags,\n      aliases.alias as aliases,\n      '(' || group_concat(RTRIM (refs.\"type\", '\"') || ':' || LTRIM(refs.ref, '\"'), ' ') || ')' as refs\n    FROM nodes\n    LEFT JOIN files ON files.file = nodes.file\n    LEFT JOIN tags ON tags.node_id = nodes.id\n    LEFT JOIN aliases ON aliases.node_id = nodes.id\n    LEFT JOIN refs ON refs.node_id = nodes.id\n    GROUP BY nodes.id, tags.tag, aliases.alias )\n  GROUP BY id, tags )\nGROUP BY id\n;")
  apply(#f(compiled-function (connection message) #<bytecode 0x3004cbfdd254da9>) (#<emacsql-sqlite-builtin-connection emacsql-sqlite-builtin-connection-10239f1ff29c> "\nSELECT\n  title,\n  aliases,\n\n  id,\n  file,\n  filetitle,\n  \"level\",\n  todo,\n\n  pos,\n  priority ,\n  scheduled ,\n  deadline ,\n  properties ,\n\n  olp,\n  atime,\n  mtime,\n  '(' || group_concat(tags, ' ') || ')' as tags,\n  refs\nFROM\n  (\n  SELECT\n    id,\n    file,\n    filetitle,\n    \"level\",\n    todo,\n    pos,\n    priority ,\n    scheduled ,\n    deadline ,\n    title,\n    properties ,\n    olp,\n    atime,\n    mtime,\n    tags,\n    '(' || group_concat(aliases, ' ') || ')' as aliases,\n    refs\n  FROM\n    (\n    SELECT\n      nodes.id as id,\n      nodes.file as file,\n      nodes.\"level\" as \"level\",\n      nodes.todo as todo,\n      nodes.pos as pos,\n      nodes.priority as priority,\n      nodes.scheduled as scheduled,\n      nodes.deadline as deadline,\n      nodes.title as title,\n      nodes.properties as properties,\n      nodes.olp as olp,\n      files.atime as atime,\n      files.mtime as mtime,\n      files.title as filetitle,\n      tags.tag as tags,\n      aliases.alias as aliases,\n      '(' || group_concat(RTRIM (refs.\"type\", '\"') || ':' || LTRIM(refs.ref, '\"'), ' ') || ')' as refs\n    FROM nodes\n    LEFT JOIN files ON files.file = nodes.file\n    LEFT JOIN tags ON tags.node_id = nodes.id\n    LEFT JOIN aliases ON aliases.node_id = nodes.id\n    LEFT JOIN refs ON refs.node_id = nodes.id\n    GROUP BY nodes.id, tags.tag, aliases.alias )\n  GROUP BY id, tags )\nGROUP BY id\n;"))
  #f(compiled-function (&rest args) #<bytecode 0x1fcbe5a0a861f3c7>)(#<emacsql-sqlite-builtin-connection emacsql-sqlite-builtin-connection-10239f1ff29c> "\nSELECT\n  title,\n  aliases,\n\n  id,\n  file,\n  filetitle,\n  \"level\",\n  todo,\n\n  pos,\n  priority ,\n  scheduled ,\n  deadline ,\n  properties ,\n\n  olp,\n  atime,\n  mtime,\n  '(' || group_concat(tags, ' ') || ')' as tags,\n  refs\nFROM\n  (\n  SELECT\n    id,\n    file,\n    filetitle,\n    \"level\",\n    todo,\n    pos,\n    priority ,\n    scheduled ,\n    deadline ,\n    title,\n    properties ,\n    olp,\n    atime,\n    mtime,\n    tags,\n    '(' || group_concat(aliases, ' ') || ')' as aliases,\n    refs\n  FROM\n    (\n    SELECT\n      nodes.id as id,\n      nodes.file as file,\n      nodes.\"level\" as \"level\",\n      nodes.todo as todo,\n      nodes.pos as pos,\n      nodes.priority as priority,\n      nodes.scheduled as scheduled,\n      nodes.deadline as deadline,\n      nodes.title as title,\n      nodes.properties as properties,\n      nodes.olp as olp,\n      files.atime as atime,\n      files.mtime as mtime,\n      files.title as filetitle,\n      tags.tag as tags,\n      aliases.alias as aliases,\n      '(' || group_concat(RTRIM (refs.\"type\", '\"') || ':' || LTRIM(refs.ref, '\"'), ' ') || ')' as refs\n    FROM nodes\n    LEFT JOIN files ON files.file = nodes.file\n    LEFT JOIN tags ON tags.node_id = nodes.id\n    LEFT JOIN aliases ON aliases.node_id = nodes.id\n    LEFT JOIN refs ON refs.node_id = nodes.id\n    GROUP BY nodes.id, tags.tag, aliases.alias )\n  GROUP BY id, tags )\nGROUP BY id\n;")
  apply(#f(compiled-function (&rest args) #<bytecode 0x1fcbe5a0a861f3c7>) #<emacsql-sqlite-builtin-connection emacsql-sqlite-builtin-connection-10239f1ff29c> "\nSELECT\n  title,\n  aliases,\n\n  id,\n  file,\n  filetitle,\n  \"level\",\n  todo,\n\n  pos,\n  priority ,\n  scheduled ,\n  deadline ,\n  properties ,\n\n  olp,\n  atime,\n  mtime,\n  '(' || group_concat(tags, ' ') || ')' as tags,\n  refs\nFROM\n  (\n  SELECT\n    id,\n    file,\n    filetitle,\n    \"level\",\n    todo,\n    pos,\n    priority ,\n    scheduled ,\n    deadline ,\n    title,\n    properties ,\n    olp,\n    atime,\n    mtime,\n    tags,\n    '(' || group_concat(aliases, ' ') || ')' as aliases,\n    refs\n  FROM\n    (\n    SELECT\n      nodes.id as id,\n      nodes.file as file,\n      nodes.\"level\" as \"level\",\n      nodes.todo as todo,\n      nodes.pos as pos,\n      nodes.priority as priority,\n      nodes.scheduled as scheduled,\n      nodes.deadline as deadline,\n      nodes.title as title,\n      nodes.properties as properties,\n      nodes.olp as olp,\n      files.atime as atime,\n      files.mtime as mtime,\n      files.title as filetitle,\n      tags.tag as tags,\n      aliases.alias as aliases,\n      '(' || group_concat(RTRIM (refs.\"type\", '\"') || ':' || LTRIM(refs.ref, '\"'), ' ') || ')' as refs\n    FROM nodes\n    LEFT JOIN files ON files.file = nodes.file\n    LEFT JOIN tags ON tags.node_id = nodes.id\n    LEFT JOIN aliases ON aliases.node_id = nodes.id\n    LEFT JOIN refs ON refs.node_id = nodes.id\n    GROUP BY nodes.id, tags.tag, aliases.alias )\n  GROUP BY id, tags )\nGROUP BY id\n;")
  emacsql-send-message(#<emacsql-sqlite-builtin-connection emacsql-sqlite-builtin-connection-10239f1ff29c> "\nSELECT\n  title,\n  aliases,\n\n  id,\n  file,\n  filetitle,\n  \"level\",\n  todo,\n\n  pos,\n  priority ,\n  scheduled ,\n  deadline ,\n  properties ,\n\n  olp,\n  atime,\n  mtime,\n  '(' || group_concat(tags, ' ') || ')' as tags,\n  refs\nFROM\n  (\n  SELECT\n    id,\n    file,\n    filetitle,\n    \"level\",\n    todo,\n    pos,\n    priority ,\n    scheduled ,\n    deadline ,\n    title,\n    properties ,\n    olp,\n    atime,\n    mtime,\n    tags,\n    '(' || group_concat(aliases, ' ') || ')' as aliases,\n    refs\n  FROM\n    (\n    SELECT\n      nodes.id as id,\n      nodes.file as file,\n      nodes.\"level\" as \"level\",\n      nodes.todo as todo,\n      nodes.pos as pos,\n      nodes.priority as priority,\n      nodes.scheduled as scheduled,\n      nodes.deadline as deadline,\n      nodes.title as title,\n      nodes.properties as properties,\n      nodes.olp as olp,\n      files.atime as atime,\n      files.mtime as mtime,\n      files.title as filetitle,\n      tags.tag as tags,\n      aliases.alias as aliases,\n      '(' || group_concat(RTRIM (refs.\"type\", '\"') || ':' || LTRIM(refs.ref, '\"'), ' ') || ')' as refs\n    FROM nodes\n    LEFT JOIN files ON files.file = nodes.file\n    LEFT JOIN tags ON tags.node_id = nodes.id\n    LEFT JOIN aliases ON aliases.node_id = nodes.id\n    LEFT JOIN refs ON refs.node_id = nodes.id\n    GROUP BY nodes.id, tags.tag, aliases.alias )\n  GROUP BY id, tags )\nGROUP BY id\n;")
  #f(compiled-function (connection sql &rest args) #<bytecode -0x97bff269bbac00b>)(#<emacsql-sqlite-builtin-connection emacsql-sqlite-builtin-connection-10239f1ff29c> "\nSELECT\n  title,\n  aliases,\n\n  id,\n  file,\n  filetitle,\n  \"level\",\n  todo,\n\n  pos,\n  priority ,\n  scheduled ,\n  deadline ,\n  properties ,\n\n  olp,\n  atime,\n  mtime,\n  '(' || group_concat(tags, ' ') || ')' as tags,\n  refs\nFROM\n  (\n  SELECT\n    id,\n    file,\n    filetitle,\n    \"level\",\n    todo,\n    pos,\n    priority ,\n    scheduled ,\n    deadline ,\n    title,\n    properties ,\n    olp,\n    atime,\n    mtime,\n    tags,\n    '(' || group_concat(aliases, ' ') || ')' as aliases,\n    refs\n  FROM\n    (\n    SELECT\n      nodes.id as id,\n      nodes.file as file,\n      nodes.\"level\" as \"level\",\n      nodes.todo as todo,\n      nodes.pos as pos,\n      nodes.priority as priority,\n      nodes.scheduled as scheduled,\n      nodes.deadline as deadline,\n      nodes.title as title,\n      nodes.properties as properties,\n      nodes.olp as olp,\n      files.atime as atime,\n      files.mtime as mtime,\n      files.title as filetitle,\n      tags.tag as tags,\n      aliases.alias as aliases,\n      '(' || group_concat(RTRIM (refs.\"type\", '\"') || ':' || LTRIM(refs.ref, '\"'), ' ') || ')' as refs\n    FROM nodes\n    LEFT JOIN files ON files.file = nodes.file\n    LEFT JOIN tags ON tags.node_id = nodes.id\n    LEFT JOIN aliases ON aliases.node_id = nodes.id\n    LEFT JOIN refs ON refs.node_id = nodes.id\n    GROUP BY nodes.id, tags.tag, aliases.alias )\n  GROUP BY id, tags )\nGROUP BY id\n")
  apply(#f(compiled-function (connection sql &rest args) #<bytecode -0x97bff269bbac00b>) #<emacsql-sqlite-builtin-connection emacsql-sqlite-builtin-connection-10239f1ff29c> "\nSELECT\n  title,\n  aliases,\n\n  id,\n  file,\n  filetitle,\n  \"level\",\n  todo,\n\n  pos,\n  priority ,\n  scheduled ,\n  deadline ,\n  properties ,\n\n  olp,\n  atime,\n  mtime,\n  '(' || group_concat(tags, ' ') || ')' as tags,\n  refs\nFROM\n  (\n  SELECT\n    id,\n    file,\n    filetitle,\n    \"level\",\n    todo,\n    pos,\n    priority ,\n    scheduled ,\n    deadline ,\n    title,\n    properties ,\n    olp,\n    atime,\n    mtime,\n    tags,\n    '(' || group_concat(aliases, ' ') || ')' as aliases,\n    refs\n  FROM\n    (\n    SELECT\n      nodes.id as id,\n      nodes.file as file,\n      nodes.\"level\" as \"level\",\n      nodes.todo as todo,\n      nodes.pos as pos,\n      nodes.priority as priority,\n      nodes.scheduled as scheduled,\n      nodes.deadline as deadline,\n      nodes.title as title,\n      nodes.properties as properties,\n      nodes.olp as olp,\n      files.atime as atime,\n      files.mtime as mtime,\n      files.title as filetitle,\n      tags.tag as tags,\n      aliases.alias as aliases,\n      '(' || group_concat(RTRIM (refs.\"type\", '\"') || ':' || LTRIM(refs.ref, '\"'), ' ') || ')' as refs\n    FROM nodes\n    LEFT JOIN files ON files.file = nodes.file\n    LEFT JOIN tags ON tags.node_id = nodes.id\n    LEFT JOIN aliases ON aliases.node_id = nodes.id\n    LEFT JOIN refs ON refs.node_id = nodes.id\n    GROUP BY nodes.id, tags.tag, aliases.alias )\n  GROUP BY id, tags )\nGROUP BY id\n")
  emacsql(#<emacsql-sqlite-builtin-connection emacsql-sqlite-builtin-connection-10239f1ff29c> "\nSELECT\n  title,\n  aliases,\n\n  id,\n  file,\n  filetitle,\n  \"level\",\n  todo,\n\n  pos,\n  priority ,\n  scheduled ,\n  deadline ,\n  properties ,\n\n  olp,\n  atime,\n  mtime,\n  '(' || group_concat(tags, ' ') || ')' as tags,\n  refs\nFROM\n  (\n  SELECT\n    id,\n    file,\n    filetitle,\n    \"level\",\n    todo,\n    pos,\n    priority ,\n    scheduled ,\n    deadline ,\n    title,\n    properties ,\n    olp,\n    atime,\n    mtime,\n    tags,\n    '(' || group_concat(aliases, ' ') || ')' as aliases,\n    refs\n  FROM\n    (\n    SELECT\n      nodes.id as id,\n      nodes.file as file,\n      nodes.\"level\" as \"level\",\n      nodes.todo as todo,\n      nodes.pos as pos,\n      nodes.priority as priority,\n      nodes.scheduled as scheduled,\n      nodes.deadline as deadline,\n      nodes.title as title,\n      nodes.properties as properties,\n      nodes.olp as olp,\n      files.atime as atime,\n      files.mtime as mtime,\n      files.title as filetitle,\n      tags.tag as tags,\n      aliases.alias as aliases,\n      '(' || group_concat(RTRIM (refs.\"type\", '\"') || ':' || LTRIM(refs.ref, '\"'), ' ') || ')' as refs\n    FROM nodes\n    LEFT JOIN files ON files.file = nodes.file\n    LEFT JOIN tags ON tags.node_id = nodes.id\n    LEFT JOIN aliases ON aliases.node_id = nodes.id\n    LEFT JOIN refs ON refs.node_id = nodes.id\n    GROUP BY nodes.id, tags.tag, aliases.alias )\n  GROUP BY id, tags )\nGROUP BY id\n")
  org-roam-db-query("\nSELECT\n  title,\n  aliases,\n\n  id,\n  file,\n  filetitle,\n  \"level\",\n  todo,\n\n  pos,\n  priority ,\n  scheduled ,\n  deadline ,\n  properties ,\n\n  olp,\n  atime,\n  mtime,\n  '(' || group_concat(tags, ' ') || ')' as tags,\n  refs\nFROM\n  (\n  SELECT\n    id,\n    file,\n    filetitle,\n    \"level\",\n    todo,\n    pos,\n    priority ,\n    scheduled ,\n    deadline ,\n    title,\n    properties ,\n    olp,\n    atime,\n    mtime,\n    tags,\n    '(' || group_concat(aliases, ' ') || ')' as aliases,\n    refs\n  FROM\n    (\n    SELECT\n      nodes.id as id,\n      nodes.file as file,\n      nodes.\"level\" as \"level\",\n      nodes.todo as todo,\n      nodes.pos as pos,\n      nodes.priority as priority,\n      nodes.scheduled as scheduled,\n      nodes.deadline as deadline,\n      nodes.title as title,\n      nodes.properties as properties,\n      nodes.olp as olp,\n      files.atime as atime,\n      files.mtime as mtime,\n      files.title as filetitle,\n      tags.tag as tags,\n      aliases.alias as aliases,\n      '(' || group_concat(RTRIM (refs.\"type\", '\"') || ':' || LTRIM(refs.ref, '\"'), ' ') || ')' as refs\n    FROM nodes\n    LEFT JOIN files ON files.file = nodes.file\n    LEFT JOIN tags ON tags.node_id = nodes.id\n    LEFT JOIN aliases ON aliases.node_id = nodes.id\n    LEFT JOIN refs ON refs.node_id = nodes.id\n    GROUP BY nodes.id, tags.tag, aliases.alias )\n  GROUP BY id, tags )\nGROUP BY id\n")
  org-roam-node-list()
  org-roam-node-read--completions(nil nil)
  consult-org-roam-node-read(nil nil nil nil nil)
  apply(consult-org-roam-node-read (nil nil nil))
  org-roam-node-read(nil nil nil)
  org-roam-node-find()
  funcall-interactively(org-roam-node-find)
  command-execute(org-roam-node-find)

I tried to delete the .db file and let it rebuilt , but still no success. I downgrade the org-roam to melpa-stable and the same error shows up again. I tried to recompile all packages, and no success. Interestingly, org-roam-version yields funcall-interactively: Search failed: "\\(?:;; Version: \\([^z-a]*?$\\)\\)" ( for both the melpa and melpa-stable package version).

What issue am I facing here :(?

2

Hard to know what is happening but I would suggest that you remove consult-org-roam-node-read and see if this changes the behavior.

If the problem persists, ideally you should reproduce the problem with vanilla Emacs with only Org-roam and its dependencies, then report details to the project GitHub.

3

Thanks for the reply. I deleted the consult-org-roam-node-read package and actually went ahead to re-install emacs on my machine. The problem still persisted with those emacsql-sqlite-builtin-connection thing.

Then, I decided to troubleshoot by initiating only two packages org-roam and org in the init file , and used a few org files (with ID numbers) in a org-roam-directory to test. The emacs seemed to work like usual where I can invoke the org-roam-node-find and other commands. The db file was built without issue. I was also able to create a new node and org-roam-db-sync like usual.

So, I am worried that I possibly have corrupted some org files in the org-roam-directory. Is this the case here? I have like 200 org files and how can I know or figure out which files are good or bad :frowning: ?

BTW, when I checked org-roam-version, there was error saying funcall-interactively: Search failed: "\\(?:;; Version: \\([^z-a]*?$\\)\\)". The backtrace gives

Debugger entered--Lisp error: (search-failed "\\(?:;; Version: \\([^z-a]*?$\\)\\)")
  org-roam-version()
  funcall-interactively(org-roam-version)
  command-execute(org-roam-version record)
  execute-extended-command(nil "org-roam-version" "org-roam-version")
  funcall-interactively(execute-extended-command nil "org-roam-version" "org-roam-version")
  command-execute(execute-extended-command)
4

Cannot tell if your issue is cases by any of your org files but it is easy to find out.

  1. Delete (or move) your db file
  2. Copy your org-roam-directory as a backup
  3. Go back to org-roam-directory and move half of the 200 to out of the directory, and create a new db file
  4. Delete the db file, and try the other half

Repeat the process with 50, 25,12 files to narrow down the list of files. I am assuming that one or only a few file(s) cause the problem.

For the org-roam-version issue, I would suggest that it is not related to your db problem, and that you raise an issue on the GitHub repository.

5

After some thoughts, I made some guess on what caused these sql problems, especially after I edited these org files. But, I still went ahead to try your suggestion. I sieved through the files and pin-pointed to one specific file that I recently worked on. And my guess was right. It was the :ID property that got messed. I deleted it and re-create a new ID, org-roam-db-sync and everything is back.

So, I wonder if org/org-roam has the feature to hard-lock these ID and numbers as read-only on the buffer when the files are worked on. Thankfully, my org files barely have any internal link, but I can envision how one mis-step of keyboard movement can in-sanitize these ID numbers and possibly break the sql database syncing.

Anyway, thanks for the great suggestion and I was happy to solve this issue.

1 Like