SQL command line error

Question

I was trying to add a table to a database in MySQL using the command line:

mysql -u Jon -p testdb --password=password < "C:\Users\Jon\Documents\Summer\Do\SQL\root.sql"

However, I get an error when I type the above:

ERROR 1064 (42000) at line 12: 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 'VARCHAR(255) DEFAULT NULL, Clearing VARCHAR(255) DEFAULT NULL, Globex VARCH' at line 2

I used dbForge to create a sql file called root.sql:

SET NAMES 'utf8';

USE testdb;

DROP TABLE IF EXISTS roottable;
CREATE TABLE roottable 
  (
    ProductName VARCHAR(255) DEFAULT NULL,
    Clearing VARCHAR(255) DEFAULT NULL,
    Globex VARCHAR(255) DEFAULT NULL,
    IsActive VARCHAR(255) DEFAULT NULL,
    FloorId VARCHAR(255) DEFAULT NULL,
    GroupId VARCHAR(255) DEFAULT NULL,
    SubGroup VARCHAR(255) DEFAULT NULL,
    ConversionFactor VARCHAR(255) DEFAULT NULL,
    Id INT PRIMARY KEY AUTO_INCREMENT
  )
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

I also just successfully ran that `root.sql` against a newly created `testdb` without error. My guess is that you have some strange non-printing or tokenised whitespace character in your file, which has not copied over into your question. Try viewing it in a hex editor. — eggyal, Jul 15 '13 at 11:29
MySQL 5.5.32 - expected "IT_end | IT_num | IT_word", got (WORD, SET) from "SET" at offset 0. — TN888, Jul 15 '13 at 11:39
@Ty221: I've never seen that message before; I'm not even sure what it's trying to convey... however, I'm pretty certain that you did not run the above code correctly (perhaps you were in the wrong context)? — eggyal, Jul 15 '13 at 11:41
this may be wrong , i think it conveys something that i don't ever thought of , any way try this by specifying character-set in import as "mysql --default-character-set=utf8 -u *****(goes the rest)" check your Sql file using Notepad++ (at least) especially Line "ProductName VARCHAR(255) DEFAULT NULL " — internals-in, Jul 15 '13 at 11:55
@MaxKim: Are you able to upload your `root.sql` file somewhere that we can inspect it? — eggyal, Jul 15 '13 at 11:59
I'm not sure where I can upload it, but I ran the code in dbForge, it created the table. I looked for the table from command line via `describe roottable` and it looks like there is a table with the correct columns. It just the `mysql -u Jon ....` command which is causing the problem. — Max Kim, Jul 15 '13 at 12:03
your code and syntax are absolutely correct,I ran the code and it ran successfully having no error. — Abhik Dey, Nov 27 '13 at 10:42

score 1 · Answer 1 · answered Jul 15 '13 at 14:12

1

this may be funny, but try to save the SQL file as utf8, open it with notepad.exe, then save as and choose utf-8 then run the query. have a try.

answered Jul 15 '13 at 14:12

score 0 · Answer 2 · answered Jul 15 '13 at 11:30

I think this code should look like this :

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `testdb`;

DROP TABLE IF EXISTS `roottable`;
CREATE TABLE `testdb`.`roottable`
  (
    `ProductName` VARCHAR(255) DEFAULT NULL,
    `Clearing` VARCHAR(255) DEFAULT NULL,
    `Globex` VARCHAR(255) DEFAULT NULL,
    `IsActive` VARCHAR(255) DEFAULT NULL,
    `FloorId` VARCHAR(255) DEFAULT NULL,
    `GroupId` VARCHAR(255) DEFAULT NULL,
    `SubGroup` VARCHAR(255) DEFAULT NULL,
    `ConversionFactor` VARCHAR(255) DEFAULT NULL,
    `Id` INT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`Id`) 
  )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

SQL command line error

2 Answers2