Title: Restore Database Problem Last modified: August 18, 2016 --- # Restore Database Problem * [risach](https://wordpress.org/support/users/risach/) * (@risach) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/) * I was following the instructions (Codex [1](http://codex.wordpress.org/Backing_up_your_database), [2](http://codex.wordpress.org/Restoring_your_database_from_backup), [Podz](http://www.tamba2.org.uk/wordpress/backup/)) 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. Viewing 15 replies - 1 through 15 (of 22 total) 1 [2](https://wordpress.org/support/topic/restore-database-problem/page/2/?output_format=md) [→](https://wordpress.org/support/topic/restore-database-problem/page/2/?output_format=md) * [Mark (podz)](https://wordpress.org/support/users/podz/) * (@podz) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163128) * 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. * Thread Starter [risach](https://wordpress.org/support/users/risach/) * (@risach) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163132) * 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. * [Mark (podz)](https://wordpress.org/support/users/podz/) * (@podz) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163133) * 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 ! * Thread Starter [risach](https://wordpress.org/support/users/risach/) * (@risach) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163138) * 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”](http://127.0.0.1”); instead of “[http://auteuil.au.ohost.de”](http://auteuil.au.ohost.de”);. Even [http://auteuil.au.ohost.de/wordpress/wp-login.php](http://auteuil.au.ohost.de/wordpress/wp-login.php) changes immediately/gets ‘forwarded’ to [http://127.0.0.1/wordpress/wp-login.php](http://127.0.0.1/wordpress/wp-login.php). * Any further advice? would be great— * [Mark (podz)](https://wordpress.org/support/users/podz/) * (@podz) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163146) * Change your siteurl and home values in the db: [http://www.tamba2.org.uk/wordpress/site-url/](http://www.tamba2.org.uk/wordpress/site-url/) * Thread Starter [risach](https://wordpress.org/support/users/risach/) * (@risach) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163193) * 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. * [Mark (podz)](https://wordpress.org/support/users/podz/) * (@podz) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163197) * Did you alter both siteurl AND the home value ? * Thread Starter [risach](https://wordpress.org/support/users/risach/) * (@risach) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163222) * 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 … 🙂 * Thread Starter [risach](https://wordpress.org/support/users/risach/) * (@risach) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163242) * 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 … * [Mark (podz)](https://wordpress.org/support/users/podz/) * (@podz) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163244) * I’m not entirely clear which issues need addressing ? It’s all solveable though 🙂 * Thread Starter [risach](https://wordpress.org/support/users/risach/) * (@risach) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163245) * 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. * Thread Starter [risach](https://wordpress.org/support/users/risach/) * (@risach) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163246) * 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/](http://auteuil.au.ohost.de/wordpress/) * Thanks for being around all day! such a bless … * [Mark (podz)](https://wordpress.org/support/users/podz/) * (@podz) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163248) * 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](http://www.wordpress.de)? I’d say they have this issue sorted 🙂 * Thread Starter [risach](https://wordpress.org/support/users/risach/) * (@risach) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163287) * Well, it seems ([german WP-forum](http://forum.wordpress.de/beitrag650.php&highlight=umlaute)) 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. * [Mark (podz)](https://wordpress.org/support/users/podz/) * (@podz) * [21 years, 2 months ago](https://wordpress.org/support/topic/restore-database-problem/#post-163289) * 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 Viewing 15 replies - 1 through 15 (of 22 total) 1 [2](https://wordpress.org/support/topic/restore-database-problem/page/2/?output_format=md) [→](https://wordpress.org/support/topic/restore-database-problem/page/2/?output_format=md) The topic ‘Restore Database Problem’ is closed to new replies. * In: [Installing WordPress](https://wordpress.org/support/forum/installation/) * 22 replies * 8 participants * Last reply from: [paperlion](https://wordpress.org/support/users/paperlion/) * Last activity: [20 years, 9 months ago](https://wordpress.org/support/topic/restore-database-problem/page/2/#post-163892) * Status: not resolved ## Topics ### Topics with no replies ### Non-support topics ### Resolved topics ### Unresolved topics ### All topics