Hi

I'm currently using 1.12.3, but I've been upgrading the system for a long time, about 6 years or so.
I've searched a few fixes and tried without result for this error I keep getting on my email with no resolve.
Can someone give me a hand?
Thx

[SELECT COUNT(DISTINCT CASE WHEN THEN A1.ticket_id END) AS q15, COUNT(DISTINCT CASE WHEN A2.state = 'open' THEN A1.ticket_id END) AS q1, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '0' THEN A1.ticket_id END) AS q2, COUNT(DISTINCT CASE WHEN (A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A1.staff_id IN (13)) AND A2.state = 'open' AND (A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A1.staff_id IN (13)) AND A2.state = 'open' AND A1.staff_id IN (13) THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-15 01:00:00' AND '2020-01-16 00:59:59' THEN A1.ticket_id END) AS q9, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' THEN A1.ticket_id END) AS q3, COUNT(DISTINCT CASE WHEN (A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A1.staff_id IN (13)) AND A2.state = 'open' AND (A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A1.staff_id IN (13)) AND A2.state = 'open' AND NOT A1.staff_id IN (13) THEN A1.ticket_id END) AS q7, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-14 01:00:00' AND '2020-01-15 00:59:59' THEN A1.ticket_id END) AS q10, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isoverdue = '1' THEN A1.ticket_id END) AS q4, COUNT(DISTINCT CASE WHEN (A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A1.staff_id IN (13)) AND A2.state = 'open' THEN A1.ticket_id END) AS q5, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-13 01:00:00' AND '2020-01-20 00:59:59' THEN A1.ticket_id END) AS q11, COUNT(DISTINCT CASE WHEN A2.state = 'closed' THEN A1.ticket_id END) AS q8, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-01 01:00:00' AND '2020-02-01 00:59:59' THEN A1.ticket_id END) AS q12, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-01 01:00:00' AND '2020-04-01 01:59:59' 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 01:00:00' AND '2021-01-01 00:59:59' 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_team A6 ON (A4.object_type = 'E' AND A4.object_id = A6.team_id) LEFT JOIN ost_department A7 ON (A4.object_type = 'D' AND A4.object_id = A7.id) WHERE (A2.state = 'open' AND (A1.staff_id = 13 OR A5.staff_id = 13 OR A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A6.team_id IN (1, 2, 3, 5, 6, 7, 8, 9))) OR (A2.state IN ('open', 'closed') AND (A1.dept_id IN (14, 7, 8, 6, 5, 9, 10, 11, 13) OR A7.id IN (14, 7, 8, 6, 5, 9, 10, 11, 13)))]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL 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<br />
<br />
---- Backtrace ----<br />
#0 (raiz)/include/mysqli.php(199): osTicket->logDBError('DB Error #1064', '[SELECT COUNT(D...')<br />
#1 (raiz)/include/class.orm.php(3455): db_query('SELECT COUNT(DI...', true, true)<br />
#2 (raiz)/include/class.orm.php(3502): MySqlExecutor->execute()<br />
#3 (raiz)/include/class.orm.php(2085): MySqlExecutor->getArray()<br />
#4 (raiz)/include/class.orm.php(2035): HashArrayIterator->{closure}()<br />
#5 (raiz)/include/class.orm.php(2014): CallbackSimpleIterator->next()<br />
#6 (raiz)/include/class.orm.php(2023): CallbackSimpleIterator->rewind()<br />
#7 (raiz)/include/class.orm.php(1695): CallbackSimpleIterator->valid()<br />
#8 (raiz)/include/class.orm.php(1705): CachedResultSet->fillTo(9223372036854775807)<br />
#9 (raiz)/include/class.orm.php(1328): CachedResultSet->asArray()<br />
#10 (raiz)/include/class.orm.php(1351): QuerySet->all()<br />
#11 (raiz)/include/class.search.php(925): QuerySet->one()<br />
#12 (raiz)/include/ajax.search.php(397): SavedQueue::counts(Object(StaffSession), true, Array)<br />
#13 (raiz)/include/class.dispatcher.php(145): SearchAjaxAPI->collectQueueCounts()<br />
#14 (raiz)/include/class.dispatcher.php(38): UrlMatcher->dispatch('counts', Array)<br />
#15 (raiz)/include/class.dispatcher.php(120): Dispatcher->resolve('counts', Array)<br />
#16 (raiz)/include/class.dispatcher.php(38): UrlMatcher->dispatch('/queue/counts', NULL)<br />
#17 (raiz)/scp/ajax.php(289): Dispatcher->resolve('/queue/counts')<br />
#18 {main}

@vasco.leiria#112723 You have an error in your SQL syntax; check the manual that corresponds to your MySQL 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

