SOLVED Querying the Eximstats database - file is encrypted or is not a database

[MrJingles]

Hi,

I'm trying to run some queries on the eximstats database. I've tried a number of things as follows:

I have a development environment running on a Windows PC where I do my development work.

After reviewing a large number of posts and websites relating to my issue I have been very aware of the following posts and procedures:

github.com/AstralInternet/query_cpanel_eximstats_sqlite
Excellent script to query the database using perl.

stackoverflow.com/questions/44499844/cant-access-eximstats-sqlite3-db-after-whm64-upgrade
Stack overflow post regarding querying the eximstats database

API call to get a json equivalent of View Sent Summary

In my development environment I've taken a copy of the eximstats database from my live server (/var/cpanel/eximstats_db.sqlite3).

I've then created php scripts which have successfully queried this eximstats database. These php scripts used a variety of methods: calling a perl script, using PDO & using SQLite 3.

All of these scripts have been able to connect to the database I downloaded without any issue in my development environment.

All of these scripts have failed when I have ran them on my production server (they were ran as root).
They fail when querying the database directly (/var/cpanel/eximstats_db.sqlite3).
They fail when querying a copy of the database that has had its permissions changed to 0777.

The error message is always the same: 'file is encrypted or is not a database'.

I've tried numerous other things that may provide some insight.

If I open a copy of the database in DB Browser for SQLite and change the Journal Mode from 'WAL' to 'Off' and then upload this database to my production server, all of the scripts work.

I've read numerous posts about version differences but am not sure if this is the issue. Below are the sqlite version issue information I obtained from my production server:

cpanel-sqlite-3.22.0-1.cp1162.x86_64
ea-php56-php-pdo-5.6.40-2.2.4.cpanel.x86_64
sqlite-3.6.20-1.el6_7.2.x86_64
ea-php55-php-pdo-5.5.38-40.40.4.cpanel.x86_64
ea-php70-php-pdo-7.0.33-2.2.4.cpanel.x86_64

After numerous days trying numerous things I am now at a complete loss!!

Is anyone able to provide any further guidance that may assist me?

Thanks in advance,

Paddy

 

[cPanelMichael]

Hello Paddy,

Can you try using the direct path to the sqlite3 binary that's provided as part of cPanel & WHM to see if that makes a difference? For example, instead of entering "sqlite3", you'd enter:

/usr/local/cpanel/3rdparty/bin/sqlite3

Let me know if this makes a difference.

Thank you.

 

[MrJingles]

Hi Michael, thanks for the follow-up and my apologies for the length of this post - I want to provide as much information as possible...

I'm not entirely sure how I can do what you have suggested in my php scripts. Are you able to assist?

As mentioned I've tried it in numerous ways and I'll try to give you the appropriate code snippets I'm using so maybe you can point me in the right direction:

My php code (without using perl or PDO) is basically as follows:

$sqldb = "/location/of/copied/database/eximstats_db.sqlite3";
$sql3db = new Sqlite3($sqldb);
$sql3results = $sql3db->query('SELECT * FROM failures');

This basically shows an empty results set on my production server and $sql3db->lastErrorMsg() shows 'file is encrypted or is not a database '. I cannot see how I can call the sqlite3 binary in this code!

My php code using PERL is as follows (copied / modified from the github example provided above):

$perlscript = "/location/of/perscript/perl_eximstats.pl";
$query ="SELECT * FROM failures";
$command = "perl " . $perlscript . ' "' . $query . '"';
$json_records = json_encode(system($command));
echo $json_records . " \n";

