any known DBD::pg change caused this utf8 issue?
Shaomei Liu <[email protected]> Wed, 18 Dec 2024 11:20:27 -0500
| Newsgroups | gmane.comp.db.postgresql.dbdpg |
|---|---|
| Message-ID | <CAK70g6PpyKPrZyRUxNiQVgVVP62GEq21R+kOC7CwZMNBJcfhGw@mail.gmail.com> |
--00000000000053656f06298dcbfd
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Hello,
I have a project which uses DBI to write to postgres DB.
after upgrading from RHEL7 to RHEL8, the utf-8 character is not displayed
properly in the DB. DB has correct utf-8 encoding set.
for example, left double quotation mark =E2=80=9C is displayed as =C3=A2=
\u0080\u009C.
with support from DBI community, the issue was solved by calling decode
from Encode module before writing to DB.
wondering what is the change from DBD::pg cause this issue.
perl version is 5.26.3 and 5.16.3 on EL8 and EL7 respectively.
DBI version is 1.641 and 1.627 on EL8 and EL7 respectively.
here is the program and execution results.
Any feedback are greatly appreciated!
thank you
Shirley
xxx.com> cat testutf_decode.pl
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Encode 'decode';
print "DBI version: $DBI::VERSION\n";
my $db =3D "debugutf";
my $host =3D "db";
my $user =3D "postgres";
my $pass =3D "";
my $dbh =3D DBI->connect("DBI:Pg:dbname=3D$db;host=3D$host",$user,$pass);
my $sql =3D 'INSERT INTO table1 (title) VALUES (?)';
my $query =3D $dbh->prepare($sql);
my $bytes =3D '=E2=80=9C';
my $chars =3D decode('UTF-8', $bytes);
print "$bytes contains ".length($bytes)." characters\n";
print "after decode $bytes contains ".length($chars)." characters\n";
#my @values =3D ($bytes); #=3D=3D=3D=3D=3D=3D=3D>without decode, Database s=
hows =E2=80=9C on EL7
but =C3=A2\u0080\u009C on EL8
my @values =3D ($chars); #=3D=3D=3D=3D=3D=3D>with decode, Database shows =
=E2=80=9C on both EL8
and EL7, decode fixed the issue
$query->execute(@values);
############### running on EL8
xxx.com> ./testutf_decode.pl
DBI version: 1.641
=E2=80=9C contains 3 characters
after decode =E2=80=9C contains 1 characters
[yyy.com]$ psql -Upostgres -hdb debugutf
psql (16.6)
debugutf=3D# select * from table1;
title
---------------
=C3=A2\u0080\u009C =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>NOK without decode
=E2=80=9C =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>OK with dec=
ode, so decode fixed the issue
(2 rows)
############### running on EL7
xxx.com> ./testutf_decode.pl
DBI version: 1.627
=E2=80=9C contains 3 characters
after decode =E2=80=9C contains 1 characters
[yyy.com]$ psql -Upostgres -hdb debugutf
psql (16.6)
debugutf=3D# select * from table1;
title
---------------
=E2=80=9C =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>OK without dec=
ode
=E2=80=9C =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>OK with decode
(2 rows)
--00000000000053656f06298dcbfd
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">Hello,<div><div>I have a project which uses DBI to write t=
o postgres DB.</div><div>after upgrading from RHEL7 to RHEL8, the utf-8 cha=
racter is not displayed properly in the DB. DB has correct utf-8 encoding s=
et.</div><div>for example, left double quotation mark=C2=A0 =C2=A0=E2=80=9C=
=C2=A0 is displayed as=C2=A0<span style=3D"background-color:rgb(31,31,31);c=
olor:rgb(204,204,204);font-family:Consolas,"Courier New",monospac=
e;font-size:14px">=C3=A2\u0080\u009C</span>.</div></div><div>with support f=
rom DBI community, the issue was solved by calling decode from Encode modul=
e before writing to DB.</div><div>wondering what is the change from DBD::pg=
cause this issue.</div><div><br></div><div><div>perl version is 5.26.3 and=
5.16.3 on EL8 and EL7 respectively.</div><div>DBI version is 1.641 and 1.6=
27 on EL8 and EL7 respectively.</div></div><div><br></div><div>here is the =
program and execution results.</div><div>Any feedback are greatly appreciat=
ed!</div><div>thank you</div><div>Shirley</div><div><br></div><div><div sty=
le=3D"color:rgb(204,204,204);background-color:rgb(31,31,31);font-family:Con=
solas,"Courier New",monospace;font-size:14px;line-height:19px;whi=
te-space:pre"><div><span style=3D"color:rgb(156,220,254)">xxx</span>.<span =
style=3D"color:rgb(156,220,254)">com</span><span style=3D"color:rgb(212,212=
,212)">></span> cat <span style=3D"color:rgb(156,220,254)">testutf_decod=
e</span>.<span style=3D"color:rgb(156,220,254)">pl</span></div><div>#<span =
style=3D"color:rgb(212,212,212)">!/</span>usr<span style=3D"color:rgb(212,2=
12,212)">/</span>bin<span style=3D"color:rgb(212,212,212)">/</span>perl</di=
v><div>use strict;</div><div>use <span style=3D"color:rgb(156,220,254)">war=
nings</span>;</div><div>use <span style=3D"color:rgb(156,220,254)">DBI</spa=
n>;</div><div>use <span style=3D"color:rgb(156,220,254)">Encode</span> <spa=
n style=3D"color:rgb(206,145,120)">'decode'</span>;</div><div><span=
style=3D"color:rgb(156,220,254)">print</span> <span style=3D"color:rgb(206=
,145,120)">"DBI version: $DBI::VERSION</span><span style=3D"color:rgb(=
215,186,125)">\n</span><span style=3D"color:rgb(206,145,120)">"</span>=
;</div><br><div><span style=3D"color:rgb(156,220,254)">my</span> <span styl=
e=3D"color:rgb(156,220,254)">$db</span> <span style=3D"color:rgb(212,212,21=
2)">=3D</span> <span style=3D"color:rgb(206,145,120)">"debugutf"<=
/span>;</div><div><span style=3D"color:rgb(156,220,254)">my</span> <span st=
yle=3D"color:rgb(156,220,254)">$host</span> <span style=3D"color:rgb(212,21=
2,212)">=3D</span> <span style=3D"color:rgb(206,145,120)">"db"</s=
pan>;</div><div><span style=3D"color:rgb(156,220,254)">my</span> <span styl=
e=3D"color:rgb(156,220,254)">$user</span> <span style=3D"color:rgb(212,212,=
212)">=3D</span> <span style=3D"color:rgb(206,145,120)">"postgres"=
;</span>;</div><div><span style=3D"color:rgb(156,220,254)">my</span> <span =
style=3D"color:rgb(156,220,254)">$pass</span> <span style=3D"color:rgb(212,=
212,212)">=3D</span> <span style=3D"color:rgb(206,145,120)">""</s=
pan>;</div><div><span style=3D"color:rgb(156,220,254)">my</span> <span styl=
e=3D"color:rgb(156,220,254)">$dbh</span> <span style=3D"color:rgb(212,212,2=
12)">=3D</span> <span style=3D"color:rgb(156,220,254)">DBI</span>-><span=
style=3D"color:rgb(220,220,170)">connect</span>(<span style=3D"color:rgb(2=
06,145,120)">"DBI:Pg:dbname=3D$db;host=3D$host"</span>,<span styl=
e=3D"color:rgb(156,220,254)">$user</span>,<span style=3D"color:rgb(156,220,=
254)">$pass</span>);</div><div><span style=3D"color:rgb(156,220,254)">my</s=
pan> <span style=3D"color:rgb(156,220,254)">$sql</span> <span style=3D"colo=
r:rgb(212,212,212)">=3D</span> <span style=3D"color:rgb(206,145,120)">'=
INSERT INTO table1 (title) VALUES (?)'</span>;</div><div><span style=3D=
"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220,254)">$=
query</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <span style=
=3D"color:rgb(156,220,254)">$</span><span style=3D"color:rgb(156,220,254)">=
dbh</span>-><span style=3D"color:rgb(220,220,170)">prepare</span>(<span =
style=3D"color:rgb(156,220,254)">$sql</span>);</div><div><span style=3D"col=
or:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220,254)">$byte=
s</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <span style=3D"c=
olor:rgb(206,145,120)">'=E2=80=9C'</span>;</div><div><span style=3D=
"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220,254)">$=
chars</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <span style=
=3D"color:rgb(220,220,170)">decode</span>(<span style=3D"color:rgb(206,145,=
120)">'UTF-8'</span>, <span style=3D"color:rgb(156,220,254)">$bytes=
</span>);</div><div><span style=3D"color:rgb(156,220,254)">print</span> <sp=
an style=3D"color:rgb(206,145,120)">"$bytes contains "</span>.<sp=
an style=3D"color:rgb(220,220,170)">length</span>($bytes).<span style=3D"co=
lor:rgb(206,145,120)">" characters</span><span style=3D"color:rgb(215,=
186,125)">\n</span><span style=3D"color:rgb(206,145,120)">"</span>;</d=
iv><div><span style=3D"color:rgb(156,220,254)">print</span> <span style=3D"=
color:rgb(206,145,120)">"after decode $bytes contains "</span>.<s=
pan style=3D"color:rgb(220,220,170)">length</span>($chars).<span style=3D"c=
olor:rgb(206,145,120)">" characters</span><span style=3D"color:rgb(215=
,186,125)">\n</span><span style=3D"color:rgb(206,145,120)">"</span>;</=
div><div><span style=3D"color:rgb(156,220,254)">#my</span> @values <span st=
yle=3D"color:rgb(212,212,212)">=3D</span> ($bytes); #<span style=3D"color:r=
gb(212,212,212)">=3D=3D=3D=3D=3D=3D=3D></span>without decode, Database s=
hows =E2=80=9C on EL7 but =C3=A2\u0080\u009C on EL8</div><div><span style=
=3D"color:rgb(156,220,254)">my</span> @values <span style=3D"color:rgb(212,=
212,212)">=3D</span> ($chars); =C2=A0#<span style=3D"color:rgb(212,212,212)=
">=3D=3D=3D=3D=3D=3D></span>with decode, Database shows =E2=80=9C on bot=
h EL8 <span style=3D"color:rgb(212,212,212)">and</span> EL7, decode fixed t=
he issue</div><div>$<span style=3D"color:rgb(156,220,254)">query</span>->=
;<span style=3D"color:rgb(220,220,170)">execute</span>(@values); </div><br>=
<div>############### running on EL8</div><div><span style=3D"color:rgb(156,=
220,254)">xxx</span>.<span style=3D"color:rgb(156,220,254)">com</span><span=
style=3D"color:rgb(212,212,212)">></span> .<span style=3D"color:rgb(212=
,212,212)">/</span><span style=3D"color:rgb(156,220,254)">testutf_decode</s=
pan>.<span style=3D"color:rgb(156,220,254)">pl</span></div><div>DBI version=
: <span style=3D"color:rgb(181,206,168)">1.641</span></div><div>=E2=80=9C c=
ontains <span style=3D"color:rgb(181,206,168)">3</span> characters</div><di=
v>after decode =E2=80=9C contains <span style=3D"color:rgb(181,206,168)">1<=
/span> characters</div><br><div>[<span style=3D"color:rgb(156,220,254)">yyy=
</span>.<span style=3D"color:rgb(156,220,254)">com</span>]$ psql -Upostgres=
-hdb debugutf</div><div>psql (16.6)</div><div>debugutf=3D# select * from t=
able1;</div><div>=C2=A0 =C2=A0 =C2=A0<span style=3D"color:rgb(156,220,254)"=
>title</span></div><div><span style=3D"color:rgb(212,212,212)">------------=
---</span></div><div>=C2=A0=C3=A2\u0080\u009C =C2=A0<span style=3D"color:rg=
b(212,212,212)">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D></span>NOK without decode=
</div><div>=C2=A0=E2=80=9C =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
<span style=3D"color:rgb(212,212,212)">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D></span>OK with decode, so decode fixed the issue</div><div>(<span st=
yle=3D"color:rgb(181,206,168)">2</span> rows)</div><br><div>###############=
running on EL7</div><div><span style=3D"color:rgb(156,220,254)">xxx</span>=
.<span style=3D"color:rgb(156,220,254)">com</span><span style=3D"color:rgb(=
212,212,212)">></span> .<span style=3D"color:rgb(212,212,212)">/</span><=
span style=3D"color:rgb(156,220,254)">testutf_decode</span>.<span style=3D"=
color:rgb(156,220,254)">pl</span></div><div>DBI version: <span style=3D"col=
or:rgb(181,206,168)">1.627</span></div><div>=E2=80=9C contains <span style=
=3D"color:rgb(181,206,168)">3</span> characters</div><div>after decode =E2=
=80=9C contains <span style=3D"color:rgb(181,206,168)">1</span> characters<=
/div><br><div>[<span style=3D"color:rgb(156,220,254)">yyy</span>.<span styl=
e=3D"color:rgb(156,220,254)">com</span>]$ psql -Upostgres -hdb debugutf</di=
v><div>psql (16.6)</div><div>debugutf=3D# select * from table1;</div><div>=
=C2=A0 =C2=A0 =C2=A0<span style=3D"color:rgb(156,220,254)">title</span></di=
v><div><span style=3D"color:rgb(212,212,212)">---------------</span></div><=
div>=C2=A0=E2=80=9C =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <span style=3D"color=
:rgb(212,212,212)">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D></span>OK wit=
hout decode</div><div>=C2=A0=E2=80=9C =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <s=
pan style=3D"color:rgb(212,212,212)">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D></span>OK with decode</div><div>(<span style=3D"color:rgb(181,206,16=
8)">2</span> rows)</div></div></div></div>
--00000000000053656f06298dcbfd--