Hallo Zusammen,
Mein Plugin importiert/aktualisiert Tausende von Artikeln in Shopware Datenbank. Nach dem Update vom Shopware bis zu 5.3.7 habe ich angefangen beim Importprozess denfolgenden Fehler zu bekommen (die Fehlermeldung selbst kommt von meinem Plugin):
2018-03-30 09:51:50: The article 61460 could not be saved (An exception occurred while executing 'SELECT t0.sorting_ids AS sorting_ids_1, t0.hide_sortings AS hide_sortings_2, t0.facet_ids AS facet_ids_3, t0.id AS id_4, t0.parent AS parent_5, t0.stream_id AS stream_id_6, t0.description AS description_7, t0.position AS position_8, t0.meta_title AS meta_title_9, t0.metakeywords AS metakeywords_10, t0.metadescription AS metadescription_11, t0.cmsheadline AS cmsheadline_12, t0.cmstext AS cmstext_13, t0.active AS active_14, t0.template AS template_15, t0.product_box_layout AS product_box_layout_16, t0.blog AS blog_17, t0.path AS path_18, t0.external AS external_19, t0.external_target AS external_target_20, t0.hidefilter AS hidefilter_21, t0.hidetop AS hidetop_22, t0.changed AS changed_23, t0.added AS added_24, t0.mediaID AS mediaID_25, t26.id AS id_27, t26.categoryID AS categoryID_28, t26.attribute1 AS attribute1_29, t26.attribute2 AS attribute2_30, t26.attribute3 AS attribute3_31, t26.attribute4 AS attribute4_32, t26.attribute5 AS attribute5_33, t26.attribute6 AS attribute6_34, t26.categoryID AS categoryID_35, t0.mediaID AS mediaID_36, t0.stream_id AS stream_id_37, t0.parent AS parent_38 FROM s_categories t0 LEFT JOIN s_categories_attributes t26 ON t26.categoryID = t0.id WHERE t0.description = ? AND t0.parent = ? LIMIT 1' with params ["\x55\x53\x42\x2f\x46\x69\x72\x65\x57\x69\x72\x65\x2d\x5a\x75\x62\x65\x68\xf6\x72", 3]:
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='). The exception comes from /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php on line 119.
Es geht, soweit ich verstehe, um die Spalten s_categories.id und s_categories_attributes.categoryID. Ich habe weiter die Kollationen-Daten überprüft:
(rreimche@localhost) [frontsign]> SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "frontsign" AND T.table_name = "s_categories";
+--------------------+
| character_set_name |
+--------------------+
| utf8 |
+--------------------+
1 row in set (0,00 sec)
(rreimche@localhost) [frontsign]> SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "frontsign" AND T.table_name = "s_categories_attributes";
+--------------------+
| character_set_name |
+--------------------+
| utf8 |
+--------------------+
1 row in set (0,01 sec)
(rreimche@localhost) [frontsign]> SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = "frontsign" AND table_name = "s_categories" AND column_name = "id";
+--------------------+
| character_set_name |
+--------------------+
| NULL |
+--------------------+
1 row in set (0,01 sec)
(rreimche@localhost) [frontsign]> SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = "frontsign" AND table_name = "s_categories_attributes" AND column_name = "categoryID";
+--------------------+
| character_set_name |
+--------------------+
| NULL |
+--------------------+
1 row in set (0,00 sec)
(rreimche@localhost) [frontsign]> SELECT default_character_set_name FROM information_schema.SCHEMATA
-> WHERE schema_name = "frontsign";
+----------------------------+
| default_character_set_name |
+----------------------------+
| latin1 |
+----------------------------+
1 row in set (0,00 sec)
Auch wenn ich versuche “SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘frontsign’;” sehe ich, dass alle Spalten sind enweder von utf8_unicode_ci oder von NULL. Woher kommt dann in der Fehlermeldung “utf8mb4_general_ci”?
Liegt das Problem auch an der Kollation von der Datenbank? Oder liegt das eher an “WHERE t0.description = ? AND t0.parent = ?” Wie könnte ich das Problem lösen?
Danke im Vorab.