Unicode problem

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

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

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.

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?

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:

3  ـبشسبشسب شسبنتم شبتشمصضث    شسبشسب شسبشسبشسب لشسلشسلشس…  0000-00-00 00:00:00

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.

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.

– 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.

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

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

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