Server: Update to NC16, database update failed <Solved>

Created on 17 May 2019 · 6Comments · Source: nextcloud/server

I just tried to update from NC15.0.7 to 16 and when I tried to use the web updater, all was well until the attempt was made to update the database. The error message on the bottom said to notify NC, so I am.

The update was unsuccessful. Please report this issue to the Nextcloud community.

This is the error message received when using the web updater:

DoctrineDBALExceptionDriverException: An exception occurred while executing 'CREATE TABLE oc_login_flow_v2 (id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, timestamp BIGINT UNSIGNED NOT NULL, started SMALLINT UNSIGNED DEFAULT 0 NOT NULL, poll_token VARCHAR(255) NOT NULL, login_token VARCHAR(255) NOT NULL, public_key TEXT NOT NULL, private_key TEXT NOT NULL, client_name VARCHAR(255) NOT NULL, login_name VARCHAR(255) DEFAULT NULL, server VARCHAR(255) DEFAULT NULL, app_password VARCHAR(1024) DEFAULT NULL, UNIQUE INDEX poll_token (poll_token), UNIQUE INDEX login_token (login_token), INDEX timestamp (timestamp), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB ROW_FORMAT = compressed': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

I then tried the manual update method from the command line, and I got this message. It is essentially the same, but it has more information:
DoctrineDBALExceptionDriverException: An exception occurred while executing 'CREATE TABLE oc_login_flow_v2 (id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, timestamp BIGINT UNSIGNED NOT NULL, started SMALLINT UNSIGNED DEFAULT 0 NOT NULL, poll_token VARCHAR(255) NOT NULL, login_token VARCHAR(255) NOT NULL, public_key TEXT NOT NULL, private_key TEXT NOT NULL, client_name VARCHAR(255) NOT NULL, login_name VARCHAR(255) DEFAULT NULL, server VARCHAR(255) DEFAULT NULL, app_password VARCHAR(1024) DEFAULT NULL, UNIQUE INDEX poll_token (poll_token), UNIQUE INDEX login_token (login_token), INDEX timestamp (timestamp), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB ROW_FORMAT = compressed':

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

Anyone else seen this? Any ideas for a fix?

Server configuration

Operating system:
Devuan Ascii, debian 9 without systemd

Web server:
Apache

Database:
mariadb

PHP version:
PHP Version 7.2.18-1+0~20190503103213.21+stretch~1.gbp101320

Nextcloud version: (see Nextcloud admin page)
NC16

Updated from an older Nextcloud/ownCloud or fresh install:
updated from NC15.0.7

