Multiple Form Display Bug

dendi23

ntozier

Please help us to help you by reading and following the posting guidelines located in this thread: Please read before requesting assistance. The more information you give us the better we will be able to assist you. Thank you.

Environment details?
Version of osTicket?
Just giving us a screen shot doesn't really tell us what your problem is or how you created the problem.

dendi23

There were multiple forms displayed in one ticket. This happened when we restored our db backup into another server.

    osTicket Version 	v1.12.3

Web Server Software Apache/2.4.6 (CentOS) OpenSSL/1.0.2k-fips PHP/7.2.24
MySQL Version 5.7.28
PHP Version 7.2.24

[SELECT COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND NOT (A1.team_id = 0 AND A1.staff_id = 0) AND A1.ticket_id = Z1.ticket_id THEN A1.ticket_id END) AS q24, COUNT(DISTINCT CASE WHEN A1.number LIKE '%PM-%' THEN A1.ticket_id END) AS q28, COUNT(DISTINCT CASE WHEN A1.number LIKE '%SR-%' THEN A1.ticket_id END) AS q29, COUNT(DISTINCT CASE WHEN A1.number LIKE '%CM-%' 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 (2) OR A1.staff_id IN (2)) AND A2.state = 'open' AND (A1.team_id IN (2) OR A1.staff_id IN (2)) AND A2.state = 'open' AND A1.staff_id IN (2) THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-11-26 00:00:00' AND '2019-11-26 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) OR A1.staff_id IN (2)) AND A2.state = 'open' AND (A1.team_id IN (2) OR A1.staff_id IN (2)) AND A2.state = 'open' AND NOT A1.staff_id IN (2) THEN A1.ticket_id END) AS q7, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-11-25 00:00:00' AND '2019-11-25 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) OR A1.staff_id IN (2)) 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-11-25 00:00:00' AND '2019-12-01 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-11-01 00:00:00' AND '2019-11-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-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 '2019-01-01 00:00:00' AND '2019-12-31 23:59:59' THEN A1.ticket_id END) AS q14 FROM ostPRODITG_ticket A1 JOIN ostPRODITG_ticket_status A2 ON (A1.status_id = A2.id) LEFT JOIN ostPRODITG_thread A3 ON (A3.object_type = 'T' AND A1.ticket_id = A3.object_id) LEFT JOIN ostPRODITG_thread_referral A4 ON (A3.id = A4.thread_id) LEFT JOIN ostPRODITG_staff A5 ON (A4.object_type = 'S' AND A4.object_id = A5.staff_id) LEFT JOIN ostPRODITG_team A6 ON (A4.object_type = 'E' AND A4.object_id = A6.team_id) LEFT JOIN ostPRODITG_department A7 ON (A4.object_type = 'D' AND A4.object_id = A7.id) WHERE (A2.state = 'open' AND (A1.staff_id = 2 OR A5.staff_id = 2 OR A1.team_id IN (2) OR A6.team_id IN (2))) OR (A2.state IN ('open', 'closed') AND (A1.dept_id IN (1, 3, 4, 5, 6, 7, 9, 10, 11, 12, 14, 15, 19, 20) OR A7.id IN (1, 3, 4, 5, 6, 7, 9, 10, 11, 12, 14, 15, 19, 20)))] Unknown column 'Z1.ticket_id' in 'field list'

---- Backtrace ----
#0 (root)/include/mysqli.php(199): osTicket->logDBError('DB Error #1054', '[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)/scp/autocron.php(51): SavedQueue::counts(Object(StaffSession), false)
#13 {main}

KevinTheJedi

@dendi23

Looks like you have some duplicates in the database. Check your database for duplicate forms.

As for the SELECT COUNT error you need to delete ost_queue record with id of 24. First copy the contents of the config column and post here so we can see what criteria is breaking then you can delete the record.

Cheers.

dendi23

KevinTheJedi

Thank you very much Sir. Btw i have deleted the following:

#TRUNCATE osticketITGUAT_db.ostUATITG_task;
#TRUNCATE osticketITGUAT_db.ostUATITG_task__cdata;
#TRUNCATE osticketITGUAT_db.ostUATITG_ticket;
#TRUNCATE osticketITGUAT_db.ostUATITG_ticket__cdata;
#TRUNCATE osticketITGUAT_db.ostUATITG_file;
#TRUNCATE osticketITGUAT_db.ostUATITG_file_chunk;
#TRUNCATE osticketITGUAT_db.ostUATITG__search;
#TRUNCATE osticketITGUAT_db.ostUATITG_attachment;
#TRUNCATE osticketITGUAT_db.ostUATITG_draft;
#TRUNCATE osticketITGUAT_db.ostUATITG_syslog;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread_collaborator;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread_entry;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread_entry_email;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread_event;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread_referral;

the error still persist 🙁 which table should i check next?

Here's the config:
'24', '1', NULL, NULL, '57', '2', '0', 'Assigned Open', '{\"criteria\":[[\"assignee\",\"assigned\",null],[\":keywords\",null,\"Assigned\"]],\"conditions\":[]}', NULL, 'T', '/1/24/', '2019-10-09 14:06:49', '2019-10-09 14:06:55'
I checked the 'forms' table. it has no duplicates. 🙁

dendi23

KevinTheJedi
Questions...

What is form_entry and form_entry_values tables?
Are they safe to truncate?
what happens when i truncate those tables?

KevinTheJedi

@dendi23

#TRUNCATE osticketITGUAT_db.ostUATITG_task;
#TRUNCATE osticketITGUAT_db.ostUATITG_taskcdata;
#TRUNCATE osticketITGUAT_db.ostUATITG_ticket;
#TRUNCATE osticketITGUAT_db.ostUATITG_ticketcdata;
#TRUNCATE osticketITGUAT_db.ostUATITG_file;
#TRUNCATE osticketITGUAT_db.ostUATITG_file_chunk;
#TRUNCATE osticketITGUAT_db.ostUATITG__search;
#TRUNCATE osticketITGUAT_db.ostUATITG_attachment;
#TRUNCATE osticketITGUAT_db.ostUATITG_draft;
#TRUNCATE osticketITGUAT_db.ostUATITG_syslog;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread_collaborator;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread_entry;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread_entry_email;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread_event;
#TRUNCATE osticketITGUAT_db.ostUATITG_thread_referral;

This was not a good idea. Now you have permanently deleted all tasks, tickets, files, logs, threads, events, etc. (unless you have a backup of before running those queries)

What is form_entry

This is an entry of a specific form.

What is form_entry_values

This is form field values for the form entries.

Are they safe to truncate? what happens when i truncate those tables?

If you do you'll lose all form field values for all tickets (custom and built-in fields).

What happened in your case is you imported a database inside another database. So you have multiple records with matching ids which is why it's showing same form but different values. You need to restart from backups of before your switch.

Cheers.