Troubles in upgrading Nextcloud from 19.0.4 to 19.0.8

A couple of months ago, I was upgrading my install of Nextcloud like I usually do every month or so. Updating the install is quite easy: use their Updater app, allow it to download the files, unpack the files, and update the database. This time, though, I was presented with an error:

An exception occurred while executing 'ALTER TABLE apps2_games_map  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':  SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too  large. The maximum row size for the used table type, not counting BLOBs,  is 65535.

Let me write about how this came about. Sometime last year, I decided to upgrade the Nextcloud database to one which supported 4-byte emoji symbols (Why? Because sometimes people need to communicate with smilies, that’s why). I went through the motions of updating the database to support 4-btyes of whatever, changing the necessary fields. When the database was updated, and I ran the instance, everything looked fine.

Fast foward to a couple of months later, and I decided to upgrade Nextcloud from 19.0.4 to 19.0.8 since I like to have security updates and bug fixes in my instance. When all was said and done, the web-based updater went through and tried updating the database. It soon gave the above error, and I had a dead site. You can’t be productive when your files don’t synchronize.

After searching for a few minutes, I came across this issue in the Nextcloud issue tracker. The user “madpipeline” had a similar problem, and he solved the problem by changing some of the database field types from “varchar” to “text”. I logged into the database, I looked through the fields, and narrowed it down to these few:

 jason@computer:~ $ SHOW COLUMNS FROM apps2_games_map;

 +---------+----------------+------+-----+---------+-------+
 | Field   | Type           | Null | Key | Default | Extra |
 +---------+----------------+------+-----+---------+-------+
 | game_id | varchar(63)    | NO   | PRI |         |       |
 | author  | varchar(63)    | NO   | PRI |         |       |
 | map_id  | bigint(20)     | NO   | PRI | 0       |       |
 | json    | varchar(10240) | NO   |     |         |       |
 | jsol    | varchar(10240) | YES  |     | NULL    |       |
 +---------+----------------+------+-----+---------+-------+

I saw that the json and jsol fields looked the biggest. I chose the jsol field and changed its type to “text” with the following command:

jason@computer:~$ ALTER TABLE apps2_games_map MODIFY COLUMN jsol TEXT;

After that command exited successfully, I then used this command to repair the database:

jason@computer:~$ sudo -u www-data php /var/www/nextcloud/occ maintenance:repair

At first the command failed because of something that happens when you want the latest and greatest: my version of PHP, 8.0.1, was too new for Nextcloud (I knew I shouldn’t have been using that PPA). This was easily fixed by removing the “php8.0-common” package.

Finally, I took Nextcloud out of maintenance mode, and the upgrade was allowed to continue. It looks like I shouldn’t be modifying the database for every little improvement. But it doesn’t hurt to be prepared for the next big thing in communication.😉