mysql LOAD DATA LOCAL INFILE with default timestamp column value

Question

I've a table which has a column defined like this:

| Field       | Type          | Null | Key | Default           | Extra          |
+-------------+---------------+------+-----+-------------------+----------------+
| created_at  | timestamp     | NO   |     | CURRENT_TIMESTAMP |                |

and I insert data using this command:

LOAD DATA LOCAL INFILE '<path_to_the_file>' INTO TABLE my_table FIELDS TERMINATED BY '|' (<some_columns>,created_at,<more_columns>)

from a file where each line is like this:

822202|NETGEAR|||||1448ce8f-efc5-7b07-6982-1ff991bf967e||||||||1|||

Note: created_at is the 5th column, so the file has no value for it, therefore I expect CURRENT_TIMESTAMP

instead, this is what I see:

mysql> select distinct created_at from my_table;
+---------------------+
| created_at          |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

the code running the command is, supposedly, using a standard JDBC, it's a jdbc.clj Clojure library.

Note 2: if I run INSERT INTO ... query manually, it (obviously) works as expected.

Anyone can tell me what am I missing?

Have you tried execute the query from workbench, query browser, phpmyadmin, or console without use JDBC? — Rodney Salcedo
– Rodney Salcedo, Commented Oct 5, 2015 at 1:47
Good question; directly from mysql CLI console it's failing like this: ERROR 1148 (42000): The used command is not allowed with this MySQL version — alexakarpov
– alexakarpov, Commented Oct 5, 2015 at 1:58
mysql Ver 14.14 Distrib 5.6.25, for osx10.10 (x86_64) using EditLine wrapper // mysqld --version mysqld Ver 5.6.25 for osx10.10 on x86_64 (Homebrew) ---- both are rather new — alexakarpov
– alexakarpov, Commented Oct 5, 2015 at 4:03
Check: TIMESTAMP columns are set to the current date and time only if there is a NULL value for the column (that is, \N) and the column is not declared to permit NULL values, or if the TIMESTAMP column's default value is the current timestamp and it is omitted from the field list when a field list is specified. - 13.2.6 LOAD DATA INFILE Syntax. — wchiquito
– wchiquito, Commented Oct 5, 2015 at 4:41

wchiquito · Accepted Answer · 2015-10-05 19:07:11Z

Table:

DROP TABLE IF EXISTS `test_table`;

CREATE TABLE `test_table` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `column0` VARCHAR(6) DEFAULT NULL,
    `column1` VARCHAR(20) DEFAULT NULL,
    `column2` VARCHAR(20) DEFAULT NULL,
    `column3` VARCHAR(20) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `column5` VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

File:

/path/to/test_file.txt

822202|NETGEAR||||1
822203|NETGEAR1|||2000-01-01 00:00:00|2
822204|NETGEAR2|||NULL|3
822205|NETGEAR3|||\N|4

MySQL Command-Line:

mysql> LOAD DATA INFILE '/path/to/test_file.txt'
       INTO TABLE `test_table`
       FIELDS TERMINATED BY '|'
       (`column0`, `column1`, `column2`, `column3`, @`created_at`, `column5`)
       SET `created_at` = IF(CHAR_LENGTH(TRIM(@`created_at`)) = 0
                          OR
                          TRIM(@`created_at`) = 'NULL', NULL, @`created_at`);

Collectives™ on Stack Overflow

mysql LOAD DATA LOCAL INFILE with default timestamp column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related