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 39 · Accepted Answer · edited Feb 25 '14 at 13:50

39

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

482
1
4
13

answered Feb 25 '14 at 09:31

Ohlin

3,910
2
27
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,103
3
19
34

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 1 · Answer 4 · 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 5 · 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

609
6
18

answered Jan 18 '21 at 15:15

abhilash james

11
1

score 0 · Answer 6 · 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

23
8

score 0 · Answer 7 · 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 8 · answered May 08 '20 at 10:28

0

This error comes when you have a column name from one of the mysql keyword, try to run your queries by putting all your column names in `` this

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

In this example column name order is a keyword in mysql, so i had to put that in `` quotes.

answered May 08 '20 at 10:28

Anshul Sharma

842
3
9
32

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 0 · Answer 9 · answered Dec 15 '21 at 18:19

Im code in bash script

name=$(cat $file | jq -r ".["$i"].name")    
program_url=$(cat $file | jq -r ".["$i"].program_url")  
url=$(cat $file | jq -r ".["$i"].url")      
platform=$(cat $file | jq -r ".["$i"].platform")    
bounty=$(cat $file | jq -r ".["$i"].bounty")
sudo mysql  -u root -p "--password=4310260985" "programs"  -e "use programs;insert into website(name, program_url, url, platform, bounty) VALUES ('$name', '$program_url', '$url', '$platform', $bounty);"

discription: varible put into (') but $bounty not put into (') becuse varible $bounty is type=boolean

score 0 · Answer 10 · answered May 09 '22 at 06:14

0

-- create CREATE TABLE jobposition ( id int(10) NOT NULL, jobtype varchar(50) NOT NULL, positionname int(50) NOT NULL, description varchar(50) NOT NULL,

/*Data for the table jobposition */

insert into jobposition(id,jobtype,positionname,description) values

(1,'San Francisco',+1 650 219 4782,'Market Street'),

(2,'Boston',+1 215 837 0825,'Court Place' ),

(3,'NYC',+1 212 555 3000,'East 53rd Street' ),

(4,'Paris',+33 14 723 4404,'Rue Jouffroy' ),

(5,'Tokyo',+81 33 224 5000,'Kioicho'),

(6,'Sydney',+61 2 9264 2451,NULL,'Wentworth Avenue'),

(7,'London',+44 20 7877 2041,'Old Broad Street' );

-- fetch SELECT * FROM JOBPOSITION ;

ERROR 1064 (42000) at line 3: 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 'insert into jobposition(id,jobtype,positionname,description) values ' at line 10

answered May 09 '22 at 06:14

DANIEL

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 09 '22 at 18:39
This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/31726764) – Kuro Neko May 14 '22 at 07:57

score -1 · Answer 11 · answered Nov 27 '20 at 19:56

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

Please follow my step : change < to ( and >; to );

Just use: (

);

enter code here

` 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)
-> );`

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 `);`. – Ahx 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 12 · 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

18,869
22
90
106

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

12 Answers12

Linked