3 Replies - 218 Views - Last Post: 11 August 2017 - 08:31 AM

#1 roparzhhemon  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 19
  • Joined: 06-December 15

Syntax error in MySQL table deletion command ?

Posted 11 August 2017 - 08:12 AM

My subtitles.sql file has the following two commands :

DROP TABLE IF EXISTS `javaee`.subtitles`; 
CREATE TABLE `javaee`.`subtitles` ( 
    `mainIndex` INT NOT NULL AUTO_INCREMENT , 
	`blockIndex` INT NOT NULL ,
	`timeInterval` VARCHAR(29) NOT NULL ,
	`originalText` VARCHAR(100) NOT NULL , 
	`translatedText` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`mainIndex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;




When I do
 source substitles.sql 
in my MySQL console, I get the following error message :

ERROR 1064 (42000) at line 2 in file: '/Users/ewandelanoy/Documents/Web_Projects/workspace/SubtitleTranslator/WebContent/WEB-INF/subtitles.sql': 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 `javaee`.`subtitles` ( 
    `mainIndex` INT NOT NULL AUTO_INCRE' at line 1



If I remove the "DROP TABLE .." line in the file, however, the creation command is correctly executed.
So it seems that the problem comes from this "DROP TABLE" instruction, but I copy-pasted it from some working third-party code, so I fail to see what's wrong with it.

Is This A Good Question/Topic? 0
  • +

Replies To: Syntax error in MySQL table deletion command ?

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4138
  • View blog
  • Posts: 13,073
  • Joined: 08-June 10

Re: Syntax error in MySQL table deletion command ?

Posted 11 August 2017 - 08:14 AM

there's a quote missing.
Was This Post Helpful? 1
  • +
  • -

#3 roparzhhemon  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 19
  • Joined: 06-December 15

Re: Syntax error in MySQL table deletion command ?

Posted 11 August 2017 - 08:18 AM

View PostDormilich, on 11 August 2017 - 08:14 AM, said:

there's a quote missing.


Indeed. Works now that I corrected it, I feel a little ashamed about that one, thank you for your patience.
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4138
  • View blog
  • Posts: 13,073
  • Joined: 08-June 10

Re: Syntax error in MySQL table deletion command ?

Posted 11 August 2017 - 08:31 AM

Since you seem to have little experience debugging SQL ...

- the SQL snippet shown in the error message is the part after the SQL parser errors out, hence the problem must be immediately or short before that position

- always have the manual at hand (e.g. https://dev.mysql.co...ipulation.html) where you can look up the SQL syntax

And a tip from my experience, leave out the backtick quotes where possible. although you might struggle with the use of reserved words at first, it makes the code more robust in the long run (e.g. when you'd work with a DB without such quotes)

... and you don't accidentally confuse ` with '
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1