plesk mysql databases...

[mneese]

This is a new server default installation from 1and1...plesk 9.3...the restore functions do not replace the databases...plesk forum response from them refers the latin to utf8 conversion possibility

keys in Latin1 in Joomla databases which will be incorrectly converted to UTF8,

Yet upon inspection I find that the default plesk databases:

Code: Select all

horde....latin1_swedish
mysql....latin1_swedish
phpmyadnin...latin1_swedish
psa....latin1_swedish
tortix....latin1_swedish

Whereas the new databases created for new domains through the plesk admin interface are ALL...utf8_general_ci

Is this unusual...is this a problem?
Are all my backups/restores bad because of these plesk database inconsistencies????
Should I convert all to utf8_general or leave alone?

[faris]

All our 8.6 databases are latin1_swedish by default.

I have no ideas of the implications I'm afraid.

Faris.

[Highland]

The problem you'll run into with latin character sets is that most web pages are UTF8 encoded. mysql doesn't play nice in trying to convert them so, sometimes, you'll have characters run amok when they come into the database from UTF8. It's best to have your websites and databases use the same encoding. utf8_general is faster but doesn't support international character sets. utf8_unicode will support them all but is slower in doing so.

[faris]

Hmmm.... any tips on converting en-mass, and is anything likely to go wrong in doing so?

I think the latin1_swedish is something that was used as the default in Plesk going way way back. For speed reasons, I guess?

Faris.

[breun]

I believe latin1_swedish is still MySQL's default collation, which is used when no collation is specified. At some point Plesk was probably changed to create databases using utf8_general_ci.

[biggles]

I believe latin1_swedish is still MySQL's default collation, which is used when no collation is specified. At some point Plesk was probably changed to create databases using utf8_general_ci.

The Swedish heritage I'd guess...

[Highland]

This has really only started being an issue in the last few years as 5.0 uses utf8_general as its default rather than swedish plus more and more webpages use utf8 as their default now, rather than windows-latin or ASCII. I seriously doubt Plesk or any other application actually specified a character set in their database creation.

Can you change it en masse? Somewhat. The danger is double encoding. Let's say you bring in utf8 data to latin1. Mysql will translate it into latin1 which is where the problem is in the first place. So when you change it to utf8 it now takes gobbledygook and converts it into gibberish. In other words, the cure can be worse than the problem.

There's no perfect way that I've found to do it. I'm still rolling data myself as I get time but it requires changing the collation of each field and then the table itself. It's safe about 99% of the time. You'll know it did something funky when you see the weird diamond question mark in your browser. In most of those cases it was one of those #%@& MS Word quotes that came in but it's also sometimes just a simple tick mark ( ' ).

Is it worth it? Mostly this is necessary when you see oddball characters pop into your data. I had a Canadian checkout that tipped me to this problem. YMMV. If you're not seeing any problems it doesn't hurt to stay put, just realize that new tables will be made in utf8 unless you set mysql to use latin1.

[faris]

Thanks Highland -- that's very informative and has put my mind at rest.

Faris.

[mneese]

Yes, possibly not the core issue for my problems with the plesk backups and restore failures... the plesk people threw this at me when I queried about consistent lack of restores with the databases...so, they were implying the possibility of imported databases not working or something like that...

Thanks for all the info...