Error when trying to show all users in admin panel

cgb · August 22, 2019, 11:02am

Support intro

Sorry to hear you’re facing problems

help.nextcloud.com is for home/non-enterprise users. If you’re running a business, paid support can be accessed via portal.nextcloud.com where we can ensure your business keeps running smoothly.

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces:

example

Or for longer, use three backticks above and below the code snippet:

longer
example
here

Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can

Nextcloud version (eg, 12.0.2): 16.0.3
Operating system and version (eg, Ubuntu 17.04): Ubuntu 18.04
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4
PHP version (eg, 7.1): 7.2

The issue you are facing:
When I go to the ‘Users’ panel and select ‘Everyone’ I get an error occurs and no users are shown. When clicking on the ‘Admin’ users, the users are shown correctly.

An aside:
Perhaps noteworthy that I worked through the instructions for setting up emoji’s with MariaDB, and realised a little too late that I was working off the instructions for MySQL (the page perhaps could be a bit better formatted ). Consequently some errors where shown after going through the emoji process. The emojis aren’t working and I haven’t done anything with this issue yet.

Is this the first time you’ve seen this error? (Y/N): Y

Steps to replicate it:

Top right click avatar icon
Select Users
Select ‘Everyone’

The output of your Nextcloud log in Admin > Logging:

[no app in context] Error: 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 25 OFFSET 0' with params ["%%", "%%", "%%"]:

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8' at <<closure>>

 0. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 184
    Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exception oc ... '", Doctrine\DBAL\Dr ... ]})
 1. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 158
    Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... '")
 2. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 938
    Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "SELECT `uid`, ` ... 0", {1: "%%",2: "%%",3: "%%"})
 3. /var/www/nextcloud/lib/private/DB/Connection.php line 195
    Doctrine\DBAL\Connection->executeQuery("SELECT `uid`, ` ... 0", {1: "%%",2: "%%",3: "%%"}, {1: 2,2: 2,3: 2}, null)
 4. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php line 219
    OC\DB\Connection->executeQuery("SELECT `uid`, ` ... 0", {1: "%%",2: "%%",3: "%%"}, {1: 2,2: 2,3: 2})
 5. /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php line 214
    Doctrine\DBAL\Query\QueryBuilder->execute()
 6. /var/www/nextcloud/lib/private/User/Database.php line 280
    OC\DB\QueryBuilder\QueryBuilder->execute()
 7. /var/www/nextcloud/lib/private/User/Database.php line 381
    OC\User\Database->getDisplayNames("", "25", "0")
 8. /var/www/nextcloud/lib/private/User/Manager.php line 228
    OC\User\Database->getUsers("", "25", "0")
 9. /var/www/nextcloud/apps/provisioning_api/lib/Controller/UsersController.php line 165
    OC\User\Manager->search("", "25", "0")
10. /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 166
    OCA\Provisioning_API\Controller\UsersController->getUsersDetails("", "25", "0")
11. /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 99
    OC\AppFramework\Http\Dispatcher->executeController(OCA\Provisioning ... {}, "getUsersDetails")
12. /var/www/nextcloud/lib/private/AppFramework/App.php line 126
    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Provisioning ... {}, "getUsersDetails")
13. /var/www/nextcloud/lib/private/AppFramework/Routing/RouteActionHandler.php line 47
    OC\AppFramework\App::main("OCA\\Provisioni ... r", "getUsersDetails", OC\AppFramework\ ... {}, {_route: "ocs.pr ... "})
14. <<closure>>
    OC\AppFramework\Routing\RouteActionHandler->__invoke({_route: "ocs.pr ... "})
15. /var/www/nextcloud/lib/private/Route/Router.php line 297
    undefinedundefinedcall_user_func(OC\AppFramework\ ... {}, {_route: "ocs.pr ... "})
16. /var/www/nextcloud/ocs/v1.php line 82
    OC\Route\Router->match("/ocsapp/cloud/users/details")
17. /var/www/nextcloud/ocs/v2.php line 24
    undefinedundefinedrequire_once("/var/www/nextcloud/ocs/v1.php")

