MySQL: 1071 Specified key was too long; max key length is 3072 bytes

gschbro commented 1 year ago

⚠️ This issue respects the following points: ⚠️

[X] This is a bug, not a question or a configuration/webserver/proxy issue.
[X] This issue is not already reported on Github (I've searched it).
[X] I agree to follow Nextcloud's Code of Conduct.

What went wrong, what did you observe?

When running the command occ maintenance:repair I'm getting the error:

Polls - Create indices and foreign key constraints
- ERROR: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes

After deinstalling polls, delete tables and reinstall pools, the same issue occurs.

What did you expect, how polls should behave instead?

Database will be rebuild

What steps does it need to replay this bug?

sudo -u www-data php /var/www/nextcloud/occ maintenance:repair

Installation method

Installed/updated from the appstore (Apps section of your site)

Installation type

Updated from a minor version within same major version (i.e. 4.0.0 to 4.1.1)

Affected polls version

4.1.2

Which browser did you use, when experiencing the bug?

[ ] Firefox
[X] Chrome
[X] Chromium/Chromium based (i.e. Edge)
[ ] Safari
[ ] Other/Don't know

Other browser

No response

Which System did you use, when experiencing the bug?

[X] Windows
[ ] Linux
[ ] MacOs
[ ] iOS
[ ] Android
[ ] Other/Don't know

Other OS

No response

Add your browser log here

Additional client environment information

1

NC version

Nextcloud 25 (Nextcloud Hub 3)

Other Nextcloud version

No response

PHP engine version

PHP 7.3

Other PHP version

No response

Database engine

MySQL

Database Engine version or other Database

No response

Which user-backends are you using?

[X] Default user-backend (database)
[X] LDAP/ Active Directory
[ ] SSO - SAML
[ ] Other/Don't know

Add your nextcloud server log here

No response

Additional environment informations

No response

Configuration report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "172.29.252.34",
            "cloud.unis-computer.de",
            "cloud.schick.family",
            "172.29.253.10"
        ],
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "25.0.3.2",
        "overwrite.cli.url": "https:\/\/cloud.schick.family",
        "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",
        "ldapIgnoreNamingRules": false,
        "ldapProviderFactory": "OCA\\User_LDAP\\LDAPProviderFactory",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "25",
        "maintenance": false,
        "preview_max_x": 1024,
        "preview_max_y": 1024,
        "preview_max_scale_factor": 10,
        "enable_previews": true,
        "enabledPreviewProviders": [
            "OC\\Preview\\PNG",
            "OC\\Preview\\JPEG",
            "OC\\Preview\\GIF",
            "OC\\Preview\\BMP",
            "OC\\Preview\\XBitmap",
            "OC\\Preview\\MP3",
            "OC\\Preview\\TXT",
            "OC\\Preview\\MarkDown",
            "OC\\Preview\\Movie",
            "OC\\Preview\\Illustrator",
            "OC\\Preview\\OpenDocument",
            "OC\\Preview\\PDF",
            "OC\\Preview\\SVG",
            "OC\\Preview\\TIFF"
        ],
        "theme": "",
        "loglevel": 0,
        "updater.release.channel": "stable",
        "app_install_overwrite": [
            "githubmergetracker",
            "dicomviewer",
            "files_readmemd",
            "drawio"
        ],
        "filesystem_check_changes": 1,
        "default_phone_region": "DE",
        "allow_local_remote_servers": "1",
        "updater.secret": "***REMOVED SENSITIVE VALUE***"
    }
}

List of activated Apps

Enabled:
  - activity: 2.17.0
  - announcementcenter: 6.4.0
  - bruteforcesettings: 2.5.0
  - calendar: 4.2.3
  - circles: 25.0.0
  - cloud_federation_api: 1.8.0
  - comments: 1.15.0
  - contacts: 5.0.3
  - contactsinteraction: 1.6.0
  - dashboard: 7.5.0
  - dav: 1.24.0
  - dicomviewer: 1.2.4
  - drawio: 2.1.0
  - external: 5.0.0
  - federatedfilesharing: 1.15.0
  - federation: 1.15.0
  - files: 1.20.1
  - files_automatedtagging: 1.15.0
  - files_external: 1.17.0
  - files_fulltextsearch: 25.0.0
  - files_pdfviewer: 2.6.0
  - files_readmemd: 1.2.2
  - files_rightclick: 1.4.0
  - files_sharing: 1.17.0
  - files_trashbin: 1.15.0
  - files_versions: 1.18.0
  - firstrunwizard: 2.14.0
  - fulltextsearch: 25.0.0
  - fulltextsearch_elasticsearch: 25.0.0
  - integration_gitlab: 1.0.12
  - integration_openproject: 2.2.1
  - integration_zammad: 2.0.1
  - logreader: 2.10.0
  - lookup_server_connector: 1.13.0
  - nextcloud_announcements: 1.14.0
  - notifications: 2.13.1
  - oauth2: 1.13.0
  - password_policy: 1.15.0
  - phonetrack: 0.7.4
  - photos: 2.0.1
  - polls: 4.1.2
  - privacy: 1.9.0
  - provisioning_api: 1.15.0
  - quicknotes: 0.8.5
  - related_resources: 1.0.3
  - richdocuments: 7.1.0
  - richdocumentscode: 22.5.802
  - serverinfo: 1.15.0
  - settings: 1.7.0
  - sharebymail: 1.15.0
  - support: 1.8.0
  - survey_client: 1.13.0
  - systemtags: 1.15.0
  - text: 3.6.0
  - theming: 2.0.1
  - twofactor_backupcodes: 1.14.0
  - twofactor_totp: 7.0.0
  - updatenotification: 1.15.0
  - user_ldap: 1.15.0
  - user_status: 1.5.0
  - viewer: 1.9.0
  - weather_status: 1.5.0
  - workflowengine: 2.7.0
