WordPress.org

Forums

Restore Database Problem (23 posts)

  1. risach
    Member
    Posted 10 years ago #

    I was following the instructions (Codex 1, 2, Podz) closely to backup my local WP1.5-database (MySQL 4.1.9, phpAdmin 2.6.1-pl3) and restore it on the server-site (MySQL 4.0.23a, phpMyAdmin 2.6.1-pl2).
    After selecting my local backup.sql-file the server's phpMyAdmin gave me the error:

    MySQL said:
    #1064 - 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=6' at line 10

    Could anybody help? What kind of measures do I have to take to fix this, as I understand it, Charset-encoding problem?

    Thanks for help!

    Btw: As a total OS X-Cocoa-head I would love to use CocoaMySQL instead of phpAdmin: but my first attempts to choose the right settings failed. Are there any guides or stuff available, specifically for using CocoaMySQL and WordPress? Couldnt find none so far.

  2. Mark (podz)
    Support Maven
    Posted 10 years ago #

    Cocoa ? Send me a mac, give me a day to two and I shall advise :)

    The error ... you are correct, and this could mean a fair bit of editing.
    Backup the original.

    Open the .sql file in something sensible like BBEdit / textwrangler
    Each table in the db has to be created in a certain way, and each table dump has that info.
    "CREATE TABLE wp_comments (" followed by lots of stuff
    The last line in that has some info:
    ") TYPE=MyISAM AUTO_INCREMENT=2411 ;" and it may also have the charset info.

    Go through the .sql file and delete each instance of the charset being mentioned.
    Then try and import again.

    Please please work off a backup.

  3. risach
    Member
    Posted 10 years ago #

    Wow, that was a fast answer, thanks! :)

    Unfortunately, I dont get it yet: I have BBEdit and a backup of my sql-file; but what is it precisely that has to be edited?
    Here's a bit of the sql-file, it's the part with the categories: do you want me to delete everything between "CREATE TABLE" and "... AUTO_INCREMENT=6 ;"?
    Of course, there are a lot of instances of text-blocks like this.

    ------------------from the .sql-file-----------------------------

    -- Table structure for table wp_categories
    --

    DROP TABLE IF EXISTS wp_categories;
    CREATE TABLE wp_categories (
    cat_ID bigint(20) NOT NULL auto_increment,
    cat_name varchar(55) NOT NULL default '',
    category_nicename varchar(200) NOT NULL default '',
    category_description longtext NOT NULL,
    category_parent int(4) NOT NULL default '0',
    PRIMARY KEY (cat_ID),
    UNIQUE KEY cat_name (cat_name),
    KEY category_nicename (category_nicename)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

    --
    -- Dumping data for table wp_categories
    --

    INSERT INTO wp_categories VALUES (1, 'General', 'general', '', 0);
    INSERT INTO wp_categories VALUES (2, 'Generisch', 'generisch', '', 0);
    INSERT INTO wp_categories VALUES (3, 'Bla√É≈�rot', 'bla%c3%9frot', '', 0);
    INSERT INTO wp_categories VALUES (4, 'Veilchenrot', 'veilchenrot', '', 0);
    INSERT INTO wp_categories VALUES (5, 'Schwarzrot', 'schwarzrot', '', 0);

    ----------------------------------------------------------

    Actually, I was thinking that I could fix something about the charset-encoding when exporting the database, in order to get a sql-backup-file which is suitable for the MySQL on the server-site. Wrong idea?
    I mean, I thought I could adjust the exported db-information in any way necessary.

    Would be great if you could give me another hint. Sorry, being new to all this stuff.

  4. Mark (podz)
    Support Maven
    Posted 10 years ago #

    DROP TABLE IF EXISTS wp_categories;
    CREATE TABLE wp_categories (
    cat_ID bigint(20) NOT NULL auto_increment,
    cat_name varchar(55) NOT NULL default '',
    category_nicename varchar(200) NOT NULL default '',
    category_description longtext NOT NULL,
    category_parent int(4) NOT NULL default '0',
    PRIMARY KEY (cat_ID),
    UNIQUE KEY cat_name (cat_name),
    KEY category_nicename (category_nicename)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

    The bit in bold is what I would delete.
    And no need for apologies :)

    This has come about because of the difference in charsets, and yes there is a way of altering a db charset but I have lost the link right now.
    The way I have described does work - I've done it a few times - it's just boring to do :)

    And keep posting if you have questions !

  5. risach
    Member
    Posted 10 years ago #

    Well, thanks to your advice, it worked -- somewhat, that is.
    The server's MySQL took my sql-backup-file, but: it screwed the german Umlaute (like: ä ö ü, etc.) throughout the WP-installation.

    And, what is worse, but probably a different problem: all links internal to WordPress still direct towards my local site, i.e.: they all start with "http://127.0.0.1" instead of "http://auteuil.au.ohost.de".
    Even http://auteuil.au.ohost.de/wordpress/wp-login.php changes immediately/gets 'forwarded' to http://127.0.0.1/wordpress/wp-login.php.

    Any further advice? would be great---

  6. Mark (podz)
    Support Maven
    Posted 10 years ago #

    Change your siteurl and home values in the db:
    http://www.tamba2.org.uk/wordpress/site-url/

  7. risach
    Member
    Posted 10 years ago #

    Thanks for your further reply -- unfortunately, although I followed the instructions regarding correcting the siteurl, and although I double-checked that the url was indeed appropriately changed, the wp-internal-links still direct wrongly to my local machine.
    Neither any progress on the german Umlaute-site of things. I double-, triple-checked all the charset-encodings of the engaged databases and WP's -- to no avail. All utf-8.

    I think I try a fresh install, since I never had such issues before.

    I am still very interested in figuring out how to swap databases between local machine and server-installation. So if anyone could give a helping hand, this would be very much appreciated. Thanks to podz for helping me out so far.

  8. Mark (podz)
    Support Maven
    Posted 10 years ago #

    Did you alter both siteurl AND the home value ?

  9. risach
    Member
    Posted 10 years ago #

    Oh goodness, you're right: I missed to change the home value, I didn't realize that there is more than one page in 'Browse'. Although your page was perfectly clear about that, that was my fault.
    I have to say though, that in the by now fresh installation there is no value given for 'home', although there is a value for siteurl.

    Ok, anyway, I re-installed my WP and now the WP-internal-links link in a good way and the german Umlaute are treated well again so far.

    Now, I haven't yet tried again to upload my database-file, being really afraid that things get messed up again due to some charset-encoding-issues which I still do not understand.

    Well, I think I am too curious to not give it another try ... :)

  10. risach
    Member
    Posted 10 years ago #

    Ok, I hope to not bother anybody, but anyway. The saga continues ...:

    I uploaded my local database-backup (sql-file) once again to my host: while doing this I tried to be even more careful than before; I exported the backup anew from my local MySQL and I realized that in MySQL 4.1.9, phpAdmin 2.6.1-pl3 there is a compatibility-option when exporting. I exported the db therefore two times, first with compatibility-mode: NONE, second with compatibility-mode: MYSQL40, having in mind that my host still runs on MySQL 4.0.23a.
    Ok, to make a long story short: the compatibility-mode does almost exactly the same thing as podz advised to me to do earlier, namely, to leave out 'DEFAULT CHARSET=latin1' in the sql-file (the second thing compatibility-mode does, is changing 'TYPE=MyISAM' into 'ENGINE=MyISAM' -- I checked that carefully with BBEdit's 'Find Differences').

    Okay, the sql-file was therefore compatible with my host's MySQL without further hand-editing.
    But: when I had the sql-file successfully imported, again, my internal WP-links were screwed (they directed again to my local machine, where the original DB resides), and screwed the german Umlaute as well.

    I could fix the link-issue by following podz' instructions (see above), this time more carefully.

    Ok, that means: the issue results from some incompatibility between the used MySQL's, phpAdmin's, I guess. What exaclty is going on, is so far beyond me.

    I read in the WP-support-forum about some problems with german Umlaute -- but since I went through the whole process with utf-8, I really dont understand, where things get messed up.

    Any idea would be warmly welcomed ...

  11. Mark (podz)
    Support Maven
    Posted 10 years ago #

    I'm not entirely clear which issues need addressing ?
    It's all solveable though :)

  12. risach
    Member
    Posted 10 years ago #

    Oh, a quick addition: I took care to have the "MySQL connection collation" set to 'latin1-swedish-ci', since this seems to be appropriate (from what I read on the forum; I can hardly grasp the exact meaning of "collation", anyway).
    The thing is, only in the newer setup: MySQL 4.1.9, phpAdmin 2.6.1-pl3, this parameter seems to be an option and adjustable.

  13. risach
    Member
    Posted 10 years ago #

    podz: the remaining issue are the screwed german Umlaute!

    Even without understanding german too well, I guess, you could see the strange 'string-patterns' at: http://auteuil.au.ohost.de/wordpress/

    Thanks for being around all day! such a bless ...

  14. Mark (podz)
    Support Maven
    Posted 10 years ago #

    I have no experience at all with other language characters, so I'm of very limited use.
    Have you tried Annette and other WP users at http://www.wordpress.de ?
    I'd say they have this issue sorted :)

  15. risach
    Member
    Posted 10 years ago #

    Well, it seems (german WP-forum) there are some unresolved issues regarding charset-encoding -- fortunately, newly edited posts show up fine on my blog; since I just get started I am going to hand-edit the rest.
    Still, compatibility between different MySQL-/, charset-encoding-versions (or whatever) is still on my wish-list.

  16. Mark (podz)
    Support Maven
    Posted 10 years ago #

    One option ...... well, two actually:

    - Export the db again, then run a find/replace on the character replacement

    - See if anyone around here can write the sql query to do the same above in phpmyadmin

  17. jamesinealing
    Member
    Posted 10 years ago #

    Just to add a note that I had the same #1064 mySQL error and found a solution - the secret for me was to change the 'SQL export compatibility' option in phpMyAdmin 2.6.1-PL2 to MYSQL4.0 and then the restore worked error-free.

  18. ojoshiro
    Member
    Posted 10 years ago #

    Hi there,

    I have the following error occuring while trying to restore my backup on a new server :

    #1064 - 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 'visit bigint(11) NOT NULL default '0',
    stamp` datetime defa

    Does this pain have to do with the CHARSET issue ?

    I can't find a way to change the SQL export compatibility as mentioned above :(
    Any idea someone ?

  19. Anonymous
    Unregistered
    Posted 10 years ago #

    If it is the CHARSET issue, then one solution is to edit the .sql file and remove that line from each 'create_table' part.

  20. ojoshiro
    Member
    Posted 10 years ago #

    Yep. Unfortunately, there's no reference to the CHARSET variable in my .sql backup file :(
    I guess it's something else... Thanks anyway !

  21. cheewl
    Member
    Posted 9 years ago #

    I had the same problem after editing the .sql file to replace the old domain with the new domain.

    I managed to solved it simply by saving it using Notepad with ANSI encoding. Hope it helps you too :)

  22. miaow
    Member
    Posted 9 years ago #

    Thank you, THANK YOU for the suggestion to 'delete each instance of the charset being mentioned' - I knew that changing webhosts wasn't going to be as easy as it seemed - and when I got this error when trying to restore my database, I was pulling my hair out! Deleted all mentions and it is now telling me 'Your SQL-query has been executed successfully'. Yay!

    :)

  23. paperlion
    Member
    Posted 9 years ago #

    Aahh, an old post. Hope these still get read.

    I am having other character set problems.

    Starting with a Windows-1250 (Central European) encoded email that looks fine, I bath it in Notepad (or various other re-encoders), resaving there as UTF8. Copy again and paste into WordPress. Still fine. Backup and restore, and presto - apostrophes and quotes have become question marks.

    1) Is this something with backup / restore encoding changes?

    2) If WP is displaying it correctly, isn't it then solid UTF8?

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.