| Subject: | Bug in DBD::SQLite-1.43_08 - Legacy DOS 8.3 filename support incompatible with SQLITE WAL journal mode |
| Date: | Sun, 19 Oct 2014 09:38:37 -0500 |
| To: | bug-DBD-SQLite [...] rt.cpan.org |
| From: | Pat Horton <phorton2 [...] gmail.com> |
Hey there,
Pardon me in advance if the format of this bug report is non-compliant. This is the first time I've reported a bug in a CPAN Perl Module. I hope I get it right, because it's a fairly simple bug with a definite negative impact for clients of DBD::SQLite going forward. The fix is probably a spot modification to a single line of Perl code, or a few lines, depending on the approach.
DESCRIPTION:
On current Windows platforms, using DBD::SQLite 1.43_08 with databases that specify WAL (Write Ahead Logging) journaling does not work correctly.
Changes made by a client that is using WAL journaling are not visible to clients of DBD::SQLite until some other client checkpoints the database. Checkpointing via DBD::SQLite does not work correctly. Changes to databases using WAL journaling made by clients using windows DBD::SQLite are not visible to other clients on the same machine, and the other clients cannot 'checkpoint' the changes made by clients usind DBD::SQLite.
SEVERITY:
Moderate to severe. At a minimum bug results in incorrect SQL results being returned. I suspect that there are orders of operations that, as a result of this bug, could lead to incorrect or inconsistent data in the database itself, possibly even corruption of databases.
SYMPTOMS:
On Windows platforms, with databases using WAL journaling, queries made to a database that is being modified by another, possibly non-perl, process return inconsistent results.
I noticed the bug when writing an application to interface to the SQLite database provided by a program called 'Plex'. Plex is windows specific media server that provides a web client to allow users to make changes to the database. Specifically, there is a field in a record in a table in an sqlite3 database that captures the 'watched' status of a movie or video. Plex marks an item as 'watched' when it serves it to a client, and the user can explicitly modify the 'watched' status of a particular movie or tv show via an explicit Web UI and/or API.
My application is a backup program that copies files from an internal system disk to external hard disk, and specifically attempts to detect if a video file has been 'watched' or not. If a movie has been watched, after it is backed up to the external hare disk, it can be removed from the system disk.
It was moderately difficult, for me, to track this problem down. Most of the time, my DBD::SQLite queries were returning the correct watched status, but sometimes the Plex Web UI would tell me that a movie was 'watched' but the DBD::SQLite query would return an 'unwatched' status, or vice-versa. Other programs, notably an SQLite browser, were showing the correct results, but DBD::SQLite kept giving me 'stale' information, which would occasionally, and somewhat mysteriously, correct itself.
ANALYSIS
What I learned was that Plex is using WAL journaling. As I would make changes in the Plex Web UI, they would be written to the journal database files. The journal is only checkpointed occasionally under conditions determined by the Plex server. DBD::SQLite was only returning the results from the static database files, and would not 'checkpoint' the database correctly to combine the journaled data in to the static database, and so DBD::SQLite could not 'see' the changes made by Plex until Plex decided to checkpoint the database.
Attempts to checkpoint the database using DBD::SQLite failed. Note that DBD::SQLite does not support the sqlite3_checkpoint_wal or sqlite3_checkpoint_wal_v2 methods, so I was forced to use a SQL PRAGMA to communicate between my Perl application and the sqlite binaries in order to (attempt to) effect a WAL checkpoint.
In tracking down the bug I inserted printf's into the sqlite3.c file to first ensure that the pragma was correctly calling the appropriate C routines to do the wal checkpoints, which it was. So it was not the version of DBD::SQLite that was the problem, and sqlite3.c appears to have sufficient code to correctly handle the wal checkpointing.
I had perused the perl code in /Perl/site/lib/DBD/SQLite.pm and had seen a snippet relating to DOS 8.3 filenames.
I was looking at the database directory during modifications by Plex and saw the WAL journals being written to .wal and .shm files.
I noticed that when I booted my perl program, a SEPARATE SET of .wal and .shm files were created in DOS 8.3 filename format.
A lightbulb went off in my head, I added a "0 &&" to disable the DOS 8.3 filename snippet in SQLite.pm, and my application started working.
DETAILS
My name is 'Pat'. Plex stores it's database on my machine in the following directory:
/Users/Pat/AppData/Local/Plex Media Server/Plug-in Support/Databases
The database name, and the corresponding WAL journal files, as written by Plex are:
com.plexapp.plugins.library.db
com.plexapp.plugins.library.shm
com.plexapp.plugins.library.wal
Booting a program that uses DBD::SQLite against said database results in the creation of the following, incorrect, WAL journal files.
COMPLE~1.shm
COMPLE~1.wal
Adding the "0 &&" to line 116 in SQLite.pm caused DBD::SQLite to use the correct long filenames:
# PRH 2014-10-19 - Force Save to comment out this code (0 &&)
# which was passing old 8.3 DOS filenames to sqlite3 and screwing
# up the wal_checkpointing
116: if ( 0 && $^O =~ /MSWin32/ and $real ne ':memory:' and $real ne '' and $real !~ /^file:/) {
117: require Win32;
118: require File::Basename;
119: my ($file, $dir, $suffix) = File::Basename::fileparse($real);
120: my $short = Win32::GetShortPathName($real);
121: if ( $short && -f $short ) {
122: # Existing files will work directly.
123: $real = $short;
124: } elsif ( -d $dir ) {
125 ....
COMMENTARY:
I am reporting this bug because it stymied me for a full day and perhaps other users will benefit from attention to it.
You may wish to consider a "legacy filename support' flag that can be passed into the connect() method to allow clients to use the legacy code.
This issue could be thorny, as the validity of the 8.3 filename depends on who created the database. If created by DBD::SQLite, then the database itself, as well as the journals, will have 8.3 filenames, and so the system *should* work ok. But when interfacing to other programs via sqlite databases, it is necessary to use the correct long filenames.
IMHO, providing transparent correct behavior to new clients is important, as 'newbies' will often make design decisions based on early results, and if they can't get it to work the first time, if they can't understand what is wrong, then they will likely not use DBD::SQLite and/or Perl for their application, which would be a shame, since it is a wonderful, really, cool, combination. Existing clients that update to the newest version of DBD::SQLite could be made aware that they need to include the legacy filename support flag, going forward, if they so desire.
Alternatively, the fix could be couched in terms of 'enable long filenames', and you could allow (knowledgable) users to provide the connect flag.
Either way is ok with me. I just don't like having local Perl mods, as they can easily get lost and are difficult to manage. I have had to poke quite a few /site/lib modules in my day, and today I decided that the better approach would be to try to get the change into the trunk so I'm not holding the bag :-)
CLOSING
I just want to say thank you to ADAMK, DUNCAND, ISHIGAKI, MSERGEANT et al for providing DBD::SQLIte. I not only use it in this application, but in a number of other applications, and it's a really great thing you guys have done and are doing, bug notwithstanding. Kudos!
- Patrick
The bug results in clients being unable to do WAL checkpoints to synchronize database changes, getting stale information and possibly committing inconsistent or incorrect data to the database.
Pardon me in advance if the format of this bug report is non-compliant. This is the first time I've reported a bug in a CPAN Perl Module. I hope I get it right, because it's a fairly simple bug with a definite negative impact for clients of DBD::SQLite going forward. The fix is probably a spot modification to a single line of Perl code, or a few lines, depending on the approach.
DESCRIPTION:
On current Windows platforms, using DBD::SQLite 1.43_08 with databases that specify WAL (Write Ahead Logging) journaling does not work correctly.
Changes made by a client that is using WAL journaling are not visible to clients of DBD::SQLite until some other client checkpoints the database. Checkpointing via DBD::SQLite does not work correctly. Changes to databases using WAL journaling made by clients using windows DBD::SQLite are not visible to other clients on the same machine, and the other clients cannot 'checkpoint' the changes made by clients usind DBD::SQLite.
SEVERITY:
Moderate to severe. At a minimum bug results in incorrect SQL results being returned. I suspect that there are orders of operations that, as a result of this bug, could lead to incorrect or inconsistent data in the database itself, possibly even corruption of databases.
SYMPTOMS:
On Windows platforms, with databases using WAL journaling, queries made to a database that is being modified by another, possibly non-perl, process return inconsistent results.
I noticed the bug when writing an application to interface to the SQLite database provided by a program called 'Plex'. Plex is windows specific media server that provides a web client to allow users to make changes to the database. Specifically, there is a field in a record in a table in an sqlite3 database that captures the 'watched' status of a movie or video. Plex marks an item as 'watched' when it serves it to a client, and the user can explicitly modify the 'watched' status of a particular movie or tv show via an explicit Web UI and/or API.
My application is a backup program that copies files from an internal system disk to external hard disk, and specifically attempts to detect if a video file has been 'watched' or not. If a movie has been watched, after it is backed up to the external hare disk, it can be removed from the system disk.
It was moderately difficult, for me, to track this problem down. Most of the time, my DBD::SQLite queries were returning the correct watched status, but sometimes the Plex Web UI would tell me that a movie was 'watched' but the DBD::SQLite query would return an 'unwatched' status, or vice-versa. Other programs, notably an SQLite browser, were showing the correct results, but DBD::SQLite kept giving me 'stale' information, which would occasionally, and somewhat mysteriously, correct itself.
ANALYSIS
What I learned was that Plex is using WAL journaling. As I would make changes in the Plex Web UI, they would be written to the journal database files. The journal is only checkpointed occasionally under conditions determined by the Plex server. DBD::SQLite was only returning the results from the static database files, and would not 'checkpoint' the database correctly to combine the journaled data in to the static database, and so DBD::SQLite could not 'see' the changes made by Plex until Plex decided to checkpoint the database.
Attempts to checkpoint the database using DBD::SQLite failed. Note that DBD::SQLite does not support the sqlite3_checkpoint_wal or sqlite3_checkpoint_wal_v2 methods, so I was forced to use a SQL PRAGMA to communicate between my Perl application and the sqlite binaries in order to (attempt to) effect a WAL checkpoint.
In tracking down the bug I inserted printf's into the sqlite3.c file to first ensure that the pragma was correctly calling the appropriate C routines to do the wal checkpoints, which it was. So it was not the version of DBD::SQLite that was the problem, and sqlite3.c appears to have sufficient code to correctly handle the wal checkpointing.
I had perused the perl code in /Perl/site/lib/DBD/SQLite.pm and had seen a snippet relating to DOS 8.3 filenames.
I was looking at the database directory during modifications by Plex and saw the WAL journals being written to .wal and .shm files.
I noticed that when I booted my perl program, a SEPARATE SET of .wal and .shm files were created in DOS 8.3 filename format.
A lightbulb went off in my head, I added a "0 &&" to disable the DOS 8.3 filename snippet in SQLite.pm, and my application started working.
DETAILS
My name is 'Pat'. Plex stores it's database on my machine in the following directory:
/Users/Pat/AppData/Local/Plex Media Server/Plug-in Support/Databases
The database name, and the corresponding WAL journal files, as written by Plex are:
com.plexapp.plugins.library.db
com.plexapp.plugins.library.shm
com.plexapp.plugins.library.wal
Booting a program that uses DBD::SQLite against said database results in the creation of the following, incorrect, WAL journal files.
COMPLE~1.shm
COMPLE~1.wal
Adding the "0 &&" to line 116 in SQLite.pm caused DBD::SQLite to use the correct long filenames:
# PRH 2014-10-19 - Force Save to comment out this code (0 &&)
# which was passing old 8.3 DOS filenames to sqlite3 and screwing
# up the wal_checkpointing
116: if ( 0 && $^O =~ /MSWin32/ and $real ne ':memory:' and $real ne '' and $real !~ /^file:/) {
117: require Win32;
118: require File::Basename;
119: my ($file, $dir, $suffix) = File::Basename::fileparse($real);
120: my $short = Win32::GetShortPathName($real);
121: if ( $short && -f $short ) {
122: # Existing files will work directly.
123: $real = $short;
124: } elsif ( -d $dir ) {
125 ....
COMMENTARY:
I am reporting this bug because it stymied me for a full day and perhaps other users will benefit from attention to it.
You may wish to consider a "legacy filename support' flag that can be passed into the connect() method to allow clients to use the legacy code.
This issue could be thorny, as the validity of the 8.3 filename depends on who created the database. If created by DBD::SQLite, then the database itself, as well as the journals, will have 8.3 filenames, and so the system *should* work ok. But when interfacing to other programs via sqlite databases, it is necessary to use the correct long filenames.
IMHO, providing transparent correct behavior to new clients is important, as 'newbies' will often make design decisions based on early results, and if they can't get it to work the first time, if they can't understand what is wrong, then they will likely not use DBD::SQLite and/or Perl for their application, which would be a shame, since it is a wonderful, really, cool, combination. Existing clients that update to the newest version of DBD::SQLite could be made aware that they need to include the legacy filename support flag, going forward, if they so desire.
Alternatively, the fix could be couched in terms of 'enable long filenames', and you could allow (knowledgable) users to provide the connect flag.
Either way is ok with me. I just don't like having local Perl mods, as they can easily get lost and are difficult to manage. I have had to poke quite a few /site/lib modules in my day, and today I decided that the better approach would be to try to get the change into the trunk so I'm not holding the bag :-)
CLOSING
I just want to say thank you to ADAMK, DUNCAND, ISHIGAKI, MSERGEANT et al for providing DBD::SQLIte. I not only use it in this application, but in a number of other applications, and it's a really great thing you guys have done and are doing, bug notwithstanding. Kudos!
- Patrick
The bug results in clients being unable to do WAL checkpoints to synchronize database changes, getting stale information and possibly committing inconsistent or incorrect data to the database.