Unwanted characters appearing (character encoding)

Regular Joined: Mar 26, 2006 Posts: 87 Location: UK

Whenever I backup my db then drop and upload it again, I get a "Â" (upper-case "a" with circumflex) appearing in various places. I see it before the copyright symbol in the footer and before the 'middot' at the start of all the links in the administration block.

It's not such a big deal and I get round it by manually deleting the characters from the tables they appear in, but is there anything I can do to prevent it happening at all?

My web host's MySQL/phpMyAdmin details are...

MySQL 4.1.15-standard
phpMyAdmin 2.6.3-pl1

phpMyAdmin settings are...

Language: English (en-utf-8)
MySQL charset: UTF-8 Unicode (default)
MySQL connection collation: utf8_general_ci

My Nuke site is encoded as ISO-8859-1, but there isn't the option to select this in phpMyAdmin. Also, I use BigDump to upload.

Thanks for any advice.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

That's a conflict between the 2 encodings. When you backup your db, since you say you can't select your encoding, try selecting the SQL compatibility mode of MYSQL40

Posted: Sat May 05, 2007 1:46 pm

Thanks Raven that did the trick for my table.

But I´m sure there must be also another simple way to export and import without this issue. Is there somewhere a FAQ about this ?

My data:My SQL: UTF-8 Unicode (utf8) collation: utf8_general_cgi
within the nuke tables:latin1_swedish_ci

registered · Posted: Mon May 07, 2007 7:07 pm

Susann, I have this same issue all the time and have not had time to dig closely into it with the exception of I just cannot ever seem to get phpMyAdmin to do it.

Maybe try the same phpMyAdmin version level between the source and the target and see what happens.

Posted: Mon May 07, 2007 8:24 pm

I found already the best FAQ written in German and maybe the best tool to avoid such problems. I´ve learned today a lot and I´m able to understand the reason why this happens by import.
The complete stuff is so hard to understand but I hope I reach the point of enlightenment some day to fix all character issues.

For exampel I have in my wordpress blog a preview function for comments and my blog is utf 8 everything works correctly only the preview function shows strange characters. The author did´t know a fix for this issue when I asked him some weeks ago.
However, with bigdump you can change what you need.

But the tool I found can recognise character-set-client and character-set-connection
automatically and you don´t need to think hours and days about what you need to change there to avoid this problem.

I haven´t tried this tool because I fixed my problem today using utf8 instead of latin1 for the import with bigdump. Smile

Posted: Tue May 08, 2007 6:01 am

Susann, I didn't even realize that BigDump was still active. I was stuck back on version .21b and there is a .27b! :clap:

Looks like version .23b had added charset "awareness". That is awesome!!

Thanks for telling me this...