[转] SqlServe到PG迁移错误:无效的编码序列"UTF8": 0x00

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介:

标签

PostgreSQL , Greenplum , 0x00 , 空字符 , NUL , 数据清洗 , 规则 , 过滤 , 非法字符


背景

原文

http://www.codeweblog.com/sqlserve%E5%88%B0pg%E8%BF%81%E7%A7%BB%E9%94%99%E8%AF%AF-%E6%97%A0%E6%95%88%E7%9A%84%E7%BC%96%E7%A0%81%E5%BA%8F%E5%88%97-utf8-0x00/

环境:

sqlserver2008 R2 (winXP)  
  
postgresql9.3.4 (win7-64bit)  

1. 通过java像PostgreSQL提交批量 insert(或者普通insert或者执行copy):

错误:

java.sql.BatchUpdateException: 批次处理 被中止,呼叫 getNextException 以取得原因。

解决:在catch模块打印出getNextException的内容,就能知道具体的原因是什么了。

catch (ClassNotFoundException | SQLException ex)  
{  
       System.out.println("Error: " + ex.getMessage());  
       ex.printStackTrace(System.out);  
       if (ex instanceof BatchUpdateException)  
       {  
             BatchUpdateException bex = (BatchUpdateException) ex;  
             bex.getNextException().printStackTrace(System.out);  
       }  
}  

2. getNextException抓到的具体错误信息是:org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00

org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00  
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)  
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)  
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)  
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2892)  
 at JDBCTest.ConnTest.main(ConnTest.java:154)  

3. 经查看sqlsever和PG的字符编码:

sql server的字符集编码是GBK(对于sqlserver查看encoding很复杂,我看参考了这个方法 http://bbs.csdn.net/topics/190034619 )

PostgreSQL的字符集编码是UTF8(对于PG来说很容易查看encoding,看一下属性中的encoding就可以)

这样看来两个数据库的字符集编码不同,但是PG的服务器端不支持字符集GBK,也就是创建数据库时不能指定encoding=GBK,PG中可替代GBK的字符集EUC_CN也无法使用。只有客户端可以支持GBK,但该设置只在一个连接中有效,故在插入数据之前执行 ppstmt =conn.prepareStatement("set client_encoding=GBK;");,此时又产生了如下错误:

org.postgresql.util.PSQLException: The server's client_encoding parameter was changed to GBK. The JDBC driver requires client_encoding to be UTF8 for correct operation.  
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1966)  
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)  
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)  
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419)  
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:365)  
    at JDBCTest.ConnTest.main(ConnTest.java:56)  

这样看来,sqlserver和PG的字符集没法实现完全统一。

4. 重新回到invalid byte sequence for encoding "UTF8": 0x00。

我在postgresql的社区邮件列表里找到了这个问题:http://postgresql.1045698.n5.nabble.com/invalid-byte-sequence-for-encoding-quot-UTF8-quot-0x00-td2172080.html

http://postgresql.1045698.n5.nabble.com/supporting-0x00-from-client-Unicode-JDBC-td1969492.html#a1969493

