What causes "SQL statement is not read-only" in siyuan-note/siyuan?

Rewrite the SQL so the whole statement is a genuine read-only SELECT

SQL statement is not read-only

Error message

SQL statement is not read-only

What it means

This is the second line of defense: even if the textual pre-check passes, the kernel executes the statement via the SQLite driver and calls SQLiteStmt.Readonly() (sqlite3_stmt_readonly) on the prepared statement. If SQLite itself reports the statement is not read-only, execution is aborted. This catches statements that pass keyword heuristics but actually mutate state.

Solutions

Rewrite the SQL so the whole statement is a genuine read-only SELECT
Remove any embedded write constructs (writable CTEs, chained statements)
If you legitimately need writes, use the kernel's write transaction APIs, not the read-only validator

Example fix

// before
stmt := "SELECT * FROM blocks; PRAGMA journal_mode=WAL" // rejected by sqlite3_stmt_readonly
// after
stmt := "SELECT * FROM blocks"

Defensive patterns

Strategy: validation

Validate before calling

// Keep statements trivially read-only; avoid chained statements and side-effecting constructs
if (stmt.includes(";")) throw new Error("multiple statements not allowed");

Try / catch

try { await runQuery(stmt); } catch (e) { if (String(e.message).includes("not read-only")) logAndReject(stmt); else throw e; }

Prevention

Avoid multi-statement strings and pragmas in query paths
Treat the keyword pre-check as advisory: SQLite's readonly flag is the authority
Keep statements simple SELECTs over known tables

When it happens

Trigger: A statement containing write sub-expressions or side effects that slipped past the string-level check (e.g. cleverly crafted statements, functions with side effects, or unsupported statement kinds that the keyword check misclassifies).

Common situations: Custom SQL built dynamically by plugins that embed write keywords inside an otherwise SELECT-looking statement; version changes where the driver's readonly semantics became stricter.

Understand the failure class

Background: UnsupportedOperationException and "is not supported" errors: when a library deliberately refuses a call — this error's family across 30 libraries.

Related errors

AI-assisted analysis of siyuan-note/siyuan@9f775e8a12 (2026-09-19). Data as JSON: /api/errors/78130773fe6f2e33. Report an issue: GitHub.

Appendix: source

Thrown at kernel/sql/stmt_validate.go:231

	defer conn.Close()

	return conn.Raw(func(dc any) error {
		sqliteConn, ok := dc.(*sqlite3.SQLiteConn)
		if !ok {
			return fmt.Errorf("SQL driver connection type is unexpected: %T", dc)
		}
		ds, err := sqliteConn.Prepare(stmt)
		if err != nil {
			return err
		}
		defer ds.Close()

		sst, ok := ds.(*sqlite3.SQLiteStmt)
		if !ok {
			return fmt.Errorf("SQL driver statement type is unexpected: %T", ds)
		}
		if !sst.Readonly() {
			return errors.New("SQL statement is not read-only")
		}
		return nil
	})
}

// isReadonlyQueryStatement 仅允许查询语句进入 SQLite prepare，提前拒绝会被 sqlite3_stmt_readonly
// 视为只读的 ATTACH、DETACH 和事务控制语句。WITH 中的写操作仍由 sqlite3_stmt_readonly 拒绝。
func isReadonlyQueryStatement(stmt string) bool {
	stmt = strings.TrimSpace(stmt)
	for "" != stmt {
		switch {
		case strings.HasPrefix(stmt, "--"):
			if lineEnd := strings.IndexByte(stmt, '\n'); 0 <= lineEnd {
				stmt = strings.TrimSpace(stmt[lineEnd+1:])
				continue
			}
			return false
		case strings.HasPrefix(stmt, "/*"):

View on GitHub (pinned to 9f775e8a12)