create index fails with key was too long (1.6.3 -> 1.7.4)

[tessus]

What is going wrong?

Upgrading from 1.6.3 to 1.7.4 gives the following error:

An error occured during the request. Unable to proceed.
An exception occurred while executing 'CREATE UNIQUE INDEX UNIQ_options ON oc_polls_options (poll_id, poll_option_text, timestamp)': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

I also had to deactivate the app, because it requires occ upgrade which fails because of above error.

To Reproduce Steps to reproduce the behavior:

1. Nextcloud Apps section
2. Click on Update to 1.7.4

Expected behavior No error and successful update

Screenshots If applicable, add screenshots to help explain your problem.

Information about your polls installation

Polls version? 1.7.4

Fresh installation or update from a prior version (from which one)? 1.6.3

How did you install this version?(Appstore or describe installation) Appstore

Information about your Instance of Nextcloud

Nextcloud version: 19.0.8

List of activated apps:

Enabled:
 - accessibility: 1.5.0
 - activity: 2.12.1
 - admin_audit: 1.9.0
 - announcementcenter: 3.8.1
 - apporder: 0.11.0
 - bruteforcesettings: 2.0.1
 - calendar: 2.0.4
 - cloud_federation_api: 1.2.0
 - comments: 1.9.0
 - contacts: 3.4.3
 - contactsinteraction: 1.0.0
 - dav: 1.15.0
 - federatedfilesharing: 1.9.0
 - federation: 1.9.0
 - files: 1.14.0
 - files_external: 1.10.0
 - files_pdfviewer: 1.8.0
 - files_rightclick: 0.16.0
 - files_sharing: 1.11.0
 - files_trashbin: 1.9.0
 - files_versions: 1.12.0
 - files_videoplayer: 1.8.0
 - firstrunwizard: 2.8.0
 - issuetemplate: 0.7.0
 - logreader: 2.4.0
 - lookup_server_connector: 1.7.0
 - nextcloud_announcements: 1.8.0
 - notes: 3.6.4
 - notifications: 2.7.0
 - oauth2: 1.7.0
 - password_policy: 1.9.1
 - phonetrack: 0.6.4
 - photos: 1.1.0
 - privacy: 1.3.0
 - provisioning_api: 1.9.0
 - serverinfo: 1.9.0
 - settings: 1.1.0
 - sharebymail: 1.9.0
 - systemtags: 1.9.0
 - tasks: 0.13.6
 - text: 3.0.1
 - theming: 1.10.0
 - twofactor_backupcodes: 1.8.0
 - twofactor_totp: 5.0.0
 - twofactor_u2f: 6.0.0
 - updatenotification: 1.9.0
 - viewer: 1.3.0
 - workflowengine: 2.1.0
Disabled:
 - activitylog
 - bookmarks
 - encryption
 - files_markdown
 - files_readmemd
 - polls
 - recommendations
 - support
 - survey_client
 - user_ldap

Nextcloud configuration:

{
    "instanceid": "***REMOVED SENSITIVE VALUE***",
    "passwordsalt": "***REMOVED SENSITIVE VALUE***",
    "datadirectory": "***REMOVED SENSITIVE VALUE***",
    "dbtype": "mysql",
    "version": "19.0.8.1",
    "installed": true,
    "forcessl": true,
    "loglevel": 2,
    "maintenance": false,
    "trusted_domains": [
        "removed"
    ],
    "share_folder": "\/Shared",
    "dbname": "***REMOVED SENSITIVE VALUE***",
    "dbhost": "***REMOVED SENSITIVE VALUE***",
    "dbuser": "***REMOVED SENSITIVE VALUE***",
    "dbpassword": "***REMOVED SENSITIVE VALUE***",
    "logdateformat": "Y-m-d H:i:s O",
    "logtimezone": "Europe\/Vienna",
    "secret": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpmode": "sendmail",
    "mail_from_address": "***REMOVED SENSITIVE VALUE***",
    "mail_domain": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpsecure": "ssl",
    "memcache.local": "\\OC\\Memcache\\APCu",
    "memcache.locking": "\\OC\\Memcache\\Redis",
    "filelocking.enabled": true,
    "redis": {
        "host": "***REMOVED SENSITIVE VALUE***",
        "port": 0,
        "timeout": 0
    },
    "appstore.experimental.enabled": true,
    "trashbin_retention_obligation": "auto",
    "updater.release.channel": "stable",
    "htaccess.RewriteBase": "\/",
    "overwrite.cli.url": "removed",
    "auth.bruteforce.protection.enabled": false,
    "simpleSignUpLink.shown": false,
    "upgrade.disable-web": true,
    "theme": "evermeet",
    "mail_smtpauthtype": "PLAIN",
    "mail_smtpauth": 1,
    "mail_sendmailmode": "smtp",
    "filesystem_check_changes": 0
}

