Re: any known DBD::pg change caused this utf8 issue?
[email protected] (Shaomei Liu) Wed, 18 Dec 2024 16:33:53 -0500
| Newsgroups | perl.dbd.pg |
|---|---|
| Message-ID | <CAK70g6N_ouVFNj3SHpeigBh6m_EEbdoO+N940uDpoFR+eHDrxg@mail.gmail.com> |
--0000000000003e3ea80629922cce
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
send again after subscribing.
On Wed, Dec 18, 2024 at 11:20=E2=80=AFAM Shaomei Liu <sliu.newjersey@gmail.=
com>
wrote:
> 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=
shows =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 d=
ecode, 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 d=
ecode
> =E2=80=9C =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>OK with deco=
de
> (2 rows)
>
--0000000000003e3ea80629922cce
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">send again after subscribing.</div><br><div class=3D"gmail=
_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed,=
Dec 18, 2024 at 11:20=E2=80=AFAM Shaomei Liu <<a href=3D"mailto:sliu.ne=
[email protected]">[email protected]</a>> wrote:<br></div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hello,<div><div=
>I have a project which uses DBI to write to postgres DB.</div><div>after u=
pgrading from RHEL7 to RHEL8, the utf-8 character is not displayed properly=
in the DB. DB has correct utf-8 encoding set.</div><div>for example, left =
double quotation mark=C2=A0 =C2=A0=E2=80=9C=C2=A0 is displayed as=C2=A0<spa=
n style=3D"background-color:rgb(31,31,31);color:rgb(204,204,204);font-famil=
y:Consolas,"Courier New",monospace;font-size:14px">=C3=A2\u0080\u=
009C</span>.</div></div><div>with support from DBI community, the issue was=
solved by calling decode from Encode module before writing to DB.</div><di=
v>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 respectiv=
ely.</div><div>DBI version is 1.641 and 1.627 on EL8 and EL7 respectively.<=
/div></div><div><br></div><div>here is the program and execution results.</=
div><div>Any feedback are greatly appreciated!</div><div>thank you</div><di=
v>Shirley</div><div><br></div><div><div style=3D"color:rgb(204,204,204);bac=
kground-color:rgb(31,31,31);font-family:Consolas,"Courier New",mo=
nospace;font-size:14px;line-height:19px;white-space:pre-wrap"><div><span st=
yle=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 <spa=
n style=3D"color:rgb(156,220,254)">testutf_decode</span>.<span style=3D"col=
or:rgb(156,220,254)">pl</span></div><div>#<span style=3D"color:rgb(212,212,=
212)">!/</span>usr<span style=3D"color:rgb(212,212,212)">/</span>bin<span s=
tyle=3D"color:rgb(212,212,212)">/</span>perl</div><div>use strict;</div><di=
v>use <span style=3D"color:rgb(156,220,254)">warnings</span>;</div><div>use=
<span style=3D"color:rgb(156,220,254)">DBI</span>;</div><div>use <span sty=
le=3D"color:rgb(156,220,254)">Encode</span> <span style=3D"color:rgb(206,14=
5,120)">'decode'</span>;</div><div><span style=3D"color:rgb(156,220=
,254)">print</span> <span style=3D"color:rgb(206,145,120)">"DBI versio=
n: $DBI::VERSION</span><span style=3D"color:rgb(215,186,125)">\n</span><spa=
n style=3D"color:rgb(206,145,120)">"</span>;</div><br><div><span style=
=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220,254)=
">$db</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <span style=
=3D"color:rgb(206,145,120)">"debugutf"</span>;</div><div><span st=
yle=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220,2=
54)">$host</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <span s=
tyle=3D"color:rgb(206,145,120)">"db"</span>;</div><div><span styl=
e=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220,254=
)">$user</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <span sty=
le=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)">""</span>;</div><div><span styl=
e=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220,254=
)">$dbh</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <span styl=
e=3D"color:rgb(156,220,254)">DBI</span>-><span style=3D"color:rgb(220,22=
0,170)">connect</span>(<span style=3D"color:rgb(206,145,120)">"DBI:Pg:=
dbname=3D$db;host=3D$host"</span>,<span style=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</span> <span style=3D"color:=
rgb(156,220,254)">$sql</span> <span style=3D"color:rgb(212,212,212)">=3D</s=
pan> <span style=3D"color:rgb(206,145,120)">'INSERT INTO table1 (title)=
VALUES (?)'</span>;</div><div><span style=3D"color:rgb(156,220,254)">m=
y</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 styl=
e=3D"color:rgb(220,220,170)">prepare</span>(<span style=3D"color:rgb(156,22=
0,254)">$sql</span>);</div><div><span style=3D"color:rgb(156,220,254)">my</=
span> <span style=3D"color:rgb(156,220,254)">$bytes</span> <span style=3D"c=
olor:rgb(212,212,212)">=3D</span> <span style=3D"color:rgb(206,145,120)">&#=
39;=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'</sp=
an>, <span style=3D"color:rgb(156,220,254)">$bytes</span>);</div><div><span=
style=3D"color:rgb(156,220,254)">print</span> <span style=3D"color:rgb(206=
,145,120)">"$bytes contains "</span>.<span style=3D"color:rgb(220=
,220,170)">length</span>($bytes).<span style=3D"color:rgb(206,145,120)">&qu=
ot; 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"co=
lor:rgb(156,220,254)">print</span> <span style=3D"color:rgb(206,145,120)">&=
quot;after decode $bytes contains "</span>.<span style=3D"color:rgb(22=
0,220,170)">length</span>($chars).<span style=3D"color:rgb(206,145,120)">&q=
uot; 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"c=
olor:rgb(156,220,254)">#my</span> @values <span style=3D"color:rgb(212,212,=
212)">=3D</span> ($bytes); #<span style=3D"color:rgb(212,212,212)">=3D=3D=
=3D=3D=3D=3D=3D></span>without decode, Database shows =E2=80=9C on EL7 b=
ut =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> ($ch=
ars); =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 both EL8 <span style=3D"colo=
r:rgb(212,212,212)">and</span> EL7, decode fixed the 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>############### runn=
ing on EL8</div><div><span style=3D"color:rgb(156,220,254)">xxx</span>.<spa=
n style=3D"color:rgb(156,220,254)">com</span><span style=3D"color:rgb(212,2=
12,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"color:rg=
b(181,206,168)">1.641</span></div><div>=E2=80=9C contains <span style=3D"co=
lor: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><b=
r><div>[<span style=3D"color:rgb(156,220,254)">yyy</span>.<span style=3D"co=
lor:rgb(156,220,254)">com</span>]$ psql -Upostgres -hdb debugutf</div><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></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:rgb(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 wit=
h decode, so decode fixed the issue</div><div>(<span style=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"colo=
r: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,25=
4)">pl</span></div><div>DBI version: <span style=3D"color: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 s=
tyle=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)</di=
v><div>debugutf=3D# select * from table1;</div><div>=C2=A0 =C2=A0 =C2=A0<sp=
an style=3D"color:rgb(156,220,254)">title</span></div><div><span style=3D"c=
olor: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 without decode</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 with de=
code</div><div>(<span style=3D"color:rgb(181,206,168)">2</span> rows)</div>=
</div></div></div>
</blockquote></div>
--0000000000003e3ea80629922cce--