Bug #105169 Cannot alter table on MySQL workbench when a field has uuid() as default value
Submitted: 8 Oct 2021 8:50 Modified: 12 Oct 2021 10:23
Reporter: Andrea Palmate' Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.26, 8.0.34 OS:Windows (Microsoft Windows 10 Pro)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[8 Oct 2021 8:50] Andrea Palmate'
Description:
If you add a field that has uuid() function as default value (via ALTER TABLE) you cannot edit the table anymore.
You will get a DDL error and the table is no more editable graphically

How to repeat:
Add a field that has uuid() as default value and try to alter the table via workbench
[12 Oct 2021 7:33] MySQL Verification Team
Hello Andrea Palmate,

Thank you for the report and feedback.
I quickly tried with a dummy table with uuid() as default value for a column and attempted to "ALTER.." from WB interface but not seeing any issues. Issue seen only after making changes to that column with default UUID value which results in syntax error. 

- MySQL Workbench on Windows 10 connected to the MySQL instance 8.0.26 running locally.

create database bug105169;
use bug105169;
CREATE TABLE bug105169(id CHAR(36) PRIMARY KEY DEFAULT (uuid()), col2 INT, col3 BIGINT);
INSERT INTO bug105169 value(DEFAULT, 1, 1);

- WB -> Schemas -> bug105169 -> Tables -> bug105169 <- right click and select "Alter Table.."

I can confirm that table is opened from above steps and no DDL issues reported.

If I make any minor changes, and attempt to update then it results in syntax error. E.g changed id char(36) to id char(37( -

ALTER TABLE `bug105169`.`bug105169` 
CHANGE COLUMN `id` `id` CHAR(37) NOT NULL DEFAULT uuid() ;

-
Operation failed: There was an error while applying the SQL script to the database.
Executing:
ALTER TABLE `bug105169`.`bug105169` 
CHANGE COLUMN `id` `id` CHAR(37) NOT NULL DEFAULT uuid() ;

ERROR 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 'uuid()' at line 2
SQL Statement:
ALTER TABLE `bug105169`.`bug105169` 
CHANGE COLUMN `id` `id` CHAR(37) NOT NULL DEFAULT uuid()

In any case, please provide exact create table which is causing issue at your end. Thank you!

regards,
Umesh
[12 Oct 2021 9:28] Andrea Palmate'
CREATE TABLE `Users` (
  `UserID` int(11) NOT NULL AUTO_INCREMENT,
  `Email` varchar(128) DEFAULT NULL,
  `Username` varchar(128) NOT NULL,
  `Password` varchar(128) NOT NULL,
  `Status` int(11) NOT NULL DEFAULT 0,
  `UserLevelID` int(11) NOT NULL,
  `LastAccessTime` datetime DEFAULT NULL,
  `APIPlanID` int(11) DEFAULT NULL,
  `CompanyName` varchar(128) DEFAULT NULL,
  `Address1` varchar(128) DEFAULT NULL,
  `Address2` varchar(128) DEFAULT NULL,
  `ZipCode` varchar(45) DEFAULT NULL,
  `City` varchar(128) DEFAULT NULL,
  `CountryID` int(11) DEFAULT NULL,
  `CanBeDisabled` int(1) NOT NULL DEFAULT 1,
  `TestUser` int(1) NOT NULL DEFAULT 0,
  `Image` longblob DEFAULT NULL,
  `2FAEnabled` int(1) NOT NULL DEFAULT 0,
  `UUID` varchar(64) DEFAULT (uuid()),
  PRIMARY KEY (`UserID`),
  UNIQUE KEY `Username_UNIQUE` (`Username`),
  UNIQUE KEY `Email_UNIQUE` (`Email`),
  KEY `fk_user_userlevel_idx` (`UserLevelID`),
  KEY `fk_user_apiplan_idx` (`APIPlanID`),
  KEY `fk_user_countries_idx` (`CountryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Try with this table. I get an error when use DEFAULT uuid() without additional parenthesis. I mean using (uuid()).
When trying to alter table i get a DDL error and the DDL is created without parenthesis:

  `UUID` varchar(64) DEFAULT uuid(),

Don't know if this can be the problem in the parser
[12 Oct 2021 10:01] MySQL Verification Team
Thank you for the details, however I'm not seeing any issues with the provided table. I'm able to edit/alter table (no graphical error but syntax error which I mentioned in my previous note i.e. syntax error due to missing parenthesis in ALTER..). Joining the screenshot shortly.

Is there anything else which I'm missing? Please let me know.

regards,
Umesh
[12 Oct 2021 10:01] MySQL Verification Team
WB 8.0.26 screenshot

Attachment: 105169.png (image/png, text), 83.18 KiB.

[12 Oct 2021 10:02] MySQL Verification Team
Also, could you please attach the exact screenshot of the error that you are seeing? Thank you!
[12 Oct 2021 10:10] Andrea Palmate'
Well,
i got the problem. It was my fault since I have used MySQL workbench to alter that table that is using a MariaDB server.. while on MySQL server it seems to works correctly. So sorry for this but at least we have found another problem :)

But I have also an error when trying to export any MySQL table (tested on MySQL Server..) and clicking on advance tab (I've found this problem when trying to create the DDL). Is this already known? Or I should file another bug report?
[12 Oct 2021 10:23] MySQL Verification Team
>>Well,
>>i got the problem. It was my fault since I have used MySQL workbench to alter that table that is using a MariaDB server.. while on MySQL server it seems to works correctly. So sorry for this but at least we have found another problem :)

I'll verify this because even though I'm able to "Alter Table.." but any changes to the column which has "uuid()" as default value causing syntax error for the ALTER table operation.

>>But I have also an error when trying to export any MySQL table (tested on MySQL Server..) and clicking on advance tab (I've found this problem when trying to create the DDL). Is this already known? Or I should file another bug report?

Please report a new bug.

Thank you so much for confirming that "I have used MySQL workbench to alter that table that is using a MariaDB server.. while on MySQL server it seems to works correctly"

regards,
Umesh
[27 Nov 2023 11:32] MySQL Verification Team
Bug #113231 marked as duplicate of this one.