search

Arp-G / csv2sql

A blazing fast fully-automated CSV to database importer
MIT License
54 stars 4 forks source link

MySQL error when running the application for the first time. #5

Open Arp-G opened 4 years ago

Arp-G commented 4 years ago

When running the application sometimes MySQL gives an error like:

%MyXQL.Error{connection_id: 9, message: "(1067) (ER_INVALID_DEFAULT) Invalid default value for 'DateModified'", 
mysql: %{code: 1067, name: :ER_INVALID_DEFAULT}, statement: "CREATE TABLE csvsql_test.ActivityStream (`ItemId` VARCHAR(100), 
`ItemVersion` BIT, `_ItemTimestamp` VARCHAR(100), `ActivityStreamType` INT, `OriginalValue` TEXT, `NewValue` VARCHAR(100), 
`CreatorId` VARCHAR(100), `DateCreated` TIMESTAMP, `ModifierId` VARCHAR(100), `DateModified` TIMESTAMP, `OwnerId` VARCHAR(100));"}

Surprisingly this error disappears most of the time, and the app works fine when stopped and started again just after the error occurs.

As evident from the error message, it has something to do with default values for timestamp columns.

I suspect setting some MySQL modes might have something to do with this error. However, I am not sure, since this error occurs randomly even when testing with the same CSV files.

SET GLOBAL SQL_MODE="NO_BACKSLASH_ESCAPES,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE";

MySQL docs, regarding the NO_ZERO_IN_DATE mode.