An unexpected database error occurred. Please try again later.

Kaiser

Well-known member
They should all be "utf8_general_ci". You should change them. Check the other tables and fields as well.

Did you deliberately change those collations to "latin1_swedish_ci"?

FYI, you should be able to reproduce the error if you try to create a thread with the title indicated in the error:
I didnt change it, there are alot of other places in the database as Latin1_swedish_ci, how can I change all of them to "utf8_general_ci" ?
 

Jake Bunce

XenForo moderator
Staff member
I didnt change it, there are alot of other places in the database as Latin1_swedish_ci, how can I change all of them to "utf8_general_ci" ?
Here is a script to automate the process:

http://kb.siteground.com/article/How_to_change_the_collation_for_all_tables_in_db_to_UTF8.html

Code:
<?php
$db = mysql_connect('localhost','myuser_mydbuser','mypassword');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('myuser_mydbname'); $result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
}}
echo "The collation of your database has been successfully changed!";
?>
But you will need to modify the script to use this query instead (so it changes both the tables and their columns):

Code:
ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
I have attached the modified script to this post. This script will update your entire database to use utf8. You need to edit the file to enter your database information (host, user, pass, dbname), upload it to your site and run it from your browser.

I tested this script and it works. You should backup your database before using this script. I am pretty sure it's safe, but don't take my word for it.
 

Attachments

Kaiser

Well-known member
Here is a script to automate the process:

http://kb.siteground.com/article/How_to_change_the_collation_for_all_tables_in_db_to_UTF8.html

Code:
<?php
$db = mysql_connect('localhost','myuser_mydbuser','mypassword');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('myuser_mydbname'); $result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
}}
echo "The collation of your database has been successfully changed!";
?>
But you will need to modify the script to use this query instead (so it changes both the tables and their columns):

Code:
ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
I have attached the modified script to this post. This script will update your entire database to use utf8. You need to edit the file to enter your database information (host, user, pass, dbname), upload it to your site and run it from your browser.

I tested this script and it works. You should backup your database before using this script. I am pretty sure it's safe, but don't take my word for it.
Thanks jake I did everything, and it worked, and now all the collations are "utf8_general_ci" Thanks so much im sure this will fix many of the problems I've had. You are great! Thanks :)
 

ekine

Active member
But this script does not change the database data with umlaut characters (e.g. ö,ä,ü).
 

Jake Bunce

XenForo moderator
Staff member
But this script does not change the database data with umlaut characters (e.g. ö,ä,ü).
Are you having a problem converting the collations? It shouldn't change any data, only the data types. But like I said, backup first.
 

ekine

Active member
I think that the script may have worked as intended but where ever my content should show a special character (ö,ä,ü), it is replaced by a “black diamond question mark” symbol: �.
 

Jake Bunce

XenForo moderator
Staff member
How was that content inserted? I mean was it from a new post on the XF forum? Or was it imported from a different forum application? It sounds like the data is not UTF8. Probably not much you can do about it unless you find a script to perform an appropriate conversion.
 

ekine

Active member
It is from a different forum application. At the moment I am trying out all kinds of conversion scripts and I am sure that I will find a solution. Thanks for trying to help out. :)
 
Top