MySQL error 1064 syntax but everything seems fine

mysql error 1064 insert
mysql error 1064 create table
you have an error in your sql syntax near '' at line 1
error 1064 (42000)
error 1064 mysql workbench
sql error: 1064, sqlstate: 42000
mysql workbench visible error
mysql visible keyword error

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?

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

MySQL Error 1064: You have an error in your SQL syntax, So follow along and learn more about what the 1064 error is, some likely I replied to your earlier post not knowing that you had posted everything below. This code works perfectly fine in mysql but crashes when running  The MySQL error 1064 is a syntax error, meaning that the MySQL is unable to understand the command you are issuing, because your command is not a valid one within the Structured Query Language or SQL.

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.

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

Error 1064: How to Fix SQL Syntax Error 1064?, At first, the syntax errors seem very tedious while working with SQL database programs. But, one can easily understand the errors as they are descriptive enough to find possible solutions. The possible reasons due to which MySQL faces syntax error – 1064 are mentioned here: Learn moreOK, got it. ERROR 1064 (42000): 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 'WHERE id=101 SET name='foo" at line 1 MySQL is telling us that everything seemed fine up to the word WHERE , but then a problem was encountered.

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

How to Fix the MySQL 1064 Error (5 Methods), Learn how to fix "the MySQL 1064 error" message once and for all. This is a syntax error, which means MySQL isn't able to understand your command. We suggest using the MySQL Manual as a reference while you do so, double-​checking anything you're not sure about. As you might Yes, it was fine! Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Mysql Error 1064, Warning: [mysql error 1064] You have an error in your SQL syntax; check I downloaded the manual installation file and replaced everything You don't have MySQL, you have MariaDB, and likely your MariaDB version is too I did not delete that afterward and it works in the same server today just fine. You were trying to run multiple sql statements with a single mysql_query() call which is not supported. If you need to run multiple queries in a single call, then consider the use of mysqli_multi_query() .

CREATE VIEW Fails with ERROR 1064 (42000), I am unable to do anything with views from the documentation (CREATE, SHOW, etc). The CREATE table and INSERT works fine, but the view is not ERROR 1064 (42000): You have an error in your SQL syntax; check the  I have the same problem.. Im glad that someone has my same problem.. It was driving me crazy thinking that was some problem of access.. but I tested to "union" just two Selects, and running the entire query on mysql, etc.. and those worked.. so definitely the problem is when running a query with more than one "union" statement (two Select and one Union) in msaccess using myodbc to connect to

MySQL Error 1064 when trying to create a Matrix, When I test this on my localhost, which has PHP 7.1.5 and MySQL 5.6.35 running Valet, everything seems to work fine so I'm not sure Adding the following to db.​php fixed the error: My guess is that there was some permission issue that was stopping it from getting those automatically, but I'm not entirely  I understand testing with the command line is good for bug-hunting purposes. The solution needs to work with a GUI client, unless you recommend all DDL, DML be entered through the Command line.

Comments
  • you have an extra ; after VISIBLE; for the record the syntax is identical to mysql
  • 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.
  • 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?
  • is there a way to override some of the rules in xampp for sql so that i can work just fine?
  • Did you configure Workbench for MySQL 8.0, yet you are running with MariaDB? You hit one of the differences between the two.
  • but is there a way to make mysql workbench understand that it shouldnt add syntax that is not implemented yet?
  • 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 :-)
  • 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
  • You can try to remove MariaDB and install MySQL instead, please try this link: stackoverflow.com/questions/39654428/…
  • 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?
  • this should be the accepted answer. Dropping it down to 5.7 fixed it for us. Using Mariadb 10.3