Support Doc for Database Cache Size (MB)?

JDIacobbo · January 4, 2023, 7:28pm

Figured it out. The utility was setting the owner as root which was the issue. Running

chown 99:100

on both the blob and db files fixed the issue for me. This was for the linuxserver.io container btw.

It might be a good idea to add in the documentation somewhere to check the permissions before and after running the utility to confirm they are correct.

Permissions after running the utility:
-rw-r--r-- 1 root root 1033482240 Jan 4 12:24 com.plexapp.plugins.library.blobs.db
-rw-r--r-- 1 root root 287322112 Jan 4 12:24 com.plexapp.plugins.library.db

Correct permissions after running chown 99:100
-rw-r--r-- 1 abc users 1033482240 Jan 4 12:44 com.plexapp.plugins.library.blobs.db
-rw-r--r-- 1 abc users 287322112 Jan 4 12:44 com.plexapp.plugins.library.db

ChuckPa · January 4, 2023, 7:35pm

That’s strange.

I get the owner of the DB during initialization

# Get the owning UID/GID before we proceed so we can restore
Owner="$(stat $STATFMT '%u:%g' $CPPL.db)"

I set the owner when done

      # Set ownership on new files
      chown $Owner $CPPL.db $CPPL.blobs.db

jaquestati · January 4, 2023, 9:44pm

yes. i can confirm running these three options MASSIVELY sped up library/poster loading… everything is much much snappier : )

thanks again for the tip/program : )

alas it didn’t solve my problem with searches containing common words (the and of etc) timing out like and saying no results/hits…

like searches for gone with the wind and the rookie still say no results, whereas leaving out the common words (in this case THE) and just search gone wind… and rookie… gives the proper results/hits : ) : (

jaquestati · January 4, 2023, 9:57pm

where/how exactly do you set this new database cache size setting?

ChuckPa · January 4, 2023, 9:57pm

OK… so that’s a “PMS thing”.

I’ll try to make some time and see what I can recreate here.

ChuckPa · January 4, 2023, 9:58pm

@jaquestati

You would set it in Preferences.xml

As has been discovered, with repeated success, letting SQLite handle its default auto cache size coupled with physically reorganizing the DB has a much more profound impact on all systems (including those with only 1 GB of RAM)

jaquestati · January 4, 2023, 11:29pm

so you don’t think i’d see any improvements even with such a massive library/3gb database (i already run it from a ram disk)?

JDIacobbo · January 5, 2023, 1:27am

I’d be willing to run a debug or something if you wanted to take a deeper look into this. LMK

TeknoJunky · January 5, 2023, 3:10am

if you are running the db from a ram drive, I hope you are careful to make constant backups for if/when the server crashes and/or power/ups goes out.

that said, running the db from a ram drive would ostensibly remove disk io as the bottleneck, the next bottleneck would then become your single core cpu power/score.

meaning, how fast the cpu core can process single threaded tasks (db actions are typically single process/thread).

in that scenario, a high GHZ cpu is much better than lots of slow cores (for example a high ghz desktop vs older/lower ghz server cpu with lots of cores.

I would suspect that in many cases, CPU bottlenecks are far more common than realized, especially where database is located on ssd/nvme.

you can see this using HTOP or similar util, where monitoring the plex processes you can see it go to 100% vs 100x#cores for multi-threaded tasks.

also, to answer your question

I would expect this to entirely depend on how ‘fragmented’ the database/index/etc are.

a very old database that has had many years of adding/removing content, updating indexes/id’s/etc, would likely take more cpu processing than something freshly rebuild and made with sequential itemization/indexing, so I would expect it to help lower the cpu load.

in any case, only you can determine whether it helps or not, make a backup, run the script, compare how responsive it feels before and after.

jaquestati · January 5, 2023, 3:15am

i’ve got a real overpowered machine ahaha… and hardware decoding… never see much cpu usage… : )

and yes yer right re database backups… i do one every few days and before any reboot…

my library is so large though that things were really sluggish even running the database off ssd/nvme… running in ram really did help…

TeknoJunky · January 5, 2023, 3:20am

well over power is relative.

and hardware decoding is irrelevant as far as database performance.

jaquestati · January 5, 2023, 3:48am

i think the database performance is good, like i said… searches are instantaneous, (especially after i ran ChuckPa’s script to repair and reindex the database). before i can even finish typing the name,

so long as i don’t use a common word like the, and or of… : )

