HeidiSQL fails to handle certain column collations

Suppose my database has default collation set to utf8_polish_ci. And then I create a simple table (I don't specify any collation when creating columns) and I end up with this:

CREATE TABLE `test_table` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`string1` VARCHAR(50) NOT NULL COLLATE 'utf8_polish_ci',
`string2` VARCHAR(50) NOT NULL COLLATE 'utf8_polish_ci',
PRIMARY KEY (`id`)
)
COLLATE='utf8_polish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

So far so good. Then I use the table editor in HeidiSQL to change the collation of the string1 column to utf8_general_ci and click Save. Then this command is issued properly:

ALTER TABLE `test_table` CHANGE COLUMN `string1` `string1` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci' AFTER `id`;

Then I switch to another table and come back to test_table or choose Refresh on my test_table and the collation definition disappears in the column list for string1! Only string2 is marked as utf8_polish_ci. Then the CREATE code tab shows this:

CREATE TABLE `test_table` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`string1` VARCHAR(50) NULL,
`string2` VARCHAR(50) NOT NULL COLLATE 'utf8_polish_ci',
PRIMARY KEY (`id`)
)
COLLATE='utf8_polish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

Again, the collation definition for string1 is missing. Among other things, this is very inconvenient when I have a foreign key set on a column which should be utf8_general_ci, because whenever I make a change to the table in HeidiSQL, I get an error because the proper collation on the column has not been set - Heidi somehow forgets the collation definition and I have to set it manually each time.

I think the expected behaviour would be for Heidi to always display column collations, always provide collation information in CREATE TABLE statement for each column and for any ALTER TABLE code that is issued as a result of using the table editor.

Is this a known issue or should I file a bug? This happens on build 3481.

If the ALTER query looks good, that sounds like your database collation is not utf8_polish_ci, but utf8_general_ci ? Heidi cannot forget something - if the ALTER query is ok then it could only be the server not returning the just changed collation in a SHOW CREATE TABLE result.

However, I also think Heidi could display the right collation, just to be sure what you have.

Well, I did some more testing and it seems Heidi really forgets something and I also found another bug... Let me explain in more detail. Let's use a database named `test`. Its collation is utf8_polish_ci:

SHOW CREATE DATABASE test

outputs:

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_polish_ci */

Now I create my test_table:

CREATE TABLE `test_table` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`string1` VARCHAR(50) NOT NULL COLLATE 'utf8_polish_ci',
`string2` VARCHAR(50) NOT NULL COLLATE 'utf8_polish_ci',
PRIMARY KEY (`id`)
)
COLLATE='utf8_polish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

Then I change the collation of one column like before:

ALTER TABLE `test_table` CHANGE COLUMN `string1` `string1` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci' AFTER `id`;

And now after switching to another table and coming back to test_table there are two problems:

1. The collation for string1 is not displayed.
2. (just discovered) Column string1 is incorrectly marked as Allow NULL.

Then the CREATE code tab show BOTH of these problems and the code is incorrect:

CREATE TABLE `test_table` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`string1` VARCHAR(50) NULL,
`string2` VARCHAR(50) NOT NULL COLLATE 'utf8_polish_ci',
PRIMARY KEY (`id`)
)
COLLATE='utf8_polish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

In fact when I run SHOW CREATE TABLE `test_table` manually, I get the correct definition with all collations defined:

CREATE TABLE `test_table` (
`id` int(10) unsigned NOT NULL auto_increment,
`string1` varchar(50) character set utf8 NOT NULL,
`string2` varchar(50) collate utf8_polish_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci

So both the table editor and CREATE code tab display incorrect information regarding column string1. This is not only a problem of wrong display because it can propagate further if I use Heidi's table editor. Now let's suppose I continue using Heidi and I just want to rename column string1 to new_string1. Heidi runs this:

ALTER TABLE `test_table` CHANGE COLUMN `string1` `new_string1` VARCHAR(50) NULL AFTER `id`;

Now you can see that the ALTER TABLE statement is wrong because apart from renaming the field it also makes two changes I didn't ask for:

1. The column is changed from NOT NULL to NULL
2. The column's collation is changed from utf8_general_ci to utf8_polish_ci (because no collation definition means the default table collation is used)

I hope I made it clear enough this time. I think you should be able to reproduce these bugs if you follow the same steps.

Hi,
Did you have a chance to look into it? This bug is still present in the latest build. I've noticed also another bug relating to this: comments are not shown for the problematic columns with a different collation and are also lost in the CREATE Code tab.

Not yet looked after this. But I should do that definitely after having multiple connections committed.

Ok, thanks. The multiple connections feature also looks useful!

Should be fixed in r3559.

The problem was that, if you set and save a column collation - let's say "dec8_swedish", the server will only return the CHARSET clause, not the COLLATION clause if the selected collation is the default for the specified charset. HeidiSQL now detects this per-charset default collation if it's not explicitely seen in the SHOW CREATE TABLE result.

Looks like it works well now. Thanks!