Server: Upgrade to v13: Doctrine\DBAL\Exception\DriverException

[codemusings]

Steps to reproduce

1. Used web updater to update to 12.0.4
2. Got an immediate screen to update to v13
3. Update failed with an error

Expected behaviour

Update completes.

Actual behaviour

I get the following error:

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'CREATE TABLE oc_migrations (app VARCHAR(255) NOT NULL, version VARCHAR(255) NOT NULL, PRIMARY KEY(app, version)) 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

Server configuration

Operating system: Ubuntu 16.04.3

Web server: Apache 2.4.18

Database: MySQL 5.7

PHP version: 7.0

Nextcloud version: 12.0.3.3

Updated from an older Nextcloud/ownCloud or fresh install: Updated from an older Nextcloud version.

Where did you install Nextcloud from: Got the zip archive from the website.

Signing status:

Signing status

Login as admin user into your Nextcloud and access 
http://example.com/index.php/settings/integrity/failed 
paste the results here.

List of activated apps:

App list

Enabled:
  - activity: 2.5.2
  - bruteforcesettings: 1.0.2
  - calendar: 1.5.6
  - comments: 1.2.0
  - contacts: 2.0.1
  - dav: 1.3.0
  - federatedfilesharing: 1.2.0
  - federation: 1.2.0
  - files: 1.7.2
  - files_pdfviewer: 1.1.1
  - files_sharing: 1.4.0
  - files_texteditor: 2.4.1
  - files_trashbin: 1.2.0
  - files_versions: 1.5.0
  - files_videoplayer: 1.1.0
  - firstrunwizard: 2.1
  - gallery: 17.0.0
  - logreader: 2.0.0
  - lookup_server_connector: 1.0.0
  - nextcloud_announcements: 1.1
  - notes: 2.3.1
  - notifications: 2.0.0
  - oauth2: 1.0.5
  - password_policy: 1.2.2
  - provisioning_api: 1.2.0
  - serverinfo: 1.2.0
  - sharebymail: 1.2.0
  - survey_client: 1.0.0
  - systemtags: 1.2.0
  - tasks: 0.9.5
  - theming: 1.3.0
  - twofactor_backupcodes: 1.1.1
  - updatenotification: 1.2.0
  - workflowengine: 1.2.0
Disabled:
  - admin_audit
  - deck
  - encryption
  - files_external
  - keeweb
  - user_external
  - user_ldap

Nextcloud configuration:

Config report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "<redacted>"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "overwrite.cli.url": "<redacted>",
        "dbtype": "mysql",
        "version": "12.0.3.3",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "memcache.local": "\\OC\\Memcache\\APCu",
        "objectstore": {
            "class": "OC\\Files\\ObjectStore\\S3",
            "arguments": {
                "bucket": "<redacted>",
                "autocreate": true,
                "key": "<redacted>",
                "secret": "<redacted>",
                "hostname": "<redacted>",
                "port": 443,
                "use_ssl": true,
                "region": "<redacted>",
                "use_path_style": true
            }
        },
        "maintenance": false,
        "updater.secret": "***REMOVED SENSITIVE VALUE***",
        "theme": "",
        "loglevel": 2
    }
}

Are you using external storage, if yes which one: Yes. Amazon S3.

Are you using encryption: No.

Are you using an external user-backend, if yes which one: No.

By the way: the update screen for v13 is presented permanently without the need for authentication right now. I think in the case of a failure this is not a very good solution.

[MorrisJobke]

cc @nickvergessen

[MorrisJobke]

Maybe related to #8085 (fixed in #8121) and #7528 (fixed in #7699)

By the way: the update screen for v13 is presented permanently without the need for authentication right now. I think in the case of a failure this is not a very good solution.

Put your instance in maintenance mode and it will not show up.

[nickvergessen]

Hmm after checking the interwebs:

max key length is 767 bytes

This should be fixed by using long prefix: https://stackoverflow.com/a/22873006
That is exactly what we describe in: https://docs.nextcloud.com/server/13/admin_manual/configuration_database/mysql_4byte_support.html

Can you make sure, you have that configured correctly?
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix

Afterwards the limit should be 3072 instead of 767 bytes.

[nickvergessen]

I guess that is also why it worked for most people so far.

[codemusings]

Yeah I remembered that too and was convinced I did that during the initial setup. Apparently not. Should have double checked. It works as intended now. Thank you very much for the quick response.

As a side note: I noticed that the occ maintenance commands for this particular configuration step doesn't include the php command and at least on my installation occ is not marked as executable by default. This could be confusing to newcomers. Can I submit a pull request for that?

[ItalyPaleAle]

I am having the same issue. Sadly, the solution you're suggesting (setting innodb_large_prefix on) isn't viable because my MariaDB server has innodb_file_per_table off (see: https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_large_prefix ). Now i'm stuck with a broken Nextcloud. Any suggestion?

[ItalyPaleAle]

Ok, I think I found a solution.

First, change mysql.utf8mb4 to false in the config.php file. Then run: sudo -u www-data occ maintenance:repair. This will disable emoji file names, but that's ok; changing my MariaDB server would have been harder.

After this, occ upgrade did the job.

[bcutter]

Same for me on update from NC v12.0.5 to NC v12.0.6. Changed to UTF8mb4 manually because of a hint in the news app. Seems like the update scripts don´t respect the config.php setting "mysql.utf8mb4 = true" and therefore fire the wrong SQL commands.

Anyway, @EgoAleSum´s solution (https://github.com/nextcloud/server/issues/8202#issuecomment-364702520) worked perfectly and saved me a lot of time.

But: after the succesful update I would need to convert my UTF8 database back to UTF8mb4 using https://docs.nextcloud.com/server/11/admin_manual/maintenance/mysql_4byte_support.html#enabling-mysql-4-byte-support information again. Finger´s crossed next update (to NC v13.0.1) will run smoother on this (when will UTF8mb4 be supported as standard?).

[nickvergessen]

UFT8mb4 is the default for new installations, when the database is configured correctly.
Since it requires changes on my.ini we can't do this on update.
But you found the manual already how to migrate to it. so do it and be happy from now on. :smile_cat:

[bcutter]

I will check the my.ini once again, not sure if I set everything correct (initially the news app redirected to a superuser.com thread).

[ItalyPaleAle]

UFT8mb4 is the default for new installations, when the database is configured correctly.

Please note that not everyone can use this. As I wrote above, as an example, my server has innodb_file_per_table off, so I can't enable innodb_large_prefix.

[bcutter]

_Update:_
As SHOW VARIABLES LIKE "innodb_file_format"; still showed "Antilope", I checked my.cnf once again and yes, my mistake - there were two lines (innodb_file_format=barracuda & innodb_file_per_table=1) missing. After changing, restarting mysql server, changing config.php value and converting nextcloud database back to utf8mb4 using OCC everything seems to work now and InnoDB File-Format is "Barracuda" now. So I´m confident that the next nextcloud update will run smooth regarding database actions.