CDH CM修改配置文件Data truncation: Incorrect string value: ‘\xE5\xB7\xB2\xE6\x9B\xB4...‘ for column ‘MESSA

​

Data truncation: Incorrect string value: '\xE5\xB7\xB2\xE6\x9B\xB4...' for column 'MESSAGE' at row 1

​

cloudera server 端日志：

Caused by: java.sql.BatchUpdateException: Incorrect string value: '\xE5\xB7\xB2\xE6\x9B\xB4...' for column 'VALUE' at row 1
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2054)
        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
        at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:110)
        ... 98 more
Caused by: java.sql.SQLException: Incorrect string value: '\xE5\xB7\xB2\xE6\x9B\xB4...' for column 'VALUE' at row 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006)
        ... 101 more

问题出现原因：

CM元数据库 字符集不兼容 字段 VALUE  MESSAGE  无法写入对应的值。

解决办法：

先修改元数据库编码格式为utf8，如果修改后没有效果，继续按照如下修改

 SHOW VARIABLES LIKE '%character_set_%';

​

修改元数据库。cm_server为源数据库名。

use cm_server;
alter table CONFIGS         modify `VALUE` longtext character set utf8 collate utf8_general_ci; 
alter table CONFIGS_AUD     modify `VALUE` longtext character set utf8 collate utf8_general_ci; 
alter table GLOBAL_SETTINGS modify `VALUE` longtext character set utf8 collate utf8_general_ci; 
alter table USER_SETTINGS   modify `VALUE` longtext character set utf8 collate utf8_general_ci; 
alter table AUDITS modify `MESSAGE` longtext character set utf8 collate utf8_general_ci; 
alter table REVISIONS modify `MESSAGE` longtext character set utf8 collate utf8_general_ci;

​