0

i just started learning mysql. I created schema and added few things, here it is:

CREATE TABLE `twitter`.`tweets` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `body` TEXT NOT NULL,
  `created` DATE NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
ENGINE = InnoDB;

But when i apply i am getting this 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 6
Bill Karwin
  • 499,602
  • 82
  • 638
  • 795
Edin Osmic
  • 83
  • 1
  • 7
  • Look at this post: https://stackoverflow.com/a/70647083/9629503 - I believe this will answer your question. :-) – Jeppe Spanggaard Apr 05 '22 at 13:55
  • maybe this can help https://stackoverflow.com/questions/52785125/mysql-workbench-error-in-query-1064-syntax-error-near-visible-at-line-1 – Kevin Alejandro Figuera Apr 05 '22 at 13:55
  • Your statement works fine in MySQL 8, but it doesn't inn 5.7 and below. What version of MySQL do you have installed? – Jetto Martínez Apr 05 '22 at 13:56
  • MySQL Workbench 8.0CE, i guess it is okay? But there is one problem when i am opening mysql, it is giving me a warning. It says 'Connection Warning (Local instance MySQL80) Mysql workbench is developed and tested for mysql versions 5.6, 5.7 and 8.0, for server older than 5.6 use Workbenck version 6.3'. But when i go to control panel and check for server version it says 8.0 – Edin Osmic Apr 05 '22 at 14:03
  • 1
    Does the statement work with `VISIBLE` removed? Your version may not support that keyword. My understanding is that the main advantage of `VISIBLE` is to allow you to quickly switch the index to `INVISIBLE` to easily assess whether the index is needed. I don't believe it does anything else in terms of your table or the indexing thereof. – pjd Apr 05 '22 at 15:02
  • It looks like the comments from Jeppe and Kevin above also point to the `VISIBLE` keyword. – pjd Apr 05 '22 at 15:06
  • You are using MariaDB (it says so in the syntax error), but MariaDB does not support the `VISIBLE` keyword. In MariaDB they introduced a similar feature, but they use the `IGNORE` keyword instead of `VISIBLE`. Go figure. See this answer: [MySQL error 1064 syntax but everything seems fine](https://stackoverflow.com/questions/50393245/mysql-error-1064-syntax-but-everything-seems-fine) – Bill Karwin Apr 05 '22 at 15:29
  • 1
    Here are a couple of tips: (1) The server you use (MariaDB in your case) determines the SQL syntax supported. The client (MySQL Workbench) is irrelevant for syntax. (2) MariaDB is not compatible with MySQL. MariaDB forked from MySQL in 2010, and both products have been gradually adding features since then. They are different products now. – Bill Karwin Apr 05 '22 at 15:34
  • Okay, pjd's answer worked. I removed VISIBLE keyword and i was able to set a table succesfully. Thank you pjd. But i am a little bit confused rn with Bill's answer. I dont get it how am i using MariaDB??? – Edin Osmic Apr 05 '22 at 16:34
  • To be more clear, i was following tutorial How to use MySQL with NodeJS, I downloaded mysql, created schema and table so i am wondering where MariaDB comes from now? – Edin Osmic Apr 05 '22 at 16:47
  • Are you connecting to a database that is on your own machine or on a remote machine? I am wondering if you installed MySQL Workbench locally to connect to a database server that is actually a MariaDB server. When you are logged in to the database, type `STATUS;`. If you are connected to MariaDB I think one of the lines of output will say MariaDB. More definitively, if you are using Linux, log in to your database from the command line and it will tell you right in the terminal as soon as you log in if you are connected to MySQL or MariaDB. – pjd Apr 06 '22 at 00:49
  • I am connecting on my own machine, and yes i have installed MySQL locally. When i type status server version says 5.5.5-10.4.14-MariaDB mariadb.org binary distribution. – Edin Osmic Apr 06 '22 at 06:23
  • I would say it sure looks like you are connecting to a MariaDB database and not MySQL. – pjd Apr 06 '22 at 15:39
  • try `UNIQUE KEY` – csaw Apr 06 '22 at 15:53

0 Answers0