skip to Main Content

I am trying to create a demo website for users to test. I want to restore the databse every x hours. When I run the php script in browser it works. It drops the tables and restores the databse without any issues. When I add the same file in cron, the tables are dropped but it does not restore the databse. I am not sure why it’s not restoring the database.



<?php 

$mysqli = new mysqli("localhost", "", "", "");
$mysqli->query('SET foreign_key_checks = 0');
if ($result = $mysqli->query("SHOW TABLES"))
{
    while($row = $result->fetch_array(MYSQLI_NUM))
    {
        $mysqli->query('DROP TABLE IF EXISTS '.$row[0]);
    }
}

$mysqli->query('SET foreign_key_checks = 1');

echo "Deleted databse";

sleep(5);


$sqlScript = file('demo.sql');
foreach ($sqlScript as $line)   {
    
    $startWith = substr(trim($line), 0 ,2);
    $endWith = substr(trim($line), -1 ,1);
    
    if (empty($line) || $startWith == '--' || $startWith == '/*' || $startWith == '//') {
        continue;
    }
        
    $query = $query . $line;
    if ($endWith == ';') {
        mysqli_query($mysqli,$query) or die('<div class="error-response sql-import-response">Problem in executing the SQL query <b>' . $query. '</b></div>');
        $query= '';     
    }
}
$mysqli->close();

echo '<div class="success-response sql-import-response">SQL file imported successfully</div>';
?>


Cron job

*/10    *   *   *   *   /usr/local/bin/php /home/demo/public_html/db.php

2

Answers


  1. Chosen as BEST ANSWER

    aynber provided the answer in a comment

    Cron jobs either run relative to the home of the user running it, or the server root if it's using /etc/cron. file('demo.sql'); is looking for a relative file path to where the script is being run from, so it will fail when being run by cron

    I updated the sql file path and it worked.


  2. As mentioned above, in your script there is a relative path. Therefore you need to run it from the directory where script is. You can simply replace the cron command to this:

    */10 * * * * cd /home/demo/public_html/ && /usr/local/bin/php /home/demo/public_html/db.php
    

    && means after applying the first command, apply the following command.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search