Mysql multi_query breaks

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 problems and all tables are created and data is inserted. (I am creating a database from scratch)

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

I decided NOT to use multi_query because it seems to be an error and sees if an error occurs using only mysql_query in each decimal place. Has anyone encountered such a problem?

Answer 1

I saw similar problems when using multi_query with queries that can create or modify tables. In particular, I tend to get InnoDB 1005 errors that seem to be related to foreign keys; it, like MySQL, does not completely complete one statement before moving on to the next, so foreign keys do not have a corresponding referent.

On one system, I break up problematic statements into my own files. In another case, I actually ran each command separately, breaking 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; } 

Answer 2

Fast and efficient

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

Answer 3

I have never addressed a multitasking request. When I needed something like this, I switched to mysqli . Also, if you don't need any results from the query, passing the script to mysql_query will also work. You will also get these errors if there is an export in the wrong order that interferes with the need tables for foreign keys and others.

Answer 4

I think the approach of hacking an SQL file into single-line queries would be a good idea. Even if this is true for comparison purposes (to check if it solves the problem).

Also, I'm not sure how big your file is, but I had several cases where the file was incredibly large and split it into lots, doing this job.