Recent Topics

HOW-TO: Configure b2evolution for non-English posts

Started by on Nov 22, 2007 – Contents updated: Nov 22, 2007

Nov 22, 2007 18:40    

This is an adaptation from the post [url=http://forums.b2evolution.net/viewtopic.php?t=10992&start=0&postdays=0&postorder=asc&highlight=][1.9.x] HOW-TO: Configure b2evolution for non-English posts[/url] by smpdawg.

This manual applies to every B2evo version.

-----
Typical cause:
The primary cause of foreign language posting problems is caused by bad character set and collation settings in your database. A typical install of MySQL tends to default to Latin1 character set which does not support non-English languages well. When MySQL sees text that is not in its character set it tries to translate it from the original character set to Latin1 and the result is posts that are full of ???????.

Step One
The first step is to fix your /conf/_locales.php. In this example all of the comments have been removed and I have set my default locale to English/US. This will work for you regardless of your actual locale because the real problem we are addressing is character set translation.

Some of these settings may require reasonably current installations of MySQL which you should already have.

Modified section of _locales.php.

Code

$use_l10n = 2;
$evo_charset = '';
$force_io_charset_if_accepted = 'utf-8';
$db_config['connection_charset'] = 'utf8';
$default_locale = 'en-US';

Now that your _locales.php is configured we have two choices. Fix an existing site or start over.

Start over/a new installation.

Drop your database and create a one. Don't neglect this step because it will come back to haunt you. With the new _locales.php in place you will get a database this is capable of displaying posts in other languages.

An existing installation.

Copy this file and change the items marked as YOUR, they are your MySQL user name, password and database. Once you have verified your settings copy it to your server. I didn't write this code, just modified it a little.

Code

<?php
$conn = mysql_connect("localhost", "YOURDBUSER", "YOURDBPASSWORD");
 
$printonly=true; //change this to false to alter on the fly
$charset="utf8";
$collate="utf8_unicode_ci";
$altertablecharset=true;
$alterdatabasecharser=true;
 
function PMA_getDbCollation($db)
{
$sq='SHOW CREATE DATABASE `'.$db.'`;';
$res = mysql_query($sq);
if(!$res) echo "\n\n".$sq."\n".mysql_error()."\n\n"; else
if($row = mysql_fetch_assoc($res))
{
  $tokenized = @explode(' ', $row[1]);
  unset($row, $res, $sql_query);
  for ($i = 1; $i + 3 < count($tokenized); $i++)
  {
   if ($tokenized[$i] == 'DEFAULT' && $tokenized[$i + 1] == 'CHARACTER' && $tokenized[$i + 2] == 'SET')
   {
    if (isset($tokenized[$i + 5]) && $tokenized[$i + 4] == 'COLLATE')
    {
     return array($tokenized [$i + 3],$tokenized[$i + 5]); // We found the collation!
    }
    else
    {
     return array($tokenized [$i + 3]);
    }
   }
  }
}
return '';
}
 
?>
<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=windows-1251<? //remember to change it if needed ?>" />
<xmp>
<?php
 
$rs2 = mysql_query("SHOW DATABASES LIKE 'YOURDBTOFIX'");
 
if(!$rs2) echo "\n\n".$sq."\n".mysql_error()."\n\n"; else
while ($data2 = mysql_fetch_row($rs2))
{
$db=$data2[0];
$db_cha=PMA_getDbCollation($db);
if ( $db!='mysql' /* &&( $db=='mydb_x' || $db=='mydb_y' ) */ ) // limit to database(s) - $db=='mydb_x' || $db=='mydb_y' || $db=='mydb_z'
if ( @substr($db_cha[0],0,4)!='utf8' ) // limit to charset
{
mysql_select_db($db);
$rs = mysql_query("SHOW TABLES");
if(!$rs) echo "\n\n".$sq."\n".mysql_error()."\n\n"; else
while ($data = mysql_fetch_row($rs))
{
$rs1 = mysql_query("show FULL columns from $data[0]");
if(!$rs1) echo "\n\n".$sq."\n".mysql_error()."\n\n"; else
while ($data1 = mysql_fetch_assoc($rs1))
{
  if(in_array(array_shift(split("\\(",$data1['Type'],2)),array(
//'national char',
//'nchar',
//'national varchar',
//'nvarchar',
'char',
'varchar',
'tinytext',
'text',
'mediumtext',
'longtext',
'enum',
'set'
  )))
  {
   if(substr($data1['Collation'],0,4)!='utf8') // limit to charset
   {
    $sq="ALTER TABLE `$data[0]` CHANGE `".$data1['Field'].'` `'.$data1['Field'].'` '.$data1['Type'].' CHARACTER SET binary '.($data1['Default']==''?'':($data1['Default']=='NULL'?' DEFAULT NULL':' DEFAULT \''.mysql_escape_string($data1['Default']).'\'')).($data1['Null']=='YES'?' NULL ':' NOT NULL').';';
    if(!$printonly&&!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
    else
    {
     echo ($sq."\n") ;
     $sq="ALTER TABLE `$data[0]` CHANGE `".$data1['Field'].'` `'.$data1['Field'].'` '.$data1['Type']." CHARACTER SET $charset ".($collate==''?'':"COLLATE $collate").($data1['Default']==''?'':($data1['Default']=='NULL'?' DEFAULT NULL':' DEFAULT \''.mysql_escape_string($data1['Default']).'\'')).($data1['Null']=='YES'?' NULL ':' NOT NULL').($data1['Comment']==''?'':' COMMENT \''.mysql_escape_string($data1['Comment']).'\'').';';
     if(!$printonly&&!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
     else echo ($sq."\n") ;
    }
   }
  }
}
if($altertablecharset)
{
  /*
  $sq='ALTER TABLE `'.$data[0]."` DEFAULT CHARACTER SET binary";
  echo ($sq."\n") ;
  if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
  */
  $sq='ALTER TABLE `'.$data[0]."` DEFAULT CHARACTER SET $charset ".($collate==''?'':"COLLATE $collate");
  echo ($sq.";\n") ;
    if(!$printonly)
  if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
}
}
if($alterdatabasecharser)
{
  /*
  $sq='ALTER DATABASE `'.$data2[0]."` DEFAULT CHARACTER SET binary";
  echo ($sq."\n") ;
  if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
  */
  $sq='ALTER DATABASE `'.$data2[0]."` DEFAULT CHARACTER SET $charset ".($collate==''?'':"COLLATE $collate;");
  echo ($sq."\n") ;
    if(!$printonly)
  if(!mysql_query($sq)) echo "\n\n".$sq."\n".mysql_error()."\n\n";
}
}
}
?>
</xmp>

Run the script. In my example it is http://localhost/install/fixcharset.php

This script will take some time to execute and may even time out if your install of PHP have a short script execution time. When it is finished it will generate a list of ALTER statements that can be used to fix your existing database. All you need to do is copy these commands into the SQL window of phpMyAdmin and execute them.

If you don't have phpMyAdmin then you can change the line $printonly = true; to $printonly = false; and copy the script to your server and run it again. This will run the ALTER statements against your database.

Notes:
1. While this has worked for me and other people, you may encounter a problem that I have not anticipated. If so, please ask and I will see if I can help.
2. Any post that was scrambled before the change will still be scrambled because MySQL did a charset conversion when the post was saved.
3. You must at the very least fix the posts table and the pre-rendering table because it is ultimately used to display posts.
4. Failure to address the other tables will cause odd displaying of comments, categories, etc. so please don't skip a step.
5. If this doesn't address all the problems, I do have a hack to one file but it may not be necessary so start with this list.

A tip of the hat goes to kskhater. He suggested that I share these tips since this seems to be a problem for a few people. If he hadn't suggested it I would have probably filed this away in the back of my mind and forgotten what I had done to get this working.

-----

lotek replied:
lotek wrote:

smpdawg's post helped alot but there were some things that kept me back anyways from being able to post in Japanese:

unfortunately it has been awhile since I troubleshooted and I didn't write down exactly what I did, but basically I needed to change a couple more things for the encoding to work.

-in phpMyAdmin, go to databases -> (yourdatabase) -> operations -> and check "Collation". I set mine to "utf8_unicode_ci"

-some servers treat the actual spelling of utf8 differently, so make sure you're using the one that works in all your files! (UTF-8, utf8, utf-8, etc...) For example, in 'conf/_locales.php' I added 'utf-8' to the following lines:

Code

$force_io_charset_if_accepted = 'utf-8';
$db_config['connection_charset'] = 'utf-8';

But I originally had those down as just 'utf8' and was pulling my hair out forever until I read about the spelling issue somewhere on this board.

Result: no more question marks or garbage characters!

So thanks, smpdawg, and for those still having trouble, try what I suggested. I hope it works! b2e is a great and I was thrilled to finally get it working! -> http://www.en-now.com/blog

And finally freemink added to this discussion:
freemink wrote:

Thanks lotek,

I must have got lucky as my database 'collation' was already set to "utf8_unicode_ci" and smpdawg's:
$force_io_charset_if_accepted = 'utf-8';
$db_config['connection_charset'] = 'utf8';

seems to be working:
http://puranto.com/minimum

Please go for questions to the original topic.

Good luck

Nov 22, 2007 19:33

I'm so glad I only speak english .... and babbleish, but it's a similar charset ;)

¥

Dec 01, 2007 23:01

If this suggestion is still not working, I refer to this post: [url=http://forums.b2evolution.net/viewtopic.php?t=6470&start=0&postdays=0&postorder=asc&highlight=]UTF-8 problem restoring from backup[/url].

The idea of that solution is basically this:
- export the database in the current encoding
- remove SET NAMES from the dump
- replace all mentions of the old codepage in the dump with utf8 and utf8_general_ci
- drop the database from the server and create a new one with UTF-8 charset
- import the dump back into the newly created database with a different default encoding (UTF-8)

As mentioned before, this is a last resort action.

Thanks to CaptSolo

May 02, 2008 23:20

On a 242 installation where I can see via phpmyadmin that everything seems to be "latin1_swedish_ci" for collation. Specifically: selecting a database then operations shows latin1_swedish_ci as the selected value in the Collation box at the bottom, and, selecting Structure shows latin1_swedish_ci in the column for "Collation" for each table, and, selecting any table shows latin1_swedish_ci in the column for "Collation" if it shows anything at all for each field in the table.

So I run the script in the initial post and get a blank screen. View source shows me the header and <xmp></xmp> on two lines (of course).

Am I doing something wrong, or is there something I'm missing, or does this not work in the 242 world?

BTW I duplicated the problem with my WAMP database even after changing a couple of the if conditionals that determine if anything will be echoed to "if(true) {" and still I see a blank page.

I will tinker some more but would like to make sure that I am properly set up to handle non-English characters in order to make a functional plugins translation support plugin.

May 02, 2008 23:40

You need to replace "localhost", "YOURDBUSER", "YOURDBPASSWORD" and somewhere half way the file 'YOURDBTOFIX'

This is not a B2evo issue. Version play no part in this.

Good luck

May 02, 2008 23:49

The first few were a given. Didn't notice the YOURDBTOFIX bit though. Reckon I should have been asking myself "how can it possibly do something with a database when I haven't told it the name of the database to do something with" eh? Cool - will try again.

Also, unrelated, gonna see about an easy way to say "just copy it from conf/_basic_config.php into here" when we need to hack up an external file that needs connection info.

EDIT: turns out doing it via the new database method was easier - especially since the new translator blog doesn't really exist yet. Still I think I want to go back and upgrade my existing installations just because...


Form is loading...

powered by b2evolution – This forum is powered by b2evolution CMS, a complete engine for your website.