Disabled:
  - admin_audit
  - encryption
  - files_markdown: 2.3.6
  - previewgenerator: 5.1.1
  - recommendations: 0.4.0
  - spreed: 15.0.3
  - suspicious_login

Nextcloud Signing status

No errors have been found.

Additional Information

No response

dartcafe commented 1 year ago

Sorry, but:

PHP 7.3 can't be right, it is not supported since version 3.5 nor is it supported by NC25
You skipped the database version of MySQL
No server log provided, so I can only guess which index causes the error.

It seems you run into a problem caused by a complex character set of your db engine. Last but not least, I can't reproduce this error.

Feel free to reopen the issue or open another one and add some more informations to get me a chance to search for hints.

dartcafe commented 1 year ago

I leave that issue open, to give you the opportunity to add the information, since I saw this is your first issue.

sr-oct commented 1 year ago

Had the same issue.

MySQL 8.0
PHP 8.1
Linux

Changed MySQL (utf8mb4)

oc_polls_options.poll_option_text
oc_polls_votes.vote_option_text

Reproduce

php occ polls:db:rebuild

All polls tables will get checked against the current schema.
NO data migration will be executed, so make sure you have a backup of your database.

Continue with the conversion (y/n)? [n] y
Remove foreign key constraints and generic indices
 Remove FK_8843EB9B3C947C0F from polls_comments
 Remove FK_3710B4A43C947C0F from polls_log
[....]
 Added unique index UNIQ_preferences to polls_preferences
 Added unique index UNIQ_watch to polls_watch

In ExceptionConverter.php line 114:
  An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes

In Exception.php line 26:
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes

In Connection.php line 82:
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes

Really Worse Fix

ALTER TABLE `oc_polls_options` CHANGE `poll_option_text` `poll_option_text` VARCHAR(256)  CHARACTER SET utf8mb4  BINARY  NULL  DEFAULT '';
ALTER TABLE `oc_polls_votes` CHANGE `vote_option_text` `vote_option_text` VARCHAR(256)  CHARACTER SET utf8mb4  BINARY  NULL  DEFAULT '';

php occ polls:db:rebuild

Result:maintenance:repair is OK, for now

gschbro commented 1 year ago

Hi sr-oct,

thank you, this has fixed my issue.

dartcafe you are right. My php version is 7.4.11

Thank you guys for the quick response and the help

dartcafe commented 1 year ago

@sr-oct Thanks for the workaround.

Why did you explicitly had to set to utf8mb4?
Which character set is the default for your installation, as utf8mb4 is the recommended character set for NC?

sr-oct commented 1 year ago

@sr-oct Thanks for the workaround.
* Why did you explicitly had to set to utf8mb4?
Default on our NCs

Which character set is the default for your installation, as utf8mb4 is the recommended character set for NC? Sure, MySQL needs to support fancy emojis so users don't break their calendars with party invitations 🎉, for example

dartcafe commented 1 year ago

yes, sure. I just was wondering, if you changed the column to utf8mb4 explicitly from different default charater set. But I guess you only changed the column length to 256 characters and the character set setting was misleading.

Does MySQL have other limitations to the key length as MariaDB, since I do not experience this problem. But all our instances are run with MariaDB, not MySQL.

sr-oct commented 1 year ago

yes, sure. I just was wondering, if you changed the column to utf8mb4 explicitly from different default charater set. But I guess you only changed the column length to 256 characters and the character set setting was misleading.

I haven't tested from which point on the char length it breaks. It's a "Really Worse Fix" to give you a hint.

Does MySQL have other limitations to the key length as MariaDB, since I do not experience this problem. But all our instances are run with MariaDB, not MySQL.

Exactly this implies the behavior of MySQL after the update. Maybe you should also plan tests with MySQL.

dartcafe commented 1 year ago

I reopened this, because I can approve, that the bug at least relates to MySQL 8.0. see https://github.com/nextcloud/polls/actions/runs/4256533722/jobs/7405539469#logs

We have to think over another solution for that unique index, because the length of the fields poll_option_text and vote_option_text is too long for the allowed MySQL index length.

The problem was introduced with #2714

dartcafe commented 1 year ago

see #2777

dartcafe commented 1 year ago

4.1.4 will be out in minutes. Happy about any response or feedback.

marc-leinweber commented 1 year ago

