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

Question

While I am trying to insert a row to my table, I'm getting the following errors:

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 ''filename') 
VALUES ('san', 'ss', 1, 1, 1, 1, 2, 1, 1, 'sment', 'notes','sant' at line 1

please help me out.

mysql> desc risks;
+-----------------+--------------+------+-----+---------------------+----------------+
| Field           | Type         | Null | Key | Default             | Extra          |
+-----------------+--------------+------+-----+---------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL                | auto_increment |
| status          | varchar(20)  | NO   |     | NULL                |                |
| subject         | varchar(100) | NO   |     | NULL                |                |
| reference_id    | varchar(20)  | NO   |     |                     |                |
| location        | int(11)      | NO   |     | NULL                |                |
| category        | int(11)      | NO   |     | NULL                |                |
| team            | int(11)      | NO   |     | NULL                |                |
| technology      | int(11)      | NO   |     | NULL                |                |
| owner           | int(11)      | NO   |     | NULL                |                |
| manager         | int(11)      | NO   |     | NULL                |                |
| assessment      | longtext     | NO   |     | NULL                |                |
| notes           | longtext     | NO   |     | NULL                |                |
| submission_date | timestamp    | NO   |     | CURRENT_TIMESTAMP   |                |
| last_update     | timestamp    | NO   |     | 0000-00-00 00:00:00 |                |
| review_date     | timestamp    | NO   |     | 0000-00-00 00:00:00 |                |
| mitigation_id   | int(11)      | NO   |     | NULL                |                |
| mgmt_review     | int(11)      | NO   |     | NULL                |                |
| project_id      | int(11)      | NO   |     | 0                   |                |
| close_id        | int(11)      | NO   |     | NULL                |                |
| submitted_by    | int(11)      | NO   |     | 1                   |                |
| filename        | varchar(30)  | NO   |     | NULL                |                |
+-----------------+--------------+------+-----+---------------------+----------------+
21 rows in set (0.00 sec)

**mysql> INSERT INTO risks (`status`, `subject`, `reference_id`, `location`, `category`,
`team`, `technology`, `owner`, `manager`, `assessment`, `notes`,'filename')     VALUES 
('san', 'ss', 1, 1, 1, 1, 2, 1, 1, 'sment', 'notes','santu');**

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 ''filename') 
VALUES ('san', 'ss', 1, 1, 1, 1, 2, 1, 1, 'sment', 'notes','sant' at line 1

score 45 · Accepted Answer · edited Feb 25 '14 at 13:50

45

There are two different types of quotation marks in MySQL. You need to use ` for column names and ' for strings. Since you have used ' for the filename column the query parser got confused. Either remove the quotation marks around all column names, or change 'filename' to `filename`. Then it should work.

edited Feb 25 '14 at 13:50

santu47

612
1
5
13

answered Feb 25 '14 at 09:31

Ohlin

4,068
2
29
35

score 4 · Answer 2 · answered Feb 25 '14 at 09:20

4

Don't quote the column filename

mysql> INSERT INTO risks (status, subject, reference_id, location, category, team,    technology, owner, manager, assessment, notes,filename) 
VALUES ('san', 'ss', 1, 1, 1, 1, 2, 1, 1, 'sment', 'notes','santu');

answered Feb 25 '14 at 09:20

Deepak Rai

2,163
3
21
36

Boolment Software · Answer 3 · 2017-07-29T06:35:30.543

This Error comes due to same table exist in 2 database like you have a database for project1 and in which you have table emp and again you have another database like project2 and in which you have table emp then when you try to insert something inside the database with-out your database name then you will get an error like about

Solution for that when you use mysql query then also mention database name along with table name.

OR

Don't Use Reserved key words like KEY as column name

score 3 · Answer 4 · edited Oct 12 '22 at 12:17

3

This error comes when you have a column that is one of the MySQL keywords. Try to run your queries with all of your column names in back ticks. For example:

insert into test (`sno`, `order`, `category`, `samp`, `pamp`, `method`) values(1, 1, 'top', 30, 25, 'Total');

Column name order is a keyword in MySQL, so I had to put that in `` quotes.

edited Oct 12 '22 at 12:17

Stephen Ostermiller

23,933
14
88
109

answered May 08 '20 at 10:28

Anshul Sharma

1,018
3
17
39

Generally while naming columns in our table we do not keep keywords in mind, so better put all the columns in **``** quotes. – Anshul Sharma May 14 '20 at 04:45

score 1 · Answer 5 · answered May 04 '15 at 12:01

C:\xampp>mysql -u root -p mydatabase < C:\DB_Backups\stage-new.sql
Enter password:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the ma
nual that corresponds to your MySQL server version for the right syntax to use n
ear 'stage-new.sql

----lot of space----

' at line 1

The reason was when I dumped the DB I used following command :

mysqldump -h <host> -u <username> -p <database> > dumpfile.sql
dumpfile.sql

By mistaken dumpfile.sql added twice in the syntax.

Solution : I removed the dumpfile.sql text added to first line of the exported dumpfile.

score 1 · Answer 6 · edited Jan 19 '21 at 06:33

1

Save code below as command.sql

USE restaurant
TRUNCATE `orders`;
TRUNCATE `order_items`;

Then run

mysql -u root <comands.sql> output.tab

edited Jan 19 '21 at 06:33

Ardiya

677
6
19

answered Jan 18 '21 at 15:15

abhilash james

11
1

score 0 · Answer 7 · answered Dec 04 '18 at 04:15

0

This solution is for windows:

Open command prompt in Administrator Mode.
Goto path: C:\Program Files\MySQL\MySQL Server 5.6\bin
Run below command: mysqldump -h 127.0.01 -u root -proot db table1 table2 > result.sql

answered Dec 04 '18 at 04:15

Ajay Sharma

33
9

score 0 · Answer 8 · answered Aug 19 '19 at 08:57

I encountered this same error: 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 ', completed)' at line 1

This was the input I had entered on terminal: mysql> create table todos (description, completed);

Solution: For each column type you must specify the type of content they will contain. This could either be text, integer, variable, boolean there are many different types of data.

mysql> create table todos (description text, completed boolean);

Query OK, 0 rows affected (0.02 sec)

It now passed successfully.

score 0 · Answer 9 · edited Oct 12 '22 at 11:45

In my case the values below were first written ( multiple lines) as follows in the sql dump file:

-- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '33ce20a2-ad12-11ec-bdac-3238639baa41:1-9133,
7dd21400-4a0f-11ec-a3f9-aa2aaded1a97:1-5031,
a8ffc1f8-6888-11eb-9a01-3671a0dcaa62:1-9355,
dce51f13-7f37-11ea-9882-627116a92c36:1-6177,
f1115a53-0a94-11ed-8ccd-923c66acbd99:1-1584';

Fixed after making them all in one line:

*-- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '33ce20a2-ad12-11ec-bdac-3238639baa41:1-9133,7dd21400-4a0f-11ec-a3f9-aa2aaded1a97:1-5031,a8ffc1f8-6888-11eb-9a01-3671a0dcaa62:1-9355,dce51f13-7f37-11ea-9882-627116a92c36:1-6177,f1115a53-0a94-11ed-8ccd-923c66acbd99:1-1584';*

score 0 · Answer 10 · answered Nov 05 '22 at 14:03

Actually the problem can be solved in 2 methods

1.Change filename to something else as SQL considers some words as keywords that will solve the issue 2.Simply Change the Quote Style to ''(this), but I would suggest the first option is better for the long run.

I hope that helps!!

score 0 · Answer 11 · edited May 13 '23 at 03:29

0

CREATE TABLE information (
 id INT(11) NOT NULL AUTO_INCREMENT,
 name VARCHAR(30) NOT NULL,
 age INT(10) NOT NULL,
 salary INT(100) NOT NULL,
 address VARCHAR(100) NOT NULL,
 PRIMARY KEY(id)
 );

edited May 13 '23 at 03:29

Suraj Rao

29,388
11
94
103

answered May 13 '23 at 01:50

Giselia Cirino

1

score -1 · Answer 12 · edited Oct 12 '22 at 12:14

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

Very simple question that you can solved it easily: change < to ( and >; to );

Just use:

CREATE TABLE information (
-> id INT(11) NOT NULL AUTO_INCREMENT,
-> name VARCHAR(30) NOT NULL,
-> age INT(10) NOT NULL,
-> salary INT(100) NOT NULL,
-> address VARCHAR(100) NOT NULL,
-> PRIMARY KEY(id)
-> );

edited Oct 12 '22 at 12:14

Stephen Ostermiller

23,933
14
88
109

answered Nov 27 '20 at 19:56

Ruman

11
1

2

Maybe the answer will become even better if you just design it. I mean there is a `enter code` section which you may remove. There is a `);`. – Ahmet Nov 27 '20 at 22:32
1

This is the better solution for this problem because this is updated result ! if you write >; it than you will face a problem But if you write this ); it will success! – Ruman Nov 28 '20 at 19:21

score -2 · Answer 13 · edited Nov 10 '19 at 04:47

-2

Execute dump query in terminal then it will work

mysql -u root -p  <Database_Name> > <path of the input file>

edited Nov 10 '19 at 04:47

Stephen Kennedy

20,585
22
95
108

answered May 19 '17 at 15:39

Manikyam

11

fixed it for me . Actual helpful message – Proviste Oct 13 '20 at 19:05

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

13 Answers13

Linked