0

Good Morning!

I've been trying to implement emojis in my applications for a while, but I'm having a lot of difficulties. I have already tested several internet solutions, but none has been effective.

I will try to detail as much as I am doing:

  • I'm using Classic ASP and MySQL in versions 5.6.40-84.0-log and 5.6.26-log.
  • The application is hosted on a Plesk Windows (I tried to run locally and the same goes for the database). Notepad ++ pages have already been tested in UTF-8 and UTF8 without BOM.
  • In the HTML <head> there is <meta charset = "utf-8">.
  • The <form> has the tag accept-charset="UTF-8".
  • The ASP has Response.AddHeader "Content-Type", "text/html;charset=UTF-8", Response.CodePage=65001,Response.LCID=1060 and Response.Charset="utf-8".

Now for the problems:

In both versions of MySQL, when I change COLLATE to utf8mb4_unicode_* or utf8mb4_bin, it returns to utf8mb4_0900_ai_ci automatically.

In version 5.6.40-84.0-log, if I register the emojis directly in the database, they are like ??????????????.

In version 5.6.26-log, if I register directly with the database, the error returns:

Executing: INSERT INTO `db`.`table` (` emoji`) VALUES ('tion 123 ão'); Operation failed: There was an error while applying the SQL script to the database. ERROR 1366: 1366: Incorrect string value: '\ xF0 \ x9F \ x98 \ x80 \ xF0 \ x9F ...' for column 'emoji' at row 1 SQL Statement: INSERT INTO 'db'. 'Table' ('emoji') VALUES ('tion 123 ão')

In this same version, registering through the of the page, using SET NAMES 'utf8mb4' or SET NAMES' utf8' in the ODBC connection string, the following error is returned:

Microsoft OLE DB Provider for ODBC Drivers error '80004005' [MySQL] [ODBC 3.51 Driver] [mysqld-5.6.22] Incorrect string value: '\ xE7 \ xE3o' for column 'field' at row 1

EDIT 1: When I consult SHOW SESSION VARIABLES LIKE 'character_set%'; and SHOW SESSION VARIABLES LIKE "%collation%"; the database it returns the following results:

enter image description here

Thanks!

Translation by Google Translate (rsrs)

Savio
  • 23
  • 6
  • See "question mark" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James May 07 '20 at 03:53

1 Answers1

0

For Emoji and some Chinese, you need utf8mb4, not utf8 in MySQL.

Since you are running the old 5.6, you may stumble over another problem. http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

If that E7E3 should have been çã, then you have some latin1-encoded text, too.

Do not mix encodings in a single column.

Your output for SHOW SESSION VARIABLES LIKE 'char%' shows that the connection is just utf8; it needs to be utf8mb4. The main difference between them is in Emoji.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • This is strange, because the error is also shown when I insert it directly into the bank, but only when there is an emoji with it. And the database is in UTF8MB4 – Savio May 07 '20 at 12:11
  • The database does not matter; what matters is the column. – Rick James May 07 '20 at 15:21
  • Change the connection parameters. Use `SET NAMES utf8mb4` if you can't find what is going wrong in the parameters. – Rick James May 07 '20 at 15:23
  • As mentioned at the beginning, if you do this, you get the error: `[MySQL][ODBC 3.51 Driver][mysqld-5.6.40-log]Incorrect string value: '\xE7\xE3o' for column...` – Savio May 08 '20 at 20:05