Character-set Problem When Connecting To Server

[frodeh]

The history-widwow shows this :

Quote:

/*[14:55:54][  10 ms]*/ show variables like '%character%'

/*[14:55:54][  40 ms]*/ Set character_set_connection=utf8

/*[14:55:54][  40 ms]*/ Set character_set_results=utf8

/*[14:55:54][  40 ms]*/ Set character_set_client=utf8

And I have to manually do :

Quote:

/*[15:01:43][  0 ms]*/ /*[14:55:54][  40 ms]*/ Set character_set_connection=latin1

/*[15:01:43][  30 ms]*/ /*[14:55:54][  40 ms]*/ Set character_set_results=latin1

/*[15:01:43][  30 ms]*/ /*[14:55:54][  40 ms]*/ Set character_set_client=latin1

in order to get my characters staight.

This has only happened on one of the servers I connect to.

When connecting locally to the database the characters seems to be ok.

I can't seem to find where in the connection-properties I can adjust this once in for all.

Is this database-related ?

[peterlaursen]

Quote:

Is this database-related ?

Yes it is!

You can read her for instance: http://dev.mysql.com/doc/refman/4.1/en/charset.html . It is similar for mySQL 4.1 and 5.0. And much different than 3.23 and 4.0!

With MySQL 4.1 and 5.0 SQLyog starts querying the server for the default character set. However ' the default character set' is only a DEFAULT character set. You might have data that don't follow defaults. For instance if data were created when you had other defaults or if they were imported from another server having other defaults.

There are default character sets (and collations) for the server, for each database, for each table and for each column! So you can have one column in Chinesse and another in English (even 😀 – believe it or not but somebody use that ridiculous language! ).

You can most easily change the default character set for the server with MySQL Administrator” from http://dev.mysql.com . From the left-hand menu it is “startup variables” .. “advanced” tab. See attached. The server must be restarted for the changes to take effect. Charsets for Databases, tables and columns are defined with a CREATE or ALTER statement.

[frodeh]

Thanks. This explains the difference from the previous database (which was 3.xx).

Too bad there was no option in SQLyog for this. Then I wouldn't have to bother the DBA with the change.

This should really be up to the client, since I then could make different connections with different users and character-sets to different databases on the same server… The default for the serve might be correct for some databases, but not for others.

[Author]

Posts