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