Restore SQL V4 Backup to SQL V5 Database using MyPhpAdmin fails

  • Resolved wendyhouse

    (@wendyhouse)


    13 years, 11 months ago

    Hi Guys, I realise this is probably out of your scope but I’m getting deperate and you guys have always been so helpful

    Request: can you tell me how to identify the ‘create database’ line in a V4 .sql backup so that I can restore the back-up to a V5 database for my wordpress blog.

    Background.
    To upgrade my wordpress blog to 2.9.2 I needed to upgrade my sql server from V4 to V5

    I used the wordpress guidance to create a backup .sql file of my V4 database. I also used the in-wordpress back-up facility. Thank you for really clear step by step guidance and automated process.

    Using GoDaddy web hosting I created a V5 database, then upgraded my wordpress, changed the config file then posted a few posts on the blog to check it all works – it does :-).

    Then I tried to restore the backup first using GoDaddy UI – it gave me a success message but didnt actually restore, then using MyPhpAdmin (StarfieldTech), again it gave me a success message without actually restoring the backup. Both support services told me the failure is because I still have the you “create database line in your SQL file”. They will not tell me how to identify this line.

    Searching the backup SQL file (in wordpad) for ‘Create Database’ produces no results. I do not know what line to delete in order to restore my database. Please help me 🙂

Viewing 9 replies - 1 through 9 (of 9 total)
  • MichaelH

    (@michaelh)

    13 years, 11 months ago

    To answere your question, it would have the word CREATE
    http://dev.mysql.com/doc/refman/5.1/en/create-table.html

    But in theory, you should be able to completely DROP the new tables then use phpMyAdmin to import the V4 database into the V5 database. I believe you would want to set the compatibility mode to MYSQL40.

    mrmist

    (@mrmist)

    13 years, 11 months ago

    It should work off the bat, but anyway, the CREATE DATABASE would be near the top of your import file, ‘cos it’s done first. Alternatively if you still have access you can tell phpmyadmin not to include the CREATE DATABASE statement when you take the backup.

    Thread Starter wendyhouse

    (@wendyhouse)

    13 years, 11 months ago

    Thank you for taking the trouble to – think about this – give me relevant useful information.

    Now I know the text I am looking for is create table rather than create database.

    I tried deleting (using wordpad) the create table text (illustrated below) from the backup made automatically by wordpress. Then tried importing the file with this missing text using MyPhpAdmin UI, making sure to select V4 compatibility drop-down.

    The restore failed – in that the test posts in the V5 database are what shows on my blog 🙁

    Maybe there is some other problem. Both Starfieldtech and Godaddy said that the crate databse text was my problem. but could it be comething-else that interrupts a restore – like the size of the file (16.4 MB)? How would you recommend that I solve this? In my ideal world WordPress would provide an automatic restore back-up aswell as the automatic make-back-up.

    I REALLY appreciate your input because you are wordpress experts, and supporters, not .sql server experts. I’ll do my best to help you to help me.

    This is the text I deleted from my backup .sql before restoring it. The restore failed:

    CREATE TABLE wp_startupcomments (
    comment_ID bigint(20) unsigned NOT NULL auto_increment,
    comment_post_ID bigint(20) unsigned NOT NULL default ‘0’,
    comment_author tinytext NOT NULL,
    comment_author_email varchar(100) NOT NULL default ”,
    comment_author_url varchar(200) NOT NULL default ”,
    comment_author_IP varchar(100) NOT NULL default ”,
    comment_date datetime NOT NULL default ‘0000-00-00 00:00:00’,
    comment_date_gmt datetime NOT NULL default ‘0000-00-00 00:00:00’,
    comment_content text NOT NULL,
    comment_karma int(11) NOT NULL default ‘0’,
    comment_approved varchar(20) NOT NULL default ‘1’,
    comment_agent varchar(255) NOT NULL default ”,
    comment_type varchar(20) NOT NULL default ”,
    comment_parent bigint(20) unsigned NOT NULL default ‘0’,
    user_id bigint(20) unsigned NOT NULL default ‘0’,
    comment_comvatar_url text NOT NULL,
    comment_favicon_url text NOT NULL,
    PRIMARY KEY (comment_ID),
    KEY comment_approved (comment_approved),
    KEY comment_post_ID (comment_post_ID),
    KEY comment_approved_date_gmt (comment_approved,comment_date_gmt),
    KEY comment_date_gmt (comment_date_gmt)
    ) TYPE=MyISAM ;

    mrmist

    (@mrmist)

    13 years, 11 months ago

    phpmyadmin should say what the max size of import file is when you go to the import screen.

    mrmist

    (@mrmist)

    13 years, 11 months ago

    If you have a new, empty, database then you’ll want to leave the create table statements in.

    If you have a database that’s already being used for WordPress, you should remove the create tables. (Although in this case you’d probably have issues with existing data)

    There will be more than one create table unless you only exported one table.

    Thread Starter wendyhouse

    (@wendyhouse)

    13 years, 11 months ago

    Import Size Max: 8,192KiB

    Looks like my import is 2 (and a tiny bit) x the import size maximum. Probably my main problem.

    Does the ‘Partial Import’ checkbox play a role here – I’ve always left it in the default position of checked/

    How do I tackle the size problem? Can I create 3 back-ups with different tables in each? and import the 3 backups consecutiely?

    GoDaddy provides me with the ability to create some databases that I haven’t used so I could create a completely empty database and import into that to bypass the possible problem with already having content – then update my worpress config file to point to the new database.

    Apologies for this thread being so long, I really appreciate your time and thought and am trying to think about and solve the problem myself. too. Being without my 5 years of blog posts really makes me sad

    many thanks
    Hilary

    MichaelH

    (@michaelh)

    13 years, 11 months ago

    You could use a text editor and break that .sql file into pieces.

    Thread Starter wendyhouse

    (@wendyhouse)

    13 years, 11 months ago

    I do not know the formatting rules that are acceptable for a import/expert back-up file.

    Hence despite knowing how to use many different text editors – I do not know ‘how’ to ‘split’ the contents of a back-up file in a way that does not cause errors during the restore.

    I was hoping that you guys could help.

    Thread Starter wendyhouse

    (@wendyhouse)

    13 years, 11 months ago

    I am marking this as resolved – even though it has not been resolved – because I am trying refremaing the problem to find a solution that I can manage.

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘Restore SQL V4 Backup to SQL V5 Database using MyPhpAdmin fails’ is closed to new replies.