Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: ERROR Data truncation: Data too long for column

  1. #1

    Default ERROR Data truncation: Data too long for column

    Hi to all.

    I have a transformation that take lines from a csv and put it in a mysql db.

    I have the following problem: Data truncation: Data too long for column 'IN_ATTESA_DI' at row 1

    I have look and it falls on line 5007, but the length of the field is the same of the other lines.

    I'v tried to change the field type on the db. I've change it from a varchar to e text field. The csv field is about 40 byte lenght.

    Can you help me?

  2. #2
    Join Date
    Jul 2009
    Posts
    24

    Default String Cut

    Hello,
    Without any more details it sounds like the error is coming from the insert to MySQL. You might try adding a String Cut step to insure the field in question is reduced to 40 characters in the stream. The other alternative that may be a bit safer would be to use a Select Value step and change the meta data for the field in the stream to make sure it is the correct length.

    Thank you,
    Jeff

  3. #3

    Default

    It is not a lenght problem. The database field is a TEXT field, so it can accept string of 65536 bytes. The csv string is only 40 bytes.

    Tell me what kind of datail you need.

    Thank you

  4. #4
    Join Date
    Jul 2009
    Posts
    24

    Default Meta Data

    Verify the meta data in the CSV file is accurate for the inbound file format. Since it is a delimitted file you may want to try and widen the length in the CSV input definition. At some point the stream is expectong 40 characters and getting something longer. This issue appears to be in the meta data for the 'IN_ATTESA_DI' column.

    Thank you,
    Jeff

  5. #5

    Default

    Thank you for the suggestions.

    I have verified csv data and I don't see nothing wrong. I also try to widen csv definition in spoon, but nothing change.

    I attach a csv with header, the last line that spoon have imported and the line that create the problem. Field with "<>" are confidential so I've change they.

    Bye
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2009
    Posts
    24

    Default Nothing unusual in the file

    Try changing your final step to go into a dummy step instead of the Table Output to MySQL. That will help you narrow down where the problem is. If you can post a screen shot of the CSV Input configuration I would be happy to lend a second pair of eyes.

    Thank you,
    Jeff

  7. #7

    Default

    I've yet incremented the size of the field "in_attesa_di".

    What can I see if I put a dummy step instead of mysql table?

    Thanx a lot
    Attached Images Attached Images  

  8. #8

    Default

    Is there a way to not stop the transformation on error?

  9. #9
    Join Date
    Sep 2007
    Posts
    834

    Default

    Giovanni
    I had the same error with MySQL and it wasn't a data length problem. The problem was that there was a non null column in the TABLE and I was sending a null value. I realized that when I tried to insert the same data directly in mySQL (outside PDI). Maybe this helps you.
    I still have to re-launch the transformation and eventually raise a Jira.

    To avoid the transformation to abort because of a single error, you can handle errors in the Table Output step.

    Regards
    mc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.