Question 181025 in Mysql Question posted in
The official MySQL documentation can be found here.

Uploading CSV to MySQL fails due to "created_at" error

Summary: I tried to upload a csv file to a database table using the MySQL Workbench import feature, but was unsuccessful. The error log shows an issue with the ‘createdAt’ column. I am working on a Mac.

I’m trying to upload a csv file with some data to a table in my database using the MySQL Workbench import wizard. All the steps for finding the file, matching the columns in the csv to my table, etc. seem to be working fine, but when I try to run the import I get a result that shows 0 records uploaded.

The MySQL log shows this error for every row in the csv file:

- Row import failed with error: ("Field 'createdAt' doesn't have a default value", 1364)

I am able to successfully add rows to the database via Postman, but the CSV upload isn’t working.

My application is build with javascript using Sequelize. My db.config file looks like this:

module.exports = {
    HOST: "localhost",
    USER: "root",
    PASSWORD: "myPassword",
    DB: "cookbookdb",
    dialect: "mysql",
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
    }
  };

The model for the table I’m trying to populate looks like this:

const { DECIMAL, STRING, INTEGER } = require("sequelize");

module.exports = (sequelize, Sequelize) => {
  const Region = sequelize.define("region", {

    country: {
      type: Sequelize.STRING
    },
    alpha2Code: {
      type: Sequelize.STRING
    },
    alpha3Code: {
      type: Sequelize.STRING
    },
    lat: {
      type: Sequelize.DECIMAL(8,6)
    },
    lng: {
      type: Sequelize.DECIMAL(9,6)
    }
  });
  return Region;
};

Since the ‘createdAt’ column is generated by MySQL, I don’t know what might be setting this off. The info for the ‘createdAt’ column shows it corresponding to datetime, which is what is generated when I add a row via Postman. What MySQL shows:

`Column: createdAt

Definition:
createdAt datetime`

The csv file is a raw version of this country data from google: https://github.com/google/dspl/blob/master/samples/google/canonical/countries.csv

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I finally figured out that adding NULL before the DEFAULT for both createdAt and updatedAt columns fixed my issue. Since I was running into the same problem with other CSV files as well as another test database, this looks like it will be a good solution to keep in mind when using the MySQL workbench GUI import if others run into the same issue.

    ALTER TABLE regions MODIFY createdAt DATETIME NULL DEFAULT CURRENT_TIMESTAMP; ALTER TABLE regions MODIFY updatedAt DATETIME NULL DEFAULT CURRENT_TIMESTAMP;


  2. 0

    you can add these values to your modules as you have not told the version or
    the whole process and the csv file all i know the general solution is this..

    just add these conditions in your model.

    {
timestamps: false,
createdAt: false,
createdAt: false,
}

    or just drop the column after the file is created by using deop command

    ALTER TABLE your_table_name
DROP COLUMN createdAt;

    you can also refer to this answer as it is similar to your problem.

    https://stackoverflow.com/a/39587875/21179012

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search