MySQL setting for correct character and collation in Laravel

2
votes

I am using external DB(read only) with local DB(test system).

So everytime I getting as below exception warning with default null charater like "�" when getting data from external DB.

How should I modify DB setting in my situation? Thanks.

(3/3) QueryException

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF8peri ...' for column 'customer_name' at row 1 (SQL: insert into customers (id, customer_name, customer_email) values (10008, Ol**n N***n K***t�pe AS, n***n@k***t.no))

External DB

character_set_client = utf8

character_set_connection = latin1

character_set_database = utf8

character_set_filesystem = binary

character_set_results = latin1

character_set_server = utf8

character_set_system = utf8

collation_connection = utf8_general_ci

collation_database = latin1_swedish_ci

collation_server = latin1_swedish_ci

Server Mysql version 5.1.

Internal DB

character_set_client = utf8mb4

character_set_connection = utf8mb4

character_set_database = utf8mb4

character_set_filesystem = binary

character_set_results = utf8mb4

character_set_server = latin1

character_set_system = utf8

collation_connection = utf8mb4_general_ci

collation_database = utf8mb4_unicode_ci

collation_server = latin1_swedish_ci

SErver MySQL version 5.7.19

Test System Enviroments: Win10, WAMPSERVER(MySQL v5.7.19, PHP v7.1.9)

1
mysqldatabaselaravel-5character-encodingcollation
Dup of stackoverflow.com/questions/46640891/… – Rick James

1 Answers

1
votes

\xF8 is a kind of unicode, it doesn't work with latin and latin_swedish character set. You have to update your character set to UTF8 or UTF8mb4 and collation to utf8_general_ci or utf8mb4_general_ci