I noticed that ost_file_chunk was growing large.

Went about deleting as many old, closed tickets as possible. Noted that not only wasn't the table size growing smaller, BUT the number of records was not going down either.

Then I scanned records sequentially and noted that the sequence was unbroken, meaning again that records were never being deleted.

Next, I created a ticket and attached a file to it. I noted the new record in ost_file. The ticket was then deleted and observed that the records in ost_file was still there.

I was running 1.11 and just upgraded to v1.15.2, so this has been going on for a very long time. I also have to believe that my situation is not unique.

Can we get a patch and then a built-in utility to scan for and delete orphaned file attachment records. I see numerous threads with people having to run complex SQL statements to clean up these tables. This should be a built-in feature/utility IMO.

4 months later

I've always been running cron. The emails are parsed and sent out, so I believe that is proof.

Are there any switches for cron to make it CleanOrphanedFiles?

How can I debug this? I don't see any feedback/logging any where for cron.php.

If I enable Debug loggin, all I see is:

Cron Job
Cron job executed

KevinTheJedi I just looked at the code you referenced and AttachmentFile::deleteOrphans looks for files in the file system, not in the database. My attachments are stored in the DB.

Shouldn't osTicket delete the attachment in the DB when the ticket it is attached to is deleted? Based on my experiment outlined in the first post, this is not happening.

      vicos

      It actually looks for files in the database. static::objects() in this case is AttachmentFile which is the ost_file and its ost_file_chunk relation.

      Cheers.

        @"KevinTheJedi" Well, my mistake, but in any case, no workee on my system.

        I think I am going to try moving the files to the file system and hope this resolves the issue.

        in that regard, can "Base folder for attachment files" be located above the root folder for the web server, where the files can not be accessible via httpd?

        For example, my webroot is /home/me/www and I like to keep file data in /home/me/data/ where the web server can directly read and write it, but it can never be accessed directly via the web by a user.

        @vicos

        Sure, but you’ll need to give Apache access or if you are running PHP-FPM make sure the FPM user has access.

        Cheers.

        @vicos

        Also, if it’s not deleting the attachments then I assume they are not orphaned and actually belong to something that still exists.

        Cheers.

          KevinTheJedi I will verify that the tickets are actually deleted from the database when I delete them from the UI. I have seen quite a few posts here about people with orphaned files that had to resort to SQL to remove them. In any case I will post screen shots when I know more.

            2 months later

            Which source code file contains the actual code that is supposed to purge the old attachment data from the databse, specifically ost_attachments and ost_file_chunk?

            osTicket v1.15.2 and PHP 7.4 and cron runs every 2 minutes

            This is supposed to work in this version, yet it doesn't and the 2 tables continue to grow in size.

                /**
     * Removes files and associated meta-data for files which no ticket,
     * canned-response, or faq point to any more.
     */
    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;
    }

            This appears to be the actual code in class.file.php. It looks like if it at any point it fails to delete the current file, it just breaks out and quits.

            I am guessing that either 1) It has a problem with a file early in the list and just quits or 2) the select criteria not being met. I looked at ost_attachments and I do not see a single entry where object_id is NULL out of almost 17,000 records and there are only like 5 entries where type='T' ... unless I am misinterpreting this.

            On closer inspection, I pulled up an older ticket which was closed and had an attachment. I fund the corresponding records in tables (attachment, file, file_chunk). I deleted the ticket and the corresponding records in the aforementioned tables were gone almost immediately.

            I'll have to assume that the other attachments were not being purged due to a bug in a previous version and the new version does know how to deal with them. I guess I'll have to purge them manually. Going to be messy since it looks like other important files are stored there, like logos and probably images inserted into FAQ topics.

            Would be good to have a manually run utility to purge orphaned attachments.

            @vicos

            You should just be able to just run the cron and let the cron task purge them. The core attachment/file stuff has not changed in a long time so I can't imagine the cron task not being able to "understand" the old attachments. You really need to figure out why the cron task isn't running properly. If it is running properly then that means the attachments/files aren't actually orphaned.

            Cheers.

              KevinTheJedi

              Well, without having to tear through hundreds of lines of lightly documented code, the best I can figure out by observation is that when a ticket is deleted, any file attachments to that ticket should have their records deleted from ost_attachments. Then when cron runs, it deletes any records in ost_file and ost_file_chunk where there is no record in attachments where attachments.file_id eq file.id. I have manually deleted records from attachments and then cron has subsequently deleted the corresponding records in file and file_chunks. Therefore, in a past version the attachment record was not being removed or updated or whatever it is supposed to do.

              KevinTheJedi If it is running properly then that means the attachments/files aren't actually orphaned.

              I have 245 tickets in the database and over 16,000 attachment records. I thinks its safe to say that most of those attachments are orphaned, if your definition of 'orphaned' is that they have no ticket to which they belong.

              In any case, it works with new data. Its just a matter of manually cleaning up the old junk.

                    vicos

                    It can be attached to anything tickets, tasks, faqs, custom fields, internal notes, inline images, etc.

                    Cheers.

                        KevinTheJedi I manually examined the database. There are no corresponding entries of the types you mention. Plus, the file table lists the filenames and I recognize them as attachments to previously deleted tickets -- previously as in deleted years ago.

                          Write a Reply...