Closed ikke-t closed 4 years ago
ikke-t
commented
5 years ago After googling around the issue, I feel the index key for the table gets too big while converting it. So some extra step is missing from conversion. Perhaps something along the lines of:
Or do we even need emojis in keys?
kesselb
commented
5 years ago Strange. Usually 1071 Specified key was too long; max key length is 1000 bytes we have this warning with less bytes (e.g. https://github.com/nextcloud/server/issues/15058).
Are you still using the bookmarks app? If not I would remove the tables and try again. Don't forget the backup ;)
ikke-t
commented
5 years ago OK, I dropped the boomarks_tags table, and found the next one causing the same problem:
In AbstractMySQLDriver.php line 106:
An exception occurred while executing 'ALTER TABLE `oc_jobs` 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 1000 bytes
In PDOStatement.php line 119:
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes
In PDOStatement.php line 117:
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes
here's what the dump of that looks like. I suppose I could drop the table, and somehow fix that SQL so it creates it back correctly. What modifications would be needed? And shouldn't the occ restore script do that automatically?
-- phpMyAdmin SQL Dump
-- version 4.9.1
-- https://www.phpmyadmin.net/
--
-- Host: 192.168.117.243
-- Generation Time: Nov 17, 2019 at 01:47 PM
-- Server version: 10.3.13-MariaDB
-- PHP Version: 7.2.23
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `nextcloud`
--
-- --------------------------------------------------------
--
-- Table structure for table `oc_jobs`
--
CREATE TABLE `oc_jobs` (
`id` bigint(20) UNSIGNED NOT NULL,
`class` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`argument` varchar(4000) COLLATE utf8_bin NOT NULL DEFAULT '',
`last_run` int(11) DEFAULT 0,
`last_checked` int(11) DEFAULT 0,
`reserved_at` int(11) DEFAULT 0,
`execution_duration` int(11) NOT NULL DEFAULT 0
) ENGINE=Aria DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Dumping data for table `oc_jobs`
--
INSERT INTO `oc_jobs` (`id`, `class`, `argument`, `last_run`, `last_checked`, `reserved_at`, `execution_duration`) VALUES
(1586, 'OCA\\Activity\\BackgroundJob\\EmailNotification', 'null', 1573997402, 1573997402, 0, 0),
(6315, 'OCA\\Activity\\BackgroundJob\\ExpireActivities', 'null', 1573920002, 1573997403, 0, 0),
(6937, 'OCA\\NextcloudAnnouncements\\Cron\\Crawler', 'null', 1573921803, 1573997403, 0, 15),
(6760, 'OCA\\Files\\BackgroundJob\\ScanFiles', 'null', 1573997402, 1573997402, 0, 0),
(6761, 'OCA\\Files\\BackgroundJob\\DeleteOrphanedItems', 'null', 1573995602, 1573997404, 0, 0),
(6762, 'OCA\\Files\\BackgroundJob\\CleanupFileLocks', 'null', 1573997401, 1573997401, 0, 0),
(6532, 'OCA\\Files_Trashbin\\BackgroundJob\\ExpireTrash', 'null', 1573995602, 1573997402, 0, 0),
(6533, 'OCA\\Files_Versions\\BackgroundJob\\ExpireVersions', 'null', 1573995602, 1573997402, 0, 0),
(6764, 'OCA\\DAV\\CardDAV\\SyncJob', 'null', 1517078706, 1574039702, 0, 3),
(6765, 'OCA\\Federation\\SyncJob', 'null', 1573927202, 1573997402, 0, 0),
(6863, '\\OC\\Authentication\\Token\\DefaultTokenCleanupJob', 'null', 1573997402, 1573997402, 0, 0),
(6876, 'OCA\\Survey_Client\\BackgroundJobs\\MonthlyReport', 'null', 1572881403, 1573997402, 0, 5),
(6865, 'OCA\\UpdateNotification\\Notification\\BackgroundJob', 'null', 1573923603, 1573997402, 0, 36),
(6867, 'OCA\\DAV\\CardDAV\\Sync\\SyncJob', 'null', 0, 1574039703, 0, 0),
(6868, 'OCA\\Files_Sharing\\DeleteOrphanedSharesJob', 'null', 1573996503, 1573997402, 0, 0),
(6869, 'OCA\\Files_Sharing\\ExpireSharesJob', 'null', 1573923639, 1573997402, 0, 0),
(7135, 'OC\\Authentication\\Token\\DefaultTokenCleanupJob', 'null', 1573997403, 1573997403, 0, 0),
(7136, 'OC\\Log\\Rotate', 'null', 1573997403, 1573997403, 0, 0),
(7188, 'OCA\\Deck\\Cron\\DeleteCron', 'null', 1573997403, 1573997403, 0, 0),
(7189, 'OCA\\Deck\\Cron\\ScheduledNotifications', 'null', 1573997403, 1573997403, 0, 0),
(7359, 'OC\\Preview\\BackgroundCleanupJob', 'null', 1573994703, 1573997403, 0, 0),
(7360, 'OCA\\DAV\\BackgroundJob\\CleanupDirectLinksJob', 'null', 1573923644, 1573997403, 0, 0),
(7361, 'OCA\\DAV\\BackgroundJob\\UpdateCalendarResourcesRoomsBackgroundJob', 'null', 1573993803, 1573997403, 0, 0),
(7362, 'OCA\\DAV\\BackgroundJob\\CleanupInvitationTokenJob', 'null', 1573923644, 1573997403, 0, 0),
(7363, 'OCA\\Files_Sharing\\BackgroundJob\\FederatedSharesDiscoverJob', 'null', 1573923644, 1573997403, 0, 0),
(7364, 'OCA\\AdminAudit\\BackgroundJobs\\Rotate', 'null', 1573986607, 1573997403, 0, 0),
(7365, 'OCA\\Support\\BackgroundJobs\\CheckSubscription', 'null', 1573997403, 1573997403, 0, 0),
(7427, 'OCA\\Deck\\Cron\\CardDescriptionActivity', 'null', 1573997403, 1573997403, 0, 0),
(11450, 'OCA\\Text\\Cron\\Cleanup', 'null', 1573997404, 1573997403, 0, 0),
(11449, 'OCA\\DAV\\BackgroundJob\\EventReminderJob', 'null', 1573997404, 1573997404, 0, 0),
(9841, 'OC\\Core\\BackgroundJobs\\CleanupLoginFlowV2', 'null', 1573996504, 1573997404, 0, 0);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `oc_jobs`
--
ALTER TABLE `oc_jobs`
ADD PRIMARY KEY (`id`),
ADD KEY `job_class_index` (`class`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `oc_jobs`
--
ALTER TABLE `oc_jobs`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11749;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;should I fix the CHARSET to utf8mb4 and COLLATE to utf8mb4_bin manually there, and drop and import the table? But should I also somehow modify the field sizes? The bigint and varchar counts?
kesselb
commented
5 years ago I don't know sorry.
For MariaDB 10.3.13 Dynamic is the default row format. According to https://mariadb.com/kb/en/library/innodb-dynamic-row-format/#index-prefixes-with-the-dynamic-row-format the prefix length is 3072.
System variable innodb_large_prefix has been removed with 10.3.1: https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_large_prefix
https://mariadb.com/kb/en/library/innodb-large_prefix-deprecated-resulting-key-length/ probably check innodb_page_size? Very confusing to be honest ;)
ikke-t
commented
5 years ago Values are a bit different, but nothing matches to 1000:
MariaDB [nextcloud]> SHOW VARIABLES like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
MariaDB [nextcloud]> SHOW VARIABLES like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | true |
+---------------------+-------+
nursoda
commented
4 years ago I face the same issue in one of my instances (the one I used bookmarks in). Very frustrating since one is stuck in the middle and must restore from backup. My system is on arch (thus all latest versions).
ikke-t
commented
4 years ago I dropped the empty bookmarks table. Problem just moves forward to another table with the very same error.
nursoda
commented
4 years ago Yes, indeed. Until one hits tables one definitely cannot / doesn't want to delete … ;-)
ThomasT02
commented
4 years ago The same issue for me. And I have also no root access. Is backing up the database and restoring it into a newly created database a recommendable work around? Or will the failure also be restored into the new database? @kesselb Do you know when usually an assignee will be called to a bug?
kesselb
commented
4 years ago Strange. Usually 1071 Specified key was too long; max key length is 1000 bytes we have this warning with less bytes (e.g. #15058).
@ThomasT02 just to be sure. You have the same error with 1000 bytes?
thus all latest versions
It's probably happening with very new versions of MariaDB.
Do you know when usually an assignee will be called to a bug?
I don't know.
ThomasT02
commented
4 years ago Sorry for my wrong statement, I don't have MariaDB but MySQL. And this was my error message:
kesselb
commented
4 years ago 
tomtana
commented
4 years ago Hi,
I had the same issue. Just found this post on stackoverflow which solved the problem:
https://stackoverflow.com/questions/35847015/mysql-change-innodb-large-prefix
## Innodb settings to bypass error of max size 737
innodb-file-format=barracuda
innodb-file-per-table=ON
innodb-large-prefix=ON
## Above 3 didnot work so i added below
innodb_default_row_format = 'DYNAMIC'Key is specifying the row format:
SET GLOBAL innodb_default_row_format = 'DYNAMIC;'
.. and it worked :)
ikke-t
commented
4 years ago Good for you! However you don't have the same issue. Your limiting size is about 737 where as mine is 1000. I also set all those variables before creating the issue. You see I printed them out. I read the same stack overflow :)
So something else still missing here :(
nursoda
commented
4 years ago Off-Topic? @seyfahni pointed out that the bug I experience was different. What I saw was the error "column already exists" when upgrading NC or updating an app, leading to a DB update. Deleting one culprit table only lead to the next table giving the same error. In my case, some entries in oc_migrations were missing. Adding them (from a freshly installed NC instance) solved my issue.
kolewu
commented
4 years ago As you can see in your list of tables, the ones that create errors are not of the format innodb (because neither oc_bookmarks_tags nor oc_jobs are listed there). And the settings for longer keys are only applicable to innodb tables.
Try to change the format of all tables to innodb. That should fix the 1071 key error.
Since this problem seems to be quite common (at least if the database has been updated through many owncloud and nextcloud versions), this should be integrated into the database repair option of occ.
kesselb
commented
4 years ago Ping @ikke-t ;)
ikke-t
commented
4 years ago Thanks, sorry for not answering due crazy hurry before holidays. I'll try to change those after the travels.
ikke-t
commented
4 years ago OK, coming back now after holiday season. Yes, @kolewu was correct with his spotting, some of my tables were innodb, some aria. I listed the Aria ones like this:
show table status where engine like 'aria' ;
and converted them to Innodb like this, one at the time:
ALTER TABLE oc_table_name_here ENGINE=InnoDB TRANSACTIONAL=default
It required the transactional clause there to succeed. After this the repair function fixed them nicely. Thanks for help, I close this ticket now. I hope this helps someone else in same state to get over the problem.
Steps to reproduce
su www-data -s /bin/sh -c 'php occ maintenance:repair'Expected behaviour
Tell us what should happen repair should do the
ALTER TABLE `oc_bookmarks_tags` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_binActual behaviour
the conversion fails for an empty table oc_bookmarks_tags, (perhaps too big key size?):
Server configuration
Operating system: Nextcloud container: docker.io/library/nextcloud:latest version 17.0.1: ce76d56c5f24
Web server: Apache, from withing the container
Database:
Mariadb settings:
Table definitions:
To make it more frustrating, the table is totally empty.
PHP version: PHP 7.3.11 (cli) (built: Oct 25 2019 02:28:50) ( NTS ) From within the container
Nextcloud version: (see Nextcloud admin page) 17.0.1
Updated from an older Nextcloud/ownCloud or fresh install: Updated along the years from very early NextCloud. I converted from OwnCloud at very beginning, so old database.
Where did you install Nextcloud from: Container from dockerhub
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. No errors have been found. ```List of activated apps:
App list
``` - accessibility: 1.3.0 [1/1531] - activity: 2.10.1 - admin_audit: 1.7.0 - bruteforcesettings: 1.4.0 - cloud_federation_api: 1.0.0 - comments: 1.7.0 - dav: 1.13.0 - deck: 0.7.0 - drawio: 0.9.4 - federatedfilesharing: 1.7.0 - federation: 1.7.0 - files: 1.12.0 - files_markdown: 2.1.0 - files_pdfviewer: 1.6.0 - files_rightclick: 0.15.1 - files_sharing: 1.9.0 - files_trashbin: 1.7.0 - files_versions: 1.10.0 - files_videoplayer: 1.6.0 - firstrunwizard: 2.6.0 - gallery: 18.4.0 - keeweb: 0.5.1 - logreader: 2.2.0 - lookup_server_connector: 1.5.0 - nextcloud_announcements: 1.6.0 - notes: 3.0.3 - notifications: 2.5.0 - oauth2: 1.5.0 - password_policy: 1.7.0 - privacy: 1.1.0 - provisioning_api: 1.7.0 - recommendations: 0.5.0 - serverinfo: 1.7.0 - sharebymail: 1.7.0 - support: 1.0.1 - survey_client: 1.5.0 - systemtags: 1.7.0 - text: 1.1.1 - theming: 1.8.0 - twofactor_backupcodes: 1.6.0 - twofactor_totp: 4.0.0 - updatenotification: 1.7.0 - viewer: 1.2.0 - workflowengine: 1.7.0 Disabled: - calendar - encryption - files_external - mindmaps - socialsharing_email - user_ldap ```Nextcloud configuration:
Config report
``` { [1/1583] "system": { "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "dbtype": "mysql", "version": "17.0.1.1", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbtableprefix": "oc_", "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "installed": true, "theme": "", "maintenance": false, "trusted_domains": [ "example.com", "foobar.com:8090" ], "mail_smtpmode": "smtp", "mail_from_address": "***REMOVED SENSITIVE VALUE***", "mail_domain": "***REMOVED SENSITIVE VALUE***", "mail_smtphost": "***REMOVED SENSITIVE VALUE***", "mail_smtpport": "25", "secret": "***REMOVED SENSITIVE VALUE***", "forcessl": false, "loglevel": 3, "trashbin_retention_obligation": "auto", "htaccess.RewriteBase": "\/", "appstore.experimental.enabled": true, "overwrite.cli.url": "https:\/\/example.com", "memcache.local": "\\OC\\Memcache\\APCu", "apps_paths": [ { "path": "\/var\/www\/html\/apps", "url": "\/apps", "writable": false }, { "path": "\/var\/www\/html\/custom_apps", "url": "\/custom_apps", "writable": true } ], "mail_smtpauthtype": "PLAIN", "app_install_overwrite": [ "mindmaps", "keeweb" ], "mysql.utf8mb4": false } } ```Are you using external storage, if yes which one: NFS
Are you using encryption: yes, termitated at external HAProxy
Are you using an external user-backend, if yes which one: No
Client configuration
Browser:
Operating system:
Logs
Web server error log
Web server error log
``` ```Nextcloud log (data/nextcloud.log)
Nextcloud log
``` Insert your Nextcloud log here ```Browser log
Browser log
``` Insert your browser log here, this could for example include: a) The javascript console log b) The network log c) ... ```