`oc_accounts` CONVERT TO CHARACTER SET utf8mb4 Syntax error or access violation

Open neeks76 opened this issue 5 years ago • 1 comments

Steps to reproduce

Update to 10.5.0 by web brower.

Expected behaviour

Tell us what should happen They can't upgrade because of Syntax error or access violation:

Actual behaviour

Tell us what happens instead

Server configuration

Operating system: Debian 9

Web server: apache2

Database: myriadb

PHP version: 7.3

ownCloud version: (see ownCloud admin page) 10.4

Updated from an older ownCloud or fresh install:

Where did you install ownCloud from: https://linuxhostsupport.com/blog/how-to-install-owncloud-10-on-debian-9/

Signing status (ownCloud 9.0 and above):

Login as admin user into your ownCloud and access 
http://example.com/index.php/settings/integrity/failed 
paste the results into https://gist.github.com/ and puth the link here.

The content of config/config.php: root@hooni:/var/www/owncloud# cat config/config.php

false, 'instanceid' => 'ocbxh4pwg1sz', 'passwordsalt' => 'azRzuRRi7Wkv5f1F8Q2YnF15Qzm7ry', 'secret' => '7SvA+Nd4QvhixlkfTjftZQc/H0p5HvMfyK4uW2J5RXWxyLOQ', 'trusted_domains' => array ( 0 => '192.168.0.11', 1 => 'hooni.mooo.com', ), 'datadirectory' => '/media/owncloud/', 'overwrite.cli.url' => 'http://192.168.0.11/owncloud', 'dbtype' => 'mysql', 'version' => '10.4.1.3', 'dbname' => 'owncloud', 'dbhost' => 'localhost', 'dbtableprefix' => 'oc_', 'dbuser' => 'owncloud', 'dbpassword' => '*******', 'logtimezone' => 'UTC', 'apps_paths' => array ( 0 => array ( 'path' => '/var/www/owncloud/apps', 'url' => '/apps', 'writable' => false, ), 1 => array ( 'path' => '/var/www/owncloud/apps-external', 'url' => '/apps-external', 'writable' => true, ), ), 'installed' => true, 'files_external_allow_create_new_local' => true, 'logfile' => '/media/owncloud/ext_owncloud.log', 'loglevel' => 2, 'htaccess.RewriteBase' => '/', 'memcache.local' => '\\OC\\Memcache\\APCu', 'mysql.utf8mb4' => true, 'filelocking.enabled' => true, 'memcache.distributed' => '\\OC\\Memcache\\Redis', 'memcache.locking' => '\\OC\\Memcache\\Redis', 'redis' => array ( 'host' => 'localhost', 'port' => '6379', ), 'theme' => '', 'maintenance' => true, ); ``` Log in to the web-UI with an administrator account and click on 'admin' -> 'Generate Config Report' -> 'Download ownCloud config report' This report includes the config.php settings, the list of activated apps and other details in a well sanitized form. or If you have access to your command line run e.g.: sudo -u www-data php occ config:list system from within your ownCloud installation folder *ATTENTION:* Do not post your config.php file in public as is. Please use one of the above methods whenever possible. Both, the generated reports from the web-ui and from occ config:list consistently remove sensitive data. You still may want to review the report before sending. If done manually then it is critical for your own privacy to dilligently remove *all* host names, passwords, usernames, salts and other credentials before posting. You should assume that attackers find such information and will use them against your systems. ``` **List of activated apps:** ``` If you have access to your command line run e.g.: sudo -u www-data php occ app:list from within your ownCloud installation folder. ``` **Are you using external storage, if yes which one:** local/smb/sftp/... **Are you using encryption:** yes/no **Are you using an external user-backend, if yes which one:** LDAP/ActiveDirectory/Webdav/... #### LDAP configuration (delete this part if not used) ``` With access to your command line run e.g.: sudo -u www-data php occ ldap:show-config from within your ownCloud installation folder Without access to your command line download the data/owncloud.db to your local computer or access your SQL server remotely and run the select query: SELECT * FROM `oc_appconfig` WHERE `appid` = 'user_ldap'; Eventually replace sensitive data as the name/IP-address of your LDAP server or groups. ``` ### Client configuration **Browser:** **Operating system:** ### Logs root@hooni:/var/www/owncloud# sudo -u www-data ./occ upgrade ownCloud or one of the apps require upgrade - only a limited number of commands are available You may use your browser or the occ upgrade command to do the upgrade 2020-08-13T14:07:43+00:00 Set log level to debug 2020-08-13T14:07:43+00:00 Turned on maintenance mode 2020-08-13T14:07:43+00:00 Repair step: Upgrade app code from the marketplace 2020-08-13T14:07:43+00:00 Repair info: Using market to update existing apps 2020-08-13T14:07:43+00:00 Repair info: Attempting to update the following existing compatible apps from market: bplog, cms_pico, comments, configreport, dav, extract, federatedfilesharing, federation, files, files_clipboard, files_external, files_mediaviewer, files_pdfviewer, files_sharing, files_texteditor, files_trashbin, files_versions, firstrunwizard, guests, market, metadata, notifications, onlyoffice, provisioning_api, systemtags, templateeditor, updatenotification 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: bplog 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: cms_pico 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: comments 2020-08-13T14:07:43+00:00 Repair info: App (comments) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: configreport 2020-08-13T14:07:43+00:00 Repair info: App (configreport) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: dav 2020-08-13T14:07:43+00:00 Repair info: App (dav) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: extract 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: federatedfilesharing 2020-08-13T14:07:43+00:00 Repair info: App (federatedfilesharing) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: federation 2020-08-13T14:07:43+00:00 Repair info: App (federation) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: files 2020-08-13T14:07:43+00:00 Repair info: App (files) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: files_clipboard 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: files_external 2020-08-13T14:07:43+00:00 Repair info: App (files_external) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: files_mediaviewer 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: files_pdfviewer 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: files_sharing 2020-08-13T14:07:43+00:00 Repair info: App (files_sharing) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: files_texteditor 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: files_trashbin 2020-08-13T14:07:43+00:00 Repair info: App (files_trashbin) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: files_versions 2020-08-13T14:07:43+00:00 Repair info: App (files_versions) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: firstrunwizard 2020-08-13T14:07:43+00:00 Repair info: App (firstrunwizard) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: guests 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: market 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: metadata 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: notifications 2020-08-13T14:07:43+00:00 Repair info: App (notifications) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: onlyoffice 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: provisioning_api 2020-08-13T14:07:43+00:00 Repair info: App (provisioning_api) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: systemtags 2020-08-13T14:07:43+00:00 Repair info: App (systemtags) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: templateeditor 2020-08-13T14:07:43+00:00 Repair info: 2020-08-13T14:07:43+00:00 Repair info: Fetching app from market: updatenotification 2020-08-13T14:07:43+00:00 Repair info: App (updatenotification) is not known at the marketplace. 2020-08-13T14:07:43+00:00 Repair info: App was not updated: bplog 2020-08-13T14:07:43+00:00 Repair info: App was not updated: cms_pico 2020-08-13T14:07:43+00:00 Repair info: App was not updated: comments 2020-08-13T14:07:43+00:00 Repair info: App was not updated: configreport 2020-08-13T14:07:43+00:00 Repair info: App was not updated: dav 2020-08-13T14:07:43+00:00 Repair info: App was not updated: extract 2020-08-13T14:07:43+00:00 Repair info: App was not updated: federatedfilesharing 2020-08-13T14:07:43+00:00 Repair info: App was not updated: federation 2020-08-13T14:07:43+00:00 Repair info: App was not updated: files 2020-08-13T14:07:43+00:00 Repair info: App was not updated: files_clipboard 2020-08-13T14:07:43+00:00 Repair info: App was not updated: files_external 2020-08-13T14:07:43+00:00 Repair info: App was not updated: files_mediaviewer 2020-08-13T14:07:43+00:00 Repair info: App was not updated: files_pdfviewer 2020-08-13T14:07:43+00:00 Repair info: App was not updated: files_sharing 2020-08-13T14:07:43+00:00 Repair info: App was not updated: files_texteditor 2020-08-13T14:07:43+00:00 Repair info: App was not updated: files_trashbin 2020-08-13T14:07:43+00:00 Repair info: App was not updated: files_versions 2020-08-13T14:07:43+00:00 Repair info: App was not updated: firstrunwizard 2020-08-13T14:07:43+00:00 Repair info: App was not updated: guests 2020-08-13T14:07:43+00:00 Repair info: App was not updated: market 2020-08-13T14:07:43+00:00 Repair info: App was not updated: metadata 2020-08-13T14:07:43+00:00 Repair info: App was not updated: notifications 2020-08-13T14:07:43+00:00 Repair info: App was not updated: onlyoffice 2020-08-13T14:07:43+00:00 Repair info: App was not updated: provisioning_api 2020-08-13T14:07:43+00:00 Repair info: App was not updated: systemtags 2020-08-13T14:07:43+00:00 Repair info: App was not updated: templateeditor 2020-08-13T14:07:43+00:00 Repair info: App was not updated: updatenotification 2020-08-13T14:07:43+00:00 Repair step: Repair MySQL database engine 2020-08-13T14:07:43+00:00 Repair step: Repair MySQL collation 2020-08-13T14:07:43+00:00 Repair info: Change collation for oc_accounts ... 2020-08-13T14:07:44+00:00 Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE `oc_accounts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes 2020-08-13T14:07:44+00:00 Update failed 2020-08-13T14:07:44+00:00 Maintenance mode is kept active 2020-08-13T14:07:44+00:00 Reset log level #### Web server error log ``` Insert your webserver log here ``` #### ownCloud log (data/owncloud.log) ``` Insert your ownCloud log here ``` Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE `oc_accounts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes #### Browser log ``` Insert your browser log here, this could for example include: a) The javascript console log b) The network log c) ... ```

Aug 13 '20 14:08 neeks76

I encountered the same issue after restoring my owncloud 10.15 database to a new MariaDB 10.11 instance. It seems the oc_accounts table doesn't yet get used as a utf8mb4 character set even with 'mysql.utf8mb4'=>true in config.php.

In this state I wasn't able to run occ user:list, it aborted with errors like yours. And viewing the "Everyone" list of users in my small instance just appeared to load forever.

Running this against my database fixed the issue by resetting the character set back to utf8mb3, even if all the other tables are utf8mb4: alter table oc_accounts convert to character set utf8mb3 collate utf8mb3_general_ci;

May 18 '25 00:05 izzyboris