Iām not sure how to resolve this, I could modify the upgrade script that is trying to alter the database (if I knew where that was ), or I could alter the database to work with the script, but Iām not sure how to do that either.
For now my Nextcloud instance is kaput, so any advice is appreciated.
Hereās the full error text:
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE oc_mimetypes CHANGE `mimetype` `mimetype` VARCHAR(255) DEFAULT '' NOT NULL':
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
Update failed
Here is the table status of the table in question:
I tried reverting the character set and collation on oc_mimetypes to latin1 so the upgrade scriptās ALTER TABLE statement would work. This got the script past this error, but it tripped on a few other tables. I repeated the process for the additional tables and that seemed to go well, but then I ran into something of a paradox.
At one point the upgrade script tries to alter the class column of the oc_admin_sections table and set its type to VARCHAR(255). If the collation is set to utf8 this fails because a 255 char key is too long:
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE oc_admin_sections CHANGE `class` `class` VARCHAR(255) DEFAULT '' NOT NULL':
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
This is just like the earlier errors, and if I change the character set/collation, the error is cleared. However, later in the upgrade the script attempts to change the character set to utf8mb4. This fails because the class column has been set to VARCHAR(255) as described above:
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing āALTER TABLE oc_admin_sections 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
This creates a chicken-and-egg problem. I can modify the table to pass one error, but then the upgrade script trips later; if I modify the DB to address the second error, I canāt get past the first.
Iām guessing that there is a better way to resolve the 1071 Specified key was too long; error than reverting the collation, but Iām not familiar enough with mysql (yet ) to know what that might be. I assume this because otherwise I donāt understand how the upgrade script could ever complete successfully.
At this point Iād be happy to roll-back the upgrade just to get my Nexcloud back on-line and buy some time to find a long-term solution to this problem (even if that means backing up the DB and re-installing from scratch). But at the moment if a roll-back isnāt possible, Iām looking for any hack that can get the system back on-line until a better solution can be executed.
tl:dr: to fix this, try to add in my.cnf āinnodb_large_prefix=1ā instead of āinnodb_large_prefix=trueā
I had same issue, in fact trouble with this āinnodb_large_prefix=trueā
Itās a little bit hidden, but according to the documentation, for MySQL < 5.6, you have to use 1 instead of ON in the config file:
Variables that have a type of ābooleanā can be set to 0, 1, ON or OFF. (If you set them on the command line or in an option file, use the numeric values.)