Import CSV file incorrectly detects columns as NOT NULL

7 posts

sacrophyte posted 3 years ago in General

Reference text file: https://datasets.imdbws.com/title.akas.tsv.gz

When I attempt to import this tsv, HeidiSQL comes up with the following table DDL:

CREATE TABLE `test`.`data` (
    `titleId` VARCHAR(10) NOT NULL,
    `ordering` TINYINT NOT NULL,
    `title` TEXT NOT NULL,
    `region` VARCHAR(10) NOT NULL,
    `language` VARCHAR(10) NOT NULL,
    `types` VARCHAR(20) NOT NULL,
    `attributes` VARCHAR(70) NOT NULL,
    `isOriginalTitle` VARCHAR(10) NULL
)

However, when I do an import, I get the following errors:

LOAD DATA LOCAL INFILE 'C:\\Users\\sacro\\Downloads\\imdb\\title.akas.tsv\\data.tsv' INTO TABLE `test`.`data` CHARACTER SET utf8 FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n' IGNORE 1 LINES (`titleId`, `ordering`, `title`, `region`, `language`, `types`, `attributes`, `isOriginalTitle`);
/* 28,785,255 rows imported in 332.562 seconds. */
SHOW WARNINGS;
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 1 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 1 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 2 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'types' at row 2 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 3 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 3 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 4 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 4 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 5 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 5 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 6 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 6 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'region' at row 7 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 7 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 7 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 8 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'region' at row 9 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 9 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 9 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 10 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 10 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 11 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 11 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 12 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'types' at row 12 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 13 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 13 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 14 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 14 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 15 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'types' at row 15 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 16 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 17 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 17 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 18 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 18 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 19 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 20 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 20 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 21 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 21 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'region' at row 22 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 22 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 22 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 23 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 23 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 24 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 24 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 25 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'types' at row 25 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'region' at row 26 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 26 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 26 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 27 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 27 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 28 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'types' at row 28 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 29 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 29 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 30 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 30 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'attributes' at row 31 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'region' at row 32 */
/* Warning (1263): Column set to default value; NULL supplied to NOT NULL column 'language' at row 32 */

10378 posts

ansgar posted 3 years ago

That's because HeidiSQL did not yet detect \N values as NULL, which I just added for the upcoming nightly build.

7 posts

sacrophyte posted 3 years ago

You rock, thanks!!