Keep getting Log save failed and delete failed with error no such table: _requests

[skoeswanto]

I did change some collections in pocketbase and make new ones and now I am getting this error everytime I click on any collection from the pocketbase web admin (host/_ )

[0.00ms] SELECT {{_admins}}.* FROM `_admins` WHERE `id`='wzid8jgk9p17mf8' LIMIT 1
[0.00ms] SELECT {{_collections}}.* FROM `_collections` WHERE [[id]] = 'bhppf4c64p2uw9d' OR LOWER([[name]])='bhppf4c64p2uw9d' LIMIT 1
[0.00ms] SELECT `approver`.* FROM `approver` ORDER BY [[approver.created]] DESC LIMIT 40
2024/05/16 01:31:56 Log save failed: no such table: _requests
2024/05/16 01:31:56 Logs delete failed: no such table: _requests
[0.00ms] SELECT {{_admins}}.* FROM `_admins` WHERE `id`='wzid8jgk9p17mf8' LIMIT 1
[0.00ms] SELECT {{_collections}}.* FROM `_collections` WHERE [[id]] = 'bhppf4c64p2uw9d' OR LOWER([[name]])='bhppf4c64p2uw9d' LIMIT 1
[0.00ms] SELECT `approver`.* FROM `approver` LIMIT 1
[0.00ms] SELECT COUNT(DISTINCT [[approver.id]]) FROM `approver`
2024/05/16 01:31:56 Log save failed: no such table: _requests
2024/05/16 01:31:56 Logs delete failed: no such table: _requests
[0.00ms] SELECT {{_admins}}.* FROM `_admins` WHERE `id`='wzid8jgk9p17mf8' LIMIT 1
[0.00ms] SELECT {{_collections}}.* FROM `_collections` WHERE [[id]] = 'xrriq4jsqt56oqv' OR LOWER([[name]])='xrriq4jsqt56oqv' LIMIT 1
[0.00ms] SELECT `applications`.* FROM `applications` LIMIT 1
[0.00ms] SELECT COUNT(DISTINCT [[applications.id]]) FROM `applications`
2024/05/16 01:32:04 Log save failed: no such table: _requests
2024/05/16 01:32:04 Logs delete failed: no such table: _requests
[0.00ms] SELECT {{_admins}}.* FROM `_admins` WHERE `id`='wzid8jgk9p17mf8' LIMIT 1
[0.00ms] SELECT {{_collections}}.* FROM `_collections` WHERE [[id]] = 'xrriq4jsqt56oqv' OR LOWER([[name]])='xrriq4jsqt56oqv' LIMIT 1
[0.00ms] SELECT `applications`.* FROM `applications` ORDER BY [[applications.created]] DESC LIMIT 40
2024/05/16 01:32:04 Log save failed: no such table: _requests
2024/05/16 01:32:04 Logs delete failed: no such table: _requests

Checked from sqlite3 command, the _requests and _logs are there

SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> .schema _logs
CREATE TABLE `_logs` (
                                `id`      TEXT PRIMARY KEY DEFAULT ('r'||lower(hex(randomblob(7)))) NOT NULL,
                                `level`   INTEGER DEFAULT 0 NOT NULL,
                                `message` TEXT DEFAULT "" NOT NULL,
                                `data`    JSON DEFAULT "{}" NOT NULL,
                                `created` TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%fZ')) NOT NULL,
                                `updated` TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%fZ')) NOT NULL
                        );
CREATE INDEX _logs_level_idx on `_logs` (`level`);
CREATE INDEX _logs_message_idx on `_logs` (`message`);
CREATE INDEX _logs_created_hour_idx on `_logs` (strftime('%Y-%m-%d %H:00:00', `created`));
sqlite> .schema _requests
CREATE TABLE `_requests` (
                                `id`        TEXT PRIMARY KEY NOT NULL,
                                `url`       TEXT DEFAULT "" NOT NULL,
                                `method`    TEXT DEFAULT "get" NOT NULL,
                                `status`    INTEGER DEFAULT 200 NOT NULL,
                                `auth`      TEXT DEFAULT "guest" NOT NULL,
                                "remoteIp"        TEXT DEFAULT "127.0.0.1" NOT NULL,
                                `referer`   TEXT DEFAULT "" NOT NULL,
                                `userAgent` TEXT DEFAULT "" NOT NULL,
                                `meta`      JSON DEFAULT "{}" NOT NULL,
                                `created`   TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%fZ')) NOT NULL,
                                `updated`   TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%fZ')) NOT NULL
                        , `userIp` TEXT DEFAULT "127.0.0.1" NOT NULL);
CREATE INDEX _request_status_idx on `_requests` (`status`);
CREATE INDEX _request_auth_idx on `_requests` (`auth`);
CREATE INDEX _request_created_hour_idx on `_requests` (strftime('%Y-%m-%d %H:00:00', `created`));
CREATE INDEX `_request_remote_ip_idx` ON `_requests` (`remoteIp`);
CREATE INDEX `_request_user_ip_idx` ON `_requests` (`userIp`);

