Re: any known DBD::pg change caused this utf8 issue?
Shaomei Liu <[email protected]> Thu, 19 Dec 2024 11:05:28 -0500
| Newsgroups | gmane.comp.db.postgresql.dbdpg |
|---|---|
| Message-ID | <CAK70g6MsSz-8yTJOuw98D=o=J4b297Vj0QKCNPsCA8LntC5PMg@mail.gmail.com> |
--000000000000a958180629a1b3f6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable thank you, Warstone! EL8 pg_lib_version 120001 pg_enable_utf8:1 EL7 pg_lib_version: 90224 pg_enable_utf8:1 not sure if that is the version you asked though. Shirley On Wed, Dec 18, 2024 at 10:32=E2=80=AFPM [email protected] <[email protected]= > wrote: > > Can you provide DBD::Pg versions for your EL7 and EL8 distros? And check > this flag: https://metacpan.org/pod/DBD::Pg#pg_enable_utf8-(integer) > > > > > > =D0=A7=D0=B5=D1=82=D0=B2=D0=B5=D1=80=D0=B3, 19 =D0=B4=D0=B5=D0=BA=D0=B0= =D0=B1=D1=80=D1=8F 2024, 0:34 +03:00 =D0=BE=D1=82 Shaomei Liu < > [email protected]>: > > send again after subscribing. > > On Wed, Dec 18, 2024 at 11:20AM Shaomei Liu <[email protected] > <//e.mail.ru/compose/?mailto=3Dmailto%[email protected]>> 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) > > > --000000000000a958180629a1b3f6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">thank you, Warstone!<div>EL8 pg_lib_version 120001 pg_enab= le_utf8:1</div><div>EL7 pg_lib_version: 90224 =C2=A0pg_enable_utf8:1</div><= div>not sure if that is the version you asked though.</div><div>Shirley</di= v></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"lt= r" class=3D"gmail_attr">On Wed, Dec 18, 2024 at 10:32=E2=80=AFPM <a href=3D= "mailto:[email protected]">[email protected]</a> <<a href=3D"mailto:warsto= [email protected]">[email protected]</a>> wrote:<br></div><blockquote class=3D"g= mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204= ,204,204);padding-left:1ex"> <div><div>=C2=A0</div><div>Can you provide DBD::Pg versions for your EL7 an= d EL8 distros? And check this flag: <a href=3D"https://metacpan.org/pod/DBD= ::Pg#pg_enable_utf8-(integer)" target=3D"_blank">https://metacpan.org/pod/D= BD::Pg#pg_enable_utf8-(integer)</a></div><div>=C2=A0</div><div><div><div>= =C2=A0</div></div></div><div>=C2=A0</div><div>=C2=A0</div><blockquote style= =3D"border-left:1px solid rgb(8,87,166);margin:10px;padding:0px 0px 0px 10p= x">=D0=A7=D0=B5=D1=82=D0=B2=D0=B5=D1=80=D0=B3, 19 =D0=B4=D0=B5=D0=BA=D0=B0= =D0=B1=D1=80=D1=8F 2024, 0:34 +03:00 =D0=BE=D1=82 Shaomei Liu <<a href= =3D"mailto:[email protected]" target=3D"_blank">sliu.newjersey@gmail= .com</a>>:<br>=C2=A0<div id=3D"m_6122762659938024578"><div><div><div id= =3D"m_6122762659938024578style_17345576531395160766_BODY"><div><div>send ag= ain after subscribing.</div>=C2=A0<div><div><div><span>On Wed, Dec 18, 2024= at 11:20AM Shaomei Liu <<a href=3D"//e.mail.ru/compose/?mailto=3Dmailto= %[email protected]" target=3D"_blank">[email protected]</a>= > wrote:</span></div><div><blockquote style=3D"margin:0px 0px 0px 0.8ex;= border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>Hello,<div><d= iv>I have a project which uses DBI to write to postgres DB.</div><div>after= upgrading from RHEL7 to RHEL8, the utf-8 character is not displayed proper= ly in the DB. DB has correct utf-8 encoding set.</div><div>for example, lef= t double quotation mark=C2=A0 =C2=A0=E2=80=9C=C2=A0 is displayed as=C2=A0<s= pan style=3D"background-color:rgb(31,31,31);color:rgb(204,204,204);font-fam= ily:Consolas,"Courier New",monospace;font-size:14px">=C3=A2\u0080= \u009C</span>.</div></div><div>with support from DBI community, the issue w= as solved by calling decode from Encode module before writing to DB.</div><= div>wondering what is the change from DBD::pg cause this issue.</div><div>= =C2=A0</div><div><div>perl version is 5.26.3 and 5.16.3 on EL8 and EL7 resp= ectively.</div><div>DBI version is 1.641 and 1.627 on EL8 and EL7 respectiv= ely.</div></div><div>=C2=A0</div><div>here is the program and execution res= ults.</div><div>Any feedback are greatly appreciated!</div><div>thank you</= div><div>Shirley</div><div>=C2=A0</div><div><div style=3D"background-color:= rgb(31,31,31);color:rgb(204,204,204);font-family:Consolas,"Courier New= ",monospace;font-size:14px;line-height:19px;white-space:pre-wrap"><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_decode</span>.<span sty= le=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,212,212)">/</span>b= in<span style=3D"color:rgb(212,212,212)">/</span>perl</div><div>use strict;= </div><div>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 style=3D"color:rgb(156,220,254)">Encode</span> <span style=3D"color:r= gb(206,145,120)">'decode'</span>;</div><div><span style=3D"color:rg= b(156,220,254)">print</span> <span style=3D"color:rgb(206,145,120)">"D= BI version: $DBI::VERSION</span><span style=3D"color:rgb(215,186,125)">\n</= span><span style=3D"color:rgb(206,145,120)">"</span>;</div>=C2=A0<div>= <span style=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(1= 56,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><di= v><span style=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb= (156,220,254)">$host</span> <span style=3D"color:rgb(212,212,212)">=3D</spa= n> <span style=3D"color:rgb(206,145,120)">"db"</span>;</div><div>= <span style=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(1= 56,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:r= gb(156,220,254)">$pass</span> <span style=3D"color:rgb(212,212,212)">=3D</s= pan> <span style=3D"color:rgb(206,145,120)">""</span>;</div><div>= <span style=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(1= 56,220,254)">$dbh</span> <span style=3D"color:rgb(212,212,212)">=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(206,145,120)">&qu= ot;DBI:Pg:dbname=3D$db;host=3D$host"</span>,<span style=3D"color:rgb(1= 56,220,254)">$user</span>,<span style=3D"color:rgb(156,220,254)">$pass</spa= n>);</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,21= 2)">=3D</span> <span style=3D"color:rgb(206,145,120)">'INSERT INTO tabl= e1 (title) VALUES (?)'</span>;</div><div><span style=3D"color:rgb(156,2= 20,254)">my</span> <span style=3D"color:rgb(156,220,254)">$query</span> <sp= an 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>-><s= pan style=3D"color:rgb(220,220,170)">prepare</span>(<span style=3D"color:rg= b(156,220,254)">$sql</span>);</div><div><span style=3D"color:rgb(156,220,25= 4)">my</span> <span style=3D"color:rgb(156,220,254)">$bytes</span> <span st= yle=3D"color:rgb(212,212,212)">=3D</span> <span style=3D"color:rgb(206,145,= 120)">'=E2=80=9C'</span>;</div><div><span style=3D"color:rgb(156,22= 0,254)">my</span> <span style=3D"color:rgb(156,220,254)">$chars</span> <spa= n 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&#= 39;</span>, <span style=3D"color:rgb(156,220,254)">$bytes</span>);</div><di= v><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,12= 0)">" characters</span><span style=3D"color:rgb(215,186,125)">\n</span= ><span style=3D"color:rgb(206,145,120)">"</span>;</div><div><span styl= e=3D"color:rgb(156,220,254)">print</span> <span style=3D"color:rgb(206,145,= 120)">"after decode $bytes contains "</span>.<span style=3D"color= :rgb(220,220,170)">length</span>($chars).<span style=3D"color:rgb(206,145,1= 20)">" characters</span><span style=3D"color:rgb(215,186,125)">\n</spa= n><span style=3D"color:rgb(206,145,120)">"</span>;</div><div><span sty= le=3D"color:rgb(156,220,254)">#my</span> @values <span style=3D"color:rgb(2= 12,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 but =C3=A2\u0080\u009C on EL8</div><div><span style=3D"color:rgb(156,2= 20,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 both EL8 <span style= =3D"color:rgb(212,212,212)">and</span> EL7, decode fixed the issue</div><di= v>$<span style=3D"color:rgb(156,220,254)">query</span>-><span style=3D"c= olor:rgb(220,220,170)">execute</span>(@values);</div>=C2=A0<div>###########= #### running on EL8</div><div><span style=3D"color:rgb(156,220,254)">xxx</s= pan>.<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)">/</sp= an><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:rgb(181,206,168)">1.641</span></div><div>=E2=80=9C contains <span st= yle=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> characte= rs</div>=C2=A0<div>[<span style=3D"color:rgb(156,220,254)">yyy</span>.<span= style=3D"color:rgb(156,220,254)">com</span>]$ psql -Upostgres -hdb debugut= f</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></d= iv><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></sp= an>OK with decode, so decode fixed the issue</div><div>(<span style=3D"colo= r:rgb(181,206,168)">2</span> rows)</div>=C2=A0<div>############### running = on EL7</div><div><span style=3D"color:rgb(156,220,254)">xxx</span>.<span st= yle=3D"color:rgb(156,220,254)">com</span><span style=3D"color:rgb(212,212,2= 12)">></span> .<span style=3D"color:rgb(212,212,212)">/</span><span styl= e=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:rgb(18= 1,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 cont= ains <span style=3D"color:rgb(181,206,168)">1</span> characters</div>=C2=A0= <div>[<span style=3D"color:rgb(156,220,254)">yyy</span>.<span style=3D"colo= r:rgb(156,220,254)">com</span>]$ psql -Upostgres -hdb debugutf</div><div>ps= ql (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><sp= an 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,2= 12,212)">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D></span>OK without decod= e</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></sp= an>OK with decode</div><div>(<span style=3D"color:rgb(181,206,168)">2</span= > rows)</div></div></div></div></blockquote></div></div></div></div></div><= /div></div></div></blockquote><div>=C2=A0</div></div> </blockquote></div> --000000000000a958180629a1b3f6--