Bug #16366 unable to set latin1_general_ci for column collate

• Submitted: 11 Jan 2006 10:49
• Modified: 15 Jan 2006 10:52
• Reporter: Johan Kennes
• Status: Can't repeat
• Category: MySQL Administrator
• Severity: S3 (Non-critical)
• Version: 1.1.7
• OS: Windows (Windows XP)
• CPU Architecture: Any

[11 Jan 2006 10:49] Johan Kennes

Description:
When changing a table, on column level I am unable to set column collate to latin1_general_ci for a varchar(25) column. It changes to latin1_general_cs in de SQL statement before execution.

After closer inspection I found that there are several problems using collation with the Administrator.

How to repeat:
Use the Administrator and play with default collation and row collation. Check what happend using QueryBrowser: SHOW FULL COLUMNS FOR...

[11 Jan 2006 15:33] MySQL Verification Team

I was unable to repeat the behavior reported:

C:\mysql\bin>mysql -uroot db3
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-nt-log

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

mysql> show full columns from tb1\G
*************************** 1. row ***************************
     Field: id
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: col2
      Type: varchar(50)
 Collation: latin1_swedish_ci
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 3. row ***************************
     Field: col3
      Type: char(20)
 Collation: latin1_swedish_ci
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
3 rows in set (0.00 sec)

Then using Administrator I changed the collation for col2

mysql> show full columns from tb1\G
*************************** 1. row ***************************
     Field: id
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: col2
      Type: varchar(50)
 Collation: latin1_general_ci
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 3. row ***************************
     Field: col3
      Type: char(20)
 Collation: latin1_swedish_ci
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
3 rows in set (0.00 sec)

[11 Jan 2006 15:45] Johan Kennes

The Bug is in Administrator. I think they make no difference between latin1_general_ci and latin1_general_cs.

When I type "show full columns from pers" in QueryBrowser I get that column persnaam is latin1_general_ci. But when I check the column in Administrator it gives me latin_general_cs. This way I can't be sure that Administrator worked. I always have to check

Administrator 1.1.6, QueryBrowser 1.1.18, Server 5.0.16

[15 Jan 2006 10:50] Johan Kennes

Voorbeeld QueryBrowser

Attachment: MySQL QueryBrowser.jpg (image/jpeg, text), 145.55 KiB.

[15 Jan 2006 10:51] Johan Kennes

Voorbeeld Administrator

Attachment: MySQL Administrator.jpg (image/jpeg, text), 92.40 KiB.

[15 Jan 2006 10:52] Johan Kennes

I have added two print-screens which show you the differences for the columns AFD, NR, NAAM and PERSNAAM