Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

DB error after reset dashboard and statistics ?

Hi,

I wanted to reset the dashboard and statistics, so the system could go live. But, there seems to be few issues now:
 
1. I tried the forum discussions and did truncate the ost_ticket_event table and it did reset the dashboard chart, but now I'm seeing instead of ticket numbers it shows 'NaN' which is very weird..Now I keep getting, DB error# 1582 and 1064 error alerts after every 30 seconds

2. I also want to clear the statistics at the bottom of dashboard page, as it keeps showing numbers which are not true.

Can somebody help in this regard ?
Tagged:

Comments

  • Sorry, just to post the error for any one of you to refer:

    DB Error # 1064

    [SELECT CONCAT_WS(' ', T1.firstname, T1.lastname) as name,

                SUM(CASE WHEN A1.state = \"created\" THEN 1 ELSE 0 END) AS Opened,

                SUM(CASE WHEN A1.state = \"assigned\" THEN 1 ELSE 0 END) AS Assigned,

                SUM(CASE WHEN A1.state = \"overdue\" THEN 1 ELSE 0 END) AS Overdue,

                SUM(CASE WHEN A1.state = \"closed\" THEN 1 ELSE 0 END) AS Closed,

                SUM(CASE WHEN A1.state = \"reopened\" THEN 1 ELSE 0 END) AS Reopened

                FROM ost_staff T1

                    LEFT JOIN ost_ticket_event A1 ON (A1.staff_id=T1.staff_id AND NOT annulled AND (A1.timestamp BETWEEN FROM_UNIXTIME(1386847170) AND FROM_UNIXTIME(1389525570)))

                    LEFT JOIN ost_staff S1 ON (S1.staff_id=A1.staff_id)

                WHERE T1.staff_id=S1.staff_id

                          AND

                          (T1.staff_id=1)

                GROUP BY T1.staff_id

                ORDER BY name]

     

    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 '\"created\" THEN 1 ELSE 0 END) AS Opened,

                SUM(CASE WHEN A1.state = \' at line 2<br /> <br />

    ---- Backtrace ----<br />

    #0 C:\xampp\htdocs\CrownTechSupport\include\mysqli.php(126): osTicket->logDBError('DB Error #1064', '[SELECT CONCAT_...')<br />

    #1 C:\xampp\htdocs\CrownTechSupport\include\ajax.reports.php(133): db_query('SELECT CONCAT_W...')<br />

    #2 C:\xampp\htdocs\CrownTechSupport\include\ajax.reports.php(159): OverviewReportAjaxAPI->getData()<br />

    #3 [internal function]: OverviewReportAjaxAPI->getTabularData()<br />

    #4 C:\xampp\htdocs\CrownTechSupport\include\class.dispatcher.php(140): call_user_func_array(Array, Array)<br />

    #5 C:\xampp\htdocs\CrownTechSupport\include\class.dispatcher.php(38): UrlMatcher->dispatch('table', Array)<br />

    #6 C:\xampp\htdocs\CrownTechSupport\include\class.dispatcher.php(118): Dispatcher->resolve('table', Array)<br />

    #7 C:\xampp\htdocs\CrownTechSupport\include\class.dispatcher.php(38): UrlMatcher->dispatch('/report/overvie...', NULL)<br />

    #8 C:\xampp\htdocs\CrownTechSupport\scp\ajax.php(99): Dispatcher->resolve('/report/overvie...')<br />

    #9 {main}



  • DB ERROR # 1582

    [SELECT T1.dept_name,

                    COUNT(*)-COUNT(NULLIF(A1.state, "created")) AS Opened,

                    COUNT(*)-COUNT(NULLIF(A1.state, "assigned")) AS Assigned,

                    COUNT(*)-COUNT(NULLIF(A1.state, "overdue")) AS Overdue,

                    COUNT(*)-COUNT(NULLIF(A1.state, "closed")) AS Closed,

                    COUNT(*)-COUNT(NULLIF(A1.state, "reopened")) AS Reopened

                FROM ost_department T1

                    LEFT JOIN ost_ticket_event A1

                        ON (A1.dept_id=T1.dept_id

                             AND NOT annulled

                             AND (A1.timestamp BETWEEN FROM_UNIXTIME() AND FROM_UNIXTIME(1389526531)))

                    LEFT JOIN ost_staff S1 ON (S1.staff_id=A1.staff_id)

                WHERE T1.dept_id IN (1)

                GROUP BY T1.dept_id

                ORDER BY T1.dept_name]

     

    Incorrect parameter count in the call to native function 'FROM_UNIXTIME'<br /> <br />

    ---- Backtrace ----<br />

    #0 D:\Web\Support\include\mysqli.php(126): osTicket->logDBError('DB Error #1582', '[SELECT T1.dept...')<br />

    #1 D:\Web\Support\include\ajax.reports.php(136): db_query('SELECT T1.dept_...')<br />

    #2 D:\Web\Support\include\ajax.reports.php(162): OverviewReportAjaxAPI->getData()<br />

    #3 [internal function]: OverviewReportAjaxAPI->getTabularData()<br />

    #4 D:\Web\Support\include\class.dispatcher.php(140): call_user_func_array(Array, Array)<br />

    #5 D:\Web\Support\include\class.dispatcher.php(38): UrlMatcher->dispatch('table', Array)<br />

    #6 D:\Web\Support\include\class.dispatcher.php(118): Dispatcher->resolve('table', Array)<br />

    #7 D:\Web\Support\include\class.dispatcher.php(38): UrlMatcher->dispatch('/report/overvie...', NULL)<br />

    #8 D:\Web\Support\scp\ajax.php(99): Dispatcher->resolve('/report/overvie...')<br />

    #9 {main}

  • Hey guys,

    DB ERROR # 1582 on v1.8.0.1

    im not sure what actually caused these errors. wasnt exactly doing anything apart from we did assign a job to someone a minute earlier. i cant see that anything is affected

    These came through in about12 different emails: (also see attached)


    [SELECT T1.dept_name,

                    FORMAT(AVG(DATEDIFF(T2.closed, T2.created)),1) AS ServiceTime

                FROM ost_department T1

                    LEFT JOIN ost_ticket T2 ON (T2.dept_id=T1.dept_id)

                    LEFT JOIN ost_staff S1 ON (S1.staff_id=T2.staff_id)

                WHERE T1.dept_id IN (1) AND T2.closed BETWEEN FROM_UNIXTIME() AND FROM_UNIXTIME(1389748881)

                GROUP BY T1.dept_id

                ORDER BY T1.dept_name]

     

    Incorrect parameter count in the call to native function 'FROM_UNIXTIME'<br /> <br />

    ---- Backtrace ----<br />

    #0 (root)/include/mysqli.php(126): osTicket->logDBError('DB Error #1582', '[SELECT T1.dept...')<br />

    #1 (root)/include/ajax.reports.php(136): db_query('SELECT T1.dept_...')<br />

    #2 (root)/include/ajax.reports.php(162): OverviewReportAjaxAPI->getData()<br />

    #3 [internal function]: OverviewReportAjaxAPI->getTabularData()<br />

    #4 (root)/include/class.dispatcher.php(140): call_user_func_array(Array, Array)<br />

    #5 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch('table', Array)<br />

    #6 (root)/include/class.dispatcher.php(118): Dispatcher->resolve('table', Array)<br />

    #7 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch('/report/overvie...', NULL)<br />

    #8 (root)/scp/ajax.php(99): Dispatcher->resolve('/report/overvie...')<br />

    #9 {main}

    imageimageimageimageimageimageimageimageimage
    DBError.pdf
    73K
  • I thought you'd provide a solution or workaround :P
  • I've asked the devs to take a look at this.
  • I too am having a similar problem - the dashboard is OK until I try to select a date range, and then I get database errors #1582 in the logs, and the dashboard then shows no statistics. This also started for me after truncating the above mentioned table. Any insight on how to fix this would be greatly appreciated!
  • Incorrect parameter count in the call to native function 'FROM_UNIXTIME'  <- seesm to be the problem?
  • Appreciate your reply guys, but I'm still stuck at the same issue.

    So, any updated from devs ?

    I think , if there's a button which can refresh the entire dashboard view to NIL (should only be done by Admin), then it will make it good to go.
Sign In or Register to comment.