Syntax error or access violation: 1253 COLLATION 'utf8mb4_ general_ci' is not valid for CHARACTER SET 'utf8mb3

Support intro

Sorry to hear you’re facing problems :slightly_frowning_face: is for home/non-enterprise users. If you’re running a business, paid support can be accessed via 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:


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


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

Nextcloud version (eg, 20.0.5): 20.0.14
Operating system and version (eg, Ubuntu 20.04): Solaris 11.4 (x86)
Apache or nginx version (eg, Apache 2.4.25): 2.4.51
PHP version (eg, 7.4): 7.4.23

The issue you are facing:
Trying to move beyond NC20. SInce upgrading to PHP8 I am seeing errors with occ commands about utf8mb3

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

Steps to replicate it:

  1. Run a scan (php occ music:scan

The output of your Nextcloud log in Admin > Logging:

Error	core	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 'utf8mb3'

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

$CONFIG = array (
  'passwordsalt' => 'XXXXXXXXXX',
  'secret' => 'XXXXXXXXXXX',
  'trusted_domains' => 
  array (
    0 => 'localhost',
    1 => '',
    2 => '',
    3 => 'ytc-cloud:643',
    4 => '',
    5 => '',
    6 => 'ytc-cloud:80',
  'datadirectory' => '/nextcloud/server/nextcloud/data',
  'overwrite.cli.url' => 'http://localhost',
  'dbtype' => 'mysql',
  'version' => '',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'oc_admin',
  'dbpassword' => 'XXXXXXXXX',
  'logtimezone' => 'UTC',
  'installed' => true,
  'instanceid' => 'oc798ms43svp',
  'loglevel' => 0,
  'mail_from_address' => 'bruce',
  'mail_smtpmode' => 'smtp',
  'mail_domain' => '',
  '' => false,
  'theme' => '',
  'mail_sendmailmode' => 'smtp',
  'maintenance' => false,
  'mysql.utf8mb4' => true,
  'mail_smtpsecure' => 'tls',
  'mail_smtpauth' => 1,
  'mail_smtpauthtype' => 'LOGIN',
  'mail_smtphost' => '',
  'mail_smtpport' => '587',
  'mail_smtpname' => 'XXXXXXX',
  'mail_smtppassword' => 'XXXXXX',
  'music.lastfm_api_key' => '5b185aea65ea445ffd1cb301543e9a46',

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

[Wed Feb 09 17:11:03.936245 2022] [mpm_event:notice] [pid 7583:tid 1] AH00489: Apache/2.4.51 (Unix) PHP/7.4.23 OpenSSL/1.0.2za configured -- resuming normal operations
[Wed Feb 09 17:11:03.936456 2022] [core:notice] [pid 7583:tid 1] AH00094: Command line: '/usr/apache2/2.4/bin/httpd'
[Thu Feb 10 10:38:59.931337 2022] [php7:error] [pid 7587:tid 12] [client] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/nextcloud/lib/private/DB/Connection.php:72\nStack trace:\n#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1449): OC\\DB\\Connection->connect()\n#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(892): Doctrine\\DBAL\\Connection->getWrappedConnection()\n#2 /var/www/nextcloud/lib/private/DB/Connection.php(202): Doctrine\\DBAL\\Connection->executeQuery()\n#3 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): OC\\DB\\Connection->executeQuery()\n#4 /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(248): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /var/www/nextcloud/lib/private/AppConfig.php(345): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 /var/www/nextcloud/lib/private/AppConfig.php(110): OC\\AppConfig->loadConfigValues()\n#7 /var/www/nextcloud/lib/priv in /var/www/nextcloud/lib/private/DB/Connection.php on line 72

Ok, somewhere along the way I must have noticed the warning

MySQL is used as database but does not support 4-byte characters. To be able to handle 4-byte characters (like emojis) without issues in filenames or comments for example it is recommended to enable the 4-byte support in MySQL. For further details read the documentation page about this.

And set the utd8mb4 to be true in config.php

I have set it to false, and the errors appear to have gone away.
‘mysql.utf8mb4’ => false,

That leaves the question, how do I get it set and get rid of the warning?

I think it is an issue with the user table, any help in sorting this out would be appreciated