Home > Java, JDBC > java.sql.SQLException: Incorrect string value: ‘\xCF\x82 \xCE\xBC\xCE…’ for column ‘body’ at row 1

java.sql.SQLException: Incorrect string value: ‘\xCF\x82 \xCE\xBC\xCE…’ for column ‘body’ at row 1

If you are getting this while trying to insert some unicode characters in a text type column in the database it will probably go away if you change the type of the column to longtext. I was getting it with the following table

CREATE TABLE messages (
  id int(10) unsigned NOT NULL,
  body text,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

and Java code

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/simpledb?useUnicode=true&characterEncoding=utf8", "username", "password"
Statement insertSt = conn.createStatement();
insertSt.executeUpdate("INSERT INTO messages (id, body) VALUES (" + id + ", '" + body + "')");
insertSt.close();

and the following exception

java.sql.SQLException: Incorrect string value: '\xCF\x82 \xCE\xBC\xCE...' for column 'body' at row 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1648)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1567)
        at migration.JDBCConnection.main(JDBCConnection.java:78)

The solution was to simply change the data type of body to longtext

CREATE TABLE messages (
  id int(10) unsigned NOT NULL,
  body longtext,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Advertisements
Categories: Java, JDBC Tags:
  1. 18 March 2010 at 6:42 am

    How long was the data you where trying to insert?

    • 18 March 2010 at 7:53 am

      Not sure. But it should have thrown a warning that the data is truncated if the data exceeded the column’s maximum length (according to this: http://dev.mysql.com/doc/refman/5.1/en/blob.html since strict SQL mode is not enabled). It didn’t, so I guess the data could fit in the column. And I had the correct collation, driver properties, encoding and everything set up properly in the database, so it was really weird I was getting this error.

      Anyway I didn’t spend much time on it, I just changed the column type and it worked.

  2. 26 May 2010 at 5:53 pm

    This didn’t solve my problem. 😦

  3. 27 May 2010 at 6:41 am

    Hello Djembefola, try to change the character encoding of the database or your table:

    ALTER DATABASE <database> DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

    or

    ALTER TABLE <table> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

    Because you are right, simply changing the column type does not work. I encountered the same issue again and I had to alter the tables encoding in order for it to work.

  4. 10 April 2011 at 4:43 pm

    Thanks. This post really helped me.

    I ran into this issue when I imported a server DB (default charset latin1) dump into my local windows DB (default charset utf8). For such cases changing database charset will not help and you will need to fix the individual charsets, like

    mysql> alter table gloo_auto_post convert to character set utf8 collate utf8_general_ci ;

    Also, be aware of columns used in keys and UNIQUE constraints. in latin1 1 char = 1 byte, but in utf-8 mysql seems to reserve 3 bytes/char, so if your column in unique constraint was 512 char long in latin1 and you switch to utf8 , you will run into errors like

    ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

    The only remedy is to fix such column widths inside of 1000 bytes/3.

  5. senthil
    29 November 2012 at 2:55 pm

    thanks! great piece of work

  6. eric
    8 April 2013 at 7:22 am

    thanks for a very useful post! ^^

  7. 16 April 2014 at 3:58 am

    eric, hi. I know, this post is too old. But this didn’t fix my problem too. The root cause was in the ‘utf8’ restrictions inside MySQL: “A maximum of three bytes per multi-byte character.”
    https://dev.mysql.com/doc/refman/5.6/en/charset-unicode-utf8.html
    So when we try to insert 4-byte char we always get this error. If database is not in strict mode, the string is stored, but with empty value.
    How to fix: change column type or database type to utf8mb4, read http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html
    this works with MySQL 5.5.3 and above.
    Changing one column:
    ALTER TABLE MODIFY MEDIUMTEXT CHARSET utf8mb4;
    This is blocking operation, so be careful.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: