Enabling MySQL 4-byte support doesn't work

[kaffeeringe]

Steps to reproduce

I followed the steps fpr MySQL described here: https://docs.nextcloud.com/server/latest/admin_manual/configuration_database/mysql_4byte_support.html

Expected behaviour

The administration and the News plugin stop telling me to change collation.

Actual behaviour

It fails in step 5 while changing collation for nc_addressbooks:

In AbstractMySQLDriver.php line 125:

An exception occurred while executing 'ALTER TABLE nc_addressbooks CONVER
T TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':

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

In PDOStatement.php line 143:

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

In PDOStatement.php line 141:

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

I can't change the config for MySQL... so the solutions that I found regarding some innodb-settings don't work for me.

Server configuration

Operating system: Linux 4.4.0-148-generic #174-Ubuntu SMP Tue May 7 12:20:14 UTC 2019 x86_64

Webserver: Apache (fpm-fcgi)

Database: mysql 5.6.44

PHP version: 7.3.6-nmm1

Modules loaded: Core, date, libxml, openssl, pcre, zlib, bcmath, bz2, calendar, ctype, dom, hash, filter, ftp, gettext, SPL, iconv, json, mbstring, posix, Reflection, session, standard, shmop, SimpleXML, soap, exif, sysvsem, sysvshm, tokenizer, wddx, xml, xmlreader, xmlwriter, zip, cgi-fcgi, propro, raphf, http, mysqlnd, PDO, curl, dba, fileinfo, gd, gender, gmp, htscanner, imagick, imap, interbase, intl, ldap, mongodb, mysqli, OAuth, odbc, pdo_dblib, pdo_mysql, PDO_ODBC, pdo_pgsql, pdo_sqlite, pgsql, Phar, pspell, recode, sodium, sqlite3, tidy, xsl, ionCube Loader, Zend OPcache

Nextcloud version: 16.0.1 - 16.0.1.1

Updated from an older Nextcloud/ownCloud or fresh install:

Where did you install Nextcloud from: unknown

Signing status

Array ( )

List of activated apps

Enabled:

• accessibility: 1.2.0
• activity: 2.9.1
• bookmarks: 1.0.5
• calendar: 1.7.0
• cloud_federation_api: 0.2.0
• contacts: 3.1.3
• dav: 1.9.2
• encryption: 2.4.0
• federatedfilesharing: 1.6.0
• federation: 1.6.0
• files: 1.11.0
• files_external: 1.7.0
• files_frommail: 0.2.0
• files_markdown: 2.0.6
• files_pdfviewer: 1.5.0
• files_sharing: 1.8.0
• files_texteditor: 2.8.0
• files_trashbin: 1.6.0
• files_videoplayer: 1.5.0
• firstrunwizard: 2.5.0
• gallery: 18.3.0
• lookup_server_connector: 1.4.0
• mail: 0.15.1
• metadata: 0.9.0
• news: 13.1.6
• notes: 3.0.0
• notifications: 2.4.1
• oauth2: 1.4.2
• password_policy: 1.6.0
• previewgenerator: 2.1.0
• privacy: 1.0.0
• provisioning_api: 1.6.0
• recommendations: 0.4.0
• support: 1.0.0
• survey_client: 1.4.0
• theming: 1.7.0
• twofactor_backupcodes: 1.5.0
• twofactor_totp: 2.1.2
• updatenotification: 1.6.0
• viewer: 1.0.0
• workflowengine: 1.6.0
  Disabled:
• admin_audit
• audioplayer
• bruteforcesettings
• comments
• dashboard
• external
• files_retention
• files_versions
• logreader
• nextcloud_announcements
• serverinfo
• sharebymail
• spreed
• systemtags
• tasks
• user_ldap
• user_usage_report

Configuration (config/config.php)

