{"id":563,"date":"2021-04-19T00:41:05","date_gmt":"2021-04-19T05:41:05","guid":{"rendered":"https:\/\/www.jasonsblog.place\/?p=563"},"modified":"2021-04-19T00:41:06","modified_gmt":"2021-04-19T05:41:06","slug":"troubles-in-upgrading-nextcloud-from-19-0-4-to-19-0-8","status":"publish","type":"post","link":"https:\/\/www.jasonsblog.place\/index.php\/2021\/04\/19\/troubles-in-upgrading-nextcloud-from-19-0-4-to-19-0-8\/","title":{"rendered":"Troubles in upgrading Nextcloud from 19.0.4 to 19.0.8"},"content":{"rendered":"\n

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<\/a>, allow it to download the files, unpack the files, and update the database. This time, though, I was presented with an error: <\/p>\n\n\n\n

An exception occurred while executing 'ALTER TABLE apps2_games_map<\/code>  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.<\/pre>\n\n\n\n
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<\/a> (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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
After searching for a few minutes, I came across this issue<\/a> in the Nextcloud issue tracker. The user “madpipeline<\/a>” 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:<\/p>\n\n\n\n
 jason@computer:~ $ SHOW COLUMNS FROM apps2_games_map;<\/code>\n\n +---------+----------------+------+-----+---------+-------+\n | Field   | Type           | Null | Key | Default | Extra |\n +---------+----------------+------+-----+---------+-------+\n | game_id | varchar(63)    | NO   | PRI |         |       |\n | author  | varchar(63)    | NO   | PRI |         |       |\n | map_id  | bigint(20)     | NO   | PRI | 0       |       |\n | json    | varchar(10240) | NO   |     |         |       |\n | jsol    | varchar(10240) | YES  |     | NULL    |       |\n +---------+----------------+------+-----+---------+-------+<\/pre>\n\n\n\n
I saw that the json<\/code> and jsol<\/code> fields looked the biggest. I chose the jsol<\/code> field and changed its type to “text” with the following command:<\/p>\n\n\n\n
jason@computer:~$ ALTER TABLE apps2_games_map MODIFY COLUMN jsol TEXT;<\/code><\/pre>\n\n\n\n
After that command exited successfully, I then used this command to repair the database:<\/p>\n\n\n\n
jason@computer:~$ sudo -u www-data php \/var\/www\/nextcloud\/occ maintenance:repair<\/code><\/pre>\n\n\n\n
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<\/em> I shouldn’t have been using that PPA). This was easily fixed by removing the “php8.0-common” package.<\/p>\n\n\n\n
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.\ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"
I thought I was doing a small update to my file server. Turns out I was about to take a dip into the database.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[223,225,67,70,224],"class_list":["post-563","post","type-post","status-publish","format-standard","hentry","category-information-technology","tag-emoji","tag-fields","tag-nextcloud","tag-sql","tag-tables"],"_links":{"self":[{"href":"https:\/\/www.jasonsblog.place\/index.php\/wp-json\/wp\/v2\/posts\/563","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jasonsblog.place\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jasonsblog.place\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jasonsblog.place\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jasonsblog.place\/index.php\/wp-json\/wp\/v2\/comments?post=563"}],"version-history":[{"count":5,"href":"https:\/\/www.jasonsblog.place\/index.php\/wp-json\/wp\/v2\/posts\/563\/revisions"}],"predecessor-version":[{"id":568,"href":"https:\/\/www.jasonsblog.place\/index.php\/wp-json\/wp\/v2\/posts\/563\/revisions\/568"}],"wp:attachment":[{"href":"https:\/\/www.jasonsblog.place\/index.php\/wp-json\/wp\/v2\/media?parent=563"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jasonsblog.place\/index.php\/wp-json\/wp\/v2\/categories?post=563"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jasonsblog.place\/index.php\/wp-json\/wp\/v2\/tags?post=563"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}