DB Error #1054

Android2

Firstly, have to say it's a great product.
However, when a slight change is made (Sometimes), it can be infuriating.

Example, I added a new field to tickets - as we need to know the Venue they are calling from

Should be easy enough, however, saving and going back to the main screen - removes all the tickets from the panel

(There should be about 15 or so tickets open)

About the system;

The error in email:
DB Error #1054

[SELECT A1.number AS number, A1.ticket_id AS ticket_id, A1.source AS source, A1.lastupdate AS lastupdate, B4.subject AS cdata__subject, A1.isoverdue AS isoverdue, B6.name AS user__name, B4.priority AS cdata__priority, B7.firstname AS staff__firstname, B7.lastname AS staff__lastname, B8.name AS team__name, A1.team_id AS team_id, A1.isanswered = '0' AS 2GkBZxz, (SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND NOT R0.flags & 4 != 0) AS _thread_count, (SELECT COUNT(R1.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) LEFT JOIN ost_attachment R1 ON (R1.type = 'H' AND R0.id = R1.object_id) WHERE Q8.ticket_id = A1.ticket_id AND R1.inline = 0) AS _att_count, C0.expire > NOW() AND NOT C0.staff_id = 1 AS _locked, (SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_collaborator R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id) AS _collabs, COALESCE(B7.firstname, B7.lastname, B8.name, 'zzz') AS assignee 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_thread A6 ON (A1.ticket_id = A6.object_id AND A6.object_type = 'C') LEFT JOIN ost_thread_referral A7 ON (A6.id = A7.thread_id) LEFT JOIN ost_staff A8 ON (A7.object_type = 'S' AND A7.object_id = A8.staff_id) LEFT JOIN ost_team B0 ON (A4.object_type = 'E' AND A4.object_id = B0.team_id) LEFT JOIN ost_team B1 ON (A7.object_type = 'E' AND A7.object_id = B1.team_id) LEFT JOIN ost_department B2 ON (A4.object_type = 'D' AND A4.object_id = B2.id) LEFT JOIN ost_department B3 ON (A7.object_type = 'D' AND A7.object_id = B3.id) LEFT JOIN ost_ticket__cdata B4 ON (A1.ticket_id = B4.ticket_id) LEFT JOIN ost_ticket_priority B5 ON (B4.priority = B5.priority_id) LEFT JOIN ost_user B6 ON (A1.user_id = B6.id) LEFT JOIN ost_staff B7 ON (A1.staff_id = B7.staff_id) LEFT JOIN ost_team B8 ON (A1.team_id = B8.team_id) LEFT JOIN ost_lock C0 ON (A1.lock_id = C0.lock_id) WHERE A2.state = 'open' AND ((A2.state = 'open' AND (A1.staff_id = 1 OR A5.staff_id = 1 OR A6.object_type = 'C' AND A8.staff_id = 1 OR A1.team_id IN (1, 2) OR B0.team_id IN (1, 2) OR A6.object_type = 'C' AND B1.team_id IN (1, 2))) OR A1.dept_id IN (1, 2, 3) OR B2.id IN (1, 2, 3) OR A6.object_type = 'C' AND B3.id IN (1, 2, 3)) AND (A1.ticket_pid IS NULL OR A1.flags & 8 != 0) GROUP BY A1.ticket_id ORDER BY B5.priority_urgency ASC, A1.lastupdate DESC LIMIT 30]

Unknown column 'B4.subject' in 'field list' 
 
---- Backtrace ---- 
#0 (root)/include/mysqli.php(211): osTicket->logDBError() 
#1 (root)/include/class.orm.php(3482): db_query() 
#2 (root)/include/class.orm.php(3529): MySqlExecutor->execute() 
#3 (root)/include/class.orm.php(2104): MySqlExecutor->getArray() 
#4 (root)/include/class.orm.php(2054): HashArrayIterator->{closure}() 
#5 (root)/include/class.orm.php(2033): CallbackSimpleIterator->next() 
#6 (root)/include/class.orm.php(2042): CallbackSimpleIterator->rewind() 
#7 (root)/include/class.orm.php(1713): CallbackSimpleIterator->valid() 
#8 (root)/include/class.orm.php(1723): CachedResultSet->fillTo() 
#9 (root)/include/class.orm.php(1739): CachedResultSet->asArray() 
#10 (root)/include/staff/templates/queue-tickets.tmpl.php(258): CachedResultSet->getIterator() 
#11 (root)/scp/tickets.php(569): require_once('...') 
#12 {main}

I could restore the database from the last backup, but I'd lose about a week's worth of work. (I didn't think a backup was necessary before adding a new field)

We've more fields to add over the next few weeks, including adding support for another company.
Are there any ideas on why this is the case?
Are there any keywords that should be avoided?

KevinTheJedi

Android2

Nothing is terribly wrong here and this is an easy fix. You apparently don't have a cron job (or scheduled task for windows) setup so the Ticket CDATA table is not properly being recreated and repopulated once you make changes to the Ticket Details Form. You see, the system drops (deletes) this table once you make major changes to any of the Fields on the Ticket Details Form, then the system should recreate the table with appropriate columns (fields), and then repopulates the table with the needed data all by itself. It appears since you don’t have a cron job (or scheduled task) setup the system is relying on Auto-cron which can be quite slow and will show such errors until it can do the above steps.

I’d recommend setting up a cron job (or scheduled task) so the system can do things automatically in a timely manner.

Cheers.

Android2

Kevin, Thanks for the timely reply. Is this the cron job you are referring too;
*/5 * * * * ticket-admin /usr/bin/php /var/www/html/osticket/api/cron.php
Its being hosted on Alma Linux and accessed by W10/W11 PCs

Android2

I should add, that I have run the cron job as above on numerous occasions manually - just in case. Rebooted the server, logged off/on, cleared cookies/cache, etc, and used incognito mode. All to no avail.
Anything else that can be run or suggested?

KevinTheJedi

Android2

Sounds like you need to drop the table yourself and run cron manually until it’s recreated and fully repopulated with data. It could also be that you deleted the Issue Summary field or changed the variable name of this field. This field should not be deleted nor should the variable be changed. But I’m not certain you’ve done this so just mentioning this in case you have.

Cheers.

Android2

Hi Kevin
Yes, I manually deleted the tables (AFTER making a backup), and had the following on loop while doing it;
while true; do /usr/bin/php /var/www/html/osticket/api/cron.php; echo "Done"; sleep 10; done
I also had the site "offline" while doing it.
Something that may help others;
To backup the database: mysqldump osTicket > osTicket.sql
To restore the database: mysql -u root -p osTicket < osTicket.sql

I'll be creating a script that will back up the Database a little more often than a week - Just in case...

AND I'll ensure the site is set to "Offline" mode with the above "while true" running before I make any more changes
The site was down for half a day, not a good look (On my behalf)

KevinTheJedi

Android2

You don’t need the system to be offline for this. So “bad look” for nothing.

Cheers.