I am having lot of problems processing CSVs with ñ,é, and all of those non ASCII chars, and saving those words in MySQL.
In order to solve this I created a CSV that contains in one cell this data:
iphonée@3,;= ÑÑñe x
I know that if I can save this word I will solve the problem.
The thing is that when I process the CSV I get this cell as
b"iphonée@3,;= ÑÑñe x"
Binary String!!. So when I do a select in my database using this data, I get this error:
QueryException {#1695
#sql: "select * from `seller_product_languages` where `seller_product_id` = ? and `lang` = ? and `name` = ? and `description` = ? and `description_html` = ? and `bullet_html` is null and `bullet` = ? and `meta_keywords` is null limit 1"
#bindings: array:6 [
0 => 102
1 => "es"
2 => b"iphonée@3,;= ÑÑñe x"
3 => "negro"
4 => "negro"
5 => ""
]
#message: b"SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' (SQL: select * from `seller_product_languages` where `seller_product_id` = 102 and `lang` = es and `name` = iphonée@3,;= ÑÑñe x and `description` = negro and `description_html` = negro and `bullet_html` is null and `bullet` = and `meta_keywords` is null limit 1)"
#code: "HY000"
#file: "/home/vagrant/Code/...../vendor/laravel/framework/src/Illuminate/Database/Connection.php"
#line: 664
-previous: PDOException {#1694
#message: "SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='"
#code: "HY000"
#file: "/home/vagrant/Code/..../vendor/laravel/framework/src/Illuminate/Database/Connection.php"
#line: 330
+errorInfo: array:3 [
0 => "HY000"
1 => 1267
2 => "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='"
My column type is VARCHAR(255), but Im also using TEXT type.
So, if I don't use non ASCII chars on my string, I get a normal string (not a binary string) and I can save it perfectly. The problem is when I add Ñéá or any weird character, I get a binary string and that binary string makes my database crush.
I looked and all seller_product_languages columns are UTF-8 with default collation.
So here is the thing:
How can I convert a binary string into a normal string? And how can I detect that the string is a binary string in order to make this conversion?
Thats the only solution I thought of, if you think you have another one, I would be grateful.
P.S.: The closest I get to the solution was doing:
$arr = unpack("a*",$binary_string);
But I get only the ASCII chars:
$arr[1] =iphon�e@3,;= ���e x
Environment :
SHOW VARIABLES LIKE 'c%' character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ check_proxy_users OFF collation_connection utf8_general_ci collation_database utf8_general_ci collation_server latin1_swedish_ci completion_type NO_CHAIN concurrent_insert AUTO connect_timeout 10 core_file OFF
show create table seller_products;
CREATE TABLE `seller_products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`subcategory_id` int(10) unsigned NOT NULL,
`subcategory_name` varchar(45) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
`sku` varchar(45) NOT NULL,
`brand` varchar(45) DEFAULT NULL,
`weight` int(10) DEFAULT NULL,
`height` int(10) DEFAULT NULL,
`length` int(10) DEFAULT NULL,
`width` int(10) DEFAULT NULL,
`stock` int(10) unsigned NOT NULL DEFAULT '9999',
`country_iso` varchar(2) DEFAULT NULL,
`amount_usd` decimal(10,2) NOT NULL,
`tax_usd` decimal(10,2) NOT NULL DEFAULT '0.00',
`shipping_usd` decimal(10,2) NOT NULL DEFAULT '0.00',
`total_amount_usd` decimal(11,2) NOT NULL DEFAULT '0.00',
`discount_applied` decimal(5,4) NOT NULL DEFAULT '0.0000',
`enabled` tinyint(1) NOT NULL DEFAULT '1',
`hs_code` varchar(20) DEFAULT NULL,
`pickup_address` int(10) unsigned DEFAULT NULL,
`locked` tinyint(1) NOT NULL DEFAULT '0',
`seller_sku` varchar(45) DEFAULT NULL,
`chargeable_weight` decimal(6,3) NOT NULL,
`gross_weight` decimal(6,3) DEFAULT NULL,
`product_weight` decimal(6,3) DEFAULT NULL,
`product_length` int(10) DEFAULT NULL,
`product_width` int(10) DEFAULT NULL,
`product_height` int(10) DEFAULT NULL,
`warehouse` varchar(15) DEFAULT NULL,
`pickup_shipping_usd` decimal(7,2) NOT NULL DEFAULT '0.00',
`seller_fee_usd` decimal(7,2) NOT NULL DEFAULT '0.00',
`fullfilment_usd` decimal(7,2) NOT NULL DEFAULT '0.00',
`seller_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sku_UNIQUE` (`sku`),
KEY `reseller_product_subcategory_idx` (`subcategory_id`),
KEY `seller_products_unique` (`sku`),
KEY `seller_product_address_idx` (`pickup_address`),
CONSTRAINT `reseller_product_subcategory` FOREIGN KEY (`subcategory_id`) REFERENCES `subcategories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `seller_product_address` FOREIGN KEY (`pickup_address`) REFERENCES `merchant_addresses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
show create table seller_product_languages
CREATE TABLE `seller_product_languages` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
`seller_product_id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`bullet` text,
`description` text,
`meta_keywords` text,
`lang` varchar(2) NOT NULL,
`description_html` text,
`bullet_html` text,
`default` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `index3` (`seller_product_id`,`lang`),
KEY `fk_seller_product_languaje_seller_products1_idx`
(`seller_product_id`),
CONSTRAINT `fk_seller_product_languaje_seller_products1` FOREIGN
KEY (`seller_product_id`) REFERENCES `seller_products` (`id`) ON
DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
I solve it changing in the config/database.php file, the 'mysql' array:
It was in:
'mysql' => [
...
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
...
],
And I change it to:
'mysql' => [
...
'charset' => 'utf8',
'collation' => 'utf8_general_ci',
...
],