Problem/Motivation

With MySQL 8.0.22, per the changelog:

Regular expression functions such as REGEXP_LIKE() yielded inconsistent results with binary string arguments. These functions now reject binary strings with an error. (Bug #31031886, Bug #98951, Bug #31031888, Bug #98950)

As a result, using BINARY (vs. NOT REGEXP) as an option (per the default setting upon install and the module's config form for transliteration_file_lowercase === TRUE), no longer work.

Steps to reproduce

Download the module and attempt to enable/install Transliteration while using MySQL 8.0.22+ as your site's database. In my case, it returns the following error (though the character set may vary for other users):

SQLSTATE[HY000]: General error: 3995 Character set 'utf8_bin' cannot be used in conjunction with 'binary' in call to regexp_like.

Proposed resolution

Update the SQL statement to cast the uri as BINARY for MySQL databases.

Remaining tasks

Review the attached patch and see if it works correctly on MySQL 8.0.22 and lower MySQL versions.

User interface changes

None.

API changes

None.

Data model changes

None.

CommentFileSizeAuthor
#5 mysql8-3196961-5.patch805 byteslisotton
#3 transliteration-MySQL-8-BINARY-fix-3196961-1.patch1.05 KBaubjr_drupal
transliteration-MySQL-8-BINARY-fix-N-1.diff1.05 KBaubjr_drupal

Comments

aubjr_drupal created an issue. See original summary.

aubjr_drupal’s picture

aubjr_drupal commented
Title: Module install breaks on MySQL 8.0.22+ (BINARY) » Module install breaks on MySQL 8.0.22+ (General error: 3995 Character set 'utf8_bin' cannot be used in conjunction with 'binary' in call to regexp_like)
aubjr_drupal’s picture

aubjr_drupal commented
StatusFileSize
new transliteration-MySQL-8-BINARY-fix-3196961-1.patch1.05 KB
Irbis’s picture

Irbis commented

Patch #3 also works for the following error:

PDOException: SQLSTATE[HY000]: General error: 3995 Character set 'utf8_bin' cannot be used in conjunction with 'binary' in call to regexp_like.: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {file_managed} file_managed WHERE (uri NOT REGEXP BINARY :db_condition_placeholder_0) ) subquery; Array ( [:db_condition_placeholder_0] => /[a-z0-9_.-]+$ ) in function transliteration_retroactive() (line 21 in file .../sites/all/modules/transliteration/transliteration.admin.inc).

Environment versions:

Drupal: 7.78
Database 4 byte UTF-8 support: Enabled
PHP: 7.4.16
Web-server: Apache/2.4.46 (Ubuntu)
DBMS version: 8.0.23

lisotton’s picture

lisotton commented
StatusFileSize
new mysql8-3196961-5.patch805 bytes

I think the problem is because #3 changed the condition, it is entering there only if strictly equal to 1, so if the default value is returned (which is TRUE), it will not enter the condition.

aitala’s picture

aitala commented

Just hit this today - thanks for the patch. Does the job.

jmizarela’s picture

jmizarela commented
Status: Active » Needs review
renatog’s picture

renatog commented
Status: Needs review » Reviewed & tested by the community

#5 Really makes, +1 to it. Marking as RTBC
Thanks a lot Lisotton o/

  • 8ce77cf committed on 7.x-3.x 
    Issue #3196961 by aubjr_drupal, lisotton, jmizarela, Irbis, RenatoG,...
renatog’s picture

renatog commented
Version: 7.x-3.2 » 7.x-3.x-dev
Status: Reviewed & tested by the community » Fixed

Moved to the dev branch

Thank you so much everyone for your contribution

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

emersonreis.dev’s picture

emersonreis.dev
he/him/his
Primary language Portuguese, Brazil
Location Balneário Camboriú
commented

Are there any plans for a new release including this patch?

amateescu’s picture

amateescu commented
Issue tags: -database

I released 7.x-3.3, thanks for the reminder.. :)