mysql multi_query intermittently fails

Question

function cpanel_populate_database($dbname) 
{ 
     // populate database
     $sql = file_get_contents(dirname(__FILE__) . '/PHP-Point-Of-Sale/database/database.sql');
     $mysqli->multi_query($sql);

     $mysqli->close();
 }

The sql file is a direct export from phpMyAdmin and about 95% of the time runs without issue and all the tables are created and data is inserted. (I am creating a database from scratch)

The other 5% only the first table or sometimes the first 4 tables are created, but none of the other tables are created (there are 30 tables).

I have decided to NOT use multi_query because it seems buggy and see if the the bug occurs by using just mysql_query on each line after semi-colon. Has anyone ran into issue's like this?

score 12 · Answer 1 · answered Sep 23 '11 at 06:35

12

Fast and effective

system('mysql -h #username# -u #username# -p #database# < #dump_file#');

answered Sep 23 '11 at 06:35

Nimit Dudani

4,840
3
31
46

score 2 · Accepted Answer · answered Sep 23 '11 at 18:34

I've seen similar issues when using multi_query with queries that can create or alter tables. In particular, I tend to get InnoDB 1005 errors that seem to be related to foreign keys; it's like MySQL doesn't completely finish one statement before moving on to the next, so the foreign keys lack a proper referent.

In one system, I split the problematic statements into their own files. In another, I have indeed run each command separately, splitting on semicolons:

function load_sql_file($basename, $db) {
    // Todo: Trim comments from the end of a line
    log_upgrade("Attempting to run the `$basename` upgrade.");

    $filename = dirname(__FILE__)."/sql/$basename.sql";
    if (!file_exists($filename)) {
        log_upgrade("Upgrade file `$filename` does not exist.");
        return false;
    }

    $file_content = file($filename);
    $query = '';
    foreach ($file_content as $sql_line) {
        $tsl = trim($sql_line);
        if ($sql_line and (substr($tsl, 0, 2) != '--') and (substr($tsl, 0, 1) != '#')) {
            $query .= $sql_line;
            if (substr($tsl, -1) == ';') {
                set_time_limit(300);
                $sql = trim($query, "\0.. ;");
                $result = $db->execute($sql);
                if (!$result) {
                    log_upgrade("Failure in `$basename` upgrade:\n$sql");
                    if ($error = $db->lastError()) {
                        log_upgrade("$error");
                    }

                    return false;
                }

                $query = '';
            }
        }
    }

    $remainder = trim($query);
    if ($remainder) {
        log_upgrade("Trailing text in `$basename` upgrade:\n$remainder");
        if (DEBUG) trigger_error('Trailing text in upgrade script: '.$remainder, E_USER_WARNING);
        return false;
    }

    log_upgrade("`$basename` upgrade successful.");
    return true;
}

score 0 · Answer 3 · answered Sep 19 '11 at 13:34

I have never resorted to multi-query. When I needed something like that, I moved over to mysqli. Also, if you do not need any results from the query, passing the script to mysql_query will also work. You'll also get those errors if there are exports in an incorrect order that clash with require tables for foreign keys and others.

score 0 · Answer 4 · answered Sep 19 '11 at 13:37

I think the approach of breaking the SQL file to single-queries would be a good idea. Even if its just for comparison purposes (to see if it solves the issue).

Also, I'm not sure how big is your file - but I've had a couple of cases where the file was incredibly big and splitting it into batches did the job.

mysql multi_query intermittently fails

4 Answers4