Support ⇒ Troubleshootings :: Archives ⇒ MySQL 4.1.x-5.0.x and new utf-8 feature :: Archived ⇒ Community Forums ⇒ CPG Dragonfly™ CMS
Forum IndexTroubleshootings

Archived ⇒ MySQL 4.1.x-5.0.x and new utf-8 feature


While trying to use MySQL 4.1.x-5.0.x and its new utf-8 features, I ran into character encodings problems.
Even though the html page encoding was correctly set to utf-8 and data in the database was correctly utf-8 encoded data, the content displayed in my browser was completely wrong.
Non ascii characters were wrongly converted to ? chars instead of the correct char, revealing that MySQL was serving latin-1 instead of utf-8 as it should.
Trying to fix that by setting every default character set to utf-8 in my.conf, the MySQL configuration file, or in the database, didn't solve the problem. Changing collations, etc. didn't do it neither.
By looking at phpMyAdmin's way to solve the problem, I found this query right after they make a connection to the database:

SET NAMES 'utf8';

This statement set the character encoding for the next operations. This is a design flaw in MySQL, they used a hack instead of doing things properly from the beginning. What happens if you use a database wrapper like PEAR::DB or whatever ? You will have to test beforehand if the db in use is MySQL if you need utf-8. Sounds silly ?
So the only way I found to avoid doing $db->query("SET NAMES 'utf8'");
was to add this line to my.conf under the [mysqld] section:

init_connect="SET NAMES utf8"