osTicket only comes with 14 custom queues, so this makes me think that your q15 is custom and has a problem. I would write down what it is (or screen shot it) and delete it.

I can't find it. I dont have any created. Maybe it is a leftover of a previous version.What table in the DB has this?

Queues are located in the table ost_queue.

I deleted all records and still get the same error
SELECT COUNT(DISTINCT CASE WHEN THEN A1.ticket_id END) AS q15, COUNT(DISTINCT CASE WHEN A2.state = 'open' THEN A1.ticket_id END) AS q1, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '0' THEN A1.ticket_id END) AS q2, COUNT(DISTINCT CASE WHEN (A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A1.staff_id IN (13)) AND A2.state = 'open' AND (A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A1.staff_id IN (13)) AND A2.state = 'open' AND A1.staff_id IN (13) THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-20 01:00:00' AND '2020-01-21 00:59:59' THEN A1.ticket_id END) AS q9, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' THEN A1.ticket_id END) AS q3, COUNT(DISTINCT CASE WHEN (A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A1.staff_id IN (13)) AND A2.state = 'open' AND (A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A1.staff_id IN (13)) AND A2.state = 'open' AND NOT A1.staff_id IN (13) THEN A1.ticket_id END) AS q7, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-19 01:00:00' AND '2020-01-20 00:59:59' THEN A1.ticket_id END) AS q10, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isoverdue = '1' THEN A1.ticket_id END) AS q4, COUNT(DISTINCT CASE WHEN (A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A1.staff_id IN (13)) AND A2.state = 'open' THEN A1.ticket_id END) AS q5, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-20 01:00:00' AND '2020-01-27 00:59:59' THEN A1.ticket_id END) AS q11, COUNT(DISTINCT CASE WHEN A2.state = 'closed' THEN A1.ticket_id END) AS q8, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-01 01:00:00' AND '2020-02-01 00:59:59' THEN A1.ticket_id END) AS q12, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-01-01 01:00:00' AND '2020-04-01 01:59:59' 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 01:00:00' AND '2021-01-01 00:59:59' 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_team A6 ON (A4.object_type = 'E' AND A4.object_id = A6.team_id) LEFT JOIN ost_department A7 ON (A4.object_type = 'D' AND A4.object_id = A7.id) WHERE (A2.state = 'open' AND (A1.staff_id = 13 OR A5.staff_id = 13 OR A1.team_id IN (1, 2, 3, 5, 6, 7, 8, 9) OR A6.team_id IN (1, 2, 3, 5, 6, 7, 8, 9))) OR (A2.state IN ('open', 'closed') AND (A1.dept_id IN (14, 7, 8, 6, 5, 9, 10, 11, 13) OR A7.id IN (14, 7, 8, 6, 5, 9, 10, 11, 13)))]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL 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<br />
<br />
---- Backtrace ----<br />
#0 (raiz)/include/mysqli.php(199): osTicket->logDBError('DB Error #1064', '[SELECT COUNT(D...')<br />
#1 (raiz)/include/class.orm.php(3455): db_query('SELECT COUNT(DI...', true, true)<br />
#2 (raiz)/include/class.orm.php(3502): MySqlExecutor->execute()<br />
#3 (raiz)/include/class.orm.php(2085): MySqlExecutor->getArray()<br />
#4 (raiz)/include/class.orm.php(2035): HashArrayIterator->{closure}()<br />
#5 (raiz)/include/class.orm.php(2014): CallbackSimpleIterator->next()<br />
#6 (raiz)/include/class.orm.php(2023): CallbackSimpleIterator->rewind()<br />
#7 (raiz)/include/class.orm.php(1695): CallbackSimpleIterator->valid()<br />
#8 (raiz)/include/class.orm.php(1705): CachedResultSet->fillTo(9223372036854775807)<br />
#9 (raiz)/include/class.orm.php(1328): CachedResultSet->asArray()<br />
#10 (raiz)/include/class.orm.php(1351): QuerySet->all()<br />
#11 (raiz)/include/class.search.php(925): QuerySet->one()<br />
#12 (raiz)/scp/autocron.php(51): SavedQueue::counts(Object(StaffSession), false)<br />
#13 {main}

If you are still getting that error then q15 wasn't deleted.

6 days later

I deleted them again, I think this time it was solved.

Glad to hear it. Would you like me to leave this open until you are sure that the issue is resolved?

The error returned today. Is it recreating?

[INSERT INTO ost_staff_dept_access SET dept_id = '9', role_id = '2', flags = 1]

