Skip to content

Commit ac8a3f3

Browse files
vincentkocvincentkoc
committed
fix(sqlite): disable WAL on network filesystems
1 parent 8694fe7 commit ac8a3f3

6 files changed

Lines changed: 220 additions & 28 deletions

File tree

‎docs/plugins/sdk-subpaths.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -250,7 +250,7 @@ usage endpoint failed or returned no usable usage data.
250250
| `plugin-sdk/session-store-runtime` | Session workflow helpers (`getSessionEntry`, `listSessionEntries`, `patchSessionEntry`, `upsertSessionEntry`), legacy session store path/session-key helpers, updated-at reads, and deprecated whole-store mutation helpers |
251251
| `plugin-sdk/cron-store-runtime` | Cron store path/load/save helpers |
252252
| `plugin-sdk/state-paths` | State/OAuth dir path helpers |
253-
| `plugin-sdk/plugin-state-runtime` | Plugin sidecar SQLite keyed-state types |
253+
| `plugin-sdk/plugin-state-runtime` | Plugin sidecar SQLite keyed-state types plus centralized connection pragma and WAL maintenance setup for plugin-owned databases |
254254
| `plugin-sdk/routing` | Route/session-key/account binding helpers such as `resolveAgentRoute`, `buildAgentSessionKey`, and `resolveDefaultAgentBoundAccountId` |
255255
| `plugin-sdk/status-helpers` | Shared channel/account status summary helpers, runtime-state defaults, and issue metadata helpers |
256256
| `plugin-sdk/target-resolver-runtime` | Shared target resolver helpers |

‎extensions/workboard/src/sqlite-store.ts‎

Lines changed: 22 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22
import fs from "node:fs";
33
import path from "node:path";
44
import { DatabaseSync, type SQLInputValue } from "node:sqlite";
5+
import { configureSqliteConnectionPragmas } from "openclaw/plugin-sdk/plugin-state-runtime";
56
import { resolveStateDir } from "openclaw/plugin-sdk/state-paths";
67
import type {
78
PersistedWorkboardAttachment,
@@ -361,15 +362,6 @@ function ensureWorkboardSchema(db: DatabaseSync): void {
361362
).run(`schema-${SCHEMA_VERSION}`, Date.now());
362363
}
363364

364-
function configureWorkboardDatabase(db: DatabaseSync): void {
365-
db.exec(`
366-
PRAGMA journal_mode = WAL;
367-
PRAGMA synchronous = NORMAL;
368-
PRAGMA busy_timeout = ${WORKBOARD_SQLITE_BUSY_TIMEOUT_MS};
369-
PRAGMA foreign_keys = ON;
370-
`);
371-
}
372-
373365
function chmodIfExists(targetPath: string, mode: number): void {
374366
try {
375367
fs.chmodSync(targetPath, mode);
@@ -385,19 +377,30 @@ function hardenWorkboardDatabaseFiles(dbPath: string): void {
385377
chmodIfExists(dbPath, WORKBOARD_SQLITE_FILE_MODE);
386378
chmodIfExists(`${dbPath}-wal`, WORKBOARD_SQLITE_FILE_MODE);
387379
chmodIfExists(`${dbPath}-shm`, WORKBOARD_SQLITE_FILE_MODE);
380+
chmodIfExists(`${dbPath}-journal`, WORKBOARD_SQLITE_FILE_MODE);
388381
}
389382

390-
function createDatabase(dbPath: string): DatabaseSync {
383+
function createDatabase(dbPath: string): {
384+
db: DatabaseSync;
385+
maintenance: ReturnType<typeof configureSqliteConnectionPragmas>;
386+
} {
391387
fs.mkdirSync(path.dirname(dbPath), { recursive: true, mode: WORKBOARD_SQLITE_DIR_MODE });
392388
chmodIfExists(path.dirname(dbPath), WORKBOARD_SQLITE_DIR_MODE);
393389
if (!fs.existsSync(dbPath)) {
394390
fs.closeSync(fs.openSync(dbPath, "a", WORKBOARD_SQLITE_FILE_MODE));
395391
}
396392
const db = new DatabaseSync(dbPath);
397-
configureWorkboardDatabase(db);
393+
const maintenance = configureSqliteConnectionPragmas(db, {
394+
busyTimeoutMs: WORKBOARD_SQLITE_BUSY_TIMEOUT_MS,
395+
checkpointIntervalMs: 0,
396+
databaseLabel: "workboard database",
397+
databasePath: dbPath,
398+
foreignKeys: true,
399+
synchronous: "NORMAL",
400+
});
398401
ensureWorkboardSchema(db);
399402
hardenWorkboardDatabaseFiles(dbPath);
400-
return db;
403+
return { db, maintenance };
401404
}
402405

403406
function childRows(db: DatabaseSync, table: string, cardId: string): Row[] {
@@ -1401,12 +1404,17 @@ export function createWorkboardSqliteStores(
14011404
env?: NodeJS.ProcessEnv;
14021405
} = {},
14031406
): WorkboardSqliteStores {
1404-
const db = createDatabase(options.dbPath ?? resolveWorkboardSqlitePath(options.env));
1407+
const { db, maintenance } = createDatabase(
1408+
options.dbPath ?? resolveWorkboardSqlitePath(options.env),
1409+
);
14051410
return {
14061411
cards: new WorkboardSqliteCardStore(db),
14071412
boards: new WorkboardSqliteBoardStore(db),
14081413
subscriptions: new WorkboardSqliteSubscriptionStore(db),
14091414
attachments: new WorkboardSqliteAttachmentStore(db),
1410-
close: () => db.close(),
1415+
close: () => {
1416+
maintenance.close();
1417+
db.close();
1418+
},
14111419
};
14121420
}

‎extensions/workboard/src/store.test.ts‎

Lines changed: 34 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,18 @@ function createMemoryStore<T = PersistedWorkboardCard>(options?: {
3636
};
3737
}
3838

39+
function statfsFixture(type: number): ReturnType<typeof fs.statfsSync> {
40+
return {
41+
type,
42+
bsize: 1024,
43+
blocks: 1,
44+
bfree: 1,
45+
bavail: 1,
46+
files: 0,
47+
ffree: 0,
48+
};
49+
}
50+
3951
describe("WorkboardStore", () => {
4052
it("persists boards, cards, subscriptions, and attachment blobs in sqlite", async () => {
4153
const dir = fs.mkdtempSync(path.join(os.tmpdir(), "openclaw-workboard-sqlite-"));
@@ -90,7 +102,7 @@ describe("WorkboardStore", () => {
90102
if (process.platform !== "win32") {
91103
expect(fs.statSync(dir).mode & 0o777).toBe(0o700);
92104
expect(fs.statSync(dbPath).mode & 0o777).toBe(0o600);
93-
for (const sidecarPath of [`${dbPath}-wal`, `${dbPath}-shm`]) {
105+
for (const sidecarPath of [`${dbPath}-wal`, `${dbPath}-shm`, `${dbPath}-journal`]) {
94106
if (fs.existsSync(sidecarPath)) {
95107
expect(fs.statSync(sidecarPath).mode & 0o777).toBe(0o600);
96108
}
@@ -144,6 +156,27 @@ describe("WorkboardStore", () => {
144156
}
145157
});
146158

159+
it("uses rollback journaling on network-backed volumes", () => {
160+
const dir = fs.mkdtempSync(path.join(os.tmpdir(), "openclaw-workboard-sqlite-network-"));
161+
const dbPath = path.join(dir, "workboard.sqlite");
162+
const statfs = vi.spyOn(fs, "statfsSync").mockReturnValue(statfsFixture(0xff534d42));
163+
try {
164+
const stores = createWorkboardSqliteStores({ dbPath });
165+
stores.close();
166+
167+
const rawDb = new DatabaseSync(dbPath);
168+
expect(rawDb.prepare("PRAGMA journal_mode").get()).toMatchObject({
169+
journal_mode: "delete",
170+
});
171+
rawDb.close();
172+
expect(fs.existsSync(`${dbPath}-wal`)).toBe(false);
173+
expect(fs.existsSync(`${dbPath}-shm`)).toBe(false);
174+
} finally {
175+
statfs.mockRestore();
176+
fs.rmSync(dir, { recursive: true, force: true });
177+
}
178+
});
179+
147180
it("creates and lists cards by status order and position", async () => {
148181
const store = new WorkboardStore(createMemoryStore());
149182

‎src/infra/sqlite-wal.test.ts‎

Lines changed: 114 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -73,7 +73,97 @@ describe("sqlite WAL maintenance", () => {
7373
}
7474
});
7575

76-
it("refuses NFS-backed databases when SQLite keeps WAL active", () => {
76+
it.each([
77+
["SMB", 0x517b],
78+
["CIFS", 0xff534d42],
79+
["SMB2", 0xfe534d42],
80+
])("uses rollback journaling for databases on Linux %s volumes", (_label, fsType) => {
81+
const tempDir = fs.mkdtempSync(path.join(os.tmpdir(), "openclaw-sqlite-network-"));
82+
try {
83+
const db = createMockDb();
84+
vi.spyOn(fs, "statfsSync").mockReturnValue(statfsFixture(fsType));
85+
86+
configureSqliteWalMaintenance(db, {
87+
checkpointIntervalMs: 0,
88+
databasePath: path.join(tempDir, "openclaw.sqlite"),
89+
});
90+
91+
expect(db["prepare"]).toHaveBeenCalledWith("PRAGMA journal_mode = DELETE;");
92+
} finally {
93+
fs.rmSync(tempDir, { recursive: true, force: true });
94+
}
95+
});
96+
97+
it.each([
98+
String.raw`\\server\share\openclaw.sqlite`,
99+
String.raw`\\?\UNC\server\share\openclaw.sqlite`,
100+
"//server/share/openclaw.sqlite",
101+
"//?/UNC/server/share/openclaw.sqlite",
102+
])("uses rollback journaling for databases on Windows UNC paths: %s", (databasePath) => {
103+
const db = createMockDb();
104+
vi.spyOn(process, "platform", "get").mockReturnValue("win32");
105+
106+
configureSqliteWalMaintenance(db, {
107+
checkpointIntervalMs: 0,
108+
databasePath,
109+
});
110+
111+
expect(db["prepare"]).toHaveBeenCalledWith("PRAGMA journal_mode = DELETE;");
112+
expect(db["exec"]).not.toHaveBeenCalled();
113+
});
114+
115+
it("uses rollback journaling for mapped Windows network drives", () => {
116+
const db = createMockDb();
117+
const databasePath = String.raw`Z:\state\openclaw.sqlite`;
118+
vi.spyOn(process, "platform", "get").mockReturnValue("win32");
119+
const realpath = vi
120+
.spyOn(fs.realpathSync, "native")
121+
.mockReturnValue(String.raw`\\server\share\state\openclaw.sqlite`);
122+
123+
configureSqliteWalMaintenance(db, {
124+
checkpointIntervalMs: 0,
125+
databasePath,
126+
});
127+
128+
expect(realpath).toHaveBeenCalledWith(databasePath);
129+
expect(db["prepare"]).toHaveBeenCalledWith("PRAGMA journal_mode = DELETE;");
130+
expect(db["exec"]).not.toHaveBeenCalled();
131+
});
132+
133+
it("does not treat namespaced Windows local drives as UNC paths", () => {
134+
const db = createMockDb();
135+
const databasePath = String.raw`\\?\C:\state\openclaw.sqlite`;
136+
vi.spyOn(process, "platform", "get").mockReturnValue("win32");
137+
const realpath = vi.spyOn(fs.realpathSync, "native").mockReturnValue(databasePath);
138+
139+
configureSqliteWalMaintenance(db, {
140+
checkpointIntervalMs: 0,
141+
databasePath,
142+
});
143+
144+
expect(realpath).toHaveBeenCalledWith(databasePath);
145+
expect(db["prepare"]).not.toHaveBeenCalled();
146+
expect(db["exec"]).toHaveBeenNthCalledWith(1, "PRAGMA journal_mode = WAL;");
147+
});
148+
149+
it("uses rollback journaling when Windows cannot classify an opened drive path", () => {
150+
const db = createMockDb();
151+
const databasePath = String.raw`Z:\restricted\openclaw.sqlite`;
152+
vi.spyOn(process, "platform", "get").mockReturnValue("win32");
153+
vi.spyOn(fs.realpathSync, "native").mockImplementation(() => {
154+
throw new Error("access denied");
155+
});
156+
157+
configureSqliteWalMaintenance(db, {
158+
checkpointIntervalMs: 0,
159+
databasePath,
160+
});
161+
162+
expect(db["prepare"]).toHaveBeenCalledWith("PRAGMA journal_mode = DELETE;");
163+
expect(db["exec"]).not.toHaveBeenCalled();
164+
});
165+
166+
it("refuses network-backed databases when SQLite keeps WAL active", () => {
77167
const tempDir = fs.mkdtempSync(path.join(os.tmpdir(), "openclaw-sqlite-nfs-"));
78168
try {
79169
const db = createMockDb();
@@ -137,6 +227,29 @@ describe("sqlite WAL maintenance", () => {
137227
}
138228
});
139229

230+
it("uses macOS SMB mount filesystem names", () => {
231+
const tempDir = fs.mkdtempSync(path.join(os.tmpdir(), "openclaw-sqlite-smb-"));
232+
try {
233+
const db = createMockDb();
234+
vi.spyOn(fs, "statfsSync").mockReturnValue(statfsFixture(0));
235+
vi.spyOn(fs, "readFileSync").mockImplementation(() => {
236+
throw new Error("no proc mountinfo");
237+
});
238+
vi.spyOn(childProcess, "execFileSync").mockReturnValue(
239+
Buffer.from(`//server/share on ${tempDir} (smbfs, nodev, nosuid)\n`),
240+
);
241+
242+
configureSqliteWalMaintenance(db, {
243+
checkpointIntervalMs: 0,
244+
databasePath: path.join(tempDir, "openclaw.sqlite"),
245+
});
246+
247+
expect(db["prepare"]).toHaveBeenCalledWith("PRAGMA journal_mode = DELETE;");
248+
} finally {
249+
fs.rmSync(tempDir, { recursive: true, force: true });
250+
}
251+
});
252+
140253
it("parses Linux mount command filesystem names when proc mountinfo is unavailable", () => {
141254
const tempDir = fs.mkdtempSync(path.join(os.tmpdir(), "openclaw-sqlite-nfs-"));
142255
try {

‎src/infra/sqlite-wal.ts‎

Lines changed: 48 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,11 @@ export const DEFAULT_SQLITE_WAL_CHECKPOINT_INTERVAL_MS = 30 * 60 * 1000;
1515
*/
1616
export const DEFAULT_SQLITE_WAL_TRUNCATE_INTERVAL_MS = DEFAULT_SQLITE_WAL_CHECKPOINT_INTERVAL_MS;
1717
const LINUX_NFS_SUPER_MAGIC = 0x6969;
18+
const LINUX_SMB_SUPER_MAGIC = 0x517b;
19+
const LINUX_CIFS_SUPER_MAGIC = 0xff534d42;
20+
const LINUX_SMB2_SUPER_MAGIC = 0xfe534d42;
1821
const PROC_MOUNTINFO_PATH = "/proc/self/mountinfo";
22+
const NETWORK_FILESYSTEM_TYPES = new Set(["cifs", "smbfs", "smb2", "smb3"]);
1923

2024
type IntervalHandle = ReturnType<typeof setInterval> & {
2125
unref?: () => void;
@@ -133,33 +137,66 @@ function isPathWithinMount(targetPath: string, mountPoint: string): boolean {
133137
);
134138
}
135139

136-
function isNfsMountType(fsType: string): boolean {
137-
return fsType.toLowerCase().startsWith("nfs");
140+
function isNetworkMountType(fsType: string): boolean {
141+
const normalized = fsType.toLowerCase();
142+
return normalized.startsWith("nfs") || NETWORK_FILESYSTEM_TYPES.has(normalized);
138143
}
139144

140-
function isNfsMountEntryPath(targetPath: string): boolean {
145+
function isNetworkMountEntryPath(targetPath: string): boolean {
141146
const mountEntry = readMountEntries()
142147
.filter((entry) => isPathWithinMount(targetPath, entry.mountPoint))
143148
.toSorted((a, b) => b.mountPoint.length - a.mountPoint.length)[0];
144-
return mountEntry ? isNfsMountType(mountEntry.fsType) : false;
149+
return mountEntry ? isNetworkMountType(mountEntry.fsType) : false;
145150
}
146151

147-
function isNfsBackedPath(targetPath: string): boolean {
152+
function isWindowsUncPath(targetPath: string): boolean {
153+
return (
154+
/^\\\\\?\\UNC\\[^\\]+\\[^\\]+/i.test(targetPath) ||
155+
/^\\\\(?![?.]\\)[^\\]+\\[^\\]+/.test(targetPath)
156+
);
157+
}
158+
159+
function isWindowsDrivePath(targetPath: string): boolean {
160+
return /^[A-Za-z]:[\\/]/.test(targetPath) || /^\\\\\?\\[A-Za-z]:[\\/]/i.test(targetPath);
161+
}
162+
163+
function isNetworkBackedPath(targetPath: string): boolean {
164+
if (process.platform === "win32") {
165+
const normalizedTargetPath = path.win32.normalize(targetPath);
166+
if (isWindowsUncPath(normalizedTargetPath)) {
167+
return true;
168+
}
169+
if (isWindowsDrivePath(normalizedTargetPath)) {
170+
try {
171+
return isWindowsUncPath(path.win32.normalize(fs.realpathSync.native(targetPath)));
172+
} catch {
173+
// Windows can deny SMB path normalization when parent components are
174+
// unreadable. Treat an unclassifiable opened database as network-backed.
175+
return true;
176+
}
177+
}
178+
}
148179
if (typeof fs.statfsSync !== "function") {
149-
return isNfsMountEntryPath(targetPath);
180+
return isNetworkMountEntryPath(targetPath);
150181
}
151182
const checkedPath = findExistingVolumePath(targetPath);
152183
if (!checkedPath) {
153184
return false;
154185
}
155186
try {
156-
if (fs.statfsSync(checkedPath).type === LINUX_NFS_SUPER_MAGIC) {
187+
const filesystemType = fs.statfsSync(checkedPath).type;
188+
if (
189+
filesystemType === LINUX_NFS_SUPER_MAGIC ||
190+
filesystemType === LINUX_SMB_SUPER_MAGIC ||
191+
filesystemType === LINUX_CIFS_SUPER_MAGIC ||
192+
filesystemType === LINUX_SMB2_SUPER_MAGIC
193+
) {
157194
return true;
158195
}
159196
} catch {
160-
return isNfsMountEntryPath(checkedPath);
197+
return isNetworkMountEntryPath(checkedPath);
161198
}
162-
return isNfsMountEntryPath(checkedPath);
199+
return isNetworkMountEntryPath(checkedPath);
163200
}
164201

165202
function readJournalModeResult(row: unknown): string | null {
@@ -179,7 +216,7 @@ function requireRollbackJournalMode(db: DatabaseSync, options: SqliteWalMaintena
179216
const location = options.databasePath ? ` at ${options.databasePath}` : "";
180217
const actual = journalMode ?? "unknown";
181218
throw new Error(
182-
`${label}${location} is on an NFS-backed volume but SQLite kept journal_mode=${actual}; refusing to continue with WAL on NFS.`,
219+
`${label}${location} is on a network-backed volume but SQLite kept journal_mode=${actual}; refusing to continue with WAL on network storage.`,
183220
);
184221
}
185222
}
@@ -200,7 +237,7 @@ export function configureSqliteWalMaintenance(
200237
const timerIntervalMs = Math.min(checkpointIntervalMs, MAX_TIMER_TIMEOUT_MS);
201238
const checkpointMode = options.checkpointMode ?? "TRUNCATE";
202239
const periodicCheckpointMode = options.checkpointMode ?? "PASSIVE";
203-
if (options.databasePath && isNfsBackedPath(options.databasePath)) {
240+
if (options.databasePath && isNetworkBackedPath(options.databasePath)) {
204241
requireRollbackJournalMode(db, options);
205242
return {
206243
checkpoint: () => true,

‎src/plugin-sdk/plugin-state-runtime.ts‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
/**
22
* Runtime SDK type surface for plugin-scoped keyed state stores.
33
*/
4+
export { configureSqliteConnectionPragmas } from "../infra/sqlite-wal.js";
45
export type {
56
OpenKeyedStoreOptions,
67
PluginStateEntry,

0 commit comments

Comments
 (0)