Hi
After setting up an additional status ("pending"), add agents, departments, custom fields, custom queues and using osTicket so far only for tickets, I receive DB error 1066 + 1054. It happens e.g. when loading queues, but not only. Besides throwing DB errors I don't see any problems arising from the errors.
I found no root entry in the forum, but read some posts with similar reports. I had the impression that in some cases the error could be read from the log entries, so they are added here.
Thank you for any support.
Al
osTicket: v1.14.1 (f1e9e88)
Server: LiteSpeed
MySQL: 5.7.26
PHP: 7.1.33
DB Error #1066
[SELECT COUNT(*) FROM (SELECT A1.ticket_id
FROM ostho_ticket
A1 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 ('Alles' IN NATURAL LANGUAGE MODE) AS relevance
FROM ostho__search
Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('Alles' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN ostho_thread_entry
Z2 ON (Z1.object_type
= 'H' AND Z1.object_id
= Z2.id
) LEFT JOIN ostho_thread
Z3 ON (Z2.thread_id
= Z3.id
AND (Z3.object_type
= 'T' OR Z3.object_type
= 'C')) LEFT JOIN ostho_ticket
Z5 ON (Z1.object_type
= 'T' AND Z1.object_id
= Z5.ticket_id
) LEFT JOIN ostho_user
Z6 ON (Z6.id
= Z1.object_id
and Z1.object_type
= 'U') LEFT JOIN ostho_organization
Z7 ON (Z7.id
= Z1.object_id
AND Z7.id
= Z6.org_id
AND Z1.object_type
= 'O') LEFT JOIN ostho_ticket
Z8 ON (Z8.user_id
= Z6.id
)) Z1 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 ('Alles' IN NATURAL LANGUAGE MODE) AS relevance
FROM ostho__search
Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('Alles' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN ostho_thread_entry
Z2 ON (Z1.object_type
= 'H' AND Z1.object_id
= Z2.id
) LEFT JOIN ostho_thread
Z3 ON (Z2.thread_id
= Z3.id
AND (Z3.object_type
= 'T' OR Z3.object_type
= 'C')) LEFT JOIN ostho_ticket
Z5 ON (Z1.object_type
= 'T' AND Z1.object_id
= Z5.ticket_id
) LEFT JOIN ostho_user
Z6 ON (Z6.id
= Z1.object_id
and Z1.object_type
= 'U') LEFT JOIN ostho_organization
Z7 ON (Z7.id
= Z1.object_id
AND Z7.id
= Z6.org_id
AND Z1.object_type
= 'O') LEFT JOIN ostho_ticket
Z8 ON (Z8.user_id
= Z6.id
)) Z1 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 ('Alles' IN NATURAL LANGUAGE MODE) AS relevance
FROM ostho__search
Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('Alles' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN ostho_thread_entry
Z2 ON (Z1.object_type
= 'H' AND Z1.object_id
= Z2.id
) LEFT JOIN ostho_thread
Z3 ON (Z2.thread_id
= Z3.id
AND (Z3.object_type
= 'T' OR Z3.object_type
= 'C')) LEFT JOIN ostho_ticket
Z5 ON (Z1.object_type
= 'T' AND Z1.object_id
= Z5.ticket_id
) LEFT JOIN ostho_user
Z6 ON (Z6.id
= Z1.object_id
and Z1.object_type
= 'U') LEFT JOIN ostho_organization
Z7 ON (Z7.id
= Z1.object_id
AND Z7.id
= Z6.org_id
AND Z1.object_type
= 'O') LEFT JOIN ostho_ticket
Z8 ON (Z8.user_id
= Z6.id
)) Z1 WHERE A1.ticket_id
= Z1.ticket_id
AND A1.ticket_id
= Z1.ticket_id
AND A1.ticket_id
= Z1.ticket_id
ORDER BY Z1.relevance , Z1.relevance , Z1.relevance ) __] Not unique table/alias: 'Z1'
---- Ablaufverfolgung ----
#0 (root)/include/mysqli.php(200): osTicket->logDBError('DB Error #1066', '[SELECT COUNT(...')
#1 (root)/include/class.orm.php(3459): db_query('SELECT COUNT()...', true, true)
#2 (root)/include/class.orm.php(3515): MySqlExecutor->execute()
#3 (root)/include/class.orm.php(2945): MySqlExecutor->getRow()
#4 (root)/include/class.orm.php(1380): MySqlCompiler->compileCount(Object(QuerySet))
#5 (root)/include/class.search.php(897): QuerySet->count()
#6 (root)/include/class.search.php(976): SavedQueue->getTotal()
#7 (root)/scp/autocron.php(43): SavedQueue::counts(Object(StaffSession), false)
#8 {main}
DB Error #1054
[SELECT COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A2.state
= 'open' AND A1.dept_id
= 4 AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q15
, COUNT(DISTINCT CASE WHEN A1.ticket_id
= Z1.ticket_id
AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q23
, COUNT(DISTINCT CASE WHEN A1.ticket_id
= Z1.ticket_id
AND A1.ticket_id
= Z1.ticket_id
AND A1.ticket_id
= Z1.ticket_id
AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q24
, COUNT(DISTINCT CASE WHEN A1.ticket_id
= Z1.ticket_id
THEN A1.ticket_id
END) AS q25
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 6 AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q26
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 6 AND A1.dept_id
= 6 AND A2.state
= 'open' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q27
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 8 AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q28
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 6 AND A1.dept_id
= 6 AND A2.id
IN (6) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q29
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 6 AND A1.dept_id
= 6 AND A2.id
IN (2) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q30
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 8 AND A1.dept_id
= 8 AND A2.id
IN (1) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q31
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 8 AND A1.dept_id
= 8 AND A2.id
IN (6) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q32
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 8 AND A1.dept_id
= 8 AND A2.id
IN (2) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q33
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 4 AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q16
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 4 AND A1.dept_id
= 4 AND A2.state
= 'open' AND A2.id
IN (1) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q17
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 4 AND A1.dept_id
= 4 AND A2.id
IN (6) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q18
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 4 AND A1.dept_id
= 4 AND A2.id
IN (2) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q19
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 4 AND A1.dept_id
= 4 AND A2.state
= 'closed' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q20
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 4 AND A1.dept_id
= 4 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 q21
, COUNT(DISTINCT CASE WHEN A1.dept_id
= 4 AND A1.dept_id
= 4 AND A1.closed
BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59' AND A1.ticket_id
= Z1.ticket_id
AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q22
, 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 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 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.staff_id
IN (1) AND A2.state
= 'open' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q5
, COUNT(DISTINCT CASE WHEN A1.staff_id
IN (1) AND A2.state
= 'open' AND 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 A1.staff_id
IN (1) AND A2.state
= 'open' AND 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 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-01-02 00:00:00' AND '2020-01-02 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q9
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2020-01-01 00:00:00' AND '2020-01-01 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q10
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2019-12-30 00:00:00' AND '2020-01-05 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q11
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2020-01-01 00:00:00' AND '2020-01-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-01-01 00:00:00' AND '2020-03-31 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 ostho_ticket
A1 JOIN ostho_ticket_status
A2 ON (A1.status_id
= A2.id
) LEFT JOIN ostho_thread
A3 ON (A3.object_type
= 'T' AND A1.ticket_id
= A3.object_id
) LEFT JOIN ostho_thread_referral
A4 ON (A3.id
= A4.thread_id
) LEFT JOIN ostho_staff
A5 ON (A4.object_type
= 'S' AND A4.object_id
= A5.staff_id
) LEFT JOIN ostho_thread
A6 ON (A1.ticket_id
= A6.object_id
AND A6.object_type
= 'C') LEFT JOIN ostho_thread_referral
A7 ON (A6.id
= A7.thread_id
) LEFT JOIN ostho_staff
A8 ON (A7.object_type
= 'S' AND A7.object_id
= A8.staff_id
) LEFT JOIN ostho_department
B0 ON (A4.object_type
= 'D' AND A4.object_id
= B0.id
) LEFT JOIN ostho_department
B1 ON (A7.object_type
= 'D' AND A7.object_id
= B1.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 (A2.state
IN ('open', 'closed') AND (A1.dept_id
IN (3, 4, 5, 6, 7, 8) OR B0.id
IN (3, 4, 5, 6, 7, 8))) OR A6.object_type
= 'C' AND B1.id
IN (3, 4, 5, 6, 7, 8))] Unknown column 'Z1.ticket_id' in 'field list'
---- Ablaufverfolgung ----
#0 (root)/include/mysqli.php(200): osTicket->logDBError('DB Error #1054', '[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)/scp/autocron.php(43): SavedQueue::counts(Object(StaffSession), false)
#13 {main}