Client Variables Database - Millions of Entries!

Hello Everyone!

Primary Quesiton:
I have a strange problem that I’m hoping people way smarter than I have either run into or figured out.
I have several websites running under Lucee/Tomcat and IIS. I’ve stored all the data the websites use in a database (lets say it’s called “storage”) with my Client Variables stored in a separate database (Client Variables cf_client_data) with both in MS SQL Server. All the websites work just fine with no visibly apparent issues.

The problem started when I realized there were an absolutely insane amount of entries in my cf_client_data database and I didn’t understand how that could be since we didn’t have that much traffic. After MUCH exploration, I realized that if I deleted all of the records in cf_client_data, they would still be created even if I removed all of the directories to the websites so it was impossible for anyone to be on them and logging in or accessing any data.

With no website directories able to be hit, the database was still showing the creation of hundreds of client variables in the database per minute. I’m at a loss since I cannot figure out what is generating them.

The entries in cf_client_data ‘name’ includes the name of the active database that the website uses. Can anyone explain to me how Client Variables can be created in a database when no one is hitting any websites associated with databases I’m using? I would like to bring this down to reasonable levels without feeling that Skynet is somehow going to take over everything by magically replicating millions of Client Variables with seemingly no reason.

To sum up:

  1. Lucee/Tomcat is running
  2. IIS is running
  3. SQL server is running with databases “storage” and “client variables cf_client_data” active.
  4. Website directories have been removed so no one can hit them.
  5. Client Variables are being created in “client variables cf_client_data” by the hundreds per minute.

Secondary Question:
If the first question can be solved, how does Lucee purge old Client Variables? In Coldfusion, there was an option to automatically purge unused Client Variables at a set interval. If Lucee doesn’t do this automatically, are there any code examples of how to do it?

Version: Lucee 5.3.7.47
Servlet Container: Apache Tomcat/8.5.33
Windows Server 2016 Datacenter
MS SQL Server

I hope I have explained this strange behavior well enough and thank you everyone for taking the time to read this.

Update:

I had renamed the “Storage” directory to “Storage_old”. I restarted Lucee and the Client Variables creation in the database had stopped.

I decided to create an empty directory named “Storage” to see what would happen while Lucee was running. Lucee automatically created it’s WEB-INF directory under “Storage” and the Client Variables creation started up again. I have since change the location of WEB-INF to outside the webroot (for safety) but the Client Variables are still propagating in the database. I assume there’s something in the WEB-INF directory that’s somehow causing this to happen.

Has anyone else experienced something like this? Thank you in advance for any help!

Firstly, I’d enable debugging to see where the database inserts are coming from

New records in the client vars storage should be created only when a request comes in with no cookies (no cfid/cftoken, specifically) to an app that has clientmanagement enabled.

I realize you think “no requests can be running” because you removed the website directories. Consider that request could be coming through the tomcat webserver as well.

Indeed, the requests could perhaps be some scheduled task running against that internal web server. Again, ANY request that comes in without cookies would create a new record. (Same with cfhttp calls into a cfm/cfc.)

If you had a tool like fusionreactor or seefusion (free trials for both) you could see what requests, if any, were running.

Short of that, have you tried turning off iis entirely? Just to ensure there’s not something where “removing the site directories” wasnt enough?

And you could try disabling the tomcat web server, by commenting our the http connector in server.xml. You won’t be able to access the lucee admin, but it’s “for science”.

Let us know how it goes.

HEY!! GUYS! THANK YOU SO MUCH!

I hadn’t thought that CVs could be generated by ANY request in to the web server. That was the disconnect in my brain as to why I couldn’t figure this out. I honestly should have known better but a big thank you for helping with that!

I installed fusion reactor as you said and sure enough, the web server was getting spammed from an IP in the UK. It wasn’t big enough for me to have noticed; however, one IIS IP ban later and no more hundreds of CVs being generated per minute. So that part is SOLVED!

