Failed to connect to search database: disk I/O error

Frappe Framework
1

I keep having this error in my deployment after I upgraded the frappe to v16 and helpdesk module to 1.29.

Traceback with variables (most recent call last):
File “apps/frappe/frappe/search/sqlite_search.py”, line 1197, in _get_connection
self.set_pragmas(cursor, read_only)
self = helpdesk.search_sqlite.HelpdeskSearch(db_name=‘helpdesk_search.db’, db_path=‘./ibratro.connect.cloudplay.cloud/helpdesk_search.db’, doc_configs={‘HD Ticket’: {‘fields’: [‘name’, ‘subject’, ‘description’, ‘modified’, ‘agent_group’, ‘status’, ‘priority’, ‘raised_by’, ‘owner’], ‘field_mappings’: {‘title’: ‘subject’, ‘content’: ‘description’}, ‘content_field’: ‘description’, ‘title_field’: ‘subject’, ‘modified_field’: ‘modified’, ‘filters’: {}}, ‘HD Ticket Comment’: {‘fields’: [‘name’, ‘content’, ‘modified’, ‘reference_ticket’, ‘commented_by’, ‘owner’], ‘field_mappings’: {}, ‘content_field’: ‘content’, ‘title_field’: None, ‘modified_field’: ‘modified’, ‘filters’: {}}, ‘Communication’: {‘field…icket’}}}, schema={‘metadata_fields’: [‘agent_group’, ‘customer’, ‘status’, ‘priority’, ‘owner’, ‘reference_doctype’, ‘reference_name’, ‘reference_ticket’, ‘doctype’, ‘name’, ‘modified’], ‘tokenizer’: 'unicode61 remove_diacritics 2 tokenchars '-‘’, ‘text_fields’: [‘title’, ‘content’]}, warnings=)
read_only = True
conn = sqlite3.Connection()
cursor = sqlite3.Cursor()
e = disk I/O error
File “apps/frappe/frappe/search/sqlite_search.py”, line 1208, in set_pragmas
cursor.execute(“PRAGMA journal_mode = WAL;”) # Write-Ahead Logging for concurrency
self = helpdesk.search_sqlite.HelpdeskSearch(db_name=‘helpdesk_search.db’, db_path=‘./ibratro.connect.cloudplay.cloud/helpdesk_search.db’, doc_configs={‘HD Ticket’: {‘fields’: [‘name’, ‘subject’, ‘description’, ‘modified’, ‘agent_group’, ‘status’, ‘priority’, ‘raised_by’, ‘owner’], ‘field_mappings’: {‘title’: ‘subject’, ‘content’: ‘description’}, ‘content_field’: ‘description’, ‘title_field’: ‘subject’, ‘modified_field’: ‘modified’, ‘filters’: {}}, ‘HD Ticket Comment’: {‘fields’: [‘name’, ‘content’, ‘modified’, ‘reference_ticket’, ‘commented_by’, ‘owner’], ‘field_mappings’: {}, ‘content_field’: ‘content’, ‘title_field’: None, ‘modified_field’: ‘modified’, ‘filters’: {}}, ‘Communication’: {‘field…icket’}}}, schema={‘metadata_fields’: [‘agent_group’, ‘customer’, ‘status’, ‘priority’, ‘owner’, ‘reference_doctype’, ‘reference_name’, ‘reference_ticket’, ‘doctype’, ‘name’, ‘modified’], ‘tokenizer’: 'unicode61 remove_diacritics 2 tokenchars '-‘’, ‘text_fields’: [‘title’, ‘content’]}, warnings=)
cursor = sqlite3.Cursor()
is_read = True
sqlite3.OperationalError: disk I/O error

And apparently this error is affecting users from logging into the ERPNext, submitting SO, DN, and many things else. Users have to keep retrying a few times before they can login or submit orders. They get Server Error.- RecursionError: maximum recursion depth exceeded.

The deployment is in a kubernetes cluster and since we need ReadWriteMany type of storage for sites directory, we use NFS storage. And I think this is the problem. Helpdesk (and Gameplan) module is building the search index to a sqlite database and fails. WAL needs a -shm file that SQLite memory-maps, and NFS can’t provide that.

What will be the best approach to get this solved?

2

The issue is most likely caused by Frappe v16/Helpdesk 1.29 using SQLite search indexes with WAL mode on the NFS-mounted sites directory. SQLite WAL requires local filesystem support for the .db-wal and .db-shm files, which can cause the disk I/O error on NFS.

Recommended solution: Keep the Frappe sites directory on NFS for RWX, but move the Helpdesk/Gameplan SQLite search databases to local pod storage (emptyDir or local PV), and then rebuild the search indexes.

This should prevent the SQLite errors from affecting login, SO/DN submissions, and other operations. I would not recommend simply disabling WAL, as SQLite on NFS can still have locking/concurrency issues.

We can connect and discuss the implementation approach if needed.

3

Which helpdesk branch are you?

4

I am using the helpdesk main branch.

I implemented this override and put the search index under /home/frappe/frappe-branch/search-index which is a local PV and it seems OK now.

import os
import frappe
from frappe.search import sqlite_search

INDEX_DIR = os.environ.get(“FRAPPE_SEARCH_INDEX_DIR”, “/home/frappe/frappe-bench/search-index”)

def _get_db_path(self, is_temp=False):
  site = getattr(frappe.local, "site", None) or "site"
  index_dir = os.path.join(INDEX_DIR, site)
  os.makedirs(index_dir, *exist_ok*=True)
  db_path = os.path.join(index_dir, self.db_name)
  if is_temp:
    return db_path.replace(".db", ".temp.db")
  return db_path

def _patch_class(cls):
  cls.\_get_db_path = \_get_db_path

def apply():
  _patch_class(sqlite_search.SQLiteSearch)
  for cls in sqlite_search.SQLiteSearch._subclasses_():
    _patch_class(cls)

  try:
    from helpdesk.search_sqlite import HelpdeskSearch

    _patch_class(HelpdeskSearch)
  except ImportError:
    pass

  if not getattr(sqlite_search.build_index, “_itechstro_patched”, False):
    original = sqlite_search.build_index

    def wrapped_build_index(*args, **kwargs):
      apply()
      return original(*args, **kwargs)

    wrapped_build_index.\__name_\_ = original.\__name_\_
    wrapped_build_index.\__module_\_ = original.\__module_\_
    wrapped_build_index.\_itechstro_patched = True
    sqlite_search.build_index = wrapped_build_index

def build_index(search_class_path=None, force=True, **kwargs):
  “”“Apply the off-NFS path patch, then build. Use this from `bench execute`.”“”
  apply()
  if search_class_path:
    kwargs\["search_class_path"\] = search_class_path
  kwargs.setdefault("force", force)
  return sqlite_search.build_index(**kwargs)

Each site will have its own folder under the search-index folder. And each pod will have the copy locally.