Re: Character Set Issues with MySQL 5.0.17?  A

[Robert Cluett]

I believe I may have solved my own problem here. By pieceing together the random fixes that were out there I compiled a procedure on how I fixed my own. Here it is:

MySQL Character Set and Collation Issues:

Problem Description:

When the privilege tables were rebuilt odd characters such as        appeared within the mysql fields of type text. The deleting and save of the content through a php script for that text field did not remove the characters. The original mysql character and collation settings used were unknown and not documented although it’s assumed that they were never changed since the original install.

Resolution:

Set all MySQL character variables back to their assumed default (try these since it worked for me and I don’t recall ever changing these settings). To make sure they are set perform the below query via the CLI of the mysql client on the server (it should match this output):

mysql> show variables like '%character%';
| character_set_client | latin1
| character_set_connection | latin1
| character_set_database | latin1
| character_set_results | latin1
| character_set_server | latin1
| character_set_system | utf8
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/
7 rows in set (0.00 sec)

Either modify each PHP script to contain the below META TAG at the top of each script (this is the long and tedious way of doing things):

<META http-equiv="Content-Type" content="text/html; charset=utf-8">

OR set the apache.conf files to default to utf-8 for each generated page/script with the parameters (this would override the above if both were to exist):

AddCharset UTF-8 .utf8
AddDefaultCharset UTF-8

(you must perform a restart of the httpd deamon to take effect)

I also tried to force php via the php.ini to default to utf-8, but this made no difference. The apache.conf change is what made the difference.

default_mimetype = "text/html"
;default_charset = "iso-8859-1"
default_charset = "utf-8"

PHPMyadmin should dictate the following:

Language: English (en-utf-8)
MySQL charset: UTF08 Unicode (utf-8)
MySQL connection collation: latin1_swedish

and tables collation: latin1_swedish_ci
and fields collation: latin1_swedish_ci

This now works when I added text for type field through a script.. The page displays it fine, the database contains no odd characters and when I attempt to pull it up and edit the same content it looks ok. However, after saving it after this edit the content in the database still contained added       characters.

To fix this portion of the problem you need to add the following query to your php script before your insert or update of the data into the database (this made an immediate impact and corrected the issue):

$query = "SET NAMES 'utf8';";
mysql_query($query) or die(mysql_error());

This corrected all of the issues. The data in the database no longer contains any  characters or other odd characters. I do however have to go back and edit the existing content and ensure that my scripts contain the above parameters. Below is the my.cnf file proving that everything is defaulting in regards to the character sets.

[mysqld]
log-bin
server-id=1
query_cache_type = 1
query_cache_size = 26214400
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2

This will only solve your problem going forward. It will not clean up what already has the odd characters in it unless you manually edit them. There are a few other posts which describe how to clean up the existing data, but in my situation I don't need to do that since the amount of existing content is minimal.

What is Character Set Encoding?

This is the character set the data is stored in. The default character set for Mysql is ‘latin1’ however I am now under the assumption that I compiled MySQL to use the default of UTF-8. I don’t believe this was a parameter I can change after the fact but I don't know.

What is Character Set Collation?

The default server collation is ‘latin1_swedish_ci’ and this still exists as the default collation for our installation




Edited 1 time(s). Last edit at 06/28/2006 11:05AM by Robert Cluett.