SitePoint Sponsor |
|
User Tag List
Results 1 to 16 of 16
-
Nov 1, 2007, 04:28 #1
- Join Date
- Oct 2007
- Location
- Bilthoven, Netherlands
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
php mysql dump: execute dump file through php
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:
PHP Code:$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);
Then I try to execute the dump file on the remote web host like this:
PHP Code:$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 />";
}
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, jLast edited by jonas-e; Nov 2, 2007 at 03:28.
-
Nov 2, 2007, 01:14 #2
- Join Date
- Oct 2007
- Location
- Bilthoven, Netherlands
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
please ..?
Hello!
Is there really no one who can help me out on this ..?
Please - I'm lost ...
-
Nov 2, 2007, 03:22 #3
- Join Date
- Oct 2007
- Location
- Bilthoven, Netherlands
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
NOT mysql version issue; but how to pass dump commands through php?
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)
-
Nov 2, 2007, 04:22 #4
- Join Date
- Aug 2007
- Posts
- 80
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I think you should split the variable on semi-colon ";"
and get an array of statements. Then execute each
statement.
-
Nov 2, 2007, 04:59 #5
- Join Date
- Oct 2007
- Location
- Bilthoven, Netherlands
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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, jLast edited by jonas-e; Nov 2, 2007 at 10:49.
-
Nov 2, 2007, 05:23 #6
- Join Date
- Aug 2004
- Location
- Manchester UK
- Posts
- 13,807
- Mentioned
- 158 Post(s)
- Tagged
- 3 Thread(s)
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.Mike Swiffin - Community Team Advisor
Only a woman can read between the lines of a one word answer.....
-
Nov 2, 2007, 05:35 #7
- Join Date
- Oct 2007
- Location
- Bilthoven, Netherlands
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 ...Last edited by jonas-e; Nov 4, 2007 at 04:52.
-
Nov 2, 2007, 06:41 #8
- Join Date
- Aug 2004
- Location
- Manchester UK
- Posts
- 13,807
- Mentioned
- 158 Post(s)
- Tagged
- 3 Thread(s)
hmmm, that error message text doesn't appear in the dump file.
Do you still get the error message?Mike Swiffin - Community Team Advisor
Only a woman can read between the lines of a one word answer.....
-
Nov 2, 2007, 06:55 #9
- Join Date
- Oct 2007
- Location
- Bilthoven, Netherlands
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.sqlI 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
-
Nov 2, 2007, 07:35 #10
- Join Date
- Oct 2007
- Location
- Bilthoven, Netherlands
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I found a work-around which works:
PHP Code:$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>";
}
There must be some way of running the full dump in one go ...
-
Nov 2, 2007, 07:53 #11
- Join Date
- Aug 2004
- Location
- Manchester UK
- Posts
- 13,807
- Mentioned
- 158 Post(s)
- Tagged
- 3 Thread(s)
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!Mike Swiffin - Community Team Advisor
Only a woman can read between the lines of a one word answer.....
-
Nov 2, 2007, 10:49 #12
- Join Date
- Oct 2007
- Location
- Bilthoven, Netherlands
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Nov 3, 2007, 07:31 #13
- Join Date
- Jun 2006
- Location
- /USA/Kentucky/Richmond/
- Posts
- 27
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
mysql database_name < /path/to/dump.sql &
I passed my Zend Certified Engineer exam.
Why not certify your PHP skills too?
Visit Bobby's MySpace
-
Nov 3, 2007, 08:11 #14
-
Nov 3, 2007, 09:24 #15
- Join Date
- Oct 2007
- Location
- Bilthoven, Netherlands
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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=512342Last edited by jonas-e; Nov 4, 2007 at 05:03.
-
Nov 3, 2007, 09:32 #16
Bookmarks