V1.12 DB 1064 when opening dashboard

funnyfela

Hey all,
I've had osticket running for about 8 months now. A few months ago started getting DB Error #1064. Have traced to the Dashboard page, as generates an error every time the dashboard is opened. The page is slow to load and the graph never generates. We never use the dashboard page for our setup/use of osticket, but everytime a user clicks it by mistake I get an error email.

Nothing changed, and graph used to work, now just errors. I have moved to a new permanent server from the test setup and same error.

Any help would be awesome ?

osTicket Version v1.12 (a076918)
Web Server Software Apache/2.4.17 (Win32) OpenSSL/1.0.2d PHP/5.6.15
MySQL Version 10.1.9
PHP Version 5.6.15

DB Error #1064
[SELECT A1.topic_id AS topic_id, A3.topic AS topic__topic, A3.flags AS topic__flags, COUNT(CASE WHEN A1.event_id = 1 THEN 1 END) AS Opened, COUNT(CASE WHEN A1.event_id = 4 THEN 1 END) AS Assigned, COUNT(CASE WHEN A1.event_id = 8 THEN 1 END) AS Overdue, COUNT(CASE WHEN A1.event_id = 2 THEN 1 END) AS Closed, COUNT(CASE WHEN A1.event_id = 3 THEN 1 END) AS Reopened, COUNT(CASE WHEN A1.event_id = 14 THEN 1 END) AS Deleted FROM ost_thread_event A1 JOIN ost_thread A2 ON (A1.thread_id = A2.id) LEFT JOIN ost_help_topic A3 ON (A1.topic_id = A3.topic_id) WHERE A1.annulled = 0 AND A1.timestamp BETWEEN FROM_UNIXTIME(1557425755) AND FROM_UNIXTIME(1560104155) AND A2.object_type = 'T' AND A1.dept_id IN (7) AND A1.topic_id > 0 AND A1.topic_id IN () GROUP BY A1.topic_id] 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 ') GROUP BY A1.topic_id' at line 1

---- Backtrace ----
#0 C:\xampp\htdocs\osticket\include\mysqli.php(199): osTicket->logDBError('DB Error #1064', '[SELECT A1.top...') #1 C:\xampp\htdocs\osticket\include\class.orm.php(3455): db_query('SELECT A1.topi...', true, true)
#2 C:\xampp\htdocs\osticket\include\class.orm.php(3502): MySqlExecutor->execute()
#3 C:\xampp\htdocs\osticket\include\class.orm.php(2085): MySqlExecutor->getArray()
#4 C:\xampp\htdocs\osticket\include\class.orm.php(2035): HashArrayIterator->{closure}()
#5 C:\xampp\htdocs\osticket\include\class.orm.php(2014): CallbackSimpleIterator->next()
#6 C:\xampp\htdocs\osticket\include\class.orm.php(2023): CallbackSimpleIterator->rewind()
#7 C:\xampp\htdocs\osticket\include\class.orm.php(1695): CallbackSimpleIterator->valid()
#8 C:\xampp\htdocs\osticket\include\class.orm.php(1705): CachedResultSet->fillTo(2147483647)
#9 C:\xampp\htdocs\osticket\include\class.orm.php(1721): CachedResultSet->asArray()
#10 C:\xampp\htdocs\osticket\include\class.report.php(270): CachedResultSet->getIterator()
#11 C:\xampp\htdocs\osticket\include\staff\dashboard.inc.php(100): OverviewReport->getTabularData('topic')
#12 C:\xampp\htdocs\osticket\scp\dashboard.php(50): require_once('C:\xampp\htdocs...')
#13 {main}

RBGE

