Old Corrupted tickets - Can't be Viewed or Deleted

vicos

Looking at my ost_ticket table, the first 7 tickets date back to 2019. When you query the number in the Staff's Panel search box, it pulls up the one liner for the ticket. But, when you click on the Number to go to the detail view for the ticket, you get:

ticket: Unknown or invalid ID.

The id being passed in the URL matches the database.

I don't see any data in the records that appears different than the data for valid tickets. Can I just manually delete those 7 records out of ost_ticket?

I also suspect this is related to my orphaned attachments not being purged. Over the years, my database has grown to 5GB. There are tens of thousands of records in ost_attachment, file, and file_chunk despite there only being 200 or so ticket records.

KevinTheJedi

vicos

Check all possible logs (general server logs, webserver error logs, PHP error logs, MySQL/MariaDB error logs, osTicket System Logs, Browser Console logs, etc.) for related errors. I get that is shows unknown ID but are you getting any fatal errors at PHP/webserver and/or database level?

Cheers.

vicos

can you point me to which file(s) contains the sql code that purges the orphaned attachments?

i would like to run it manually and see what errors pop up.

vicos

p.s. I have never seen anything in the logs related to this. If memory serves me, this all started years ago (prob 2019) after a version upgrade.

In any case I deleted those 7 records from ticket and thread. I'll check to see if the orphan attachment purge works at some time in the near future, via cron.

KevinTheJedi

vicos

Having cron is always a good idea. Orphaned files get deleted when they are no longer attached to an object via attachment table and created older than 48 hours.

Cheers.

vicos

KevinTheJedi Orphaned files get deleted when they are no longer attached to an object via attachment table and created older than 48 hours.

They're supposed to, but on my system, this has not happened in several years.

At this point, I'm just going to Maguyver it and delete the records using SQL.

KevinTheJedi

vicos

Make sure you do it thouroughly and ONLY touch true orphaned records. Remember, inline images are referenced within external content and can sometimes not have a correlating record in the attachment table. So you may think it's orphaned when it's not. You'd have to scan all content that can contain inline images to ensure none are referenced before deleting.

If cron isn't purging orphaned files then you need to look deeper as that's concerning. Wonder if the same issue with sessions, etc. which would be terrible.

Cheers.

vicos

KevinTheJedi Wonder if the same issue with sessions, etc. which would be terrible.

There are 594 records and they are all very recent.

vicos

This is the SQL I came up with. As far as I can tell, ost_attachment.type = 'H' are attachments assigned to TICKETS. Whereas, the other type would be FAQ articles, etc?

DELETE FROM ost_attachment WHERE type = 'H' AND object_id NOT IN (SELECT id FROM ost_thread_entry);

DELETE FROM ost_file WHERE id NOT IN (SELECT file_id FROM ost_attachment);

DELETE FROM ost_file_chunk WHERE file_id NOT IN (SELECT id FROM ost_file) LIMIT 500;

I'll check sessions and let you know.

KevinTheJedi

vicos No, H means ThreadEntry.

vicos That's good then. Odd that file cleanup isn't working.

Cheers.

vicos

class.file.php

`
static function deleteOrphans() {

    // XXX: Allow plugins to define filetypes which do not represent
    //      files attached to tickets or other things in the attachment
    //      table and are not logos
    $files = static::objects()
        ->filter(array(
            'attachments__object_id__isnull' => true,
            'ft' => 'T',
            'created__lt' => SqlFunction::NOW()->minus(SqlInterval::DAY(1)),
        ));

    foreach ($files as $f) {
        if (!$f->delete())
            break;
    }

    return true;
}

If this is the code to deleteOrphans, am I correct in assuming it is looking for ost_attachment.object_id to be NULL? If it should be NULL when orphaned, where is that function performed?

I queried the 16,912 records and not a single object_id is NULL. I am 1 Million % positive that the vast majority of the attachments belonged to now deleted tickets and should not be associated to anything else.

What is 'ft' => 'T'