maria-discuss team mailing list archive

[Alessandro Ren <dirty.ren@xxxxxxxxx>]

   Hey Dave,

I had some problems dealing with wrong coded UTF-8 chars.
You have to either deal with them before sending them to MariaDB, trying to
detect if the string is encoded correctly or you can change the field type
to blob, which will accept this string as is and not thrown a invalid
string error in MariaDB.

   []s.


On Wed, Jun 19, 2019 at 2:56 PM Dave Wreski <dwreski@xxxxxxxxxxxxxxxxxxx>
wrote:

> Hi,
>
> I'm trying to use perl-DBD to write a buffer of text that contains an
> email with umlauts and other non-ASCII characters to a joomla database
> and having a problem.
>
>      DBD::mysql::st execute failed: Incorrect string value:
> '\xD6sterl...' for column `lsv5webstage`.`xuxgc_content`.`fulltext` at
> row 1 at /home/alerts/scripts_linstage/AdvisoryTest.pm line 373.
>
> I'm not familiar enough with how encoding works to fully understand what
> the problem is. This is a fedora29 system with mariadb-10.3.12 and
> joomla-3.9.
>
> Apparently the '\xD6' is an O with an umlaut in "Sebastian �sterlund". I
> read something about utf8 not being able to handle 4-char, but I don't
> fully understand.
>
> I found the following reference online which talks about changing the
> encoding type from utf8 to utf8mb4, but the tables all appear to already
> be using that encoding:
>
>      > SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR
>      Variable_name LIKE 'collation%';
>      +--------------------------+--------------------+
>      | Variable_name            | Value              |
>      +--------------------------+--------------------+
>      | character_set_client     | utf8mb4            |
>      | character_set_connection | utf8mb4            |
>      | character_set_database   | utf8mb4            |
>      | character_set_filesystem | binary             |
>      | character_set_results    | utf8mb4            |
>      | character_set_server     | utf8mb4            |
>      | character_set_system     | utf8               |
>      | collation_connection     | utf8mb4_unicode_ci |
>      | collation_database       | utf8mb4_unicode_ci |
>      | collation_server         | utf8mb4_unicode_ci |
>      +--------------------------+--------------------+
>
> execute failed: Incorrect string value: '\xD6sterl...' with mariadb and
> perl DBD
> a novice perl programmer
> I'm not sure it's helpful, but this is the insert statement I'm using in
> my perl code:
>
>          my $sql                 = <<EOF;
>          INSERT INTO xuxgc_content (title, alias, introtext, `fulltext`,
> state, catid, created, created_by, created_by_alias, modified,
> modified_by, checked_out, checked_out_time, publish_up, publish_down,
> images, urls, attribs, version, ordering, metakey, metadesc, metadata,
> access, hits, language)
>          VALUES ($title, "$title_alias", $introText, $fullText, $state,
> $catid, $created, $created_by, $created_by_alias, $modified,
> $modified_by, $checked_out, $checked_out_time, $publish_up,
> $publish_down, $images, $urls, $attribs, $version, $ordering, $metakey,
> $metadesc, $metadata, $access, $hits, $language);
>          EOF
>
>          my $sth = $dbh->prepare($sql);
>          $sth->execute();
>          db_disconnect($dbh);
>
> I don't recall having this problem in the past, and this script has been
> in use for quite a while.
>
> What am I doing wrong?
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>