php mysql dump: execute dump file through php

[jonas-e]

I used this thread to get started on dumping, then upload to remote server:
http://www.sitepoint.com/forums/showthread.php?t=485403
using this code to dump the database on my local machine:

$d="C:/xampp/mysql/bin/mysqldump --skip-opt --insert-ignore --add-drop-table --host=*** --user=*** --password=*** db_name > dump.sql";
exec($d);
// set up basic connection
$ftp_server="ftp.server.no";
$conn_id = ftp_connect($ftp_server);
// login with username and password
$ftp_user_name="***";
$login_result = ftp_login($conn_id, $ftp_user_name, '***');
// turn passive mode on
ftp_pasv($conn_id, true);
// check connection
if ((!$conn_id) || (!$login_result)) {
        echo "FTP connection has failed!<br />";
        echo "Attempted to connect to $ftp_server for user $ftp_user_name<br />";
        exit;
    } else {
        echo "Connected to $ftp_server, for user $ftp_user_name<br />";
    }
$destination_file="dump.sql";
$source_file="dump.sql";
// upload the file
$upload = ftp_put($conn_id, $destination_file, $source_file, FTP_BINARY);
// check upload status
if (!$upload) {
        echo "FTP upload has failed!<br />";
    } else {
        echo "Uploaded $source_file to $ftp_server as $destination_file<br />";
    }
// close the FTP stream
ftp_close($conn_id); 

That works great!
Then I try to execute the dump file on the remote web host like this:

$myFile = "dump.sql";
$fh = fopen($myFile, 'r');
$theData = fread($fh, filesize($myFile));
fclose($fh);
if (!mysql_query($theData)) {
    echo mysql_error();
} else {
    echo "SUCCES!<br />";
} 

And I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101' at line 7

Can anyone see what the error is?
Am I perhaps using an entirely wrong php function "mysql_query"?

cheers, j

[jonas-e]

Hello!

Is there really no one who can help me out on this ..?

Please - I'm lost ...

[jonas-e]

Someone suggested this:

When you output the MySQL dump file from your local DB you need to set compatibility on it to a lower version either lower then the version on the live server or equal.

So let me clarify:

I just tested the mysql_query($text_from_dump_file) on my local machine; the very same server which produced the dump file - and I still get the same error!

But it works with a mysql command prompt and with phpmyadmin. So it must something about the way the dump commands are passed on to the mysql server through php ..?

I figure it could perhaps be done through an exec('...') function instead - but how ..?

(My web host has confirmed, though, that they are running mysql version 4.1.1)

[skaykay]

I think you should split the variable on semi-colon ";"
and get an array of statements. Then execute each
statement.

[jonas-e]

Thanks skaykay, very interesting work-around!
I tried it but ran into this problem:

Which is caused by the fact that the text in the database entries occasionally contains ";" ...
- does anyone have ideas how to work around that?
- this splitting into single commands one-by-one makes sense - but surely there must be a direct way?
cheers, j

[spikeZ]

Hi jonas,
what is on the line immediately above:

; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101'

The problem might be a stray ' somewhere as the error message doesnt start with the /*! but just before the end of the previous line.

[jonas-e]

Thanks .. the dump.sql file is located here:
http://skred-svalbard.no/dump.sql
i.e. the file starts like this:

-- MySQL dump 10.11
--
-- Host: localhost Database: skred_svalbard_
-- ------------------------------------------------------
-- Server version 5.0.41-community-nt
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `avalanches`
--

DROP TABLE IF EXISTS `avalanches`;

I can't see any stray ' in the dump text ...

[spikeZ]

hmmm, that error message text doesn't appear in the dump file.
Do you still get the error message?

[jonas-e]

Oh, sorry - that's from an older dump when I used this command:

C:/xampp/mysql/bin/mysqldump --opt --host=*** --user=*** --password=*** db_name > dump.sql

I am now using the one mentioned above. Anyway, the error message just skips down to the next line it doesn't like:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE' at line 6

[jonas-e]

I think you should split the variable on semi-colon ";"
and get an array of statements. Then execute each
statement.

I found a work-around which works:

$file = "dump.sql";
$fh = fopen($file, 'r');
$text = fread($fh, filesize($file));
fclose($fh);

// insert seperator before each command
$sep=" |SEP| ";
$text=ereg_replace("DROP"," $sep DROP",$text); 
$text=ereg_replace("INSERT"," $sep INSERT",$text); 
$text=ereg_replace("CREATE"," $sep CREATE",$text); 
$text=ereg_replace("--"," $sep --",$text); 
$text = eregi_replace(
    '/\*([^\\[]+)\*/',
    '', $text); // remove what is btw /* and */

// create array from seperator and run
// queries one-by-one
$sqls=explode(' |SEP| ',$text);
foreach ($sqls as $sql) {
    echo '<div style=\'border:solid 1px grey;margin:0 auto 8px auto;width:500px;\'><p>'
        .$sql.'</p>';
    if (!mysql_query($sql)) {
        echo '<p style=\'color:red;\'>ERROR: '.mysql_error().'</p>';
    } else {echo "<p style='color:red;'>SUCCES!</p>";
    }
    echo "</div>";
} 

- as long as someone doesn't write content in the database with the words INSERT, CREATE or DROP in capital letters - so it's not entirely fool-proof.

There must be some way of running the full dump in one go ...

[spikeZ]

it seems that the dump is falling on ANY semi-colon for some reason.

If the workaround works then roll with it for now. I will keep thinking about it!

[jonas-e]

Thanks spikeZ!
I managed to make a system which works for now - but I would feel more safe handing the cms over to others if I knew that the system is less fragile ...
/jonas

[Chemo]

mysql database_name < /path/to/dump.sql &

[php_daemon]

it seems that the dump is falling on ANY semi-colon for some reason.

If the workaround works then roll with it for now. I will keep thinking about it!

mysql_query() won't run multiple queries, afaik

Loading, please wait...

... Done.

mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

[jonas-e]

Thanks chemo, i'll try something along the line of
exec("/path/to/mysql_bin/mysql database_name < /path/to/dump.sql"):
- just gotta find out if that is possible on the web host ...

php_daemon: Is there another php function which can pass multiple queries?

cheers, j

btw - this topic has also been discussed in the mysql forum: http://www.sitepoint.com/forums/showthread.php?t=512342

[php_daemon]

php_daemon: Is there another php function which can pass multiple queries?

Nope, that's why you have to separate each query and run separately; that's what you're already doing.