how can i solve it

[tmsdy]

2019-09-14 10:57:22 ERROR _create_table failed creating table asins: 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 'INT64 NOT NULL , nation CHAR(5) NOT NULL , temprank INT(11) NULL , sourc' at line 1 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 'INT64 NOT NULL ,nationCHAR(5) NOT NULL ,temprankINT(11) NULL ,sourc' at line 1

[techouse]

Erm, from what I can see you have an INT64 somewhere and that is not a valid data type in MySQL. The largest integer you can use is BIGINT(20) UNSIGNED. Otherwise just use VARCHAR(64). You might also wanna show me your SQLite table DDL and I might take a look into it.

[tmsdy]

CREATE TABLE "history0001"( [id] integer PRIMARY KEY ON CONFLICT FAIL AUTOINCREMENT NOT NULL ON CONFLICT FAIL, [asin] CHAR(10) NOT NULL, [sales] INTEGER, [rank] INTEGER, [price] FLOAT, [date] DATE NOT NULL, [source] INT NOT NULL DEFAULT 1, UNIQUE([asin], [date]) ON CONFLICT REPLACE);

[techouse]

This does’t look like the table the error message describes, because the error message mentions columns named nation and temprank which are clearly missing in the DDL you provided above. Are you sure this is the correct one?

I would suggest you make a copy of the SQLite database, clear out the data or replace if with some dummy data (you can generate that online even) and send it over so I can take a closer look at it.

[tmsdy]

I'm sorry about the mistake, following DDL is right:

CREATE TABLE [asins] ( [id] integer NOT NULL ON CONFLICT FAIL PRIMARY KEY ON CONFLICT FAIL AUTOINCREMENT, [asin] CHAR(10) NOT NULL, [brand] NVARCHAR(30), [sales] FLOAT, [rank] INTEGER, [price] FLOAT, [star] FLOAT, [review] FLOAT, [srratio] FLOAT, [catalogid] INTEGER, [firsttime] DATETIME, [creattime] DATETIME DEFAULT (datetime('now','localtime')), [timestamp] INT64 NOT NULL, [nation] CHAR(5) NOT NULL DEFAULT US, [temprank] INTEGER, [source] INT);

CREATE INDEX asin_timestamp on asins (asin, timestamp);

[techouse]

Aha, I didn't realise INT64 was a valid SQLite alias for NUMERIC. Basically it should translate to a MySQL BIGINT(19).

Fixed!