MySQL table creation error in workbench

Question

I'm new to MySQL and I'm running version 5.7.19-log (At least thats what I'm getting from command line client) with Workbench 6.3.9 Community edition. My problem is I'm trying to create a simple table with auto-generated id using the UI, however when I execute the generated script it throws me the following error:

  Operation failed: There was an error while applying the SQL script to the database.
Executing:
CREATE TABLE `travelportaldb`.`user` (
  `user_id` INT GENERATED ALWAYS AS () VIRTUAL,
  `name` VARCHAR(45) NOT NULL,
  `account_id` VARCHAR(15) NULL,
  `account_currency` CHAR(3) NULL,
  `account_balance` DECIMAL NULL,
  PRIMARY KEY (`user_id`));

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 ') VIRTUAL,
  `name` VARCHAR(45) NOT NULL,
  `account_id` VARCHAR(15) NULL,
  `ac' at line 2
SQL Statement:
CREATE TABLE `travelportaldb`.`user` (
  `user_id` INT GENERATED ALWAYS AS () VIRTUAL,
  `name` VARCHAR(45) NOT NULL,
  `account_id` VARCHAR(15) NULL,
  `account_currency` CHAR(3) NULL,
  `account_balance` DECIMAL NULL,
  PRIMARY KEY (`user_id`))

This is the script that it generated:

CREATE TABLE `travelportaldb`.`user` (
  `user_id` INT GENERATED ALWAYS AS () VIRTUAL,
  `name` VARCHAR(45) NOT NULL,
  `account_id` VARCHAR(15) NULL,
  `account_currency` CHAR(3) NULL,
  `account_balance` DECIMAL NULL,
  PRIMARY KEY (`user_id`));

I have no idea what's wrong. I'm from the oracle background and the query seems to be fine except the auto generated apart which I have no idea about.

Please help.

Thanks.

I haven't studied virtual columns yet but... does it make sense to have an empty definition? — Álvaro González
– Álvaro González, Commented May 16, 2017 at 15:51
Not sure what Workbench is trying to do. Auto increment in Mysql should be something like: INT AUTO_INCREMENT — Leonardo Chaia
– Leonardo Chaia, Commented May 16, 2017 at 15:52
INT GENERATED ALWAYS AS (expression) <- expects non-empty expression. See here about generated columns: dev.mysql.com/doc/refman/5.7/en/… — poncha
– poncha, Commented May 16, 2017 at 15:53
So, I should use auto increment? That worked when I manually changed it to auto_increment. Funny Workbench didn't allow me this option. It's only showing me the generated option. — zhaider
– zhaider, Commented May 16, 2017 at 16:21

Mike Lischke · Accepted Answer · 2017-05-18 08:15:18Z

2

When you switch on the generated flag in MySQL Workbench the column details fields will change a bit to allow setting the required options for a generated column:

Most important here is the Expression field. For a generated column you have to specify an expression which is used for generation. Obviously you didn't do that and hence in your generated SQL the column definitions are wrong (empty parentheses, where an expression should be).

PS: You can also see the used server version in the Session tab page:

edited May 18, 2017 at 8:15

answered May 17, 2017 at 6:56

Mike Lischke

54.4k18 gold badges142 silver badges210 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL table creation error in workbench

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related