User list "everyone" empty when setting mysql.utf8mb4 to true

ℹ️ Support
1

Nextcloud version: 18.0.4
Operating system and version: Ubuntu Server 18.04.04 LTS
Apache or nginx version: Apache/2.4.29
PHP version: 7.2

The issue you are facing:
The user list “everyone” is empty when mysql.utf8mb4 is set to true in config/config.php
It works fine when I remove the line 'mysql.utf8mb4' => true, in config.php.

Steps to replicate it:

  1. Setup database as explained here:
    https://docs.nextcloud.com/server/18/admin_manual/configuration_database/linux_database_configuration.html
    Specifically:
CREATE DATABASE nextdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  1. Install Nextcloud as usual
  2. Insert 'mysql.utf8mb4' => true, into config/config.php

The output of your Nextcloud log in Admin > Logging:

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'SELECT `uid`, `displayname` FROM `oc_users` `u` LEFT JOIN `oc_preferences` `p` ON (`userid` = `uid`) AND (`appid` = 'settings') AND (`configkey` = 'email') WHERE (`uid` COLLATE utf8mb4_general_ci LIKE ?) OR (`displayname` COLLATE utf8mb4_general_ci LIKE ?) OR (`configvalue` COLLATE utf8mb4_general_ci LIKE ?) ORDER BY `uid_lower` ASC LIMIT 500' with params ["%%", "%%", "%%"]: SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'

The output of your config.php file in /var/www/html/cloud/config:

1   <?php
  1 $CONFIG = array (
  2   'instanceid' => '',
  3   'passwordsalt' => '',
  4   'secret' => '',
  5   'trusted_domains' =>
  6   array (
  7     0 => 'localhost:8010',
  8   ),
  9   'trusted_proxies' =>
 10   array (
 11     0 => 'localhost',
 12   ),
 13   'datadirectory' => '/var/www/html/cloud/data',
 14   'dbtype' => 'mysql',
 15   'version' => '18.0.4.2',
 16   'overwrite.cli.url' => 'http://localhost:8010/cloud',
 17   'htaccess.RewriteBase' => '/cloud',
 18   'dbname' => 'nextdb',
 19   'dbhost' => 'localhost',
 20   'dbport' => '',
 21   'dbtableprefix' => 'oc_',
 22   'dbuser' => 'next',
 23   'dbpassword' => '',
 24   'mysql.utf8mb4' => true,
 25   'memcache.local' => '\\OC\\Memcache\\APCu',
 26   'installed' => true,
 27 );
2

Ok, I was able to identify the problem:

The character set and collation on the tables created by nextcloud where wrong:

MariaDB [nextdb]> show table status from nextdb;
+-----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name                        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| oc_accounts                 | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2020-05-16 10:53:12 | NULL        | NULL       | utf8_bin           |     NULL |                |         |
...

I fixed this by running:

sudo -u www-data php occ maintenance:mode --on

USE nextdb;
SET GLOBAL innodb_large_prefix=on;
SET GLOBAL innodb_file_format=Barracuda;
ALTER DATABASE nextdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

sudo -u www-data php occ maintenance:repair
sudo -u www-data php occ maintenance:mode --off

Now everything is working.