com.overset.dev» Blog Archive » Manually clear stale Coldfusion CLIENT data from MySQL

11.8.06

Manually clear stale Coldfusion CLIENT data from MySQL

So I’ve had a great deal of experience watching Coldfusion MX servers cripple themselves during the “client data storage purge” which happens on a defined interval via the CFAdmin. I’ve seen this only be a problem with using a MySQL 4.1 database with MX 6.X through 7.X.

After shelling out the $500 single support incident with numerous thread dumps via the kill 3 on the root cfmx pid and no real resolution - I decided to do the purge manually.

I came up with the following query to resolve this issue being run via a crontab on a set interval to destroy client data that is at least 45 min old:

New version for MySQL5+

LOCKTABLES CGLOBAL AS g WRITE, CDATA AS d WRITE;
DELETE g, d FROM CGLOBAL g LEFTJOIN CDATA d ON d.cfid=g.cfid WHERE DATE_ADD(g.lvisit, INTERVAL 45 MINUTE) < now();
UNLOCKTABLES;

Download this code: clear_coldfusion_client_data_MySQL5.sql

Old version for MySQL4

LOCK TABLES CGLOBAL AS g WRITE, CDATA AS d WRITE;

DELETE g, d FROM CGLOBAL g, CDATA d WHERE g.cfid=d.cfid AND DATE_ADD(g.lvisit, INTERVAL 45 MINUTE) < now();

UNLOCK TABLES;

Download this code: clear_coldfusion_client_data.sql

JimV @ 11.26.07 10am

We have encountered the same problem that you describe. I’m curious why you set the interval so low (45min) as opposed to a day or two. Also would you explain the process for setting up the crontab entry. - Thanks

#353 216chars

Jim Palmer @ 11.26.07 11am

JimV

All our ECommerce has a 20min session and 45min is from our legacy system. All our other session storing is minuscule in comparison. The CLIENT data fills the database up VERY fast from the ECommerce so we need to clear it sooner than later.

The crontab entry I use simple “pipes” the query above into the `mysql` command line tool. Here’s the entry:

*/2 * * * * mysql -uUSERNAME –password=”`cat ~/.mysqlpw`” client < ~/clear_sessions.sql > /dev/null 2>&1

This runs every 2 minutes.
I run this as root.
It calls the mysql CLI as though it was in the PATH.
Replace with the appropriate USERNAME.
I store the password in a ~/.mysqlpw text file which is contains just the password with no \r or \n at the end and set the permissions readable only by user (so that the password is not in plain text in the crontab).
The “client” is the name of the database that CF is configured to use as the “client store”.
The ~/clear_sessions.sql is just a plain text file with the query above.
I “pipe” all STDOUT and STDERR output to /dev/null so I don’t have to worry about logging.

#354 1093chars