DB collation error during install

Installation
1

Trying to perform a NEW install of C5 and are getting the following error:
“unrecognized result of the show collation database query”

Details:
mariadb v11.2,
C5 V: 8.5.2
PHP: 7.4.33 (tried 8.x but of course not supported with this ver of C5).
DB details: CREATE DATABASE DataBase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

The correct user has unlimited access to the DB. We even tried using the mysql root account - so it doesn’t appear to be an account issue.

We have done this (i.e. the same steps) many times in past and have never seen this error. We get it on Ubuntu and Windows, so we are sure it’s not the OS.

We have confirmed the DB creation according to the this page: Database should support the configured character set and collation - ERROR - concrete5

Also, there are some reasons for using this version of C5, so we want to stick with it for now.

Any pointers would be appreciated.

2

That forum page is way out of date (though may have been current at the time of 8.5.2). Current v9 installs default to utf8mb4 and utf8mb4_unicode_ci

Could there be a misunderstanding? The installer doesn’t set collation for you. You need to create the database with the character set and collation. The installer then checks the character set and collation are suitable.

3

Thanks for this. When I created the DB I always use this script (prior to V9):
CREATE DATABASE DataBase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;

Using C5 8.4.2

4

Looking at 8.5.2 on Github

class Concrete\Core\Database\Connection\Connection

 public function getSupportedCollations()
    {
        if ($this->supportedCollations === null) {
            $supportedCollations = [];
            $rs = $this->executeQuery('SHOW COLLATION');
            while (($row = $rs->fetch(PDO::FETCH_ASSOC)) !== false) {
                if (!isset($row['Collation']) || !isset($row['Charset'])) {
                    throw new Exception(t('Unrecognized result of the "%s" database query.', 'SHOW COLLATION'));
                }
                $supportedCollations[strtolower($row['Collation'])] = strtolower($row['Charset']);
            }
            $this->supportedCollations = $supportedCollations;
        }

        return $this->supportedCollations;
    }

So the SHOW COLLATION query has not returned either/both of Collation and Charset columns.

As a check, you could try the same query as the same database user from phpMyAdmin or the MySQL CLI.

5

Okay all, I think we got this. Remembered an old thread (or post) that mentioned one had to use mysql 5.7 with C5 8.4, we are using C5 8.5.

In short, we were running mariadb 11.x. Downgraded to mariadb 10.4 and now we can install.

Didn’t even think of mariadb version…

6

You can keep mariaDB 11.x with ConcreteCMS 8.5.x, but you have to use this patch:

It has been merged in v9 branch, but never in v8 branch, don’t know why…

1 Like
7

Concrete 9.0.1
10.11.6-MariaDB-0+deb12u1 - Debian 12
PHP 7.4.33

My MariaDB supports different collation - charset pairs where charset is NULL, uca1400_ai_ci to uca1400_croatian_nopad_as_cs. Unfortunately I cannot change this. To fix this, the file concrete\concrete\src\Database\Connection.php needs to be edited. The line:
if (!isset($row[‘Collation’])|| !isset($row[‘Charset’])) {
needs to be replaced with:
if (!isset($row[‘Collation’]) && !isset($row[‘Charset’])) {

|| is replaced by &&, means OR to AND.
This solved my problem.

1 Like