MySQL Forums
Forum List  »  Newbie

Update sql terminated with an incorrect string value error in MySQL 8.0.21
Posted by: zhu guan
Date: October 13, 2022 09:07AM

Hi,

I have had a Website working for 7 years and have had no problems
until a few weeks ago I replace my MySQL server with a newer one.

MySQL version (old): MySQL 5.1.73
MySQL version (new): MySQL 8.0.21

I run following sql in MySQL 5.1.73 and MySQL 8.0.21,
MySQL 5.1.73 can run normally, but MySQL 8.0.21 is terminated with an error below:

ERROR:SQL : #HY000Incorrect string value: '\xF0\xA3\x98\xBA' for column

========================SQL=====================

UPDATE profile SET sei = '𣘺' WHERE uid = '100000'

========================OLD=====================

mysql> select version();
+------------+
| version() |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'chara%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | ujis |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql>show create table profile;
| profile | CREATE TABLE `profile` (
`sei` text
) ENGINE=InnoDB AUTO_INCREMENT=749298 DEFAULT CHARSET=utf8 |

========================NEW=====================
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21 |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'chara%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql>show create table profile;
| profile | CREATE TABLE `profile` (
`sei` text
) ENGINE=InnoDB AUTO_INCREMENT=749298 DEFAULT CHARSET=utf8|


What is the reason for this and is there any easy way to maintain compatibility?

Regards,

--

gzh

Options: ReplyQuote


Subject
Written By
Posted
Update sql terminated with an incorrect string value error in MySQL 8.0.21
October 13, 2022 09:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.