CODEWITHSUNDEEP
mysqlmariadbmysql-workbenchutf8mb4
user8668708
user8668708

Reputation:

MariaDB incorrect string value error with utf8mb4 encoding

I changed database and table character set as utf8mb4 and collation utf8bm4_turkish_ci on MariaDB for emoji support. While i can easily insert and select operations by connecting to the server through the terminal but i am getting "Incorrect String Value" error while i want to perform insert operation via Client (web application or MySQL workbench). Also, when i want to display the data with select, it is corrupt. In the meantime, i noticed another situation; even if the database and tables do not support utf8mb4 (i only used utf8 for testing), i can insert emoji through the terminal and even view it with select.

Insert and Select operation through terminal

Insert and Select operation through MySQL Workbench

The definitions on the my.cnf file are as follows;

[mysqld]
datadir        = /var/lib/mysql
socket         = /var/lib/mysql/mysql.sock
user           = mysql
symbolic-links = 0
old_passwords  = 0

innodb_file_format        = Barracuda
default-storage-engine    = InnoDB
innodb_file_per_table     = 1
innodb_open_files         = 500
innodb_thread_concurrency = 24
lower_case_table_names    = 1
character-set-server      = utf8mb4
collation-server          = utf8mb4_turkish_ci
init-connect              = 'SET NAMES utf8mb4 COLLATE utf8mb4_turkish_ci'

skip-character-set-client-handshake
innodb_large_prefix

[client]
default-character-set = utf8mb4

[mysql]
skip-secure-auth
default-character-set = utf8mb4

Upvotes: 0

Views: 1840

Answers (1)

Rick James
Rick James

Reputation: 142298

Also, the column in the table needs to be CHARACTER SET utf8mb4. Please provide SHOW CREATE TABLE so we can check it.

Also, do SHOW VARIABLES LIKE 'char%'; from the failing client; it may be wrong there.

Upvotes: 2

Related Questions