Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 查询报1300 - Invalid utf8mb4 character string: 'F0A18CB6’错误
|
username: zengchaojie
Executing the select statement reports error 1300 - Invalid utf8mb4 character string: ‘F0A18CB6’, but after setting set @@session.tidb_isolation_read_engines = “tiflash”, it executes normally.
Check the executed SQL, and also check the character set settings of the corresponding table. What does the normal display of TiFlash look like?
SELECT
count(*) cnt
FROM
db_ysb_dictionary.ts_drugstore_branch b
INNER JOIN ti_ysb_dictionary.ts_provider_drugstore a ON a.storetitle = b.full_name
INNER JOIN db_ysb_dictionary.ts_drugstore_provider c ON b.id = c.drugstore_branch_id
AND c.provider_id = a.provider_id
AND c.first_order_time > UNIX_TIMESTAMP() - 86400 * 3
Execution plan without TiFlash set
Execution plan with TiFlash set
It seems that some data in TiKV has an issue and does not comply with the utf8mb4 rule.
Several tables have their character sets defined as “DEFAULT CHARSET=utf8 COLLATE=utf8_bin”.
Your tables use the utf8 character set, but an error related to the utf8mb4 character set is reported… What are the default character sets for these two databases, db_ysb_dictionary and ti_ysb_dictionary?
If the character set of the database is utf8mb4, the character set of the table needs to be converted during the query. It should have triggered a bug related to the utf8mb4 character set in TiDB. You can create a database with the utf8 character set in the test environment, then export the table there and check again.
You can try upgrading. TiFlash uses a different character set or encoding method to process data, which might bypass this error.
It should have triggered a bug, I have encountered it before.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: #!/bin/bash
# Function to check the character encoding settings
check_character_encoding() {
echo "Checking character encoding settings..."
echo "Database Character Encoding:"
mysql -u <username> -p<password> -e "SHOW VARIABLES LIKE 'character_set%';"
echo "Client Application Character Encoding:"
locale charmap
}
# Function to check the table and column settings
check_table_column_settings() {
echo "Checking table and column settings..."
echo "Table and Column Character Set and Collation:"
mysql -u <username> -p<password> -e "SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '<database_name>';"
}
# Function to check the database configuration
check_database_configuration() {
echo "Checking database configuration..."
echo "Database Configuration:"
mysql -u <username> -p<password> -e "SHOW VARIABLES LIKE '%char%';"
}
# Main function
main() {
check_character_encoding
check_table_column_settings
check_database_configuration
}
# Run the main function
main
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: