chrisan
1 month ago
217
1
General

General error: 1366 Incorrect string value: '\xE2?

Posted 1 month ago by chrisan

I am parsing a text file which is encoded in UTF-8 and there is a string with the value of Cluse La Boh?⿿me Grey Leather 38mm When I view it in Notepad++ it looks like Cluse La Boh?xE2?xBFme Grey Leather 38mm

First of all, clearly something is wrong and I'll work with the client to fix the feed, however I'd rather it still be something I can save via Eloquent

I have verified mysql8 has the characterset set to utf8mb4 with

SELECT * FROM information_schema.SCHEMATA 
WHERE schema_name = "testapp";

SELECT CCSA.* FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation  
  AND T.table_name = "products";
  
SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.`COLUMNS` 
WHERE table_name = "products"
  AND column_name = "name";

Also if I copy the text from the text file directly, or the error output from laravel I can insert it with MySQL workbench

insert into `products` 
(`datasource_id`, `item_group_id`, `name`, `item_group_number`, `updated_at`, `created_at`) 
values (313, '7992F', 'Cluse La Boh?â?¿me Grey Leather 38mm', '7992F', '2020-08-26 11:22:48', '2020-08-26 11:22:48');

insert into `products` 
(`datasource_id`, `item_group_id`, `name`, `item_group_number`, `updated_at`, `created_at`) 
values (313, '7992F', 'Cluse La Boh?⿿me Grey Leather 38mm', '7992F', '2020-08-26 11:22:48', '2020-08-26 11:22:48')

But if I try with Product::updateOrCreate I get an error from mysql.

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE2?\xBFme ...' for column 'name' at row 1 (SQL: insert into `products` (`datasource_id`, `item_group_id`, `name`, `item_group_number`, `updated_at`, `created_at`) values (313, 7992F, Cluse La Boh?â?¿me Grey Leather 38mm, 7992F, 2020-08-26 11:22:48, 2020-08-26 11:22:48))

The database.php config is such:

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

I'm opening the file with $file = fopen(Storage::path("imports/{$this->import->datasource->id}/feed.txt"), 'r');

and just looping with while (($data = fgetcsv($file, 0, "\t")) !== false) {

PHP 7.4 with mbstring

I notice when I dump the data there is a b in front of the string "name" => b"Cluse La Boh?â?¿me Grey Leather 38mm"

Any ideas on what else I can try?

Please sign in or create an account to participate in this conversation.