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_mapCONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;': SQLSTATE: 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:
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
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.😉