How to fix “Incorrect string value” errors?

by

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;)

More Related Contents:

  1. how to generate report for attendance system
  2. How can I return pivot table output in MySQL?
  3. MySQL pivot table query with dynamic columns
  4. ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
  5. Finding duplicate values in MySQL
  6. Error installing mysql2: Failed to build gem native extension
  7. How to debug Lock wait timeout exceeded on MySQL?
  8. MySQL – Selecting a Column not in Group By
  9. MySQL : isn’t in GROUP BY
  10. Why does MySQL report a syntax error on FULL OUTER JOIN?
  11. SQL: Creating a Relational table with 2 different auto_increment
  12. CURRENT_DATE/CURDATE() not working as default DATE value
  13. How to extract the nth word and count word occurrences in a MySQL string?
  14. How do I add a custom CHECK constraint on a MySQL table?
  15. MySql Count cannot show 0 values
  16. How to store uuid as number?
  17. Cannot change column used in a foreign key constraint
  18. How does one create an index on the date part of DATETIME field in MySql
  19. How to delete a MySQL record after a certain time
  20. using (-) dash in mysql table name
  21. Force InnoDB to recheck foreign keys on a table/tables?
  22. Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT
  23. How can I enable MySQL’s slow query log without restarting MySQL?
  24. Does MySQL allows to create database with dot?
  25. Second SELECT query if first SELECT returns 0 rows
  26. Selecting multiple columns/fields in MySQL subquery
  27. Array data type, split string,
  28. Trouble with GROUP_CONCAT and Longtext in MySQL
  29. How to pass a variable to a IN clause?
  30. How can I make a table in MySQL called “order”?

Leave a Comment