Restore Database Problem

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.

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, ‘Blaa???‰a‰?a??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.

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 !

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 “https://127.0.0.1” instead of “https://auteuil.au.ohost.de”.
Even https://auteuil.au.ohost.de/wordpress/wp-login.php changes immediately/gets ‘forwarded’ to https://127.0.0.1/wordpress/wp-login.php.

Any further advice? would be great—

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

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.

Did you alter both siteurl AND the home value ?

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 … ??

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 …

I’m not entirely clear which issues need addressing ?
It’s all solveable though ??

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.

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: https://auteuil.au.ohost.de/wordpress/

Thanks for being around all day! such a bless …

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 https://www.wordpress.de ?
I’d say they have this issue sorted ??

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.

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