Database Size

DanRhodes

Hi All, we currently have a 300MB DB Size with our provider. Is there any way we can compact the DB at all.Are the attachements stored in the DB, could they be removed easily?Cheers

Chefkeks

Yes the attachments are stored in the database. To check the size used by attachments check the info under:

Admin panel > dashboard > information > database space used for attachmentsRemoving attachments itself can then be done via the database (need to look up the details tomorrow if you want to know it) or by deleting the tickets with really large attachments.

DanRhodes

Hi,I am able to access the DB and see the File BLOBS and sizes but am unsure as to what tickets these relate to is there a way to see which tickets so I can delete the tickets with large file attachments?Cheers

Chefkeks

Yes there is a way, but...Unfortunately I'm in a hurry and so going to answer that next week ;)

DanRhodes

Great! The time it took to login to the forums and type that you could have told me!Cheers

Chefkeks

No, since I typed from my private (Windows) phone without any possibility to check on our corporate osTicket database.

Cheers,

Michael

PS: Answering from the phone right now too

ntozier

You could install the storage filesystem plugin and export the attachments out of the database to the file system

Chefkeks

Stupid me ... Why did I not consider / suggest that?! Thanks @[deleted]!

DanRhodes

Hi,I have downloaded and then uploaded the phar file but am still getting no plugins available on the helpdesk?? Plugin NameStatusDate InstalledNo plugins installed yet — add one!

Chefkeks

Before you do anything written below, make a backup and as usually when editing the database directly you're doing this on your own risk and without any warranty etc. provided!To get the largest attachments (PREFIX is "ost_" so when you're using a different prefix change it):SELECT * FROM `ost_file` ORDER BY `ost_file`.`size` DESCTo find then the tickets related to these files take a look at the following:SELECT * FROM `ost_ticket_attachment`You'll notice the columns attach_id and ticket_id in the ost_ticket_attachment table. These columns are to associate the ticket attachment to the ticket.So when you'd like to remove large attachments you can simply do this by deleting the rows in the ost_file and ost_ticket_attachment table that are in a relationship to each other. That way you'll delete the actual attachment (from ost_file table) and the relationship info to the ticket for the attachment (ost_ticket_attachment table).Btw. we've done this in the past too to keep the tickets but delete unnecessarily big attachments from the tickets and never experienced any issues so it shall work, but as already said it happens under your responsibility.

DanRhodes

Thanks Chefkeks, any idea re the plugins not showing up?

Chefkeks

Did you hit the "add one" link?Beside the only idea I currently have is the file ownership / permission that is maybe not correct and needs to be corrected.