Server configuration

Operating system: Linux

Webserver: Apache/2.4.46 (Unix) (fpm-fcgi)

Database: mysql 5.6.25

PHP version: 7.3.26

Nextcloud version: 19.0.8 - 19.0.8.1

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

Client configuration

Irrelevant

Logs

Nextcloud log (data/nextcloud.log)

[core] Error: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'CREATE UNIQUE INDEX UNIQ_options ON oc_polls_options (poll_id, poll_option_text, timestamp)':

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at <<closure>>

 0. /data/projects/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 169
    Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exception oc ... s", Doctrine\DBAL\Dr ... ]})
 1. /data/projects/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 145
    Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... s")
 2. /data/projects/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 1012
    Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "CREATE UNIQUE I ... )")
 3. /data/projects/nextcloud/lib/private/DB/Migrator.php line 262
    Doctrine\DBAL\Connection->query("CREATE UNIQUE I ... )")
 4. /data/projects/nextcloud/lib/private/DB/Migrator.php line 85
    OC\DB\Migrator->applySchema(Doctrine\DBAL\Schema\Schema {})
 5. /data/projects/nextcloud/lib/private/DB/Connection.php line 451
    OC\DB\Migrator->migrate(Doctrine\DBAL\Schema\Schema {})
 6. /data/projects/nextcloud/lib/private/DB/MigrationService.php line 486
    OC\DB\Connection->migrateToSchema(Doctrine\DBAL\Schema\Schema {})
 7. /data/projects/nextcloud/lib/private/DB/MigrationService.php line 414
    OC\DB\MigrationService->executeStep("0107Date20201210204702", false)
 8. /data/projects/nextcloud/lib/private/legacy/OC_App.php line 910
    OC\DB\MigrationService->migrate()
 9. /data/projects/nextcloud/lib/private/Updater.php line 375
    OC_App::updateApp("*** sensitive parameters replaced ***")
10. /data/projects/nextcloud/lib/private/Updater.php line 254
    OC\Updater->doAppUpgrade()
11. /data/projects/nextcloud/lib/private/Updater.php line 130
    OC\Updater->doUpgrade("19.0.8.1", "19.0.8.1")
12. /data/projects/nextcloud/core/Command/Upgrade.php line 255
    OC\Updater->upgrade()
13. /data/projects/nextcloud/3rdparty/symfony/console/Command/Command.php line 255
    OC\Core\Command\Upgrade->execute(Symfony\Componen ... {}, Symfony\Componen ... {})
14. /data/projects/nextcloud/3rdparty/symfony/console/Application.php line 1012
    Symfony\Component\Console\Command\Command->run(Symfony\Componen ... {}, Symfony\Componen ... {})
15. /data/projects/nextcloud/3rdparty/symfony/console/Application.php line 272
    Symfony\Component\Console\Application->doRunCommand(OC\Core\Command\ ... }}, Symfony\Componen ... {}, Symfony\Componen ... {})
16. /data/projects/nextcloud/3rdparty/symfony/console/Application.php line 148
    Symfony\Component\Console\Application->doRun(Symfony\Componen ... {}, Symfony\Componen ... {})
17. /data/projects/nextcloud/lib/private/Console/Application.php line 215
    Symfony\Component\Console\Application->run(Symfony\Componen ... {}, Symfony\Componen ... {})
18. /data/projects/nextcloud/console.php line 100
    OC\Console\Application->run()
19. /data/projects/nextcloud/occ line 11
    require_once("/data/projects/nextcloud/console.php")

at 2021-02-01T01:19:15+01:00

Browser log

no error

[dartcafe]

See https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes

[dartcafe]

Another one: https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740

Suggested solution:

$ sudo mysql -u root -p
MariaDB [(none)]> use nextcloud;
MariaDB [nextcloud]> set global innodb_large_prefix=on;
MariaDB [nextcloud]> set global innodb_file_format=Barracuda;
$ sudo -u www-data php occ maintenance:repair

[ClCfe]

Hello

It has nothing to do with the error, but why the unique on (poll_id, poll_option_text, timestamp) and not only on (poll_id, poll_option_text)?? I don't the a Use Case where you would like to have several times the sames option in a poll?

[dartcafe]

It has nothing to do with the error,

Right. So it is better to open a new issue, so it can be tracked.

[tessus]

Thanks for the info. I will have to look into it this evening. Although I'm not very keen on setting a global var in mysql, unless they really only persist for the selected database. I have to look into this as well. I certainly do not want to set those 2 things for 20 other databases.

[dartcafe]

I certainly do not want to set those 2 things for 20 other databases.

Understandable. Unfortunately we rely on the unique index to avoid duplicates, and they need full column width.

I updated some instances with the new version without any problems. Seems that these admins have enabled large_prefix for some reason. BTW: Be aware, that NC21 drops support for MySQL <8