```
Enabled:

accessibility: 1.1.0
activity: 2.8.2
cloud_federation_api: 0.1.0
comments: 1.5.0
dav: 1.8.1
federatedfilesharing: 1.5.0
federation: 1.5.0
files: 1.10.0
files_pdfviewer: 1.4.0
files_sharing: 1.7.0
files_texteditor: 2.7.0
files_trashbin: 1.5.0
files_versions: 1.8.0
files_videoplayer: 1.4.0
firstrunwizard: 2.4.0
gallery: 18.2.0
logreader: 2.0.0
lookup_server_connector: 1.3.0
nextcloud_announcements: 1.4.0
notifications: 2.3.0
oauth2: 1.3.0
password_policy: 1.5.0
provisioning_api: 1.5.0
serverinfo: 1.5.0
sharebymail: 1.5.0
support: 1.0.0
survey_client: 1.3.0
systemtags: 1.5.0
twofactor_backupcodes: 1.4.1
updatenotification: 1.5.0
workflowengine: 1.5.0
Disabled:
admin_audit
encryption
files_external
theming
user_ldap

"system": {
    "instanceid": "***REMOVED SENSITIVE VALUE***",
    "passwordsalt": "***REMOVED SENSITIVE VALUE***",
    "secret": "***REMOVED SENSITIVE VALUE***",
    "trusted_domains": [
        "nextcloud.<domain>.org"
    ],
    "datadirectory": "***REMOVED SENSITIVE VALUE***",
    "overwrite.cli.url": "https:\/\/nextcloud.<domain>.org",
    "dbtype": "mysql",
    "version": "15.0.7.0",
    "dbname": "***REMOVED SENSITIVE VALUE***",
    "dbhost": "***REMOVED SENSITIVE VALUE***",
    "dbport": "",
    "dbtableprefix": "oc_",
    "dbuser": "***REMOVED SENSITIVE VALUE***",
    "dbpassword": "***REMOVED SENSITIVE VALUE***",
    "installed": true,
    "loglevel": 2,
    "memcache.local": "\\OC\\Memcache\\APCu",
    "maintenance": false,
    "theme": "",
    "mail_from_address": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpmode": "smtp",
    "mail_domain": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpauth": 1,
    "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpport": "587",
    "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
    "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
    "updater.release.channel": "production",
    "updater.secret": "***REMOVED SENSITIVE VALUE***"

Client configuration

Browser:
Waterfox

Operating system:
Linux

0. Needs triage bug

Source

cafn

All 6 comments

+1 for the report. Config Debian9,

Mise à jour du schéma de la base de données

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'CREATE TABLE oc_login_flow_v2 (id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, timestamp BIGINT UNSIGNED NOT NULL, started SMALLINT UNSIGNED DEFAULT 0 NOT NULL, poll_token VARCHAR(255) NOT NULL, login_token VARCHAR(255) NOT NULL, public_key TEXT NOT NULL, private_key TEXT NOT NULL, client_name VARCHAR(255) NOT NULL, login_name VARCHAR(255) DEFAULT NULL, server VARCHAR(255) DEFAULT NULL, app_password VARCHAR(1024) DEFAULT NULL, UNIQUE INDEX poll_token (poll_token), UNIQUE INDEX login_token (login_token), INDEX timestamp (timestamp), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB ROW_FORMAT = compressed': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Same done with occ update :

Set log level to debug
Turned on maintenance mode
Updating database schema
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'CREATE TABLE oc_login_flow_v2 (id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, timestamp BIGINT UNSIGNED NOT NULL, started SMALLINT UNSIGNED DEFAULT 0 NOT NULL, poll_token VARCHAR(255) NOT NULL, login_token VARCHAR(255) NOT NULL, public_key TEXT NOT NULL, private_key TEXT NOT NULL, client_name VARCHAR(255) NOT NULL, login_name VARCHAR(255) DEFAULT NULL, server VARCHAR(255) DEFAULT NULL, app_password VARCHAR(1024) DEFAULT NULL, UNIQUE INDEX poll_token (poll_token), UNIQUE INDEX login_token (login_token), INDEX timestamp (timestamp), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB ROW_FORMAT = compressed':

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
Update failed
Maintenance mode is kept active
Reset log level

ewidance on 17 May 2019

It seems to be associated with 4 bytes utf8. I've modfied the nextcloud config with 👍

sudo -u www-data php occ config:system:set mysql.utf8mb4 --type boolean --value="false"
Retried the update and it works.

ewidance on 17 May 2019

👍1

sudo -u www-data php occ config:system:set mysql.utf8mb4 --type boolean --value="false"

+1 Confirmed, this works enable the install to continue.

However, something is still not right. After updating, I get this message on the admin/settings page:

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.

Plus, the above command is the opposite from which is stated on the official docs page:
See #4 at https://docs.nextcloud.com/server/16/admin_manual/configuration_database/mysql_4byte_support.html

Which states this as being the default setting:

sudo -u www-data php occ config:system:set mysql.utf8mb4 --type boolean --value="true"

I would like to fix it properly and move beyond this bug.

cafn on 17 May 2019

UPDATE:
I redid the steps to enable the 4byte support as shown in the link above. The last step to repair the database took a few minutes, and I have a small database with just a couple of users. I may have overlooked the last step to repair the database when it was first implemented.

So far all is working well. I will know if it is fixed correctly at the next update.

cafn on 17 May 2019

Is it ok for you to close this issue as Duplicate of https://github.com/nextcloud/server/issues/15058?