Illegal mix of collations in urlrewrite module

[indykoning]

Preconditions and environment

• Magento version: 2.4.8-p2

Steps to reproduce

1. Navigate to your magento store with a urlencoded non-utf8 character https://example.com/%C0, another example with a utf8 character but it being an emoj https://example.com/🔎
2. See as an error gets produced because invalid characters are put into the mysql query

Expected result

A 404 page, or redirect if it has been set

Actual result

An error gets thrown:

SQLSTATE[HY000]: General error: 1271 Illegal mix of collations for operation ' IN ', query was: SELECT `url_rewrite`.* FROM `url_rewrite` WHERE (`request_path` IN ('%C0', '%C0/', '�', '�/')) AND (`store_id` IN ('1'))

Additional information

This error comes from: vendor/magento/module-url-rewrite/Model/Storage/DbStorage.php@doFindOneByData

Changing the code like this, does fix it. Question is, is it the right way?

-            $decodedRequestPath = urldecode($requestPath);
+            $decodedRequestPath = mb_convert_encoding(urldecode($requestPath), 'UTF-8');

This replaces those unknown characters to ?

Release note

No response

Triage and priority

• [ ] Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• [ ] Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• [ ] Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• [ ] Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• [ ] Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

[m2-assistant[bot]]

Hi @indykoning. Thank you for your report. To speed up processing of this issue, make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce.

• For more details, review the Magento Contributor Assistant documentation.
• Add a comment to assign the issue: @magento I am working on this
• To learn more about issue processing workflow, refer to the Code Contributions.

---

Join Magento Community Engineering Slack and ask your questions in #github channel. :warning: According to the Magento Contribution requirements, all issues must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting. :clock10: You can find the schedule on the Magento Community Calendar page. :telephone_receiver: The triage of issues happens in the queue order. If you want to speed up the delivery of your contribution, join the Community Contributions Triage session to discuss the appropriate ticket.

[indykoning]

Related: https://github.com/magento/magento2/issues/40138#issuecomment-3220679187

[m2-assistant[bot]]

