Hi!
Just upgrated my system:
Here are the new specs:
OSTIcket. 1.12.4
PHP 7.3
MySQL 5.6.41-84.1
Apache 2.4.39
I migrated from 1.10.x. And now I'm receiving this email reporting DB Error #1064.
[SELECT COUNT(DISTINCT CASE WHEN THEN A1.ticket_id
END) AS q15
, COUNT(DISTINCT CASE WHEN THEN A1.ticket_id
END) AS q16
, 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 (2, 3) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND (A1.team_id
IN (2, 3) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND A1.staff_id
IN (1) THEN A1.ticket_id
END) AS q6
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2019-10-15 00:00:00' AND '2019-10-15 23: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 (2, 3) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND (A1.team_id
IN (2, 3) OR A1.staff_id
IN (1)) AND A2.state
= 'open' AND NOT A1.staff_id
IN (1) THEN A1.ticket_id
END) AS q7
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2019-10-14 00:00:00' AND '2019-10-14 23: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 (2, 3) OR A1.staff_id
IN (1)) 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 '2019-10-14 00:00:00' AND '2019-10-20 23: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 '2019-10-01 00:00:00' AND '2019-10-31 23:59:59' THEN A1.ticket_id
END) AS q12
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2019-10-01 00:00:00' AND '2019-12-31 23:59:59' THEN A1.ticket_id
END) AS q13
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2018-12-31 23:00:00' AND '2019-12-31 23:59:59' THEN A1.ticket_id
END) AS q14
FROM ostqf_ticket
A1 JOIN ostqf_ticket_status
A2 ON (A1.status_id
= A2.id
) LEFT JOIN ostqf_thread
A3 ON (A3.object_type
= 'T' AND A1.ticket_id
= A3.object_id
) LEFT JOIN ostqf_thread_referral
A4 ON (A3.id
= A4.thread_id
) LEFT JOIN ostqf_staff
A5 ON (A4.object_type
= 'S' AND A4.object_id
= A5.staff_id
) LEFT JOIN ostqf_team
A6 ON (A4.object_type
= 'E' AND A4.object_id
= A6.team_id
) LEFT JOIN ostqf_department
A7 ON (A4.object_type
= 'D' AND A4.object_id
= A7.id
) WHERE (A2.state
= 'open' AND (A1.staff_id
= 1 OR A5.staff_id
= 1 OR A1.team_id
IN (2, 3) OR A6.team_id
IN (2, 3))) OR (A2.state
IN ('open', 'closed') AND (A1.dept_id
IN (3, 4) OR A7.id
IN (3, 4)))]
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 THEN A1.ticket_id
' 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}
I know this is an SQL error. Can anyone guide me in how to fix this problem? I don't understand the information reported.
Please let me know if I need to provide any other information!
Thanksful for any help!
Fabio.