QUEUE counts and results inaccurate

TPE

We created a custom QUEUE with the following clauses:
select tickets where the help topic = "sales order" and custom_field_YEAR has no value

We have created 7 test records pictured on the screenshot.
We added a value for YEAR to each of the 7 records.
Each record does have a value for YEAR, as you can see from the pop-up.
The year value on each ticket is not showing on the QUEUE screen in the YEAR column.

We can observe:
The QUEUE count on the menu flyouts are inaccurate. Should be 0, has an errant value of 7
The QUEUE results are inaccurate - see screenshot - there should be 0 rows, since all have a value for YEAR.

We have been observing this for in excess of 8 hours at the time of this writing.
Waiting patiently doesn't help
Observed results are same for administrators, agents in the department, and agents outside of the dept.
Browser refreshing doesn't help
Terminating the user session doesn't help (logout, close browser)
Bouncing the database doesn't help.

We're stuck trying to figure out how to "un-cache" this data.

Thanks in advance.

KevinTheJedi

TPE

You can try dropping the ticket__cdata table, running cron manually a bunch of times (until the table is recreated and fully repopulated with data), and retest.

Also, do you have more than one Year field?

Cheers.

TPE

KevinTheJedi

There is only one field for YEAR and we have given it a unique ID in the GUI: ("TPEYear")

I can drop a table (really drop it, or purge all the records instead?)

I'm not sure how to run "cron" many times. Is there a procedure or instruction for that?

Thanks

KevinTheJedi

TPE

You can completely drop the table. The script that cron runs recreates it if it's missing and will add all appropriate columns. However, we import the relevant data in batches which requires multiple cron runs until it's complete. Just to note, when you drop the table some of the Queue Columns will show no data until the table is recreated and fully repopulated with data.

You can run cron manually by ssh-ing to the server and running /path/to/php-8.1 /path/to/osTicket/api/cron.php. However, if you can't or don't want to ssh and you have CPanel available, you can setup a Cron Job in CPanel. Here is the relevant documentation:

https://docs.cpanel.net/cpanel/advanced/cron-jobs/

The concept is the same as manually running it, as in, you need the full path to PHP (PHP 8.1) and the full path the api/cron.php file. You can set this job to run every minute and you can delete the job once the table is fully repopulated with data.

Cheers.

TPE

For the benefit of others:
After running this procedure, we discovered that CRON was not set up and running on an interval. We enabled that.

We also discovered that observing the resulting query results on the screen as originally reported above was a red herring: the query generator was found to have a bug:

When a QUERY is constructed using either of the following conditions, "IS NULL" or "IS NOT NULL", the query generator will interpret either as "IS NULL". In other words, the result set is identical regardless of which condition you use.

There is an expected fix/patch.

This ticket can be closed.