Re: any known DBD::pg change caused this utf8 issue?
Grant McLean <[email protected]> Thu, 19 Dec 2024 15:00:42 +1300
| Newsgroups | gmane.comp.db.postgresql.dbdpg |
|---|---|
| Message-ID | <[email protected]> |
--=-nrNDgoCac0AVPskdAymK Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello Shirley This is a complex topic which includes "encodings" and the way in which Perl is able to deal with binary data=C2=A0 (e.g.: a string of bytes) vs character data (in which each character might be represented by one or more bytes). There is no "quick fix". You really need to understand when you're dealing 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: =C2=A0 =C2=A0=C2=A0https://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 where 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. =C2=A0The 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: =C2=A0 =C2=A0=C2=A0https://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. >=20 > On Wed, Dec 18, 2024 at 11:20=E2=80=AFAM Shaomei Liu > <[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=C2=A0 =C2=A0=E2=80=9C=C2=A0 is = displayed > > as=C2=A0=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. > >=20 > > 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. > >=20 > > here is the program and execution results. > > Any feedback are greatly appreciated! > > thank you > > Shirley > >=20 > > xxx.com> cat testutf_decode.pl > > #!/usr/bin/perl > > use strict; > > use warnings; > > use DBI; > > use Encode 'decode'; > > print "DBI version: $DBI::VERSION\n"; > >=20 > > 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, Databa= se shows =E2=80=9C > > on EL7 but =C3=A2\u0080\u009C on EL8 > > my @values =3D ($chars); =C2=A0#=3D=3D=3D=3D=3D=3D>with decode, Databas= e shows =E2=80=9C on > > both EL8 and EL7, decode fixed the issue > > $query->execute(@values);=20 > >=20 > > ############### 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 > >=20 > > [yyy.com]$ psql -Upostgres -hdb debugutf > > psql (16.6) > > debugutf=3D# select * from table1; > > =C2=A0 =C2=A0 =C2=A0title > > --------------- > > =C2=A0=C3=A2\u0080\u009C =C2=A0=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>NOK witho= ut decode > > =C2=A0=E2=80=9C =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>OK with decode, so decode fixed the > > issue > > (2 rows) > >=20 > > ############### 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 > >=20 > > [yyy.com]$ psql -Upostgres -hdb debugutf > > psql (16.6) > > debugutf=3D# select * from table1; > > =C2=A0 =C2=A0 =C2=A0title > > --------------- > > =C2=A0=E2=80=9C =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D>OK without decode > > =C2=A0=E2=80=9C =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D>OK with decode > > (2 rows) --=-nrNDgoCac0AVPskdAymK Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable <html><head><style>pre,code,address { margin: 0px; } h1,h2,h3,h4,h5,h6 { margin-top: 0.2em; margin-bottom: 0.2em; } ol,ul { margin-top: 0em; margin-bottom: 0em; } blockquote { margin-top: 0em; margin-bottom: 0em; } </style></head><body><div>Hello Shirley</div><div><br></div><div>This is a = complex topic which includes "encodings" and the way in which Perl is able = to deal with binary data </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 un= derstand when you're dealing with bytes vs characters. As a</div><div>rule = of thumb you'll want to "decode" data that is coming 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, here's a link to a video I made on the subje= ct:<br><br> <a href=3D"https://www.youtube.com/watch?v=3D= cgswnneFp-s">https://www.youtube.com/watch?v=3DcgswnneFp-s</a></div><div><b= r></div><div>There are a number of reasons why the behaviour of your code m= ight have changed following the upgrade.</div><div>- The newer versions of = libraries and utilities might have different defaults for handling bytes vs= character data.</div><div>- The "locale" setting in the upgraded system mi= ght be different (e.g.: LANG=3D"C" vs LANG=3D"en_US.UTF-8").</div><div>- Th= e environment in which the code executes might be different.</div><div><br>= </div><div>Well-written code that is explicit about handling character data= and where the encoding/decoding should happen</div><div>would be resistant= to those types of outside influences.</div><div><br></div><div>In the vide= o I walk through a scenario where some code which appeared to be working co= rrectly but then one small</div><div>change broke things in different ways.= The fixes are to add in explicit handling of encoding.</div><div><br= ></div><div>However this is not really an issue that is specific to DBI or = DBD::Pg - apart from being explicit about your use of the</div><div>"pg_ena= ble_utf8" attribute on your database handle:</div><div><br></div><div> = ; <a href=3D"https://metacpan.org/pod/DBD::Pg#pg_enable_utf8-(i= nteger)">https://metacpan.org/pod/DBD::Pg#pg_enable_utf8-(integer)</a></div= ><div><br></div><div>I hope that sets you on the right path.</div><div><br>= </div><div>Regards</div><div>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:0 0 0 .8ex; border-left:2px #729fcf solid;padding-left:1e= x"><div dir=3D"ltr">send again after subscribing.</div><div><br></div><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:[email protected]">[email protected]</a>> wrote:<br= ></div><blockquote type=3D"cite" style=3D"margin:0 0 0 .8ex; border-left:2p= x #729fcf solid;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 =E2=80=9C is displayed as <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</s= pan> <span style=3D"color:rgb(206,145,120)">"DBI version: $DBI::VERSION</sp= an><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 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 style=3D"color:rgb(156,220,254)">my</= span> <span style=3D"color:rgb(156,220,254)">$host</span> <span style=3D"co= lor:rgb(212,212,212)">=3D</span> <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(156,220,254)">$user</span> <span style=3D"color:rgb(212,= 212,212)">=3D</span> <span style=3D"color:rgb(206,145,120)">"postgres"</spa= n>;</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,2= 12)">=3D</span> <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(= 156,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)">"D= BI:Pg:dbname=3D$db;host=3D$host"</span>,<span style=3D"color:rgb(156,220,25= 4)">$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</= span> <span style=3D"color:rgb(206,145,120)">'INSERT INTO table1 (title) VA= LUES (?)'</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)">$</spa= n><span style=3D"color:rgb(156,220,254)">dbh</span>-><span style=3D"colo= r: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> <sp= an 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</span> <span s= tyle=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,22= 0,254)">print</span> <span style=3D"color:rgb(206,145,120)">"$bytes contain= s "</span>.<span style=3D"color:rgb(220,220,170)">length</span>($bytes).<sp= an style=3D"color: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)">print</span> <span style= =3D"color:rgb(206,145,120)">"after decode $bytes contains "</span>.<span st= yle=3D"color:rgb(220,220,170)">length</span>($chars).<span style=3D"color:r= gb(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 s= tyle=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 o= n 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</spa= n> ($chars); #<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><br><div>############= ### running on EL8</div><div><span style=3D"color:rgb(156,220,254)">xxx</sp= an>.<span style=3D"color:rgb(156,220,254)">com</span><span style=3D"color:r= gb(212,212,212)">></span> .<span style=3D"color:rgb(212,212,212)">/</spa= n><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><br><div>[<span style=3D"color:rgb(156,220,254)">yyy</span>.<span s= tyle=3D"color:rgb(156,220,254)">com</span>]$ psql -Upostgres -hdb debugutf<= /div><div>psql (16.6)</div><div>debugutf=3D# select * from table1;</div><di= v> <span style=3D"color:rgb(156,220,254)">title</span></= div><div><span style=3D"color:rgb(212,212,212)">---------------</span></div= ><div> =C3=A2\u0080\u009C <span style=3D"color:rgb(212,212,212)"= >=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D></span>NOK without decode</div><div>&nbs= p;=E2=80=9C <span style=3D"= color:rgb(212,212,212)">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D></span>O= K with decode, so decode fixed the issue</div><div>(<span style=3D"color:rg= b(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)">&= gt;</span> .<span style=3D"color:rgb(212,212,212)">/</span><span style=3D"c= olor:rgb(156,220,254)">testutf_decode</span>.<span style=3D"color:rgb(156,2= 20,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(18= 1,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>[<s= pan style=3D"color:rgb(156,220,254)">yyy</span>.<span style=3D"color:rgb(15= 6,220,254)">com</span>]$ psql -Upostgres -hdb debugutf</div><div>psql (16.6= )</div><div>debugutf=3D# select * from table1;</div><div> &nbs= p;<span style=3D"color:rgb(156,220,254)">title</span></div><div><span style= =3D"color:rgb(212,212,212)">---------------</span></div><div> =E2=80= =9C <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> =E2=80=9C <span style=3D"col= or:rgb(212,212,212)">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D></span>OK w= ith decode</div><div>(<span style=3D"color:rgb(181,206,168)">2</span> rows)= </div></div></div></div></blockquote></div></blockquote><div><br></div><div= ><span></span></div></body></html> --=-nrNDgoCac0AVPskdAymK--