Data truncation: Data too long for column 'logo' at row 1

Question

I am trying to insert a photo into a BLOB column of a MySQL table, and I get an exception:

Data too long for column 'logo' at row 1.

Here is the JDBC:

    int idRestaurant = 42;
    String restoname=  "test";
    String restostatus=  "test";
    InputStream fileContent = getUploadedFile();
    int fileSize = getUploadedFileSize();

    Class.forName("com.mysql.jdbc.Driver");
    try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/resto" , "root" , "" )) {
        PreparedStatement ps = conn.prepareStatement("insert into restaurants (idRestaurant, restaurantName, status, logo) values(?,?,?,?)");
        ps.setInt(1, idRestaurant);
        ps.setString(2, restoname);
        ps.setString(3, restostatus);
        ps.setBinaryStream(4, fileContent, fileSize);
        ps.executeUpdate();
        conn.commit();
    }

How do I solve this problem?

You have three choices to fix this. 1. Increase the size of your column datatype. 2. Decrease the size of the content being inserted into that column. 3. Don't insert the information. — Eric Leschinski, Aug 08 '14 at 20:41

score 96 · Accepted Answer · edited Dec 21 '14 at 23:15

96

You are trying to insert data that is larger than allowed for the column logo.

Use following data types as per your need

TINYBLOB   :     maximum length of 255 bytes  
BLOB       :     maximum length of 65,535 bytes  
MEDIUMBLOB :     maximum length of 16,777,215 bytes  
LONGBLOB   :     maximum length of 4,294,967,295 bytes

Use LONGBLOB to avoid this exception.

edited Dec 21 '14 at 23:15

Nick Tomlin

28,402
11
61
90

answered Feb 03 '14 at 08:55

Aniket Kulkarni

12,825
9
67
90

I have a VARCHAR field with length 50 and this string 'amsterdam 12234 2^)(&*||' is giving me the same error even when its length is less than 50. Are there any restrictions on the data format too? – Chinmay jain Feb 18 '17 at 10:44

score 12 · Answer 2 · answered Feb 03 '14 at 08:55

12

Use data type LONGBLOB instead of BLOB in your database table.

answered Feb 03 '14 at 08:55

Saurabh Kachhia

300
3
12

13ushm4n · Answer 3 · 2016-02-03T12:33:26.897

8

Following solution worked for me. When connecting to the db, specify that data should be truncated if they are too long (jdbcCompliantTruncation). My link looks like this:

jdbc:mysql://SERVER:PORT_NO/SCHEMA?sessionVariables=sql_mode='NO_ENGINE_SUBSTITUTION'&jdbcCompliantTruncation=false

If you increase the size of the strings, you may face the same problem in future if the string you are attempting to store into the DB is longer than the new size.

EDIT: STRICT_TRANS_TABLES has to be removed from sql_mode as well.

edited Feb 03 '16 at 12:33

answered Dec 22 '15 at 13:19

13ushm4n

359
2
5

This part "jdbcCompliantTruncation=false" is what I actually needed, cuz first I had removed STRICT_TRANS_TABLES and that had had no effect. Thank you so much! – Roman Mar 09 '18 at 05:54

score 0 · Answer 4 · answered Jul 20 '21 at 18:15

String, in general, should be used for short text. By default, it is a VARCHAR(255)

Solution: In The entity use annotation @Column with length.

Exemple:

@Entity
public class Users {
     @Id
     @GeneratedValue(strategy = GenerationType.IDENTITY)
     private Long id;
     private String email;
     //..
     @Column(length = 1000) //1000 will be fine
     private String imgProfile;
}

Data truncation: Data too long for column 'logo' at row 1

4 Answers4

Linked

Related