New Tickets not displaying content. Shows "deleted by username"

mattg

OS Ticket Version: 1.17.4
Web Server: IIS
MySQL Version: 5.7.41
PHP Version: 8.0.21

I've been having a problem since Thursday in that ticket contents are no longer being shown and instead a "deleted by message" presumably from a previous unrelated action is being displayed.

I enclose a screenshot of a new test ticket created today by me. Notice that the "deleted by" line has last Thursday's date (5 days before i created the ticket). There should be visible content in that ticket body. This is happening for all tickets since last Thursday.

I get nothing in the "System Logs" even with log level set to Debug.

The PHP log has some stuff in it. The first two i suspect are information rather than errors (as it looks like XMLRPC isn't available for PHP8 anyway, but i include it incase:

[19-Aug-2024 10:59:05 UTC] PHP Warning:  PHP Startup: Unable to load dynamic library 'php_xmlrpc.dll' (tried: C:\Program Files\PHP\v8.0.21\ext\php_xmlrpc.dll (The specified module could not be found), C:\Program Files\PHP\v8.0.21\ext\php_php_xmlrpc.dll.dll (The specified module could not be found)) in Unknown on line 0
[19-Aug-2024 11:00:00 UTC] PHP Warning:  PHP Startup: Unable to load dynamic library 'php_gd2.dll' (tried: C:\Program Files\PHP\v8.0.21\ext\php_gd2.dll (The specified module could not be found), C:\Program Files\PHP\v8.0.21\ext\php_php_gd2.dll.dll (The specified module could not be found)) in Unknown on line 0
[19-Aug-2024 11:00:52 UTC] PHP Fatal error:  Uncaught ObjectNotUnique: One object was expected; however multiple objects in the database matched the query. In fact, there are 2 matching objects. in [Redacted]\osticket\include\class.orm.php:1364
Stack trace:
#0 [Redacted]\osticket\include\class.orm.php(606): QuerySet->one()
#1 [Redacted]\osticket\include\class.orm.php(381): VerySimpleModel::lookup()
#2 [Redacted]\osticket\include\class.orm.php(417): VerySimpleModel->get()
#3 [Redacted]\osticket\include\class.thread.php(1122): VerySimpleModel->__get()
#4 [Redacted]\osticket\include\class.thread_actions.php(29): ThreadEntry->getEmailHeader()
#5 [Redacted]\osticket\include\class.thread.php(1839): TEA_ShowEmailRecipients->isVisible()
#6 [Redacted]\osticket\include\class.thread.php(1850): ThreadEntry->getActions()
#7 [Redacted]\osticket\include\staff\templates\thread-entry.tmpl.php(27): ThreadEntry->hasActions()
#8 [Redacted]\osticket\include\staff\templates\thread-entries.tmpl.php(58): include('...')
#9 [Redacted]\osticket\include\class.thread.php(415): include('...')
#10 [Redacted]\osticket\include\staff\ticket-view.inc.php(777): Thread->render()
#11 [Redacted]\osticket\scp\tickets.php(569): require_once('...')
#12 {main}
  thrown in [Redacted]\osticket\include\class.orm.php on line 1364

Any thoughts?

KevinTheJedi

mattg

You are having duplicate records and/or the db is reusing old IDs. You need to halt the system, do a db cleanup, and reset each table’s auto-increment. You can do so by running a repair on each table. I would highly recommend upgrading to MySQL 8.0 so that this doesn’t happen again.

Cheers.

mattg

Thanks for the quick reply @KevinTheJedi - I do have a MySQL 8 instance that i can move it into once i get this issue resolved.

I'm not really clear on how to do a repair. I've tried to do a repair table ost_xxx for the tables, but all apart from ost__search are InnoDB and they all say "The storage engine for the table doesn't support repair".

I have MySQL Workbench and obviously the MySQL command line access, but my Google-Fu is failing me. I've been able to Analyze, Optimzie and Check Table using MySQL and everything reports okay.

Do i just need some way to manually reset the auto-increment value on a particular table?

Sorry to be a noob on this and thanks in advance!

KevinTheJedi

mattg

Oh okay. Then best case is to stop system, fully dump database, drop it, re-import it, and that should repair the tables and any auto-increment.

Cheers.

mattg

Okay, fresh day, fresh attempt!
Being a bit nervous to drop the original database, i instead created a new one with "a" on the end and reimported everything to this. I then changed the DBNAME in ost-config.php to the new database name and confirmed OSTicket is loading from the new data on the information page (under Database Information and Usage / Schema).

It's still doing the same thing, but every ticket shows the same "deleted by" date and time:

I do have a DB backup from the 11th if it's an unsolvable. Now that i've run through the recovery process, it would mean losing a week of data, but it would in theory get me up and running again if we think it's not likely to just re-occur.

Cheers,
Matt

mattg

FYI i've just restored to the backup prior to the issue. I'm throwing a few hundred tickets at it to see if triggeres the issue again or it happened because a unique set of circumstances.

Thanks for the guidance.

Matt

mattg

Final update here just in case the extra info helps anyone else while troubleshooting (or if it becomes relevent to a future bug discovery).

The issue seemed to happen when a colleague was deleting about 100 tickets (automatically created during station reimaging). I wonder if tickets were being created as they were being deleted and some wires got crossed. We will just close tickets now rather than delete.

My solution was to restore from prior to the date in the weird "deleted by" message. Then in the mailbox, i moved all of the processed tickets from the missing period back into the inbox and they were automatically re-imported.
This will have sent some duplicate messages to users, but ensures we lose nothing.

Finally, i have also updated my MYSQL server to version 8.3 as i had an install already available on that server.

Thanks for the guidance.

KevinTheJedi

mattg

That shouldn't be happening so either MySQL isn't fully deleting data, the deletion process gets interrupted and not deleting data all the way, or maybe a bug (which if this were the case we'd be seeing many reports), or something along those lines. Glad you have a workable solution for now though!

Cheers.