savantali · January 8, 2023, 3:44pm

Great to see this as an addition, but, because I have modified the pragma_cache due to recommendations in other threads to do slow/underperforming database (old post, I am now unable to update/upgrade one of my instance.

At first I thought it was due to a DB corruption, so I performed the steps outlined here and here:

root@dionysus:~/Library/Application Support/Plex Media Server/Plug-in Support/Databases# /usr/lib/plexmediaserver/Plex\ SQLite
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .quit
root@dionysus:~/Library/Application Support/Plex Media Server/Plug-in Support/Databases# /usr/lib/plexmediaserver/Plex\ SQLite com.plexapp.
com.plexapp.dlna.db                              com.plexapp.plugins.library.db-2022-12-30
com.plexapp.dlna.db-shm                          com.plexapp.plugins.library.db-2023-01-02
com.plexapp.dlna.db-wal                          com.plexapp.plugins.library.db-2023-01-05
com.plexapp.plugins.library.blobs.db             com.plexapp.plugins.library.db-shm
com.plexapp.plugins.library.blobs.db-2022-12-27  com.plexapp.plugins.library.db-wal
com.plexapp.plugins.library.blobs.db-2022-12-30  com.plexapp.plugins.library.db.backup
com.plexapp.plugins.library.blobs.db-2023-01-02  com.plexapp.plugins.library.db.db-recovery
com.plexapp.plugins.library.blobs.db-2023-01-05  com.plexapp.plugins.library.db.original
com.plexapp.plugins.library.db                   com.plexapp.plugins.library.db.original-shm
com.plexapp.plugins.library.db-2022-12-27        com.plexapp.plugins.library.db.original-wal
root@dionysus:~/Library/Application Support/Plex Media Server/Plug-in Support/Databases# /usr/lib/plexmediaserver/Plex\ SQLite com.plexapp.plugins.library.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> PRAGMA integrity_check;
ok
sqlite> VACUUM;
sqlite> REINDEX;
sqlite> .output db-recover.sqlite
sqlite> .recover
sqlite> .quit
root@dionysus:~/Library/Application Support/Plex Media Server/Plug-in Support/Databases# mv com.plexapp.plugins.library.db com.plexapp.plugins.library.db.db-recovery-2
root@dionysus:~/Library/Application Support/Plex Media Server/Plug-in Support/Databases# /usr/lib/plexmediaserver/Plex\ SQLite com.plexapp.plugins.library.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> .read db-recover.sqlite
sqlite> .quit
root@dionysus:~/Library/Application Support/Plex Media Server/Plug-in Support/Databases# cd /var/run/s6/services/
root@dionysus:/var/run/s6/services# s6-svc -u plex
root@dionysus:/var/run/s6/services# exit
exit
$ docker logs -f dionysus
[s6-init] making user provided files available at /var/run/s6/etc...exited 0.
[s6-init] ensuring user provided files have correct perms...exited 0.
[fix-attrs.d] applying ownership & permissions fixes...
[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] 40-plex-first-run: executing...
Plex Media Server first run setup complete
[cont-init.d] 40-plex-first-run: exited 0.
[cont-init.d] 45-plex-hw-transcode-and-connected-tuner: executing...
[cont-init.d] 45-plex-hw-transcode-and-connected-tuner: exited 0.
[cont-init.d] 50-plex-update: executing...
Attempting to upgrade to: 1.30.1.6497-5fc2e0894
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   190    0   190    0     0    414      0 --:--:-- --:--:-- --:--:--   413
100 81.6M  100 81.6M    0     0  43.8M      0  0:00:01  0:00:01 --:--:-- 65.6M
Selecting previously unselected package plexmediaserver.
(Reading database ... 7274 files and directories currently installed.)
Preparing to unpack /tmp/plexmediaserver.deb ...
PlexMediaServer install: Pre-installation Validation.
PlexMediaServer install: Custom environment detected.  Skipping preinstallation validation.
Unpacking plexmediaserver (1.30.1.6497-5fc2e0894) ...
Setting up plexmediaserver (1.30.1.6497-5fc2e0894) ...
PlexMediaServer install: Custom environment detected.  Skipping postinstallation tasks. Continuing.
[cont-init.d] 50-plex-update: exited 0.
[cont-init.d] done.
[services.d] starting services
Starting Plex Media Server.
[services.d] done.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]

However, then I realized the two things that were probably causing this:

