Beginner mysql - Syntax error creating tables

Question

I'm very new to SQL and I found similar questions like this, but I made the suggested changes and I'm still receiving the same error. Can anyone prompt me in the right direction on why I might be receiving this error still?

Any suggestions or help would be greatly appreciated. Thank you.

mysql> USE famous_scientists;
Database changed
    -> CREATE TABLE scientists (
    ->     id INT(1) NOT NULL auto_increment,
    ->     name VARCHAR(255) NOT NULL,
    ->     discovery VARCHAR(255) NOT NULL,
    ->     year_of_birth INT(4) NOT NULL,
    ->     year_of_death INT(4) NULL,
    ->     PRIMARY KEY (id)
    -> ) AUTO_INCREMENT=1;
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 '
CREATE TABLE scientists (
    id INT(1) NOT NULL auto_increment,
    name V' at line 1

Answer 1

mysql> USE famous_scientists;
Database changed
    ->

This ^^^^ arrow is wrong. It should not be here.

You should see this:

mysql> USE famous_scientists;
Database changed
mysql>

The fact that you didn't suggests that something strange was going on with the data you were typing or pasting in. This isn't really a syntax error, it's just blatant confusion.

The -> arrow means the CLI thinks you are already in the middle of typing or pasting a SQL statement, when clearly you did not intend to be.

The appropriate thing to do next is Control-C and start over.

The error message itself is very common -- any kind of syntax error will trigger it, but you'll save yourself a lot of mental anguish if you internalize exactly what it means. It seems cryptic, but it really isn't.

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 '...'

Whatever is inside the single quotes after the word near is the part of the query that had not yet been parsed when the error occurred.

In other words, what you see is the beginning of the exact place -- left-to-right -- where the query stopped making syntactic sense to the server.

Importantly, the specific error being reported is never any later than the beginning of the quoted string. It is also not likely to be before, because anything before this spot in the query, the server believes to have been syntactically valid. You might have an error earlier in your query that does not do what you intended, but it isn't a syntax error from the servers perspective.

Examples:

mysql> SELECT * * FROM t1;
ERROR 1064 (42000): ...right syntax to use near '* FROM t1' at line 1

The second * is what confused it. What's after that doesn't matter, because the second * is syntactically invalid.

mysql> SELECT * FROM FROM t1;
ERROR 1064 (42000): ...right syntax to use near 'FROM t1' at line 1

Same thing... the second FROM is invalid. Parsing stopped there, because it doesn't make sense.

Let's say I want to drop a table, but I forget to type the word DROP.

mysql> TABLE t1;
ERROR 1064 (42000): ...right syntax to use near 'TABLE t1' at line 1

The server says "wait, you can't start a query with the keyword TABLE." The first thing in the query was wrong.

Note also that I do not actually have a table named t1, but the server is not to the point of checking that. The first unexpected thing that is encountered is what stops the process.

This example is just like yours... the server seems to be complaining about the very beginning of the query... so looking any deeper into your query is entirely unnecessary for the moment.

So this error...

...the right syntax to use near '
CREATE TABLE scientists (

...tells you immediately that the server was confused by CREATE or maybe some control characters or something that looks like whitespace before it. But the problem is nothing later. Nothing after CREATE has yet been evaluated.

How is that possible? You're right, it's not... unless there was something that got entered before that... and we know that's the case because of the -> prompt. What it was or exactly how it got there is impossible to speculate.

One last example, and this one tends to be the most confusing... but it makes sense once you understand everything above.

mysql> SELECT * FROM user WHERE;
ERROR 1064 (42000): ...right syntax to use near '' at line 1

Wait, what? What does near '' mean?

Remember the part about the part in quotes beginning with the first thing that confused the server? What has confused the server in this case is that it has parsed everything, all the way to the end of my query, and found that my query makes no sense. It's confused because, while interpreting my query, it needed more but it found nothing more. Yet, my query can't possibly be complete, because I didn't put anything after WHERE. So near '' means after the end of everything. The parser (or perhaps it's the lexer, I digress) has run out of things to examine, and yet the query as it stands is incomplete, so the error is "near" the... nothing. It's near the nothing that remains unparsed at the end of the query.

If there is anything actually wrong with your query, this error is not telling you about that. It's essentially not being parsed at all.

But now you know how to "think like MySQL" when facing ERROR 1064 (42000) and trying to figure out what the server is trying to tell you.

Answer 2

 CREATE TABLE scientists (
       id INT NOT NULL auto_increment,
         name VARCHAR(255) NOT NULL,
         discovery VARCHAR(255) NOT NULL,
         year_of_birth INT(4) NOT NULL,
         year_of_death INT(4) NULL,
         PRIMARY KEY (id)
     )

Answer 3

Your code works fine. See the SQL Fiddle.

If you have an error, it is likely to be above this code. The error message is saying that the create is not recognized. Sometimes bad characters can creep in.

Answer 4

try:

mysql> USE famous_scientists;
Database changed
    CREATE TABLE scientists (
         id INT NOT NULL auto_increment,
         name VARCHAR(255) NOT NULL,
         discovery VARCHAR(255) NOT NULL,
         year_of_birth INT(4) NOT NULL,
         year_of_death INT(4) NULL,
         PRIMARY KEY (id)
     ) AUTO_INCREMENT=1;