Could Not Determine SQL Lite Path Error
Closed this issue · 26 comments
Hi
I have been directed here by Plex Ninja (Otto) to see if the repair tool will fix my suspected corrupt Plex DB
I have downloaded the files, I am running it on a Windows server
When I run the DBRepair-Windows.bat in elevated CMD mode I get the error message "Could Not Determine SQL Lite Path"
My SQL Lite for plex is in the plex install directory of C:\Program Files (x86)\Plex\Plex Media Server
I am not so technical but I think the batch file to start the DB repair is looking for the SQL Lite program in C:\Program Files\Plex\Plex Media Server directory (I guess I am running the 32 bit plex server)
Is there a way for me to still run the DB repair on the version and place where its installed on my windows server??
Many thanks
Apologies if this is a dumb user error and Ive missed something somewhere
I didn't realize there is a difference. (I had help with Windows)
In the script, it looks for "%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe"
If you saved a copy, then changed %PROGRAMFILES%\ to %PROGRAMFILES% (x86)\
that might be enough.
Would you be willing to edit it and let me know the results?
Which value (path) did you hard code for the Plex SQLite?
I can handle getting the variables right.
Is this the right path?
C:\Program Files (x86)\Plex\Plex Media Server\Plex SQLite.exe
Would you try / look at this please ?
did it show which line?
Look at these pathnames, which is correct / incorrect?
REM Find PMS installation location.
for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKCU\Software\Plex, Inc.\Plex Media Server" /v "InstallFolder" 2^> nul') do set "PlexSQL=%%b\Plex SQLite.exe"
if not exist "%PlexSQL%" (
if exist "%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe" (
set "PlexSQL=%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe"
goto :FoundSQL
) else if exist "%PROGRAMFILES(x86)%\Plex\Plex Media Server\Plex SQLite.exe" (
set "PlexSQL=%PROGRAMFILES(x86)%\Plex\Plex Media Server\Plex SQLite.exe"
goto :FoundSQL
) else (
echo Could not determine SQLite path.
echo Normally %PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe
echo Or %PROGRAMFILES% (x86)\Plex\Plex Media Server on 64 bit systems
echo.
goto :EOF
)
)
It sounds like I'm accidentally adding something.
Can you show me the exact path?
Chatting with Otto,
You do not need to run elevated privileges. PMS runs in your normal user account.
Run the script in whichever account PMS runs as.
I have found my Windows 10 VM.
I have discovered the Plex installer writes incorrect information in the registry.
I will report this as a bug ( a big bug )
I will update the tool to ignore the registry
I have discovered the Plex installer writes incorrect information in the registry.
I will report this as a bug ( a big bug )
I will update the tool to ignore the registry
I saw this as well from investigating this reddit thread, and created my own fork to address it before I saw this thread that seems to be the ~same issue.
Hi Dan
Thanks
I tried to copy your lines of script from the fork you posted to replace the same section that starts "REM Find PMS installation location" in Chucks script
Assuming I did it right and didnt mess it up, I got an error message that said
"" was unexpected at this time.
I am afraid I have no knowledge of scripting, syntx etc so dont know at what point that error message happens
I have attached Chucks script with the section replaced by your script
Many thanks
@echo off
REM PlexDBRepair.bat - Database maintenance / rebuild tool for Windows.
REM
REM This tool currently works as a "full shot" service.
REM - everything is done without need to interact.
REM
REM -- WARNNING -- WARNING -- WARNING
REM
REM This is stable working software but not "Released" software. Development will continue.
REM
REM ### Create Timestamp
set Hour=%time:~0,2%
set Min=%time:~3,2%
set Sec=%time:~6,2%
REM ## Remove spaces from Hour ##
set Hour=%Hour: =%
REM ## Set TimeStamp ##
set TimeStamp=%Hour%-%Min%-%Sec%
REM Find PMS database location
for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKCU\Software\Plex, Inc.\Plex Media Server" /v "LocalAppDataPath" 2^> nul') do set "PlexData=%%b\Plex Media Server\Plug-in Support\Databases"
if not exist "%PlexData%" (
if exist "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases" (
set "PlexData=%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases"
) else (
echo Could not determine Plex database path.
echo Normally %LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases
echo.
goto :EOF
)
)
REM Find PMS installation location.
REM As of PMS 1.40.2, a 32-bit install on a 64-bit OS sets InstallFolder to 'C:\Program Files\Plex\Plex Media Server', even though it's actually installed at
REM 'C:\Program Files (x86)\Plex\Plex Media Server'. Because of that, overwrite InstallFolder if %PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe exists
for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKCU\Software\Plex, Inc.\Plex Media Server" /v "InstallFolder" 2^> nul') do set "PlexSQL=%%b\Plex SQLite.exe"
if not exist "%PlexSQL%" (
if exist "%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe" (
set "PlexSQL=%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe"
) else (
echo Could not determine SQLite path.
echo Normally %PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe
echo.
goto :EOF
)
) else (
if exist "%PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe" (
echo WARN: 32-bit version of PMS detected on a 64-bit version of Windows. Updating to the 64-bit release of PMS is recommended.
set "PlexSQL=%PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe"
) else (
if exist "%PROGRAMFILES(ARM)%\Plex\Plex Media Server\Plex SQLite.exe" (
set "PlexSQL=%PROGRAMFILES(ARM)%\Plex\Plex Media Server\Plex SQLite.exe"
) else if not exists "%PlexSQL%" (
echo Could not determine SQLite path.
echo Normally %PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe
echo.
goto :EOF
)
)
)
REM Set temporary file locations
set "DBtmp=%PlexData%\dbtmp"
set "TmpFile=%DBtmp%\results.tmp"
REM Time now.
echo %time% -- ====== Session begins. (%date%) ======
echo %time% -- ====== Session begins. (%date%) ====== >> "%PlexData%\PlexDBRepair.log"
REM Make certain Plex is NOT running.
tasklist | find /I "Plex Media Server.exe" >NUL
if %ERRORLEVEL%==0 (
echo %time% -- Plex is running. Please stop Plex Media Server and try again.
echo %time% -- Plex is running. Please stop Plex Media Server and try again. >> "%PlexData%\PlexDBRepair.log"
exit /B 1
)
cd "%PlexData%"
md "%PlexData%\dbtmp" 2>NUL
del "%TmpFile%" 2>NUL
echo %time% -- Exporting Main DB
echo %time% -- Exporting Main DB >> "%PlexData%\PlexDBRepair.log"
echo .dump | "%PlexSQL%" "%PlexData%\com.plexapp.plugins.library.db" > "%DBtmp%\library.sql_%TimeStamp%"
if not %ERRORLEVEL%==0 (
echo %time% -- ERROR: Cannot export Main DB. Aborting.
exit /b 2
)
echo %time% -- Exporting Blobs DB
echo %time% -- Exporting Blobs DB >> "%PlexData%\PlexDBRepair.log"
echo .dump | "%PlexSQL%" "%PlexData%\com.plexapp.plugins.library.blobs.db" > "%DBtmp%\blobs.sql_%TimeStamp%"
if not %ERRORLEVEL%==0 (
echo %time% -- ERROR: Cannot export Blobs DB. Aborting.
)
REM Now create new databases from SQL statements
echo %time% -- Exporting Complete.
echo %time% -- Exporting Complete. >> "%PlexData%\PlexDBRepair.log"
echo %time% -- Creating Main DB
echo %time% -- Creating Main DB >> "%PlexData%\PlexDBRepair.log"
"%PlexSQL%" "%PlexData%\com.plexapp.plugins.library.db_%TimeStamp%" < "%DBtmp%\library.sql_%TimeStamp%"
if not %ERRORLEVEL%==0 (
echo %time% -- ERROR: Cannot create Main DB. Aborting.
echo %time% -- ERROR: Cannot create Main DB. Aborting. >> "%PlexData%\PlexDBRepair.log"
exit /b 3
)
echo %time% -- Verifying Main DB
echo %time% -- Verifying Main DB >> "%PlexData%\PlexDBRepair.log"
"%PlexSQL%" "%PlexData%\com.plexapp.plugins.library.db_%TimeStamp%" "PRAGMA integrity_check(1)" >"%TmpFile%"
set /p Result= < "%TmpFile%"
del "%TmpFile%"
echo %time% -- Main DB verification check is: %Result%
echo %time% -- Main DB verification check is: %Result% >> "%PlexData%\PlexDBRepair.log"
if not "%Result%" == "ok" (
echo %time% -- ERROR: Main DB verificaion failed. Exiting.
echo %time% -- ERROR: Main DB verificaion failed. Exiting. >> "%PlexData%\PlexDBRepair.log"
exit /B 4
)
echo %time% -- Main DB verification successful.
echo %time% -- Main DB verification successful. >> "%PlexData%\PlexDBRepair.log"
echo %time% -- Creating Blobs DB
echo %time% -- Creating Blobs DB >> "%PlexData%\PlexDBRepair.log"
"%PlexSQL%" "%PlexData%\com.plexapp.plugins.library.blobs.db_%TimeStamp%" < "%DBtmp%\blobs.sql_%TimeStamp%"
if not %ERRORLEVEL%==0 (
echo %time% -- ERROR: Cannot create Blobs DB. Aborting.
echo %time% -- ERROR: Cannot create Blobs DB. Aborting. >> "%PlexData%\PlexDBRepair.log"
exit /b 5
)
echo %time% -- Verifying Blobs DB
echo %time% -- Verifying Blobs DB >> "%PlexData%\PlexDBRepair.log"
"%PlexSQL%" "%PlexData%\com.plexapp.plugins.library.blobs.db_%TimeStamp%" "PRAGMA integrity_check(1)" > "%TmpFile%"
set /p Result= < "%TmpFile%"
del "%TmpFile%"
echo %time% -- Blobs DB verification check is: %Result%
echo %time% -- Blobs DB verification check is: %Result% >> "%PlexData%\PlexDBRepair.log"
if not "%Result%" == "ok" (
echo %time% -- ERROR: Blobs DB verificaion failed. Exiting.
echo %time% -- ERROR: Blobs DB verificaion failed. Exiting. >> "%PlexData%\PlexDBRepair.log"
exit /B 6
)
echo %time% -- Blobs DB verification successful.
echo %time% -- Blobs DB verification successful. >> "%PlexData%\PlexDBRepair.log"
echo %time% -- Import and verification complete.
echo %time% -- Import and verification complete. >> "%PlexData%\PlexDBRepair.log"
REM Import complete, now reindex
echo %time% -- Reindexing Main DB
echo %time% -- Reindexing Main DB >> "%PlexData%\PlexDBRepair.log"
"%PlexSQL%" "%PlexData%\com.plexapp.plugins.library.db_%TimeStamp%" "REINDEX;"
echo %time% -- Reindexing Blobs DB
echo %time% -- Reindexing Blobs DB >> "%PlexData%\PlexDBRepair.log"
"%PlexSQL%" "%PlexData%\com.plexapp.plugins.library.blobs.db_%TimeStamp%" "REINDEX;"
REM Index complete, make active
echo %time% -- Reindexing complete.
echo %time% -- Reindexing complete. >> "%PlexData%\PlexDBRepair.log"
echo %time% -- Moving current DBs to DBTMP and making new databases active
echo %time% -- Moving current DBs to DBTMP and making new databases active >> "%PlexData%\PlexDBRepair.log"
move "%PlexData%\com.plexapp.plugins.library.db" "%PlexData%\dbtmp\com.plexapp.plugins.library.db_%TimeStamp%"
move "%PlexData%\com.plexapp.plugins.library.db_%TimeStamp%" "%PlexData%\com.plexapp.plugins.library.db"
move "%PlexData%\com.plexapp.plugins.library.blobs.db" "%PlexData%\dbtmp\com.plexapp.plugins.library.blobs.db_%TimeStamp%"
move "%PlexData%\com.plexapp.plugins.library.blobs.db_%TimeStamp%" "%PlexData%\com.plexapp.plugins.library.blobs.db"
echo %time% -- Database repair/rebuild/reindex completed.
echo %time% -- Database repair/rebuild/reindex completed. >> "%PlexData%\PlexDBRepair.log"
echo %time% -- ====== Session completed. ======
echo %time% -- ====== Session completed. ====== >> "%PlexData%\PlexDBRepair.log"
exit /b
REM #### Functions
REM Output - Write text to the console and the log file
:Output
echo %time% %~1
echo %time% %~1 >> "%PlexData%\PlexDBRepair.log"
exit /B
Sorry about that @chriskeeganhw, I was too hasty with some refactoring and ended up posting a broken script. I've pushed a new commit that should address it (danrahn@4e427a1).
Hi Dan
I think it must be me pasting code in the wrong place
Still get the same error "" was unexpected at this time.
I replaced this
REM 'C:\Program Files (x86)\Plex\Plex Media Server'. Because of that, overwrite InstallFolder if %PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe exists
for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKCU\Software\Plex, Inc.\Plex Media Server" /v "InstallFolder" 2^> nul') do set "PlexSQL=%%b\Plex SQLite.exe"
if not exist "%PlexSQL%" (
if exist "%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe" (
set "PlexSQL=%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe"
) else (
echo Could not determine SQLite path.
echo Normally %PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe
echo.
goto :EOF
)
) else (
if exist "%PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe" (
echo WARN: 32-bit version of PMS detected on a 64-bit version of Windows. Updating to the 64-bit release of PMS is recommended.
set "PlexSQL=%PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe"
) else (
if exist "%PROGRAMFILES(ARM)%\Plex\Plex Media Server\Plex SQLite.exe" (
set "PlexSQL=%PROGRAMFILES(ARM)%\Plex\Plex Media Server\Plex SQLite.exe"
) else if not exists "%PlexSQL%" (
echo Could not determine SQLite path.
echo Normally %PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe
echo.
goto :EOF
)
)
)
with
REM 'C:\Program Files (x86)\Plex\Plex Media Server'. Because of that, overwrite InstallFolder if %PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe exists
for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKCU\Software\Plex, Inc.\Plex Media Server" /v "InstallFolder" 2^> nul') do set "PlexSQL=%%b\Plex SQLite.exe"
if exist "%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe" (
if exist "%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe" (
set "PlexSQL=%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe"
) else (
if exist "%PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe" (
@@ -46,11 +46,13 @@ for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKCU\Software\Plex, Inc.\Plex
) else (
if exist "%PROGRAMFILES(ARM)%\Plex\Plex Media Server\Plex SQLite.exe" (
set "PlexSQL=%PROGRAMFILES(ARM)%\Plex\Plex Media Server\Plex SQLite.exe"
) else if not exists "%PlexSQL%" (
echo Could not determine SQLite path.
echo Normally %PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe
echo.
goto :EOF
) else (
if not exist "%PlexSQL%" (
echo Could not determine SQLite path.
echo Normally %PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe
echo.
goto :EOF
)
)
)
)
I'd avoid trying any partial copy/pasting and use the entire script directly. You can find the "raw" version here, which you can copy/paste in its entirety, or right-click > Save as: raw.githubusercontent.com/danrahn/PlexDBRepair/master/DBRepair-Windows.bat.
Also, as a future tip, if you paste code in a comment, you can surround it with three backticks to keep the formatting:
```
Your
Code
Here
```
becomes
Your
Code
Here
This is a weird but fun problem ?
On X64 machines, ProgramFiles (x86) exists. A 32 bit Plex is installed there.
On a x32 (x86) machine, everything ends up in Program files (e.g. "Program Files" is for all same-mode programs)
The problem I run into is the
if exists "......" (
)
logic, setting the variable values, then trying to continue
Windows loves to say unexpected
It's further compounded when folks install Plex in anything but the default location.
FWIW: I stopped using Windows completely in 2002. Haven't missed it 🤣
If anyone can help me finish this for all folks, I'll gladly implement immediately.
Please correct me if wrong, we need to solve for (without the registry)
- Install Path?
- Data Path ?
regarding fixing this.
I would prefer a PR filed, which "Fixes: #143"
This would make tracking all changes very easy for myself as well as everyone else (issues, prs, and commits all align)
ALSO, PLEASE squash before the PR. (multi-commit PR's are ugly)
I use VS Code editor for all this work.
- It understands the different scripting languages
- It's GitHub aware
Thinking outloud.
How about this idea? (do NOT know the correct syntax)
set PlexSQLite=""
for %%D in C D E (
if exists "%%D:\Program Files\Plex\Plex SQLite.exe" (
set PlexSQLite="%%D:\Program Files\Plex\Plex SQLite.exe"
)
if exists "%%D:\Program Files (x86)\Plex\Plex SQLite.exe" (
set PlexSQLite="%%D:\Program Files (x86)\Plex\Plex SQLite.exe"
)
)
If "%PlexSQLite" = "" (
echo ERROR: Is Plex installed? Cannot find PlexSQLite
exit /b 99
)
I would prefer a PR filed, which "Fixes: #143"
This would make tracking all changes very easy for myself as well as everyone else (issues, prs, and commits all align)
ALSO, PLEASE squash before the PR. (multi-commit PR's are ugly)
Sounds good! I was planning on opening a PR, but was hoping for confirmation that what I had so far worked on someone else's machine before sending it out, but I'll get what I have out soon.
As for drive letter iteration, I could see that as a potential fallback if the current approaches don't work, since I think in 99% of cases the InstallFolder/%PROGRAMFILES[(X86)]% checks would be more reliable. I also added a secondary fallback to user input if neither of those approaches work, but feel free to let me know in the PR if you think there's a better approach.
As I shared earlier. HKLM vs HKCU are the delineators.
(new versus old installers).
Not sure how windows handles the differences.
There's also a 'wow6432' out there.
Hope you gents can figure this out. My best windows skill is 💣 lol
would this likely mean any of those backup scripts that save the registry would be missing some registry entries, values and seetings?
From what I've seen, it's only InstallFolder that might be in HKLM instead of HKCU, but it's not something I've tested heavily. But that does still mean it's possible those backup scripts aren't capturing everything if they also aren't checking whether HKLM\SOFTWARE\Plex, Inc. (or apparently HKLM\SOFTWARE\WOW6432Node\Plex, Inc. as well, based on Chuck's new info).
It's very likely they can be.
Any registry grab worth its salt should know to check the return value from the REG call to confirm it's a valid location