Column Collate Showing Up Different

[skygxproject]

When I take a look at the “4 Objects” tab of the tables in my MySQL database which include some char, varchar and text columns, I see that the the Collation is latin1_swidish_ci. When I take a look at the same table in MySQL Administrator 1.1.4, it shows those columns collating on latin1_general_ci. When I right-click on the table in SQLyog and select View Advances Properties, the listing in the window that pops up shows the collation to be latin1_general_ci. So I think the Objects tab is displaying incorrect collation info. Could I get a verification of that bug?

Thanks,

Christopher

[Ritesh]

Can you send me the CREATE TABLE statement for the table. It seems to be a bug with MySQL rather than SQLyog as we just display information sent by MySQL.

[peterlaursen]

I have some columns using latin1_swedish_ci collation and some using latin1_danish_ci. The collation latin1_swedish_ci is the server default.

the columns using latin1_danish_ci show that all three places

the columns using latin1_swedish_ci show this both places in SQLyog. In MySQL Administrator they show up as using “default” collation.

Is there such thing as a “latin1_general_ci” collation at all ?

What is your server default ?

[peterlaursen]

Well yes there is a “latin1_general_ci” collation ! Sorry!

http://dev.mysql.com/doc/refman/5.0/en/charset-mysql.html

here

http://dev.mysql.com/doc/refman/5.0/en/charset-defaults.html

…. it is explained that there are server, database, table and column defaults!

The OBJECTS tab show (or should show!) the collation for each column.

Advanced table properties show the table default (only), BUT IT MAY NOT BE USED by the columns actually!

[peterlaursen]

so ..

I guess you simply have some columns of data where the collation differs from your defaults. How could that happen?

1)

if the data were imported from another server or database using other defaults, the data don't use your defaults. The charset and collation stay with the data!

2)

Also changing server/database/table defaults does not change data. To do that you must issue an ALTER TABLE statement. So if the data were created when you had other defaults, the charset and collation still stay with the data!

[Ritesh]

As I said before, its not an issue with SQLyog. Nevertheless, a really nice explanation from Peter.

[peterlaursen]

Actually I think that the next major release (with Unicode support etc.) should have an additional column in ALTER TABLE and CREATE TABLE pane where to set charset and collation for character-data.

As I said before: in an international environment you might need to have various language translations for the same thing in more columns such as catalogs, stock-lists etc. And there also could be good reasons to choose a binary collation for certain columns.

[Ritesh]

Quote:

Actually I think that the next major release (with Unicode support etc.) should have an additional column in ALTER TABLE and CREATE TABLE pane where to set charset and collation for character-data.

Indeed. We plan to add this support in v5.1 😀

[Author]

Posts