Developer

Could you provide an example unreadable string from your database?

Here it is

Ïðèâåò! ß òóò âïåðâûå

The most common case of that problem is having data in DB already encoded in utf8, but named as other charset. In this case you can just make a copy of your database backup, open it in any editor more powerful than notepad, replace string "YOUR_CURRENT_CHARSET" with "UTF8" and then upload it back. After that just run this script again to fix collations where needed and that's all...

SHOW VARIABLES LIKE 'character_set_database';
returns this on old database

Variable_name Value
character_set_database latin1

Database collation is set to latin1_swedish_ci
All tables had collation set to latin1_swedish_ci

SHOW VARIABLES LIKE 'character_set_database';
The new DB returns
Variable_name Value
character_set_database utf8

Database collation is set to utf8_unicode_ci
All tables have collation set to utf8_general_ci

I have also tried to add these instructions to .htaccess as mentioned as per official instructions

AddDefaultCharset utf-8

AddCharset utf-8 *

<IfModule mod_charset.c>

CharsetSourceEnc utf-8

CharsetDefault utf-8

</IfModule>

By the way old database has had 148 tables and the converted one has 180.

I guess that some data has been in UTF-8 in DB, but I am not sure about it. How may I check it?
The problem with downloading,editing and uploading back is that the DB is 1.5 GB in size and I can only manipulate it on my server via SSH or similar way without downloading it.

Also please note that my MySQL default charset is set to UTF-8 Unicode (utf8) and default MySQL connection collation is set to utf_unicode_ci

ejakabo, greetz from [url=http://forum.ibresource.ru]russian ipboard community[/url], we have exactly what you need =)

[size="4"]1) For average sized forums - database conversion script based on PHP:[/size]

<?php

// Database info


include("conf_global.php");


$dbhost = $INFO['sql_host'];

$dbuser = $INFO['sql_user'];

$dbpass = $INFO['sql_pass'];

$dbname = $INFO['sql_database'];


//---------------


header('Content-type: text/plain');


$dbconn = mysql_connect($dbhost, $dbuser, $dbpass) or die( mysql_error() );

$db = mysql_select_db($dbname) or die( mysql_error() );


$sql = "ALTER DATABASE `".$dbname."` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";

$result = mysql_query($sql) or die( mysql_error() );

print "Database changed to UTF-8.\n";


$sql = 'SHOW TABLES';

$result = mysql_query($sql) or die( mysql_error() );


while ( $row = mysql_fetch_row($result) )

{

$table = mysql_real_escape_string($row[0]);

$sql = "ALTER TABLE $table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci, CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";

mysql_query($sql) or die( mysql_error() );

print "$table changed to UTF-8.\n";

}


mysql_close($dbconn);

?>

Upload this script as "any_name_you_like.php" in forum's root folder and execute. Be careful with large databases - be sure to correctly configure max_execution_time limit. 2) For large forums - 2 step conversion: Step 1:

<?php

// Database info


include("conf_global.php");


$dbhost = $INFO['sql_host'];

$dbuser = $INFO['sql_user'];

$dbpass = $INFO['sql_pass'];

$dbname = $INFO['sql_database'];


//---------------


header('Content-type: text/plain');


$dbconn = mysql_connect($dbhost, $dbuser, $dbpass) or die( mysql_error() );

$db = mysql_select_db($dbname) or die( mysql_error() );


$sql = "ALTER DATABASE `".$dbname."` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;\n";


$exec_sql = 'SHOW TABLES';

$result = mysql_query($exec_sql) or die( mysql_error() );


while ( $row = mysql_fetch_row($result) )

{

$table = mysql_real_escape_string($row[0]);

$sql .= "ALTER TABLE `".$table."` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci, CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;\n";

}

mysql_close($dbconn);


echo $sql;

As in first method, upload this script into forum's root folder and execute. Follow it's URL and you'll get a mysql dump file. Save it, for example, as win2utf.sql and upload to /somewhere/at/server Step 2: Connect to your sever throught SSH and run:

