Update to NC16, database update failed <Solved>

[cafn]

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:

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

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

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+020190503103213.21+stretch1.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

[ewidance]

+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]

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.

[cafn]

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]

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.

[kesselb]

Is it ok for you to close this issue as Duplicate of #15058?

[cafn]

Sure is. Will mark it as closed. Thanks for the notice.