The PERL script it calls is also taken / modified from the github example (I've include it up until it fails)

use strict;
use JSON;
use CGI;
use DBD::SQLite();

my $driver   = "SQLite";
my $database = "/location/of/copied/database/eximstats_db.sqlite3";
my $dsn = "DBI:$driver:dbname=$database";

my $dbh = DBI->connect($dsn,, undef, undef,
        {
            sqlite_open_flags                => "DBD::SQLite::OPEN_READONLY",
            sqlite_use_immediate_transaction => 0,
            RaiseError                       => 1,
            PrintWarn                        => 0,
        }
    )or die $DBI::errstr;

if ( not $dbh or $DBI::errstr ) {
    my $err = $DBI::errstr // q{something went wrong};
    print $err."\n";
    die qq{$err\n};
}

# Grab the query from the function argument.
my ($query) = @ARGV;

my $sth = $dbh->prepare( $query );

This basically errors stating 'DBD::SQLite::db prepare failed: file is encrypted or is not a database'.

I have tried changing the line in the above code as follows:

my $driver   = "/usr/local/cpanel/3rdparty/bin/SQLite";

This however returned the error: "Can't connect to data source 'DBI:/usr/local/cpanel/3rdparty/bin/SQLite:dbname=/location/of/copied/database/eximstats_db.sqlite3' because I can't work out what driver to use (it doesn't seem to contain a 'dbi:driver:' prefix and the DBI_DRIVER env var is not set)"

I also tried changing the 'use DBD::SQLite();' line in the perl script to:

use DBI::SQLite();

This resulted in the following error: "Can't locate DBI/SQLite.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .)".

Again, I cannot see how I can call the sqlite3 binary in this code!

My php code snippet using PDO is as follows:

$sql3db = new PDO('sqlite:/location/of/copied/database/eximstats_db.sqlite3');
$sql3results = $sql3db->query('SELECT * FROM failures');

This also errored but I didn't catch the error, it just didn't return any results.

I tried changing the code to:

$sql3db = new PDO('/usr/local/cpanel/3rdparty/bin/sqlite:/location/of/copied/database/eximstats_db.sqlite3');

but this errored stating 'Exception : could not find driver'.

Again, I cannot see how I can call the sqlite3 binary in this code!

So, I am still at a loss and after altering my script to try the three different methods of querying the database, it still doesn't work, mainly because I don't know how to implement the direct path method you recommend.

Any thoughts?

Thanks, Paddy

 

[cPanelMichael]

Hello @MrJingles,

The /usr/local/cpanel/3rdparty/bin/sqlite3 binary is something you'd use directly from the command like or as part of a shell script. For instance, here's an example of a command you would use:

/usr/local/cpanel/3rdparty/bin/sqlite3 /var/cpanel/eximstats_db.sqlite3 "SELECT * FROM failures"

The binary is not applicable to your PHP or Perl scripts.

Thank you.

 

[MrJingles]

Hi Michael,

Thanks for the clarity.

I can confirm that when I ran that line of code from the command it worked perfectly on the production server!

Does this help in any way?

Thanks for the support! Paddy

 

[cPanelMichael]

Hello @MrJingles,

The command line method is an example of an alternative to developing a custom PHP or Perl application. You could simply develop a bash script that makes use of the /usr/local/cpanel/3rdparty/bin/sqlite3 binary.

Thank you.

 

[MrJingles]

Hi Michael,

It's been a while since you kindly got back to me, but I just wanted to provide an update in case anyone else wants to implement the solution I've used.

My knowledge with bash scripts is limited but I decided that I would write one that basically created a csv file from the table and then called a php script to work with this csv file.

The bash script basically stated two lines to run:

#!/bin/bash
/usr/local/cpanel/3rdparty/bin/sqlite3 -header -csv /var/cpanel/eximstats_db.sqlite3 "SELECT * FROM failures;" > /home/someone/temp/location/tostorecsvfiles/eximfailures.csv
/usr/local/bin/php -f /location/to/php/script/script_to_process_failures.php

I was then able to easily process the csv file and delete it from the server once completed in php which is my preferred programming language.

Thanks again for such great help! Really superb and very much appreciated.

Paddy

 

[cPanelMichael]

Hello Paddy,

Thanks for sharing the solution!

 

[Sarangi Tech Solution]

hi MrJingles ,

thank you for your solution , as like you we also have limited knowledge of bash script , we would like to run the above code
as a cronjob tab in our server. We use the first line and added one cronjob but it didnot provide a data from the database . Could you please help us to improve our cronjob and get success.

thank you