Skip to main content

Can I delete unused items from storage?
I'm behind on releases, I'm still on 2022.06. One thing preventing me upgrading is that I need to make a full backup for upgrading, but my database has blown up in size.

For me, the new storage table is pretty unfamiliar. But it seems to me that there's a lot of unused data there. Everything in storage must be either a photo or an attachment - right? I have 191433 rows in storage. I have 127206 rows in photo, and just one in attach. And it seems that the remaining rows are using 12GB, about half of the space of the whole DB:
MariaDB [friendica]> select count(*), sum(octet_length(storage.data)) from storage left outer join photo on storage.id = photo.`backend-ref` where photo.id is null;
+----------+---------------------------------+
| count(*) | sum(octet_length(storage.data)) |
+----------+---------------------------------+
|    63459 |                     13781405163 |
+----------+---------------------------------+

Am I right that I could just delete those rows (except for the single row in attach)?

!Friendica Support
!Friendica Support

Friendica Support reshared this.

in reply to Matthew Exon

utzer [Friendica]
utzer [Friendica]
• •
@Matthew Exon probably not and I am sure some developer will tell you why.

Did you ever optimize the database? Do you have enough free space on you server for a copy of the largest table (optimize or update could require to copy a whole table). Did you try to gain some free space by purging old versions of updated packages?

You can do the backup to some ssh accessible storage by piping the dump straight to another server by ssh.
@Matthew Exon

Friendica Support reshared this.

in reply to utzer [Friendica]

Matthew Exon
Matthew Exon
•
I did sudo mysqlcheck -o --all-databases, and it saved 1GB. But my dumps are 24GB and that's about the same as the database on disk, so it looks like that won't solve anything this time. Interestingly, the optimise succeeded despite claiming that it was rebuilding, and obviously there's not enough space to really rebuild. So something's fishy.

It's just Digital Ocean, I could easily add another disk, but that's not really the point. The database is twice as big as it should be, and that's going to make every step from now on a lot more than twice as hard as it should be. I should fix the biggest problem first.

Friendica Support reshared this.

in reply to Matthew Exon

utzer [Friendica]
utzer [Friendica]
• •
@Matthew Exon now I see the problem, I did not check before.

When you go to https://friendica.exon.name/friendica you can see that the update did not finish. So there is a problem.
@Matthew Exon

Friendica Support reshared this.

in reply to utzer [Friendica]

utzer [Friendica]
utzer [Friendica]
• •
@Matthew Exon at least the post update of the database is missing, see here under "Post Update Tasks":
https://friendi.ca/2022/06/11/friendica-2022-06-released/

So at least bin/console dbstructure update has to be run, but maybe also bin/console postupdate needs to be executed, but I am not sure about the later one and not sure about the best order to run these.
@Matthew Exon

Friendica Support reshared this.

in reply to utzer [Friendica]

Matthew Exon
Matthew Exon
•
Woah, I never knew about the /friendica diagnostics page.

I did run dbstructure update when I updated in July. I even copied and pasted the terminal output into my notes so that I can prove it 😛 But I've never heard of postupdate before, that does explain a lot.

Right now when I run dbstructure update it does nothing. When I run postupdate I get this error:
ALTER IGNORE TABLE `gserver` MODIFY `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `poco` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `noscrape` varchar(255) NOT NULL DEFAULT '' COMMENT '';

Error 1834 occurred during database update:

Cannot delete rows from table which is parent in a foreign key constraint 'apcontact_ibfk_1' of table 'apcontact'

I can probably try to untangle this myself. Thanks for the hints!

Friendica Support reshared this.

⇧