Sporadically losing ticket attachments when ticket is closed and reopen

KevinTheJedi

cortkny2024

Upgrade to the latest and retest.
If you can still replicate after upgrading then provide an screenshot of Admin Panel > Dashboard > Information and exact steps to replicate.

Cheers.

cortkny2024

Hi Kevin, Thanks. Do we have a checklist of things to do backup .etc before upgrade? I followed the youtube tutorial but it is published 5 years ago
https://www.youtube.com/watch?v=Oah6vn9DaDk

KevinTheJedi

cortkny2024

Turn the helpdesk Offline (Admin Panel > Settings > System > Helpdesk Status > Offline > Save Changes).
Disable Email Fetching (Admin Panel > Emails > Settings > Email Fetching > uncheck both boxes > Save Changes).
Perform a full MySQL database dump (using MySQL's native mysqldump command is best).
Perform a backup of all the site files (including plugins, etc.). You can zip the entire directory, etc. whatever works best for you.

Then you should be good to start the normal upgrade process. If something goes wrong and you need to revert bcak you can simply restore the site files, drop/re-import the database, and re-enable the settings.

Cheers.

cortkny2024

Hi @KevinTheJedi Thanks for the response.
When i try to export the database to create a backup, MySQL80 service keeps crashing.
I manage to identify 2 corrupted tables: ost_search and ost_file_chunk.

I was able to export the database without this two tables with the native mysql
mysqldump -u root -p mcbigrp --ignore-table=mcbigrp.ost__search --ignore-table=mcbigrp.ost_file_chunk > C:\Users\user\backup.sql

Question; will this 2 tables affect the overall stability and performance of osTicket?
PS. I have set osticket to use filesystem back in 2024 when I encountered the same behavior but back then the issue was just the ost_file_chunk

How do I make sure that this is not encountered again? as of this time the size of mcbigrp db is 99 MB

Thanks,
Knychtell

KevinTheJedi

cortkny2024

Ouch. Search table can always be rebuilt and reindexed however the file chunk table is very important. It houses the raw file data for files not stored on the filesystem. If that table is corrupted then that’s probably part of the issue.

Cheers.

cortkny2024

Could this also be the reason why we are seeing sporadic incidents that when a Resolver attaches a file (Less than 20MB)
and closes the ticket. When the ticket is reopened, the files are no longer present?

As for the attachments, I set the the store attachments in File System. Right now, the folder resides in inetpub/wwwroot/osticket directory and its already 5GB.

(
https://)

KevinTheJedi

cortkny2024

Well if it has been set to that this whole time then no. It’d be something else entirely. Make sure the cron job user is the web server user. I don’t think I’ve ever seen an instance where attachments successfully upload and function fine but then reopening the ticket removes them.

Cheers.

cortkny2024

Is there a guidance of how I can do repair /rebuild those corrupted tables in MySQL from a test environment without causing further issue before I do the upgrade?

cortkny2024

With this information, is it safe to perform the upgrade now?

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlcheck -u root -p mcbigrp
Enter password: *************
mcbigrp.ostsearch OK
mcbigrp.ost_api_key OK
mcbigrp.ost_attachment OK
mcbigrp.ost_audit OK
mcbigrp.ost_canned_response OK
mcbigrp.ost_config OK
mcbigrp.ost_content OK
mcbigrp.ost_department OK
mcbigrp.ost_draft OK
mcbigrp.ost_email OK
mcbigrp.ost_email_account OK
mcbigrp.ost_email_template OK
mcbigrp.ost_email_template_group OK
mcbigrp.ost_event OK
mcbigrp.ost_faq OK
mcbigrp.ost_faq_category OK
mcbigrp.ost_faq_topic OK
mcbigrp.ost_file OK
mcbigrp.ost_file_chunk OK
mcbigrp.ost_filter OK
mcbigrp.ost_filter_action OK
mcbigrp.ost_filter_rule OK
mcbigrp.ost_form OK
mcbigrp.ost_form_entry OK
mcbigrp.ost_form_entry_values OK
mcbigrp.ost_form_field OK
mcbigrp.ost_group OK
mcbigrp.ost_help_topic OK
mcbigrp.ost_help_topic_form OK
mcbigrp.ost_list OK
mcbigrp.ost_list_items OK
mcbigrp.ost_lock OK
mcbigrp.ost_note OK
mcbigrp.ost_organization OK
mcbigrp.ost_organizationcdata OK
mcbigrp.ost_plugin OK
mcbigrp.ost_plugin_instance OK
mcbigrp.ost_queue OK
mcbigrp.ost_queue_column OK
mcbigrp.ost_queue_columns OK
mcbigrp.ost_queue_config OK
mcbigrp.ost_queue_export OK
mcbigrp.ost_queue_sort OK
mcbigrp.ost_queue_sorts OK
mcbigrp.ost_role OK
mcbigrp.ost_schedule OK
mcbigrp.ost_schedule_entry OK
mcbigrp.ost_sequence OK
mcbigrp.ost_session OK
mcbigrp.ost_sla OK
mcbigrp.ost_staff OK
mcbigrp.ost_staff_dept_access OK
mcbigrp.ost_syslog OK
mcbigrp.ost_task OK
mcbigrp.ost_taskcdata OK
mcbigrp.ost_team OK
mcbigrp.ost_team_member OK
mcbigrp.ost_thread OK
mcbigrp.ost_thread_collaborator OK
mcbigrp.ost_thread_entry OK
mcbigrp.ost_thread_entry_email OK
mcbigrp.ost_thread_entry_merge OK
mcbigrp.ost_thread_event OK
mcbigrp.ost_thread_referral OK
mcbigrp.ost_ticket OK
mcbigrp.ost_ticketcdata OK
mcbigrp.ost_ticket_priority OK
mcbigrp.ost_ticket_status OK
mcbigrp.ost_translation OK
mcbigrp.ost_user OK
mcbigrp.ost_user__cdata OK
mcbigrp.ost_user_account OK
mcbigrp.ost_user_email OK

KevinTheJedi

cortkny2024

To do that in a test environment you’d have to copy the database which the only two ways I know of is to dump it or to replicate it. Your best bet is to just attempt it out of business hours to minimize the impact in case of issues. And of course always make full snapshots/backups of everything beforehand in case you need to restore.

Generally speaking if mysqlcheck says the tables are good then they should be good to export. With this being said I am in no way a DBA nor am I a MySQL expert so take what I say with a grain of salt and please do your own research and proceed at your own risk. 🙏🏻

Cheers.

cortkny2024

I understand, quick question though I managed to upgrade to 1.18.2 however, the Tickets tab in SCP doesn't pull out the actual tickets

The pull down menu seem to have my filters but none of the tickets show up when I click any of the filters.
I see 500 error when I inspect the traffic

KevinTheJedi

cortkny2024

That means you need to check your error logs for related errors.

Cheers.