An unexpected database error occurred. Please try again later.

[Kaiser]

Whenever I visit my site I get An unexpected database error occurred. Please try again later. before that it was loading real slow and it has been doing this for the past week. I opened a ticket on my host. You guys have any idea why?

EDIT: Its working again but loading very slow.

http://adminbb.org/

 

[Kaiser]

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]

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

<?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):

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.

 

[Kaiser]

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

<?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):

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]

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

 

[Jake Bunce]

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]

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]

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]

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.