CURRENT PROJECTS
loading
CATEGORIES AND POSTS
loading
overset
DEVELOPMENT LOG FOR JIM PALMER
Posted 11/08/2006 in coldfusion


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+
LOCK TABLES CGLOBAL AS g WRITE, CDATA AS d WRITE;
delete g, d from CGLOBAL g LEFT JOIN CDATA d ON d.cfid=g.cfid
	where DATE_ADD(g.lvisit, INTERVAL 45 MINUTE) < now();
UNLOCK TABLES;

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;
comments
loading
new comment
NAME
EMAIL ME ON UPDATES
EMAIL (hidden)
URL
MESSAGE TAGS ALLOWED: <code> <a> <pre class="code [tab4|tabX|inline|bash]"> <br>
PREVIEW COMMENT
TURING TEST
gravatar