Hi @engcom-November. Thank you for working on this issue. In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
• [ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue.
• [ ] 3. Add Area: XXXXX label to the ticket, indicating the functional areas it may be related to.
• [ ] 4. Verify that the issue is reproducible on 2.4-develop branch
  Details

  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!
• [ ] 5. Add label Issue: Confirmed once verification is complete.
• [ ] 6. Make sure that automatic system confirms that report has been added to the backlog.

[engcom-November]

Hello @indykoning ,

Thank you for your report and collaboration.

We are proceeding to close this ticket as it is a duplicate of the following issue:

https://github.com/magento/magento2/issues/40138.

Therefore, this issue will be marked as closed.

Thank you once again!

[hostep]

@engcom-November: I'm not convinced it's an exact duplicate, it's very related, but not the same, because #40138 (and its fix #40142) only deal with tax related functionalities and not with url_rewrite related functionalities.

[indykoning]

Exactly as you say, it is an issue that's similar in nature (multibyte string issues). But quite different in location and fix.

[hostep]

@engcom-November: can you re-open this please?

[engcom-November]

Hello @indykoning

Thank you, @hostep, for bringing this to our attention.

We appreciate your report and collaboration on this matter.

Based on the comments provided above, we confirm the issue as reported.

Thank you once again!

[github-jira-sync-bot]

:white_check_mark: Jira issue https://jira.corp.adobe.com/browse/AC-15647 is successfully created for this GitHub issue.

[m2-assistant[bot]]

:white_check_mark: Confirmed by @engcom-November. Thank you for verifying the issue.
Issue Available: @engcom-November, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

[DmitryFurs]

@hostep @engcom-November @indykoning i was able to fix this by updating app/etc/env.php

For MariaDB 11.4

 'db' => [
     'table_prefix' => '',
     'connection' => [
         'default' => [
             'host' => 'localhost',
             'dbname' => 'magento_db',
             'username' => 'root',
             'password' => 'admin123',
             'model' => 'mysql4',
             'engine' => 'innodb',
-            'initStatements' => 'SET NAMES utf8;',
+            'initStatements' => 'SET NAMES utf8mb4;',
             'active' => '1'
         ]
     ]
 ]

For MySQL 8

 'db' => [
     'table_prefix' => '',
     'connection' => [
         'default' => [
             'host' => 'localhost',
             'dbname' => 'magento_db',
             'username' => 'root',
             'password' => 'admin123',
             'model' => 'mysql4',
             'engine' => 'innodb',
-            'initStatements' => 'SET NAMES utf8;',
+            'initStatements' => 'SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;',
             'active' => '1'
         ]
     ]
 ]

Default value: https://github.com/magento/magento2/blob/2.4.8-p3/setup/src/Magento/Setup/Model/ConfigOptionsList.php#L163

Documentation: https://experienceleague.adobe.com/en/docs/commerce-operations/configuration-guide/files/config-reference-envphp?utm_source=chatgpt.com#db

[djadobe16]

@DmitryFurs Thank you for your solution. Can you just confirm me that which mysql patch version you are using?

[hostep]

Official fix seems to have landed here: ACP2E-4328: Suspicious requests are throwing exceptions in the exception.log

However, I really like @DmitryFurs's suggestion, would be good if we can investigate that one a bit? Since this problem happens in other places as well, like in #40138. @thiaramus / @engcom-november / @glo71317

[DmitryFurs]

@djadobe16

$ mysql --version
mysql  Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

[engcom-Bravo]

Hi @indykoning,

Adobe Commerce Engineering team started working on this issue. We will reach out to you if we need more information and you will get notified once the issue is fixed. Please leave comments for any further questions. Thank you!

[engcom-November]

Hello,

As I can see this issue got fixed in the scope of the internal Jira ticket ACP2E-4328 by the internal team

Related commits: https://github.com/search?q=repo%3Amagento%2Fmagento2+ACP2E-4328&type=commits

Based on the Jira ticket, the target version is 2.4.9-beta1.

[hostep]

@engcom-November : can I ask you to review my earlier remark: https://github.com/magento/magento2/issues/40189#issuecomment-3569560714, I feel like there may be a better solution to this issue.

[djadobe16]

@hostep
Investigated and successfully reproduced the issue. The root cause is that the initStatement parameter in env.php is automatically set to utf8 by default, which is incorrect. While the system has built-in logic to handle collation selection based on MySQL/MariaDB versions, this default initStatement value overrides that logic and forces the connection to use utf8 during SELECT operations. The table-level collations are correctly configured during setup install and update.

Will work on fixing this issue, as the system should not add initStatement to env.php by default.

[hostep]

Thanks @djadobe16!

I was also following https://github.com/OpenMage/magento-lts/pull/4173 a bit (the community fork of Magento 1), which seems to suggest that the initStatements was a hack for very old PHP versions (< 5.3.6), and it should be fine to drop it in these days. (It would probably even have been possible to drop this statement when Magento 2.0.0 was released in 2015, which had a minimum requirement of PHP 5.5 if I remember it correctly. So, good to see that 10 years later we are finally cleaning this up 🙌)

[DmitryFurs]

MySQL:

utf8 has been used by MySQL in the past as an alias for the utf8mb3 character set, but this usage is now deprecated

The recommended character set for MySQL is utf8mb4. All new applications should use utf8mb4.

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html

MariaDB:

utf8 is an alias for utf8mb3, but this can changed to ut8mb4 by changing the default value of the old_mode system variable.

https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/unicode

[djadobe16]

Hi everyone,

We investigated the reported issues in detail and, based on our findings, decided to revert the following commit for now: https://github.com/magento/magento2/commit/a2e91ce36426696e3eb13ddae94fb1025795b867

During the RCA, we identified that the root cause is the default initStatement value (SET NAMES utf8). This setting was leading to the observed issues, including the URL rewrite problem.

As an immediate stabilization step, we have cleaned up the default initStatement value (SET NAMES utf8). This change has resolved the URL rewrite issue in our testing and is expected to prevent similar problems in other related scenarios as well.

We are now working on a more robust and comprehensive fix that addresses all these issues together, rather than applying partial or isolated changes. We'll share updates once the improved solution is ready for review.

Thanks to everyone for reporting, investigating, and collaborating on this. Your feedback has been very helpful.