Error when adding new page with non-ascii characters in a page field

[gideonso]

Short description of the issue

Error when adding new page with non-ascii characters in a page field

Expected behavior

Page should be successfully created without any error.

Actual behavior

Error occurs and page not get created

Optional: Screenshots/Links that demonstrate the issue

Optional: Suggestion for a possible fix

NIL

Steps to reproduce the issue

1. create a page reference field that allows user to create new page in the page edit page.
2. try to create a new page with non ASCII characters in the page reference field.
3. got this error:

Error adding page "一二三" - SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Setup/Environment

• ProcessWire version: this bug was introduce in 3.0.111 and remains till 3.0.115
• (Optional) PHP version: 7.2.10 and 5.6.29

[gideonso]

Hi @ryancramerdesign

I think it is urgent to take a look on this newly introduced issue.

Gideon

[BitPoet]

The exception occurs when executing the query in PageNames::pageNameExists, and the reason is that the pages.name column is explicitly set to ascii character set at creation in install.sql.

@ryancramerdesign: I don't think there is still a compelling reason for pages.name still having to be ascii (correct me if I'm wrong) since all input through core methods is sanitized anyway if $config->pageNamesCharset is still ascii.

As a workaround, one can change the charset for the name column manually after installation by executing the following MySQL statement:

ALTER TABLE pages MODIFY COLUMN `name` varchar(128) character set utf8 NOT NULL;

Replace utf8 with utf8mb4 if you selected that character set at installation time.

[gideonso]

Thanks for the work around. If it is a multi-language site, there are more than one name column. Do I need to change all of them to utf8 or utf8mb4??

P.S. I tested your work around and it it works nicely. @ryancramerdesign will you consider this change to the core??

[BitPoet]

If it is a multi-language site, there are more than one name column. Do I need to change all of them to utf8 or utf8mb4?

Yes, LanguageSupportPageNames also adds new name columns with an ascii charset.

[AndZyk]

Hello,

this issues doesn't only affect the page field but also creating new pages regular.

I have many multi-language sites which use the nice UTF8-page names feature:
$config->pageNameCharset = 'UTF8';

First introduced here.

Often I want Umlauts in every language name, but I can only have Umlauts in the default language name. It is not possible anymore to have Umlauts in another language, like it used to. This is a little bit annoying and I hope this will be fixed.

Regards, Andreas

[gideonso]

I don't have problem with creating new page with non-ascii character but I do think thios issue really worth Ryan to take a look at it.

[ryancramerdesign]

ProcessWire always stores page names as ascii. When using UTF-8 for the charset, it stores them as encoded UTF-8 which is ascii compatible. I don't recommend changing your character set on the name field. I think the issue here is that the highlighted pageNameExists() method in /wire/core/PagesNames.php needs to perform this encoding conversion, and it isn't at present. I've just pushed a possible fix for this. Can you let me know if you find it does/doesn't fix it? Thanks.

[gideonso]

Hi @ryancramerdesign,

I reverted the name field to ascii general ci collation and confirmed that your commit fixed this issue. Thanks for your hard work.

[AndZyk]

Hello @ryancramerdesign,

I am not sure if this is related, but I also want to confirm that with the newest changes it is possible to add new pages with Umlauts in different languages. But you still get an error notification after saving the page, even though it saved corret.

Add new page:

Error notification after saving:

Saved page name with Umlauts in different languages correct:

Thank you for looking into this.

Regards, Andreas

[gideonso]

Hi @AndZyk,

Can you confirm if this bug is fixed?? If it is fixed, I will close this issue.

Gideon