4.1.4 will be out in minutes. Happy about any response or feedback.

Tried 4.1.5, have a syntax error in Rebuild Command (trailing comma in constructor parameter list):

ParseError: syntax error, unexpected ')', expecting variable (T_VARIABLE) in /var/www/nextcloud/apps/polls/lib/Command/Db/Rebuild.php:46
Stack trace:
#0 /var/www/nextcloud/lib/composer/composer/ClassLoader.php(428): Composer\Autoload\includeFile()
#1 [internal function]: Composer\Autoload\ClassLoader->loadClass()
#2 [internal function]: spl_autoload_call()
#3 /var/www/nextcloud/lib/private/AppFramework/Utility/SimpleContainer.php(114): ReflectionClass->__construct()
#4 /var/www/nextcloud/lib/private/AppFramework/Utility/SimpleContainer.php(133): OC\AppFramework\Utility\SimpleContainer->resolve()
#5 /var/www/nextcloud/lib/private/AppFramework/DependencyInjection/DIContainer.php(465): OC\AppFramework\Utility\SimpleContainer->query()
#6 /var/www/nextcloud/lib/private/ServerContainer.php(145): OC\AppFramework\DependencyInjection\DIContainer->queryNoFallback()
#7 /var/www/nextcloud/lib/private/Console/Application.php(219): OC\ServerContainer->query()
#8 /var/www/nextcloud/lib/private/Console/Application.php(130): OC\Console\Application->loadCommandsFromInfoXml()
#9 /var/www/nextcloud/console.php(99): OC\Console\Application->loadCommands()
#10 /var/www/nextcloud/occ(11): require_once('/var/www/nextcl...')
#11 {main}

After fixing the error, the rebuild command works without any issues. But: when creating a test poll, the GUI displays an error. My error log, however, remains empty (maybe I have some misconfiguration in logging, I'm looking into this).

Edit: Browser console tells me that the request failed with an HTTP 500 which I cannot find in my logs (my php-fpm knowledge is somewhat rusty but logging should be configured correctly because a lost DB connection during MariaDB update was logged both in nginx in fpm logs...)

dartcafe commented 1 year ago

Strange. The syntax is invalid for PHP7.4, but the checks ran through. I guess we will get an .6 today. Sorry!

dartcafe commented 1 year ago

Edit: Browser console tells me that the request failed with an HTTP 500 which I cannot find in my logs (my php-fpm knowledge is somewhat rusty but logging should be configured correctly because a lost DB connection during MariaDB update was logged both in nginx in fpm logs...)

Hmmm. A 500 should be reported in the nextcloud log.

marc-leinweber commented 1 year ago

Edit: Browser console tells me that the request failed with an HTTP 500 which I cannot find in my logs (my php-fpm knowledge is somewhat rusty but logging should be configured correctly because a lost DB connection during MariaDB update was logged both in nginx in fpm logs...)

Hmmm. A 500 should be reported in the nextcloud log.

I checked logging and it works as intended. If I add syntax errors, they are reported correctly. Error reporting is set to E_ALL & ~E_DEPRECATED & ~E_STRICT; changing it to E_ALL did not change anything. The only appearance of the 500 is nginx' access log (which is disabled on all my setups). However, after reading your hint again I realized that Nextcloud is intercepting error logging. A quick glance and I got:


OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1364 Field 'description' doesn't have a default value at <<closure>>

 0. /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php line 329
    OC\DB\Exceptions\DbalException::wrap()
 1. /var/www/nextcloud/lib/public/AppFramework/Db/QBMapper.php line 139
    OC\DB\QueryBuilder\QueryBuilder->executeStatement()
 2. /var/www/nextcloud/apps/polls/lib/Service/PollService.php line 259
    OCP\AppFramework\Db\QBMapper->insert()
 3. /var/www/nextcloud/apps/polls/lib/Controller/PollController.php line 98
    OCA\Polls\Service\PollService->add()
 4. /var/www/nextcloud/apps/polls/lib/Controller/BaseController.php line 91
    OCA\Polls\Controller\PollController->OCA\Polls\Controller\{closure}("*** sensitive parameters replaced ***")
 5. /var/www/nextcloud/apps/polls/lib/Controller/PollController.php line 98
    OCA\Polls\Controller\BaseController->responseCreate()
 6. /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 225
    OCA\Polls\Controller\PollController->add()
 7. /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 133
    OC\AppFramework\Http\Dispatcher->executeController()
 8. /var/www/nextcloud/lib/private/AppFramework/App.php line 172
    OC\AppFramework\Http\Dispatcher->dispatch()
 9. /var/www/nextcloud/lib/private/Route/Router.php line 298
    OC\AppFramework\App::main()
10. /var/www/nextcloud/lib/base.php line 1047
    OC\Route\Router->match()
11. /var/www/nextcloud/index.php line 37
    OC::handleRequest()

POST /apps/polls/poll/add

dartcafe commented 1 year ago

Strange. The default is defined as ''. You could fix that with setting '' as default for that column. But I will investigate that.

github-actions[bot] commented 4 months ago

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.

nextcloud / polls