I first installed friendica back in July of 2019 and had largely let it sit since then. Here's how I upgraded from 2020.03 to 2021.07, and how I fixed the mess I made.

First, for those of you who might be in the midst of some half-cooked upgrade like I was and are looking for the immediate fix, let me go into the short version of what happened to my install and how I fixed it. Then I can expound on what I learned.

Fixing a busted upgrade

I logged into friendica after a couple of month hiatus and found that my profile page was utterly blank and I got all sorts of unsettling "not found" type of errors. The user list in the admin panel was totally blank. I figured I must have somehow lost a bunch of data. In a sense, I did.

Here's how I fixed it:

• Download the 2020.07 package and extract it to a staging directory, just as I would for an upgrade. Copy the configs too

• Run some SQL queries to delete data from the tables that violated the foreign key constraints that the DB upgrade was trying to apply. In my case I had one record in the item table and two in the item-content table. I found the IDs with these queries:

  SELECT `item`.`id`, `item-uri`.`uri` FROM `item` LEFT JOIN `item-uri` ON 
  `item`.`uri-id` = `item-uri`.`id` WHERE `item-uri`.`uri` IS NULL;
  
  SELECT `item-content`.`id`, `item-uri`.`uri` FROM `item-content` LEFT JOIN
  `item-uri` ON `item-content`.`uri-id` = `item-uri`.`id` WHERE `item-uri`.`uri`
  IS NULL;
  

• Re-run the php bin/console.php dbstructure upgrade -v to see that the structure was fully upgraded with no errors. (The first run, I got past the item table only to get hung up on the item-content table, etc etc. If you're troubleshooting a similar scenario, it might be a different table entirely.

• Once I got a clean upgrade, then I ran the php bin/console.php postupdate task.
• Then I did another incremental upgrade to 2021.01. Fortunately I didn't have to go through the same foreign key song & dance.
• Finally, I was able to re-run the dbstructure upgrade from 2021.07, and it went through. I then ran the postupdate.
• Then I turned my php-fpm back on and checked the version url (/friendica). Everything looked good. I logged in and everything seemed back to normal.
• I immediately took another database backup.
• Then I turned the worker cron job back on.

The minute details

So after this long hiatus of not using friendica, in July or early August I decided to start posting again and maybe get some use out of the platform. I opted to upgrade since I knew it had been some time since I had upgraded and there were probably substantial changes. These days it seems the nature of software that it evolves constantly and backward compatibility isn't what it used to be.

At any rate, I ran through the upgrade process without being too diligent, which is what got me into this mess. Here is an outline of the upgrade process:

• Unpack the latest release distribution (e.g. friendica-full-2021.07.tar.gz) into a new direcotory
• Unpack the corresponding addon distribution into this new directory
• Copy the config/local.config.php, config/addon.config.php (if it exists), and the .htaccess files from the current site directory to the new directory
• Double check the path entry in config/local.config.php to make sure it references this new staging directory and is not hard-coded to the last release.
• Stop the worker cron and the php-fpm that's running the current site
• Back up the database (very important!)
• Run php bin/console.php dbstructure upgrade -v from the staging directory
• Run php bin/console.php postupdate from the staging directory
• Change the symlink for the document root to point to the new staging directory.
• Start up the php-fpm and check the versions listed at https://mynode/friendica
• You're done!

Well, at least that's what I thought when I ran the upgrade. It turns out that the database upgrade didn't complete successfully. If you don't check the /friendica url on your node, you might not know this. I sure didn't. I also didn't know that URL even existed. You also might not know to turn on debug logging in the admin section. This creates a log/friendica.log in your document root. If it doesn't, make sure log exists and is owned by the webserver or (heaven help you) is chmod'd 777.

So I finally figure out that I can turn on debug logging and I check this /friendica url, and it tells me the database is at version 1358/1425 or something. That's not good. After doing a bunch of flailing around I eventually restore from a backup of the database from post-update but before I panicked and started retrying the update process over again. That got me to these errors in the friendica log:

2021-10-27T16:26:06Z console [ERROR]: DB Error
{"code":1452,"error":"Cannot add or update a child row: a foreign key
constraint fails (`friendica_prod`.`#sql-alter-f6af-147d2c`, CONSTRAINT
`#sql-alter-f6af-147d2c_ibfk_1` FOREIGN KEY (`uri-id`) REFERENCES `item-uri`
(`id`) ON DELETE CASCADE)"...

This corresponded with a reference I found when desperately trying to piece together some coherent search terms for the issue I was facing. I came up with these issues from the friendica github:

• https://github.com/friendica/friendica/issues/10519
• https://github.com/friendica/friendica/issues/10375
• https://github.com/friendica/friendica/issues/9753
• https://github.com/friendica/friendica/issues/9878
• https://github.com/friendica/friendica/issues/10180

I gathered a couple of useful tidbits (and the solution) from these threads:

• Don't ever run dbstructure update with the -f flag, unless you have a very specific use case (for instance you have manually tweaked the DB schema)
• It's a common enough scenario to have data inconsistencies and "dangling references" that manually deleting unreferenced data could be necessary.
• It's not always the same data or even the same data type that needs to be cleaned out.
• You might need to delete some tables and let the migration reconstruct them.
• No, seriously. Never use -f with dbstructure upgrade.
• There's this handy-dandy /friendica url on every node that shows the database version and what rev the database thinks it is. You can use this for troubleshooting.

Ultimately, the synthesis of all of this information led to the solution that I wrote up above. Iteratively upgrade each increment, watching the logs and validating each upgrade in succession. I've had to do this before when I upgraded my owncloud instance from something like 3.0 up to 10.x. There were some really dicey upgrades back in there where the DB schema had to be manually tweaked in order to complete the upgrade. Fortunately things have gotten easier.

That's enough of this for now.