starred/audiobookshelf
1
0
Fork 0
mirror of https://github.com/advplyr/audiobookshelf.git synced 2026-05-30 23:40:40 +02:00
Code Issues 1.0k Packages Projects Releases 10 Wiki Activity

[Bug]: malformed database schema (update_library_items_author_names_on_book_authors_insert) #2668

New Issue
Closed
opened 2026-04-25 00:09:28 +02:00 by adam · 6 comments
adam commented 2026-04-25 00:09:28 +02:00
Owner
Copy Link
Copy Source

Originally created by @PhantomPhoton on GitHub (Mar 18, 2025).

What happened?

I updated to v2.20.0 and I check the database after backups to ensure it's valid. The v2.20.0 upgrade broke something with the update_library_items_author_names_on_book_authors_insert trigger.

Error: near line 20: in prepare, malformed database schema (update_library_items_author_names_on_book_authors_insert) - near "ORDER": syntax error (11)

What did you expect to happen?

I expected to have the database with a valid schema

Steps to reproduce the issue

  1. Upgrade to 2.20.0
  2. use the sqlite command line to query anything.

Audiobookshelf version

v2.20.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

Error: in prepare, malformed database schema (update_library_items_author_names_on_book_authors_insert) - near "ORDER": syntax error (11)

Additional Notes

No response

Originally created by @PhantomPhoton on GitHub (Mar 18, 2025). ### What happened? I updated to [v2.20.0](https://github.com/advplyr/audiobookshelf/releases/tag/v2.20.0) and I check the database after backups to ensure it's valid. The v2.20.0 upgrade broke something with the `update_library_items_author_names_on_book_authors_insert` trigger. ``` Error: near line 20: in prepare, malformed database schema (update_library_items_author_names_on_book_authors_insert) - near "ORDER": syntax error (11) ``` ### What did you expect to happen? I expected to have the database with a valid schema ### Steps to reproduce the issue 1. Upgrade to 2.20.0 2. use the sqlite command line to query anything. ### Audiobookshelf version v2.20.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 ```shell Error: in prepare, malformed database schema (update_library_items_author_names_on_book_authors_insert) - near "ORDER": syntax error (11) ``` ### Additional Notes _No response_
adam added the bug label 2026-04-25 00:09:28 +02:00
adam closed this issue 2026-04-25 00:09:28 +02:00
adam commented 2026-04-25 00:09:28 +02:00
Author
Owner
Copy Link
Copy Source

@nichwall commented on GitHub (Mar 18, 2025):

Can you run PRAGMA integrity_check; on your database?

@nichwall commented on GitHub (Mar 18, 2025): Can you run `PRAGMA integrity_check;` on your database?
adam commented 2026-04-25 00:09:28 +02:00
Author
Owner
Copy Link
Copy Source

@PhantomPhoton commented on GitHub (Mar 18, 2025):

Sure thing

sqlite> PRAGMA integrity_check;
Error: in prepare, malformed database schema (update_library_items_author_names_on_book_authors_insert) - near "ORDER": syntax error (11)
sqlite>
@PhantomPhoton commented on GitHub (Mar 18, 2025): Sure thing ``` sqlite> PRAGMA integrity_check; Error: in prepare, malformed database schema (update_library_items_author_names_on_book_authors_insert) - near "ORDER": syntax error (11) sqlite> ```
adam commented 2026-04-25 00:09:28 +02:00
Author
Owner
Copy Link
Copy Source

@nichwall commented on GitHub (Mar 18, 2025):

Looks like your database is corrupt.

You can try doing something like the following to export everything to a new temp database to see if that fixes the index being wrong.

sqlite3 absdatabase.sqlite ".dump" | sqlite3 temp.db
@nichwall commented on GitHub (Mar 18, 2025): Looks like your database is corrupt. You can try doing something like the following to export everything to a new temp database to see if that fixes the index being wrong. ``` sqlite3 absdatabase.sqlite ".dump" | sqlite3 temp.db ```
adam commented 2026-04-25 00:09:28 +02:00
Author
Owner
Copy Link
Copy Source

@PhantomPhoton commented on GitHub (Mar 18, 2025):

Unfortunately that also fails

:~# /usr/bin/sqlite3 config/absdatabase.sqlite .dump | sqlite3 /tmp/temp.db
Error: near line 79907: in prepare, near "ORDER": syntax error (1)
Error: near line 79919: in prepare, near "ORDER": syntax error (1)
Error: near line 79931: in prepare, near "ORDER": syntax error (1)
@PhantomPhoton commented on GitHub (Mar 18, 2025): Unfortunately that also fails ``` :~# /usr/bin/sqlite3 config/absdatabase.sqlite .dump | sqlite3 /tmp/temp.db Error: near line 79907: in prepare, near "ORDER": syntax error (1) Error: near line 79919: in prepare, near "ORDER": syntax error (1) Error: near line 79931: in prepare, near "ORDER": syntax error (1) ```
adam commented 2026-04-25 00:09:28 +02:00
Author
Owner
Copy Link
Copy Source

@PhantomPhoton commented on GitHub (Mar 18, 2025):

So I created a new database and my sqlite binary still was throwing an error. I looked at the change log and the ORDER BY support for GROUP_CONCAT was added in version 2023-11-01 (3.44.0), and my binary is slightly too old for that support, so the database isn't corrupt, it's just too new. Apologies for the noise.

@PhantomPhoton commented on GitHub (Mar 18, 2025): So I created a new database and my sqlite binary still was throwing an error. I looked at the change log and the `ORDER BY` support for `GROUP_CONCAT` was added in version 2023-11-01 (3.44.0), and my binary is slightly too old for that support, so the database isn't corrupt, it's just too new. Apologies for the noise.
adam commented 2026-04-25 00:09:29 +02:00
Author
Owner
Copy Link
Copy Source

@nichwall commented on GitHub (Mar 18, 2025):

Ah, thanks for providing that information. I was unable to replicate the issue using the docker image, but am able to replicate it using DB Browser. Looks like the newest release for DB Browser is still using SQLite 3.42.0, which explains why it is also having the issue.

@nichwall commented on GitHub (Mar 18, 2025): Ah, thanks for providing that information. I was unable to replicate the issue using the docker image, but am able to replicate it using [DB Browser](https://sqlitebrowser.org/). Looks like the newest release for DB Browser is still using SQLite 3.42.0, which explains why it is also having the issue.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
authentication
backlog
bug
chapter editor
config-issue
ebooks
encoding/embedding
enhancement
help wanted
listening sessions & progress
planned
possible plugin
progress sync
pull-request
Mirrored from GitHub Pull Request
 sorting/filtering/searching
unable to reproduce
upload
users & permissions
waiting
No Label bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam (Adam Melkus)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/audiobookshelf#2668
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?