Specified key was too long; max key length is 1000 bytes #17975
Comments
ikke-t
added
0. Needs triage
|
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? |
|
Strange. Usually Are you still using the bookmarks app? If not I would remove the tables and try again. Don't forget the backup ;) |
|
OK, I dropped the boomarks_tags table, and found the next one causing the same problem: |
|
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? |
|
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? |
|
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 https://mariadb.com/kb/en/library/innodb-large_prefix-deprecated-resulting-key-length/ probably check |
|
Values are a bit different, but nothing matches to 1000: |
|
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). |
|
I dropped the empty bookmarks table. Problem just moves forward to another table with the very same error. |
|
Yes, indeed. Until one hits tables one definitely cannot / doesn't want to delete … ;-) |
This comment has been minimized.
This comment has been minimized.
@ThomasT02 just to be sure. You have the same error with 1000 bytes?
It's probably happening with very new versions of MariaDB.
I don't know. |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
|
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. |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
|
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: 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. |
kesselb
changed the title
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
List of activated apps:
App list
Nextcloud configuration:
Config report
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
Browser log
Browser log
The text was updated successfully, but these errors were encountered: