• Registered: 2007-11-13
  • Posts: 23

Topic: *help* How to change collation??

Hi my collation in the database in right now at UTF-8_bin. This cause my big problem because the forum cant se the different in BIG and small letters. Thats means serveral things dont work.

People cant log in with    "Staffan" if there username is "staffan"
Author search do not work,  you cant search for peoples topic if the got a big letter in there name.
ect...


So if Im right the best collation to use is utf8_general_ci..

How do I change my tables in the database to utf8_general_ci instead of UTF-8_bin?

  • From: France
  • Registered: 2004-11-06
  • Posts: 695

Re: *help* How to change collation??

Aren't username case sensitive?

Anyway, the best collation depend on your language. If your forum is in Farsi, Swedish, French, Spanish, or others, the collation isn't the same. Look at the utf8_ collate available, if your language isn't one of them utf8_general_ci is probably the best one for you.

You simply need to alter it for your database: 

ALTER DATABASE `your db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

for example,

and then the same for every table, like 

ALTER TABLE `pun13_categories`  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

And then, maybe row by row (where it's useful).

Much simpler to set it up at the database level, then install PunBB.

Jeu de rôle en ligne sur table virtuelle ⁂ Shadowrun France ⁂ Chez Blacky

Jérémie's Website

3 Reply by staffanl (edited by staffanl 2008-02-01 14:08)

  • Registered: 2007-11-13
  • Posts: 23

Re: *help* How to change collation??

Jérémie wrote:

and then the same for every table, like
ALTER TABLE `pun13_categories`  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
And then, maybe row by row (where it's useful).

Is it enought to do this command or do I have to do this command for the tables under also?
Like:

bans
      username
      ip
      email
      message

Jérémie wrote:

Much simpler to set it up at the database level, then install PunBB.

Which means?

Jérémie wrote:

If your forum is in Farsi, Swedish, French, Spanish, or others, the collation isn't the same. Look at the utf8_ collate available, if your language isn't one of them utf8_general_ci is probably the best one for you.

Ok I understand. My forum is in Swedish, is latin1_swedish_ci a good choice or shall I use utf8_swedish_ci?

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: *help* How to change collation??

Yes, put a ; after each one and run them in phpMyAdmin or the MySQL CLI tool.

5 Reply by staffanl (edited by staffanl 2008-02-07 13:46)

  • Registered: 2007-11-13
  • Posts: 23

Re: *help* How to change collation??

Thanks! It works now.. The only row that I cant change is search_words/word

ALTER TABLE `search_words` CHANGE `word` `word` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL

MySQL: Documentation
#1062 - Duplicate entry 'prevoyance' for key 1
  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: *help* How to change collation??

Empty those tables and rebuild the search index.