random error of 'ost_ticket__cdata' doesn't exist on custom SQL query

stelaso

We use 1.9.8.1.We hit an error saying " 'ost_ticket__cdata' doesn't exist " when we do custom SQL query on table ost_ticket__cdata.However, seems the OST system still works fine at the times when custom query fails with such error.I saw the below seems to be the same, but seems the cause was un-known at the end of that thread.https://github.com/osTicket/osTicket-1.8/issues/1545Has there been new updates please?Any solutions?

mfelber

My understanding is that this table will be dropped and recreated under some circumstances like it is describe here: https://github.com/osTicket/osTicket-1.8/pull/1840. So this is sometimes a wanted behavior. And when your query runs in this moment the error would be "normal". But I have also seen another case when this table is missing and I'm not sure if this case is a "normal behavior" so I have reported it under the issue you mentioned..

stelaso

hi mfelber, i saw your update on https://github.com/osTicket/osTicket-1.8/issues/1545.i seem to experience the missing table "ost_ticket__cdata" after i edit the ticket too.but i'm not really sure under what situation this table comes back alive.i just experienced that my SQL query will then error out as the table doesn't exist.strangely all the while OST works normal and can navigate into ticket details screen too.

mfelber

@[deleted] I only navigate to the ticket view (in the agent panel) where all tickets are listed (for example the open tickets). After that the table "comes back"..

stelaso

@[deleted] that's what i feel too. i don't know which exact action will trigger the re-creation of the ost_ticket__cdata table.from the issue log, it is "unknown cause"i guess it's more of a bug than an expected behaviour.any advice from others please?

mfelber

@[deleted] When I'm looking at this comment from Jared from another issue, it looks to me that this a wanted behavior: https://github.com/osTicket/osTicket-1.8/issues/1005 "Has your system create a CDATA table in the mean time? It should be automatically created and populated when you visit the open tickets queue."

stelaso

@[deleted] i'm confused then.i saw the updates in https://github.com/osTicket/osTicket-1.8/issues/1545.why is it an expected behaviour to drop CDATA table at certain time?and maybe we have to identify what exact function is triggered to re-create CDATA table when we visit the open tickets queue, we just have to do the exact same thing before we run any custom SQL query on CDATA table, in order to avoid the non-exist error.

mfelber

@[deleted] I’m not really an expert on these things but I try to explain what I think about it: peter said "the cdata table is a materialized view" not a normal table. This type of „table“ have to be refreshed once in a while because it is not updatable like a normal table therefore it will be dropped and rebuild. This must happen otherwise the data in it will not be up to date. This is my understanding.His last comment "I think we need some kind of locking mechanism and also make the rebuilding of the table instantaneous“ let me think he sees possibilities to avoid getting this sql errors. I think it will be the best when this will be addressed by the developers. Besides I don’t really have the knowledge to figure out what could be a workaround for this. Maybe one of the developers like peter (protich) and jared (greezybacon) have an advice for you how to avoid this with a workaround.

stelaso

@[deleted] if that's only a VIEW, i hope we can find out the base tables forming this view. So far i have not figured out where the same set of tickets data are stored in other base tables unfortunately.Otherwise, there is no way to make sure custom SQL query on CDATA table will not fail with an error.

peter

@[deleted] - The way custom data is stored is a little abstract and cryptic, but you can follow how cdata table is refreshed to get an idea. Materialization is necessary to avoid the expensive and crazy joins to pull the data.In your case, I'll recommend calling the following function before you issue your query.TicketForm:();Cheers,

stelaso

@[deleted], appreciate it much.