0

Converted sqlite3 to mysql, but the import fails in phpMyAdmin and produces this error: #1064 - 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 'PRIMARY KEY NOT NULL ,sequence VARCHAR,items_count INTEGER,total FLOAT,`c' at line 1

What am I missing? 

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (`id` VARCHAR PRIMARY KEY NOT NULL ,`sequence` VARCHAR,`items_count` INTEGER,`total` FLOAT,`change` FLOAT,`tax_subtotal` FLOAT,`surcharge_subtotal` FLOAT,`discount_subtotal` FLOAT,`payment_subtotal` FLOAT,`rounding_prices` VARCHAR,`precision_prices` VARCHAR,`rounding_taxes` VARCHAR,`precision_taxes` VARCHAR,`status` INTEGER,`service_clerk` VARCHAR,`service_clerk_displayname` VARCHAR,`proceeds_clerk` VARCHAR,`proceeds_clerk_displayname` VARCHAR,`member` VARCHAR,`member_displayname` VARCHAR,`member_email` VARCHAR,`member_cellphone` VARCHAR,`invoice_type` VARCHAR,`invoice_title` VARCHAR,`invoice_no` VARCHAR,`invoice_count` INTEGER,`destination` VARCHAR,`table_no` INTEGER,`check_no` INTEGER,`no_of_customers` INTEGER,......
n8techy
  • 3
  • 3
  • Looks like you are missing the data lengths of the `VARCHAR` columns. MySQL requires a length like `VARCHAR(32)` – Michael Berkowski Jan 04 '14 at 03:06
  • thank you so much! I been assigned mysql projects now and I dont know a lick about it. Knew it had to be something simple. – n8techy Jan 04 '14 at 03:23

1 Answers1

0

There is a litte differnce between SQLite and MySQL syntax.

Below answer is taken from here

Here a list of ALL the differences in SQL syntax that I know about between the two file formats: The lines starting with:

BEGIN TRANSACTION
COMMIT
sqlite_sequence
CREATE UNIQUE INDEX

are not used in MySQL

SQLlite uses CREATE TABLE/INSERT INTO "table_name" and MySQL uses CREATE TABLE/INSERT INTO table_name
MySQL doesn't use quotes inside the schema definition
MySQL uses single quotes for strings inside the INSERT INTO clauses
SQLlite and MySQL have different ways of escaping strings inside INSERT INTO clauses
SQLlite uses 't' and 'f' for booleans, MySQL uses 1 and 0 (a simple regex for this can fail when you have a string like: 'I do, you don\'t' inside your INSERT INTO)
SQLLite uses AUTOINCREMENT, MySQL uses AUTO_INCREMENT

The above link will help more regarding this.

Community
  • 1
  • 1
Sabin Jose
  • 638
  • 9
  • 18
  • I have already tried that thread and it didn't help. Probably because my mistake was so simple it didn't need mention – n8techy Jan 04 '14 at 06:03
  • @n8techy I have also faced a similar problem in past. The method which I followed was, first used export feature of : [link](http://sourceforge.net/projects/sqlitebrowser/) then replaced all the above mentioned keywords with MySQL equalent from the sql file. – Sabin Jose Jan 04 '14 at 06:14