#mysql -uusername -p dbname < /somewhere/at/server/win2utf.sql

Thats it. More info and russian support topics: 1, 2. Always make a backup before performing any operations with database!

Hi, I have run the first script and it has gone well. I have then added this code to config file

$INFO['sql_charset']            =    'utf8';

and upgraded to IPB 3.x
Then I have set UTF-8 encoding from withing ACP.
I can confirm that my database collation is now set to utf8_unicode_ci, but posts arent readable from within phpMyAdmin and the forum.
Please advice what could have gone wrong?

Many thanks to all of you for your nice words!

Yes, I can probably upload the script to resources, after a few improvements.

Well, you seem to want to try this on a backup copy of your database at your server - I think this is possible,

although I did it at my copy at home.

I do not know the current charset of your database, but you should try to run the script, on a database copy having exactly

the same content, however both the database copy and its tables should be created with utf8 encoding.

I did this by:

1) Exporting the database (with create tables commands) to an sql script

2) Replacing latin1 to utf8 everywhere in the script (create tables commands and client charset)

3) Creating a new utf8 database

4) Importing the sql script in this new utf8 database

Good luck and please tell us if it worked!

I hope it works better in your case - as I said in my case some posts/fields were not converted, and I am still checking what went wrong and if I find something I will post it here.

Hello,

I have a forum with Win1251 forum encoding and latin1_swedish_ci collation database.
Can you please outline step by step instructions here?
I am a bit lost with so many posts a few mentioned scripts.

First I will need to make a backup and export my database as SQL file and then run your script from command line?

Also could you please comment about these parameters?

Note two important issues:
- the "set names" command to the old encoding to read correctly from the database and
-the "_utf8" modifier before the utf8 string at the UPDATE command to write to the database. without it, the script did NOT work.
how?

$old_char_set = "iso-8859-7";
In my case it will be Win1251?

$alter_database=false; // if true,database and its tables (structure) will be converted to utf8 as well. NOTE: May alter length/type of some fields
Should this be set to true?

$autodetect_encodings="ISO-8859-7,UTF-8";
In my case Win1251, UTF-8?

finally after 3 months of searching the web for solution .

finally i found good script.

DataBase Charset Converter v2.2 done by AL3NDALEEB :thumbsup:

i think this will help i did not test it yet becouse

i'm away from my computer soon i'll test it and provide feedback her .

it's only one file that will convert your Db even the large one becouse it's done 500 per cycle .

never try it on live forum

also when you export your DB via phpmyadmin export it with the same Charset you use in your forum .

if you use latin1 in your forum export your DB in latin1 form because this script will mainly depend on that

to 100% convert your DB into utf-8 without problems .

i hope this will help :whistle: .

c22.zip

Has anyone tried this script yet?

Hello,

First thing first...

If you upgrading your UTF-8 site to 3.0.0,

YOU HAVE TO PUT THAT LINE ($INFO['sql_charset'] = 'utf8' ) INTO CONFIG FILE BEFORE STARTING UPGRADE PROCESS

Otherwise some of your database will be broken...

Then that will set the name utf8 and you are good to go... :)

Good luck

May it be that if I put it in config file and upgrade from 2.x to 3.x it will convert the database content to UTF-8 and the forum with UTF-8 encoding will start working just fine without running above script?

I've not yet been able to find the appropriate answers for you yet, but I sincerely apologize for this oversight and delay. I will speak to Jason and we will get this resolved for you shortly.

It has been two weeks since you have promised to get an answer for me,but I am still waiting for the reply.

Hello,

I have order a paid programming in January 2009 and surprisingly the job has not been completed until now.
I have tried to contact Customer Support with no luck. They have promised that someone will contact me "tomorrow" which was two weeks ago, but no one did contacted me.

I dont see other choice than to complain publicly here on the board.

Anyone care to explain me WHY my $120 value service has NOT been offered in the last 6 months?
Should I wait for another 6 months to get this job done?

See ticket 587929 for more details.