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.

MariaDB cannot alter table with generated column (workaround)

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

I had problems with generated columns in HeidiSQL 11.2 and 11.1.

The following example script works:

CREATE TABLE table22 (

b VARCHAR(32),
 d VARCHAR(5) AS (left(b,5)) PERSISTENT

);

When I try to use the generated create script below (or try to alter the table in HeidiSQL). I get an error. Reason seems to be that HeidiSQL (?) adds DEFAULT NULL and COLLATE '...' for the generated column which provoke the error. When I remove these two in script or change the values in HeidiSQL it works - but HeidiSQL again adds them to the table definition, so next time create or alter will again fail.

CREATE TABLE table22 (

`b` VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`d` VARCHAR(5) **DEFAULT NULL** AS (left(`b`,6)) stored **COLLATE 'utf8_general_ci'**

) COLLATE='utf8_general_ci' ENGINE=InnoDB ;

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




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.