Heidisql: Field description does not appear

Created on 4 May 2019 · 6Comments · Source: HeidiSQL/HeidiSQL

Steps to reproduce this issue

Step 1;
Create the table

CREATE TABLE `teste` (
  `aip_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id',
  `aip_por` smallint(5) unsigned NOT NULL COMMENT 'por',
  `aip_em` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'em ??',
  `aip_por2` smallint(5) unsigned DEFAULT NULL COMMENT 'por',
  `aip_em2` datetime DEFAULT NULL ON UPDATE current_timestamp() COMMENT 'em',
  PRIMARY KEY (`aip_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='Teste'

Step 2;

Look into the fields grid

Step 3;

Show create table teste

Current behavior

Field description does not appear

I saw this point when I use the On Update default value

Expected behavior

Possible solution

Environment

HeidiSQL version:
10.1.0.5552
Database system and version:
MariaDB 10.2.x
Operating system:
Windows and Linux

bug confirmed

Source

lukinhaspm

👍1

All 6 comments

What do you expects here?

rentalhost on 4 May 2019

I expect the coment "em"

all columns has comments, but heidi not show all when we have "On update" in default value

lukinhaspm on 5 May 2019

👍1

Oh, now I got and I could reproduce it!

Simplified case:

DROP TABLE IF EXISTS test;

CREATE TABLE test (
  `column` DATETIME ON UPDATE current_timestamp() COMMENT 'Comment'
);

Inspection:

The SHOW CREATE TABLE test will report it correctly:

CREATE TABLE `test` (
  `column` datetime DEFAULT NULL ON UPDATE current_timestamp() COMMENT 'Comment'
  ############################################################ ^^^^^^^^^^^^^^^^^
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Seems to be related to:

https://github.com/HeidiSQL/HeidiSQL/blob/fad93ace54c5f4909ad8f05ed84a511e247679c9/source/dbconnection.pas#L5229-L5236

The regular expression above will matches the COMMENT part when it have the ON UPDATE clause, instead of read only the content related to this second part. So the following _real_ COMMENT parse part is ignored because the COMMENT was consumed here.

https://github.com/HeidiSQL/HeidiSQL/blob/fad93ace54c5f4909ad8f05ed84a511e247679c9/source/dbconnection.pas#L5242-L5243

Possible fix:

Replace from: ^\s*ON UPDATE\s+(.*)($|\s+(COLUMN_FORMAT|COMMENT|INVISIBLE)\b) (example)
Replace to: ^\s*ON UPDATE\s+(.*?)(?:$|\s+(?=COLUMN_FORMAT|COMMENT|INVISIBLE)\b) (example)

Note: don't copy directly from regex101 because I have to drop the initial ^, that exists on original regular expression to matches the start of the string (that was consumed by previous procedures).

It must:

From (.*) to (.*?) make it lazy (so will stop if the next match pattern could be applied, in this case, the COMMENT;
From ( to (?: will ignore the second match, that is not need here;
From ( to (?= will do a positive lookahead by COMMENT to allow "stop" the matching when it is applied;

Additional notes:

If you are modifying the table it will not lost the comment after save, _except_ if you are modifying specifically a column that have ON UPDATE:

ALTER TABLE `test`
    ALTER `column` DROP DEFAULT;
ALTER TABLE `test`
    CHANGE COLUMN `column` `column` DATETIME NOT NULL FIRST;

Nodev note:

I just don't know if Pascal allows that, but basically, only the first and third change is need.

rentalhost on 5 May 2019

I had to apply a different approach as TSynRegExpr does not seem to support positive lookahead. However, I could reproduce the issue before, and after applying my fix I can't.