21

In mySQL workbench database, one of the tables has latitude, longitude and district attributes

lat: decimal (10,8)

lng: decimal (11,8)

district: int(4)

I'm trying to import data from .csv file to this table

ERROR 1366: 1366: Incorrect decimal value: '' for column 'lat' at row 1
SQL Statement:
ERROR 1366: 1366: Incorrect integer value: '' for column 'district' at row 1
SQL Statement:
INSERT INTO `db`.`myTable` (`id`, `name_en`, `icon`, `lat`, `lng`, `district`, `city`, `postal_code`)
    VALUES ('686', 'Name',?, '', '', '','1', 'P.O. Box 1111')
Improve this question
1
  • I prefer using varchar type to avoid sensitive error. Commented Jan 27, 2016 at 12:31

3 Answers 3

Reset to default
39

You have strict sql mode enabled, and you try to pass an empty string ('') as value for decimal fields in the insert. Empty string is an invalid value for a numeric field and in strict sql mode mysql generates an error if you try to insert an invalid data into a column, rather than providing a warning and use the default value (0 for numeric columns) of the particular column's data type:

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.

If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings (see Section 13.7.5.40, “SHOW WARNINGS Syntax”). In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE.

Remove the strict sql mode for the session before starting the import:

SET SESSION sql_mode = ''
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

@Shadow - It's not a good idea in production, I'll give you that. I was forced into doing this locally during a MS-SQL to MySQL data migration though because the tool would not allow me to set it in the session (I tried to set it via session numerous times). When I set it globally, my errors went away. I returned my sql_mode to the default afterwards. It is a brute force, at best ... so I don't recommend it. But it is possible.
I maintain: it is a really bad idea to disable strict sql mode globally. It would make more sense to figure out why you cannot set the sql mode in the session ather than apply the setting globally.
Removing strict mode leads to bad coding practice. The strict mode has a purpose and decrease lost records and security problems dramatically.
This was it, thanks. We ended up writing a simple UPDATE query to set all empty String values ("") to NULL in the tables/columns we were trying to update.
1

Remove STRICT_TRANS_TABLE from global sql_mode. You might required other attributes such as NO_ZERO_DATE in global sql_mode if already set.

Set this under [mysqld] in my.cnf (or /etc/mysql/mysql.conf.d/mysqld.cnf) depending on your server version.

[mysqld]  
sql_mode = "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Improve this answer

1 Comment

This is a realy bad idea! Strict sql mode helps to avoid a lot of bad practices, so completely disabling it is not recommended.
-1

All I did was change my query from INSERT INTO... to INSERT IGNORE INTO... Worked perfectly.

Improve this answer

1 Comment

All that data lost....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.