0

I want to create a table from performance_schema.events_statements_history_long, but got an error.

sql:

create table history select * from performance_schema.events_statements_history_long

error:

Incorrect string value: '\xF0\x9F\x9F\xA8\xE8\xAD...' for column 'SQL_TEXT' at row 1108

character set

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Variable_name   Value
character_set_client    utf8
character_set_connection    utf8
character_set_database  utf8
character_set_filesystem    binary
character_set_results   utf8
character_set_server    utf8
character_set_system    utf8
collation_connection    utf8_general_ci
collation_database  utf8_general_ci
collation_server    utf8_general_ci

I have tried many times, but didn't work.

character set is utf8, i have also tried utf8mb4, and the result was the same.

Did anybody know what is the reason?

zheng bo
  • 13
  • 3
  • Try to use not `SELECT *` but specify separate columns. For `SQL_TEXT` column use `CONVERT(SQL_TEXT USING utf8mb4)`. – Akina Jun 29 '22 at 10:59
  • `create table history select CONVERT(sql_text USING utf8mb4) from performance_schema.events_statements_history_long `, got the same error – zheng bo Jun 29 '22 at 11:09
  • If so then the value itself does not match table's charset, and the problem is occured during the data retrieving, not during saving it to new table. Execute SELECT only then check warnings - you'd see this I think. – Akina Jun 29 '22 at 11:44
  • Look at your database default encoding – CangSheng Shen Jun 29 '22 at 10:37
  • i have re-edit the post and you can find the encoding. – zheng bo Jun 29 '22 at 10:51
  • the table `events_statements_history_long` is managed by mysql itself, and the chracter set is utf8. I select `sql_text` and `show warnings`. The result is : `Incorrect string value: '\xF0\x9F\x9F\xA5\xE7\xB4...' for column 'SQL_TEXT' at row 1768`. It shows some record has error format. but how can I fix this problem ? – zheng bo Jun 30 '22 at 02:13

1 Answers1

0

You need utf8mb4, not utf8. The clue is that 4-byte hex starting with F0.

F09F9FA8 ,      LARGE YELLOW SQUARE

Find where your connection parameters are; change utf8 to utf8mb4. If you can't find that, execute SET NAMES utf8mb4 right after connecting.

If latin1 is involved anywhere, you have other problems.

See also Trouble with UTF-8 characters; what I see is not what I stored

Rick James
  • 135,179
  • 13
  • 127
  • 222