Strange chars in DB

[Dawn]

Hi,

I'm using serendipity since a longer time. In the beginning I used MySQL 4.x and upgraded then to MySQL 5.x. The default charset was in the beginning "latin_swedish". Then I switched to UTF-8.

Here my MySQL variables:
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection utf8_general_ci
collation_database utf8_general_ci
collation_server utf8_general_ci

That look correct. In serendipity I use UTF-8 for displaying chars too. So everything is right as it is...

But when I login via PhpMyAdmin to my database I see strange chars which replacing the umlauts. The word "über" is in the database stored as "über". But its displayed correctly when I view my blog in the browser.

So can anybody explain me what is wrong or what can I do to fix that?

Greets and best regards,
Dawn

[garvinhicking]

Hi!

There's nothing wrong.

Did you check that your phpMyAdmin is using UTF-8 as connection charset?

When upgrading from MySQL 4 to 5, no native UTf-8 was created, but ISO-data simply now interpreted as UTF-8. So you can't actually change it easily so that it displays correctly in all environments, because s9y stores UTF-8 data in a ISO-8859-1 table for you. That's no problem, as long as proper charset connections are issued.

Best regards,
Garvin

[Dawn]

When I enter PMA it displays:
mysql charset: UTF-8 Unicode (utf8)
charset/ coalition of connection: utf8_unicode_ci

You said serendipity uses ISO-8859-1? When I look into serendipity_entries for example, it uses for all text and varchar fields as coalition utf8_unicode_ci.

Do I understand you in the right way that serendipity uses UTF8 but stores it in the table (UTF8 too) as ISO-8859-1 And there is no way to store the data in UTF8?

[garvinhicking]

Hi!

When I enter PMA it displays:
mysql charset: UTF-8 Unicode (utf8)
charset/ coalition of connection: utf8_unicode_ci

That looks right to me then.

You said serendipity uses ISO-8859-1? When I look into serendipity_entries for example, it uses for all text and varchar fields as coalition utf8_unicode_ci.

I only said so because I figured you installed s9y with ISO-support at first.

s9y uses whatever you configure for storage. When you change it in s9y's config, it does not change older entries, the change will only affect string storage of new entries.

How s9y store entries also depends on if you enalbe the option "Use dbNames command" in s9y config. You can configure MySQL and s9y so that it store UTF-8 in UTF-8 tables, yes.

In your case, it could be that the command is not set and thus the UTF-8 is encoded in UTF-8 twice.

Regards,
Garvin

[Dawn]

Thanks for your explanation

I saw "Use dbNames command" is disabled as you said. Do you see a easy possibility to convert the existing data to real UTF-8 which I can see and edit via phpMyAdmin too (and activate "Use dbNames command" then)?

Greets and Thanks for your great Support,
Dawn

[garvinhicking]

Hi!

Ah, that's true, I forgot to mention that when you enable this option, you will get broken chars in the s9y output. But new entries you create like this should show up properly in phpMyAdmin.

What you CAN do is to enable the option, make a SQL dump via phpMyAdmin, use a tool to convert double UTF-8 encodings to UTF-8 single encodings (that might prove complicated) and then re-import the SQL dump.

But this is all so complicated that I would leave it at the current situation and live with "broken" chars within phpMyAdmin

Regards,
Garvin

[Dawn]

Can you suggest me a tool which can handle this?

It is perhaps a little bit perfectionistic, but I want my data cleaned up and don't show only the right ouput....

Greets and THX again,
Dawn

[garvinhicking]

Hi!

Can you suggest me a tool which can handle this?

There's no tool for this, you need to use a proper Editor. VI, VIM can do that for you. I personally use UltraEdit for Windows to do character recoding.

It is perhaps a little bit perfectionistic, but I want my data cleaned up and don't show only the right ouput....

Remember that you can break all of your data, if you don't have the expertise to perform the required operations...

Best regards,
Garvin

[Dawn]

Remember that you can break all of your data, if you don't have the expertise to perform the required operations...

I own UltraEdit too, but where do you see the difficulties? What steps would you suggest me to do...?

[garvinhicking]

Hi!

I can't give preceise steps, because it very much depends on exact server settings, charset settings, mysql version, s9y entries, used browsed, used operating system.

But you need to make sure that after you upload the final SQL dump, all is in UTF-8 with UTF-8 collations, and no ISO-characters or double-UTF-8s remain.

Best regards,
Garvin

[Dawn]

I only meant the steps in UltraEdit... I'm using Debian with Mysql 5.x (UTF 8, UFT 8 Coallition) and the dump is saved as UTF 8 too... (with MysqlDumper)

I wouldn't see any problems with an ISO document which I want to convert to UTF8 but I don't have any idea to convert double-UT8s...

[garvinhicking]

Hi!

Ah, okay. Double-UTF-8 encoding can'T be fixed by Ultraedit (or at least I didn'T know how to use it).

I wrote a php script that used file_get_contents, then used utf8_decode() once (or was it twice) and then re-saved the file. Sadly I don't have that small script no longer here

Regards,
Garvin