GET /ocs/v2.php/cloud/users/details?offset=0&limit=25&search=
from <IP> by my.email@email.com at 2019-08-22T10:47:51+00:00

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'instanceid' => '',
  'passwordsalt' => 'salt',
  'secret' => 'kkkkkk',
  'trusted_domains' => 
  array (
    0 => 'cloud.domain.com',
  ),
  'datadirectory' => '/var/www/nextcloud/data',
  'dbtype' => 'mysql',
  'version' => '16.0.3.0',
  'overwrite.cli.url' => 'https://cloud.domain.com',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'myuser',
  'dbpassword' => 'mypassword',
  'installed' => true,
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'mysql.utf8mb4' => true,
  'maintenance' => false,
);

The output of your Apache/nginx/system log in /var/log/____:

<IP> - - [22/Aug/2019:12:39:17 +0200] "GET /ocs/v2.php/cloud/users/details?offset=0&limit=25&search= HTTP/1.1" 404 1405 "-" "Mozilla/5.0 (Windows NT 6.1; rv:60.0) Gecko/20100101 Firefox/60.0"

CFelix · August 22, 2019, 12:57pm

Hey cgb, welcome to the forums

Your issue is this line

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8' at <<closure>>

As you said it yourself, you had some errors converting your database collation from utf8 to utf8mb4.

Please could you check in your database whether your tables and their content are all set to utf8mb4? How did you try to change the collation, were you using a GUI software or did you do it via SSH?

cgb · August 22, 2019, 1:20pm

Thanks @CFelix,

I went through the following from top to bottom via ssh:
https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/mysql_4byte_support.html

While trying to get the emoji support going I got the following error after Step 6: $ sudo -u www-data php occ maintenance:repair

/var/www/nextcloud$ sudo -u www-data php occ maintenance:repair
Nextcloud is in maintenance mode - no apps have been loaded

 - Repair MySQL collation
     - Change row format for oc_addressbooks ...
     - Change collation for oc_addressbooks ...

In AbstractMySQLDriver.php line 125:
                                                                                                                                
  An exception occurred while executing 'ALTER TABLE `oc_addressbooks` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':  
                                                                                                                                
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes               
                                                                                                                                

In PDOStatement.php line 143:
                                                                                                                   
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes  
                                                                                                                   

In PDOStatement.php line 141:
                                                                                                                   
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes  
                                                                                                                   

maintenance:repair [--include-expensive]

cgb · August 22, 2019, 1:21pm

How do I do this?

CFelix · August 22, 2019, 1:51pm

Log back in into your mysql server and use the following statement, to show if your tables and columns have the right collation:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'oc_%';

This will list all your NC tables and their columns and tell you what collation they have.

As for the error:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

This should not happen, if you followed the instruction in the provided link?!

Can you also run the following commands in your mysql console, to make sure the required parameters are set?

Commands:

SHOW VARIABLES LIKE 'innodb_file_%';

Should come up with:

+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| innodb_file_format    | barracuda |
| innodb_file_per_table | ON        |
+-----------------------+-----------+

SHOW VARIABLES LIKE 'innodb_large_%';

Should come up with:

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | true  |
+---------------------+-------+

If that’s not the case, you may want to go over the documentation again, just to make sure these variables are set correctly.

cgb · August 23, 2019, 12:06pm

Re collation:
The tables oc_activity, oc_activity_mq, oc_addressbook_changes (for uri), oc_deck_assigned_users, oc_deck_attachment, oc_deck_board_acl, oc_deck_boards, oc_deck_cards, oc_deck_stacks, oc_notes_meta, have utf8mb4_bin.

The other tables have utf8_bin.

SHOW VARIABLES LIKE 'innodb_file_%';

Gives

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Antelope  |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+


SHOW VARIABLES LIKE 'innodb_large_%';

Gives

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+

I’ll run the commands on website again and will report back if that changed anything. Thanks again for helping

cgb · August 23, 2019, 12:24pm

Cool, it seems to work now not sure what I did wrong before (but I was impacted by a flu while going through the instructions…)

Anyway thanks for your help and for the good documentation, in case you have contributed to this.

Cheers!