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...
Field Collation column disappeared
Ok here relays the problem,
As I can see in the mysql manual
<quote>
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
Important
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.
</quote>
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` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`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,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8
As I can see in the mysql manual
<quote>
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
Important
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.
</quote>
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` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`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,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8
Code modification/commit
from ansgar.becker,
14 years ago,
revision 5.1.0.3460
Table editor fails to detect the default collation on some tables. Fetch it from SHOW TABLE STATUS instead as fallback. See http://www.heidisql.com/forum.php?t=6202
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.
Thanks for the Default collation now its working
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.
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.