Bug #20961 The Server does not accept collation changes in my.cnf

• Submitted: 11 Jul 2006 7:17
• Modified: 18 Jul 2006 16:07
• Reporter: Lenz Grimmer
• Status: Can't repeat
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 5.0.23
• OS: Linux (SUSE Linux 10.1)
• CPU Architecture: Any

[11 Jul 2006 7:17] Lenz Grimmer

Description:
I use the MySQL-server-5.0.23-0.glibc23 on SUSE Linux 10.1 (x86). I try to define a server character set and collation in /etc/my.cnf, but somehow the changed values do not get honoured by the server - SHOW VARIABLES still shows the same defaults.

How to repeat:
Create a /etc/my.cnf configuration file with the following content:

[mysqld]
character_set_server=latin1
collation_server=latin1_german1_ci

Restart the server and connect to it using the mysql client. Check the value of the "collation_server" variable and observe it's still set to the default collation:

lenz@localhost:(none) > show variables like 'collation_server';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| collation_server | latin1_swedish_ci |
+------------------+-------------------+
1 row in set (0.01 sec)

lenz@localhost:(none) > SHOW COLLATION like 'latin1_german%';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
+-------------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)

I would have expected that "collation_server" would now be set to "latin1_german1_ci" by default? Or am I missing something here?

[12 Jul 2006 1:10] Peter Laursen

This works fine on SuSE 10.0 (and WinXP)
(and is what 'MySQL Administrator' does)

my.cnf settings:
================

#Set the default character set.
default-character-set=latin1

#Set the default collation.
default-collation=latin1_danish_ci

.. and after restart of server:
===============================

show variables like '%character_set_s%';

Variable_name         Value 
--------------------  ------
character_set_server  latin1
character_set_system  utf8

show variables like '%collation_server%';

Variable_name     Value           
----------------  ----------------
collation_server  latin1_danish_ci

BTW: I think you use the settings in both the [mysqld] and the [client] section of my.cnf. And here MySQL Administrator has no option to write to the [client] section what I have complained about elsewhere!

[12 Jul 2006 1:22] Peter Laursen

BTW: I think you SHOULD use the settings ...

[18 Jul 2006 16:07] MySQL Verification Team

Still I am not able to repeat on Suse 10, source server version.

[14 Jan 2007 21:08] 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/18099

ChangeSet@1.2377, 2007-01-15 01:11:56+04:00, [e-mail] +5 -0
  bug #20961 (DEFAULT for NO DEFAULT may insert garbage)
  GEOMETRY field fixed - now it returns an error in this
  case.