原 对于SQL Server数据迁移至PostgreSQL出错的解释以及解决建议

最近对SQL Server到PostgreSQL的数据迁移时出现了问题,返回的错误为:invalid byte sequence for encoding "UTF8": 0x00。

经查证pg源代码,该问题引起的原因是sql server的字符类型字段中含有空字符\0,该字符在pg中不支持。

问题重现:

1、PG客户端:

postgres=# create table text_test (id int,info text);
CREATE TABLE
postgres=# insert into text_test values (1,E'\0x00');
ERROR:  invalid byte sequence for encoding "UTF8": 0x00

2、SQL Server产生数据

create table test_varchar(id int,name varchar(20));
insert into test_varchar values (1, 'name' + char(0));
insert into test_varchar values (1, 'name' + '');

然后通过java程序进行获取数据并插入到PG,同样会得到错误信息:

invalid byte sequence for encoding "UTF8": 0x00

首先我们认为此为gb2312转化到UTF8时,发生了无法转化的错误。经查UTF8是变长的, 1-6个字节。他的编码规则如下:

Bits

Last code point

Byte 1

Byte 2

Byte 3

Byte 4

Byte 5

Byte 6

7

U+007F

0xxxxxxx

11

U+07FF

110xxxxx

10xxxxxx

16

U+FFFF

1110xxxx

10xxxxxx

10xxxxxx

21

U+1FFFFF

11110xxx

10xxxxxx

10xxxxxx

10xxxxxx

26

U+3FFFFFF

111110xx

10xxxxxx

10xxxxxx

10xxxxxx

10xxxxxx

31

U+7FFFFFFF

1111110x

10xxxxxx

10xxxxxx

10xxxxxx

10xxxxxx

10xxxxxx

而0x00是符合UTF8规则的。这就使我们非常诧异。然后我们发现有两点继而确认了问题:

1、

PostgreSQL doesn't support storing NULL (\0x00) characters in text fields (this is obviously different from the database NULL value, which is fully supported).

If you need to store the NULL character, you must use a bytea field - which should store anything you want, but won't support text operations on it.

Given that PostgreSQL doesn't support it in text values, there's no good way to get it to remove it. You could import your data into bytea and later convert it to text using a special function (in perl or something, maybe?), but it's likely going to be easier to do that in preprocessing before you load it.

Source:http://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-encoding-utf8-0x0

2、

Terminating character

Indicated by

Tab

\t This is the default field terminator.

Newline character

\n This is the default row terminator.

Carriage return/line feed

\r

Backslash1

\\

Null terminator (nonvisible terminator)2

\0

Any printable character (control characters are not printable, except null, tab, newline, and carriage return)

(*, A, t, l, and so on)

String of up to 10 printable characters, including some or all of the terminators listed earlier

(**\t**, end, !!!!!!!!!!, \t—\n, and so on)

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

由此我们确定,是pg对null的处理和SQL Server处理是不相同的,所以在这里出现了错误。

而导致这一问题的PG具体代码如下(src/backend/utils/mb/wchar.c的pg_verify_mbstr_len):

if (!IS_HIGHBIT_SET(*mbstr))
		{
			if (*mbstr != '\0')
			{
				mb_len++;
				mbstr++;
				len--;
				continue;
			}
			if (noError)
				return -1;
			report_invalid_encoding(encoding, mbstr, len);
		}
#define IS_HIGHBIT_SET(ch)		((unsigned char)(ch) & HIGHBIT)
#define HIGHBIT					(0x80)

report_invalid_encoding函数是将错误信息返回,也就是

invalid byte sequence for encoding "UTF8": 0x00

而真正导致这一问题的就是:

!IS_HIGHBIT_SET(*mbstr)当*mbstr为0x00时进入判断,然后进而判断*mbstr是否为\0,当为\0时,直接进入函数report_invalid_encoding报错。

所以出现此问题的原因是PG和SQL Server对null的处理是不相同的。

处理建议 :

1、将SQL Server源数据进行修改方法,

UPDATE: This seems to work:

Select * from TABLE
where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0
So:

Update TABLE
SET naughtyField = SUBSTRING(naughtyField, 1, LEN(naughtyField) - 1)
where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0

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

2、对应用进行修改,获取到SQL Server数据时,将数据进行转化,和第一种方法异曲同工。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏龙首琴剑庐

mybaits3整合spring总结

1、maven定义properties: <org.springframework.version>4.3.1.RELEASE</org.springframe...

37870
来自专栏数据库新发现

Oracle诊断案例-Job任务停止执行

Last Updated: Saturday, 2004-11-20 12:47 Eygle

8420
来自专栏python3

数据库分批导出为csv文件

15420
来自专栏扎心了老铁

springboot mybatis优雅的添加多数据源

springboot的原则是简化配置,本文试图不通过xml配置,使用configuration配置数据源,并进行简单的数据访问。 并且配置了多数据源,在开发过程...

47460
来自专栏数据库新发现

Oracle诊断案例-Sql_trace之二

http://www.eygle.com/case/sql_trace_2.htm

12240
来自专栏一个会写诗的程序员的博客

6.1 Spring Boot集成mybatis6.1 Spring Boot集成mybatis

在SpringBoot集成MyBatis时,我们将去掉和Mybatis配置相关的xml文件配置方式,用注解和属性配置来代替这些繁杂的配置。

10850
来自专栏数据库新发现

Oracle诊断案例-Job任务停止执行[最终版]

Last Updated: Friday, 2004-11-26 9:48 Eygle

13330
来自专栏Jerry的SAP技术分享

ABAP OPEN SQL里OPEN CURSOR和SELECT的比较

After the OPEN CURSOR statement, the database cursor is positioned in front of t...

39290
来自专栏公众号_薛勤的博客

SpringBoot整合Mybatis使用注解或XML的方式开发

[User{id=1, name='name', age=18}, User{id=2, name='name2', age=19}, User{id=3, n...

8620
来自专栏杨建荣的学习笔记

探究AWR 第一篇

statspack相比awr算是比较通用,而且免费,可以在标准版,企业版中使用,awr是新企业版本中才有的,算是statspack的一个升级版,而且代码不公开。...

36570

扫码关注云+社区

领取腾讯云代金券