1

In a MySQL 5.7 environment, when trying to insert a varchar value into a varchar field, I get the error:

SQL Error [1292] [22001]: Data truncation: Truncated incorrect INTEGER value: '1876301/347'

The source and target columns are both of the datatype varchar.

Here's a small demo to reproduce an example of the error (it occurs on the last insert):

create table SRC 
(
    Z varchar(20) null,
    A varchar(63) null,
    B varchar(30) null
);

insert into SRC
select 'XYZ', '1876301/347', null
;



create table TGT
(
    C varchar(63) null
)
;

To generate the error, now run this: (is the 3rd WHEN that matches, meaning it's trying to insert the value from SRC.A into TGT.C)


insert into TGT
(
    C
)
select 
    CASE
        WHEN if(Z in ('XYZ'), A, NULL) IS NULL AND B IS NULL THEN NULL
        WHEN if(Z in ('XYZ'), A, NULL) IS NULL AND B IS NOT NULL THEN B
        WHEN if(Z in ('XYZ'), A, NULL) IS NOT NULL AND B IS NULL THEN A
        WHEN if(Z in ('XYZ'), A, NULL) IS NOT NULL AND if(Z in ('XYZ'), A, NULL) = B THEN A 
        ELSE if(Z in ('XYZ'), A, NULL)
    END AS C
from 
    SRC 

It seems to be related to there being a case statement, as, when I try to simply insert the same value directly, it works fine.

insert into TGT
(
    C
)
select 
    A
from 
    SRC 

It also works fine with the CASE STATEMENT if I use INSERT IGNORE INTO..., but I'd rather not use that.

7
  • MySQL is riddled with bugs - you might like to change to an RDBMS? Jan 26 '21 at 21:11
  • @Vérace "I might want to change to an RDMBS" - is MySQL not an RDBMS?
    – GWR
    Jan 26 '21 at 21:14
  • Bit tongue in cheek I'm afraid... I should have put in a :-)! I've long since stopped even considering filing bug reports to MySQL when I spot something anomalous with the data returned from a MySQL instance... see the link below for example, not only does MySQL not give an error, it returns incorrect data! See here (search for "It SHOULD work like this" - it's a long answer - and check out the difference between what's returned by PostgreSQL and MySQL! Jan 26 '21 at 21:25
  • I'm glad you got back to me because I was wondering why you were testing constructs like the one in your question - is it just purely theoretical or does the code actually serve a purpose? Jan 26 '21 at 21:26
  • Disable STRICT_TRANS_TABLES SQL mode.
    – Akina
    Jan 27 '21 at 4:36
2

I received this message doing an insert. The select worked fine but when prefixed with "insert into blablabla" it hated my guts. The problem turned out the be the character set of the target varchar. My dev environ defaulted to "latin" when creating a new varchar for a new table, but the varchar in the source table was "utf8" with a "utf8_unicode_ci" collation. When I corrected that in the new destination table, MySQL and I were on much better terms.

1
  • I'd bet this is exactly my issue. I've run into it with other scenarios, and for some reason, I didn't think of it when hitting this issue. Usually with those collation issues, the error message includes something about collation. I'm going to do some tests tomorrow at work, will get back at you.
    – GWR
    Jun 3 '21 at 21:26
0

Seems like a bug in mysql5.7 - it works both in mysql5.6 and mysql8.0.

I have no idea why it can be like that, but I was able to reproduce that with simpler insert statement:

insert into TGT(C)
select
    CASE
        WHEN if(Z in ('XYZ'), A, NULL) IS NULL THEN NULL
    END AS C
from SRC;

Seems like some implicit conversion makde by mysql. Forcing explicitly convert helps:

insert into TGT(C)
select
    CASE
        WHEN if(Z in ('XYZ'), convert(A, char(63)), NULL) IS NULL THEN NULL
    END AS C
from SRC;

(but converting whole case..end does not.

0

(too big for a comment)

    WHEN if(Z in ('XYZ'), A, NULL) IS NULL AND B IS NULL THEN NULL
    WHEN if(Z in ('XYZ'), A, NULL) IS NULL AND B IS NOT NULL THEN B

can be collapsed to

    WHEN Z = 'XYZ' OR A IS NULL  THEN B

The rest can possibly be simplified. (The simplifications may make the error go away.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.