SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Zealot
    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_idtrue);
    // 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_fileFTP_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:
    PHP Code:
    $myFile "dump.sql";
    $fh fopen($myFile'r');
    $theData fread($fhfilesize($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
    Last edited by jonas-e; Nov 2, 2007 at 03:28.

  2. #2
    SitePoint Zealot
    Join Date
    Oct 2007
    Location
    Bilthoven, Netherlands
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy please ..?

    Hello!

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

    Please - I'm lost ...

  3. #3
    SitePoint Zealot
    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)

  4. #4
    SitePoint Enthusiast
    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.

  5. #5
    SitePoint Zealot
    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, j
    Last edited by jonas-e; Nov 2, 2007 at 10:49.

  6. #6
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,788
    Mentioned
    151 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.....

  7. #7
    SitePoint Zealot
    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.

  8. #8
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,788
    Mentioned
    151 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.....

  9. #9
    SitePoint Zealot
    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.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

  10. #10
    SitePoint Zealot
    Join Date
    Oct 2007
    Location
    Bilthoven, Netherlands
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Quote Originally Posted by skaykay View Post
    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:
    PHP Code:
    $file "dump.sql";
    $fh fopen($file'r');
    $text fread($fhfilesize($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 ...

  11. #11
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,788
    Mentioned
    151 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.....

  12. #12
    SitePoint Zealot
    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

  13. #13
    SitePoint Enthusiast
    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 &

  14. #14
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    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.

    Quote Originally Posted by PHP Manual
    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.
    Saul

  15. #15
    SitePoint Zealot
    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=512342
    Last edited by jonas-e; Nov 4, 2007 at 05:03.

  16. #16
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jonas-e View Post
    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.
    Saul


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •