I think that all is set to utf8 but still getting question marks (???? for Ä›Å¡Å™Å¯)

klip · December 2007

I set utf8 to all possible places and I'm still getting question marks instead of some special characters (???? for Ä›Å¡Å™Å¯) when they are saved into database.

In conf/settings.php there is:

$Configuration['DATABASE_CHARACTER_ENCODING'] = 'utf8';
$Configuration['CHARSET'] = 'utf-8';

database is

mysql> show create database dforum;
+----------+---------------------------------------------------------------------------------------+
| Database | Create Database                                                                       |
+----------+---------------------------------------------------------------------------------------+
| dforum   | CREATE DATABASE `dforum` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_czech_ci */ | 
+----------+---------------------------------------------------------------------------------------+

and tables are all utf8_czech_ci collation

I made a test and saved this code into conf/language.php

mysql_query("set names utf8");
mysql_query("set character set utf8");

echo "<pre>";
printf("The current character set is: %s\n", mysql_client_encoding());
printf("MySQL client info: %s\n", mysql_get_client_info());
printf("MySQL host info: %s\n", mysql_get_host_info());
printf("MySQL protocol version: %s\n", mysql_get_proto_info());
printf("MySQL server version: %s\n", mysql_get_server_info());
echo "</pre>";
exit;

Result is:

The current character set is: latin1
MySQL client info: 5.0.22
MySQL host info: Localhost via UNIX socket
MySQL protocol version: 10
MySQL server version: 5.0.27

But it is possible that mysql_client_encoding() lies as mentioned here

This:

mysql -u dforum --default-character-set=utf8 -p dforum
mysql> status

produces


mysql  Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using readline 5.0
...
Server version:         5.0.27
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
...

Is it the Server characterset?

I already met this problem before not at Vanilla and I've never solved it. And it was on a machine with phpmyadmin installed and pma managed to connect to database without any problems and displayed all characters well. I tried to go through the code of phpmyadmin, but found nothing.
I tried to change all possible mysql server settings there to utf8 and it didn't help.
I use vanilla on my local computer and on one server without any problem, and this is my third Vanilla install I did and this one only has problems with utf

uutf?!
Am I missing something?!

Do you have any ideas?

Forum is installed at http://forum.demokracie.info/ (if you need access I created temporary test/test)

JFI some info from phpinfo():

PHP Version 5.1.4
...
default_charset	no value	no value
...
mysql
MySQL Support	enabled
Active Persistent Links 	1
Active Links 	2
Client API version 	5.0.22
MYSQL_MODULE_TYPE 	external
MYSQL_SOCKET 	/var/lib/mysql/mysql.sock
MYSQL_INCLUDE 	-I/usr/include/mysql
MYSQL_LIBS 	-L/usr/lib/mysql -lmysqlclient

Directive	Local Value	Master Value
mysql.allow_persistent	On	On
mysql.connect_timeout	60	60
mysql.default_host	no value	no value
mysql.default_password	no value	no value
mysql.default_port	no value	no value
mysql.default_socket	no value	no value
mysql.default_user	no value	no value
mysql.max_links	Unlimited	Unlimited
mysql.max_persistent	Unlimited	Unlimited
mysql.trace_mode	Off	Off

mysqli
MysqlI Support	enabled
Client API library version 	5.0.22
Client API header version 	5.0.18
MYSQLI_SOCKET 	/var/lib/mysql/mysql.sock

Directive	Local Value	Master Value
mysqli.default_host	no value	no value
mysqli.default_port	3306	3306
mysqli.default_pw	no value	no value
mysqli.default_socket	no value	no value
mysqli.default_user	no value	no value
mysqli.max_links	Unlimited	Unlimited
mysqli.reconnect	Off	Off
...

klip · December 2007

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       | 
| character_set_connection | utf8                       | 
| character_set_database   | utf8                       | 
| character_set_filesystem | binary                     | 
| character_set_results    | utf8                       | 
| character_set_server     | latin1                     | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+

it is same when running this by mysql_query and fetching the result

klip · December 2007

oh my!!!!
I forgot to alter collate table columns!!!

mysql> show full columns from LUM_Comment;
+---------------+---------------+-------------------+------+-----+---------+----------------+----------------------+---------+
| Field         | Type          | Collation         | Null | Key | Default | Extra          | Privileges           | Comment |
+---------------+---------------+-------------------+------+-----+---------+----------------+----------------------+---------+
...
| Body          | text          | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update |         | 
...
+---------------+---------------+-------------------+------+-----+---------+----------------+----------------------+---------+

after a change:

| Body          | text          | utf8_czech_ci     | YES  |     | NULL    |                | select,insert,update |         |

it works!

I spent hours on this issue and finally went to bed and I couldn't sleep and I realized that!

Max_B · December 2007

I was about writing advice on checking table/col collation.
If yours were not correct that's because you changed the database after its creation. If created with utf-8 default, all tables and columns should be ok.

klip · December 2007

thanks anyway :) Yes I changed, because it was set latin1 by hosting provider. Maybe there could be a statement in the installer about creating the database DEFAULT utf8 is the best and it saves time if it is set before the install

Max_B · December 2007

The database IS created default utf8. It is not ALTERed though if it already exists.
If you alter it before installing Vanilla, all run slick.

klip · December 2007

oh I see, I just checked the code. Still I think it would be better to give a notice to user, that if database is already created and is not created default utf8. From providers you often get access to already created database and such a notice could save time in future :-)

I think that all is set to utf8 but still getting question marks (???? for Ä›Å¡Å™Å¯)

Comments