Attempting to upgrade to: 1.30.1.6497-5fc2e0894
Database Cache Size (MB)

So now even after trying to roll back the database to older versions, I am stuck unable to perform the upgrade for a pragma change that has been working for 2 years.

Is there a fix for this rather than rebuilding libraries?

EDIT: If I run your script @ChuckPa I get the following output:


root@dionysus:~# sh DBRepair.sh



      Plex Media Server Database Repair Utility (Docker)

Select

  1. Check database
  2. Vacuum database
  3. Reindex database
  4. Attempt database repair
  5. Replace current database with newest usable backup copy
  6. Undo last successful action (Vacuum, Reindex, Repair, or Replace)
  7. Import Viewstate / Watch history from another PMS database
  8. Show logfile
  9. Exit

Enter choice: 1

Checking the PMS databases
Check complete.  PMS main database is OK.
Error: file is not a database
Check complete.  PMS blobs database is damaged.


      Plex Media Server Database Repair Utility (Docker)

Select

  1. Check database
  2. Vacuum database
  3. Reindex database
  4. Attempt database repair
  5. Replace current database with newest usable backup copy
  6. Undo last successful action (Vacuum, Reindex, Repair, or Replace)
  7. Import Viewstate / Watch history from another PMS database
  8. Show logfile
  9. Exit

Enter choice: 4

Exporting current databases using timestamp: 2023-01-08_10.03.51
Export: (main)..(blobs)..done.
Successfully exported the main and blobs databases.  Proceeding to import into new databases.
Import: (main)..(blobs)..done.
Successfully imported data from exported SQL files.
Verifying databases integrity after importing.
Verification complete.  PMS main database is OK.
Verification complete.  PMS blobs database is OK.
Saving current databases with '-ORIG-2023-01-08_10.03.51'
Making imported databases active
Import complete. Please check your library settings and contents for completeness.
Recommend:  Scan Files and Refresh all metadata for each library section.


      Plex Media Server Database Repair Utility (Docker)

Select

  1. Check database
  2. Vacuum database
  3. Reindex database
  4. Attempt database repair
  5. Replace current database with newest usable backup copy
  6. Undo last successful action (Vacuum, Reindex, Repair, or Replace)
  7. Import Viewstate / Watch history from another PMS database
  8. Show logfile
  9. Exit

Enter choice: 3

Backing up of databases
Backup current databases with '-ORIG-2023-01-08_10.04.30' timestamp.
Reindexing main database
Reindexing main database successful.
Reindexing blobs database
Reindexing blobs database successful.
Reindex complete.


      Plex Media Server Database Repair Utility (Docker)

Select

  1. Check database
  2. Vacuum database
  3. Reindex database
  4. Attempt database repair
  5. Replace current database with newest usable backup copy
  6. Undo last successful action (Vacuum, Reindex, Repair, or Replace)
  7. Import Viewstate / Watch history from another PMS database
  8. Show logfile
  9. Exit

Enter choice: 9