[ClCfe]

What about altering the poll_option_text column to a varchar(255) instead of a varchar(256)

[tessus]

Ok, so I read up on those parameters, but unfortunately some info is missing. They are global, thus this will change all my databases. But there's no information on whether my other databases will require additional maintenance or not. Can I just set these 2 vars and my other databases will still work?

Be aware, that NC21 drops support for MySQL <8

That's a problem, because my server has an uptime of more than 6 years, so you can imagine that no mysql8 packages exist for a 7 year old Linux distro. This is just great.

[dartcafe]

What about altering the poll_option_text column to a varchar(255) instead of a varchar(256)

Possibly a silly question, but I am not a database pro: What do we win besides 1 byte?

[ClCfe]

The column is in utf8 so you win 3 bytes, So no more error (255*3 + 1 byte for poll_id and 1 byte for timstamp = 767)

[tessus]

poll_id and timestamp are int, which means 4 bytes each.

[ClCfe]

Woops yes, but try it, it works for me (weird) At worst, remove timestamp from the multiple key and put a varchar(254)

[dartcafe]

Can I just set these 2 vars and my other databases will still work?

Something I can't predict. I don't know your databases. But you can find more information here about the how and why of this setting: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix

Be aware, that NC21 drops support for MySQL <8

That's a problem, because my server has an uptime of more than 6 years, so you can imagine that no mysql8 packages exist for a 7 year old Linux distro. This is just great.

Your MySQL version is 25 patch levels behind the latest release and and end of support for MySQL 5.6 is Feb 2021. So I would recommend to think about an update or maybe better switch to the current version of MariaDB.

About the discussion of field lengths: The option table will get another field which will be added to the index and since innodb_large_prefix is defaulted to ON since MySQL 5.7, I think we can rely on this setting.

Can we close this issue?

[beccon4]

I wanted to upgrade to version 1.7.5 and I got:

Es ist ein Fehler bei der Anfrage aufgetreten. Es kann nicht fortgefahren werden. An exception occurred while executing 'CREATE UNIQUE INDEX UNIQ_options ON oc_polls_options (poll_id, poll_option_text, timestamp)': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

and then when I tried to recover: (occ upgrade)

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'CREATE UNIQUE INDEX UNIQ_options ON oc_polls_options (poll_id, poll_option_text, timestamp)':

I had to disable polls to continue to work.

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

My instance failed

[ClCfe]

I wanted to upgrade to version 1.7.5 and I got:

Es ist ein Fehler bei der Anfrage aufgetreten. Es kann nicht fortgefahren werden. An exception occurred while executing 'CREATE UNIQUE INDEX UNIQ_options ON oc_polls_options (poll_id, poll_option_text, timestamp)': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

and then when I tried to recover: (occ upgrade)

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'CREATE UNIQUE INDEX UNIQ_options ON oc_polls_options (poll_id, poll_option_text, timestamp)':

I had to disable polls to continue to work.

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

My instance failed

do as I said, in oc_polls_options , alter the vote_option_text column to a varchar(255) instead of varchar(256) AND do the same in table oc_polls_votes with the column vote_option_text it s the quickest and safest fix

[dartcafe]

It is not a good idea to change system internals, because the app could rely on it's setup. Later changes may occur also. And as I said: the index will grow due to a new field in the next version. So this hack will become obsolete.

As innodb_large_prefix is a default since MySQL 5.7 and 5.6 runs out of support this month, it is a good advice to set this global to ON.

[dartcafe]

@beccon4 Read the comment above: https://github.com/nextcloud/polls/issues/1375#issuecomment-770616281

[ClCfe]

@beccon4 Read the comment above: #1375 (comment)

I ve already read comments (on other related issues / nextcloud forum as well) the problem is that you also have to change innodb_file_format = Barracuda changing this globally on a mysql server that has other databases than nextcloud is risky I think

Later changes may occur also. And as I said: the index will grow due to a new field in the next version. So this hack will become obsolete.

I ve already tested adding a field in the unique key, this won't trigger the mysql error Mysql max key length seems to apply on each field, not for the sum of their length

[tessus]

I think both arguments are valid.

At one point the dbms has to be upgraded to make it future proof. Changing the db structure as a quick hack seems the easy path forward, but might only result in trouble in the future.

The problem is that we still don't know what innodb_file_format = Barracuda means for the other dbs on the server. Do they require us to run a maintenance (mysql) admin script/command? There's no proper info in the docs, which is slightly concerning.

Either way, I think we can close this topic. Although I certainly would appreciate a reply as to what innodb_file_format = Barracuda really means and what the consequences are.

[dartcafe]

I could only speculate and google for answers, what effects are to expect, as you would do. But as far as I understood, the change does not affect existing tables. So it should be safe (of course with a backup). But don't quote me on this 😉

[github-actions[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.