Is mysqli_multi_query asynchronous?

Question

$databases = array();
$path = '/Path/To/Directory';
$main_link = mysqli_connect('localhost', 'USERNAME', 'PASSWORD');
$files = scandir($path);
$ignore_files = array();

foreach($files as $file)
{
    if (!in_array($file, $ignore_files))
    {
        $database = substr($file, 0, strpos($file,'.'));
        $databases[] = $database;
        mysqli_query($main_link, "DROP DATABASE IF EXISTS $database") or die ("$database 1" . mysqli_error($main_link));
        mysqli_query($main_link, "CREATE DATABASE $database") or die ("$database 2" .mysqli_error($main_link));
        $db_link = mysqli_connect('localhost', 'USERNAME', 'PASSWORD', $database);
        //In here a whole database dump with scheam + data is executed. 
        mysqli_multi_query($db_link, file_get_contents($path.'/'.$file)) or die ("$database 4" .mysqli_error($db_link));        
    }   
}

When running this script it was done very quickly (returned to browser), but it was still running queries after the browser said it was done. Why is this?

Kenay

what it was still running what, and how do you know? – Carlsbad

Yeah I'd like to know that too... Queries shouldn't execute async. Even multi queries (?) because they too can have results. – Bluejacket

@Dagon Maybe he did a SHOW PROCESSLIST... @Chris top | grep mysql wouldn't be acurate, because mysql daemons and processes are always running. – Bluejacket

maybe, or maybe not, that's why i asked :-) – Carlsbad

I was just executing show tables on various mysql databases and they kept growing (as my script creates tables and populates with data). It took a good 2 minutes after the script was done executing for the data to be populated into the databases. – Kenay

Answer 1

mysqli_query supports async queries. See changelog on mysqli_query. mysqli_multi_query does not mention async on the manual page specifically. Only thing mysqli_multi_query does is tell MySQL to execute a bulk set of queries. It's up to PHP to wait for the results.

As your code stands, your sending a bulk set of SQL statements to MySQL and not waiting for any results. Only time your mysqli_multi_query will ever die is when the first statement fails. So, that function returns true immediately after the first statement and moves on to the next line. That's why the queries are executing after the PHP is finished. MySQL is still working. PHP has moved on.

It's best that you loop through the results of each statement before moving on with your code. The following will die if a query fails anywhere in your batch.

mysqli_multi_query($db_link, file_get_contents($path.'/'.$file)) or die ("$database 4" .mysqli_error($db_link)); 

do {
    if($result = mysqli_store_result($db_link)){
        mysqli_free_result($result);
    }
} while(mysqli_next_result($db_link));

if(mysqli_error($db_link)) {
    die(mysqli_error($db_link));
}

Introvert

You don't actually need to store / free the result. An empty while loop is sufficient while(mysqli_next_result($db_link)) {}. See the documentation: next_result, store_result – Miner