Search code examples
mysqlsqlmysql-workbenchmysql-error-1064

Load data failure in MySQL

I am trying to load data from a csv file to table in MySQL. Though everything seems alright, for some reason it doesn't work for me whereas my team member was able to load it successfully. We both have the same table definition and data.

My table definition looks like as shown below

   CREATE TABLE SIGNS ( 
   ROW_ID MEDIUMINT UNSIGNED NOT NULL,
   SUBJECT_ID MEDIUMINT UNSIGNED NOT NULL,
   Test_ID MEDIUMINT UNSIGNED NOT NULL,
   Name_ID TINYINT UNSIGNED NOT NULL,
   date_ID TINYINT UNSIGNED NOT NULL,
   race_NO MEDIUMINT UNSIGNED NOT NULL,
   MO_TIME DATETIME NOT NULL,
   ITEMID VARCHAR(255) NOT NULL,
   VALUE SMALLINT UNSIGNED NOT NULL,
  UNIQUE KEY SIGNS_ROW_ID (ROW_ID)
  )
  CHARACTER SET = UTF8;

I have a data in csv file and when I upload it, works fine for 80 pc of the records. But around 20 pc of the records throws the below error.

Sometimes, I get the error message Out of range value for column 'VALUE' at row 438575

enter image description here

And sometimes I get Data Truncated for column 'MO_TIME' at row 1,1265

As you can see it's very inconsistent. But there is no issue with data because it worked fine for my team member. Has it got anything to do with constraints/index?

I encountered similar weird issue for other tables as well. But I TRUNCATED the table and loaded it again and it worked fine.

I tried the same here but it doesn't help

Solution

  • Your VALUE column is SMALLINT UNSIGNED. In chat you said it was inserted as 0 on the rows that warned of an out of range value.

    See this test I did:

    mysql> create table t ( i smallint unsigned);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t set i = -150;
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'i' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+------+
| i    |
+------+
|    0 |
+------+

    If you try to insert a negative value into a smallint unsigned column, it is out of range supported by unsigned. It is truncated to 0, because unsigned numeric types only support nonnegative values.

    Something similar happens for a datetime if you try to insert an invalid value. For example, a common mistake is to use dates in MM/DD/YYYY format, which MySQL does not support:

    mysql> create table t2 (mo_time datetime);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t2 set mo_time = '10/31/2019';
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'mo_time' at row 1 |
+---------+------+----------------------------------------------+

    That shows the same error message you got.

    mysql> select * from t2;
+---------------------+
| mo_time             |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+

    Aha, the meaningless date value which used to be treated as the default. Thankfully in recent versions of MySQL, it is prohibited.

    MySQL only supports YYYY-MM-DD format. Actually the punctuation between the parts is optional. See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html for details.