May 2, 2017
9
0
1
india
cPanel Access Level
Reseller Owner
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 .

Code:
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
 
Last edited by a moderator:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,218
463
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.
 
  • Like
Reactions: eva2000
May 2, 2017
9
0
1
india
cPanel Access Level
Reseller Owner
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
 
Last edited by a moderator:
May 2, 2017
9
0
1
india
cPanel Access Level
Reseller Owner
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

Administrator
Staff member
Apr 11, 2011
47,908
2,218
463
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.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,218
463
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.
 
May 2, 2017
9
0
1
india
cPanel Access Level
Reseller Owner
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
 

Konservin

Registered
Jun 14, 2017
3
0
1
Tallinn, Estonia
cPanel Access Level
Website Owner
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

Registered
Jun 14, 2017
3
0
1
Tallinn, Estonia
cPanel Access Level
Website Owner
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.