SQL Errors (latest dev)

[cookandy]

Hi,

I just updated to the latest dev and I am getting errors when trying to go to any page:

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

I tried deleting the config.php and running through the setup again, but same problem. After I save the config and go to the settings page I get this error:

There was a problem running "SELECT version FROM config". Error: SQLSTATE[HY000]: General error: 26 file is encrypted or is not a database Stack Trace: #0 plexWatch/includes/functions.php(106): PDO->query('SELECT version ...') #1 plexWatch/settings.php(65): getResults(Object(PDO), 'SELECT version ...') #2 plexWatch/settings.php(23): printVersions() #3 plexWatch/settings.php(650): printSettings() #4 {main}

Any ideas? I'm using plexWatch 0.3.3-dev.

[timrettop]

Are you pointing the config to the correct location for the plexWatch.db file?

Is plexWatch.pl working for you?

[cookandy]

Yes, and yes.

I reverted back to plexWatchWeb 1.5.4.2 and everything works fine. I even tried to copy my config.php from the older release to the new one and I get the same error.

[timrettop]

Whats the path to your plexWatch.db in the pWW config?

Whats the environment, distro, version?

[cookandy]

$plexWatch['plexWatchDb'] = '/share/data/plexWatch/plexWatch.db';

QNAP Linux 4.x, Python 2.7, PWW 1.7.0.2-dev

As I said, the older 1.5.x version of PWW works without any problems.

[timrettop]

Between the dev and the master release, PDO was added as a method to access the Database. I guess that your plexWatch.db is SQLite v2, not v3.

can you run: 'file /share/data/plexWatch/plexWatch.db'

[cookandy]

[~] # file /share/data/plexWatch/plexWatch.db /share/data/plexWatch/plexWatch.db: SQLite 3.x database Segmentation fault

[timrettop]

could you provide your phpinfo() ?

[Arcanemagus]

That is not encouraging that you get a segfault while running file. Are you sure your file system integrity is good?

[cookandy]

I get segmentation faults whenever I run 'file' against any file (ASCII, HTML, etc) for some reason. I have a RAID 5 and all disks are healthy - filesystem is good. Doesn't make sense why it would work for the older version. Here's my phpinfo

PHP Version 5.3.29 System Linux NAS 3.4.6 #1 SMP Wed Apr 8 04:30:01 CST 2015 x86_64 Build Date Apr 8 2015 04:04:44 Configure Command './configure' '--disable-debug' '--prefix=/root/daily_build/4.1.0/Model/TS-269/../../NasMgmt/HTTP/php5' '--with-config-file-path=/etc/config/php.ini' '--with-config-file-scan-dir=/etc/config/php.d' '--with-apxs2=/usr/local/apache/bin/apxs' '--enable-ftp=shared' '--enable-sockets=shared' '--with-gettext=shared' '--with-zlib=shared' '--with-jpeg-dir=' '--with-png-dir=/opt/cross-project/x86/sys-root/usr' '--with-gd=shared' '--with-freetype-dir=/opt/cross-project/x86/sys-root/usr' '--enable-gd-native-ttf=shared' '--enable-mbstring=shared' '--with-openssl=shared,/opt/cross-project/x86/sys-root/usr' '--enable-libxml=shared' '--enable-sqlite-utf8=shared' '--with-mysql=shared,/root/daily_build/4.1.0/Model/TS-269/../../DataService/DBMS/mysql' '--with-mcrypt=shared,/opt/cross-project/x86/sys-root/usr' '--enable-zend-multibyte=shared' '--with-iconv=shared,/opt/cross-project/x86/sys-root/usr' '--enable-exif=shared' '--with-pdo-mysql=shared,/root/daily_build/4.1.0/Model/TS-269/../../DataService/DBMS/mysql' '--enable-pdo=shared' '--with-pdo-sqlite=shared,/opt/cross-project/x86/sys-root/usr' '--with-sqlite=shared' '--with-curl=shared,/root/daily_build/4.1.0/Model/TS-269/../../SysUtil/curl-7.21.0' '--with-curlwrappers=shared,/root/daily_build/4.1.0/Model/TS-269/../../SysUtil/curl-7.21.0' '--with-xsl=shared,/opt/cross-project/x86/sys-root/usr' '--with-mysqli=shared,/root/daily_build/4.1.0/Model/TS-269/../../DataService/DBMS/mysql/bin/mysql_config' '--enable-calendar=shared' '--enable-bcmath=shared' '--with-ldap=shared,/opt/cross-project/x86/sys-root/usr' '--with-imap=shared,/opt/cross-project/x86/sys-root/usr' '--with-imap-ssl=/opt/cross-project/x86/sys-root/usr' '--enable-dba=shared' '--enable-dbase=shared' '--enable-shmop=shared' '--enable-wddx=shared' '--enable-zip=shared' '--with-xmlrpc=shared' '--enable-soap=shared' '--enable-pcntl=shared' '--enable-mysqlnd=shared' '--enable-session=shared' '--with-sqlite3=shared'

