Bug #15276 MySQL ignores collation-server
Submitted: 27 Nov 2005 19:04 Modified: 25 Sep 2006 18:12
Reporter: Timo Maier Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:4.1, 5.0.17-BK, 5.1 OS:Linux (Linux)
Assigned to: Alexander Barkov

[27 Nov 2005 19:04] Timo Maier
Description:
This one fails to set the collation:
#/usr/sbin/mysqld [...] \
--collation-server=latin1_german1_ci \
--default-character-set=latin1 \
--character-set-server=latin1

mysql> show variables like '%colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

this one succeds:
#/usr/sbin/mysqld [...] \
--default-character-set=latin1 \
--character-set-server=latin1 \
--collation-server=latin1_german1_ci

mysql> show variables like '%colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_german1_ci |
| collation_server     | latin1_german1_ci |
+----------------------+-------------------+

btw - how do I set the default collation_connection

How to repeat:
See above

Suggested fix:
Make --collation-server let set the default server collation
[28 Nov 2005 17:42] Valerii Kravchuk
Thank you for a bug report. I hope, you tested versions 5.0.15 and 5.0.16 (not 5.1.15 and 5.1.16 - these do not exist yet)...

In any case, the behaviour you described is verified on 5.0.17-BK (ChangeSet@1.2013.1.1, 2005-11-26 20:23:20+01:00) on Linux:

[openxs@Fedora 5.0]$ bin/mysqld_safe --collation-server=latin1_german1_ci --default-character-set=latin1 --character-set-server=latin1 &
[1] 11912
[openxs@Fedora 5.0]$ Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0,09 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysqladmin -uroot shutdown;
STOPPING server from pid file /home/openxs/dbs/5.0/var/Fedora.pid
051128 20:27:23  mysqld ended

[1]+  Done                    bin/mysqld_safe --collation-server=latin1_german1_ci --default-character-set=latin1 --character-set-server=latin1
[openxs@Fedora 5.0]$ bin/mysqld_safe --default-character-set=latin1 --character-set-server=latin1 --collation-server=latin1_german1_ci &
[1] 11973
[openxs@Fedora 5.0]$ Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_german1_ci |
| collation_server     | latin1_german1_ci |
+----------------------+-------------------+
3 rows in set (0,00 sec)

And I would call it a bug (or a documentation request), because there is nothing in current documentation that describes the different results for different order of options for mysqld.
[28 Nov 2005 18:53] Timo Maier
I would call it bug, too, because the order of parameters must not matter.
[28 Nov 2005 18:55] Timo Maier
I just forgot to ask: Shouldn't the collation_connection also be latin1_german1_ci, too? I can't find anything where I can set a default value for this.
[19 May 2006 12:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6630
[22 Jun 2006 14:56] Alexander Barkov
The fix appeared to be incomplete.
With this fix now it is impossible to chose a non-default
character set without having to specify collation as well:

./mysqld --default-character-set=koi8r
060622 19:51:57 [ERROR] COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'koi8r'
060622 19:51:57 [ERROR] Aborting
[23 Jun 2006 10:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8133
[23 Jun 2006 13:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8140
[7 Aug 2006 6:34] Alexander Barkov
Merged into 5.0.25 and 5.1.12
[25 Sep 2006 18:12] Paul Dubois
Noted in 4.1.22, 5.0.25, 5.1.12 changelogs.

The --collation-server server option was being ignored. With the fix
for this problem, if you choose a non-default character set with
--character-set-server, you should also use --collation-server to
specify the collation.