This means that everytime MySQL opens a connection, it sends SET NAMES utf8. But as the init_connect directive is not taken into account for user root (argument given is that you won't be able to fix problems under root account if your init_connect directive is wrong... not taking into account that if you are able to change my.conf, you can fix them anyway), you will have to use another account to test your changes.

Lots of time wasted.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
ClarkConnect 3.2/Apache 2.0.x/MySQL 4.1.x/PHP 4.3.x/Dragonfly CVS


I have the same problems that you had (i think), where exactly did you insert the code?

Look at this thread:http://www.dragonflycms.org/Forums/viewtopic/t=13689.html

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/1.3.33/4.1.13/4.3.10/9.0.6.1


In /includes/db/mysql.php about line 39 find: $this->time += (get_microtime()-$stime); Add after on new line: mysql_query('SET NAMES utf8', $this->connect_id);

That should fix the utf8 problem Smile

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
ClarkConnect 3.2/Apache 2.0.x/MySQL 4.1.x/PHP 4.3.x/Dragonfly CVS


ehm did you all look into using mysqli ?

Also read these messages i wrote regarding SET NAMES if your db isn't utf
coppermine-gallery.net...#msg111987

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Fedora 25 / Apache 2.4.27 / MariaDB 10.1.26 / PHP 7.1.10 / Mercurial


Yes, I have tested it and with same results. Database and content is all utf8, but shows just garbage without SET NAMES.

This is on a windows server, maybe it is different on an linux server.. I don't know, but this was the only solution that work. But I set this in the my.ini file for mysql instead of adding this code to mysql.php. Both works, but not without it even when using mysqli.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
ClarkConnect 3.2/Apache 2.0.x/MySQL 4.1.x/PHP 4.3.x/Dragonfly CVS


strange since akamu has no problems and he uses MySQL 4.1.x

Anyway SET NAMES is ok if the database content is all set to the utf8 charset. Else you get unwanted conversions.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Fedora 25 / Apache 2.4.27 / MariaDB 10.1.26 / PHP 7.1.10 / Mercurial


May be he uses linux?

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
ClarkConnect 3.2/Apache 2.0.x/MySQL 4.1.x/PHP 4.3.x/Dragonfly CVS


I'm trying to get 5.0.16 on my windows machine as a second service besides the 4.0.x
Donno if it will conflict but we will see.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Fedora 25 / Apache 2.4.27 / MariaDB 10.1.26 / PHP 7.1.10 / Mercurial


ok.. Let me know how it works.. Smile

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
ClarkConnect 3.2/Apache 2.0.x/MySQL 4.1.x/PHP 4.3.x/Dragonfly CVS


Hmm works overhere create some data as: "Test" Fôrum

database
CREATE DATABASE `dragonfly` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

After install i checked the tables using
SELECT * FROM `information_schema`.`SCHEMATA`; SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='dragonfly'; SHOW CREATE TABLE `dragonfly`.`cms_admins`;
They all had the proper charset and collation.

If yours doesn't then use like
ALTER DATABASE `dragonfly` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci; ALTER TABLE `dragonfly`.`cms_admins` DEFAULT CHARSET=utf8 COLLATE utf8_general_ci; ALTER TABLE `dragonfly`.`cms_admins` CHANGE COLUMN `aid` `aid` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

my.ini
[client] port=3307 [mysql] default-character-set=utf8 [mysqld] port=3307 basedir="C:/Program Files/MySQL/MySQL Server 5.0/" datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/" default-character-set=utf8 default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=100 query_cache_size=0 table_cache=256 tmp_table_size=5M thread_cache_size=8 myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=8M key_buffer_size=8M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=212K innodb_additional_mem_pool_size=2M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=1M innodb_buffer_pool_size=8M innodb_log_file_size=10M innodb_thread_concurrency=8

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Fedora 25 / Apache 2.4.27 / MariaDB 10.1.26 / PHP 7.1.10 / Mercurial


Here is what I got with SHOW CREATE TABLE `dragonfly`.`cms_admins`;
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

If you wan't I can give you access to the server, if you want to look at it Smile Just give me a pling at YIM.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
ClarkConnect 3.2/Apache 2.0.x/MySQL 4.1.x/PHP 4.3.x/Dragonfly CVS


change utf8_swedish_ci to utf8_general_ci
Read: dev.mysql.com/doc/refm...-sets.html

utf8_swedish_ci, like other utf8 language-specific collations, is derived from utf8_unicode_ci with additional language rules. For example, in Swedish, the following relationship holds, which is not something expected by a German or French speaker:

Ü = Y < Ö

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Fedora 25 / Apache 2.4.27 / MariaDB 10.1.26 / PHP 7.1.10 / Mercurial


Not getting sure about my problem and if that's something similar: After updateing to 9.1.1 and after some updates of my provider
my page (www.germeringer.de) doesnt show the special utf-8 signs just &!? etc.
In the Forums the month and the registration date is incompletely shown.
Any Idea what to do??

Thank you!

Server Info

- Uptime:
7 Days
20 Hours
9 Minutes
- Load: 2.60 2.41 2.46
- Status
HTTP
POP
SMTP
FTP
IRC
- Apache:
- PHP: 5.2.0-7~bpo.1
- MySQL: 5.0.30
- Kernel: 2.6.19
- Dragonfly: 9.1.1

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
MySQL: 5.0.30 / PHP: 5.2.0-7~bpo.1 / Dragonfly: 9.1.1 / Kernel: 2.6.19


Here comes a message when using the shoutbox. It defenately tells me that there is something wrong with language stuff, but I don't really know what exactly this means. Thanks for any help!

CMS Warning line 75: On /Shoutblock.html While executing query "SELECT name FROM cms_shoutblock_ipblock WHERE name = 'R�M�/' LIMIT 0,1" the following error occured: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' In: /var/www/vhosts/germeringer.de/httpdocs/modules/Shoutblock/index.php on line: 93

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
MySQL: 5.0.30 / PHP: 5.2.0-7~bpo.1 / Dragonfly: 9.1.1 / Kernel: 2.6.19


Wow, I just found this thread. This may solve my problem here dragonflycms.org/Forum...19705.html I'll read the thread more carefully and see if I can apply the fix. I use Khmer Unicode and it was all OK when I was with Yahoo. When I moved to Bluehost, all my Khmer Unicode characters are displayed as a bunch of question marks.

RC heli hobby: www.rchelicoptertown.com
Khmer Community: www.angkorthom.us

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Mysql 5.1.41/php 5.2.5/CPGNuke 9.2.1

All times are UTC