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!
I found a solution. I changed βdescriptionβ column charset to utf8mb4_unicode_ci. I passed error above but I get other issues.
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
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:
My issues solved!
4 Likes
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
kulak91
September 14, 2023, 7:59am
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β¦