Mysql 1366 ошибка

UPDATE to the below answer:

The time the question was asked, «UTF8» in MySQL meant utf8mb3. In the meantime, utf8mb4 was added, but to my knowledge MySQLs «UTF8» was not switched to mean utf8mb4.

That means, you’d need to specifically put «utf8mb4», if you mean it (and you should use utf8mb4)

I’ll keep this here instead of just editing the answer, to make clear there is still a difference when saying «UTF8»

Original

I would not suggest Richies answer, because you are screwing up the data inside the database. You would not fix your problem but try to «hide» it and not being able to perform essential database operations with the crapped data.

If you encounter this error either the data you are sending is not UTF-8 encoded, or your connection is not UTF-8. First, verify, that the data source (a file, …) really is UTF-8.

Then, check your database connection, you should do this after connecting:

SET NAMES 'utf8mb4';
SET CHARACTER SET utf8mb4;

Next, verify that the tables where the data is stored have the utf8mb4 character set:

SELECT
  `tables`.`TABLE_NAME`,
  `collations`.`character_set_name`
FROM
  `information_schema`.`TABLES` AS `tables`,
  `information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY` AS `collations`
WHERE
  `tables`.`table_schema` = DATABASE()
  AND `collations`.`collation_name` = `tables`.`table_collation`
;

Last, check your database settings:

mysql> show variables like '%colla%';
mysql> show variables like '%charac%';

If source, transport and destination are utf8mb4, your problem is gone;)

I have been looking everywhere for an answer, but I still have not found a solution.
Background:
I have a MySQL Server 5.5 running on my computer for testing data that will be eventually loaded into a Production Server for my company. I created the database in MySQL Workbench and have edited it where appropriate to fit my data as I loaded it. My data is nowhere near clean, so for the most part I am using VARCHAR for my fields, since a whole bunch of random things can be put into the field. I have tried changing multiple things and I am no longer sure whether anything I have done has fixed or even helped to eliminate the issue at all.

My Code:
DB Creation:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

SET NAMES utf8;

DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Customer_Account`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Customer_Account` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Customer_Account` (
  `idCustomer_Account` VARCHAR(20) NOT NULL,
  `Name` VARCHAR(130) NOT NULL,
  `Billing_Street` VARCHAR(150) NULL,
  `Billing_City` VARCHAR(30) NULL,
  `Billing_StateProvince` VARCHAR(25) NULL,
  `Billing_PostalCode` VARCHAR(15) NULL,
  `Billing_Country` VARCHAR(20) NULL,
  `Location_Type` VARCHAR(15) NULL,
  `Parent_ID` VARCHAR(20) NULL,
  `Parent_Name` VARCHAR(130) NULL,
  PRIMARY KEY (`idCustomer_Account`),
  UNIQUE INDEX `idCustomer_Account_UNIQUE` (`idCustomer_Account` ASC))
ENGINE = InnoDB DEFAULT CHARSET=utf8;

  'CONSTRAINT `Parent_ID`
    FOREIGN KEY (`idCustomer_Account`)
    REFERENCES `mydb`.`Customer_Account` (`idCustomer_Account`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)'

-- -----------------------------------------------------
-- Table `mydb`.`Customer_Address`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Customer_Address` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Customer_Address` (
  `idCustomer_Address` VARCHAR(20) NOT NULL,
  `Name` VARCHAR(50) NOT NULL,
  `Name_Int` INT,
  `Address` VARCHAR(80) NULL,
  `City` VARCHAR(30) NULL,
  `State` VARCHAR(5) NULL,
  `ZIP` VARCHAR(15) NULL,
  `LinkedAccount_Name` VARCHAR(100) NULL,
  `LinkedAccount_ID` VARCHAR(20) NULL,
  `CADD` INT,
  PRIMARY KEY (`idCustomer_Address`),
  UNIQUE INDEX `idCustomer_Address_UNIQUE` (`idCustomer_Address` ASC))
ENGINE = InnoDB DEFAULT CHARSET=utf8;

-- -----------------------------------------------------
-- Table `mydb`.`Contacts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Contacts` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Contacts` (
  `idContacts` VARCHAR(20) NOT NULL,
  `Name` VARCHAR(50) NOT NULL,
  `Title` VARCHAR(130) NULL,
  `Mailing_Street` VARCHAR(110) NULL,
  `Mailing_City` VARCHAR(30) NULL,
  `Mailing_State` VARCHAR(25) NULL,
  `Mailing_PostalCode` VARCHAR(20) NULL,
  `Phone` VARCHAR(50) NULL,
  `Account_ID` VARCHAR(20) NULL,
  `Account_Name` VARCHAR(100) NULL,
  PRIMARY KEY (`idContacts`),
  UNIQUE INDEX `idContacts_UNIQUE` (`idContacts` ASC))
ENGINE = InnoDB DEFAULT CHARSET=utf8;


