Hi there,

I'm reaching out in the hope that you might be able to provide some guidance on a persistent issue I'm experiencing with osTicket (v1.10).

Our database is periodically crashing. While we can still log into the system, attempting to open a ticket results in an inability to view any responses. Temporarily, a restart of the MySQL server resolves the issue and all the content becomes accessible again. However, this is not a permanent fix, and the issue arises sporadically, necessitating repeated server reboots for continued user access.

Upon investigation, I've discovered that the database is corrupted, specifically the 'ost_file' table. Attempts to repair this table have been unsuccessful, yielding an "Operation failed" error.

Regrettably, the most recent backup available is from July 31, 2023 — I'm aware that this is not ideal.

My primary concern is preserving the current content, which remains visible until the next crash and subsequent reboot. Is there a way to export the current open tickets and incorporate them after restoring the database from the July 31 backup, so we don't lose data on active tickets?

While I have a basic understanding of databases, my expertise in troubleshooting is limited.

The system, initially intended for minor tasks, has grown unexpectedly, and admittedly, I haven't maintained it as well as I should have. The database size is approximately 90GB, encompassing a substantial number of large attachments accumulated over the last six years across 72,718 tickets. It might be prudent to consider alternative storage solutions for attachments in the future.

Another frustration is the unpredictable nature of these crashes. Sometimes, post-reboot, the system functions for hours without issues, allowing full data access, but at other times, it crashes repeatedly.

What would you recommend as the best course of action to restore stability to our database?

Thank you for your time and assistance.

    rynojacobs17

    You can’t import Tickets exported from the UI. In order to backup and re-import Tickets you will need a database backup or run SQL commands to dump certain tables. Tickets touch a lot of tables so it’s basically ideal to do a full database dump. With this being said I believe the below is the best course of action:

    1. Backup your current database. You can skip the file table if it becomes a problem during mysqldump.
    2. Restore from a backup where the table is not corrupted.
    3. Try to import the latest dump. You may need to follow this guide to dump the database properly to be able to import it while ignoring duplicates, etc.
    4. After everything is back to normal you can install a file storage plugin (either filesystem or S3). Install, enable, and configure the new plugin. Then you can run CLI commands to migrate existing attachments from the database to the external storage. You can also set your system to use the new file storage for new files in your system settings.

    Now, that should dramatically decrease db storage and help with performance, etc. I believe the main issue of the crash is due to size of db and corrupted file table. File table is used in all tickets that have attachments or inline images so I can imagine this will crash the system regularly if it’s truly corrupted.

    If all that doesn’t help then you’ll simply need to dedicate more resources to MySQL. MySQL has good optimization guides as well as docs on how to dedicate more memory, etc.

    Cheers.

      rynojacobs17

      After you get everything working again it’s highly advisable to upgrade to the latest (v1.18.1). v1.10 is so out of date and has many bugs and security vulnerabilities. Plus you are missing out on all the latest features, etc. When you upgrade you will also upgrade PHP which in itself introduces performance improvements. If you are running an older version of MySQL it’s also advisable to upgrade to MySQL 8.0+ as that introduces performance improvements and overall quality of life improvements.

      Cheers.

        12 days later

        Hi Kevin, I would just like to say thanks for all the info, I managed to restore the database. Now that I have a stable system I will upgrade Ubuntu, OsTicket, PHP and MySQL and make sure I keep it updated. Then I will rather migrate the attachments to external storage.

        Cheers

        Write a Reply...