DBTools Software - Articles

As the subject raised a lot of threads in the forums we did some research in order to provide a simple and quick solution to the problem.
Here is the list of things which should be checked and fixed (if required) in order to prevent errors with different collations:

1) The database and the tables must have the same charset and collation. If you have change the server config after a database or a table was created they are in different charset/collation. A simple way to change the database/table charset/collation is by issuing the ALTER DATABASE or ALTER TABLE command.

2) The my.ini config file needs to have the default values for [client] and [server] sections as follow:

[client]
default-character-set=ascii
default-collation=ascii_general_ci

[server]
default-character-set=ascii
default-collation=ascii_general_ci

In this example we have used ascii character set with ascii_general_ci collation. Remember that the collation must belong to that charset or the error won't be prevented.

3) Restart your server to the changes take effect.

Checking the configuration. Execute the following commands:

mysql> show variables like 'collation%';
mysql> show create table [mytable];

With these commands you will make sure that your tables have the correct character set and collation. One variable doesn't need to be changed "collation_connection" if the steps above were successfully executed. On the other hand some queries which use string comparisons may require a different character set or collation. Usually executing the command "set collation_connection=ascii_general_ci" does the trick.

PS: Replace the values from the examples to the values you are going to use.

Support / DBTools Software