MySQL 'Truncated incorrect INTEGER value'

Question

I'm getting an odd 'Truncated incorrect INTEGER value' error when I run the following UPDATE query:

update tbl
set projectNumber = right(comments, 7)
where createdBy = 'me'
and length(CONVERT(right(comments, 7), SIGNED INTEGER)) = 7 
and CONVERT(right(comments, 7), SIGNED INTEGER) > 0
and CONVERT(right(comments, 7), SIGNED INTEGER) is not null
and createdOn > '2011-01-31 12:00:00'
and projectNumber is null

projectNumber is varchar(10).

When I run it as a straight select I do not get an error and I see results as expected. Any ideas? Essentially I'm trying to update the projectNumber field where the end of the comments in imported notes are 7 numeric characters (but projectNumber's are not always 7 numeric, which is why the field is varchar(10)).

score 17 · Accepted Answer · answered Feb 09 '11 at 15:34

17

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.

answered Feb 09 '11 at 15:34

Mchl

61,444
9
118
120

4

Interesting. It does say Error Code 1292 (like your example), but I can confirm that the update is *not* running. I must have a setting to abort on warnings in updates? I'll keep digging. – Todd Sharp Feb 09 '11 at 15:35
3

I suspect your server is running in `TRADITIONAL` SQL mode, which converts all warnings to errors. http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_traditional – Mchl Feb 09 '11 at 15:49
6

Right. I actually decided to just use a regex (and right(comments, 7) REGEXP '^[0-9]+$') and forget the convert nonsense. Worked just fine. Thanks so much for your help. – Todd Sharp Feb 09 '11 at 16:02
6

It's an error now in 2019 version `Ver 14.14 Distrib 5.7.26`. – Khom Nazid Aug 06 '19 at 20:37

score 10 · Answer 2 · edited Sep 03 '19 at 10:07

10

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

edited Sep 03 '19 at 10:07

Toby Speight

27,591
48
66
103

answered Sep 03 '19 at 09:49

punsku

303
4
7

Same consideration if you're using INSERT INTO. You should add "IGNORE" to the query: INSERT IGNORE INTO tbl(a,b,c) VALUES (1, CONVERT(RIGHT(COMMENTS, 7), SIGNED INTEGER), 3) – Rosario Russo Mar 05 '20 at 17:39
Is it really safe to use IGNORE? – Freedo Feb 02 '21 at 15:23
1

UPDATE IGNORE skips the rows that cannot be converted. If this is not ok in your use case, you should make sure all rows can be converted and then run the query without IGNORE. – punsku Feb 04 '21 at 11:27

score 8 · Answer 3 · edited Dec 23 '15 at 16:56

8

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

edited Dec 23 '15 at 16:56

Hambone

15,600
8
46
69

answered Dec 23 '15 at 16:05

Ryan Shirley

179
2
4

score 2 · Answer 4 · answered May 29 '19 at 07:49

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!

score 1 · Answer 5 · answered Jun 19 '17 at 17:20

1

If you're using a text type for column data and you tried to set the default value as 0 then just set it as NULL:

ALTER TABLE `__table__` 
CHANGE `__column_name__old__` `__column_name__new__` 
INT(4) NOT NULL DEFAULT '0';

answered Jun 19 '17 at 17:20

John

1
13
98
177

score 0 · Answer 6 · answered Oct 15 '20 at 15:51

Toby Speight has outlined one solution for the problem in his answer. I don't know if anybody will ever be as stupid as me, but to whom it may concern:

I got Error code 1292 trying to modify the data type of a column IsDeleted from VARCHAR ('Y' => true, 'N' => false) to BIT.

To resolve the problem: Update all 'Y's to '1's (UPDATE table SET isDeleted = '1' WHERE isDeleted = 'Y') and all 'N's to '0's, tried to modify the column type again and voila data type was modified PLUS the '1'-columns contain TRUE and vice-versa now.

Nuryagdy Mustapayev · Answer 7 · 2022-06-02T14:04:06.210

Thanks @bhavesh-harsora I fixed error by updating sql_mode. Error was caused by STRICT_TRANS_TABLES this flag in sql mode.

Also, When I removed this flag error message changed

from: "Error Code: 1292. Truncated incorrect INTEGER value: ''"

to: "2333 row(s) affected, 1024 warning(s): 1292 Truncated incorrect INTEGER value: '' .......".

So, as I understand removing this flag converts some errors into warnings.

The weird thing is, I faced this error when I run DELETE query, but when I run SELECT query with the same conditions I don't get this error.

throws Error Code: 1292. Truncated incorrect INTEGER value: '' error:

DELETE pv
FROM table_a AS pv
INNER JOIN table_b AS ws ON pv.ws_id = ws.id
INNER JOIN table_c AS w ON w.product_id = pv.product_id
INNER JOIN table_d ppp_l ON ppp_l.id = 7002 and ppp_l.product_id = w.product_id
WHERE CAST(ppp_l.value as UNSIGNED) < CAST(ws.value as UNSIGNED);

but SELECT query runs without any error:

SELECT 
pv.id
FROM table_a AS pv
INNER JOIN table_b AS ws ON pv.ws_id = ws.id
INNER JOIN table_c AS w ON w.product_id = pv.product_id
INNER JOIN table_d ppp_l ON ppp_l.id = 7002 and ppp_l.product_id = w.product_id
WHERE CAST(ppp_l.value as UNSIGNED) < CAST(ws.value as UNSIGNED)

this issue also was mentioned here: https://bugs.mysql.com/bug.php?id=76353

MySQL 'Truncated incorrect INTEGER value'

7 Answers7

Linked