/frappe-errors-database

name: frappe-errors-database
description: >
  Use when handling database errors in Frappe/ERPNext. Covers
  DuplicateEntryError, LinkValidationError, MandatoryError,
  TimestampMismatchError, CharacterLengthExceededError, InReadOnlyMode,
  QueryTimeoutError, SQL injection errors, frappe.db.sql parameter format
  (% vs %s), get_value returning None, transaction deadlocks, MariaDB gone
  away, too many connections. Error-to-fix mapping for v14/v15/v16.
  Keywords: database error, DuplicateEntryError, TimestampMismatchError,, MariaDB error, MySQL error, column not found, table missing, duplicate entry, database crash.
  SQL injection, deadlock, MariaDB gone away, query timeout.
license: MIT
compatibility: "Claude Code, Claude.ai Projects, Claude API. Frappe v14-v16."
metadata:
  author: OpenAEC-Foundation
  version: "2.0"

Frappe Database Error Diagnosis & Resolution

Cross-ref: `frappe-core-database` (API syntax), `frappe-errors-controllers` (controller errors).

---

Error-to-Fix Mapping Table

| Error / Exception | HTTP | Cause | Fix | |-------------------|------|-------|-----| | `DuplicateEntryError` | 409 | Unique constraint violation on insert/rename | Check existence first OR catch and return existing | | `DoesNotExistError` | 404 | `get_doc()` on missing record | Use `frappe.db.exists()` first OR catch exception | | `LinkValidationError` | 417 | Link field points to non-existent record | Validate link target exists before save | | `LinkExistsError` | N/A | Delete blocked by linked documents | Show linked docs to user; use `force=True` carefully | | `MandatoryError` | 417 | Required field is empty on save | Set all mandatory fields before insert/save | | `TimestampMismatchError` | N/A | Concurrent edit detected (`modified` changed) | Reload doc and retry, or inform user to refresh | | `CharacterLengthExceededError` | 417 | String exceeds field maxlength / DB column size | Truncate input or increase field length | | `DataTooLongException` | 417 | Value exceeds DB column storage capacity | Same as CharacterLengthExceededError | | `InReadOnlyMode` | 503 | Write attempted during read-only mode | Check `frappe.flags.in_import` or site config | | `QueryTimeoutError` | N/A | Query exceeded time limit [v15+] | Add indexes, reduce result set, paginate | | `QueryDeadlockError` | N/A | Two transactions waiting on each other | Retry with backoff; reduce transaction scope | | `TooManyWritesError` | N/A | Excessive writes in single request | Batch operations; use background jobs | | `InternalError` (gone away) | N/A | MariaDB connection dropped | Reconnect with `frappe.db.connect()` | | `InternalError` (too many) | N/A | Connection pool exhausted | Check `max_connections`; close idle connections | | `ValidationError` | 417 | General validation failure in save | Read error message; fix field values | | SQL syntax error | N/A | Wrong `frappe.db.sql()` parameter format | Use `%(name)s` with dict, NOT `%s` with tuple |

---

Exception Hierarchy

Exception
├── frappe.ValidationError (HTTP 417)
│   ├── frappe.MandatoryError
│   ├── frappe.LinkValidationError
│   ├── frappe.CharacterLengthExceededError
│   ├── frappe.DataTooLongException
│   ├── frappe.UniqueValidationError
│   ├── frappe.UpdateAfterSubmitError
│   └── frappe.DataError
├── frappe.DoesNotExistError (HTTP 404)
├── frappe.DuplicateEntryError (HTTP 409)  ← inherits NameError
├── frappe.TimestampMismatchError
├── frappe.LinkExistsError
├── frappe.QueryTimeoutError
├── frappe.QueryDeadlockError
├── frappe.TooManyWritesError
├── frappe.InReadOnlyMode (HTTP 503)
└── frappe.db.InternalError  ← MariaDB/Postgres driver error

---

frappe.db.sql() Parameter Format

# ❌ WRONG — %s with positional tuple (works but fragile)
frappe.db.sql("SELECT * FROM `tabItem` WHERE name = %s", ("ITEM-001",))

# ❌ WRONG — f-string or .format() — SQL INJECTION!
frappe.db.sql(f"SELECT * FROM `tabItem` WHERE name = '{item_name}'")
frappe.db.sql("SELECT * FROM `tabItem` WHERE name = '{}'".format(item_name))

# ❌ WRONG — bare % operator
frappe.db.sql("SELECT * FROM `tabItem` WHERE name = '%s'" % item_name)

# ✅ CORRECT — named parameters with dict (ALWAYS use this)
frappe.db.sql(
    "SELECT * FROM `tabItem` WHERE name = %(name)s AND warehouse = %(wh)s",
    {"name": item_name, "wh": warehouse},
    as_dict=True
)

# ✅ CORRECT — frappe.qb (query builder, no injection risk)
Item = frappe.qb.DocType("Item")
result = (
    frappe.qb.from_(Item)
    .select(Item.name, Item.item_name)
    .where(Item.warehouse == warehouse)
    .run(as_dict=True)
)

**Rule**: ALWAYS use `%(name)s` with a dict parameter. NEVER use string formatting for SQL values.

---

get_value Returns None: Not an Exception

# ❌ DANGEROUS — get_value returns None, not raises
credit = frappe.db.get_value("Customer", "CUST-001", "credit_limit")
if credit > 1000:  # TypeError: '>' not supported between NoneType and int
    pass

# ✅ CORRECT — handle None explicitly
credit = frappe.db.get_value("Customer", "CUST-001", "credit_limit")
if credit is None:
    frappe.throw(_("Customer not found"))
credit = credit or 0  # Default to 0 if field is empty

# ✅ CORRECT — get_value with as_dict for multiple fields
data = frappe.db.get_value("Customer", "CUST-001",
    ["credit_limit", "disabled"], as_dict=True)
if not data:  # None when record not found
    frappe.throw(_("Customer not found"))
if data.disabled:
    frappe.throw(_("Customer is disabled"))

**Key behavior by method**: | Method | Record Not Found | Empty Field | |--------|-----------------|-------------| | `get_doc()` | Raises `DoesNotExistError` | Returns field default | | `get_value()` | Returns `None` | Returns `None` or `""` | | `get_all()` | Returns `[]` | Included in result | | `exists()` | Returns `False` | N/A | | `set_value()` | Silently does nothing | N/A | | `db.sql()` | Returns `[]` or `()` | Included in result |

---

Handling Each Exception Type

DuplicateEntryError

#