MySQL 'Truncated incorrect INTEGER value'

I found one solution, which is out of box and could easily be implemented. The solution is much similar to SET ANSI_WARNING OFF in MS SQL Server.

In MySQL, first you need to check whether what is configurations is set for "sql_mode" by using below command:

SHOW VARIABLES LIKE 'sql_mode';

OR else use below:

SELECT @@GLOBAL.sql_mode;

There might have following sets of value in CSV.

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Based on your error, you can remove settings and Reset it using below command:

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

By using above command to reset it. It has solved similar of this problem "Truncated incorrect INTEGER value" in my case. I hope it should be working from other user also who are facing this issue.

For more details on this "sql_mode", please refer this.

Hope this would be use full!

As stated in other answers, this is an error as of 2019, which prevents the query from running. To run the query even if there are strings that cannot be converted to numbers, simply use UPDATE IGNORE.

So for example a minimal version of the original code:

UPDATE IGNORE tbl
SET projectNumber = RIGHT(comments, 7)
WHERE CONVERT(RIGHT(COMMENTS, 7), SIGNED INTEGER) > 0

It's not an error. It's a warning that comes from CONVERT() when you ask it to convert non-numeric to integer;

Run these queries in console to see:

mysql> SELECT CONVERT(right('1s23d45678', 7), SIGNED INTEGER);
+-------------------------------------------------+
| CONVERT(right('1s23d45678', 7), SIGNED INTEGER) |
+-------------------------------------------------+
|                                               3 |
+-------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '3d45678' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

As I said, it's a warning, not an error. Your query should be doing the update correctly.

Another common cause for this warning is white space in the string to be converted. Use trim() before convert() to get rid of that.