Huge SQL Usage Spike?

Sidecutter

I just received a warning from my host that SQL services for the user associated with the database for osticket have been severely limited due to massive useage. On checking the resource usage for the last few days, I found the record shown below. 193 minutes on the 9th, 489 on the 10th, and 194 on the 11th...can anyone help me figure out what the heck is going on here? The database account cited as the source is ONLY used with the osticket database.

ntozier

Did you have a high traffic day?

Sidecutter

No, not in any particular way. And high traffic on the main site would not do this anyway. Only osticket uses the MySQL username that was responsible, and that username is ONLY associated with the database of osticket. Nor can I see any kind of similar spikes in the history anywhere.Here's what I just got back from the hosting company, and the image, since it seems to have not attached properly before.### 147 Queries ### Total time: 45578.290858, Average time: 310.056400394558### Taking 300.340800 to 321.728241 seconds to complete### Rows analyzed 61023065 Rows_affected: 0 Rows_read: 61023065 - 71099892 Rows_affected: 0 Rows_read: 71099892use captured_osticket;SET timestamp=XXX;SELECT ticket.ticket_id as id ,count(DISTINCT attach.attach_id) as attachments ,count(DISTINCT message.id) as messages ,count(DISTINCT response.id) as responses ,count(DISTINCT note.id) as notes FROM ost_ticket ticket LEFT JOIN ost_ticket_attachment attach ON (ticket.ticket_id=attach.ticket_id) LEFT JOIN ost_ticket_thread message ON (ticket.ticket_id=message.ticket_id AND message.thread_type = 'XXX') LEFT JOIN ost_ticket_thread response ON (ticket.ticket_id=response.ticket_id AND response.thread_type = 'XXX') LEFT JOIN ost_ticket_thread note ON ( ticket.ticket_id=note.ticket_id AND note.thread_type = 'XXX') WHERE ticket.ticket_id=XXX GROUP BY ticket.ticket_id;use captured_osticket;SET timestamp=1399868424;SELECT ticket.ticket_id as id ,count(DISTINCT attach.attach_id) as attachments ,count(DISTINCT message.id) as messages ,count(DISTINCT response.id) as responses ,count(DISTINCT note.id) as notes FROM ost_ticket ticket LEFT JOIN ost_ticket_attachment attach ON (ticket.ticket_id=attach.ticket_id) LEFT JOIN ost_ticket_thread message ON (ticket.ticket_id=message.ticket_id AND message.thread_type = 'M') LEFT JOIN ost_ticket_thread response ON (ticket.ticket_id=response.ticket_id AND response.thread_type = 'R') LEFT JOIN ost_ticket_thread note ON ( ticket.ticket_id=note.ticket_id AND note.thread_type = 'N') WHERE ticket.ticket_id=242 GROUP BY ticket.ticket_id;

Sidecutter

OK, so I think I found the root of the issue. One ticket, which has 4-5 large images, some of which never did load right, had 72 responses a few days ago, all copies of the same response with the same broken attachments. I'm guessing because the attachments were too big to load fully it just kept trying.It now has 169 replies, so it seems to just be keeping going. But I can't delete it because the whole thing times out trying to do so. How can I clear that ticket and any associated data and attachments?

Grizly

You could try creating a script to delete it, save it in root of osTickets directory, call it from your browser (unless its truly massive, then you'll need to modify it a bit and run it from the command line) don't forget to remove it afterwards, I'd call it something like: delete_single_ticket.php:<?phprequire_once 'main.inc.php';$annoying_ticket_id = 24102; //change this to the id of the ticket, NOT the Public one// eg: /scp/tickets.php?id=24102//Remove troublesome ticket$t = new Ticket($annoying_ticket_id);echo 'Deleting ticket, please wait.';$t->delete(); ?>CLI version: add these to top... $_SERVER = 'localhost';$_SERVER = 'script';

Sidecutter

Thanks. I actually got into the database, and have been MOSTLY successful, but not entirely. There still seem to be remnants, even though all mentions of the ticket and attachments are gone! I keep getting the below error e-mail when it runs the cron job to pull new tickets from e-mail. I've totally purged all the older mail in that account, so it can't be reading that as an e-mail that never got processed fully, yet it keeps finding SOMETHING. If I can clear this up, I think I'm fixed. Suggestions?Duplicate entry 'xxxxxxxxxx@gmail.com' for key 'address'

---- Backtrace ----

#0 (root)/include/mysqli.php(126): osTicket->logDBError('DB Error #1062', '[INSERT INTO os...')

#1 (root)/include/class.orm.php(176): db_query('INSERT INTO ost...')

#2 (root)/include/class.orm.php(57): VerySimpleModel->save()

#3 (root)/include/class.orm.php(204): VerySimpleModel->set('default_email', Object(UserEmail))

#4 (root)/include/class.user.php(89): VerySimpleModel:(Array)

#5 (root)/include/class.ticket.php(2050): User:(Array)

#6 (root)/include/class.mailfetch.php(548): Ticket->create(Array, Array, 'Email')

#7 (root)/include/class.mailfetch.php(586): MailFetcher->createTicket(5)

#8 (root)/include/class.mailfetch.php(659): MailFetcher->fetchEmails()

#9 (root)/include/class.cron.php(23): MailFetcher->run()

#10 (root)/include/class.cron.php(53): Cron->MailFetcher()

#11 (root)/include/api.cron.php(19): Cron->run()

#12 (root)/include/api.cron.php(40): CronApiController->run()

#13 (root)/api/cron.php(24): LocalCronApiController:()

#14 {main}

Sidecutter

Anyone?