SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Editing a .sql (dump) file.

    Hi all,

    Is it possible for me to remove some of the SQL commands in a .sql file generated by mysqldump and then to have this editted .sql file to still be read into MySQL.

    I have created a .sql file through the musqldump command and all i want to do is to just have all the queries that I recently inserted into the database. Is it possible to have a .sql file to just contain a set in of INSERT queries to be read in by MySQL without all the CREATE TABLE query strings?

    I seem to have tried this but nothing was inserted into the database for some reason?

    Thanks

    Tryst

  2. #2
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,423
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Sure. I do this all of the time when I'm testing out a site. Just empty the tables then use the sql dump to repopulate the tables.

  3. #3
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stymiee - but what I want to do is to retain all the information that is in the table that I want to import the .sql file into. Thus, all the INSERT SQL commands that are left from the editted .sql (dumped) file will add data to the data that is already contained in the table that I wish import to.

    Do you get what I mean?

    Thanks

    Tryst

  4. #4
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,423
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Do you want to add the info from the dump into an existingtable that currently has data? Or a blank table? To tell you the truth it doesn't matter. If you run the insert commands from the dump the data will be added to the appropriate tables. Any existing data will be unaffected.

    If I don't seem to have the right idea, try explaining it for me differently or providing a simple example.

  5. #5
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to add data from a dump file into an existing table that already currently has data.

    What I don't understand is that went I last ran a dump file with just INSERT SQL commands the data was not inserted into the table for some reason. ?confused?

    Tryst

  6. #6
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,423
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Well, it seems like you have the right idea. Did you get any error messages displayed? Are you doing it from the command line or through PHPMyAdmin?

  7. #7
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had no errors displayed. The .sql file was imported correctly (so it seemed), but when I tried to search for any data that was supposedly imported into the database I never got any results back. So with this I assumed that it never worked.

    I done it through PHPMyAdmin.

    I suppose I could create another .sql dump file that only contains a few INSERT SQL statements and try again...

    Tryst

  8. #8
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,423
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    I've had PHPMyAdmin not play nice with sql scripts before so it may just be a bug in their system. Never really gave it too much thought so it could have just been user error. Try a smaller one to test it out and, if you can, try it from the command line (ssh).

  9. #9
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The last couple times I've used phpMyAdmin to do dumps, I've had to cut and paste the tables creation sql then remove all that from the dump file and then run the just the inserts.

    I don't know why. The file that phpMyAdmin creates looks good, but in my last couple experiences it hasn't worked without the above noodling.

    I use phpMyAdmin because I have to look at a man page every time I use mysqldump but the mysqldump files have always worked perfectly for me as opposed to phpMyAdmin's. But like I said, I don't know why.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  10. #10
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    samsm - so you've had no problems importing a .sql file into a database when you've removed most of the SQL from a .sql dump file?

    Thanks

    Tryst

  11. #11
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, after removing the table creation sql (and comments), copying and pasting those in manually (creating the tables), then doing:
    mysql -u user -p -D databasename < /path/to/sql.sql

    ...has worked when simply doing that last step has not.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  12. #12
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I have just done a dump from the DOS prompt on the desktop, edited the file by removing all the create table queries and most of the insert statements that I didn't want to run. I then loaded up PHPMyAdmin on the hosting server and ran the .sql dump file that contained only a few INSERT queries.

    The result was successful, so am not sure why it has worked now, and not previously :-s.

    What I did notice from a dump file generated by PHPMyAdmin was that it would wrap the name of the table (inwhich the queries would be run against) in special characters (sorry, I don't know the name of the characters) like this `products`. Where as a dump file generated through a command prompt omitted these characters and didn't wrap the table name in anything. Whether this has anything to do with anything, I am not sure...

    Thanks

    Tryst

  13. #13
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    `These are backtics.`

    They are what you use when you want MySQL to do something strange like treat a keyword as a normal word. Like if you wanted to name a column `insert`.

    Backtics shouldn't negatively affect anything, but I don't know why the dump files don't work so beats me.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?


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
  •