collapse

Author Topic: Changing Database Collations  (Read 2724 times)

0 Members and 1 Guest are viewing this topic.

Offline Skhilled

  • Administrator
  • Hero Member
  • *
  • Posts: 2641
  • Attack: 3025
    Defense: 2963
    Attack Member
  • Gender: Male
  • Retro Gamer!
    • Doc Skillz!
Changing Database Collations
« on: February 12, 2012, 06:36:38 AM »
Database collations relate to how a database views and displays various language characters. For instance, an all English collation will not display accented characters in other languages well such as ", , , etc". For the most part, all collations should always be the same. To tell what your particular server collations are you should look in phpmyadmin or ask your hosting.

Different servers may have different collations while some will have the same. When you install software on a server (such as SMF) or add mods, themes, etc. from within a program such as SMF they will be installed and set to whatever collation the default is for the server.

Problems arise if you change hosting or move to another server that does not have the same collations. A previous server you were on may have had "utf8_general_ci" as the default and the new server that you just moved to may be set to "latin1_swedish_ci".

Installing things on the old server will give you "utf8_general_ci" collations. But after the move anything you install, such as a mod, will give you "latin1_swedish_ci". When you view your database in phpmyadim you will see "mixed" collations some for one and some for the other. The more you change hostings and encounter different collations you will get even more mixed collations.

The point is: mixed collations can cause problems for your mods and you'll may never know why.

Here are 2 easy ways to fix this:

1. Use the following script by making the necessary changes described below and uploading it to the root folder of your forum:

Code: [Select]
<?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!";
?>


Make sure to substitute in the above script:

- myuser_mydbname with your database name;

- myuser_mydbuser with your mysql username;

- mypassword with your password for the mysql user;

- utf8-general_ci with your new collation if different;

2.  Download: Phoca Changing Collation Tool


Offline hackrishna

  • DS User
  • Not So New
  • *
  • Posts: 17
  • Attack: 79
    Defense: 75
    Attack Member
  • Gender: Male
    • Epic Tech Forums
Re: Changing Database Collations
« Reply #1 on: March 11, 2012, 08:47:18 AM »
i used to get error messages of UTI-8 collations. It got solved after i ran a query thru phpmyAdmin, oldiesman had helped me for that a long ago.

Last week i found the same error while banning a member :( I have changed all collations to UTF-8 still why this hapening .. Can you help me ?  :dontknow:

Offline Skhilled

  • Administrator
  • Hero Member
  • *
  • Posts: 2641
  • Attack: 3025
    Defense: 2963
    Attack Member
  • Gender: Male
  • Retro Gamer!
    • Doc Skillz!
Re: Changing Database Collations
« Reply #2 on: March 11, 2012, 09:02:41 AM »
Oldiesmann is a great guy! ;)

First, you need to find out what your server collation is. Go to phpmyadmin and on the home page you'll see what it is supposed to be. You should confirm this with your hosting.

2nd, if you have moved from hosting to hosting you probably have mixed collations. go into phpmyadmin and select your forum's database. The left most column will show you the collations for all of your tables. At the very bottom of that column you'll see what the collation should be for the entire database.


Offline Skhilled

  • Administrator
  • Hero Member
  • *
  • Posts: 2641
  • Attack: 3025
    Defense: 2963
    Attack Member
  • Gender: Male
  • Retro Gamer!
    • Doc Skillz!
Re: Changing Database Collations
« Reply #3 on: March 11, 2012, 09:09:06 AM »
BTW, if they not all the same then you should change them so they are using the tool in the first post. You can also manually do it in phpmyadmin but it will take you a month of Sundays to get it done. LOL


Offline hackrishna

  • DS User
  • Not So New
  • *
  • Posts: 17
  • Attack: 79
    Defense: 75
    Attack Member
  • Gender: Male
    • Epic Tech Forums
Re: Changing Database Collations
« Reply #4 on: March 11, 2012, 09:27:11 AM »
Oldiesmann is a great guy! ;)

First, you need to find out what your server collation is. Go to phpmyadmin and on the home page you'll see what it is supposed to be. You should confirm this with your hosting.

2nd, if you have moved from hosting to hosting you probably have mixed collations. go into phpmyadmin and select your forum's database. The left most column will show you the collations for all of your tables. At the very bottom of that column you'll see what the collation should be for the entire database.

Yeah indeed he is! ;)

Well, i have did all that you said ... all the collations are UTF-8 in phpmyAdmin ...

BTW, if they not all the same then you should change them so they are using the tool in the first post. You can also manually do it in phpmyadmin but it will take you a month of Sundays to get it done. LOL

LOL  :D
There is a query for that ... i will paste it here once i get back to my PC  ;D

Offline Skhilled

  • Administrator
  • Hero Member
  • *
  • Posts: 2641
  • Attack: 3025
    Defense: 2963
    Attack Member
  • Gender: Male
  • Retro Gamer!
    • Doc Skillz!
Re: Changing Database Collations
« Reply #5 on: March 11, 2012, 09:37:51 AM »
Yes, I saw the query on the net. i didn't post it here because I'd have to explain it in detail so new users could understand it. I try to post things here that way or explain them so a child can understand it.


Offline hackrishna

  • DS User
  • Not So New
  • *
  • Posts: 17
  • Attack: 79
    Defense: 75
    Attack Member
  • Gender: Male
    • Epic Tech Forums
Re: Changing Database Collations
« Reply #6 on: March 11, 2012, 01:04:01 PM »
Yes, I saw the query on the net. i didn't post it here because I'd have to explain it in detail so new users could understand it. I try to post things here that way or explain them so a child can understand it.

you're right.... it will confuse users, so i'm not posting it here ;)

Offline Skhilled

  • Administrator
  • Hero Member
  • *
  • Posts: 2641
  • Attack: 3025
    Defense: 2963
    Attack Member
  • Gender: Male
  • Retro Gamer!
    • Doc Skillz!
Re: Changing Database Collations
« Reply #7 on: March 11, 2012, 02:23:12 PM »
Actually, I think the script is in the first post. LOL But no one has asked or complained about it yet. I'll explain it further in needed. a lot of new users don't know much about cPanel or any of the things in it. So, I try to make sure there's a tutorial explaining such things when I post something like that or will write one soon.


 

ordinary
ordinary
ordinary
ordinary