Importing a database script into my database throws an #1064 error

Question

could you please help me? I bought a domain just for learning databases etc. and I created my model of a database in MySQL Workbench. I generated a script and tried importing it into my database using phpMyAdmin. This is the script:

CREATE TABLE IF NOT EXISTS `knight` (
    `idKnight` INT NOT NULL AUTO_INCREMENT,
    `strength` INT NOT NULL DEFAULT 1,
    `agility` INT NOT NULL DEFAULT 1,
    `vitality` INT NOT NULL DEFAULT 1,
    `attack` INT GENERATED ALWAYS AS (agility*strength) STORED,
    `defense` INT GENERATED ALWAYS AS (vitality*strength) STORED,
    `idUser` INT NOT NULL,
    `idTavern` INT NULL,
    PRIMARY KEY (`idKnight`),
    INDEX `fk_user_idx` (`idUser` ASC),
    INDEX `fk_tavern_idx` (`idTavern` ASC),
    CONSTRAINT `fk_user` FOREIGN KEY (`idUser`)
        REFERENCES `user` (`idUser`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `fk_tavern` FOREIGN KEY (`idTavern`)
        REFERENCES `tavern` (`idTavern`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
ENGINE = InnoDB

And this is the 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 'GENERATED ALWAYS AS (agility*strength) STORED, defense INT GENERATED ALWAYS ' at line 6

Now the question is how do I synchronize the MySQL Workbench version of a database for which the script is generated, and the database itself. The database is Inno DB.

Thanks for your help

EDIT: MySQL version of my server is: 5.6.28

Answer 1

This type of problem shows the importance of using the same version of database in development as you will eventually use when you deploy to your production server. So you don't get surprised by incompatibilities.

You can run that script against your MySQL 5.6 server only if you avoid SQL features introduced in more recent versions of MySQL. This includes generated columns, which were first introduced in MySQL 5.7.

So you need to remove these columns, or else change them to plain INT columns, without the generated option.

`attack` INT GENERATED ALWAYS AS (agility*strength) STORED,
`defense` INT GENERATED ALWAYS AS (vitality*strength) STORED,

If you need those columns in query result sets, you have a few alternative solutions:

Add them as expressions in the select-list of a SELECT query:

SELECT (agility*strength) AS `attack`, (vitality*strength) AS `defense`
FROM `knight` ...

Or you could create a VIEW to encode a query with those expressions.

Or you could add those columns as plain integers, and write TRIGGERs on INSERT and UPDATE to keep them in sync with the other columns.

MySQL 5.6.28 was released in December 2015, and the whole 5.6 branch is past its end-of-support date. That means if any security bugs are discovered from now on, they won't be fixed. Besides, you're already using an outdated release of 5.6, with many bugs. The last 5.6 release was 5.6.51 in January 2021.