plexWatch 0.3.3-Dev upgrade not working with PDO driver for plexWatchWeb

[cookandy]

Hello,

This issue was opened against the plexWatchWeb (PWW) project first, but after much discussion we feel this issue belongs to plexWatch.

When accessing the 0.3.3 plexWatch database via PWW using the PDO driver, the following error occurs

There was a problem running "SELECT count(DISTINCT user) as users FROM processed". Error: SQLSTATE[HY000]: General error: 26 file is encrypted or is not a database

If I dump the plexWatch.db to file and then reimport it into a new sqlite3 DB, everything works fine. However, once running ./plexWatch.pl against the DB, the error occurs again and PWW is unable to access the database.

Our guess is that the perl modules for sqlite are manipulating the database to make it not compatible with the PDO driver. Any ideas?

[cookandy]

Does anyone monitor issues for plexWatch anymore?

[ljunkie]

Does anyone monitor issues for plexWatch anymore?

Yes, however it's not a full time job :) I'm a little lost here, so it may help to supply the bad plexWatch.db so I can investigate. Just like the others, I cannot replicate this issue.

[cookandy]

Hi @ljunkie. I can definitely send you a copy of a small database which works before running plexwatch.pl against it. I will also send you a copy of the DB after running plexwatch.pl. However, because there's sensitive information in them, can you please provide me with an email address to send them to?

[cookandy]

Hello,

I was able to reproduce this issue with a new database. Here's what I did:

1. Deleted plexWatch and pulled the latest (0.3.3-DEV) from github
2. Copied config.pl-dist to config.pl and updated the $data_dir only
3. Ran ./plexWatch.pl to create a new database at this time plexWatchWeb did NOT work - was giving the same error
4. Ran echo .dump | sqlite3 plexWatch.db | sqlite3 plexWatch.db.working to create new "plexWatch.db.working" database dump
5. Copied plexWatch.db.working to plexWatch.db at this time plexWatchWeb was WORKING
6. Ran ./plexWatch.pl to update the database plexWatchWeb STOPPED working - was giving the same error

You can find a copy of the working database dump here.
You can find a copy of the non-working database dump here.

Hope this helps.

[ljunkie]

Thanks. I'll see what I can find out. Can you supply the OS you are using? I think that has some role in it.

[cookandy]

Thanks. I'm running a QNAP TS-469

Linux NAS 3.4.6 #1 SMP Fri May 22 04:59:54 CST 2015 x86_64 unknown

[ljunkie]

Well that will be hard for me to test on, but I'll see what I can find out from this dump. It's really weird that the initial DB created breaks the web interface.

[cookandy]

Yes, one thing to mention is that the older versions of plexWatchWeb seem to work (I think before the PDO driver stuff was added). Maybe perl isn't using the PDO stuff correctly?

[ljunkie]

Maybe perl isn't using the PDO stuff correctly?

That doesn't make any sense if all you did was initialize a new DB and the web interface didn't work.

[ljunkie]

Either way. I'll see if I can replicate.

[ljunkie]

So it looks like PDO requires write access to the root directory of plexWatch.db and any WAL/SHM files, since the database is using WAL journalling.

To fix (quickly) without fully understanding what's going on here.

# set the group to apache (or whatever group your http server runs as)
chgrp apache /opt/plexWatch/

# set the group to apache for any plexWatch.db file (including wal/shm)
chgrp apache /opt/plexWatch/plexWatch.db*

# give write permissions to the group for the root dir and plexWatch.db (including wal/shm)
chmod g+rwx /opt/plexWatch/
chmod g+rwx /opt/plexWatch/plexWatch.db*

[Arcanemagus]

I don't think it's a directory permissions issue, when he dumps the database through the sqlite3 command line tool the re-created database works just fine. Although I suppose it's possible he is then moving it to a different directory it doesn't seem like that.

For some reason the method of access to the database that Perl on that machine is using changes the database in a manner that the sqlite3 PDO driver for PHP on that machine doesn't like.

I'll try to throw together a minimal test case between the two.

[ljunkie]

The reason a dump/restore works is that it drops the journaling method (wal). I'm willing to bet it's directory permission. A rerun of plexWatch.pl will set the journal mode, breaking this as it seems the new PDO method does need write access. I can see it creating wal/shm files when it works.

[Arcanemagus]

Hmm, the odd thing is the previous (direct sqlite commands) method also required the ability to create the WAL files, even when set to read-only mode. Over in the other thread he states that the older method worked fine while the PDO method has issues.

[ljunkie]

That doesn't seem to be the case for me. I can replicate this exact issue, so I guess we'll see if permissions fixes his issue once he tries.

[ljunkie]

Also, if it's not the case, then I cannot replicate as his broken DB works just fine for me with the right permissions.

[cookandy]

Hi guys,

I tried the permissions issue you suggested, but no luck. As @Arcanemagus says, when I run the sqlite command, the new database dump is created with the exact same permissions as the non-working DB. I verified that no permissions change between steps 5 and 6 of my replication steps above.

But interesting that you are able to run my non-working DB without any issues.

[ljunkie]

Yep, sadly the issue was conflated by the steps to reproduce. The permissions was a side effect, and I hoped that was the real issue, but I can see it isn't for sure because the original error is not what I was seeing. So it seems this may be related to the OS as I cannot replicate the issue with your broken DB.

can you send your original broken DB to github at rarforge.com? I will see if I can replicate the issue there. If not, I may need you to email a QNAP :)

[cookandy]

Hi again guys -

It's a shame you are unable to replicate the issue. I'm not sure that sending you the original broken DB will help now that we've established that even a fresh DB breaks it. I've done everything I can to compare the databases, and from a DB-perspective they are identical. However, if you start comparing the files with a hex editor or binary editor you'll notice there are some changes after plexWatch is run against the DB.

For what it's worth, I've created a video showing the problem. The permissions are the same throughout the demo (though I didn't show the permissions at the end). Maybe it will help troubleshoot the issue.

https://youtu.be/JXlJk7S2Z0I

I wonder if it could be something related to the way perl is configured on my system?

[jhdmpp]

Same issue on windows (8.1 & 10) since the end of june

[Zlke]

I was able to fix this issue and use PlexWatch 0.3.4 with PlexWatchWeb 1.7.0.

The comment about the journaling was right, in order to fix the issue the only solution is to reset the journaling to default (DELETE) in plexWatch.pl, simply replace this line:

$dbh->do("PRAGMA journal_mode = WAL");

by this line:

$dbh->do("PRAGMA journal_mode = DELETE");

Quote from sqlite documentation:

To prevent older versions of SQLite (prior to version 3.7.0, 2010-07-22) from trying to recover a WAL-mode database (and making matters worse) the database file format version numbers (bytes 18 and 19 in the database header) are increased from 1 to 2 in WAL mode. Thus, if an older version of SQLite attempts to connect to an SQLite database that is operating in WAL mode, it will report an error along the lines of "file is encrypted or is not a database".

One can explicitly change out of WAL mode using a pragma such as this: PRAGMA journal_mode=DELETE;

Deliberately changing out of WAL mode changes the database file format version numbers back to 1 so that older versions of SQLite can once again access the database file.

[ghost]

Is it possible instead that WAL mode needs to be enabled in PlexWatchWeb instead of disabling it in PlexWatch?

[Arcanemagus]

It looks like neither branch enabled it, so that might have been the issue. It's been several years so I'm no longer sure what debugging steps were taken exactly here. In any case, PlexWatch/Web is officially dead, anyone still using it is recommended to use PlexPy.