SQL Error 1064: You have an error in your SQL syntax

Question

I cannot, for the life of me, figure out what's wrong. I created the ERD and relationships in MySQL workbench and it spit this code out. I forward engineered it to my dbms and i get this error.

Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that 
corresponds to your MariaDB server version for the right syntax to use near ')
ENGINE = InnoDB' at line 10
SQL Code:
        -- -----------------------------------------------------
        -- Table `deadiade_golfbag`.`users`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `deadiade_golfbag`.`users` (
          `id` INT NOT NULL AUTO_INCREMENT,
          `first_name` VARCHAR(150) NOT NULL,
          `last_name` VARCHAR(250) NULL,
          `professional` BINARY(1) NOT NULL DEFAULT 0,
          PRIMARY KEY (`id`, `professional`),
          UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
        ENGINE = InnoDB

SQL script execution finished: statements: 5 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

score 2 · Answer 1 · answered Sep 08 '20 at 15:58

MariaDB does not support invisible indexes - unlike MySQL, which added this feature in version 8.0.

The parser of the MariaDB 10.5 seems to allow VISIBLE (while it fails on INVISIBLE), but I suspect that it basically ignores it. In earlier versions, it just fails on VISIBLE.

VISIBLE is the default setting anyway, so you can just leave that keyword apart (as well as ASC on the indexed column, which also is the default):

CREATE TABLE IF NOT EXISTS `deadiade_golfbag`.`users` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(150) NOT NULL,
    `last_name` VARCHAR(250) NULL,
    `professional` BINARY(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`, `professional`),
    UNIQUE INDEX `id_UNIQUE` (`id`) 
) ENGINE = InnoDB

Note that it does not make sense to have a primary key on two columns and and a unique index on one of these two: the unique key is more restrictive that the primary key.

I suspect the setting is being added automatically by `mysqldump`. Do you know if there's an option to make it omit this? — Barmar, Sep 08 '20 at 16:01
@Barmar: I don't know. A quick glance at the documentation does not show an obvious option. If that's from phpmyadmin, maybe this helps: https://stackoverflow.com/questions/53434830/how-to-disable-invisible-index-option-in-mysqlworkbench? — GMB, Sep 08 '20 at 16:57

SQL Error 1064: You have an error in your SQL syntax

1 Answers1