Why is MySQL caching data on our dynamic PHP site?

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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