I have a mysql 5.7 server.

I am trying to import some .csv files.

Sometimes it may so happen that these are malformed.

Here is a sample case:

  - 'mysql: [Warning] Using a password on the command line interface can be insecure.'
  stdout: |-
    Warning (Code 1366): Incorrect integer value: ' are not separated per hour."' for column 'story_points' at row 10
    Warning (Code 1262): Row 10 was truncated; it contained more data than there were input columns
  stdout_lines: <omitted>

What I noticed is that the row was indeed somehow transformed and inserted (although in a bad way).

How can I prevent this behavior? I want the entire command to fail in such cases cause I cannot selectively roll back such malformed insertions.

  • Server SQL Modes, TRADITIONAL mode. Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. Or set this behavior separately by setting STRICT_ALL_TABLES. – Akina Feb 15 '19 at 9:19
  • Ι set the mode as follows: | sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | but still the file is imported; – pkaramol Feb 15 '19 at 9:47
  • @pkaramol what utility are you using to import these? – Michael - sqlbot Feb 15 '19 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.