Some int,decimal field lose COMMENT when have default value

vga posted 9 months ago in General

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.

vga posted 9 months ago

look the picture

2 attachment(s):
  • Navicat
  • Heidisql
vga posted 9 months ago

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

ansgar posted 9 months ago

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 ...

vga posted 9 months ago

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.

ansgar posted 9 months ago

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 ?

1 attachment(s):
  • column-comments
Misha v.3 posted 9 months ago

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

vga posted 9 months ago

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

2 attachment(s):
  • 1
  • 2
vga posted 9 months ago

that's Version 5188.

this is 5197

Create table, then view Create sattement

2 attachment(s):
  • 3
  • 4
vga posted 9 months ago

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'

vga posted 9 months ago

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

is right

aaa5131421 posted 9 months ago

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

1 attachment(s):
  • TIM-20171229151210
jmparatte posted 8 months ago

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!

jmparatte posted 8 months ago

OK, solved with 9.5.0.5219.

vga posted 8 months ago

thanks.

aaa5131421 posted 8 months ago

@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 ?

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