CODEWITHSUNDEEP
mysqlhibernategrails
Anthony
Anthony

Reputation: 35938

Incorrect string value for column

I'm getting this error while trying to save an entry from my application to mysql DB

Hibernate operation: could not execute statement; uncategorized SQLException for SQL [n/a]; SQL state [HY000]; error code [1366]; Incorrect string value: '\xE2\x80\x8BShe...' for column 'extracted_text' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xE2\x80\x8BShe...' for column 'extracted_text' at row 1 at com.some.scanner.RequestService.$tt__handleMessage(RequestService.groovy:67)

My Table is created as below:

CREATE TABLE `request` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `doc_hash` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extracted_text` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `FK414EF28FD3E87920` (`batch_id`),
) ENGINE=InnoDB AUTO_INCREMENT=20025 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

my entity mapping is quite simple in Grails

class Request {
  String docHash
  String extractedText

  static mapping = {
    extractedText type: 'text'
  }
}

Do I need to change the encoding type? If so, to what?

Upvotes: 1

Views: 7220

Answers (1)

jja
jja

Reputation: 100

Set the database properties useUnicode and characterEncoding. These are passed directly to your JDBC driver: https://tomcat.apache.org/tomcat-7.0-doc/api/org/apache/tomcat/jdbc/pool/PoolConfiguration.html#getDbProperties%28%29

dataSource {
  url = 'jdbc:mysql://...'
  properties {
    dbProperties {
      useUnicode=true
      characterEncoding='UTF-8'
     }
  }
}

A little more about MySQL Connect charsets (which includes the spelling of UTF-8): http://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-charsets.html

MySQL-specific config properties go in dbProperties: http://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

...unless they are already covered by the main dataSource properties from: http://docs.grails.org/latest/guide/conf.html#dataSource or are in the "extra" properties from: http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Common_Attributes

Upvotes: 1

Related Questions