com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'aboutMeText' at row 1

Question

I am trying to update value of a column using Spring-JPA, values are emoji/smilies. but getting error saying java.sql.BatchUpdateException: Incorrect string value: '\xF0\x9F\x98\x84\xF0\x9F...' for column

Here is the connection URL-

jdbc.url=jdbc:mysql:localhost:3306/woo?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&connectionCollation=utf8mb4_unicode_ci&characterSetResults=UTF-8

Here is the calling code

userProfile.setAboutMeText("\uD83D\uDE04\uD83D\uDC68\u200D\u2764\uFE0F\u200D\uD83D\uDC8B\u200D\uD83D\uDC68\uD83D\uDE02\uD83D\uDE20");

Here is the entity

 @Entity
public class UserProfile implements Serializable {

    @Column(length = 1000)
private String aboutMeText;
@Id
private Long id;
public Long getId() {
    return id;
}

public void seId(Long id) {
    this.id = id;
}


public String getAboutMeText() {
   return JsonEscape.unescapeJson(aboutMeText);


}
 public void setAboutMeText(String aboutMeText) {
   this.aboutMeText = JsonEscape.escapeJson(aboutMeText);


}

and

here is the complete error:

HTTP Status 500 - Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: Data truncation: Data too long for column 'aboutMeText' at row 1; SQL [n/a]; nested exception is org.hibernate.exception.DataException: Data truncation: Data too long for column 'aboutMeText' at row 1</h1>
    <div class="line"></div>
    <p>
        <b>type</b> Exception report
    </p>

<pre>org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: Data truncation: Data too long for column 'aboutMeText' at row 1; SQL [n/a]; nested exception is org.hibernate.exception.DataException: Data truncation: Data too long for column 'aboutMeText' at row 1
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:981)
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:871)
javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

I tried and checked various posts on stackoverflow , stackexchange etc.. and performed some changes, but still unable to solve the issue.

change collation to utf8_unicode_ci...[check this](http://stackoverflow.com/a/20431999/4117061) and [this](http://stackoverflow.com/a/10959780/4117061) — Prasanna Kumar H A, Sep 22 '16 at 06:55
previously it was utf8_unicode_ci, but i was getting same error so changed it to utf8mb4_unicode_ci — Asad Ali, Sep 22 '16 at 06:59
Is it a binary data that you are storing in that column? Then shouldn't it be byte[] instead of String with blob type? — Madhusudana Reddy Sunnapu, Sep 22 '16 at 12:13
its comination of normal text, smilies/emoji and special charecter like ߘ ߘ ߘ ߘ ߘ ߘ ߘ ߑ¨‍❤ߑ¨‍❤ߑ¨‍❤ etc which i am passing through controller as "%5Cud83d%5Cude04%5Cud83d%5Cudc68%5Cu200d%5Cu2764%". its encoded value of emoji-es and special characters. — Asad Ali, Sep 22 '16 at 12:33
`userProfile.setAboutMeText("\uD83D\uDE20\uD83D\uDE20\uD83D\uDE07\uD83D\uDE02\uD83D\uDE02\uD83D\uDE20\uD83D\uDE20\uD83D\uDE02\uD83D\uDE02\uD83D\uDE02\uD83D\uDE02\uD83D\uDE02\uD83D\uDE02\uD83D\uDE07\uD83D\uDE02\uD83D\uDE20\uD83D\uDE20\uD83D\uDE20\uD83D\uDE20\uD83D\uDE20\uD83D\uDE02\uD83D\uDE02\uD83D\uDE02\uD83D\uDE02\uD83D\uDE02\uD83D\uDE20\uD83D\uDE02\uD83D\uDE02\uD83D\uDE02\uD83D\uDE02\uD83D\uDE22\uD83D\uDE22\uD83D\uDE02\uD83D\uDE20\uD83D\uDE20\uD83D\uDE20\uD83D\uDE20\uD83D");` — Asad Ali, Sep 22 '16 at 12:35
Did you come across [this?](http://andy-carter.com/blog/saving-emoticons-unicode-from-twitter-to-a-mysql-database) and did you try changing the column datatype to "text"? — ameenhere, Sep 22 '16 at 12:44
error gone but i am getting my smilies/emojies as ???(question mark but not real emojies). — Asad Ali, Sep 23 '16 at 05:41
fixed.. increased size of aboutMeText from 1000 to 3000, `@Column(length = 3000) private String aboutMeText;` — Asad Ali, Sep 23 '16 at 08:14

score 9 · Accepted Answer · answered Sep 23 '16 at 09:14

9

increased size of aboutMeText from 1000 to 3000, in code and db through alter.

@Column(length = 3000) 
private String aboutMeText;

doing so com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long exception Gone and i got desired output.

answered Sep 23 '16 at 09:14

Asad Ali

389
1
10
28

What about the column definition on your database ? – biology.info Sep 23 '16 at 09:22
increased in db too... `alter table UserProfile modify aboutMeText varchar(3000);` doing so now i can send minimum 250 emojis earlier i was able to send only 85 emojies, if i was entering more than 85 emojies i was getting data too long exception. that means each emoji character is getting encoded and thus size increases. – Asad Ali Sep 23 '16 at 09:26

score 2 · Answer 2 · answered Nov 15 '20 at 11:53

You can change the data type of this column from varchar(255) to TEXT with length of 65535 bytes. Just mark your column as below:

@Column(columnDefinition = "TEXT")
private String myLongDataColumn;

Or you can use other annotations. For instance, @Lob. Visit this link for details https://www.baeldung.com/jpa-annotation-postgresql-text-type

score 1 · Answer 3 · answered Feb 04 '22 at 19:09

1

my problem was also same but I noticed that I am using
"Update " which already constructed a table which which length already define in table so I change it by " create" and my problem resolved because it create a new table when we run the query

answered Feb 04 '22 at 19:09

Arvind

11
2

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/30991847) – pringi Feb 10 '22 at 12:17

score 0 · Answer 4 · answered May 02 '23 at 16:10

truncate the description field to 100 characters before persisting it:

String description = "This is a very long description...";
  if (description.length() > 100) {
        description = description.substring(0, 100);
         }
             myEntity.setDescription(description);
             myRepository.save(myEntity);

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'aboutMeText' at row 1

4 Answers4