Restore SQL V4 Backup to SQL V5 Database using MyPhpAdmin fails

To answere your question, it would have the word CREATE
https://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.

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.

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 ;

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

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.

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

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

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.

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.