mysql - Doctrine 2.3 - text field is not saved because of special characters -

i've spent hours debug algo , noticed coming doctrine (v2.3.3). i'm using libpuzzle calculate hash of image , store hash in database. hash returned has special character in , apparently doctrine doesn't it.

this kind of string have (~550 char):

ÿ�þÿÿþ�þþÿþÿþÿþþ�þÿþÿÿÿ�ÿþþÿþÿ�ÿÿþÿþÿþþÿþþÿÿÿþÿþþþþÿþ�þþþÿ�ÿÿ�ÿÿþ�þÿþÿþÿþÿþþÿþÿÿÿþþÿþþþþÿþþþþ���ÿÿ�ÿ�þþ�þÿÿþþþÿÿþÿþþÿþþþÿþ...

i've investigated , found people saying add charset in config have it:

# doctrine configuration doctrine:     dbal:         driver:   "%database_driver%"         host:     "%database_host%"         port:     "%database_port%"         dbname:   "%database_name%"         user:     "%database_user%"         password: "%database_password%"         charset:  utf8 

if change collation of column utf8_unicode_ci utf8_general_ci still not working. i've made sure default schema collation utf8_general_ci.

i've tried string special characters:

test !§$%&/()=? äöü ÄÖÜ :d

this string inserted correctly hash still not.

does know fix issue? doctrine bug?

----------------------------------

update

just before execute still have correct data binded. guess it's encoding or driver issue. i've modified symfony configuration:

# doctrine configuration doctrine:     dbal:         driver:   "%database_driver%"         host:     "%database_host%"         port:     "%database_port%"         dbname:   "%database_name%"         user:     "%database_user%"         password: "%database_password%"         charset:  utf8         options:             1002: "set names 'utf8'" 

i've tried execute query without using entities sure:

$this->db = $this->getcontainer()->get('doctrine')->getconnection();  $img = '/var/www/acme/web/upload/tmp/cd1fa593cf6feb2cde83e68f461a2d947.jpg'; $hash = puzzle_fill_cvec_from_file($img);  $sql = "update image set hash=? id=?"; $stmt = $this->db->prepare($sql);  $stmt->execute(array($hash, 180)); 

still empty data in database. in project i've used zend_db , didn't have trouble saving hash. don't know if it's bug in doctrine or not :(

----------------------------------

update 2

i've logged queries in mysql , can see in log content binded correctly. not saved mysql.

insert image (guid, type, createtime, updatetime, images, imagesize, imagehash, status)  values ( 'c30df23d6b0b08aff079287e00f21ec8a',  'image',  '2013-04-22 03:30:33',  '2013-04-22 03:30:34', 'path/image.jpg',  '165458',  '?\0????\0?????????\0??????\0??????\0??????????????????????\0????\0??\0???\0???????????????????????????\0\0\0??\0?\0??\0??????????????????\0\0\0??\0?\0???????????????\0?????????????????\0?\0????????????\0?\0??????????????????????????\0?\0???????????\0???\0?????????\0??????\0\0?????????????????????\0\0??????\0???????????????\0',  1) 

this create table:

delimiter $$ create table `image` (   `id` bigint(20) not null auto_increment,   `guid` varchar(255) collate utf8_unicode_ci not null,   `type` varchar(255) collate utf8_unicode_ci not null,   `createtime` datetime not null,   `updatetime` datetime default null,   `images` varchar(255) collate utf8_unicode_ci default null,   `imagesize` bigint(20) default null,   `imagehash` longtext collate utf8_unicode_ci,   `status` integer(10) not null,   primary key (`id`) ) engine=innodb default charset=utf8 collate=utf8_unicode_ci$$ 

cheers, maxime

you should not use imagehash longtext collate utf8_unicode_ci, because rdbm try map data charset, not match if hash data libpuzzle binary output.

try alter schema make imagehash column blob.

as stated here: http://dev.mysql.com/doc/refman/5.0/en/blob.html

blob values treated binary strings (byte strings). have no character set, , sorting , comparison based on numeric values of bytes in column values.

text values treated nonbinary strings (character strings). have character set, , values sorted , compared based on collation of character set.