mirror of
https://github.com/advplyr/audiobookshelf.git
synced 2026-05-30 23:40:40 +02:00
[Bug]: sqlite3 Syntax error when running sql select on DB #2768
Assignees
adam (Adam Melkus)
Clear assignees
adam (Adam Melkus)
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: starred/audiobookshelf#2768
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Originally created by @lincolnep on GitHub (May 15, 2025).
What happened?
I have a python script that has been working for the last few years that will rename and move my audiobooks around according to the metadata stored in the sqlite database and also update the database inode for the files that it moves.
It has recently stopped working. I believe that is is because of this change "Commit 40504da" if I am reading it correctly.
The script is throwing a sqlite syntax errors in the database schema:
sqlite3.DatabaseError: malformed database schema (update_library_items_author_names_on_authors_update) - near "ORDER": syntax error
sqlite3.DatabaseError: malformed database schema (update_library_items_author_names_on_book_authors_delete) - near "ORDER": syntax error
sqlite3.DatabaseError: malformed database schema (update_library_items_author_names_on_book_authors_insert) - near "ORDER": syntax error
What did you expect to happen?
The script to run.
Please note if there was an issue with the script/DB table change then I would just fix it with logging a ticket.
Steps to reproduce the issue
1.run the below sql from a cli sqlite3:
SELECT name, sql FROM sqlite_master WHERE type = 'trigger';
Audiobookshelf version
2.22.0
How are you running audiobookshelf?
Docker
What OS is your Audiobookshelf server hosted from?
Linux
If the issue is being seen in the UI, what browsers are you seeing the problem on?
None
Logs
Additional Notes
I have fixed this syntax error in a test DB by replacing the SQL in these Triggers I don't know if it will work but it is not causing syntax errors any more.
Here is the changed SQL:
DROP TRIGGER "main"."update_library_items_author_names_on_authors_update";
CREATE TRIGGER update_library_items_author_names_on_authors_update
AFTER UPDATE OF name ON authors
FOR EACH ROW
BEGIN
UPDATE libraryItems
SET
authorNamesFirstLast = (
SELECT GROUP_CONCAT(name, ', ')
FROM (
SELECT authors.name
FROM authors
JOIN bookAuthors ON authors.id = bookAuthors.authorId
WHERE bookAuthors.bookId = libraryItems.mediaId
ORDER BY bookAuthors.createdAt ASC
)
),
authorNamesLastFirst = (
SELECT GROUP_CONCAT(lastFirst, ', ')
FROM (
SELECT authors.lastFirst
FROM authors
JOIN bookAuthors ON authors.id = bookAuthors.authorId
WHERE bookAuthors.bookId = libraryItems.mediaId
ORDER BY bookAuthors.createdAt ASC
)
)
WHERE mediaId IN (
SELECT bookId FROM bookAuthors WHERE authorId = NEW.id
);
END
DROP TRIGGER "main"."update_library_items_author_names_on_book_authors_delete";
CREATE TRIGGER update_library_items_author_names_on_book_authors_delete
AFTER DELETE ON bookAuthors
FOR EACH ROW
BEGIN
UPDATE libraryItems
SET
authorNamesFirstLast = (
SELECT GROUP_CONCAT(name, ', ')
FROM (
SELECT authors.name
FROM authors
JOIN bookAuthors ON authors.id = bookAuthors.authorId
WHERE bookAuthors.bookId = OLD.bookId
ORDER BY bookAuthors.createdAt ASC
)
),
authorNamesLastFirst = (
SELECT GROUP_CONCAT(lastFirst, ', ')
FROM (
SELECT authors.lastFirst
FROM authors
JOIN bookAuthors ON authors.id = bookAuthors.authorId
WHERE bookAuthors.bookId = OLD.bookId
ORDER BY bookAuthors.createdAt ASC
)
)
WHERE mediaId = OLD.bookId;
END
DROP TRIGGER "main"."update_library_items_author_names_on_book_authors_insert";
CREATE TRIGGER update_library_items_author_names_on_book_authors_insert
AFTER INSERT ON bookAuthors
FOR EACH ROW
BEGIN
UPDATE libraryItems
SET
authorNamesFirstLast = (
SELECT GROUP_CONCAT(name, ', ')
FROM (
SELECT authors.name
FROM authors
JOIN bookAuthors ON authors.id = bookAuthors.authorId
WHERE bookAuthors.bookId = NEW.bookId
ORDER BY bookAuthors.createdAt ASC
)
),
authorNamesLastFirst = (
SELECT GROUP_CONCAT(lastFirst, ', ')
FROM (
SELECT authors.lastFirst
FROM authors
JOIN bookAuthors ON authors.id = bookAuthors.authorId
WHERE bookAuthors.bookId = NEW.bookId
ORDER BY bookAuthors.createdAt ASC
)
)
WHERE mediaId = NEW.bookId;
END
From my understanding this syntax error is because Sqlite3 has tightened up there syntax in the later versions.
@Vito0912 commented on GitHub (May 15, 2025):
Your used library has to support the new sqlite version. (At least that's the cause of older issues. If you modified your SQL file yourself this could lead to other issues ofc)
(Not an actual issue with abs as far as I can tell)
@nichwall commented on GitHub (May 15, 2025):
Duplicate of https://github.com/advplyr/audiobookshelf/issues/4134. You need to be using at least SQLite 3.44.0, released in November of 2023.
@lincolnep commented on GitHub (May 15, 2025):
Ahh thanks nichwall that looks like it could be it as I have just checked and I have version 3.40.0
@lincolnep commented on GitHub (May 15, 2025):
Thanks All 100% sqlite3 version issue.
Update Python from 3.9 to 3.12.10 to fix the issue as it have sqlite3 version 3.45.2
Thank you for your help!