Fetch data from sqlite database

[Sarangi Tech Solution]

hi,
Our eximstats_db has been converted to sqlite database after our WHM upgrade, and new version is 64.0.19. We are using eximstas_db for cron job purpose in our server and we are new with SQLite . we have tried many more times to access this database using php codes . But no result is found . While run the code a new db creating in the same folder it self . How can i solve this problem ? . And attaching the code that used .

class MyDB extends SQLite3
{
function __construct()
{
$this->open('/var/cpanel/eximstats_db.sqlite3');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";




date_default_timezone_set('Asia/Kuwait');
 $time= date("Y-m-d H:i:s");
       $date = new DateTime(date("Y-m-d H:i:s"));
        $date->modify('-600 seconds');
      $intervel=$date->format('Y-m-d H:i:s');
      $query1 = '(SELECT  mailtime FROM  failures WHERE ip!="127.0.0.1" AND mailtime BETWEEN "$intervel" AND "$time" ) UNION ALL (SELECT  mailtime FROM  defers WHERE ip!="127.0.0.1" AND mailtime BETWEEN "$intervel" AND "$time")';
  
    
      $result = $db->query($query1);
    
       $count=count($result);

 echo $count;

     if($count>20){
      
         $qry="(SELECT email, COUNT( * ) c FROM failures WHERE ip!='127.0.0.1' AND mailtime BETWEEN  '$intervel' AND '$time' GROUP BY email ORDER BY c DESC LIMIT 1)";
         $qry2="(SELECT email, COUNT( * ) c FROM defers WHERE ip!='127.0.0.1' AND mailtime BETWEEN  '$intervel' AND '$time' GROUP BY email ORDER BY c DESC LIMIT 1)";
  
    $result21 = $db->query($qry);
  $result45 = $db->query($qry2);
      

echo $intervel." to" .$time." count is= ".$count.'   ';


while($row = $result21->fetchArray(SQLITE3_ASSOC) ){
    echo "MOST COUNT failures :" . $row . "  and count =" . $row[c] . "  ";
$failemail=$row[email];
$failcount=$row[c];
   }

while($row = $result45->fetchArray(SQLITE3_ASSOC)  ){
    echo "MOST COUNT failures :" . $row[email] . "  and count =" . $row[c] . "  ";
$failemail=$row[email];
$failcount=$row[c];
   }
       }
      
  
  }
  $db->close();

is there any change in this code ? how can i remove the errors?


Best Regards,


Sarangi Tech Solutions

 

[cPanelMichael]

Hello,

Here's a quote from the 64 Release Notes regarding eximstats using SQLite:

eximstats now uses SQLite
In cPanel & WHM version 64, we now use SQLite instead of MySQL® for cPanel's eximstats database. The new SQLite database will reside in the /var/cpanel directory to keep the database on the same partition as the /var/lib/mysql directory. When you install or upgrade cPanel & WHM, the /scripts/slurp_exim_mainlog script will re-import your /var/log/exim_mainlog data. Your eximstats data and the Mail Delivery Report data will be unavailable while this import occurs.

If the SQLite database becomes corrupt or unreadable, the system will automatically remove the old database and create a new one. However, the exim_mainlog data will only be re-imported a single time every seven days to avoid an infinite re-import loop. The system moves corrupted databases to /var/cpanel/eximstats_db.sqlite3.broken.<timestamp> where <timestamp> represents the time in a Unix timestamp. The system retains a maximum of three broken databases so that server owners can attempt to fix the corrupted databases.

You can find documentation on SQLite syntax for your custom application at:

Query Language Understood by SQLite

Note that if you need assistance with custom SQLite coding, you may find more user-feedback on a website such as StackOverflow.

Thank you.

 

[Sarangi Tech Solution]

how can i get the path of SQlite3 database after WHM Upgrade for using with pdos

may i know where is the location of this specific DB with cPanel & WHM 64.0

 

[cPanelMichael]

may i know where is the location of this specific DB with cPanel & WHM 64.0

Hello,

Yes, it's located at:

/var/cpanel/eximstats_db.sqlite3

Thank you.

 

[Sarangi Tech Solution]

function __construct()
{
$this->open('/var/cpanel/eximstats_db.sqlite3');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";

do this code need any modification ?

 

[cPanelMichael]

do this code need any modification ?

That looks to be correct, however you will find more feedback on custom coding examples on websites such as StackOverflow, or through the assistance of a qualified system administrator. Assistance with custom development isn't something we can provide on the cPanel Forums.

Thank you.

 

[Sarangi Tech Solution]

thanks Mr.cPanelMichael i have followed on stack overflow for a solution. they suggested to use PDOs .... but While using PDO Still it shows error even if PDO Driver is installed

 

[cPanelMichael]

Hello,

Feel free to open a support ticket using the link in my signature if you'd like us to take a closer look at the affected system. You can post the ticket number here so we can update this thread with the outcome.

Thank you.

 

[Konservin]

Try this solution, worked for me:
stackoverflow.com/questions/44499844/cant-access-eximstats-sqlite3-db-after-whm64-upgrade/44517849#44517849

 

[Sarangi Tech Solution]

Try this solution, worked for me:
stackoverflow.com/questions/44499844/cant-access-eximstats-sqlite3-db-after-whm64-upgrade/44517849#44517849

thanks Konservin i have seen thet post and tried with the content . but still also raising the exception '
Unable to open database: unable to open database file
' even if the root user have read write permission

 

[Sarangi Tech Solution]

Hello,

Feel free to open a support ticket using the link in my signature if you'd like us to take a closer look at the affected system. You can post the ticket number here so we can update this thread with the outcome.

Thank you.

we appreciate your assistance . and also we would like to notice you with our reply with Konservin......

 

[Konservin]

thanks Konservin i have seen thet post and tried with the content . but still also raising the exception '
Unable to open database: unable to open database file
' even if the root user have read write permission

I think that's exactly the problem: root permissions are irrelevant because you're connecting not as root. Try granting rw to everybody and see if that works.

 

[Konservin]

I think that's exactly the problem: root permissions are irrelevant because you're connecting not as root. Try granting rw to everybody and see if that works.

Answer at stackoverflow.com/questions/44499844/cant-access-eximstats-sqlite3-db-after-whm64-upgrade/44517849#44517849 is now updated to offer another approach in case you cannot modify privileges for these files.

Briefly: copy the files (with a cron job) to somewhere you can modify the privileges, and use these files instead.