1. 

   duane, Member

   Hi. I have editable fields which are text. Sometimes an emoji/unicode icon is included in the text. When this happens the update failes.

   The database is mariadb with utf8mb4 set - so it should work in theory. Any suggestions for getting it to accept unicode characters from a text field update?

   (it doesn't work in the demo reports either - see https://mydbr.com/demo/mydbr/report.php?r=77&m=1&h=25541008f09ce098a477425247b8c6ef47ff7377&i=1 where I tried to add: 💔)
2. 

   myDBR Team, Key Master

   Duane,
   Most likely your connection is using utf8 or your database character set is utf8 (i.e. it can store 3-byte characters) or your default character set for the database is utf8 (procedures use the db character set for string/text parameters if not specifically set).

   First, check the connection myDBR uses. See if the mydbr_config.php has a row:

   define( "DB_CHARSET", 'utf8mb4' );

   Then check the default database for the database:

   show variables like "character_set_database";

   You can change the character set via ALTER DATABASE-command.

   Finally, if your setup uses uf8, you can specify the character set for the parameter via the 'character set' definition. Sample procedure parameter would be:

   inParameter varchar(100) character set utf8mb4

   And for the demo, myDBR demo database is old enough that it was created before MySQL 5.5.3 which introduced the utf8mb4. The demo db uses still utf8. We'll update the character set for the demo (we've updated the report in question)

   --
   myDBR Team
3. 

   duane, Member

   I finally got back to this so am reporting what I did to resolve it:
   

   1. My mydbr_config.php was missing define( "DB_CHARSET", 'utf8mb4' ); so I added it.
   2. I was using REGEXP_REPLACE (mariadb) to clean the input text. But REGEXP_REPLACE replaces unicode with '?' so even with the fix above, it was not returning the text with the unicode in it
   3. So I ended up adding code to detect unicode in the input text and skip the cleanup process if it contained it
   
   

   Thanks for your help as usual solving this. Now hopefully mariadb fixes REGEXP_REPLACE!
4. 

   myDBR Team, Key Master

   You can follow the progress for this at MariaDB's JIRA.

   --
   myDBR Team

Reply

You must log in to post.