Hello, Since Mojo::mysql sets mysql_enable_utf8, you should not use encode_json and decode_json since the transport will already be handling UTF-8 encoding. You should instead use to_json and from_json (as Mojo::Pg and Mojo::SQLite do, since their transports also handle UTF-8 encoding). If you use encode_json then the result is bytes, and you would need to bind it as a BLOB when passing the data to mysql so it does not get double encoded. This is further complicated that DBD::mysql 4.042 and the latest version have differing behavior in this regard (4.042 is the only version with correct behavior in all cases).
-Dan On Sun, Jan 14, 2018 at 11:48 AM, George <[email protected]> wrote: > Hi; > > I'm following Advent Calendar project from Joel (Thank You Joel !) and as > an exercise adapted it from Sqlite to Mysql. > It all went rather smoothly until I started to insert data into Mysq that > contains utf8 chars. > > Mysql default char sert is utf8. > > Mojo:;mysql does not have an expand method as Sqlite has, so I used > decode_json for the json part of the query result. > > So this works perfectly well using json_decode: > > my $bytes = encode_json {title => ['Ralph Towner / John Abercrombie - Five > Years Later', 'Grażyna Bacewicz, Diana Ambache - Chamber Music']}; > my $hash = decode_json($bytes); > > Once I Insert into mysql: > > insert into items (title,url,purchased, user_id) values('Ralph Towner / > John Abercrombie - Five Years Later','some url',0,1); > insert into items (title,url,purchased, user_id) values('Grażyna Bacewicz, > Diana Ambache - Chamber Music','some url',0,1); > > And run the following code: > > my $sql = <<' SQL'; > SELECT > user_id, > CONCAT('[', > GROUP_CONCAT(JSON_OBJECT('id', > items.id, > 'title', > items.title, > 'url', > items.url, > 'purchased', > items.purchased)), > ']') AS items > FROM > items > WHERE > items.user_id = '1' > SQL > > $hash = $mysql->db->query($sql)->hash; > $hash->{'items'} = decode_json($hash->{'items'} ); > > I get the following error: > > Input is not UTF-8 encoded at ./1.pl line 42. > > Same results for Mysql 5.7 and 8. > > I'm not sure if this a Mojo:JSON or Mojo:mysql bug, I'm also curious why > expand method is missing from Mojo::sql as it is present in Mojo:pg and > Mojo::sqlite. > > Thanks; > > George > > > -- > You received this message because you are subscribed to the Google Groups > "Mojolicious" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/mojolicious. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Mojolicious" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/mojolicious. For more options, visit https://groups.google.com/d/optout.
