Incorrect string value: '\xC4\x81\xE1\xB8\x91' for column 'field_value' at row 14

刘超 8天前 ⋅ 3747 阅读   编辑

一、描述

  跑spark程序,报如下错误

19/06/19 07:51:50 ERROR Executor: Exception in task 64.3 in stage 36.0 (TID 15136)
java.sql.BatchUpdateException: Incorrect string value: '\xC4\x81\xE1\xB8\x91' for column 'field_value' at row 14
at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1809)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1441)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.savePartition(JdbcUtils.scala:227)

  

二、分析

  1、建表时,指定了ENGINE=InnoDB DEFAULT CHARSET=utf8; 报错

  2、jdbc连接地址中也指定了useUnicode=true,如下

jdbc:mysql://***:3306/***?autoReconnect=true&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false

  但是还是报错

  3、网上查找资料,jdbc连接地址中还需加上useOldUTF8Behavior=true,再次测试,还是不行

  4、将表的field_value改为text,再次测试,还是不行

  5、把计算结果输出到hdfs,然后再从hdfs读取结果写到mysql,就没问题啦 为啥???

  6、网上找到一篇文章,说是mysql中utf8字符MaxLen=3,但是DataFrame的字段title里某些字符转成utf8编码之后有4个字节,需从连接url中去掉useUnicode=true&characterEncoding=utf8,并把表转为utf8mb4格式。

[mysqld]
character-set-server=utf8mb4
[mysql]
default-character-set=utf8mb4 

  如果只去掉useUnicode=true&characterEncoding=utf8,表格式默认(默认是latin1),不行,还是报错

  指定charset为utf8mb4,还是不行

mysql> show create table tmp_in_ipaddress;
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                           |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tmp_in_ipaddress | CREATE TABLE `tmp_in_ipaddress` (
  `countryCode` varchar(1024) DEFAULT NULL,
  `province` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provinceNumber` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

  7、编写正则udf函数过滤掉非法数据,只取有效数据就可以了,示例代码如下

import org.apache.spark.sql.functions.udf
......

def getOSVersionRange = udf {
    (osVersion: String) => {
        if(isNumeric(version)){
            getOsVersionIndex(osVersion)
        } else {
            "-1"
        }
    }
}

def isNumeric(str:String): Boolean = {
  if (str == null) {
    return false;
  }

  return Pattern.compile("[-+]{0,1}\\d+\\.\\d+|[-+]{0,1}\\d*\\.\\d+|[-+]{0,1}\\d+").matcher(str).matches()
}

  最后没找到什么样的数据会导致这个问题,因为数据量太大,采用分类等方法定位异常数据太花费时间啦,所以就没找。想开发数据质量系统来定位异常数据

  8、也遇到了java.sql.BatchUpdateException: Incorrect string value: '\xE7\xA9\xBA\xE5\x86\x9B...' for column 'pv_info' at row 1问题,通过修改spark-sql源码,让其打印出执行的insert语句定位到了异常数据

三、解决方法

  1、方式一:使用udf函数获取有效数据

  2、方式二:修改spark-sql源码,让其打印出执行的insert语句从而定位出异常数据,再排查为啥会有异常数据,是编码导致的吗等等

四、参考连接

  1、Incorrect string value” when trying to insert UTF-8 into MySQL via JDBC?


注意:本文归作者所有,未经作者允许,不得转载

全部评论: 0

    我有话说: