Need an SQL expert - my collations are illegal!

**kelvyn** · 28 Apr 2007, 05:50 PM

I've been looking at some products, and somehow, somewhere, the ones which involve fractions of an inch (ie: 2¾") have become corrupted. For example:

So, I looked in the database, and it looks even weirder in there!
So, if I do this:

Code:

UPDATE products_description SET products_description =  REPLACE (products_description, 'Ãƒ?Ã‚Â', '' );

It says there's zero rows
And if I do this:

Code:

UPDATE products_description SET products_description =  REPLACE (products_description, 'Ã?Â', '' );

it comes back with

Code:

#1270 - Illegal mix of collations (latin1_general_ci,IMPLICIT), (utf8_unicode_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE) for operation 'replace'

Sigh. Any ideas?

**Chuckl** · 29 Apr 2007, 09:26 AM

Murky waters Kelvyn, my interpretation would be - it's telling you that you are sending data in an assumed character set - collation, to be stored in a table with another collation set as its' default.
i.e. your database connection has a collation setting of Latin General, but the table is UTF-8 Unicode, which means that the characters are going to be mistranslated; as you are seeing.

Any recent database upgrades, changes etc?

**kobra** · 29 Apr 2007, 08:07 PM

You can use decimals to mitigate "2.75"

**kelvyn** · 29 Apr 2007, 08:20 PM

Thanks for the replies. I have no idea how I'd change the collation - I think this is one for the SQL forums!
As for the decimals...well, I suppose we could, but they come from the suppliers like that ... all that effort to change them

**kobra** · 29 Apr 2007, 08:24 PM

all that effort to change them

You have not experienced the issues with changing the collation I guess if you think that this is alot of effort.

**Chuckl** · 29 Apr 2007, 08:58 PM

Help me here Kobra...

As far as I understand it, you should be able to set the connection collation to utf8 to match the server collation, which on the right hand side of the opening page of phpmyadmin.
Afraid I'm on shaky ground here, as it's my server and I have root access, so I don't know what you'd get with phpmyadmin on a hosted setup...

**kobra** · 30 Apr 2007, 01:58 AM

The DB is constructed with and contains your data in one collation.

Switching to another can cause unsupported characters to then default to "some" other character.

Zen Cart's DB is Latin 1...by default

which on the right hand side of the opening page of phpmyadmin

There is:
MySQL charset: UTF-8 Unicode (utf8)
MySQL connection collation: and a drop box to select the DB connection collation
and
then on a default Mysql install (as I recall) there is also this:
Character Sets and Collations > clickable and the supported/installed collations as part of the mysql install.

At least this is how we are setup - and reading about this subject put me to sleep and I do not think that there is a standard per'se