MySQL error 1064 syntax but everything seems fine
Im using xampp control panel and from there i start the process for apache and mysql. Then i go to mysql workbench and server status seems to be ok, here is some info
Host: Windows-PC
Socket: C:/xampp/mysql/mysql.sock
Port: 3306
Version 10.1.31-MariaDB mariadb.org binary distribution
Compiled For: Win32(32)
Configuratin File: unknown
Then everytime when i try to add the foreign key for my dummy schema like:
ALTER TABLE `puppies`.`animals`
ADD INDEX `Breed_idx` (`BreedID` ASC) VISIBLE;
;
ALTER TABLE `puppies`.`animals`
ADD CONSTRAINT `Breed`
FOREIGN KEY (`BreedID`)
REFERENCES `puppies`.`breeds` (`Breed`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
I get the following 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
'' at line 2
SQL Statement:
ALTER TABLE `puppies`.`animals`
ADD INDEX `Breed_idx` (`BreedID` ASC) VISIBLE
So what can i do so that xampp will start using mysql syntax over mariaDb?
Or if im wrong in my understanding of the problem, then what should i do so that i dont have to face this kind of issues again when using xampp?
Answer
Problem is the word VISIBLE, remove it and it will work.
Index are visible by default.
Your question: "If i remove VISIBLE it works just fine, so why did mysql workbench decided to add visible?"
My answer: The option to mark index invisible is not yet implemented in MariaDB (afaik!).
Update:
The syntax for MariaDB is different, please see this reference: https://jira.mariadb.org/browse/MDEV-7317