Setting the MySQL client character set

--2oS5YaxWCcQjTEyO
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

This just cost me a few hours of debugging, so I am posting it to spare
others:

The mysql client character set can be set to UTF-8 with:

$dbh->do("set character set utf8");
$dbh->do("set names 'utf8'");

That's in the manual. What is not in the manual is that the order is
crucial. If you swap them, it does not work, and you will get very
strange results.

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--2oS5YaxWCcQjTEyO
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iQDPAwUBRD1A91LjemazOuKpAQI/RQXQlXLUcdL5udCuQ6Bee4CjmknnPFXY qvCO
RQHFNZtU5CDQ/OIiomnOij6V7vWxykmbUygrgAxz9M5mrypLJ1aVX8lN0FeH krLj
GblazS08ST+TDgRfzCSlTk7Vwmg54XB42xYDJ4xEFFn3WkXucIfrh/aYT9un a6AE
D/yHXg550Hv+qsyv/HpAEs5ZxzWXTpF8ebtnu/7dd/KJAzpVPEZK2myqvgDU 9lP7
zgNmFhTz+dhy6ZlKd+BRqIH1
=sUYz
-----END PGP SIGNATURE-----

--2oS5YaxWCcQjTEyO--

Re: Setting the MySQL client character set

On 4/12/06 13:03, "Peter J. Holzer" wrote:

> This just cost me a few hours of debugging, so I am posting it to spare
> others:
>
> The mysql client character set can be set to UTF-8 with:
>
> $dbh->do("set character set utf8");
> $dbh->do("set names 'utf8'");

How does the result differ from simply using set names by itself?

> That's in the manual. What is not in the manual is that the order is
> crucial. If you swap them, it does not work, and you will get very
> strange results.
>
> hp

Re: Setting the MySQL client character set

On Wed, 12 Apr 2006 20:03:35 +0200, Peter J. Holzer wrote:

Hi Peter

> That's in the manual. What is not in the manual is that the order
> is crucial. If you swap them, it does not work, and you will get
> very strange results.

Geeeeeez. Congratulations on your great detective work!

--
Cheers
Ron Savage, ron@savage.net.au on 13/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: Setting the MySQL client character set

--aVD9QWMuhilNxW9f
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2006-04-12 13:25:49 -0500, Paul DuBois wrote:
> On 4/12/06 13:03, "Peter J. Holzer" wrote:
> > This just cost me a few hours of debugging, so I am posting it to spare
> > others:
> >=20
> > The mysql client character set can be set to UTF-8 with:
> >=20
> > $dbh->do("set character set utf8");
> > $dbh->do("set names 'utf8'");
>=20
> How does the result differ from simply using set names by itself?

None, apparently. At least for my test cases. And rereading the mysql
manual I think that you should use either of them, not both.=20

I think I can now explain the strange results (I could insert some
Non-ASCII characters, but not some others) I had:

The database I was using had a number of tables with character set
utf8, but the default character set of the database was still latin1.

The difference between set character set and set names seems to be that
set names sets the connection character set to the character set
specified, while set character set sets it to the default character set
of the database. So, with set character set utf8 in effect, mysql server
was converting all strings it got from the client first to latin1
(replacing all non-latin1 characters with question marks) and then back
to utf8 to insert them into the tables.


So I guess the real advice is:

* Either make sure your database default character set is a superset of
all the characters sets in your tables and use "set character set"=20
* or use "set names".

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--aVD9QWMuhilNxW9f
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iQDQAwUBRD4GClLjemazOuKpAQItjAXRAWqrxTnrVZLQr42djYQe9gnIY+Ot G+d4
Wb0VpwZk9/cNVJE7/QkYSIvinE+V/eEliepNXn3pw6Vudo/WEGzYV2pHKjuV 26tA
aY4Hz8LRtJ2YYl2nn21QCEQlPNm+6szHEol9TSZWKvuDFKqrE4/IVSwcqw6T OUbv
2hWk73WzXFclaUHNEr2wS0/cKXb0UQlye8mYS/uwYxaz9knTEG4Ep8/2S101 r6+L
qp41sZjlYS2GQnn0NWEtUOYYJg==
=D8qs
-----END PGP SIGNATURE-----

--aVD9QWMuhilNxW9f--