Edit: and here's the PDO section. I notice there's no sqllite3 driver listed. Could this be the problem?

PDO support enabled PDO drivers sqlite2, mysql, sqlite

Edit 2: Here is the PDO_SQLLITE section:

PDO Driver for SQLite 3.x enabled SQLite Library 3.4.1

[Arcanemagus]

Ah, plexWatch/Web requires PHP version 5.3.3 minimum. How QNAS is still stuck on there I'm not sure... 5.3 was EOL back on 2014-08-14.

The master version may be "working" since it does things rather simplistically, the current dev fixes a lot of problems with that (like not validating user input before directly using it in the database).

[timrettop]

@Arcanemagus is 5.3.29 older than 5.3.3?

@cookandy Does the following command work? sqlite3 /share/data/plexWatch/plexWatch.db "select version from config";

[timrettop]

also phpinfo() gives loaded modules, vs just compiled ones. Do you mind providing that?

[Arcanemagus]

@timrettop It's too early in the morning...

[cookandy]

@timrettop:

[~] # sqlite3 /share/data/plexWatch/plexWatch.db "select version from config"; 0.3.3-DEV

And here are the loaded modules:

core mod_authn_file mod_authn_default mod_authz_host mod_authz_groupfile mod_authz_user mod_authz_default mod_auth_basic mod_include mod_log_config mod_env mod_setenvif mod_version prefork http_core mod_mime mod_status mod_autoindex mod_asis mod_cgi mod_vhost_alias mod_negotiation mod_dir mod_actions mod_userdir mod_alias mod_rewrite mod_so mod_php5 mod_deflate mod_ssl mod_proxy mod_proxy_http

[timrettop]

Thats your apache modules, not your php modules.

http://php.net/manual/en/function.phpinfo.php

[cookandy]

Oops, yeah too early... Does this help?

[~] # php -m
[PHP Modules]
bcmath
bz2
calendar
ctype
date
dba
dom
exif
filter
ftp
hash
json
libxml
openssl
pcre
posix
Reflection
session
shmop
SimpleXML
sockets
SPL
standard
sysvmsg
sysvsem
sysvshm
tokenizer
xml
xmlwriter
xsl
zlib

[Zend Modules]

[Arcanemagus]

Your PHP isn't loading the necessary module (specifically pdo_sqlite). From a quick search it looks like it's possible to have that on your system, I just don't know the exact steps.

[timrettop]

hmm, your missing PDO, pdo_sqlite and sqlite3 modules.

Can you check your php.ini and add or uncomment them? And then if you have those 3 modules loaded, restart httpd / apache2

[timrettop]

if you have any trouble enabling those modules, see this post, specifically steps 2 and 3:

http://php.net/manual/en/ref.pdo-sqlite.php#68780

[cookandy]

I'm slightly confused by the php output in command line. When I look at the phpinfo(); I get v5.3.29. however, when I run it from command line I get a different version. I think the modules are missing from this CLI version of PHP. If the sqlite module was missing, how would the older version of PWW work?

PHP 5.2.17 (cli) (built: Feb 23 2012 15:05:00) 
Copyright (c) 1997-2010 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2010 Zend Technologies

I'll see if I can get PHO modules added in php.ini

[timrettop]

Fair point about sqlite. Maybe be careful that there isn't a separate php.ini for your webserver

[cookandy]

is there a way to view loaded modules on the phpinfo() page? i couldn't find them. it looks like my php.ini has already been configured:

extension_dir = /usr/local/apache/modules
extension = pdo.so
extension = pdo_sqlite.so
extension = sqlite.so

I verified that those modules do, in fact, live in the /usr/local/apache/modules directory.

I think the CLI version is a red herring as I might have two different version installed for whatever.

[cookandy]

I think on the phpinfo page each section is a loaded module. Therefore, it appears I have PDO and SQLlite loaded:

PDO

PDO support enabled
PDO drivers mysql, sqlite

pdo_mysql

PDO Driver for MySQL    enabled
Client API version  mysqlnd 5.0.11-dev - 20120503 - $Id: 15d5c781cfcad91193dceae1d2cdd127674ddb3e $

Directive   Local Value Master Value
pdo_mysql.default_socket    /tmp/mysql.sock /tmp/mysql.sock