I’m still wondering how to purge expired CVs from the database since Lucee doesn’t have the option in the Administrator like Coldfusion did.

I’m still wondering how to purge expired CVs from the database since Lucee doesn’t have the option in the Administrator like Coldfusion did.

My understanding is that Lucee does this automatically based on your settings in Admin / Scope (or Application.cfm / cfc - which will override those).

In our Lucee 5.2.8.50 environment - we have expiration set to 1 hour. I can see the queries originating from the web servers (we have several in production) every hour (hourish - which I am guessing is by design).

HOWEVER - in our Lucee 5.3.8.189 environment - the expired CF_Client_Vars data does NOT seem to be deleting at all.

We brough Lucee 5.3.8.189 live on Thursday - and I see “expires” values of 1625791248208 in there (Thursday, July 8, 2021 8:40:48.208 PM for my time zone).

I raised this issue last summer - which is what ultimately kept us from upgrading Lucee in production. Here is the thread:

https://dev.lucee.org/t/regression-cf-client-data-not-deleting-in-5-3-6-61/7293/3

Also - I am seeing “double entries” for every session. One with the appropriate Application Name from our Application.cfm, then another with a Null “Name” value.

I was really hoping this issue would be gone in 5.3.8.189…

It’s there a ticket in JIRA for this? Posting on the community forum it’s not the same as entering a ticket.

Hi! I have not logged one. I was unaware that I could… Happy to help out - where do I do that? Do I need another login to another system?

This is the right place to add, search/look for, follow, vote tickets for Lucee:

https://luceeserver.atlassian.net/

1 Like

Andreas has the correct link. It’s fine to post here looking for community help, but unless it’s in the bug tracker, it doesn’t exist so far as the dev team is concerned (who don’t necessarily monitor these public channels). Since you mentioned you “raised this issue”, I just wanted to make sure you had an actual ticket in. The 5.3.8 release actually had fixes for all known regressions in the bug tracker. Had this been logged, it likely would have been fixed :slight_smile:

Thanks so much for the info guys… Very helpful. I built an account and logged it as you suggested. Hope I did it right (first one, obviously). Any feedback would be welcome!

https://luceeserver.atlassian.net/browse/LDEV-3610

1 Like

Does anyone know what actually triggers deletes of CF_Client_Data?? We monitor the size of these tables (since we had a crash last year), and see the queries running against the database.

Example:

Delete * from cf_client_data where expires < 1626052005246

What triggers this? Are there known regression issues? Do most people write their own code to truncate this table?

try bumping up the log level for the scope log file to debug?

Thanks for the suggestion Zackster. You’ve been super helpful…

Ummm… Cough - How does one do that, exactly?

1 Like

Under logs in the Lucee server administrator

1 Like

This is the exact same thing I have happening as purdue512 with the double entries. I’ve tried Zackster’s suggestion of changing the “Maximum Logged Requests” and using “Purge” to no avail. It doesn’t seem to clear the CF_Client_Data database at all. I wouldn’t put it past myself to be missing something though.

Thanks Doggie –

So what are you doing to manage expired data? Did you write your own cf_client_data purge SQL via a Scheduled Task?

In my experience - the indexes on this table will help a ton…

AND … you will hit severe performance issues after the tables are in the tens-of-millions records (which doesn’t take that long on a busy application).

I tried Zacksters log idea. So far, no change in behavior… The CF_Client_Data just keeps growing despite settings in BOTH

Lucee Admin (set to 2 hours)

and

Application.cfm (set to 30 minutes)

This feature is definitely working on my Lucee 5.2.8.50 boxes…

Right now, I was just deleting it every few days since I realized what was going on. Certainly NOT optimal. I can roll my own purge and schedule it, but I’m trying to sort out what criteria I want for deletion.

purdue512 - where did you set it in the admin settings for 2 hours? I must be looking in the wrong place but I don’t see that under Logging/Scope. I see the Stream Timeout (in minutes) and it will only let me set it to 6 minutes max.

those options are only for debugging logs, which is completely unrelated