Weird characters after changing hosts

starsyria · Post by **starsyria** » Sat Mar 31, 2007 9:04 pm

I have moved my site to another host,I moved my files and database to the new host ,Now I have my characters encoded in a weird encoding.

Database collation: UTF-8
Language File Encoding : utf8_general_ci
Tables Collation: latin1_swedish_ci

I also made sure I have the same environment ( php and mysql version).

any 1 knows what went wrong ?

Post by **infograf768** » Sun Apr 01, 2007 6:29 am

There is a mismatch between the collation/charset and the tables Collation.

If utf-8 is your choice, all should be utf-8.

Usually this due to a non-edited dump and query.

starsyria · Post by **starsyria** » Mon Apr 02, 2007 1:45 am

Thank you for your reply,

I have changed the collation and the charset of the database:

charset : Utf8
collation utf8_general_ci

but still have the problem .

Best Regards

Post by **infograf768** » Mon Apr 02, 2007 5:23 am

What were the settings of the original database and how did you export the contents?
What is the encoding of your language file?

H13 · Post by **H13** » Tue Apr 03, 2007 9:53 pm

Perhaps you have changed database collation, but not table and column collation.

You can use the following script. But be very carefull, it can change data. Save your data before running this script.
I use this script after installing Joomla. I don't know why but collation of installed Joomla data is set to latin1_swedish_ci (this is the default collation in MySQL, because MySQL comes from Sweden). What does this script do:

It change collation in database,
it change collation in all tables,
it change collation in all columns of the tables. (Because if you change database collation or table collation, columns of the tables are still set to latin1_swedish_ci, you must change this columns too)

Code: Select all

<html>
<head>
<style type="text/css">
body {background-color:#000; font-family:"Courier New", Courier, monospace; font-size:12px;color:#ffffff}
</style>
</head>
<body>
<?php
function start_db($mysqlhost,$mysqldatabase, $mysqluser, $mysqlpass)
{
	global $conn;
	$conn = mysql_connect($mysqlhost, $mysqluser, $mysqlpass);
    if (!$conn)
	{
		echo '<a href="index.php">Back to the main site</a><br />';
		die('Database error.');
	}	
	$select = mysql_select_db($mysqldatabase, $conn);
    if (!$select)
    {
		echo '<a href="index.php">Back to the main site</a><br />';
		die('Database error.');
	}
}
function end_db ($conn)
{
	mysql_close($conn);
}

if (   isset($_POST['host'])
	&& isset($_POST['user'])
	&& isset($_POST['pass'])
	&& isset($_POST['name'])
	&& isset($_POST['col']))
{
	$mysqlhost 		= $_POST['host'];
	$mysqluser 		= $_POST['user'];
	$mysqlpass 		= $_POST['pass'];
	$mysqldatabase 	= $_POST['name'];
	$collation 		= $_POST['col'];
	
	start_db($mysqlhost,$mysqldatabase, $mysqluser, $mysqlpass);

       //Start code from http://php.vrana.cz/ - Author - Jakub Vrana
	function mysql_convert($query) {
	    echo $query . '           ... <span style="color:#26d92b">OK</span><br /> ';
	    return mysql_query($query);
	}
	mysql_convert("ALTER DATABASE $mysqldatabase COLLATE $collation");
	$result = mysql_query("SHOW TABLES");
	while ($row = mysql_fetch_row($result)) {
	    mysql_convert("ALTER TABLE $row[0] COLLATE $collation");
	    $result1 = mysql_query("SHOW COLUMNS FROM $row[0]");
	    while ($row1 = mysql_fetch_assoc($result1)) {
	        if (preg_match('~char|text|enum|set~', $row1["Type"])) {
	            mysql_convert("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] CHARACTER SET binary");
	            mysql_convert("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] COLLATE $collation" . ($row1["Null"] ? "" : " NOT NULL") . ($row1["Default"] && $row1["Default"] != "NULL" ? " DEFAULT '$row1[Default]'" : ""));
	        }
	    }
	}
	mysql_free_result($result);
        //End code from http://php.vrana.cz/ - Author - Jakub Vrana
	end_db($conn);
	echo '<br /><a href="index.php">Back to the main page</a>';
}
else
{
?>
	<h2> CHANGE DATABASE COLLATION (DATABASE, TABLES, COLUMNS)</h2>
	<form action="index.php" method="post">
	DB Host:______<input type="text" name="host" value="localhost" /><br />
	DB User:______<input type="text" name="user" value="root" /><br />
	DB Password:__<input type="password" name="pass" value="pass" /><br />
	DB Name:______<input type="text" name="name" value="jos" /><br />
	DB Collation:_<input type="text" name="col" value="utf8_general_ci" /><br />
	***** Manual signatures are NOT allowed *****__<input type="submit" value="Submit" />
	</form>
<?php
}
?>
</body>
</html>

starsyria · Post by **starsyria** » Tue Apr 03, 2007 11:02 pm

Thank you for your replies,

I did a complete cpanel account backup restore again and now I got the problem solved, however, I can see my tables have latin_swedish collation (like you said) .

I now Since everything works fine I don't dare to try any scripts.
my language file is utf-8 .

I noticed sometimes my site automatically switches the encoding to Iso , although it has a utf-8 encoding in the head of the page.
does any one know about the server's default charset (Default http connection charset ) ?
could this be related to server configuration ?

Best Regards and thanks for the script

pulp · Post by **pulp** » Mon Jul 16, 2007 10:51 am

just wanted to add another "Thank You" to H13 for the script.

H13 · Post by **H13** » Mon Jul 16, 2007 11:02 am

sedlovs · Post by **sedlovs** » Mon Jan 24, 2011 4:49 pm

Hi everybody!

Wanted to consult you about posts above. Seems that you are great experts in this field.

But I instead have very weak knowledge about all this stuff, and according to comments it seems that script works very weel!
I am just wondering where I should copy in this script. For my hosting administration I use Cpanel 11 and have installed Joomla on my homesite.

Can you please help me somehow?

Thanks in advance!

H13 · Post by **H13** » Wed Feb 16, 2011 9:08 pm

Hi, copy this script somewhere on your server (e.g. temporary folder), go to this folder through your browser and run the code, see:
http://www.phoca.cz/documents/38-tools/ ... n-database

Jan

KoichiroDK · Post by **KoichiroDK** » Thu Sep 13, 2012 7:30 am

I was wondering if my problem was related to the above. My joomla site recently got hacked through JCE, so I cleaned it up and backed it up. I then tried to move it to my laptop, so I could optimize the site offline. (I want to update it).

I installed the site using Akeeba and it works great, but suddenly the screen turns into the attached image. After searching the internet I found this thread to be most related to my problem, as I can see that some of my tables are UTF8_general_ci and a few are latin_swedish etc.

I tried to create a whole new database using UTF8 but still didnt help.

I tried to use H13´s script but I get a "Database error" on the second screen.

Please understand that I have no knownledge of how the backend, code, sql works. All my knownledge is gained through trial and error.

Anyone?

The Joomla! Forum™

Weird characters after changing hosts Topic is solved

Weird characters after changing hosts

Re: Weird characters after changing hosts

Re: Weird characters after changing hosts

Re: Weird characters after changing hosts

Re: Weird characters after changing hosts

Re: Weird characters after changing hosts

Re: Weird characters after changing hosts

Re: Weird characters after changing hosts

Re: Weird characters after changing hosts

Re: Weird characters after changing hosts

Re: Weird characters after changing hosts