Potential issue between `org-mem-roamy-do-overwrite-real-db` and org-roam packages

[krisbalintona]

Hi,

Seems like I'm getting an error when I enable org-mem-roamy-db-mode and set org-mem-roamy-do-overwrite-real-db to non-nil. I'm not sure if there's something wrong on my end or not.

This happens when I use org-roam-ql's org-roam-ql-nodes to query the org-roam database (which is "overwritten" by org-mem).

In any case, here is the full backtrace:

Debugger entered--Lisp error: (emacsql-error)
  signal(emacsql-error nil)
  #f(compiled-function (connection message) #<bytecode -0x1da4e7c269399b60>)(#<emacsql-sqlite-builtin-connection 215daab> "\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 -0x1da4e7c269399b60>) (#<emacsql-sqlite-builtin-connection 215daab> "\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 0xe29c7e3e69d7157>)(#<emacsql-sqlite-builtin-connection 215daab> "\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 0xe29c7e3e69d7157>) #<emacsql-sqlite-builtin-connection 215daab> "\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 215daab> "\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 -0xac78385684925fe>)(#<emacsql-sqlite-builtin-connection 215daab> "\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 -0xac78385684925fe>) #<emacsql-sqlite-builtin-connection 215daab> "\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 215daab> "\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(emacsql #<emacsql-sqlite-builtin-connection 215daab> "\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" nil)
  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-folgezettel-list--objects()
  make-vtable(:columns ((:name "Index" :align left :formatter org-roam-folgezettel--index-formatter) (:name "Path" :align left :formatter org-roam-folgezettel--path-formatter) (:name "Tags" :align right :formatter org-roam-folgezettel--tags-formatter)) :keymap (keymap (47 keymap (105 keymap (100 . org-roam-folgezettel-filter-descendants) (99 . org-roam-folgezettel-filter-children)) (98 . org-roam-folgezettel-filter-buffer) (110 . org-roam-folgezettel-filter-title) (116 . org-roam-folgezettel-filter-tags) (112 . org-roam-folgezettel-filter-person) (100 . org-roam-folgezettel-filter-directory) (47 . org-roam-folgezettel-filter-query-edit) (63 . org-roam-folgezettel-filter-menu)) (67108927 . org-roam-folgezettel-filter-redo) (67108911 . org-roam-folgezettel-filter-undo) (11 . org-roam-folgezettel-kill-line) (115 . org-roam-folgezettel-goto-node) (119 . org-roam-folgezettel-store-link) (M-down . org-roam-folgezettel-move-down) (M-up . org-roam-folgezettel-move-up) (27 keymap (112 . org-roam-folgezettel-backward-sibling) (110 . org-roam-folgezettel-forward-sibling) (100 . org-roam-folgezettel-downward) (117 . org-roam-folgezettel-upward)) (105 . org-roam-folgezettel-edit-index) (15 . org-roam-folgezettel-display-node) (111 . org-roam-folgezettel-open-node-other-window) (13 . org-roam-folgezettel-open-node) (103 . org-roam-folgezettel-refresh) (110 . next-line) (112 . previous-line)) :objects-function org-roam-folgezettel-list--objects :getter org-roam-folgezettel-list--getter :displayer org-roam-folgezettel-list--displayer :separator-width 2 :use-header-line t)
  org-roam-folgezettel-list(nil nil)
  funcall-interactively(org-roam-folgezettel-list nil nil)
  #<subr call-interactively>(org-roam-folgezettel-list nil nil)
  bufferlo--interactive-advice(#<subr call-interactively> org-roam-folgezettel-list nil nil)
  apply(bufferlo--interactive-advice #<subr call-interactively> (org-roam-folgezettel-list nil nil))
  call-interactively(org-roam-folgezettel-list nil nil)
  command-execute(org-roam-folgezettel-list)

[meedstrom]

Is there a buffer something like *SQL errors*? (Emacsql often creates such a buffer)

[krisbalintona]

Hmmm, strange... I was reliably reproducing the issue but for some reason it's disappeared now. Don't remember if I updated org-mem in between...

In any case, I'll leave this issue open for a few more days and will report back if the problem returns. If not, I'll close the issue 👍

[krisbalintona]

Update:

Previously I thought it was an issue with my emacsql installation, but I no longer believe that is the case. For instance, evaluating the following yields no errors:

(progn
  (setopt org-roam-db-update-on-save t
          org-mem-roamy-do-overwrite-real-db nil)
  (org-roam-db-autosync-mode 1)
  (org-mem-roamy-db-mode -1))

but then evaluating the following yields the same error I've described previously:

(progn
  (setopt org-roam-db-update-on-save nil
          org-mem-roamy-do-overwrite-real-db t)
  (org-roam-db-autosync-mode -1)
  (org-mem-roamy-db-mode 1))

Not sure how to debug this... Is the database that org-mem is writing erroneous somehow?