funnyfela Are you able to update PHP to a 7.x version? (just not 7.3, as that isn't supported!). I'm using 7.2 and there are no such errors, and while 5.6 is technically supported, I believe 7.x works quite a bit better.

Again, just make sure that x isn't a 3! ?

funnyfela

RBGE I can try that. I didn't know any PHP7 was supported haha. Give me a few days

funnyfela

Server Information
osTicket Version v1.12 (a076918) — Up to date
Web Server Software Apache/2.4.39 (Win64) OpenSSL/1.0.2r PHP/7.1.29
MySQL Version 10.1.39
PHP Version 7.1.29

Same issue occurs, no graph (meh) but errors

ntozier

PHP error messages?
MySQL error messages?

funnyfela

ntozier Its not generating any errors in the log :/

jdelisle

I have the same issue:

PHP 7.2.10
mysql Ver 15.1 Distrib 5.5.60-MariaDB

[SELECT COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THEN A1.ticket_id END) AS q20, COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THEN A1.ticket_id END) AS q21, COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THEN A1.ticket_id END) AS q22, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.dept_id = 7 THEN A1.ticket_id END) AS q23, COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THEN A1.ticket_id END) AS q24, COUNT(DISTINCT CASE WHEN THEN A1.ticket_id END) AS q25, COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THEN A1.ticket_id END) AS q26, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.isanswered = '1' AND A2.state = 'open' AND A1.isanswered = '1' AND A2.id IN (11) THEN A1.ticket_id END) AS q30, 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 (8) OR A1.staff_id IN (9)) AND A2.state = 'open' AND (A1.team_id IN (8) OR A1.staff_id IN (9)) AND A2.state = 'open' AND A1.staff_id IN (9) THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-06-15 00:00:00' AND '2019-06-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 (8) OR A1.staff_id IN (9)) AND A2.state = 'open' AND (A1.team_id IN (8) OR A1.staff_id IN (9)) AND A2.state = 'open' AND NOT A1.staff_id IN (9) THEN A1.ticket_id END) AS q7, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-06-14 00:00:00' AND '2019-06-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 (8) OR A1.staff_id IN (9)) 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-06-10 00:00:00' AND '2019-06-16 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-06-01 00:00:00' AND '2019-06-30 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-04-01 00:00:00' AND '2019-06-30 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 '2019-01-01 00:00:00' AND '2019-12-31 23: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 = 9 OR A5.staff_id = 9 OR A1.team_id IN (8) OR A6.team_id IN (8))) OR A1.dept_id IN (4, 7, 11) OR A7.id IN (4, 7, 11)]

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 q25, COUNT(DISTINCT CASE WHEN A1.dept_id = 7 THE' at line 1 
 
---- Backtrace ---- 
#0 (root)/include/mysqli.php(199): osTicket->logDBError('DB Error #1064', '[SELECT COUNT(D...') 
#1 (root)/include/class.orm.php(3455): db_query('SELECT COUNT(DI...', true, true) 
#2 (root)/include/class.orm.php(3502): MySqlExecutor->execute() 
#3 (root)/include/class.orm.php(2085): MySqlExecutor->getArray() 
#4 (root)/include/class.orm.php(2035): HashArrayIterator->{closure}() 
#5 (root)/include/class.orm.php(2014): CallbackSimpleIterator->next() 
#6 (root)/include/class.orm.php(2023): CallbackSimpleIterator->rewind() 
#7 (root)/include/class.orm.php(1695): CallbackSimpleIterator->valid() 
#8 (root)/include/class.orm.php(1705): CachedResultSet->fillTo(9223372036854775807) 
#9 (root)/include/class.orm.php(1328): CachedResultSet->asArray() 
#10 (root)/include/class.orm.php(1351): QuerySet->all() 
#11 (root)/include/class.search.php(925): QuerySet->one() 
#12 (root)/include/class.search.php(865): SavedQueue::counts(Object(StaffSession), true) 
#13 (root)/include/staff/templates/queue-tickets.tmpl.php(96): SavedQueue->getCount(Object(StaffSession)) 
#14 (root)/scp/tickets.php(526): require_once('/opt/osTicket-1...') 
#15 {main}

ntozier

@jdelisle please do not hijack someone else's thread. Your problem is not the same. Please start your own thread with information about your configuration, environment, etc. (although i can tell you that you need to delete your queue #25 as it apparently has an issue).

@funnyfela I haven't seen this error before. I'll ask the devs to take a look at this thread for you.

As an afterthought, can you upgrade your PHP to 7.2 and MariaDB to 10.3? I'm thinking that it might be an issue with the version of MariaDB but that's a total guess.

KevinTheJedi

@funnyfela

It appears that it cannot grab your Help Topics for the query. Make sure you have Help Topics and make sure they are enabled.

Cheers.

funnyfela

KevinTheJedi THANK YOU SO MUCH ?

I had zero help topics as we don't need them for our use of osticket, but I made a blank one and suddenly page loads quickly, with graph, and no errors.

? ? ?