Re: any known DBD::pg change caused this utf8 issue?
Shaomei Liu <[email protected]> Thu, 19 Dec 2024 10:59:55 -0500
| Newsgroups | gmane.comp.db.postgresql.dbdpg |
|---|---|
| Message-ID | <CAK70g6M7_Ay+nMDRhsLtb7YnSEsOL15RvF4XoRGehTNQCLYnog@mail.gmail.com> |
--000000000000c63f700629a19f0c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable thank you, Grant! thanks for sharing the video!! liked your rule of thumb! Shirley On Wed, Dec 18, 2024 at 9:01=E2=80=AFPM Grant McLean <[email protected]> = wrote: > Hello Shirley > > This is a complex topic which includes "encodings" and the way in which > Perl is able to deal with binary data > (e.g.: a string of bytes) vs character data (in which each character migh= t > be represented by one or more bytes). > > There is no "quick fix". You really need to understand when you're dealin= g > with bytes vs characters. As a > rule of thumb you'll want to "decode" data that is coming into your > program and "encode" data that is > being output to the world (e.g.: to a file or in a web page response). > > If you're prepared to make the effort to understand, here's a link to a > video I made on the subject: > > https://www.youtube.com/watch?v=3DcgswnneFp-s > > There are a number of reasons why the behaviour of your code might have > changed following the upgrade. > - The newer versions of libraries and utilities might have different > defaults for handling bytes vs character data. > - The "locale" setting in the upgraded system might be different (e.g.: > LANG=3D"C" vs LANG=3D"en_US.UTF-8"). > - The environment in which the code executes might be different. > > Well-written code that is explicit about handling character data and wher= e > the encoding/decoding should happen > would be resistant to those types of outside influences. > > In the video I walk through a scenario where some code which appeared to > be working correctly but then one small > change broke things in different ways. The fixes are to add in explicit > handling of encoding. > > However this is not really an issue that is specific to DBI or DBD::Pg - > apart from being explicit about your use of the > "pg_enable_utf8" attribute on your database handle: > > https://metacpan.org/pod/DBD::Pg#pg_enable_utf8-(integer) > > I hope that sets you on the right path. > > Regards > Grant McLean > > On Wed, 2024-12-18 at 16:33 -0500, Shaomei Liu wrote: > > send again after subscribing. > > On Wed, Dec 18, 2024 at 11:20=E2=80=AFAM Shaomei Liu <sliu.newjersey@gmai= l.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) > > > --000000000000c63f700629a19f0c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">thank you, Grant!<div>thanks for sharing the video!!</div>= <div>liked your rule of thumb!</div><div>Shirley</div></div><br><div class= =3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr= ">On Wed, Dec 18, 2024 at 9:01=E2=80=AFPM Grant McLean <<a href=3D"mailt= o:[email protected]">[email protected]</a>> wrote:<br></div><blockqu= ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px= solid rgb(204,204,204);padding-left:1ex"><div class=3D"msg-459814963757986= 8175"><div><div>Hello Shirley</div><div><br></div><div>This is a complex to= pic which includes "encodings" and the way in which Perl is able = to deal with binary data=C2=A0</div><div>(e.g.: a string of bytes) vs chara= cter data (in which each character might be represented by one or more byte= s).</div><div><br></div><div>There is no "quick fix". You really = need to understand when you're dealing with bytes vs characters. As a</= div><div>rule of thumb you'll want to "decode" data that is c= oming into your program and "encode" data that is</div><div>being= output to the world (e.g.: to a file or in a web page response).</div><div= ><br></div><div>If you're prepared to make the effort to understand, he= re's a link to a video I made on the subject:<br><br>=C2=A0 =C2=A0=C2= =A0<a href=3D"https://www.youtube.com/watch?v=3DcgswnneFp-s" target=3D"_bla= nk">https://www.youtube.com/watch?v=3DcgswnneFp-s</a></div><div><br></div><= div>There are a number of reasons why the behaviour of your code might have= changed following the upgrade.</div><div>- The newer versions of libraries= and utilities might have different defaults for handling bytes vs characte= r data.</div><div>- The "locale" setting in the upgraded system m= ight be different (e.g.: LANG=3D"C" vs LANG=3D"en_US.UTF-8&q= uot;).</div><div>- The environment in which the code executes might be diff= erent.</div><div><br></div><div>Well-written code that is explicit about ha= ndling character data and where the encoding/decoding should happen</div><d= iv>would be resistant to those types of outside influences.</div><div><br><= /div><div>In the video I walk through a scenario where some code which appe= ared to be working correctly but then one small</div><div>change broke thin= gs in different ways.=C2=A0 The fixes are to add in explicit handling of en= coding.</div><div><br></div><div>However this is not really an issue that i= s specific to DBI or DBD::Pg - apart from being explicit about your use of = the</div><div>"pg_enable_utf8" attribute on your database handle:= </div><div><br></div><div>=C2=A0 =C2=A0=C2=A0<a href=3D"https://metacpan.or= g/pod/DBD::Pg#pg_enable_utf8-(integer)" target=3D"_blank">https://metacpan.= org/pod/DBD::Pg#pg_enable_utf8-(integer)</a></div><div><br></div><div>I hop= e that sets you on the right path.</div><div><br></div><div>Regards</div><d= iv>Grant McLean</div><div><br></div><div>On Wed, 2024-12-18 at 16:33 -0500,= Shaomei Liu wrote:</div><blockquote type=3D"cite" style=3D"margin:0px 0px = 0px 0.8ex;border-left:2px solid rgb(114,159,207);padding-left:1ex"><div dir= =3D"ltr">send again after subscribing.</div><div><br></div><div class=3D"gm= ail_quote"><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:[email protected]" = target=3D"_blank">[email protected]</a>> wrote:<br></div><blockqu= ote type=3D"cite" style=3D"margin:0px 0px 0px 0.8ex;border-left:2px solid r= gb(114,159,207);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 upgrading = from RHEL7 to RHEL8, the utf-8 character is not displayed properly in the D= B. DB has correct utf-8 encoding set.</div><div>for example, left double qu= otation mark=C2=A0 =C2=A0=E2=80=9C=C2=A0 is displayed as=C2=A0<span style= =3D"background-color:rgb(31,31,31);color:rgb(204,204,204);font-family:Conso= las,"Courier New",monospace;font-size:14px">=C3=A2\u0080\u009C</s= pan>.</div></div><div>with support from DBI community, the issue was solved= by calling decode from Encode module before writing to DB.</div><div>wonde= ring 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.</d= iv><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><di= v>Any feedback are greatly appreciated!</div><div>thank you</div><div>Shirl= ey</div><div><br></div><div><div style=3D"color:rgb(204,204,204);background= -color:rgb(31,31,31);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)">c= om</span><span style=3D"color:rgb(212,212,212)">></span> cat <span style= =3D"color:rgb(156,220,254)">testutf_decode</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,212,212)">/</span>bin<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"c= olor:rgb(156,220,254)">Encode</span> <span 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"col= or: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"colo= r:rgb(206,145,120)">"debugutf"</span>;</div><div><span style=3D"c= olor:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220,254)">$ho= st</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <span style=3D"= color:rgb(206,145,120)">"db"</span>;</div><div><span style=3D"col= or: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 style=3D"co= lor: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 style=3D"c= olor:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220,254)">$db= h</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <span style=3D"c= olor:rgb(156,220,254)">DBI</span>-><span style=3D"color:rgb(220,220,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)">$us= er</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(15= 6,220,254)">$sql</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <= span style=3D"color:rgb(206,145,120)">'INSERT INTO table1 (title) VALUE= S (?)'</span>;</div><div><span style=3D"color:rgb(156,220,254)">my</spa= n> <span style=3D"color:rgb(156,220,254)">$query</span> <span style=3D"colo= r:rgb(212,212,212)">=3D</span> <span style=3D"color:rgb(156,220,254)">$</sp= an><span style=3D"color:rgb(156,220,254)">dbh</span>-><span style=3D"col= or:rgb(220,220,170)">prepare</span>(<span style=3D"color:rgb(156,220,254)">= $sql</span>);</div><div><span style=3D"color:rgb(156,220,254)">my</span> <s= pan style=3D"color:rgb(156,220,254)">$bytes</span> <span style=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,220,254)">my</sp= an> <span style=3D"color:rgb(156,220,254)">$chars</span> <span style=3D"col= or:rgb(212,212,212)">=3D</span> <span style=3D"color:rgb(220,220,170)">deco= de</span>(<span style=3D"color:rgb(206,145,120)">'UTF-8'</span>, <s= pan 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,1= 20)">"$bytes contains "</span>.<span style=3D"color:rgb(220,220,1= 70)">length</span>($bytes).<span style=3D"color:rgb(206,145,120)">" ch= aracters</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:r= gb(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,120)">" = characters</span><span style=3D"color:rgb(215,186,125)">\n</span><span styl= e=3D"color:rgb(206,145,120)">"</span>;</div><div><span style=3D"color:= 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 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 both EL8 <span style=3D"color: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,2= 20,170)">execute</span>(@values); </div><br><div>############### running on= EL8</div><div><span style=3D"color:rgb(156,220,254)">xxx</span>.<span styl= e=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"color:rgb(181= ,206,168)">1.641</span></div><div>=E2=80=9C contains <span style=3D"color:r= gb(181,206,168)">3</span> characters</div><div>after decode =E2=80=9C conta= ins <span style=3D"color:rgb(181,206,168)">1</span> characters</div><br><di= v>[<span style=3D"color:rgb(156,220,254)">yyy</span>.<span style=3D"color:r= gb(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(21= 2,212,212)">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D></span>OK with decod= e, so decode fixed the issue</div><div>(<span style=3D"color:rgb(181,206,16= 8)">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(1= 56,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"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 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><d= iv>debugutf=3D# select * from table1;</div><div>=C2=A0 =C2=A0 =C2=A0<span s= tyle=3D"color:rgb(156,220,254)">title</span></div><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 without decode</div><div>=C2= =A0=E2=80=9C =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <span style=3D"color:rgb(21= 2,212,212)">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D></span>OK with decod= e</div><div>(<span style=3D"color:rgb(181,206,168)">2</span> rows)</div></d= iv></div></div></blockquote></div></blockquote><div><br></div><div><span></= span></div></div> </div></blockquote></div> --000000000000c63f700629a19f0c--