Field Collation column disappeared

gnulp posted 8 years ago in General
I'm always using the latest nightly build and some versions ago I noticed that the column which displays the field follation in the table editor has gone. in 5.1 release and in some later builds it still was there...
ansgar posted 8 years ago
Yes, right. The collation column is just hidden since I introduced a new column inbetween "Zerofill". I'll add a popup menu for right clicking the column headers which will allow you to hide/unhide columns.
ansgar posted 8 years ago
Please update to r3457 - there you can right click the column headers to show all columns again.
gnulp posted 8 years ago
it works. perfectly. thank you.
Rapid-eraser posted 8 years ago
Shouldn't these fields show the stored collation ?
Both is column and the Default collation in the table options are not showing the stored value to my installation.
ansgar posted 8 years ago
As said above, the collation column was hidden since the zerofill column was introduced recently. To show the collation column again, you need to update to the latest build, and then rightclick the column headers.
Rapid-eraser posted 8 years ago
I can see the collation column , I was wandering if there were supposed to show the stored value of the collation like when accessing data to the database tab (where the collation of each table is shown )

ansgar posted 8 years ago
Ah, I see. That's the "Default collation" pulldown on the "Options" tab (the second tab, besides "Basic"). See it?
Rapid-eraser posted 8 years ago
Yes both is pulldown and the column "Collation" inside the table tab are refusing to show the stored collation as opposed to the "Database:" tab that the same column stored collation is shown currently.

Is this some thing happening only in my installation ?

ansgar posted 8 years ago
Hm, also here empty on some tables, but most show their collation. Probably the SHOW CREATE TABLE result does not reveal the collation. If you want you can post the SHOW CREATE TABLE xyz result here, so we can analyze that.
Rapid-eraser posted 8 years ago
Ok here relays the problem,

As I can see in the mysql manual


The original CREATE TABLE statement, including all specifications and table options are stored by MySQL when the table is created. The information is retained so that if you change storage engines, collations or other settings using an ALTER TABLE statement, the original table options specified are retained. This enables you to change between InnoDB and MyISAM table types even though the row formats supported by the two engines are different.


The "SHOW CREATE TABLE" for what I understand provides only info that is compatible to all storage engines and mysql versions

You can use the "SHOW FULL COLUMNS FROM `xyz`"

to retrieve the needed info plus the missing info.

By the way the respond to the CREATE TABLE statement for my case is:

CREATE TABLE `markers` (
`name` varchar(60) NOT NULL,
`address` varchar(80) NOT NULL,
`lat` float(10,6) NOT NULL,
`lng` float(10,6) NOT NULL,
`type` varchar(30) NOT NULL,
ansgar posted 8 years ago
Default collation pulldown should be fixed in r3460, now there is a fall back to take it from the SHOW TABLE STATUS when missing in SHOW CREATE TABLE result. Only the collation attribute for single columns will stay empty, which means these are using the default collation.
Rapid-eraser posted 8 years ago
Thanks for the Default collation now its working smile

I still have a problem with the single columns collation.

If I change a collation to a column as greek_general_ci the "SHOW CREATE TABLE xyz" will report the column as 'greek' incorrectly and the column probably wont match the contained collations and stays empty, giving the false impression that it is the default collation.

This mainly happens because as said before the "SHOW CREATE TABLE xyz" tries to produce a compatible sql query that would me honored from all mysql versions.

Please login to leave a reply, or register at first.