Error: Incorrect string value: '\xF0\x9D\x95\x92\xF0\x9D...'

Questions and Answers Database-SQL
1
System Information
  • Strapi Version: 4.1.8
  • Operating System: Ubuntu
  • Database: MySQL 8.0
  • Node Version: 14.18.2
  • NPM Version: 6.14.15
  • Yarn Version:

Hi all!
I have a problem when insert a new data to strapi collection. I thinks it come from a string have unicode charactor. Here is my error log:

error: insert into `agencies` (`alias`, `created_at`, `description`, `email`, `logo`, `name`, `phone`, `published_at`, `review_count`, `review_score`, `source`, `updated_at`,  ('2greenmonkeys.co.uk', '2022-04-25 09:28:14.723', 'Beautiful personalised gifts, that your giftee will treasure.\n\nπ•’π•Ÿπ••π•žπ•’π••π•– π•¨π•šπ•₯𝕙  𝕛𝕦𝕀π•₯ 𝕗𝕠𝕣 π•ͺ𝕠𝕦!\n\nChildren\'s name bunting | Mem                     o and cushions | Anniversary cushions | Quilts | Organic baby blankets', 'hello@2greenmonkeys.co.uk', 'https://consumersiteimages.trustpilot.net/business-units/623c5d8992d2923242cb5885-198x149-1x.jpg', , '01271 864860', '2022-04-25 09:28:14.723', 1, 3.7, 'TP', '2022-04-25 09:28:14.723', 'http://2greenmonkeys.co.uk') - ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\x9D\x95\x92\xF0\x 'description' at row 1
Error: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\x9D\x95\x92\xF0\x9D...' for column 'description' at row 1

How can I fix this issues? Thanks!

2

I found a solution. I changed β€˜description’ column charset to utf8mb4_unicode_ci. I passed error above but I get other issues.

image
image1023Γ—604 49.6 KB 

error: insert into `agencies` (`alias`, `created_at`, `description`, `email`, `logo`, `name`, `phone`, `published_at`, `review_count`, `review_score`, `source`, `updated_at`, `website`) values ('4btcgainer.com', '2022-04-25 10:14:21.542', '', '', 'https://businessunitprofile-cdn.trustpilot.net/businessunitprofile-consumersite/public/default-business-unit-image-118x89.svg', '4btcgainer Bitcoin Investment πŸ‡ΊοΏ½ 
'', '', '2022-04-25 10:14:21.542', 3, 3.5, 'TP', '2022-04-25 10:14:21.542', 'http://4btcgainer.com') - ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\x9F\x87\xBA\xF0\x9F...' for column 'name' at row 
1
Error: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\x9F\x87\xBA\xF0\x9F...' for column 'name' at row 1
3

I add 2 lines for database connect:

charset   : 'utf8mb4',
      collation : 'utf8mb4_unicode_ci'

on file config/database.js. Here is full file after changed:

module.exports = ({ env }) => ({
  connection: {
    client: 'mysql',
    connection: {
      host: env('DATABASE_HOST', '127.0.0.1'),
      port: env.int('DATABASE_PORT', 3306),
      database: env('DATABASE_NAME', 'your-db-name'),
      user: env('DATABASE_USERNAME', 'root'),
      password: env('DATABASE_PASSWORD', ''),
      ssl: env.bool('DATABASE_SSL', false),
      charset   : 'utf8mb4',
      collation : 'utf8mb4_unicode_ci'
    },
  },
});

and alter your dabase table by MySQL command:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

after that you can check it by command:

SHOW FULL COLUMNS FROM table_name;

Now you can see database table same my screenshot:

image
image1179Γ—445 61.3 KB

My issues solved!

3 Likes
4

Database config did the job. I was focused on the MySQL side of the problem. Everything seemed ok. I never thought about strapi side.
Thank you @thanhbinh87

5

It’s interesting that when you manually alter existing db tables it makes collation utf8mb4_unicode_ci, but when you add charset and collation to the config/database.js fresh mysql will be created with utf8mb4_general_ci fields…