Regression for MySQL DEFAULT CURRENT_TIMESTAMP column

[zufuliu]

Steps to reproduce this issue

This bug does not occurred in 10.2.0.5599 (64 Bit), and may related to issue #981.

Current behavior

CURRENT_TIMESTAMP highlighted as string and put in Custom text filed instead of Expression field.

Expected behavior

CURRENT_TIMESTAMP should be highlighted as keyword and put in Expression field.

• HeidiSQL version:
  11.0.0.6055 (64 Bit)

[zufuliu]

I'm sorry, this issue is identical to https://www.heidisql.com/forum.php?t=36317#p36517

[zufuliu]

After testing nightly builds on https://www.heidisql.com/download.php#, I found this was introduced in 11.0.0.6041 (Issue #350), 11.0.0.6040 and before works.

[ansgarbecker]

On MariaDB 10.3 I cannot reproduce that, probably due to automatically added parentheses after CURRENT_TIMESTAMP:

So, which server is it on your side?

[zufuliu]

tested MySQL 5.7.30-0ubuntu0.16.4.1-1 (Ubuntu)
and MySQL 5.6.16, both have the problem.

This seems only occurred with MySQL, not MariaDB, I can also confirm on MariaDB CURRENT_TIMESTAMP() became current_timestamp() after save.

[zufuliu]

on MySQL

SHOW CREATE table b

CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

on MariaDB

SHOW CREATE TABLE b

CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `add_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

[ansgarbecker]

Well there is already a fix for it, and I cannot reproduce on MySQL 5.6.47 and neither on 5.7.9. Something must be different here. You are definitely using the latest build? And is there such a query in the SQL log when opening that table?

SELECT * FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='testtable' ORDER BY ORDINAL_POSITION;

[zufuliu]

The log with Version 11.0.0.6056 (64 Bit)

USE `test`;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test';
SHOW TABLE STATUS FROM `test`;
SHOW FUNCTION STATUS WHERE `Db`='test';
SHOW PROCEDURE STATUS WHERE `Db`='test';
SHOW TRIGGERS FROM `test`;
SELECT *, EVENT_SCHEMA AS `Db`, EVENT_NAME AS `Name` FROM information_schema.`EVENTS` WHERE `EVENT_SCHEMA`='test';
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='information_schema';
SHOW TABLE STATUS FROM `information_schema`;
SHOW FUNCTION STATUS WHERE `Db`='information_schema';
SHOW PROCEDURE STATUS WHERE `Db`='information_schema';
SHOW TRIGGERS FROM `information_schema`;
SELECT *, EVENT_SCHEMA AS `Db`, EVENT_NAME AS `Name` FROM information_schema.`EVENTS` WHERE `EVENT_SCHEMA`='information_schema';
SELECT * FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='b' ORDER BY ORDINAL_POSITION;
SHOW INDEXES FROM `b` FROM `test`;
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE   CONSTRAINT_SCHEMA='test'   AND TABLE_NAME='b'   AND REFERENCED_TABLE_NAME IS NOT NULL;
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE   TABLE_SCHEMA='test'   AND TABLE_NAME='b'   AND REFERENCED_TABLE_NAME IS NOT NULL;
SHOW ENGINES;
SHOW COLLATION;
SHOW CREATE TABLE `test`.`b`;

[zufuliu]

I found the network type has changed, with the same libmysql.dll v5.7.31 or libmariadb.dll v10.4.13
HeidiSQL 6040: Connecting to x.x.x.x via MySQL (TCP/IP)
HeidiSQL 6056: Connecting to x.x.x.x via MariaDB or MySQL (TCP/IP)

[ansgarbecker]

That "MariaDB or MySQL" is just cosmetic stuff - not relevant here.

I have a gutt feeling this has to do with a code path depending on the server version. Please post the result of this query from the offending server:

SHOW VARIABLES LIKE 'version%'

[zufuliu]

version result:

[ansgarbecker]

Thanks. That's it: Heidi expects the word "mysql" somewhere in the version or version_comment variable, like it's the case here:

I'll have to change this detection to something else.