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;

Answer 1

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;

Answer 2

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.