A
A
apptester2017-08-14 01:09:39
MySQL
apptester, 2017-08-14 01:09:39

Mysql throws an error on non-UTF8 characters, how to fix?

On local is:

mysql  Ver 14.14 Distrib 8.0.0-dmr, for Linux (x86_64) using  EditLine wrapper
PHP 5.6.29
Yii2 2.0.12

Task: parsing several sites.
Problem: When writing text to a table, MySql gives:
Error: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xD1\x82\xD0\xBE \xD0...' for column 'content' at row 1

The only sensible answer I could find is this post https://mathiasbynens.be/notes/mysql-utf8mb4
After reading the article:
parameters (SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%')
*************************** 1. row ***************************
Variable_name: character_set_client
        Value: utf8mb4
*************************** 2. row ***************************
Variable_name: character_set_connection
        Value: utf8mb4
*************************** 3. row ***************************
Variable_name: character_set_database
        Value: utf8mb4
*************************** 4. row ***************************
Variable_name: character_set_filesystem
        Value: binary
*************************** 5. row ***************************
Variable_name: character_set_results
        Value: utf8mb4
*************************** 6. row ***************************
Variable_name: character_set_server
        Value: utf8mb4
*************************** 7. row ***************************
Variable_name: character_set_system
        Value: utf8
*************************** 8. row ***************************
Variable_name: collation_connection
        Value: utf8mb4_unicode_ci
*************************** 9. row ***************************
Variable_name: collation_database
        Value: utf8mb4_unicode_ci
*************************** 10. row ***************************
Variable_name: collation_server
        Value: utf8mb4_unicode_ci
10 rows in set (0.00 sec)

table (SHOW CREATE TABLE)
Table: post
Create Table: CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` int(11) NOT NULL,
  `updated_at` int(11) NOT NULL,
  `url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` smallint(6) DEFAULT '10',
  PRIMARY KEY (`id`),
  UNIQUE KEY `post_url_unq` (`url`),
) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The error has not gone away, so now the muscle also goes down. If I understand correctly, then these are the usual UTF8 characters, but the muscle is doing something weird, or I. How to live with it?
UPD 1:
Parameters (SHOW VARIABLES WHERE Variable_name LIKE '%char%'\)
*************************** 1. row ***************************
Variable_name: character_set_client
        Value: utf8mb4
*************************** 2. row ***************************
Variable_name: character_set_connection
        Value: utf8mb4
*************************** 3. row ***************************
Variable_name: character_set_database
        Value: utf8mb4
*************************** 4. row ***************************
Variable_name: character_set_filesystem
        Value: binary
*************************** 5. row ***************************
Variable_name: character_set_results
        Value: utf8mb4
*************************** 6. row ***************************
Variable_name: character_set_server
        Value: utf8mb4
*************************** 7. row ***************************
Variable_name: character_set_system
        Value: utf8
*************************** 8. row ***************************
Variable_name: character_sets_dir
        Value: /usr/share/mysql/charsets/

UPD 2:
I found a solution in declaring the column as LONGBLOB, the rest, although they seem to be reasonable methods, do not work :(
https://stackoverflow.com/a/15945126
Thanks to ThunderCat for the tip.

Answer the question

In order to leave comments, you need to log in

3 answer(s)
T
ThunderCat, 2017-08-14
@apptester

https://stackoverflow.com/questions/10957238/incor...
https://stackoverflow.com/questions/1168036/how-to...
UPD: what SHOW CREATE TABLE and SHOW VARIABLES
LIKE '%char%' says ?
UPD2: How to fix error when writing to mysql "Incorrect string value"?

F
freeLander, 2020-12-21
@freeLander

I also tried changing the table encoding to utf8mb4 and that didn't help either, but changing the encoding of the field itself helped:

ALTER TABLE `table` MODIFY `column` VARCHAR(255) CHARSET utf8mb4 null;

V
vdev85, 2022-02-23
@vdev85

collation-server=utf8mb4_general_ci

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question