Unicode problem

system · February 16, 2009, 9:07pm

Hey, guys, I have a problem.

I created a table with the following structure:

– Table structure for table news

–

CREATE TABLE news (

NEWS_ID int(11) NOT NULL auto_increment,

TITLE varchar(50) collate utf8_unicode_ci NOT NULL,

CONTENT text collate utf8_unicode_ci NOT NULL,

DATE_PUBLISHED timestamp NOT NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (NEWS_ID)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Това е таблицата с новините' AUTO_INCREMENT=2 ;

I used consecutively:

model "news"

crud "news"

Thus, I got what I needed. I have a problem, though. I wrote a text in Bulgarian, because the site I will be developing will be in Bulgarian only. I wrote something a looked up in PHpMyAdmin. The content of the fields was:

1 ÐšÐ²Ð¾ ÑÑ‚Ð°Ð²Ð° Ð¿Ð¸Ñ‡Ð¾Ð²Ðµ Ð¡ÑŠÐ´ÑŠÑ€Ð¶Ð°Ð½Ð¸ÐµÑ†Ðµ 0000-00-00 00:00:00

In fact, Yii shows them correctly in the list.php module, but they are filled in in a bad format in the database. What could be this caused by? I normally execute a query "SET NAMES utf8" after I open the connection, but is this the issue?

I don't say it is an Yii issue, but I am looking for an advice, because I haven't had such problems till now.

Penko

mocapapa · February 16, 2009, 9:58pm

Try inserting 'skip-character-set-client-handshake' sentence in the section of [mysqld] in 'my.cnf' and let me know the result.

system · February 16, 2009, 10:55pm

Yes, but I haven't had problems when I use "SET NAMES utf8". Is what you are saying an predefined equivalent of "SET NAMES…", or something else?

Here is a picture of Yii database extract: (as you see everything is OK)

Posted Image

Here is what I see in PhpMyAdmin:

Posted Image

In fact, I will say one more time. I have had this problem when I don't use "SET NAMES utf8".

Could you give me more information about your tip?

Edit: I manually edited the content field in PhpMyAdmin, so that it is “съдържание”(the word in bulgarian for “content”), but not Yii shows ?? instead of showing the proper text.

mocapapa · February 17, 2009, 8:18am

Well, sorry that I was too fast. My previous tip was for the situation where phpMyAdmin is completely OK though the characters on the screen of an application show wrongly.

Actually, I was using 'SET NAMES ujis' when my database language was EUC. On the other hand, I do not have to use set names something recently, after I had changed character coding to utf8.

What will it show when you dump this table using export function of phpMyAdmin?

system · February 17, 2009, 8:43am

No, problem, I didn't explain it very well in my first post.

Now, back to your question: It is the same when dumping:

INSERT INTO news VALUES (1, 'ÐŸÑŠÑ€Ð²Ð¸ Ð¾Ñ‚Ð³Ð¾Ð²Ð¾Ñ€', 'ÐÐµÑ‰Ð¾ Ð¸Ð½Ñ‚ÐµÑ€ÐµÑÐ½Ð¾', '0000-00-00 00:00:00');

INSERT INTO news VALUES (2, 'Ð’Ñ‚Ð¾Ñ€Ð¸ Ð¾Ñ‚Ð³Ð¾Ð²Ð¾Ñ€', 'Ð¡ÑŠÐ´ÑŠÑ€Ð¶Ð°Ð½Ð¸Ðµ', '0000-00-00 00:00:00');

Is there some Yii configuration attribute that I miss? I have several other alphabets installed on my computer, so when developing I am trying with them all, so I will try now and see what happens. But actually, this project will be in bulgarian only, so the problem remains.

Edit: I tried by typing some Arabic and Greek letters in two separate posts and here is the result from the arabic letters input:

So, basically, the problem remains with all alphabets, different from latin.

Edit 2:

I looked the yii-powered websites in the sub-section here in the forum. I saw the address: http://lepet.in-da.ru/

The website is in Russian, which uses cyrillic alphabet, like Bulgarian, so it seems there is a way… Of course, the situation is the same, my script visualizes texts properly too, so we can't be sure that the situation with this website is different.

mocapapa · February 17, 2009, 10:09am

Quote

Now, back to your question: It is the same when dumping:

INSERT INTO news VALUES (1, 'ÐŸÑŠÑ€Ð²Ð¸ Ð¾Ñ‚Ð³Ð¾Ð²Ð¾Ñ€', 'ÐÐµÑ‰Ð¾ Ð¸Ð½Ñ‚ÐµÑ€ÐµÑÐ½Ð¾', '0000-00-00 00:00:00');

INSERT INTO news VALUES (2, 'Ð’Ñ‚Ð¾Ñ€Ð¸ Ð¾Ñ‚Ð³Ð¾Ð²Ð¾Ñ€', 'Ð¡ÑŠÐ´ÑŠÑ€Ð¶Ð°Ð½Ð¸Ðµ', '0000-00-00 00:00:00');

Would you please attach a full dump data to the post by using 'mysqldump' command?

I am attaching mine,

Quote

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

:

DROP TABLE IF EXISTS Group;

CREATE TABLE Group (

id int(11) NOT NULL auto_increment,

name varchar(50) character set latin1 NOT NULL,

created datetime NOT NULL,

modified datetime NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

:

I would like to see 'SET NAMES' portion and 'DEFAULT CHARSET' portion.

Quote

Is there some Yii configuration attribute that I miss?

It may be the configuration of MySQL, not Yii.

system · February 17, 2009, 5:43pm

–

– Host: localhost Database: newoborudvane

– Server version 5.0.51b-community-nt-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0

*/;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

mysqldump: Got error: 1044: Access denied for user ''@'localhost' to database 'n

ewoborudvane' when selecting the database

Sorry man, but I was unable to dump the actual table content, as you see an error appeared. I haven't used the console, so I don't know how to login and it does not request me to do so on program startup, so it's difficult to execute the task fully. I hope what I provided is enough.

mocapapa · February 17, 2009, 9:59pm

Mmm… It shows that you do not have the user account nor password, right? If so, I do not think you can fix the problem of character encoding settings of MySQL/phpMyAdmin…

Or, if you know the user name and password, you can dump the table schema as follows.

[tt]$ mysqldump -u userName -ppassword databaseName[/tt]

As I suppose the default character set of the table may be latin1, I propose you to try to re-generate the table having default character set as utf8.

Quote

DROP TABLE IF EXISTS `xxxx`;

CREATE TABLE xxxx (

:

) DEFAULT CHARSET=utf8;

I recommend the re-generation of the table is done by mysql, not phpMyAdmin since there are some portions that you have to be more careful.

These are simple steps.

(1) $ mysqldump -u userName -ppassword databaseName >file

(2) $ edit this file to modfiy the table having 'DEFAULT CHARSET=utf8'

(3) $ mysql -u userName -ppassword databaseName < file

tri · February 17, 2009, 11:33pm

I assume you already tried 'charset'=>'utf8' in the connection string.

(I don't know if it's relevant in this case, but at least worth a try. I suspect it might make a difference in the driver, if not in the database itself. But I'm just guessing.)

/Tommy

system · February 18, 2009, 7:01am

Tommy,

thanks man, this was the issue. I thought Yii is UTF8 by default and does not support other charsets.

Mocapapa, I will anyway try another dump after school, to see whether this can be configured by default in MySQL.

Penko