Loading...
 
Architecture / Installation

Architecture / Installation

Forum List Topic List

Forums » Architecture / Installation » Character problems with Tiki 1.9.2 and MySQL 4.1.14
 
Thread actions
PDF Print this page
  • Print all pages

    • Character problems with Tiki 1.9.2 and MySQL 4.1.14

    Posted by Gary Cunningham-Lee posts: 4656 Japan

    I installed Tiki 1.9.2 on a NetBSD server running PHP5.05 and MySQL 4.1.14/InnoDB engine. Tiki basically runs fine, but I'm trying to do a bilingual site (Japanese/English), and most Japanese characters come back from the database as garbage. (In other Tiki installations, running on older MySQL versions, Japanese displays fine.)

    I googled around a bit and found that many web scripts using UTF-8 are having problems with this MySQL version. The error message I got trying to save a Tiki article is typical of the problem other people are having with other scripts: 

    An error occured in a database query!

Context:	
File	/tiki-edit_article.php	
Url	/tiki-edit_article.php	
Query:	
select max(`articleId`) from `tiki_articles` where `created` = ? and
`title`=? and `hash`=?	
Values:	
0	1131412017	
1	PowerPC ????? PowerPC ???????	
2	f7e76cace8884bf12d8b82868fa21f9d	
Message:	
Error Message	Illegal mix of collations (utf8_general_ci,IMPLICIT)
and (latin1_swedish_ci,COERCIBLE) for operation '='

    This illegal mix of collations seems to be the main problem. Most operations don't produce errors, but the displaying Japanese text is totally mucked up. Any ideas about the solution? I'm going to try to find what other people have done; what I've come across so far seems to point to major tweaking of the various character set settings throughout the server. This will probably be a growing problem for Tiki users as servers get upgraded.

    -- Gary

    Reads: 890
    Link
    Posted by sylvie greverend posts: 1092

    Did you create your database with some collations?
    My 2cents of personal experience .. didn't get further to understand.
    I got some trouble too when playing with collation.. I recreated the database with no collation and it was back....
    sylvie

    Link
    Posted by Gary Cunningham-Lee posts: 4656 Japan

    > Did you create your database with some collations?
    > My 2cents of personal experience .. didn't get further to understand.
    > I got some trouble too when playing with collation.. I recreated the database with no collation and it was back....
    > sylvie

    The database was created by another guy who manages the server, but it was probably done with the default settings. I notified him about this problem and asked him to re-do it for utf8 compatibility. I mentioned that character set and collation need to be checked for MySQL server and database as well as on table, and column levels.

    Thanks for your reply. This Tiki installation is on pretty recent server software versions and it's good that it can cope with it, albeit with some quirks along the way.

    -- Gary

    Link
    Posted by Gary Cunningham-Lee posts: 4656 Japan

    > Did you create your database with some collations?
    >....

    I finally got the database configuration changed, now with MySQL charset: UTF-8 Unicode (utf8) and MySQL connection collation: utf8_general_ci. The Tiki database is set to utf8_general_ci and so are the tables and columns where necessary.

    With phpMyAdmin, I can insert and retrieve Japanese text with no problem, but in Tiki pages all non-English text is ?????'s.

    (When submitting an article edit, I got an error like this: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='.

    Maybe the problem is the connection. I noticed phpMyAdmin appends URLs like this: "...php?lang=en-utf-8&server=1&collation_connection=utf8_general_ci", explicitly specifying the language each time.

    I read at the MySQL forum that the application can have lines added after the db connection to set the characters. But I searched through Tiki's files for "SET NAMES utf8;" and "SET CHARACTER_SET utf8;" and nothing was found, so I guess Tiki doesn't set the character set with each db connection. Is this possible to do? Can someone tell me where these statements should go? I'll also ask the server admin to try from his side to set the connection language, as that would be more efficient, I would guess. Thanks.

    -- Gary

    Link