pdo_sqlite

PDO Driver for SQLite 3.x   enabled
SQLite Library  3.4.1

sqlite3

SQLite3 support enabled
SQLite3 module version  0.7-dev
SQLite Library  3.8.7.2

Directive   Local Value Master Value
sqlite3.extension_dir   no value    no value

[cookandy]

Hello,

I created a simple php file and tried to open the plexwatch.db file.

<?php
  try
  {
    //open the database
    $db = new PDO('sqlite:/share/data/plexWatch/plexWatch.db');

    $result = $db->query('SELECT version FROM config');
    foreach($result as $row)
    {
      echo $row[0];
    }

    // close the database connection
    $db = NULL;
  }
  catch(PDOException $e)
  {
    print 'Exception : '.$e->getMessage();
  }
?>

However, I get the following error. I don't see anything wrong with my code. Can you?

Warning: Invalid argument supplied for foreach() in /share/Web/test.php on line 12

[timrettop]

you could have nothing in the array, include the error checks:

// Throw exceptions on errors $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

[cookandy]

Now I get the same error as with PWW:

Exception : SQLSTATE[HY000]: General error: 26 file is encrypted or is not a database

[cookandy]

According to the reply on this answer on stackoverflow, it sounds like this error can occur when the DB is actually sqlite2. Is this possible?

[timrettop]

so the DBO cannot talk to your DB, but why?

Everything points to a version mismatch, but that doesn't seem to be the case, your DB is version 3, and your module versions seem to indicate support for sqlite3...

[timrettop]

try making a new sqlite3 db?

$ sqlite3 /share/data/plexWatch/test.db SQLite version 3.8.5 2014-05-29 12:36:14 Enter ".help" for usage hints. sqlite> create table tbl1(one varchar(10), two smallint); sqlite> insert into tbl1 values('hello!',10); sqlite> insert into tbl1 values('goodbye', 20); sqlite> .quit

Then reference that DB, and run a 'SELECT * FROM tbl1' as your request. Make sure ownership is fine.

[cookandy]

that worked

array(4) { ["one"]=> string(6) "hello!" [0]=> string(6) "hello!" ["two"]=> string(2) "10" [1]=> string(2) "10" } array(4) { ["one"]=> string(7) "goodbye" [0]=> string(7) "goodbye" ["two"]=> string(2) "20" [1]=> string(2) "20" }

so it appears my plexwatch.db is corrupt somehow??

[timrettop]

I would backup your plexWatch.db, and then open it with sqlite3 and run: sqlite> pragma integrity_check; sqlite> .exit

If it comes back clean... then the integrity is ok.

Another test is to dump the entire database into a new file: echo '.dump' | sqlite3 /share/data/plexWatch/plexWatch.db | sqlite3 /share/data/plexWatch/plexWatch2.db

Then test your script (with original call to version from config) on that file. if it works, try pointing your pWW and pW configs at it, and see if they work.

[cookandy]

You read my mind :)

I dumped the DB to a new file and my script runs fine. However, as soon as I run ./plexWatch.pl to update the DB, it goes back to the same error. So I think maybe there's a problem with the PW version I'm using... Can I please ask what version of PW you are using?

# ../../apps/plexWatch/plexWatch.pl --version

Version: 0.3.3-DEV

##########################################
#   Author: Rob Reed
#  Created: 2013-06-26
# Modified: 2015-03-29 17:00 PST
#
#  Version: 0.3.3-DEV
# https://github.com/ljunkie/plexWatch
##########################################

[timrettop]

Thats the same that I'm using... and I don't have this issue.

I wonder if its in the db upgrade, that creates this issue, let me test.

[cookandy]

Very possible... I have been using plexWatch for almost 2 years... I know the DB has been upgraded in the past.

[timrettop]

I don't know perl very well to try to troubleshoot the perl sqlite workflows. Do you want to open a ticket on plexWatch and see if anyone can help dig out the issue?

My guess is that the perl modules for sqlite are manipulating the database to make it not compatible with the PDO driver. We know that your sqlite3 program creates db's that can be read, but the perl sqlite functions alter it in some way.

Stumped!

[cookandy]

@timrettop yeah, I'll do that. thanks for trying to troubleshoot. were you able to reproduce the issue (just so I know if it's isolated to my system in some way)?

[Arcanemagus]

If you file an issue on plexWatch can you link it here?

Also since it looks like this isn't an issue with plexWatch/Web I'm going to close this for now. Thanks for narrowing it down you two!

[cookandy]

Issue opened: https://github.com/ljunkie/plexWatch/issues/101 - feel free to add anything I may have missed.

[Zlke]

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

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 journalmode=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.