Perforce Public Knowledge Base - MySQL error: Incorrect string value: 'xF2xB6xA9xB8' for column 'DESC'
× PRODUCTS SOLUTIONS CUSTOMERS LEARN SUPPORT
Downloads Company Partners Careers Contact Free Trials
Menu Search
Perforce
Reset Search
 

 

Article

MySQL error: Incorrect string value: 'xF2xB6xA9xB8' for column 'DESC'

« Go Back

Information

 
Problem

While replicating, P4toDB displays an "Incorrect string value" exception.

Example:

May 7, 2011 5:14:26 PM com.perforce.p4todb.Messages log
SEVERE: SQL exception:  pos: 2281#218144055978 0 false false, prev_pos: 2281#218136953030 10081 false false
Incorrect string value: '\xF2\xB6\xA9\xB8\xEF\xBF...' for column 'DESC' at row 1
	SQL state:  HY000
	Vendor error code:  1,366
	Fetched from position:  2281#218136953030 10081 false false
	Lines:  2,294
java.sql.BatchUpdateException: Incorrect string value: '\xF2\xB6\xA9\xB8\xEF\xBF...' for column 'DESC' at row 1
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2024)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
	at com.perforce.p4todb.p4todb.execute_statements(p4todb.java:2942)

Note:  the actual string value and column name may be different.

Solution
Examine the string value to determine the problem character. Use the UTF-8 Codepage layout on http://en.wikipedia.org/wiki/UTF-8#Codepage_layout to determine the number of bytes based on the first byte in the string value given. For the above example, the first byte is \xF2 and in UTF-8 this means it is the start of a character encoded with four bytes.

If the character is four bytes, you must alter the table definitions and  configure the MySQL server to accept four byte UTF-8 characters.   When finished, restart P4toDB; it will continue from where it failed.

If the character is a five or six byte encoding, MySQL does not support it.  Contact Perforce Support for assistance in working around this situation.

Configuring P4toDB tables to Support Four Byte UTF-8 Encodings

If the column name in the exception is DESC, start your favorite SQL query tool and run these commands within your P4toDB database:

 alter table `DESC`
   modify column `DESC` longtext
   CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL ;

 alter table `CHANGE`
   modify column `DESC` varchar(64)
   CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL ;

 alter table `CHANGEX`
   modify column `DESC` varchar(64)
   CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL ;

 alter table `JOB`
   modify column `DESC` varchar(64)
   CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL ;

 alter table `BODTEXT`
   modify column `TEXT` longtext
   CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL ;

Note:  if your properties config file specifics a table_prefix or column_prefix then the above commands must be altered to match your actual table/column names.

If the column name is not DESC, please contact Perforce Support.

Configuring MySQL to Support Four Byte UTF-8 Encodings

Your MySQL Server must be release 5.5 or later.

Your MySQL Connector/J JDBC driver must be version 5.1.14 or later.

Your JDBC URL should not specify the parameters useUnicode or characterEncoding.  When not present, this is equivalent to autodetection of the character set.   The URL format given in the P4toDB sample config file suffices.

Configure the MySQL server with "character_set_server=utf8mb4".  The character-set-server is usually defined in your server's config file (my.ini).  For other options, see the MySQL documentation for how to configure MySQL:
http://dev.mysql.com/doc/refman/5.5/en/charset-server.html


Note: Driver version 5.1.13 works without configuring the server but will break should you ever update the Driver.  Versions earlier than 5.1.13 do not work with four byte encodings.
Related Links

Feedback

 

Was this article helpful?


   

Feedback

Please tell us how we can make this article more useful.

Characters Remaining: 255