通过邮件列表中提到的内容以及个人多次测试报错表的字段,发现导致问题的原因就是字符类型字段值中含有空字符,这是一个结束符。(Unicode 查询工具 http://www.scarfboy.com/coding/unicode-tool?s=U%2B2323)。

5. 问题重现:

在sqlserver中:

create table test_varchar3 (id int,name varchar(23));  
insert into test_varchar3  values (1,'ddd'+CHAR(0)+'aaa');  
insert into test_varchar3  values (2,'ddd'+CHAR(0));  
insert into test_varchar3  values (3,CHAR(0)+'aaa');  
insert into test_varchar3  values (4,'aaa'); ---注意此种的仅仅是字符串并不是空字符''  
select * from test_varchar3;  

将该表从sqlserver迁移到pg:

通过jdbc取到的(其中空字符无法表示,此处用空格替代):

id,name,name_length  
  
1,ddd aaa,length=7  
  
2,ddd ,length =4  
  
3, aaa,length=4  
  
4,aaa,length=5  

错误重现:invalid byte sequence for encoding "UTF8": 0x00。

解决方法:在向pg中copy或者insert数据提交之前,现将其中的字符类型字段值中的空字符取掉即可成功执行。

比如:str.replaceAll("", ""); 或者str.replace("", "");

6. 扩展问题:

sqlserver中字符类型字段值,当插入的字符值中带有空字符的时候,sqlserver客户端显示时会去掉空字符之后的字符,但是在查询中要查到该条数据还需要匹配之后的内容:

pic

create table test_varchar3 (id int,name varchar(23));  
insert into test_varchar3  values (1,'ddd'+CHAR(0)+'aaa');  
insert into test_varchar3  values (2,'ddd'+CHAR(0));  
insert into test_varchar3  values (3,CHAR(0)+'aaa');  
insert into test_varchar3  values (4,'aaa');  
select * from test_varchar3;  
select * from test_varchar3 where name='ddd';  
id | name  
-------------  
2  |  ddd  
select * from test_varchar3 where name='ddd'+CHAR(0);  
id | name  
-------------  
2  |  ddd  
select * from test_varchar3 where name='ddd'+CHAR(0)+'aaa';  
id | name  
-------------  
1  |  ddd  
select * from test_varchar3 where name=CHAR(0);  
id | name  
------------  
  
select * from test_varchar3 where name=CHAR(0)+'aaa';  
id | name  
-------------  
3  |     

关于如何从sqlsever数据库中去掉子赋值中的空值结束符,可以参考(待补充):

http://stackoverflow.com/questions/3533320/sql-server-remove-end-string-character-0-from-data

所以说尽管空值终止符''是空的不显示的,但是他会对字符类型字段值及其查询等造成影响,所以使用sqlsever最好不要向字符类型字段中插入带有空值终止符''的字符或字符串。

关于空值终止符''或者其他控制符的详细介绍,请参考MS SQL Server官方文档:

http://msdn.microsoft.com/en-us/library/ms191485.aspx

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
7月前
|
SQL 关系型数据库 MySQL
【MySQL异常解决】MySQL执行SQL文件出现【Unknown collation ‘utf8mb4_0900_ai_ci‘】的解决方案
【MySQL异常解决】MySQL执行SQL文件出现【Unknown collation ‘utf8mb4_0900_ai_ci‘】的解决方案
224 0
|
数据库 数据安全/隐私保护 数据库管理
|
9月前
|
关系型数据库 MySQL Linux
mysql 8中utf8和utf8mb4运行时的编码警告提醒[Warning]——my.ini配置问题
mysql 8中utf8和utf8mb4运行时的编码警告提醒[Warning]——my.ini配置问题
|
SQL 关系型数据库 MySQL
MySQL中sp运行check表版本更新流程解析
MySQL中sp运行check表版本更新流程解析
205 0
|
SQL 数据库
MSSQL还原数据库失败 不能选择文件或文件组 Microsoft SQL Server,错误:3219
MSSQL还原数据库失败,提示如下: 还原对与服务器 失败。(Microsoft.SqlServer.Smo) 其它信息: ┃ ┗→执行Transact-SQL语句或批处理时发生异常。(Microsoft.SqlServer.ConnectionsInfo) ┃ ┗→不能选择文件或文件组“……”用于此操作。
2042 0
|
SQL 关系型数据库 数据库
PostgreSQL sql文件编码引起的数据导入乱码或查询字符集异常报错(invalid byte sequence)
标签 PostgreSQL , 乱码 , 文件编码 背景 当用户客户端字符集与服务端字符集不匹配时,写入的多字节字符(例如中文)可能出现乱码。 例子 数据库字符集为sql_ascii,允许存储任意编码字符。
3398 0
|
SQL 存储 测试技术
SQL Server2014 SP2新增的数据库克隆功能
原文:SQL Server2014 SP2新增的数据库克隆功能 SQL Server2014 SP2新增的数据库克隆功能   创建测试库 --创建测试数据库 create database testtest use testtest go --创建表 create ta...
855 0
|
SQL 数据库 Go
SqlServer Bug:复制架构更改参数(replicate_ddl)无效
原文:SqlServer Bug:复制架构更改参数(replicate_ddl)无效 最近测试可更新订阅的架构更改问题,发现了一个 bug。 在复制中,当在发布数据库对发布数据库进行架构更改时,结构自动同步到订阅中(这就是 复制架构更改)。
1274 0
|
SQL 数据库
SQL Server 2008 中收缩数据库(DUMP,TRANSACTION,TRAN,无效,语法错误)
原文:SQL Server 2008 中收缩数据库(DUMP,TRANSACTION,TRAN,无效,语法错误) 从SQL SERVER 2008 开始,我们已经不能再用以前 DUMP TRAN 数据库名 WITH NO_LOG 的...
1605 0