Server: Specified key was too long; max key length is 1000 bytes
Steps to reproduce
- I got notified to enable emoji support following this guide: https://docs.nextcloud.com/server/17/admin_manual/configuration_database/mysql_4byte_support.html
- I did the mariadb parts, but failed at the conversion phase as last step:
su www-data -s /bin/sh -c 'php occ maintenance:repair'
3.
Expected behaviour
Tell us what should happen
repair should do the ALTER TABLE `oc_bookmarks_tags` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
Actual behaviour
the conversion fails for an empty table oc_bookmarks_tags, (perhaps too big key size?):
su www-data -s /bin/sh -c 'php occ maintenance:repair'
Nextcloud is in maintenance mode - no apps have been loaded
- Repair MySQL collation
- Change row format for oc_bookmarks_tags ...
- Change collation for oc_bookmarks_tags ...
In AbstractMySQLDriver.php line 106:
An exception occurred while executing 'ALTER TABLE `oc_bookmarks_tags` 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
maintenance:repair [--include-expensive]
Server configuration
Operating system:
Nextcloud container: docker.io/library/nextcloud:latest
version 17.0.1: ce76d56c5f24
Web server:
Apache, from withing the container
Database:
SHOW VARIABLES like '%version%';
+---------------------------------+------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------------------------+
| innodb_version | 10.3.13 |
| protocol_version | 10 |
| slave_type_conversions | |
| system_versioning_alter_history | ERROR |
| system_versioning_asof | DEFAULT |
| version | 10.3.13-MariaDB |
| version_comment | FreeBSD Ports |
| version_compile_machine | x86_64 |
| version_compile_os | FreeBSD11.2 |
| version_malloc_library | jemalloc 4.2.1-0-g3de035335255d553bdb344c32ffdb603816195d8 |
| version_source_revision | c8f9b3f915a729ec35c00e92cc534a01271aa6e6 |
| version_ssl_library | OpenSSL 1.0.2o-freebsd 27 Mar 2018 |
| wsrep_patch_version | wsrep_25.24 |
+---------------------------------+------------------------------------------------------------+
Mariadb settings:
SHOW VARIABLES like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | true |
+---------------------+-------+
MariaDB [nextcloud]> SHOW VARIABLES like 'innodb_file_format';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| innodb_file_format | barracuda |
+--------------------+-----------+
1 row in set (0.001 sec)
MariaDB [nextcloud]> SHOW VARIABLES like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
MariaDB [nextcloud]> SHOW VARIABLES like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
Table definitions:
MariaDB [nextcloud]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA where schema_name like 'nextcloud';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def | nextcloud | utf8mb4 | utf8mb4_general_ci | NULL |
+--------------+-------------+----------------------------+------------------------+----------+
MariaDB [nextcloud]> select * from information_schema.COLUMNS WHERE table_schema=DATABASE() AND TABLE_NAME='oc_bookmarks_tags';
+---------------+--------------+-------------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+--------------+------------+-------+---------------------------------+----------------+--------------+-----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | IS_GENERATED | GENERATION_EXPRESSION |
+---------------+--------------+-------------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+--------------+------------+-------+---------------------------------+----------------+--------------+-----------------------+
| def | nextcloud | oc_bookmarks_tags | bookmark_id | 1 | NULL | YES | bigint | NULL | NULL | 19 | 0 | NULL | NULL | NULL | bigint(20) | MUL | | select,insert,update,references | | NEVER | NULL |
| def | nextcloud | oc_bookmarks_tags | tag | 2 | '' | NO | varchar | 255 | 765 | NULL | NULL | NULL | utf8 | utf8_bin | varchar(255) | | | select,insert,update,references | | NEVER | NULL |
+---------------+--------------+-------------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+--------------+------------+-------+---------------------------------+----------------+--------------+-----------------------+
MariaDB [nextcloud]> describe oc_bookmarks_tags;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| bookmark_id | bigint(20) | YES | MUL | NULL | |
| tag | varchar(255) | NO | | | |
+-------------+--------------+------+-----+---------+-------+
MariaDB [nextcloud]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME like "nextcloud%";
+----------+---------------------------------------+------+--------+-------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+---------------------------------------+------+--------+-------+------------+---------------+------------+
| 262 | nextcloud/oc_accounts | 33 | 5 | 34 | Dynamic | 0 | Single |
| 420 | nextcloud/oc_activity | 33 | 18 | 192 | Dynamic | 0 | Single |
| 264 | nextcloud/oc_addressbookchanges | 33 | 8 | 36 | Dynamic | 0 | Single |
| 265 | nextcloud/oc_addressbooks | 33 | 9 | 37 | Dynamic | 0 | Single |
| 403 | nextcloud/oc_authtoken | 33 | 19 | 175 | Dynamic | 0 | Single |
| 269 | nextcloud/oc_bruteforce_attempts | 33 | 9 | 41 | Dynamic | 0 | Single |
| 549 | nextcloud/oc_calendar_invitations | 33 | 11 | 321 | Dynamic | 0 | Single |
| 477 | nextcloud/oc_calendar_reminders | 33 | 16 | 249 | Dynamic | 0 | Single |
| 408 | nextcloud/oc_calendar_resources | 33 | 9 | 180 | Dynamic | 0 | Single |
| 474 | nextcloud/oc_calendar_resources_md | 33 | 7 | 246 | Dynamic | 0 | Single |
| 409 | nextcloud/oc_calendar_rooms | 33 | 9 | 181 | Dynamic | 0 | Single |
| 475 | nextcloud/oc_calendar_rooms_md | 33 | 7 | 247 | Dynamic | 0 | Single |
| 541 | nextcloud/oc_calendarchanges | 33 | 9 | 313 | Dynamic | 0 | Single |
| 271 | nextcloud/oc_calendarobjects | 33 | 16 | 43 | Dynamic | 0 | Single |
| 272 | nextcloud/oc_calendarobjects_props | 33 | 10 | 44 | Dynamic | 0 | Single |
| 273 | nextcloud/oc_calendars | 33 | 14 | 45 | Dynamic | 0 | Single |
| 419 | nextcloud/oc_calendarsubscriptions | 33 | 16 | 191 | Dynamic | 0 | Single |
| 275 | nextcloud/oc_cards | 33 | 11 | 47 | Dynamic | 0 | Single |
| 276 | nextcloud/oc_cards_properties | 33 | 9 | 48 | Dynamic | 0 | Single |
| 471 | nextcloud/oc_collres_accesscache | 33 | 8 | 243 | Dynamic | 0 | Single |
| 467 | nextcloud/oc_collres_collections | 33 | 5 | 239 | Dynamic | 0 | Single |
| 468 | nextcloud/oc_collres_resources | 33 | 6 | 240 | Dynamic | 0 | Single |
| 476 | nextcloud/oc_dav_cal_proxy | 33 | 7 | 248 | Dynamic | 0 | Single |
| 277 | nextcloud/oc_dav_shares | 33 | 9 | 49 | Dynamic | 0 | Single |
| 278 | nextcloud/oc_deck_assigned_labels | 33 | 6 | 50 | Dynamic | 0 | Single |
| 279 | nextcloud/oc_deck_assigned_users | 33 | 6 | 51 | Dynamic | 0 | Single |
| 366 | nextcloud/oc_deck_attachment | 33 | 11 | 138 | Dynamic | 0 | Single |
| 281 | nextcloud/oc_deck_board_acl | 33 | 10 | 53 | Dynamic | 0 | Single |
| 411 | nextcloud/oc_deck_boards | 33 | 10 | 183 | Dynamic | 0 | Single |
| 414 | nextcloud/oc_deck_cards | 33 | 18 | 186 | Dynamic | 0 | Single |
| 284 | nextcloud/oc_deck_labels | 33 | 8 | 56 | Dynamic | 0 | Single |
| 547 | nextcloud/oc_deck_stacks | 33 | 9 | 319 | Dynamic | 0 | Single |
| 407 | nextcloud/oc_directlink | 33 | 8 | 179 | Dynamic | 0 | Single |
| 286 | nextcloud/oc_federated_reshares | 33 | 5 | 58 | Dynamic | 0 | Single |
| 473 | nextcloud/oc_filecache_extended | 33 | 7 | 245 | Dynamic | 0 | Single |
| 287 | nextcloud/oc_flow_checks | 33 | 8 | 59 | Dynamic | 0 | Single |
| 288 | nextcloud/oc_flow_operations | 33 | 8 | 60 | Dynamic | 0 | Single |
| 470 | nextcloud/oc_login_flow_v2 | 33 | 14 | 242 | Dynamic | 0 | Single |
| 289 | nextcloud/oc_migrations | 33 | 5 | 61 | Dynamic | 0 | Single |
| 302 | nextcloud/oc_mindmaps | 33 | 7 | 74 | Dynamic | 0 | Single |
| 307 | nextcloud/oc_mindmaps_acl | 33 | 7 | 79 | Dynamic | 0 | Single |
| 306 | nextcloud/oc_mindmaps_nodes | 33 | 11 | 78 | Dynamic | 0 | Single |
| 290 | nextcloud/oc_notes_meta | 33 | 8 | 62 | Dynamic | 0 | Single |
| 291 | nextcloud/oc_notifications | 33 | 16 | 63 | Dynamic | 0 | Single |
| 545 | nextcloud/oc_notifications_pushtokens | 33 | 11 | 317 | Dynamic | 0 | Single |
| 293 | nextcloud/oc_oauth2_access_tokens | 33 | 8 | 65 | Dynamic | 0 | Single |
| 294 | nextcloud/oc_oauth2_clients | 33 | 8 | 66 | Dynamic | 0 | Single |
| 472 | nextcloud/oc_privacy_admins | 33 | 5 | 244 | Dynamic | 0 | Single |
| 297 | nextcloud/oc_schedulingobjects | 33 | 10 | 69 | Dynamic | 0 | Single |
| 551 | nextcloud/oc_systemtag_group | 33 | 5 | 323 | Dynamic | 0 | Single |
| 478 | nextcloud/oc_text_documents | 33 | 9 | 250 | Dynamic | 0 | Single |
| 479 | nextcloud/oc_text_sessions | 33 | 10 | 251 | Dynamic | 0 | Single |
| 543 | nextcloud/oc_text_steps | 33 | 8 | 315 | Dynamic | 0 | Single |
| 299 | nextcloud/oc_trusted_servers | 33 | 10 | 71 | Dynamic | 0 | Single |
| 406 | nextcloud/oc_twofactor_backupcodes | 33 | 7 | 178 | Dynamic | 0 | Single |
| 404 | nextcloud/oc_twofactor_providers | 33 | 6 | 176 | Dynamic | 0 | Single |
| 301 | nextcloud/oc_twofactor_totp_secrets | 33 | 8 | 73 | Dynamic | 0 | Single |
| 405 | nextcloud/oc_whats_new | 33 | 8 | 177 | Dynamic | 0 | Single |
+----------+---------------------------------------+------+--------+-------+------------+---------------+------------+
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) ...
ikke-t
All 21 comments
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:
- check key size
- adjust to size while converting to four bytes
- do the alter with proper size.
Or do we even need emojis in keys?
ikke-t
on 17 Nov 2019
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 ;)
kesselb
on 17 Nov 2019
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?
ikke-t
on 17 Nov 2019
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 ;)
kesselb
on 17 Nov 2019
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 |
+---------------------+-------+
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).
nursoda
on 26 Nov 2019
I dropped the empty bookmarks table. Problem just moves forward to another table with the very same error.
ikke-t
on 26 Nov 2019
Yes, indeed. Until one hits tables one definitely cannot / doesn't want to delete … ;-)
nursoda
on 26 Nov 2019
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?
ThomasT02
on 5 Dec 2019
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.
kesselb
on 5 Dec 2019
Sorry for my wrong statement, I don't have MariaDB but MySQL. And this was my error message:
ThomasT02
on 5 Dec 2019
@ThomasT02 https://github.com/nextcloud/server/issues/18105 & https://github.com/nextcloud/server/issues/15058
kesselb
on 5 Dec 2019
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 :)
tomtana
on 7 Dec 2019
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 :(
ikke-t
on 7 Dec 2019
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.
nursoda
on 8 Dec 2019
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.
kolewu
on 15 Dec 2019
Ping @ikke-t ;)
kesselb
on 22 Dec 2019
Thanks, sorry for not answering due crazy hurry before holidays. I'll try to change those after the travels.
ikke-t
on 22 Dec 2019
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.
ikke-t
on 16 Jan 2020
Related issues
blackcrack
·
3Comments
rullzer
·
3Comments
j-ed
·
3Comments
georgehrke
·
3Comments
williambargent
·
3Comments
Most helpful comment
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=defaultIt 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.