Ok to remove temporary databases/workfiles for this session? (Y/N) ? y
Are you sure (Y/N) ? n
Retaining all temporary work files.
root@dionysus:~# cd /var/run/s6/services/
root@dionysus:/var/run/s6/services# s6-svc -u plex
root@dionysus:/var/run/s6/services# exit
exit
andrew@nas:/mnt/cache/docker/dionysus/Library/Application Support/Plex Media Server/Plug-in Support/Databases$ docker logs -f dionysus
[s6-init] making user provided files available at /var/run/s6/etc...exited 0.
[s6-init] ensuring user provided files have correct perms...exited 0.
[fix-attrs.d] applying ownership & permissions fixes...
[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] 40-plex-first-run: executing...
Plex Media Server first run setup complete
[cont-init.d] 40-plex-first-run: exited 0.
[cont-init.d] 45-plex-hw-transcode-and-connected-tuner: executing...
[cont-init.d] 45-plex-hw-transcode-and-connected-tuner: exited 0.
[cont-init.d] 50-plex-update: executing...
Attempting to upgrade to: 1.30.1.6497-5fc2e0894
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   190    0   190    0     0    414      0 --:--:-- --:--:-- --:--:--   413
100 81.6M  100 81.6M    0     0  43.8M      0  0:00:01  0:00:01 --:--:-- 65.6M
Selecting previously unselected package plexmediaserver.
(Reading database ... 7274 files and directories currently installed.)
Preparing to unpack /tmp/plexmediaserver.deb ...
PlexMediaServer install: Pre-installation Validation.
PlexMediaServer install: Custom environment detected.  Skipping preinstallation validation.
Unpacking plexmediaserver (1.30.1.6497-5fc2e0894) ...
Setting up plexmediaserver (1.30.1.6497-5fc2e0894) ...
PlexMediaServer install: Custom environment detected.  Skipping postinstallation tasks. Continuing.
[cont-init.d] 50-plex-update: exited 0.
[cont-init.d] done.
[services.d] starting services
Starting Plex Media Server.
[services.d] done.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
^C
andrew@nas:/mnt/cache/docker/dionysus/Library/Application Support/Plex Media Server/Plug-in Support/Databases$ docker restart dionysus
dionysus
andrew@nas:/mnt/cache/docker/dionysus/Library/Application Support/Plex Media Server/Plug-in Support/Databases$ docker logs -f dionysus
[s6-init] making user provided files available at /var/run/s6/etc...exited 0.
[s6-init] ensuring user provided files have correct perms...exited 0.
[fix-attrs.d] applying ownership & permissions fixes...
[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] 40-plex-first-run: executing...
Plex Media Server first run setup complete
[cont-init.d] 40-plex-first-run: exited 0.
[cont-init.d] 45-plex-hw-transcode-and-connected-tuner: executing...
[cont-init.d] 45-plex-hw-transcode-and-connected-tuner: exited 0.
[cont-init.d] 50-plex-update: executing...
Attempting to upgrade to: 1.30.1.6497-5fc2e0894
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   190    0   190    0     0    414      0 --:--:-- --:--:-- --:--:--   413
100 81.6M  100 81.6M    0     0  43.8M      0  0:00:01  0:00:01 --:--:-- 65.6M
Selecting previously unselected package plexmediaserver.
(Reading database ... 7274 files and directories currently installed.)
Preparing to unpack /tmp/plexmediaserver.deb ...
PlexMediaServer install: Pre-installation Validation.
PlexMediaServer install: Custom environment detected.  Skipping preinstallation validation.
Unpacking plexmediaserver (1.30.1.6497-5fc2e0894) ...
Setting up plexmediaserver (1.30.1.6497-5fc2e0894) ...
PlexMediaServer install: Custom environment detected.  Skipping postinstallation tasks. Continuing.
[cont-init.d] 50-plex-update: exited 0.
[cont-init.d] done.
[services.d] starting services
Starting Plex Media Server.
[services.d] done.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: file is not a database for SQL: PRAGMA cache_size=1 (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
[cont-finish.d] executing container finish scripts...
[cont-finish.d] done.
[s6-finish] waiting for services.
[s6-finish] sending all processes the TERM signal.
[s6-finish] sending all processes the KILL signal and exiting.
[s6-init] making user provided files available at /var/run/s6/etc...exited 0.
[s6-init] ensuring user provided files have correct perms...exited 0.
[fix-attrs.d] applying ownership & permissions fixes...
[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] 40-plex-first-run: executing...
[cont-init.d] 40-plex-first-run: exited 0.
[cont-init.d] 45-plex-hw-transcode-and-connected-tuner: executing...
[cont-init.d] 45-plex-hw-transcode-and-connected-tuner: exited 0.
[cont-init.d] 50-plex-update: executing...
[cont-init.d] 50-plex-update: exited 0.
[cont-init.d] done.
[services.d] starting services
Starting Plex Media Server.
[services.d] done.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
^C
andrew@nas:/mnt/cache/docker/dionysus/Library/Application Support/Plex Media Server/Plug-in Support/Databases$ docker exec -it dionysus bash
root@dionysus:/# cd /var/
backups/ cache/   lib/     local/   lock/    log/     mail/    opt/     run/     spool/   tmp/
root@dionysus:/# cd /var/
backups/ cache/   lib/     local/   lock/    log/     mail/    opt/     run/     spool/   tmp/
root@dionysus:/# cd /var/run/s6/services/
root@dionysus:/var/run/s6/services# s6-svc -d plex
root@dionysus:/var/run/s6/services# sh DBRepair.sh
sh: 0: Can't open DBRepair.sh
root@dionysus:/var/run/s6/services# s6-svc -d plex^C
root@dionysus:/var/run/s6/services# cd /config/
root@dionysus:~# sh DBRepair.sh



      Plex Media Server Database Repair Utility (Docker)

Select

  1. Check database
  2. Vacuum database
  3. Reindex database
  4. Attempt database repair
  5. Replace current database with newest usable backup copy
  6. Undo last successful action (Vacuum, Reindex, Repair, or Replace)
  7. Import Viewstate / Watch history from another PMS database
  8. Show logfile
  9. Exit

Enter choice: 1

Checking the PMS databases
Check complete.  PMS main database is OK.
Check complete.  PMS blobs database is OK.


      Plex Media Server Database Repair Utility (Docker)

Select

  1. Check database
  2. Vacuum database
  3. Reindex database
  4. Attempt database repair
  5. Replace current database with newest usable backup copy
  6. Undo last successful action (Vacuum, Reindex, Repair, or Replace)
  7. Import Viewstate / Watch history from another PMS database
  8. Show logfile
  9. Exit

Enter choice: 2

Backing up databases
Backup current databases with '-ORIG-2023-01-08_10.06.06' timestamp.
Vacuuming main database
Vacuuming main database successful (Size: 279MB/279MB).
Vacuuming blobs database
Vacuuming blobs database successful (Size: 1MB/1MB).
Vacuum complete.


      Plex Media Server Database Repair Utility (Docker)

Select

  1. Check database
  2. Vacuum database
  3. Reindex database
  4. Attempt database repair
  5. Replace current database with newest usable backup copy
  6. Undo last successful action (Vacuum, Reindex, Repair, or Replace)
  7. Import Viewstate / Watch history from another PMS database
  8. Show logfile
  9. Exit

Enter choice: 3

Backing up of databases
Backup current databases with '-ORIG-2023-01-08_10.06.11' timestamp.
Reindexing main database
Reindexing main database successful.
Reindexing blobs database
Reindexing blobs database successful.
Reindex complete.


      Plex Media Server Database Repair Utility (Docker)

Select

  1. Check database
  2. Vacuum database
  3. Reindex database
  4. Attempt database repair
  5. Replace current database with newest usable backup copy
  6. Undo last successful action (Vacuum, Reindex, Repair, or Replace)
  7. Import Viewstate / Watch history from another PMS database
  8. Show logfile
  9. Exit

Enter choice: 4

Exporting current databases using timestamp: 2023-01-08_10.06.17
Export: (main)..(blobs)..done.
Successfully exported the main and blobs databases.  Proceeding to import into new databases.
Import: (main)..(blobs)..done.
Successfully imported data from exported SQL files.
Verifying databases integrity after importing.
Verification complete.  PMS main database is OK.
Verification complete.  PMS blobs database is OK.
Saving current databases with '-ORIG-2023-01-08_10.06.17'
Making imported databases active
Import complete. Please check your library settings and contents for completeness.
Recommend:  Scan Files and Refresh all metadata for each library section.


      Plex Media Server Database Repair Utility (Docker)

Select

  1. Check database
  2. Vacuum database
  3. Reindex database
  4. Attempt database repair
  5. Replace current database with newest usable backup copy
  6. Undo last successful action (Vacuum, Reindex, Repair, or Replace)
  7. Import Viewstate / Watch history from another PMS database
  8. Show logfile
  9. Exit

Enter choice: 1

Checking the PMS databases
Check complete.  PMS main database is OK.
Check complete.  PMS blobs database is OK.

Seems like even with the DB repair, the DB is stuck in readonly mode.

[services.d] done.
Error: Unable to set up server: sqlite3_statement_backend::loadOne: attempt to write a readonly database (N4soci10soci_errorE)
Stopping Plex Media Server.

ChuckPa · January 8, 2023, 5:05pm

Check the filesystem owner & permissions???

cd  ......../Databases
ls -la

Share here please

Also share the UID/GID the container is running as.

The tool runs as ‘root’ and restores the owner’s UID/GID when done.
From the output, you can see the DB is fine.

As you retest this, I’d try NOT to use the PRAGMAs on it .
See if this was nothing more than a sorely needed case of TLC.

savantali · January 8, 2023, 5:30pm

On Host:

$ id
uid=1000(me) gid=1000(me) groups=1000(me),4(adm),24(cdrom),27(sudo),30(dip),46(plugdev),116(lxd),119(docker)

:/mnt/cache/docker/dionysus/Library/Application Support/Plex Media Server/Plug-in Support/Databases$ ls -al
total 7.9G
drwxr-xr-x 3 me 4.0K Jan  8 11:23 ./
drwxr-xr-x 7 me 4.0K Dec 26  2021 ../
-rw-r--r-- 1 root   3.1K Jan  8 10:08 DBRepair.log
-rw-r--r-- 1 me  68K Jan  2 01:01 com.plexapp.dlna.db
-rw-r--r-- 1 me  32K Jan  2 01:01 com.plexapp.dlna.db-shm
-rw-r--r-- 1 me 286K Jan  2 01:01 com.plexapp.dlna.db-wal
-rw-r--r-- 1 root      0 Jan  8 10:06 com.plexapp.plugins.library.blobs.db
-rw-r--r-- 1 me 1.2G Dec 27 01:49 com.plexapp.plugins.library.blobs.db-2022-12-27
-rw-r--r-- 1 me 1.2G Dec 30 00:04 com.plexapp.plugins.library.blobs.db-2022-12-30
-rw-r--r-- 1 me 1.2G Jan  2 01:06 com.plexapp.plugins.library.blobs.db-2023-01-02
-rw-r--r-- 1 me 1.2G Jan  5 00:01 com.plexapp.plugins.library.blobs.db-2023-01-05
-rw-r--r-- 1 me 280M Jan  8 11:23 com.plexapp.plugins.library.db
-rw-r--r-- 1 me 310M Dec 27 01:49 com.plexapp.plugins.library.db-2022-12-27
-rw-r--r-- 1 me 304M Dec 30 00:04 com.plexapp.plugins.library.db-2022-12-30
-rw-r--r-- 1 me 307M Jan  2 01:06 com.plexapp.plugins.library.db-2023-01-02
-rw-r--r-- 1 me 309M Jan  5 00:01 com.plexapp.plugins.library.db-2023-01-05
-rw-r--r-- 1 me  32K Jan  8 11:24 com.plexapp.plugins.library.db-shm
-rw-r--r-- 1 me    0 Jan  8 11:23 com.plexapp.plugins.library.db-wal
-rw-r--r-- 1 me 307M Jan  7 05:58 com.plexapp.plugins.library.db.backup
-rw-r--r-- 1 me 307M Jan  8 08:35 com.plexapp.plugins.library.db.db-recovery
-rw-r--r-- 1 me 305M Jan  8 09:30 com.plexapp.plugins.library.db.db-recovery-2
-rw-r--r-- 1 root   307M Jan  8 08:26 com.plexapp.plugins.library.db.original
-rw-r--r-- 1 me  32K Jan  8 08:32 com.plexapp.plugins.library.db.original-shm
-rw-r--r-- 1 me    0 Jan  8 08:32 com.plexapp.plugins.library.db.original-wal
-rw-rw-r-- 1 me 593M Jan  8 09:30 db-recover.sqlite

Docker Environment:

"PLEX_UID=1000",
"PLEX_GID=1000",

Inside Container:

root@dionysus:/# ls -al /config/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases/
total 8212716
drwxr-xr-x 3 plex plex       4096 Jan  8 11:23 .
drwxr-xr-x 7 plex plex       4096 Dec 26  2021 ..
-rw-r--r-- 1 root root       3145 Jan  8 10:08 DBRepair.log
-rw-r--r-- 1 plex plex      69632 Jan  2 01:01 com.plexapp.dlna.db
-rw-r--r-- 1 plex plex      32768 Jan  2 01:01 com.plexapp.dlna.db-shm
-rw-r--r-- 1 plex plex     292552 Jan  2 01:01 com.plexapp.dlna.db-wal
-rw-r--r-- 1 root root          0 Jan  8 10:06 com.plexapp.plugins.library.blobs.db
-rw-r--r-- 1 plex plex 1213507584 Dec 27 01:49 com.plexapp.plugins.library.blobs.db-2022-12-27
-rw-r--r-- 1 plex plex 1214789632 Dec 30 00:04 com.plexapp.plugins.library.blobs.db-2022-12-30
-rw-r--r-- 1 plex plex 1216355328 Jan  2 01:06 com.plexapp.plugins.library.blobs.db-2023-01-02
-rw-r--r-- 1 plex plex 1217798144 Jan  5 00:01 com.plexapp.plugins.library.blobs.db-2023-01-05
-rw-r--r-- 1 plex plex  293064704 Jan  8 11:23 com.plexapp.plugins.library.db
-rw-r--r-- 1 plex plex  324436992 Dec 27 01:49 com.plexapp.plugins.library.db-2022-12-27
-rw-r--r-- 1 plex plex  318258176 Dec 30 00:04 com.plexapp.plugins.library.db-2022-12-30
-rw-r--r-- 1 plex plex  321402880 Jan  2 01:06 com.plexapp.plugins.library.db-2023-01-02
-rw-r--r-- 1 plex plex  323121152 Jan  5 00:01 com.plexapp.plugins.library.db-2023-01-05
-rw-r--r-- 1 plex plex      32768 Jan  8 11:29 com.plexapp.plugins.library.db-shm
-rw-r--r-- 1 plex plex          0 Jan  8 11:23 com.plexapp.plugins.library.db-wal
-rw-r--r-- 1 plex plex  321070080 Jan  7 05:58 com.plexapp.plugins.library.db.backup
-rw-r--r-- 1 plex plex  321070080 Jan  8 08:35 com.plexapp.plugins.library.db.db-recovery
-rw-r--r-- 1 plex plex  319293440 Jan  8 09:30 com.plexapp.plugins.library.db.db-recovery-2
-rw-r--r-- 1 root root  321070080 Jan  8 08:26 com.plexapp.plugins.library.db.original
-rw-r--r-- 1 plex plex      32768 Jan  8 08:32 com.plexapp.plugins.library.db.original-shm
-rw-r--r-- 1 plex plex          0 Jan  8 08:32 com.plexapp.plugins.library.db.original-wal
-rw-rw-r-- 1 plex plex  620870296 Jan  8 09:30 db-recover.sqlite
drwxr-xr-x 2 root root       4096 Jan  8 10:06 dbtmp
-rw-rw-r-- 1 plex plex   62005248 Jan  6 15:31 tv.plex.providers.epg.cloud-3ccbc3b6-c597-4720-b367-e338a5c123d9.db
-rw-rw-r-- 1 plex plex      32768 Jan  6 15:36 tv.plex.providers.epg.cloud-3ccbc3b6-c597-4720-b367-e338a5c123d9.db-shm
-rw-rw-r-- 1 plex plex    1107768 Jan  6 15:31 tv.plex.providers.epg.cloud-3ccbc3b6-c597-4720-b367-e338a5c123d9.db-wal
root@dionysus:/#

If I edit the blobs.db file to plex, I then receive the following:

Starting Plex Media Server.
Error: Unable to set up server: sqlite3_statement_backend::prepare: no such table: schema_migrations for SQL: select version from schema_migrations order by version (N4soci10soci_errorE)
Stopping Plex Media Server.

ChuckPa · January 8, 2023, 6:13pm

Inside the container, you want the UID:GID to match what you assigned in the container definition.

See the zero-byte DB file? That’s the problem.

I think this is going to require some asymmetric repair work. The files shouldn’t be owned by root . ( Did you do something manually at the command line ? )

The normal squence 1-4-3 works in most cases but given you’re showing me several zero-byte files, this is going to require we use the other function of the tool. ( Replace using backup )

What is the most recent date you can remember when everything was working correctly (ignoring performance)?

savantali · January 8, 2023, 6:46pm

Inside the container, they do match the UID/GID.

I don’t know, but I would assume the last time it worked was before the upgrade was released, so the oldest DB backup will hopefully work, if not, then I am not sure.

With that said, I tried the second newest backup:

Starting Plex Media Server.


Critical: libusb_init failed

So seems to be working.

ChuckPa · January 8, 2023, 7:00pm

In a container, unless you’re using USB tuners, I ignore libusb_init errors.
If you have a DB which is working, may I see a current directory listing so I may give you current instructions ?
Lastly, given the amount of performance gain seen by those who’ve used this tool, would you consider NOT using any PRAGMA statements until after evaluating the result ?

savantali · January 8, 2023, 7:47pm

1, Yeah, I have been ignoring them as well.
2.

root@dionysus:/# ls -al /config/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases/
total 9427336
drwxr-xr-x 2 plex plex       4096 Jan  8 12:58 .
drwxr-xr-x 7 plex plex       4096 Dec 26  2021 ..
-rw-r--r-- 1 root root       5526 Jan  8 12:48 DBRepair.log
-rw-r--r-- 1 plex plex      69632 Jan  8 12:48 com.plexapp.dlna.db
-rw-r--r-- 1 plex plex      32768 Jan  8 12:48 com.plexapp.dlna.db-shm
-rw-r--r-- 1 plex plex     292552 Jan  8 12:48 com.plexapp.dlna.db-wal
-rw-r--r-- 1 plex plex 1217348608 Jan  8 13:36 com.plexapp.plugins.library.blobs.db
-rw-r--r-- 1 plex plex 1213507584 Dec 27 01:49 com.plexapp.plugins.library.blobs.db-2022-12-27
-rw-r--r-- 1 plex plex 1214789632 Dec 30 00:04 com.plexapp.plugins.library.blobs.db-2022-12-30
-rw-r--r-- 1 plex plex 1216355328 Jan  2 01:06 com.plexapp.plugins.library.blobs.db-2023-01-02
-rw-r--r-- 1 plex plex 1217798144 Jan  5 00:01 com.plexapp.plugins.library.blobs.db-2023-01-05
-rw-r--r-- 1 plex plex      32768 Jan  8 13:37 com.plexapp.plugins.library.blobs.db-shm
-rw-r--r-- 1 plex plex    1121392 Jan  8 13:37 com.plexapp.plugins.library.blobs.db-wal
-rw-r--r-- 1 plex plex  320329728 Jan  8 13:36 com.plexapp.plugins.library.db
-rw-r--r-- 1 plex plex  324436992 Dec 27 01:49 com.plexapp.plugins.library.db-2022-12-27
-rw-r--r-- 1 plex plex  318258176 Dec 30 00:04 com.plexapp.plugins.library.db-2022-12-30
-rw-r--r-- 1 plex plex  321402880 Jan  2 01:06 com.plexapp.plugins.library.db-2023-01-02
-rw-r--r-- 1 plex plex  323121152 Jan  5 00:01 com.plexapp.plugins.library.db-2023-01-05
-rw-r--r-- 1 plex plex      32768 Jan  8 13:46 com.plexapp.plugins.library.db-shm
-rw-r--r-- 1 plex plex    1228288 Jan  8 13:46 com.plexapp.plugins.library.db-wal
-rw-r--r-- 1 plex plex  321070080 Jan  7 05:58 com.plexapp.plugins.library.db.backup
-rw-r--r-- 1 plex plex  321070080 Jan  8 08:35 com.plexapp.plugins.library.db.db-recovery
-rw-r--r-- 1 plex plex  319293440 Jan  8 09:30 com.plexapp.plugins.library.db.db-recovery-2
-rw-r--r-- 1 root root  321070080 Jan  8 08:26 com.plexapp.plugins.library.db.original
-rw-r--r-- 1 plex plex      32768 Jan  8 08:32 com.plexapp.plugins.library.db.original-shm
-rw-r--r-- 1 plex plex          0 Jan  8 08:32 com.plexapp.plugins.library.db.original-wal
-rw-rw-r-- 1 plex plex  620870296 Jan  8 09:30 db-recover.sqlite
-rw-rw-r-- 1 plex plex   59866112 Jan  8 12:58 tv.plex.providers.epg.cloud-3ccbc3b6-c597-4720-b367-e338a5c123d9.db
-rw-rw-r-- 1 plex plex      32768 Jan  8 13:00 tv.plex.providers.epg.cloud-3ccbc3b6-c597-4720-b367-e338a5c123d9.db-shm
-rw-rw-r-- 1 plex plex       8416 Jan  8 12:58 tv.plex.providers.epg.cloud-3ccbc3b6-c597-4720-b367-e338a5c123d9.db-wal
root@dionysus:/#

Sure, I have been reading about some of the recent improvements, and it seems like they might have answered the issues I had which caused me to split the plex server into 3 separate instances.

ChuckPa · January 8, 2023, 8:08pm

Given you now have a working DB, Lets see what we can get for performance gains.

Stop Plex
Run option 4 (Repair – which is an export-import), followed by option 3 (Index)
Exit but KEEP the temp files
Start Plex and evaluate

Topic		Replies	Views
Suggested SQLite3 DB Optimizations Plex Media Server server-linux	142	4013	June 11, 2023
Library.db size more than doubled in latest version Plex Media Server server-windows , server-linux	432	14391	March 6, 2026
DBRepair development Apps & Creations	869	14424	April 29, 2026
Sqlite3: Sleeping for 200ms to retry busy DB Plex Media Server server-linux	59	330	June 1, 2023
Would it be possible to get some help with -[Req#a34/Database optimization/com.plexapp.plugins.libra Remote Access server-synology , media-optimizer	48	410	October 10, 2023

Support Doc for Database Cache Size (MB)?

Related topics