Importing large dump with php "mysqli"

Question

I try this solution to import large SQL file in mysql database using PHP script. I use mysqli instead of mysql. But this error appears:

previous error: Error performing query '/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

I know for sure that SQL file is fine and the problem is in my script:

// SQL import
// your config
$filename = 'auct_lots_full.sql';
$maxRuntime = 8; // less then your max script execution limit


$deadline = time()+$maxRuntime;
$progressFilename = $filename.'_filepointer'; // tmp file for progress
$errorFilename = $filename.'_error'; // tmp file for erro

$con = mysqli_connect("localhost", "xxxx", "xxxxxxxx", "asystem_db") OR die("Database selection failed: " . mysqli_error($con));
mysqli_select_db($con, "asystem_db") OR die("Database selection failed: " . mysqli_error($con));


($fp = fopen($filename, 'r')) OR die('failed to open file:'.$filename);

// check for previous error
if( file_exists($errorFilename) ){
    die('<pre> previous error: '.file_get_contents($errorFilename));
}

// activate automatic reload in browser
echo '<html><head> <meta http-equiv="refresh" content="'.($maxRuntime+2).'"><pre>';

// go to previous file position
$filePosition = 0;
if( file_exists($progressFilename) ){
    $filePosition = file_get_contents($progressFilename);
    fseek($fp, $filePosition);
}

$queryCount = 0;
$query = '';
while( $deadline>time() AND ($line=fgets($fp, 1024000)) ){
    if(substr($line,0,2)=='--' OR trim($line)=='' ){
        continue;
    }

    $query .= $line;
    if( substr(trim($query),-1)==';' ){
        if( !mysqli_query($con, $query) ){
            $error = 'Error performing query \'<strong>' . $query . '\': ' . mysqli_error($con);
            file_put_contents($errorFilename, $error."\n");
            exit;
        }
        $query = '';
        file_put_contents($progressFilename, ftell($fp)); // save the current file position for
        $queryCount++;
    }
}

if( feof($fp) ){
    echo 'dump successfully restored!';
}else{
    echo ftell($fp).'/'.filesize($filename).' '.(round(ftell($fp)/filesize($filename), 2)*100).'%'."\n";
    echo $queryCount.' queries processed! please reload or wait for automatic browser refresh!';
}

You couldn't just use this in command line: mysql -u userName -p databasename < theSqlFile.sql — salah-1, May 24 '17 at 07:18
Your issue is that you read the file line by line, which will end in corrupt queries and incomplete comments. Why dont you just use phpmyadmin? The same restrictions for phpma apply for plain PHP as well. So if it doesn't work with phpma, then use mysql CLI. — Charlotte Dunois, May 24 '17 at 07:20
@CharlotteDunois I did so, and it worked fine. But the thing is that i need to schedule this update every hour by CRON TAB... so i need a working script : ( — Dmitry Malys, May 24 '17 at 07:24
@CharlotteDunois Can you recommend a solution to schedule SQL file import using CRONTAB? — Dmitry Malys, May 24 '17 at 07:39

Importing large dump with php "mysqli"

0 Answers0