Error: Doctrine \ DBAL \ Exception \ DriverException

Nextcloud version: 20.0.5)
Operating system and version: Ubuntu 18.04)
Apache version: 2.4.29
PHP version: 7.4
MariaDB version: 10.1.47-MariaDB

When I tried to enable the Group Folders app, it fails.

Steps to replicate it:

  1. Try enable Group Folders

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 OFFSET 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 /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'instanceid' => 'ocb06ckk12kr',
  'passwordsalt' => 'q1jUK7vSvHcKriBC7WhZ2rJfGd4w3e',
  'secret' => 'so7CrIr/mpuJ75K9Hze/PYoRHn8M6rNjHFr7B/z1+PGr5kBs',
  'trusted_domains' => 
  array (
    0 => 'cloud.mydomain.com',
  ),
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'datadirectory' => '/home/cloud/public_html/data',
  'dbtype' => 'mysql',
  'version' => '20.0.5.2',
  'overwrite.cli.url' => 'https://cloud.mydomain.com',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost:3306',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'XXXXXXXXXXXXXXXXX',
  'dbpassword' => 'XXXXXXXXXXXXXXXXXX',
  'installed' => true,
  'mail_smtpmode' => 'smtp',
  'mail_smtpsecure' => 'tls',
  'mail_sendmailmode' => 'smtp',
  'mail_from_address' => 'support',
  'mail_domain' => 'mydomain.com',
  'mail_smtpauthtype' => 'LOGIN',
  'mail_smtphost' => 'mydomain.ca',
  'mail_smtpport' => '465',
  'mail_smtpauth' => 1,
  'mail_smtpname' => 'support@mydomain',
  'mail_smtppassword' => 'XXXXXXXXXXXXXXXXXXXXXXX',
  'maintenance' => false,
  'mysql.utf8mb4' => true,
);

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

Nothing in Apache log files.....

Could this be due to my MariaDB version? I have been reticent to upgrade it but I may have no choice.

Any assistance would be most appreciated.

P

1 Like

This topic has already been discussed several times in the forum. Please make sure that you’ve set-up your database settings correctly and that you’ve converted all database tables:

https://docs.nextcloud.com/server/20/admin_manual/configuration_database/mysql_4byte_support.html?highlight=byte

https://help.nextcloud.com/search?q=COLLATION%20%27utf8mb4_general_ci%27%20is%20not%20valid%20for%20CHARACTER%20SET%20%27utf8

Thanks for your input j-ed.

I have already gone through the Enabling MySQL 4-byte support process.

For show variables like ‘innodb_file_format’;, I get
±-------------------±----------+
| Variable_name | Value |
±-------------------±----------+
| innodb_file_format | Barracuda |
±-------------------±----------+
1 row in set (0.00 sec)

For show variables like ‘innodb_file_per_table’;, I get
MariaDB [(none)]> show variables like ‘innodb_file_per_table’;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| innodb_file_per_table | ON |
±----------------------±------+
1 row in set (0.01 sec)

For ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;, the response is:
Query OK, 1 row affected (0.00 sec)

For sudo -u www-data php /home/cloud/public_html/occ config:system:set mysql.utf8mb4 --type boolean --value=“true”, the response is:
System config value mysql.utf8mb4 set to boolean true. For confirmation, I checked config.php:
’mysql.utf8mb4’ => true,

The next step seems to be the problem. To convert all existing tables to the new collation, running sudo -u www-data php occ maintenance:repair generates the response:

  • Repair MySQL collation
    • Change row format for oc_addressbooks …
    • Change collation for oc_addressbooks …

In AbstractMySQLDriver.php line 106:

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 129:

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

In PDOStatement.php line 127:

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

I have no idea what this means or how to fix it.

Your thoughts?

P

Check-out the following postings which are related to this error message:

https://help.nextcloud.com/search?q=%20Specified%20key%20was%20too%20long;%20max%20key%20length%20is%20767%20bytes

Thanks j-ed…

The following seems to have my problem

Should this be included in the documentation for Enabling MySQL 4-byte support?

Again…many thanks.

DrP

1 Like

As far as I can see this is already part of the documentation, isn’t it?

https://docs.nextcloud.com/server/20/admin_manual/configuration_server/config_sample_php_parameters.html?highlight=innodb#all-other-configuration-options
https://docs.nextcloud.com/server/20/admin_manual/configuration_database/mysql_4byte_support.html?highlight=innodb