{
"instanceid": "REMOVED SENSITIVE VALUE",
"passwordsalt": "REMOVED SENSITIVE VALUE",
"secret": "REMOVED SENSITIVE VALUE",
"trusted_domains": [
"REMOVED SENSITIVE VALUE",
"ssl-account.com"
],
"datadirectory": "REMOVED SENSITIVE VALUE",
"dbtype": "mysql",
"version": "16.0.1.1",
"dbname": "REMOVED SENSITIVE VALUE",
"dbhost": "REMOVED SENSITIVE VALUE",
"dbtableprefix": "oc_",
"dbuser": "REMOVED SENSITIVE VALUE",
"dbpassword": "REMOVED SENSITIVE VALUE",
"installed": true,
"forcessl": true,
"theme": "",
"maintenance": false,
"log_type": "syslog",
"logfile": "/www/htdocs/w00e5b01/nextcloud.log",
"loglevel": "2",
"logdateformat": "F d, Y H:i:s",
"trashbin_retention_obligation": "auto",
"mail_smtpmode": "smtp",
"mail_smtpsecure": "tls",
"mail_domain": "REMOVED SENSITIVE VALUE",
"mail_from_address": "REMOVED SENSITIVE VALUE",
"updater.release.channel": "stable",
"overwrite.cli.url": "REMOVED SENSITIVE VALUE",
"mail_smtpauthtype": "LOGIN",
"app_install_overwrite": [
"mail"
],
"mysql.utf8mb4": true
}

External storages: yes

External storage configuration

No mounts configured

Encryption: yes

User-backends:

OC\User\Database

Browser: Firefox 67.0

[kesselb]

Looks like a duplicate of #15058

I can't change the config for MySQL

Why do you enable utf8mb4 then?

The administration and the News plugin stop telling me to change collation.

The message is a warning shown for administrators. If it's not possible for you to enable utf8mb4 ignore it and look for another hosting in the long run (or ask your provider to fix it). Did you ask all-inkl (the usually suffix their php version with nmmX) to enable utf8mb4?

@nextcloud/server-triage @jancborchardt mind to have a look at this kind of issues? The technical point is clear but many people are running into the same problem. They see some kind of message and follow the documentation to fix it. The fact that some messages are more or less important is not clear.

[skjnldsv]

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

I'm guessing this is an issue with the innodb_large_prefix=true config.
Have you restarted mysql after doing the [mysqld] config change?

[kaffeeringe]

@skjnldsv: I can't change the config or restart the server.

[kaffeeringe]

@kesselb: Because Nextcloud recommends it.

[skjnldsv]

When you say you can't, this is because the server is managed by someone else?
Like your provider doesn't allow db config change?

[jancborchardt]

mind to have a look at this kind of issues? The technical point is clear but many people are running into the same problem. They see some kind of message and follow the documentation to fix it. The fact that some messages are more or less important is not clear.

Do you have a screenshot of how the error appears? We do have a priority of errors (red) and warnings (yellow) and notices (regular text color) in the admin overview, so that is a bit of an indicator of what is mroe and less important, no? So is it an issue with this message not being in the correct priority level?

[kesselb]

So is it an issue with this message not being in the correct priority level?

I'm not sure 🤔 Warning seems appropriate to me because some things does not work (like files or comments with emoji). Classified most of theses issues as duplicate of #15058. I suspect that people see the warning, head over to the documentation, skip the change server settings and restart server step (not possible because shared hosting), do the next steps and report an issue then. Don't know how people comes up with the idea that step 3+ work if they leave out step 1 or 2. We could add some warning to the documentation like "If you can't change your server settings don't enable utf8m4 ..."

[nickvergessen]

We could add some warning to the documentation like "If you can't change your server settings don't enable utf8m4 ..."

Most providers change this for you, if you ask for it.

So is it an issue with this message not being in the correct priority level?

I think notice is totally correct here, due to the reasons named by kesselb. If you accidently add an emoji in a calendar event or something your events will not sync anymore...

[jancborchardt]

I think notice is totally correct here, due to the reasons named by kesselb. If you accidently add an emoji in a calendar event or something your events will not sync anymore...

If using Emojis causes stuff to not sync then I would class it as warning (or even error), not just as a notice. As adding emoji somewhere is not something people do accidentally, or rarely.

[nickvergessen]

Exactly, warning is yellow (which it is). notices are "black" (normal) text.