"Repair a Corrupt Database" support topic incorrect and possibly damaging (in some cases)

Plex Media Server
1

The support topic titled Repair a Corrupt Database can present a potentially very annoying problem for Windows users, which can be easily avoided.

The issue is in step 4 of “run a repair”:
sqlite3 com.plexapp.plugins.library.db .dump > dump.sql
This might work for most Plex installations, but if the database contains any unicode symbols (such as japanese characters) and Powershell is used (I have not tested with cmd.exe), this command may mangle that text. This is due to the fact that the > symbol reads the output of the left command into the terminal and then outputs it into the file. In my case, this meant that all unicode characters were interpreted with the Latin-1 codepage (cp850), but were written to disk as UTF-8. For example:
"言わないけどね。.flac" was written to disk as "Þ¿ÇÒéÅÒü¬ÒüäÒüæÒü®Òü¡ÒÇé.flac" (as UTF-8)

Then, when reloading the database, the latter data was used for the filename and obviously did not work.

I suggest, for Windows at least, the step is changed from:
sqlite3 com.plexapp.plugins.library.db .dump > dump.sql
To:
sqlite3 com.plexapp.plugins.library.db ".output dump.sql" ".dump"
(and for parity step 6 can become sqlite3 com.plexapp.plugins.library.db ".read dump.sql")
Because this will mean that the terminal is not a part of the process whatsoever and instead it is SQLite which is doing all of the reading and writing.

5 Likes
2

When you tested this, were you using an up-to-date version of the sqlite-tools ?
Because a similar issue was reported a few years ago and it was fixed by an update of sqlite.

That being said, I tested your workflow and it does work.
I do think it is a good thing to not involve whatever shell is in use and let the sqlite tools do the work natively. So I consider this workflow a definitive improvement.

Thank you for this contribution!

1 Like
3

Good catch MrAttoAttoAtto. Don’t know if this applies to Linux as well but I changed my dumps to:

echo “.dump” | sqlite3 -cmd “.output /path/dbname.dump” “/path/dbname.db”

Didn’t find a way to make the settings fit into this new schema as well:

echo “.dump metadata_item_settings” | sqlite3 “/path/dbname.db” | grep -v TABLE | grep -v INDEX > “/path/settings.sql”

Any ideas?

4

What do you want to achieve with that? Is this for processing with an external script or something?

5

Hm, I found that command in one of the support pages here a year or two ago. It’s a backup of the view stats - that’s what I read.

No longer required?

6

If you backup the whole database instead, no. :wink:
Can’t hurt, though.

7

Yep, I always take the long and save way. After over 41 years in IT I’ve seen all kinds of trouble. I can’t have enough backups of different kinds and at different locations.

8

So true.

9

I used the latest sqlite tools - version 3.33.0.

Do note that the command may (again, I have not tested it) have to be different for Linux/MacOS, because some StackExchange post claimed that chaining the so-called “dot-commands” (.dump and .output) in this way only works (in this format) for Windows. YMMV.

By the way, if anyone has a similar problem and wants to fix their .sql file (or database), you can do the following (updated x2):

  1. If you do not have the .sql file yet, run sqlite3 com.plexapp.plugins.library.db ".output dump.sql" ".dump"

  2. Open the terminal you used (which mangled the text) and run the command chcp. Take note of the output (e.g. Active code page: 850). It is most likely that if you have a US-based Windows copy, your output will be Active code page: 437, and if you have a (English) European copy, it will be Active code page: 850.

  3. Open Powershell and navigate to the directory with your dump.sql file

  4. Run the following command:
    Get-Content dump.sql | Out-File dumpfixed.sql -Encoding <chcp output>
    For example, if chcp gave the output Active code page: 850, your command would be:
    Get-Content dump.sql | Out-File dumpfixed.sql -Encoding 850. If the prompt spits out an error like Cannot validate argument on parameter 'Encoding', you have two options:

    1. Run the command Get-Content dump.sql | Out-File dumpfixed.sql -Encoding oem instead. If this works and all of the data in the sql file looks OK (be sure to check!), then option 2 is not required.
    2. Download Powershell Core (you can use the PowerShell-7.0.3-win-x86.zip file), then repeat the Powershell steps with Powershell Core. You can remove it once you’ve finished.

  5. The fixed file (dumpfixed.sql) can now be used however you wish. If you want to import this as your Plex library, you can run the following: (make sure Plex isn’t running)
    mv com.plexapp.plugins.library.db com.plexapp.plugins.library.db.old
    followed by
    sqlite3 com.plexapp.plugins.library.db ".read dumpfixed.sql"

1 Like
10

Thanks again! Unfortunately I can only give one ‘Like’ on the post :slight_smile:

1 Like
11

You’re a weirdo for using Powershell as your default. :slight_smile:

I wonder if sqlite3's --bom argument would have also resolved the issue. I haven’t tested with Powershell. But I agree that avoiding redirection entirely is a better solution.

I can confirm that sqlite3 database.db ".output dump.sql" ".dump" works on macOS and FreeBSD. I’m 99% sure it works on Linux and everywhere else.

Pipes and redirection of STDIN/STDOUT/STDERR in Linux/macOS/*nix should be binary-clean. They aren’t passed through or parsed by the shell in same way. But avoiding redirection is nice, and consistency is nicer.

1 Like
13

I can confirm that my commands work on Ubuntu 20.04 LTS with sqlite3 version 3.31.1

(I think Plex on Windows uses Powershell for its updater (at least its GUI); I can create BitLocker login prompts/GUIs and anything else a desktop app could want to do just from Powershell - that’s how awesome it is :star_struck:! Also, far more importantly, it aliases stuff like ls to Get-ChildItem (the equivalent of dir) and other lovely stuff, and it’s really easy to write functions for it like you would have in a .bashrc)

14

I’m a big fan of Powershell. As soon as you shift your perspective it becomes the “obvious” correct way to do things. :slight_smile: Don’t need to sell me!

I’m a little bit surprised that it modifies the output stream when redirecting to a file, but only a little. This will be useful to know in other contexts, too.

15

I was too, it’s kinda strange and not what I would call expected behaviour.

Anyone can test it for themselves by creating the following batch file and running it (and redirecting the output) in both CMD and Powershell (maybe it turns out I’ve messed up my system somehow and it only affects me…):
test.bat:

@echo off
echo キ

Then run it with
.\test.bat > out.txt
in both CMD and Powershell and inspect the output.

In CMD I get キ, but in Powershell I get Òé¡.

16

I see the same behavior as you.

Also, ugh, PowerShell and PowerShell Core behave differently:

17

I think that link refers to something different - Powershell does indeed output as UTF-8, it’s just that what it outputs is wrong. This is because (I assume) when it reads the bytes corresponding to キ, it assumed the bytes were encoded in my code page (850), so stored the result internally (as UTF-8) as “Òé¡”. Then, when it came to write this text, it writes it ‘correctly’ in UTF-8 to the file (but obviously it thinks it should be “Òé¡”).

You can see this as if you just run .\test.bat in Powershell, it outputs “Òé¡” (or your US equivalent) - hence it is misinterpreting the input, not fudging the output.

1 Like
18

No sorry, I agree! The codepage input/output issue you raised is different. I only mean that “Core” behaving differently from “PowerShell” is surprising, too.

19

Just one more reason why an alternative to SQLite is needed!

20

This is a Windows issue, not SQLite!

21

@bryanpetkus has made the same statement in multiple topics. I think SQLite stole his ice cream or kicked his puppy.

At first I thought he was following me around to post the same thing. It’s just threads that mention SQLite.

2 Likes