Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are having an annoyance with our PHP/MySQL site in that it seems to cache data from the database and our PHP pages are showing out of date records. In fact, there are pages that link to URLs that have been completely removed from the database, yet they do not show updated records or even missing data.

If we have the patience of waiting several (undefined) hours, the data will eventually show up. I've added the regular no-cache headers via PHP on all of the pages, but it doesn't seem to affect the MySQL output.

The PHP portion of the site is instantly updateable though, and shows HTML etc changes immediately. Is there a way to force MySQL to only draw fresh data from its queries? I thought this was the way it worked anyways!

Thanks,
Chris

P.S. Here are the php codes I'm using to try to force a no cache situation:

5 Answers
5

You could do SELECT SQL_NO_CACHE. This is used by mysqldump to always dump fresh data to a text file. This may also help flush the innodb buffer pool if the table being SELECTed is InnoDB.

If you cannot maniuplate the SELECT statements, then set query_cache_type to 0. That will make all SELECTs behave like SELECT SQL_NO_CACHE. You can do this without restarting mysql:

Step 1) Add this to the /etc/my.cnf

[mysqld]
query_cache_type=0

Step 2) Run this in mysql client

SET GLOBAL query_cache_type = 0;

FINAL WORD

Doing this may increase read I/O. You may also want to consider setting innodb_max_dirty_pages_pct = 0 to keey the innodb buffer pool with the freshest data that is as fully flushed to disk as possible.

Unfortunately when I added SQL_NO_CACHE to the query, it stopped providing data altogether. I think these tables might be MyISAM.
–
user1867May 20 '11 at 14:55

I've just found another abnormality here; when I host the exact same PHP files on a different host, the database updates show immediately in my PHP pages. Is there some kind of PHP result caching possible? I know something is caching the data somewhere in the chain on that server, because when I go back and visit the PHP page on that server, it's still out of date!
–
user1867May 20 '11 at 15:04

I managed to log in via shell (this Website OS 4.0 is total garbage), and sure enough I am unable to connect to the MySQL server using the shell. I get an ERROR 2003: Can't connect to MySQL server on ourserver.megasqlservers.com (110).
–
user1867May 20 '11 at 15:54

You might ask on serverfault.com . I can only speculate you have a php-caching program enabled (APC or some such). @Rolando nice answer!
–
Derek DowneyMay 20 '11 at 15:54

I'm going to look into any possible PHP caching programs that may be hidden from me, maybe i can find something in phpinfo(). Wouldn't these PHP headers prevent it from caching though?
–
user1867May 20 '11 at 15:59

That's what my main problem is here; I can't see any specific caching folder but I'm sure it's happening somewhere server-side. When I run the same scripts on a more familiar server, the caching doesn't happen. Unfortunately with megawebservers.com or whatever Rogers uses for their web servers, they are sluggish to respond if at all. I've simply suggested that my client move hosts!
–
user1867May 24 '11 at 18:49

IMHO, it sounds like your application is utilizing memcache or a disk-based cache folder. For instance, Wordpress has cache plugins that can utilize a /cache/ folder. If you have such a folder, try deleting all the the files out of it that look like hashes, such as:

I was thinking the same thing yesterday with regard to memcached and I said that as a rhetorical question in the comment to the question. I am glad I am not the only one that thought of that possibility. Since you have a more plausible explanantion for that possibility, +1 from me.
–
RolandoMySQLDBAMay 22 '11 at 1:22

Actually, you are correct !!! That is why I updated my answer around 11AM to suggest PHP's take on things. My answer wasn't accepted until my last update. Since your answer was bluntly more explicit than mine, +1 for you from me !!!
–
RolandoMySQLDBAMay 20 '11 at 21:16