Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

MicroFix - Mysql copy Table Enum Default value without quote

roxwal's profile image roxwal posted 1 month ago in General Permalink

HeidiSQL 10.3.0.5906 - Mysql V 5.7.27

when using in HeidiSQL to function copy Table,

If a column ENUM have a default value, this is not wrapped.

CREATE TABLE `tmp`.`tttt_copy` (
    `aaa` ENUM('Y','N') NULL DEFAULT Y COLLATE 'utf8_general_ci'
)
 COLLATE 'utf8_general_ci' ENGINE=MyISAM ROW_FORMAT=Fixed;
/* Errore SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Y COLLATE 'utf8_general_ci'
)
 COLLATE 'utf8_general_ci' ENGINE=MyISAM ROW_FOR' at line 2 */

correct is DEFAULT 'Y' COLLATE not DEFAULT Y COLLATE

Tks W:-}

1 attachment(s):
  • 2020-03-03-15_12_28-mydb1_tmp_tttt_-HeidiSQL-10.3.0.5906
Code modification/commit 43aec42 from Ansgar Becker <anse@heidisql.com>, 1 month ago, revision 5909
Only MariaDB 10.2.27+ seems to support more expressions in column default value, and wraps string literals in single quotes. Use that to verify default value is not an expression. Fixes unquoted default values, described here: https://www.heidisql.com/forum.php?t=35746
ansgar's profile image ansgar posted 1 month ago Permalink

Please update to the next nightly build. I just added more logic for separating functions/expressions from text in column default values.

There are also differences in current MariaDB versions compared to MySQL. See https://mariadb.com/kb/en/information-schema-columns-table/ how "COLUMN_DEFAULT" is quoted.

MySQL only supports CURRENT_TIMESTAMP and similar in temporal columns, or are there more?

roxwal's profile image roxwal posted 1 month ago Permalink

HeidiSql 10.3.0.5909 Work fine

it has been correctly created

CREATE TABLE `tttt_copy` (
    `aaa` ENUM('Y','N') NULL DEFAULT 'Y' COLLATE 'utf8_general_ci',
    `bbb` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `Colonna 3` INT NULL
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=FIXED
;

Tks

W:-}

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