Database error upgrading to 12.0.2

My upgrade is stuck due to an exception while updating the database schema.

I think this is due to changes I made previously to get unicode working (see https://docs.nextcloud.com/server/11/admin_manual/maintenance/mysql_4byte_support.html )

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 :slight_smile: ), 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:

| oc_mimetypes | InnoDB | 10 | Compact | 29 | 564 | 16384 | 0 | 16384 | 0 | 31 | 2017-07-26 11:35:24 | NULL | NULL | utf8mb4_bin | NULL | row_format=COMPRESSED | |

Thank-you!

Jason

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 :slight_smile: ) 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.

I had the same problem. My solution was to switch from MySQL to MariaDB and then follow the “even more experimental” steps from https://docs.nextcloud.com/server/11/admin_manual/maintenance/mysql_4byte_support.html#mariadb-support. After this the upgrade from 12.0.0 to 12.0.3 worked flawlessly.

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.)