:An error is reported when the database returns "Incorrect string value"

Issue

An error similar to the following is reported when you execute SQL statements on an apsaradb RDS for MySQL instance or a user-created MySQL database (a user-created database hosted on an ECS instance or a user-created database in an on-premises data center in the DMS console.

Incorrect string value

Cause

The data character set and the character set of the table are incompatible when data is written or queried.

Solutions

SQL statements that are generally copied and may have hidden characters, you can use a third-party SQL formatting tool to make sure that no hidden characters exist in the copied SQL statements. Then, select the following steps based on the on-site conditions:

• Change data
  If the preceding error is reported when you execute a data change statement, check whether the involved data change statement is abnormal.
• Query access frequency statistics
  1. Log on to the DMS console.
  2. top nav bar, select the SQLConsole > single library query and select the operation of the database, and then click confirm.
  3. in the navigation pane on the left locate the row that contains the target database click, right-click the destination table in the displayed list, select table details.
     
     
  4. Check whether the data character set and the table character set are compatible during data query.
     
  5. enter the SQLConsole page, select display settings and select the same character set as the table.
     

Scope

• Data Management (DMS)
• Apsaradb RDS for MySQL
  

  note: the issues in this article also apply to user-created MySQL databases, which are user-created databases on ECS instances and user-created databases in on-premises data centers.