I did ran (after getting these errors) some of the init back like 1640988000_init, 1660821103_add_user_ip_column and 1677760279_uppsercase_method and still have error. What am I missing?

[ganigeorgiev]

The _requests table shouldn't exists. It was removed in PocketBase v0.20.0 and replaced with _logs (see logs_generalization.go).

I did ran (after getting these errors) some of the init back like ...

This doesn't make sense. I have no idea how did you run them "back" in the first place. Migrations are executed only once. They can't be applied twice (unless you've run migrate down or deleted manually the entries in the _migrations table).

If you are still not able to resolve your issue, please elaborate clearly on your env setup and the steps you've taken that ended up in the above error, otherwise I don't think anyone will be able to help you.

[skoeswanto]

I am using pocketbase as golang framework, so I have my own main.go. To answer how I ran them back (maybe wrongly) is copying all those files 1640988000_init, 1660821103_add_user_ip_column and 1677760279_uppsercase_method and wrap them into function as one example below:

func one_init(db dbx.Builder) {
	_, err := db.NewQuery(`
			CREATE TABLE {{_logs}} (
				[[id]]      TEXT PRIMARY KEY DEFAULT ('r'||lower(hex(randomblob(7)))) NOT NULL,
				[[level]]   INTEGER DEFAULT 0 NOT NULL,
				[[message]] TEXT DEFAULT "" NOT NULL,
				[[data]]    JSON DEFAULT "{}" NOT NULL,
				[[created]] TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%fZ')) NOT NULL,
				[[updated]] TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%fZ')) NOT NULL
			);

			CREATE INDEX _logs_level_idx on {{_logs}} ([[level]]);
			CREATE INDEX _logs_message_idx on {{_logs}} ([[message]]);
			CREATE INDEX _logs_created_hour_idx on {{_logs}} (strftime('%Y-%m-%d %H:00:00', [[created]]));
		`).Execute()

	_, err = db.NewQuery(`
			CREATE TABLE {{_requests}} (
				[[id]]        TEXT PRIMARY KEY NOT NULL,
				[[url]]       TEXT DEFAULT "" NOT NULL,
				[[method]]    TEXT DEFAULT "get" NOT NULL,
				[[status]]    INTEGER DEFAULT 200 NOT NULL,
				[[auth]]      TEXT DEFAULT "guest" NOT NULL,
				[[ip]]        TEXT DEFAULT "127.0.0.1" NOT NULL,
				[[referer]]   TEXT DEFAULT "" NOT NULL,
				[[userAgent]] TEXT DEFAULT "" NOT NULL,
				[[meta]]      JSON DEFAULT "{}" NOT NULL,
				[[created]]   TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%fZ')) NOT NULL,
				[[updated]]   TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%fZ')) NOT NULL
			);

			CREATE INDEX _request_status_idx on {{_requests}} ([[status]]);
			CREATE INDEX _request_auth_idx on {{_requests}} ([[auth]]);
			CREATE INDEX _request_ip_idx on {{_requests}} ([[ip]]);
			CREATE INDEX _request_created_hour_idx on {{_requests}} (strftime('%Y-%m-%d %H:00:00', [[created]]));
		`).Execute()
	fmt.Println("One Init Err = ", err)
}

then I call them in my app/test/main.go. I notice I was getting restore files from my team at pocketbase 22 or 20 while I am actually using pocketbase 19.4 (declared in my go.mod). And have been making changes, backup, then give back to team. These happens back-and-forth many times. Maybe that cause some incompatibility, though in my team side, it is fine at their pocketbase. Any ideas how can I resolve this at my end? I need to stay in 19.4 for time being.

[ganigeorgiev]

To answer how I ran them back (maybe wrongly) is copying all those files ...

No, this is wrong.

I notice I was getting restore files from my team at pocketbase 22 or 20 while I am actually using pocketbase 19.4 (declared in my go.mod). And have been making changes, backup, then give back to team. These happens back-and-forth many times. Maybe that cause some incompatibility, though in my team side, it is fine at their pocketbase. Any ideas how can I resolve this at my end? I need to stay in 19.4 for time being.

I'm not sure that I understand your workflow or use case but if you are trying to use a database file from v0.20+ with older PocketBase version then this will not work. If you are working in a team, everyone must use a compatible PocketBase version (ideally the same; the go.mod and go.sum files usually should be committed). Once a stable v1 is reached this will be less of an issue but until then you need to keep the versions in sync (at least up to the second digit 0.X).

[skoeswanto]

Thanks for the prompt and clear answer. But what can I do to fix this error? Though all still functional, but the log is producing these error every time, even just clicking any collections (tables) from the web-UI.

[ganigeorgiev]

But what can I do to fix this error?

If you use a db file from PocketBase 0.20+ you need to use a PocketBase v0.20+ executable.

Try to perform the reverse operations of what you've executed.

I'm sorry but I don't think I can help you further as I don't understand what are you really trying to do.