Uploading CSV to MySQL fails due to "created_at" error

Question

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

Alter the column definition to `.. createdAt DATETIME DEFAULT CURRENT_TIMESTAMP, ..` — Akina, Jun 02 '23 at 04:51
Thanks for the suggestion, but unfortunately that didn't work for me. I did post the column definition that fixed it below, which was basically what you suggested, but adding NULL. — Lee, Jun 02 '23 at 18:27

score 1 · Answer 1 · edited Jun 03 '23 at 01:39

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;

score 0 · Answer 2 · answered Jun 02 '23 at 04:55

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

Uploading CSV to MySQL fails due to "created_at" error

2 Answers2