After upgrade to osTicket (v1.14.1) getting DB Error #1064

[fredotech]

Prerequisites

• [ Only have one instance] Can you reproduce the problem in a fresh installation of the "develop" branch?
• [Yes ] Do you have any errors in the PHP error log, or javascript console?
• [Yes ] Did you check the osTicket forums?
• [Yes ] Did you perform a cursory search to see if your bug or enhancement is already reported?

For more information on how to write a good bug report

Description

After upgrade getting error DB Error #1064

Steps to Reproduce

1. Upgraded to version 1.14.1
2. then started to get error when working with tickets

Expected behavior: [What you expected to happen]
Should be able to use the application without generating DB errors

Actual behavior: [What actually happened]
Getting emails and log entries for the DB Error #1064

Versions

Admin panel -> Dashboard -> Information which also additionally gives you information about your server.

Also, please include the OS and what version of the OS you're running. As well as your browser and browser version.

Error:
[SELECT COUNT(DISTINCT CASE WHEN THEN A1.ticket_id END) AS q15, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q1, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '0' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q2, COUNT(DISTINCT CASE WHEN (A1.team_id IN (1, 2, 3) OR A1.staff_id IN (1)) AND A2.state = 'open' AND (A1.team_id IN (1, 2, 3) OR A1.staff_id IN (1)) AND A2.state = 'open' AND A1.staff_id IN (1) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-05-19 00:00:00' AND '2020-05-19 23:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q9, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q3, COUNT(DISTINCT CASE WHEN (A1.team_id IN (1, 2, 3) OR A1.staff_id IN (1)) AND A2.state = 'open' AND (A1.team_id IN (1, 2, 3) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q7, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-05-18 00:00:00' AND '2020-05-18 23:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q10, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isoverdue = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q4, COUNT(DISTINCT CASE WHEN (A1.team_id IN (1, 2, 3) OR A1.staff_id IN (1)) AND A2.state = 'open' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q5, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-05-18 00:00:00' AND '2020-05-24 23:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q11, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q8, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-05-01 00:00:00' AND '2020-05-31 23:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q12, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-04-01 00:00:00' AND '2020-06-30 23:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q13, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q14 FROM ost_ticket A1 JOIN ost_ticket_status A2 ON (A1.status_id = A2.id) LEFT JOIN ost_thread A3 ON (A3.object_type = 'T' AND A1.ticket_id = A3.object_id) LEFT JOIN ost_thread_referral A4 ON (A3.id = A4.thread_id) LEFT JOIN ost_staff A5 ON (A4.object_type = 'S' AND A4.object_id = A5.staff_id) LEFT JOIN ost_thread A6 ON (A1.ticket_id = A6.object_id AND A6.object_type = 'C') LEFT JOIN ost_thread_referral A7 ON (A6.id = A7.thread_id) LEFT JOIN ost_staff A8 ON (A7.object_type = 'S' AND A7.object_id = A8.staff_id) LEFT JOIN ost_team B0 ON (A4.object_type = 'E' AND A4.object_id = B0.team_id) LEFT JOIN ost_team B1 ON (A7.object_type = 'E' AND A7.object_id = B1.team_id) LEFT JOIN ost_department B2 ON (A4.object_type = 'D' AND A4.object_id = B2.id) LEFT JOIN ost_department B3 ON (A7.object_type = 'D' AND A7.object_id = B3.id) WHERE ((A2.state = 'open' AND (A1.staff_id = 1 OR A5.staff_id = 1 OR A6.object_type = 'C' AND A8.staff_id = 1 OR A1.team_id IN (1, 2, 3) OR B0.team_id IN (1, 2, 3) OR A6.object_type = 'C' AND B1.team_id IN (1, 2, 3))) OR (A2.state IN ('open', 'closed') AND (A1.dept_id IN (1, 2, 3) OR B2.id IN (1, 2, 3))) OR A6.object_type = 'C' AND B3.id IN (1, 2, 3))]

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'THEN A1.ticket_id END) AS q15, COUNT(DISTINCT CASE WHEN A2.state = 'open' ' at line 1



---- Backtrace ----

#0 (root)/include/mysqli.php(200): osTicket->logDBError('DB Error #1064', '[SELECT COUNT(D...')

#1 (root)/include/class.orm.php(3459): db_query('SELECT COUNT(DI...', true, true)

#2 (root)/include/class.orm.php(3506): MySqlExecutor->execute()

#3 (root)/include/class.orm.php(2093): MySqlExecutor->getArray()

#4 (root)/include/class.orm.php(2043): HashArrayIterator->{closure}()

#5 (root)/include/class.orm.php(2022): CallbackSimpleIterator->next()

#6 (root)/include/class.orm.php(2031): CallbackSimpleIterator->rewind()

#7 (root)/include/class.orm.php(1703): CallbackSimpleIterator->valid()

#8 (root)/include/class.orm.php(1713): CachedResultSet->fillTo(9223372036854775807)

#9 (root)/include/class.orm.php(1336): CachedResultSet->asArray()

#10 (root)/include/class.orm.php(1359): QuerySet->all()

#11 (root)/include/class.search.php(973): QuerySet->one()

#12 (root)/include/ajax.search.php(397): SavedQueue::counts(Object(StaffSession), true, Array)

#13 (root)/include/class.dispatcher.php(145): SearchAjaxAPI->collectQueueCounts()

#14 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch('counts', Array)

#15 (root)/include/class.dispatcher.php(120): Dispatcher->resolve('counts', Array)

#16 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch('/queue/counts', NULL)

#17 (root)/scp/ajax.php(308): Dispatcher->resolve('/queue/counts')

#18 {main}

[JediKev]

@fredotech

Login to your database, go to the ost_queue table, copy the record with an id of 15 (as a backup), and delete that record. Once deleted you shouldn't receive the error any longer. You can paste the criteria of the record here so we can take a look at it.

Cheers.

[fredotech]

I was able to resolve the issue by deleted custom Searches I had..