23

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?

  • 1
    you have an extra ; after VISIBLE; for the record the syntax is identical to mysql – ATechGuy May 17 '18 at 13:59
  • How exactly do you issue the above sql statements? Why do you have a blank line there (line 3)? Btw MariaDB is a MySQL fork. – Shadow May 17 '18 at 14:00
  • im using mysql workbench and it produces those script lines for sql. I tried removing " ; " but it didn work still giving the same error, any ideas what i could be doing wrong? –  May 17 '18 at 14:05
  • is there a way to override some of the rules in xampp for sql so that i can work just fine? –  May 17 '18 at 14:20
  • If i remove VISIBLE it works just fine, so why did mysql workbench decided to add visible? –  May 17 '18 at 14:25
  • @ATechGuy You can add as many `;` as you like. Where do you have the information from, that the syntax is identical? – Daniel W. May 17 '18 at 14:29
  • @DanFromGermany many places, but here is one. For 99% of common use cases mariadb is a drop in replacement for mysql. backward compatable https://softwareengineering.stackexchange.com/questions/120178/whats-the-difference-between-mariadb-and-mysql https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/ – ATechGuy May 17 '18 at 20:56
  • 1
    Did you configure Workbench for MySQL 8.0, yet you are running with MariaDB? You hit one of the differences between the two. – Rick James May 25 '18 at 21:24
  • "Compiled for 32-bit"?? That limits you to 4GB ! – Rick James May 25 '18 at 21:25

3 Answers3

35

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

Daniel W.
  • 31,164
  • 13
  • 93
  • 151
  • 1
    but is there a way to make mysql workbench understand that it shouldnt add syntax that is not implemented yet? –  May 17 '18 at 14:29
  • 2
    MySQL Workbench is for MySQL, MariaDB is a different thing, although it is a drop-in replacement, it might have differences. It is not called MariaDB Workbench :-) – Daniel W. May 17 '18 at 14:30
  • but thats exactly what i was thinking, is there a way to make xampp work for mysql db rather than mariaDb? Was trying to find something on the internet but non of the solutions seem to be up to date –  May 17 '18 at 14:32
  • You can try to remove MariaDB and install MySQL instead, please try this link: https://stackoverflow.com/questions/39654428/how-can-i-change-mariadb-to-mysql-in-xampp – Daniel W. May 17 '18 at 14:33
  • Im looking at that thread, and basically i have all implemented all the steps at the time when i posted my question. Like Mysql workbench was installed and everything, and then through there i was trying to make a table and got the errors that i posted. So is there a more specific way to solve this issue? –  May 17 '18 at 14:44
  • I can concur. MariaDB (still) does not offer (or ignore) the VISIBLE modifier. MySQL Workbench was made for, well, MySQL and not for MariaDB. Btw. the first time you connacted to MariaDB it complained about a version mismatch. You clicked that away but in fact it is a good indicator what is going on. I know this is an old thread but it is an old issue that might (https://jira.mariadb.org/browse/MDEV-22199) – theking2 Sep 27 '21 at 13:26
15

Just to add to those who are using Maria DB with MySQL Workbench, you don't need to install mysql. You can just change 'Default Target MySQL Version' from Preferences to 5.7 or 5.6, and the VISIBLE keyword will be removed by workbench. MySQL Workbench Preference Changes

Here is a link from mysql bugs https://bugs.mysql.com/bug.php?id=92269

HussoM
  • 1,272
  • 13
  • 7
  • 1
    this should be the accepted answer. Dropping it down to 5.7 fixed it for us. Using Mariadb 10.3 – Max Jul 08 '19 at 04:09
9

I am using MySQL Workbench and have same problem. Change in the Preferences but it did not work.

Solution: If you export forward-engineer the model you need to change the configuration on another place.

  1. Go to Model > Model Options
  2. Inside the Model Options, go to MySQL
  3. Then change the "Target MySQL Version" to 5.6

example here

user2461864
  • 91
  • 1
  • 3