Recent Topics

1 Feb 26, 2007 05:09    

My b2evolution Version: 1.9.x

This isn't exhaustive but here is a how-to on getting b2evolution to display alternate languages properly. I have used these steps to help get b2evolution posting in Russian and Arabic and should work for other languages as well.

*** This information applies specifically to 1.9.x ***

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.

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


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

If the script fails to execute properly you can always run just this next piece but it will not fix all your problems, just the ones that directly involve posting.

ALTER TABLE `evo_item__prerendering` CHANGE `itpr_format` `itpr_format` enum('htmlbody','entityencoded','xml','text') CHARACTER SET binary NOT NULL;
ALTER TABLE `evo_item__prerendering` CHANGE `itpr_format` `itpr_format` enum('htmlbody','entityencoded','xml','text') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `evo_item__prerendering` CHANGE `itpr_renderers` `itpr_renderers` text CHARACTER SET binary NOT NULL;
ALTER TABLE `evo_item__prerendering` CHANGE `itpr_renderers` `itpr_renderers` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `evo_item__prerendering` CHANGE `itpr_content_prerendered` `itpr_content_prerendered` text CHARACTER SET binary NULL ;
ALTER TABLE `evo_item__prerendering` CHANGE `itpr_content_prerendered` `itpr_content_prerendered` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL ;
ALTER TABLE `evo_item__prerendering` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `evo_posts` CHANGE `post_status` `post_status` enum('published','deprecated','protected','private','draft') CHARACTER SET binary DEFAULT 'published' NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_status` `post_status` enum('published','deprecated','protected','private','draft') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'published' NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_locale` `post_locale` varchar(20) CHARACTER SET binary DEFAULT 'en-EU' NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_locale` `post_locale` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'en-EU' NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_content` `post_content` text CHARACTER SET binary NULL ;
ALTER TABLE `evo_posts` CHANGE `post_content` `post_content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL ;
ALTER TABLE `evo_posts` CHANGE `post_title` `post_title` text CHARACTER SET binary NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_title` `post_title` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_urltitle` `post_urltitle` varchar(250) CHARACTER SET binary NULL ;
ALTER TABLE `evo_posts` CHANGE `post_urltitle` `post_urltitle` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL ;
ALTER TABLE `evo_posts` CHANGE `post_url` `post_url` varchar(255) CHARACTER SET binary NULL ;
ALTER TABLE `evo_posts` CHANGE `post_url` `post_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL ;
ALTER TABLE `evo_posts` CHANGE `post_notifications_status` `post_notifications_status` enum('noreq','todo','started','finished') CHARACTER SET binary DEFAULT 'noreq' NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_notifications_status` `post_notifications_status` enum('noreq','todo','started','finished') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'noreq' NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_comment_status` `post_comment_status` enum('disabled','open','closed') CHARACTER SET binary DEFAULT 'open' NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_comment_status` `post_comment_status` enum('disabled','open','closed') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'open' NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_renderers` `post_renderers` text CHARACTER SET binary NOT NULL;
ALTER TABLE `evo_posts` CHANGE `post_renderers` `post_renderers` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `evo_posts` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

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.

2 Feb 26, 2007 05:21

Thanks smpdawg for the tip of hat, but your work should be admired by everyone who struggle to get b2evo to perfection.

3 May 18, 2007 02:59

Thanks for the tip! I tried running the script (with my info the place of all the 'YOUR's) after modifying _locals.php, but I got this error in return:

Parse error: syntax error, unexpected T_VARIABLE in /data/12/0/43/39/858854/user/879785/htdocs/b2e/blogs/install/fixcharset.php on line 12

Line 12 is:

 $sq='SHOW CREATE DATABASE `'.$db.'`;'; 

Any idea what I should do? I'm a complete newbie and don't know anyting about PHP...

Thanks in advance!

Dave

4 Jul 17, 2007 23:15

Really, really, really big thanks smpdawg!

Worked perfectly :D

5 Jul 18, 2007 02:55

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:

$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

6 Jul 18, 2007 10:17

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';

7 Oct 24, 2007 15:20

My site ( http://ralien.nytka.org ) is running b2evo 1.8.7, and I was foolish enough not to pay attention to the codepages, because most of the things I write use latin characters.

Is it safe to use this script on a 1.8.x installation?

Or should I upgrade to 1.9.x and then do the conversion?

8 Feb 21, 2008 14:57

oh really thanks,

in my case, it works only with the first step!

i modified _locales.php and ... tachán! spanish characters works correctly!

i did it with a installed version 1.10. Previosly i modified only database to utf8, but not tables (¿? maybe no need to do nothing in ddbb, tables are in default charset).


Form is loading...

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