Duplicate entry '0-9' for key 'PRIMARY'<br />
<br />
---- Backtrace ----<br />
#0 (raiz)/include/mysqli.php(200): osTicket->logDBError('DB Error #1062', '[INSERT INTO o...')<br />
#1 (raiz)/include/class.orm.php(3457): db_query('INSERT INTOos...', true, true)<br />
#2 (raiz)/include/class.orm.php(658): MySqlExecutor->execute()<br />
#3 (raiz)/include/class.staff.php(1219): VerySimpleModel->save()<br />
#4 (raiz)/include/class.staff.php(1129): Staff->updateAccess(Array, Array)<br />
#5 (raiz)/scp/staff.php(47): Staff->update(Array, Array)<br />
#6 {main}

This error seems to indicate that you are trying to add someone to a dept that is already in the dept.

Did you try to add a Agent to a department?
You can look at the Agents tab in the Admin Panel and mouse over or click on the agent that you tried to add.
The url will look something like supprt.domain.tld/scp/staff.php?id=##.
The ## is their id number.
Then you can use this query will display your table contents.
SELECT * FROM ost_staff_dept_access WHERE staff_id='##';

If it is a dupe then then you would see an entry in the table that looks something like this:
## | 9 | 2 | 1

This error is then just a user error? I shouldn't worry?
I found out this error also in the same day

SELECT COUNT(*) FROM (SELECT A1.ticket_id 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_team A6 ON (A4.object_type = 'E' AND A4.object_id = A6.team_id) LEFT JOIN ost_department A7 ON (A4.object_type = 'D' AND A4.object_id = A7.id) JOIN (SELECT COALESCE(Z3.object_id, Z5.ticket_id, Z8.ticket_id) as ticket_id, Z1.relevance FROM (SELECT Z1.object_id, Z1.object_type, MATCH (Z1.title, Z1.content) AGAINST ('´cvb' IN NATURAL LANGUAGE MODE) AS relevance FROM ost__search Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('´cvb' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN ost_thread_entry Z2 ON (Z1.object_type = 'H' AND Z1.object_id = Z2.id) LEFT JOIN ost_thread Z3 ON (Z2.thread_id = Z3.id AND Z3.object_type = 'T') LEFT JOIN ost_ticket Z5 ON (Z1.object_type = 'T' AND Z1.object_id = Z5.ticket_id) LEFT JOIN ost_user Z6 ON (Z6.id = Z1.object_id and Z1.object_type = 'U') LEFT JOIN ost_organization Z7 ON (Z7.id = Z1.object_id AND Z7.id = Z6.org_id AND Z1.object_type = 'O') LEFT JOIN ost_ticket Z8 ON (Z8.user_id = Z6.id)) Z1 WHERE ((A2.state = 'open' AND (A1.staff_id = 15 OR A5.staff_id = 15 OR A1.team_id IN (1, 2, 3, 5, 9, 6, 7, 8) OR A6.team_id IN (1, 2, 3, 5, 9, 6, 7, 8))) OR A1.dept_id IN (5) OR A7.id IN (5)) AND A1.ticket_id = Z1.ticket_id ORDER BY Z1.relevance LIMIT 25) ] Table 'osticket.ostsearch' doesn't exist

---- Backtrace ----
#0 (raiz)/include/mysqli.php(200): osTicket->logDBError('DB Error #1146', '[SELECT COUNT(...')
#1 (raiz)/include/class.orm.php(3457): db_query('SELECT COUNT()...', true, true)
#2 (raiz)/include/class.orm.php(3513): MySqlExecutor->execute()
#3 (raiz)/include/class.orm.php(2943): MySqlExecutor->getRow()
#4 (raiz)/include/class.orm.php(1374): MySqlCompiler->compileCount(Object(QuerySet))
#5 (raiz)/include/ajax.tickets.php(66): QuerySet->count()
#6 (raiz)/include/class.dispatcher.php(145): TicketsAjaxAPI->lookup()
#7 (raiz)/include/class.dispatcher.php(38): UrlMatcher->dispatch('lookup', Array)
#8 (raiz)/include/class.dispatcher.php(120): Dispatcher->resolve('lookup', Array)
#9 (raiz)/include/class.dispatcher.php(38): UrlMatcher->dispatch('/tickets/lookup', NULL)
#10 (raiz)/scp/ajax.php(289): Dispatcher->resolve('/tickets/lookup')
#11 {main}

The query has ost__search in it.
The error says osticket.ostsearch.

It looks like your DB is stripping the __ or something. Which isn't something that I have ever seen before... What DB software/ver are you using?

here's the system info

Server Information
osTicket Version v1.12.5 (933bb1f) — Up to date
Web Server Software Apache/2.2.15 (CentOS)
MySQL Version 5.5.62
PHP Version 7.2.25

Write a Reply...