By neuronq on

So this is my problem: I have website with content translation enabled, the adequate languages enable, but when I create a piece of content in a language other that english, the some foreign character get replaced by "?". The form has accept-charset="UTF-8" and I'm sure it's not a font problem. Has this happened to anyone else? How can I fix it quickly?

--
Thanks in advance

Comments

bakr’s picture

bakr commented

I think your database collation is set for western latin, try changing the collation of the database as well as the tables into utf8_bin

This script helps:
save this script as dbconv.php into you web root folder, and call it from the http ...

=================
// Script written by Vladislav "FractalizeR" Rastrusny
// http://www.fractalizer.ru

//MySQL connection settings
$db_server = 'localhost';
$db_user="root";
$db_password="";

mysql_connect($db_server, $db_user, $db_password) or die(mysql_error());

//Put here a list of databases you need to change charset at or leave array empty to change all existing
$dblist=array();

//If changing at all databases, which databases to skip? information_schema is mysql system databse and no need to change charset on it.
$skip_db_list = array('information_schema', 'mysql');

//Which charset to convert to?
$charset="utf8";

//Which collation to convert to?
$collation="utf8_bin";

//Only print queries without execution?
$printonly=false;

//Getting database names if they are not specified
$skip_db_text = '"'.implode('", "', $skip_db_list).'"';
if(count($dblist)<1) {
$sql="SELECT GROUP_CONCAT(`SCHEMA_NAME` SEPARATOR ',') AS FRST FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` NOT IN ($skip_db_text)";
$result = mysql_query($sql) or die(mysql_error());
$data = mysql_fetch_assoc ($result);
$dblist=explode(",", $data["FRST"]);
}

//Iterating databases
foreach ($dblist as $dbname) {
$sql="SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET $charset COLLATE $collation;') as FRST FROM `information_schema`.`TABLES` t WHERE t.`TABLE_SCHEMA` = '$dbname' ORDER BY 1";

$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
echo $row["FRST"]."\r\n";
if(!$printonly) {
mysql_query($row["FRST"]) or die(mysql_error());
}
}
}
=================

Procedure,
1) Backup you site (using phpmyadmin)
2) run the script
3) try submitting your forms... and see the difference. (should work)

It works fine for me.

Regards

bakr’s picture

bakr commented

Suggestions:

This could be included with drupal core...

Also, do not forget that you can change some parameters and adapt as required.

neuronq’s picture

neuronq commented

well... as far as I can see, the database collation for some tables is utf8_general_ci and for others latin1_swedish_ci... so how can I know what tables should be in utf8 and are probably not?

...guess the script you wrote might fix my problem, but I hope to at least a bit understand what's going wrong and why.

bakr’s picture

bakr commented

I am using Mixed Arabic and English site.

I have these players in the playground:

* The actual webpage (should be encoded in utf8, as well as including proper unicode signature BOM)
* The web browser should display the page properly, today browsers will detect the proper encoding to display, as utf becomes more the norm.
* The middleware should support utf, php now does, should be no problem.
* The database should be properly configured for UTF at several levels:
- Client Connector Configuratoin.
- Server Connector Configuration.
- Database Default Collation
- Sub Tables each individual collation (Yes you can have different collations for different tables under the same DB)

The bad news: Not every collation which starts with "UTF" will do the magic of clean internationalization effect for you.(I know, strange assumption)

The good news: If you use utf8_bin all accross you should surf at universal binary, and should not have problem with any script.

Also, do not worry to convert any latin script into utf, as you should face any problem. as the utf encompasses latin, but not vice versa.

The surprise is that any misconfiguration accross these presented layers will cause problem.

From my observation and experience, I tend to find users including some programmer not well versed into collations and their combinations.

In earlier drupal versions, I used to edit the source code and amend a default collation statement such as all my tables by default shall be utf8_bin.

It worked nicely for me.

Cheers.