Clearing Query Cache / APC / Apache

[Alan Dobkin]

When I add or delete fields/columns to an existing table, the changes often don't take effect in Xataface. I had an instance this morning where I deleted some old fields that were only in one table (no relationships) and no longer being used. Xataface continued to reference them when loading that table, which caused errors in the server log and a blank page in the app.

I do use the output cache feature, as well as APC, and several of my tables have custom __sql__ statements. When this happens, I go through the steps of clearing the sql views (dataface__view_...), emptying the output cache, and using the URL parameter --refresh-apc=1. However, the old select query still persists and tries to reference fields that no longer exist. Sometimes, the problem goes away if I wait a while. The problem this morning was eventually resolved by restarting Apache after performing the other steps above.

Is there something else I should check or a better way to gracefully handle this situation?

Thanks,
Alan

[Mike Paxton]

I'm seem to be having the same issue.  I've added a couple of columns/fields to several tables and modified a column name which was misspelled.  The database has been updated and reflects the new changes but Xataface won't display the additional fields and I have to refer to the old spelling of the column to keep it functioning.  I've done the whole APC refresh and clearing the view cache several times to no avail.  

Any thoughts as to a solution?

Regards,

Mike

[Alan Dobkin]

Have you tried restarting Apache? That worked for me recently.

[Mike Paxton]

That worked great, thank you!!!  

So is this an apache caching issue of some sort and not Xataface?

[Mike Paxton]

Any chance you are using MariaDB rather than MySQL?  Reason I ask is my test site is running MySQL and is not having the issue but my live site is on a MariaDB at this time.

Regards,

Mike

[Alan Dobkin]

No, just standard LAMP with MySQL on my system. Glad the Apache restart worked for you, but I'm still wondering why this is needed or if there is a more graceful way to clear the cache.

[basi....@googlemail.com]

Do you use authentication?
If you do, you probably don't have the permissions to clear the cache.

[Mike Paxton]

No authentication in my case.  If I either restart Apache or MySql I seem to be able to implement new database changes but I'm unsure as to why this should be the case?

[Alan Dobkin]

I use authentication, but I am logged in as a full admin. The output
cache does get cleared when I clear it. It displays 0 pages in cache
after it is cleared, and I can see they are gone from the cache table in
the database. The SQL views also do get removed when I clear them.
However, the application still uses the old table structure.

Here are a few more nuggets about this problem:

The xataface option --refresh-apc=1 does not appear to be working as I
expected. When I check the APC cache directly before and after using
this option, it doesn't appear to change at all. Keep in mind that there
were no changes to the PHP files, only to the database schema in this
situation. However, even if I directly clear the entire APC cache on the
server and confirm it is empty, the old database schema is still retained.

Also, if I create or edit a record in the table that has a new or
deleted field, as soon as the changes are saved, the new database schema
is now displayed. So, something in Xataface apparently reloads the table
definitions after the table data is modified.

Steve, do you have any other input on this?

Thanks,
Alan

[Alan Dobkin]

I think I've solved this problem. Here are my findings on clearing the Xataface table schema/field cache after adding or removing fields in the database:

1. You must be logged in with the proper permissions (admin role, etc.).
2. It is generally not necessary to clear the output cache or SQL views, and they seem to have no effect on solving this problem.
3. The URL parameter --refresh-apc=1 is case-sensitive. It must be entered in all lower-case. (I had been using it occasionally with APC in upper-case and realized that wasn't working.)
4. The URL parameter --refresh-apc=1 only applies to the current table specified in the URL. Using it in the "control panel", at the dashboard level, or while in some other table has no effect. (I had thought it was an application-wide command.)
5. The URL parameter --refresh-apc=1 does not actually clear the entire server APC cache. It only refreshes the elements required to display that page.
   
6. Clearing the entire server APC cache using the PHP function apc_clear_cache() does clear APC, but oddly enough, it does not clear the Xataface table field cache. (This one is still a mystery to me. Where is this cache actually stored?)
7. Creating a new record or editing an existing record in the affected table also appears to refresh the cache after it is saved. However, this may only work when adding a new field, since removing a field often causes the "blank page" error, which prevents doing anything with that table.
   
8. Restarting Apache or possibly restarting MySQL also clears out the cached table fields.
   

If anyone else has different experiences or can confirm/deny the points above, please let me know!

Thanks,
Alan

[Alan Dobkin]

Correction on the second item in my previous message. It still is necessary to also clear the SQL views if there is a custom __sql__ directive either in the table's fields.ini file or the delegate class where the fields have been added or removed. So, these two steps in order seem to work every time now after either adding or removing columns/fields from the database:

1. Go to the control panel from the dashboard or main application page. If you try to access it from the table that was just changed, it may result in an error and a blank page, since the preserved URL parameters will generate database queries on the modified table.
2. Clear the __sql__ views using that option in the control panel.
3. Go back to the table that was modified in your application and append &--refresh-apc=1 (all lower-case) to the URL. That should cause it to display properly.
   

Alan

[Steve Hannah]

Thanks for documenting this.  I have been wanting to simplify this for a while, but it is tricky.

Steve

--
You received this message because you are subscribed to the Google Groups "Xataface" group.
Visit this group at http://groups.google.com/group/xataface.

To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/549844DA.2030901%40Gmail.com.

--

Steve Hannah

Web Lite Solutions Corp.