Merging 2 versions of a table, old with current, is it possible ?

  • sabinou

    (@sabinou)


    15 years, 1 month ago

    Hello,

    Do you know if I can “merge”, in my blog’s comments table, the data of a former backup ? A part of this old table backup is missing from the present table because of a failed sql import once month ago, and I’d wish to recover the missing comments.

    I know this isn’t clear, I owe you an explanation :

    The reason : my blog’s database evolves constantly, but one month ago I damaged my DB and had to restore a backup copy with phpmyadmin.
    Unfortunately, I had overlooked the fact that my DB’s size had considerably grown since the last time I had to perform a backup (I export my database in zip format with phpmyadmin once per week), and phpmyadmin didn’t manage to import my whole DB.
    The only solution I found at that time (I now use php scripts to backup and restore it), was to use a text editor to split my big .sql file into smaller .sql files, one per table, and ask phpmyadmin to load each of them separately. That worked… Or almost, the largest table had a problem.
    The comments table was only partially imported by phpmyadmin, who froze during the import. A dozen attempts (preceded by a table deletion to start cleanly) failed to import the comments successfully, and seeing my blog still worked even if it had lost half of its comments, I gave up and went on with a crippled comments table.
    One month later, I regret this choice, but since new comments were added afterwards, I don’t know what to do.

    By default, if I ask phpmyadmin to import an sql file into an already existing table, phpmyadmin refuses it, telling the table already exists.

    Is there a way to merge in my current comments table the missing data from my former backup copy ?

    I’m ready to split my comments table in any number of smaller sql files, once again with a text editor, if needed. Or I can make it with a php script.
    But I don’t know at all if this is simply possible, to merge all data into an older comments table.

    Thank you if you can help me with that problem 🙂

    Sabinou

Viewing 1 replies (of 1 total)
  • figaro

    (@figaro)

    15 years, 1 month ago

    By default, if I ask phpmyadmin to import an sql file into an already existing table, phpmyadmin refuses it, telling the table already exists.

    That’s probably because the .sql file has a “create table” line in it.

    If you pull out just the insert comments line and the following comments, then you may be able to import them as long as there are no conflicting entries in unique fields…it’s worth a try. Backup your database first so you can recover if need be, then open your old sql file. Find where the insert comments section starts…it should look something like this:

    INSERT INTO <code>wp_comments</code> (<code>comment_ID</code>, <code>comment_post_ID</code>, <code>comment_author</code>, <code>comment_author_email</code>, <code>comment_author_url</code>, <code>comment_author_IP</code>, <code>comment_date</code>, <code>comment_date_gmt</code>, <code>comment_content</code>, <code>comment_karma</code>, <code>comment_approved</code>, <code>comment_agent</code>, <code>comment_type</code>, <code>comment_parent</code>, <code>user_id</code>) VALUES

    Start copying there and copy all the comments that you know are NOT already in your database. Paste that into a text editor and make a new sql file. Find the last comment (last line) in the file and at the end of the file replace the , with a ;

    Then try to import this into the database…should work.

    If your file is too large, you can break it apart or use bigdump to import the complete file…see this video for how to use bigdump.

    http://educhalk.org/blog/?p=125

    Again, take a good backup first just in case…

Viewing 1 replies (of 1 total)
  • The topic ‘Merging 2 versions of a table, old with current, is it possible ?’ is closed to new replies.