• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
DB charset/collation problem

#1
[eluser]serans1[/eluser]
Hi,
i have been using CI for few weeks now and i have to say it looks good, i tried to look for an answer in the forums but did not find a solution to my problem (one that not requires hacking of the CI core).

I have a DB with non en languages all fields,tables,and DB collation are set to utf 8.
CI Version 1.71.
CI config have :
$config['charset'] = "UTF-8";

DB config have :
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

and i am getting grarbage instead of text on non english language.
the same script returns good data when connecting directly and not running through CI.
Hope you can help,
Thanks

#2
[eluser]serans1[/eluser]
I got into it and I see that the chars are being corrupted once i am connecting to the database and getting the instance (I work with more then one DB).
the line
$db=$this->load->database($config, TRUE);
makes the charset goes bad and config is:
$config['char_set'] = "utf8";
$config['dbcollat'] = "utf8_general_ci";
$config['hostname'] = "x";
$config['username'] = "x";
$config['password'] = "x";
$config['database'] = "x";
$config['dbdriver'] = "mysql";
$config['dbprefix'] = "";
$config['pconnect'] = FALSE;
$config['db_debug'] = FALSE;

#3
[eluser]serans1[/eluser]
Nailed down.
CI perform the following query :
SET NAMES 'utf8' COLLATE 'utf8_general_ci'
System collation & charset before the query are :
Array ( [Variable_name] => character_set_client [Value] => latin1 ) Array ( [Variable_name] => character_set_connection [Value] => latin1 ) Array ( [Variable_name] => character_set_database [Value] => utf8 ) Array ( [Variable_name] => character_set_filesystem [Value] => binary ) Array ( [Variable_name] => character_set_results [Value] => latin1 ) Array ( [Variable_name] => character_set_server [Value] => latin1 ) Array ( [Variable_name] => character_set_system [Value] => utf8 ) Array ( [Variable_name] => character_sets_dir [Value] => /usr/share/mysql/charsets/ )

Array ( [Variable_name] => collation_connection [Value] => latin1_swedish_ci ) Array ( [Variable_name] => collation_database [Value] => utf8_unicode_ci ) Array ( [Variable_name] => collation_server [Value] => latin1_swedish_ci )

and after :
Array ( [Variable_name] => character_set_client [Value] => utf8 ) Array ( [Variable_name] => character_set_connection [Value] => utf8 ) Array ( [Variable_name] => character_set_database [Value] => utf8 ) Array ( [Variable_name] => character_set_filesystem [Value] => binary ) Array ( [Variable_name] => character_set_results [Value] => utf8 ) Array ( [Variable_name] => character_set_server [Value] => latin1 ) Array ( [Variable_name] => character_set_system [Value] => utf8 ) Array ( [Variable_name] => character_sets_dir [Value] => /usr/share/mysql/charsets/ )

Array ( [Variable_name] => collation_connection [Value] => utf8_general_ci ) Array ( [Variable_name] => collation_database [Value] => utf8_unicode_ci ) Array ( [Variable_name] => collation_server [Value] => latin1_swedish_ci )

summary
CI sets the encoding and names by default and if you migrating to CI and have two pieces of codes one works with CI and the other w/o then you need to make sure that names and charsets are matching - it will not use the default as stated in the db settings.
to view your db(mysql) settings you can query:
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.