Some int,decimal field lose COMMENT when have default value

I fouund : Some int,decimal field lose COMMENT when have default value.

I use mysql/mariadb. os: windows 7.0 heidisql up to 5191 all version

CREATE TABLE tmp ( Teaching_ID INT(11) NOT NULL DEFAULT '', Depart_ID INT(11) NOT NULL DEFAULT '', TeacherName VARCHAR(10) NOT NULL DEFAULT '-' COMMENT 'cccccccccc', TeacherCode VARCHAR(15) NOT NULL DEFAULT '-' COMMENT 'dddddddddd', ClassName VARCHAR(20) NOT NULL DEFAULT '-' COMMENT 'eeeeeeee', CourseName VARCHAR(30) NOT NULL DEFAULT '-' COMMENT 'fffffffff', s31 DECIMAL(14,4) NOT NULL DEFAULT '', s32 DECIMAL(14,4) NOT NULL DEFAULT '', s0 DECIMAL(14,4) NOT NULL DEFAULT '', s1 DECIMAL(14,4) NOT NULL DEFAULT '', s2 INT(1) NOT NULL DEFAULT '' ) COLLATE='utf8_general_ci' ENGINE=InnoDB;

modify above table in heidisql(all version), set default value not null, save, Some int,decimal field lose COMMENT when have default value.

look the picture

the 1st picture is in navicat and the 2nd picture is in heidisql, some fields' common lost.

Your SQL code contains some invalid DEFAULT '' for numeric columns. I removed these and then I could create the table. Then I added a comment to "s2" and "s31" columns, and these are there also the next time I click on that table.

I guess you need to provide more details here, especially the SQL which is generated by HeidiSQL, and where it differes from the SQL code from SHOW CREATE TABLE ...

Thank you.

My Code:

CREATE TABLE `t_team` (
    `Team_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Team_ID',
    `TeamName` VARCHAR(50) NOT NULL DEFAULT '-' COMMENT 'TeamName',
    `TeamCode` VARCHAR(32) NOT NULL DEFAULT '-' COMMENT 'TeamCode',
    `Dept_ID` INT(11) NOT NULL DEFAULT '1' COMMENT 'Dept_ID',
    `Enabled` TINYINT(4) NOT NULL DEFAULT '1' COMMENT 'Enabled',
    PRIMARY KEY (`Team_ID`),
    INDEX `Idx_Dept_ID` (`Dept_ID`)
)
COMMENT='team info'
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1

after run, the following code will see in heidisql create table: 


CREATE TABLE `t_team` (
    `Team_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Team_ID',
    `TeamName` VARCHAR(50) NOT NULL DEFAULT '_' COMMENT 'TeamName',
    `TeamCode` VARCHAR(32) NOT NULL DEFAULT '_' COMMENT 'TeamCode',
    `Dept_ID` INT(11) NOT NULL DEFAULT '',
    `Enabled` TINYINT(4) NOT NULL DEFAULT '',
    PRIMARY KEY (`Team_ID`),
    INDEX `Idx_Dept_ID` (`Dept_ID`)
)
COMMENT='team info'
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1;

theh common of fields Dept_ID and Enabled lost.

Hm, I have no problems here. All comments are preserved and displayed in the table editor. See attached image.

Are the column comments included when you fire SHOW CREATE TABLE t_team ?

I do not see any issues with the code on my both MySQL 5.7 and MariaDB 10.1 (Heidi 9.5 right now)

I don't know whats happen......

that's Version 5188.

this is 5197

Create table, then view Create sattement

SHOW CREATE TABLE t_team :

CREATE TABLE t_team ( Team_ID int(11) NOT NULL AUTO_INCREMENT COMMENT 'Team_ID', TeamName varchar(50) NOT NULL DEFAULT '-' COMMENT 'TeamName', TeamCode varchar(32) NOT NULL DEFAULT '-' COMMENT 'TeamCode', Dept_ID int(11) NOT NULL DEFAULT 1 COMMENT 'Dept_ID', Enabled tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Enabled', PRIMARY KEY (Team_ID), KEY Idx_Dept_ID (Dept_ID) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='team info'

Dept_ID int(11) NOT NULL DEFAULT 1 COMMENT 'Dept_ID', Enabled tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Enabled'

is right

hello, this replear in my, i use mariadb 10.2.11,and i change many version of heidisql ,such as 9.4s and 9.5s,i found tha when the create sql string has "fefault value" and alow has column comment ,then the heidisql's table view don't not show the comment above.

--create table：

CREATE TABLE test ( aaa int(11) DEFAULT 1 COMMENT '1111111' )

show create table test：

CREATE TABLE test ( aaa int(11) DEFAULT 1 COMMENT '1111111' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Hi all, I can confirm a bug with DEFAULT VALUE and COMMENT terms but only with INT types (all sizes): You can't define simultaneous non-empty DEFAULT VALUE and COMMENT in the CREATE or ALTER sentences, HeidiSQL will clear them. You can only define a non-empty DEFAULT VALUE or a COMMENT but not both. Finally, I set the DEFAULT VALUE and COMMENT with phpMyAdmin without errors and reopenend with HeidiSQL: HeidiSQL clears them!

OK, solved with 9.5.0.5219.

thanks.

@ansgar hi sir, i found postgresql also has a problem like this, didn't show the comment in table view and when i change the table and save, and the client show Grammatical errors in alter statement,i thind the heidisql for postgresql is a base support ,isn't it ?