Fix Nextcloud column size too large

Recently I upgraded my Nextcloud instance and after upgrading I got some missing indexes to fix. That’s normal with Nextcloud, you will get things from time to time to update. Here we will fix an error ‘column size too large‘ when you run the occ command on the server.

If you go to the Nextcloud settings -> overview tab as Admin, you may see the following missing indexes to update.

The database is missing some indexes. Due to the fact that adding indexes on big tables could take some time they were not added automatically. By running “occ db:add-missing-indices” those missing indexes could be added manually while the instance keeps running. Once the indexes are added queries to those tables are usually much faster.

– Missing index “properties_pathonly_index” in table “oc_properties”.

– Missing index “job_lastcheck_reserved” in table “oc_jobs”.

Now you may think it’s simple, just go to the server and run occ command as mentioned and we are done. It is suppose to be like that, but not in every case. In this case you may encounter an error like below.

An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes

Enable 4-byte support

If you haven’t enabled file_per_table etc in the database, let’s add that so you have 4-byte support(text based emojis) etc.

Configuration file for MariaDB is in /etc/mysql.

vim /etc/mysql/mariadb.cnf

Add the following lines or just uncomment them if they are there already.

innodb_large_prefix = true
innodb_file_format = barracuda
innodb_file_per_table = 1

Restart the database.

systemctl restart mariadb

We are not done yet for this specific issue, if you run the occ command you may still get the errors above.

Fix column size too large

This specific issue cannot be fixed via occ or adding options to configurations. We have to fix it in the Nextcloud database itself.

Login to the database and switch to the nextcloud. Your db name could be different.

use nextcloud;
ALTER TABLE oc_properties ROW_FORMAT=DYNAMIC;

Exit from the database server.

Run occ(make sure you are in the right dir to run this command) command now.

sudo -u www-data php occ db:add-missing-indices

That’s it. Go to the settings -> overview tab and refresh it.