Take the 2-minute tour ×
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:

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // Always modified
header("Cache-Control: private, no-store, no-cache, must-revalidate"); // HTTP/1.1 
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache"); // HTTP/1.0
share|improve this question
    
I am pretty sure that that is the way it works; are you sure there's nothing else that could be caching? Your PHP server perhaps? –  Gaius May 20 '11 at 13:39
    
as @gaius suggested, this could be cached from some other source. Did you try to run the queries directly from mysql client to see if they are out of date that way? –  Derek Downey May 20 '11 at 14:52
    
Since all your tables are MyISAM, only index pages are cached in the key buffer (governed by key_buffer_size). Are you using memcached ??? –  RolandoMySQLDBA May 20 '11 at 15:00
    
Updated my answer !!! –  RolandoMySQLDBA May 20 '11 at 15:36
add comment

5 Answers

up vote 2 down vote accepted

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.

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

[mysqld]
query_cache_type=0
innodb_max_dirty_pages_pct=0

Step 2) Run this in mysql client

SET GLOBAL query_cache_type = 0;
SET GLOBAL innodb_max_dirty_pages_pct = 0;

UPDATE 2011-05-20 11:17

It is possible all the DB Connections have timed out from PHP's vantage point.

See if restarting MySQL and apache will do it.

Also, make sure that the timeout values for MySQL, PHP, and Apache all match up

Also, do this

netstat | grep [m3][y3][s0][q6] | grep TIME_WAIT

This helps see if any dead DB Connections have not been release by the OS

share|improve this answer
    
Unfortunately when I added SQL_NO_CACHE to the query, it stopped providing data altogether. I think these tables might be MyISAM. –  user1867 May 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! –  user1867 May 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). –  user1867 May 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 Downey May 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? –  user1867 May 20 '11 at 15:59
show 1 more comment

This is the simplest code to select and display records from MySQL database table and display in PHP.

$cn=mysql_connect($db_host,$db_user,$db_password) or die(mysql_error());
mysql_select_db($db_name,$cn) or die(mysql_error());

$sql = “SELECT field_name FROM table_name”;
$rs = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_array($rs)){

echo $field_name = $row["field_name"];
echo “</br>”;

}
mysql_free_result($rs);

Source: http://phphelp.co/2012/04/26/how-to-select-and-display-mysql-records-data-in-php

share|improve this answer
    
From the PHP manula: "Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used." –  dezso Nov 2 '12 at 22:25
add comment

You're probably using one of these generic PHP cache accelerators: http://en.wikipedia.org/wiki/List_of_PHP_accelerators

I find apc and eaccelerator most used in the wild.

There are other PHP caches also available with each framework or app being used, e.g., Wordpress using wp-cache or w3 total cache, smarty templates using smarty cache, etc..

You say "Our site" so I'm guessing you'd know if you're using DB caches like SQL-relay or mysql-proxy-cache, or front end caches like reverse proxies, squid and varnish, or mem-cached variants.

Debug your PHP web pages outside of/without the web server to help determine where the caching is taking place.

share|improve this answer
    
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! –  user1867 May 24 '11 at 18:49
add comment

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:

9a72de66e3c2f853d08352d8e78e7628
7e688f6a4cb633965d9883bb35cb8575
799f242667b315796ffe8f8d4bb4a3e2
f9e46e6486ca03ec62abc67d011f2a89
share|improve this answer
    
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. –  RolandoMySQLDBA May 22 '11 at 1:22
add comment

Nothing that MySQL does will lead to stale data -- not the Query cache, not SQL_CACHE, not the key_buffer, not the buffer_pool. The problem must be outside MySQL.

share|improve this answer
    
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 !!! –  RolandoMySQLDBA May 20 '11 at 21:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.