Conversion to 4-byte UTF-8 fails when the database is using prefixes.

[klonos]

This was reported in the Zulip chat here: https://backdrop.zulipchat.com/#narrow/stream/218635-Backdrop/topic/converting.20db.20to.20utf8mb4

I have an upgraded D7 site that's working fine, but I wanted to add mb4 support (to display emoticons imported with the Tweet Feed module). I followed the instructions at https://docs.backdropcms.org/documentation/database-configuration but when I click the link to upgrade database to 4-byte UTF-8 I get an error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax to use
near '"dr2s_%"' at line 1: SHOW TABLES LIKE "dr2s_%"; Array ( )

(tables have the prefix 'dr2s:').

What am I doing wrong?

Update: I renamed all the tables to remove the prefix and the upgrade worked without a hitch.

[nattywebdev]

The dev computer where this was seen is running:

• Backdrop 1.23.1
• PHP 7.4.27
• MySQL 5.5.5-10.4.22-MariaDB
• Web server Apache/2.4.52 (Unix) OpenSSL/1.1.1m PHP/7.4.27 mod_perl/2.0.11 Perl/v5.32.1

Also seen on staging server:

• Backdrop 1.23.1
• PHP 7.2.34
• MySQL 10.3.38-MariaDB-log-cll-lve
• Web server: LiteSpeed

[argiepiano]

Perhaps related to this post?

[kiamlaluno]

Is this caused by Backdrop core? The only line using SHOW TABLES LIKE is in the core/modules/simpletest/backdrop_web_test_case.php file.

$tables = db_query("SHOW TABLES LIKE :prefix", array(':prefix' => db_like($prefix) . '%' ))->fetchCol();

[kswan]

@kiamlaluno, it looks like it is here https://github.com/backdrop/backdrop/blob/f1de435572903716c8f665874b8ca6a7a6388172/core/includes/database/charset_converter.inc#L110

[kiamlaluno]

@kswan My bad: I used git grep -n --fixed-strings 'SHOW TABLES LIKE' instead of git grep -n --fixed-strings 'SHOW TABLES'.

'SHOW TABLES' is used in core/includes/database/charset_converter.inc, core/modules/simpletest/backdrop_web_test_case.php, core/modules/simpletest/tests/database_test.test, and core/modules/system/system.admin.inc. If those files do not cause the bug reported here, a new issue should be opened for the remaining files.