-- -----------------------------------------------------
-- Table `mydb`.`Contact_Details`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Contact_Details` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Contact_Details` (
  `idContact_Details` VARCHAR(20) NOT NULL,
  `Salutation` VARCHAR(70) NULL,
  `First_Name` VARCHAR(30) NULL,
  `Last_Name` VARCHAR(50) NULL,
  `Title` VARCHAR(130) NULL,
  `Mailing_Street` VARCHAR(175) NULL,
  `Mailing_City` VARCHAR(50) NULL,
  `Mailing_StateProvince` VARCHAR(30) NULL,
  `Mailing_PostalCode` VARCHAR(20) NULL,
  `Mailing_Country` VARCHAR(25) NULL,
  `Phone` VARCHAR(50) NULL,
  `Mobile` VARCHAR(35) NULL,
  `Fax` VARCHAR(45) NULL,
  `Email` VARCHAR(75) NULL,
  `Email_OptOut` VARCHAR(1) NULL,
  `Account_Owner` VARCHAR(20) NULL,
  `Account_Name` VARCHAR(125) NULL,
  `Account_ID` VARCHAR(20) NOT NULL,
  `Active` VARCHAR(2) NOT NULL,
  PRIMARY KEY (`idContact_Details`),
  UNIQUE INDEX `idContact_Details_UNIQUE` (`idContact_Details` ASC))
ENGINE = InnoDB DEFAULT CHARSET=utf8;

'INDEX `Account_ID_idx` (`Account_ID` ASC)'

-- -----------------------------------------------------
-- Table `mydb`.`WF1_SR_Accounts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`WF1_SR_Accounts` ;

CREATE TABLE IF NOT EXISTS `mydb`.`WF1_SR_Accounts` (
  `idWF1_SR_Accounts` VARCHAR(20) NOT NULL,
  `Name` VARCHAR(125) NOT NULL,
  `Billing_Street` VARCHAR(135) NULL,
  `Billing_City` VARCHAR(50) NULL,
  `Billing_State` VARCHAR(20) NULL,
  `Billing_PostalCode` VARCHAR(20) NULL,
  `Billing_Country` VARCHAR(25) NULL,
  `Location_Type` VARCHAR(20) NULL,
  `Parent_ID` VARCHAR(20) NULL,
  `Parent_Name` VARCHAR(125) NULL,
  `SR_Account?` VARCHAR(5) NULL,
  `WF1_Account?` VARCHAR(5) NULL,
  `WF1_AccountNum` VARCHAR(10) NULL,
  `WF1_AccountStatus` VARCHAR(25) NULL,
  `WF1_AccountCreated` VARCHAR(50) NULL,
  `WF1_BU` VARCHAR(10) NULL,
  `WF1_Collector` VARCHAR(20) NULL,
  `WF1_CreditHold` VARCHAR(5) NULL,
  `WF1_CreditLimit` VARCHAR(10) NULL,
  `WF1_CreditRating` VARCHAR(15) NULL,
  `WF1_DVP` VARCHAR(20) NULL,
  `WF1_PaymentTerms` VARCHAR(20) NULL,
  `WF1_RSD` VARCHAR(40) NULL,
  `WF1_TerritoryName` VARCHAR(45) NULL,
  `WF1_TerritoryNumber` VARCHAR(5) NULL,
  `Select_` VARCHAR(5) NULL,
  `New_Select` VARCHAR(5) NULL,
  `CRMFusion_Use` VARCHAR(10) NULL,
  `CRMFusion_Change` VARCHAR(10) NULL,
  PRIMARY KEY (`idWF1_SR_Accounts`),
  UNIQUE INDEX `idWF1_SR_Accounts_UNIQUE` (`idWF1_SR_Accounts` ASC))
ENGINE = InnoDB DEFAULT CHARSET=utf8;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Note: I have also substituted latin1 for utf8 in SET NAMES, DEFUALT CHARSET, and CHARACTER_SET_SERVER, and tried to make my .txt files of both encryption types.

I also ran

SHOW VARIABLES like 'char%';

and this is my result:

Variable_name                   Value
character_set_client            latin1
character_set_connection            latin1
character_set_database          utf8
character_set_filesystem            binary
character_set_results           latin1
character_set_server            latin1
character_set_system            utf8
character_sets_dir            C:Program FilesMySQLMySQL Server 5.5sharecharsets

Note: at one point, everything but filesystem and server were utf8 and it did not work either.

I have also changed the encryption type in excel and notepad++ and changing that did not seem to help.

На локалке стоит:

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

Задача: парсинг нескольких сайтов.
Проблема: при записи текста в таблицу MySql выдает:

Error: SQLSTATE[HY000]: General error: 1366 Incorrect string value: 'xD1x82xD0xBE xD0...' for column 'content' at row 1

Единственный толковый ответ, который удалось найти это пост https://mathiasbynens.be/notes/mysql-utf8mb4
После прочтения статьи:

параметры (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)

таблица (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

Ошибка никуда не делась, так теперь еще и мускул уходит в даун. Если я правильно понимаю то это и есть обычные UTF8 символы, но мускул что-то чудит, или я. Как с этим жить?

UPD 1:
параметры (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:
Нашел решение в объявлении колонки как LONGBLOB, остальные, хотя вроде и разумные методы, не работают
https://stackoverflow.com/a/15945126
Спасибо ThunderCat за наводку.