Moving MySQL To A Per-Application Datasource In ColdFusion 2021
Over the weekend, after my ColdFusion blog was accidentally upgraded from MySQL 5.0.10 to 8.0.28, I had to scurry and update the datasource configuration in both my production environment as well as in my local Docker development environment. And it occurred to me that manually updating the MySQL datasource in the ColdFusion Administrator represented a huge point of human failure. And, more than that, there was no record of these changes being made; which meant that undoing said changes would be quite challenging. In a modern application context, this is highly disturbing! My application's datasource configuration workflow should be in source-control. As such, I've decided to move my blog to a per-Application datasource.
SECURITY NOTE: When I say that this workflow should be in source-control, I do not mean that any credentials should be in the source-control! Never store credentials in the source-control!
Moving from a ColdFusion Administrator-based datasource configuration over to a per-application datasource configuration was easier said than done. All in all, making this change took me 3-mornings. Mostly because the documentation on this ColdFusion feature is severely lacking. That, or my Google-skills just weren't up to the task.
Ultimately, I had to piece together the MySQL per-application datasource code by picking through several other sources. First, I went to the documentation on Data Source Management for ColdFusion. This told me what kind of information I could provide in my MySQL connection; but, it didn't offer me any insight into how that information actually gets provided in the code.
From there, I went to the documentation on ColdFusion Application.cfc variables. This page started to give me some information about how the this.datasources property could be defined. But, it was still severely lacking! It didn't even have a MySQL example. And, the examples that it did have only included a fraction of the possible configuration values outlined in the aforementioned "Data Source Management for ColdFusion" article.
I thought maybe the best idea would be to look at how the datasource was configured on disk - ie, how ColdFusion stores the configuration across server restarts. Based on the documentation for, Purpose and location of XML configuration files used in ColdFusion, I learned that the datasource information was saved in an XML file, neo-datasource.xml.
So, I bashed into my local Docker CFML container and output the contents of this XML file:
NOTE: I am using CommandBox Docker Image from Ortus Solutions; and, this is where they have configured the ColdFusion server root. This may not align with your particular server setup.
# Enter the running Docker container for ColdFusion 2021.
docker-compose exec cfml bash
# Move to the configure files directory within the ColdFusion install.
cd /usr/local/lib/serverHome/WEB-INF/cfusion/lib/
# Output the entire contents of the XML file.
cat neo-datasource.xml
This gave an XML document with a bunch of <var> elements that outlined all of the datasource configuration options. From there, I embarked on a process of trial and error, plugging those values into my this.datasources code and seeing what would happen.
Half way through this process, it occurred to me that the CommandBox CFConfig module was probably messing with all of this stuff as well. After all, I've been using a .cfconfig.json file to configure my local CFML Docker container. Which means, they are possibly reading-from or writing-to this XML file.
I hopped over to cfconfig/models/BaseAdobe.cfc source code to look at their writeDatasource() method. And, oh boy, there's a lot going on here! I don't fully understand what their code is doing; but, between their code and the neo-datasource.xml file, I was finally able to get something comprehensive working!
After 3 mornings of trial-and-error and poking through various configuration files, here's what the datasource configuration in my Application.cfc now looks like:
CAUTION: While I was able to confirm that some of these configuration settings work as advertised, I wasn't sure how to go about testing the pooling configuration. So, I'm hoping there aren't any errors here; but, I cannot attest to the full accuracy of this setup.
component {
// ... truncated for demo.
// Define the datasources and default datasource.
// --
this.datasources = {
// CAUTION: The datasource names in this struct are CASE-SENSITIVE and need to be
// quoted. I'm not sure if this is because per-application preserve-case settings
// do not appear to apply here; or, if something else is going on.
"bennadel": {
username: this.config.dsn.username,
password: this.config.dsn.password,
driver: "MySQL",
class: "com.mysql.jdbc.Driver",
url: (
"jdbc:mysql://#this.config.dsn.server#:#this.config.dsn.port#/#this.config.dsn.database#" &
"?allowMultiQueries=true" &
"&characterEncoding=UTF-8" &
"&serverTimezone=UTC" &
"&tinyInt1isBit=false" &
// Max Performance properties: https://github.com/mysql/mysql-connector-j/blob/release/8.0/src/main/resources/com/mysql/cj/configurations/maxPerformance.properties
"&useConfigs=maxPerformance"
// NOTE: Leaving zeroDateTimeBehavior as default (EXCEPTION) since I don't
// like the idea of any data/times values being shoe-horned into a working
// version. I'd rather see the errors and then deal with them.
),
// Allowed SQL commands.
delete: true,
insert: true,
select: true,
update: true,
// Disallowed SQL commands.
alter: false,
create: false,
drop: false,
grant: false,
revoke: false,
storedproc: false,
// Disables the returning of generated keys (such as PKEY AUTO_INCREMENT) in
// the "result" meta-data structure.
disable_autogenkeys: false,
// These two properties seem to work in conjunction and limit the size of the
// long-text fields that can be PULLED BACK FROM THE DATABASE. If the CLOB is
// disabled, then the given buffer size will truncate the value coming back
// from the database.
// --
// NOTE: To be clear, this DOES NOT appear to prevent the INSERT of large
// values GOING INTO the database - just the retrieval of large values coming
// OUT OF the database.
disable_clob: false,
buffer: 0, // Doesn't mean anything unless above is TRUE.
// I ASSUME these two properties for BLOB work the same way as the CLOB
// settings above; but, I have not tested them directly.
disable_blob: true,
blob_buffer: 64000, // Doesn't mean anything unless above is TRUE.
// Connection pooling.
// --
// CAUTION: I have NOT VALIDATED that the following settings actually work
// (except for the urlmap.maxConnection property).
pooling: true,
// The number of SECONDS before ColdFusion times out the data source
// connection login attempt.
login_timeout: 30,
// The number of SECONDS that ColdFusion maintains an unused connection before
// destroying it.
timeout: 1200,
// The number of SECONDS that the server waits between cycles to check for
// expired data source connections to close.
interval: 420,
urlmap: {
// Limit the number of concurrent connections to this datasource.
// --
// CAUTION: This value has to be a STRING - if you use a NUMBER, the
// entire datasource configuration will fail. And, if you don't want to
// limit connection, you have to OMIT THIS VALUE ENTIRELY.
// --
// maxConnections: ""
}
}
};
this.datasource = "bennadel";
// ... truncated for demo.
}
As you can see, I'm defining a datasource, "bennadel", in this.datasources; and then, I'm using this.datasource (singular) to allocate that datasource as the default datasource within the application. This allows me to run my CFQuery tags and queryExecute() functions without any explicit datasource attribute.
Also note that the key for the datasource is quoted. This appears to be required - at least some of the time - otherwise I was getting errors that the datasource couldn't be found. That said, I can't consistently reproduce that problem. It may have been a caching issue; or, it may have been an issue with the fact that I was testing with a mixed-case datasource name (bennadelTestin originally).
After I had this per-application datasource running in my Adobe ColdFusion 2021 application, I then went into the ColdFusion Admin and deleted the CFIDE-based datasource. And, kablamo! My blog was still working!
In order to make sure that this was actually working, and not just a false positive, I had to go in and make sure that my CommandBox CFConfig was wasn't really the thing driving the datasource generation. To do this, I entered my running Docker CFML container and exported the CFConfig settings for the currently-running ColdFusion server (the one with no CFIDE-based datasource):
# Enter the running Docker container for ColdFusion 2021. This puts me into the
# site-root for my blog.
docker-compose exec cfml bash
# Enter the CommandBox CLI.
box
# Export the ColdFusion server configuration files.
cfconfig export to=./myconfig.json
This copied the myconfig.json file to my webroot, which was mounted as a volume in my Docker setup. Which means, this JSON file was accessible in my host computer. From there, I copied it over to my Docker build, destroyed my running containers, and then brought them back up again. I confirmed that the datasource was not being generated in the ColdFusion Admin; and, that my blog was continuing to run use the per-application datasource!
I was kind of surprised that the documentation on this stuff was so hard to find; especially considering how complex a full datasource configuration is. As I was saying to Brad Wood and Gavin Pickin yesterday, it must have taken the Ortus Solutions team a lot of iteration to get CFConfig working nicely across the different ColdFusion runtimes. Their code is probably the best "documentation" of how it all actually works.
All that said, I'm excited to finally have this in my source-control. This is a critical part of the application setup. And, as I demonstrated earlier this week, this kind of configuration changes over time along with your database version. As such, it needs to be recorded and persisted along with your code.
Why Not Just Commit Your CFConfig File to Source-Control?
On a recent episode of the Modernize or Die ColdFusion podcast (sorry, I don't remember which episode exactly), Brad Wood was talking about his favorite CommandBox modules. In that episode, he mentioned two things relevant to this discussion:
There's a module that automatically takes an
.envfile and applies thosekey=valuepairs to the server so that they can be consumed as environment values in the runtime.The CFConfig module can substitute some of those environment values into the
.cfconfig.jsonfile as it's being evaluated such that you can do things like generate a datasource without having to persist the datasource password to the.cfconfig.jsonfile.
Together, this means that one could theoretically commit their .cfconfig.json file to source control as a means to manage the changes in the datasource configuration over time. So, why wouldn't I just do this rather than much with the ColdFusion per-application settings?
Simple: While I use CommandBox locally, I don't actually use it in production. My entire production setup for this blog is old school (and pre-dates CommandBox). No continuous delivery, no fancy deployment pipelines, no snazzy build processes. As such, I personally need something that works more within the boundaries of the code. Of course, if you are using CommandBox in production, I think persisting your .cfconfig.json file would be totally valid.
UPDATE: Debugging "Datasource bennadel Could Not Be Found"
CAUTION: This update did not work, see next section.
As I discussed in the comments below, after I put this per-application datasource in place, I discovered that my application would sometimes fail to bootstrap after the ColdFusion service was restated. Inside the onApplicationStart() event-handler, when I went to pre-load some data, I would end-up getting this error:
Datasource bennadel could not be found.
The crazy part about this is that if I went into the server and added code to output the application metadata at this point in the bootstrapping process:
<cfscript>
writeDump( getApplicationMetadata().datasource );
writeDump( getApplicationMetadata().datasources.keyList() );
writeDump( exception.message );
abort;
</cfscript>
... I would get the following output:
bennadel
bennadel
Datasource bennadel could not be found.
So, clearly, the datasource was defined in the application metadata, ColdFusion just wasn't seeing it.
During my trial-and-error around this problem, I discovered that - while the ColdFusion application was in this failing state - if I went into the production code and touched any of the properties in the this.datasources collection (meaning I changed one of the values), then suddenly the bootstrapping process would start working. It was like some magical, internal cache was somehow being reset when the value of the datasource changed.
This gave me the idea to add a "throw away" property to the datasource that would change whenever the application was bootstrapped. The thinking being that if the application failed to bootstrap on the first request, a second request would reload the config data and put a new timestamp in the datasource which would act as the "touch" that would clear the invalid cache state.
And, this seems to be working!
To see what I mean, here's a more fleshed-out example of my Application.cfc that shows the various parts related to the bootstrapping and configuration. Notice that the getConfigSettings() method inserts a loadedAt timestamp (tick count). This loadedAt value is then included in the datasource as the throw-away property, _loaded_at_:
component
output = false
hint = "I define the application settings and event handlers."
{
// Define application settings.
this.name = "WwwBenNadelCom";
this.applicationTimeout = createTimeSpan( 2, 0, 0, 0 );
this.sessionManagement = false;
this.setClientCookies = false;
this.config = getConfigSettings();
// Define the datasources and default datasource.
// --
this.datasources = {
// CAUTION: The datasource names in this struct are CASE-SENSITIVE and need to be
// quoted. I'm not sure if this is because per-application preserve-case settings
// do not appear to apply here; or, if something else is going on.
"bennadel": {
username: this.config.dsn.username,
password: this.config.dsn.password,
driver: "MySQL",
class: "com.mysql.jdbc.Driver",
url: (
"jdbc:mysql://#this.config.dsn.server#:#this.config.dsn.port#/#this.config.dsn.database#" &
"?allowMultiQueries=true" &
"&characterEncoding=UTF-8" &
"&serverTimezone=UTC" &
"&tinyInt1isBit=false" &
// Max Performance properties: https://github.com/mysql/mysql-connector-j/blob/release/8.0/src/main/resources/com/mysql/cj/configurations/maxPerformance.properties
"&useConfigs=maxPerformance"
// NOTE: Leaving zeroDateTimeBehavior as default (EXCEPTION) since I don't
// like the idea of any data/times values being shoe-horned into a working
// version. I'd rather see the errors and then deal with them.
),
// Allowed SQL commands.
delete: true,
insert: true,
select: true,
update: true,
// Disallowed SQL commands.
alter: false,
create: false,
drop: false,
grant: false,
revoke: false,
storedproc: false,
// Disables the returning of generated keys (such as PKEY AUTO_INCREMENT) in
// the "result" meta-data structure.
disable_autogenkeys: false,
// These two properties seem to work in conjunction and limit the size of the
// long-text fields that can be PULLED BACK FROM THE DATABASE. If the CLOB is
// disabled, then the given buffer size will truncate the value coming back
// from the database.
// --
// NOTE: To be clear, this DOES NOT appear to prevent the INSERT of large
// values GOING INTO the database - just the retrieval of large values coming
// OUT OF the database.
disable_clob: false,
buffer: 0, // Doesn't mean anything unless above is TRUE.
// I ASSUME these two properties for BLOB work the same way as the CLOB
// settings above; but, I have not tested them directly.
disable_blob: true,
blob_buffer: 64000, // Doesn't mean anything unless above is TRUE.
// Connection pooling.
// --
// CAUTION: I have NOT VALIDATED that the following settings actually work
// (except for the urlmap.maxConnection property).
pooling: true,
// The number of SECONDS before ColdFusion times out the data source
// connection login attempt.
login_timeout: 30,
// The number of SECONDS that ColdFusion maintains an unused connection before
// destroying it.
timeout: 1200,
// The number of SECONDS that the server waits between cycles to check for
// expired data source connections to close.
interval: 420,
// urlmap: {
// // Limit the number of concurrent connections to this datasource.
// // --
// // CAUTION: This value has to be a STRING - if you use a NUMBER, the
// // entire datasource configuration will fail. And, if you don't want to
// // limit connection, you have to OMIT THIS VALUE ENTIRELY.
// // --
// // maxConnections: ""
// },
// HACK: For reasons that I don't understand at all (and what is looking very
// much like a bug in ColdFusion), the datasources configuration occasionally
// bombs-out when the service-start up for the first time. But, I've
// discovered that simply "touching" this structure (ie, changing any property
// within it) appears to fix the problem (maybe by flushing some sort of
// internal, magical cache). As such, I'm going to see if including a
// timestamp will act as a "touch". This timestamp denotes when the config
// object was cached. And, since it's cached whenever the onApplicationStart()
// method runs, if the application fails to bootstrap, the next request will
// get a newer timestamp when it goes to reload the config.
_loaded_at_: this.config.loadedAt
}
};
this.datasource = "bennadel";
// ---
// LIFE-CYCLE METHODS.
// ---
/**
* I get called once when the application is being bootstrapped. This method is
* inherently single-threaded by the ColdFusion application server.
*/
public void function onApplicationStart() {
var config = this.config = application.config
= getConfigSettings( useCacheConfig = false )
;
// ... bootstrapping process ...
}
// ---
// PRIVATE METHODS.
// ---
/**
* I return the application's environment-specific config object.
*/
private struct function getConfigSettings( boolean useCacheConfig = true ) {
var configName = "appConfig_#this.name#";
if ( useCacheConfig && server.keyExists( configName ) ) {
return( server[ configName ] );
}
var config = server[ configName ] = deserializeJson( fileRead( PATH_TO_CONFIG_FILE );
// HACK: I'm having trouble with my "this.datasources" configuration bombing-out
// on server-start for reasons that I don't understand. Through trial-and-error,
// I've discovered that "touching" the "this.datasources" structure fixes this
// issue. As such, I'm going to see if this date/time-stamp can act as a "touch"
// on that value which will flush whichever cache is in an invalid state.
config.loadedAt = getTickCount();
return( config );
}
}
In the onApplicationStart() method, the call to getConfigSettings() passes in the flag, false, which means that it will bypass the server scope-cache. So, every time the ColdFusion application bootstraps, it reloads and re-caches the configuration data, giving it a new loadedAt timestamp. So, if the application gets into a bad-state after the server is restarted, it will keep trying to reload the config which will then "touch" the this.datasources object by giving it a unique _loaded_at_ throw-away property.
And, like I said above, this seems to work!
CAUTION: Part of why this is working is because my
onApplicationStart()method needs the datasource in order to preload some data; which is causing the application bootstrapping to fail; which means that the next request will try again to bootstrap the application. If myonApplicationStart()didn't need any data, the application would bootstrap successfully, even in a bad state. As such, the datasource would only fail later on when fetching data. This would be problematic because it wouldn't trigger a new bootstrapping process. As such, this entire HACK is predicated on the fact that the datasource needs to work during the bootstrapping process.
As far as I'm concerned, this is definitely a bug in ColdFusion. I can find no evidence, so far, that this has anything to do with how I am writing the code, especially since it only fails to bootstrap like 1 out of every 7 server restarts.
Here's the Adobe ColdFusion discussion forum where I was discussing this issue.
UPDATE: Database Or Pool Has Not Been Configured
In the previous section, I talked about including a "throw away" property in the this.datasources structure that would act as a cache-bust for whatever internal ColdFusion cache was in a bad state. And, while it appeared to work in so much as it allowed the ColdFusion application to finally bootstrap, I started to see other errors in the logs when I was programmatically manipulating the application state.
Sometimes, after I've updated a ColdFusion component, I need to re-initialize the application - without a restart - by calling the onApplicationStart() method programmatically. This instantiates new ColdFusion component instances and stores them in the application scope.
When I did this, I would see a variety of errors. Sometimes, I would see:
java.sql.SQLException: ERROR: Could not locate app_WwwBenNadelCom_bennadel. This usually means that the configuration data for this database or pool has not been configured.
Other times I would see:
java.sql.SQLException: ResultSet is from UPDATE. No Data.
And sometimes I would see:
java.lang.NullPointerException at checkColumnBounds()
When I was calling onApplicationStart() programmatically, it was applying a new tick count to the config, which was, in turn, applying a new _loaded_at_ cache-buster to the this.datasources structure. And, I believe that these errors were cause by me changing the datasource while the live application was actively receiving requests.
I decided to scrap the whole "cache busting" approach to the datasource configuration and go with something less clever and more brute force. Now, I set a special flag at the end of the onApplicationStart() that indicates that the application was successfully initialized. And, in my onError() ColdFusion application event-handler, I check for that flag. If it has not been set, then I call applicationStop() to kill the running application. And, what I'm seeing is that the next request will then - mostly likely - boot the application in a valid state.
Here's my latest Application.cfc that showcases this new approach (truncated for the demo):
component
output = false
hint = "I define the application settings and event handlers."
{
// Define application settings.
this.name = "WwwBenNadelCom";
this.applicationTimeout = createTimeSpan( 2, 0, 0, 0 );
this.sessionManagement = false;
this.setClientCookies = false;
this.config = getConfigSettings();
// Define the datasources and default datasource.
// --
this.datasources = {
// CAUTION: The datasource names in this struct are CASE-SENSITIVE and need to be
// quoted. I'm not sure if this is because per-application preserve-case settings
// do not appear to apply here; or, if something else is going on.
"bennadel": {
username: this.config.dsn.username,
password: this.config.dsn.password,
driver: "MySQL",
class: "com.mysql.jdbc.Driver",
// .... nothing special here ....
}
};
this.datasource = "bennadel";
// ---
// LIFE-CYCLE METHODS.
// ---
/**
* I get called once when the application is being bootstrapped. This method is
* inherently single-threaded by the ColdFusion application server.
*/
public void function onApplicationStart() {
// .... truncated initialization for demo ....
// .... truncated initialization for demo ....
// .... truncated initialization for demo ....
// As the very last step in the initialization process, we want to flag that the
// application has been fully bootstrapped. This way, we can test the state of the
// application in the onError() event handler.
application.isBootstrapped = true;
}
/**
* I handle uncaught errors within the application.
*/
public void function onError(
required any exception,
string eventName = ""
) {
var error = ( exception.rootCause ?: exception.cause ?: exception );
// If the bootstrapping flag is null, it means that the application failed to
// fully initialize. However, we can't be sure where in the process the error
// occurred, so we want to just stop the application and let the next inbound
// request re-trigger the application start-up.
if ( isNull( application.isBootstrapped ) ) {
cfheader( statusCode = 503, statusText = "Service Unavailable" );
writeOutput( "<h1> Service Unavailable </h1>" );
writeOutput( "<p> Please try back in a few minutes. </p>" );
try {
applicationStop();
} catch ( any stopError ) {
// Swallow error, let next request start application.
}
return;
}
// .... truncated for demo ....
}
}
As you can see, the very last line of the onAplicationStart() method is setting the isBootstrapped to true. Then, if this flag is null in the onError() handler, it means that the ColdFusion application wasn't able to start-up successfully. In that case, I call applicationStop() to kill the current memory-space (or whatever that built-in function does).
Once the application has been stopped, the next request to the ColdFusion application boots just fine, the datasources are defined, and all the data gets pre-cached as expected. And, if I programmatically call the onApplicationStart() method to refresh some components, nothing breaks because none of the datasource properties get changed (since they are essentially static).
This is feeling like the right solution, finally! I'll be sure to report back if I run into any more issues.
Want to use code from this post? Check out the license.
Reader Comments
It might be worth noting that this is much easier to figure out in Lucee CFML because they have a feature wherein you can go into the server administration and then export your
Application.cfcsettings:https://dev.lucee.org/t/lucee-configuration-options/321/4
So, basically, you can use the Admin GUI to get everything the way you want it, and then go about exporting a bunch of settings to see what the code-based approach should look like. That's pretty freakin brilliant if you ask me 😮
Your comment above stole the first thing I was going to point out 😃 Lucee makes this MUCH simpler by giving you an export feature. You can export your entire Application settings from the Lucee admin UI or you can just edit a single datasource in the Lucee admin, scroll to the bottom, and get just the struct literal for that datasource. Super handy.
Regarding committing your
.cfconfig.json, I think you covered most of it. There's nothing wrong with committing it, even if you just use CommandBox for local dev (your deployment script can remove it if you don't need it on prod). The only thing to look out for is your CFConfig will have passwords in it by default so you need to treat it just as importantly as you would the XML files in your CF home that stores the same info. That's where your.envfile or just Docker secrets come into play. You can swap out any data that's sensitive, or perhaps just different per environment automatically as you export the JSON so there's no manual massaging of the file necessary.https://cfconfig.ortusbooks.com/using-the-cli/command-overview/export-settings#json-expansion-replacements
So now you can just do your export like so
(this is also configurable globally as a one-time setup) and then your JSON file will automatically have the
%{DB_PASSWORD}place holders swapped out for the real password, AND we'll seed your.envfile to haveso you're ready to go and never have to worry about accidental committing anything sensitive to the repo.
@Ben,
In Lucee you can also see how to set any individual setting by clicking the
<?/>next to the setting in the Lucee admin.@Brad,
I didn't even think about the fact that Docker can provide secrets as well. I don't think it clicked that an ENV is an ENV is an ENV - it doesn't matter where it comes from (Docker vs.
.envfile). Sometimes I just miss the forest for the trees since I'm not as familiar with some of these modules.One of these days, I really have to do a deep-dive on CommandBox.
@Andrew,
Heck yeah! They make it so freakin' sweet! This seems like something that the Adobe team could easily build in to the admin.
Yep, that's the beauty of them. So when you use a placeholder like
${foo}in a JSON file, it can actually come from ANY of these placesset foo=barcommand.envfile.box.envfile in your user homeserver.jsonfile'senvstructureAnd that's not even all the places but you get the picture-- no matter how or where you load them from, the JSON placeholders will find them and use them. That's why we use
.envfiles on development to "spoof" the same env vars we set in production using Docker Secrets. That way our config and code stays exactly the same across all our environments and we just swap out the mechanism for loading the vars!Ortus has put on several 1 and 2 day full in-person workshops on CommandBox. We'll be having two days of workshops before Into The Box this Sept in Houston and one of them will probably be on CommandBox. This is your perfect opportunity to come get your mind blown at ITB and take in some workshop goodness :)
@Brad,
I might have to take you up on that. Plus, I only have vague recollections of what "human contact" is like! I'm kind of itchy to get to see some people in person again.
I now we're a little off topic now, but I promise you if you come to ITB you won't be disappointed. Heck, we'd probably even let you give a talk on something 😉
@All,
Over on Twitter, John Pansewisz asked where the
this.configis defined. This is coming out of a config file that I am loading and then caching so that I don't have to read it off disk on every single request:www.bennadel.com/blog/4197-performing-a-double-check-lock-around-run-once-code-in-coldfusion.htm
This was the topic of a previous post on the double-check lock pattern.
That said, it would have been nicer to be able to just pull the values out of the ENV (as Brad and I have been discussing above) and not have to worry about file I/O at all.
@All,
Ok, for some reason which I don't understand, this broke when I just restarted my ColdFusion server. I jumped into the server and quickly comment-out the
urlmapsub-section, and it started working again. I am not sure why this happened, especially since I had to restart the server the first time I put the per-application datasource in place.Just another reason that this really needs much better documentation.
For some reason, when I teardown and rebuild my Docker of Mac container locally, it all still works. I don't know what it is about the Windows environment (production) that is failing. Or maybe it has to do with how ColdFusion is persisting some settings on server-shutdown that is not happening when I just kill my Docker container? If I can figure out more, I'll post back.
Ok, so I just restarted by ColdFusion service after changing some JVM settings and I was running into the same issue - the datasource couldn't be found. I had made no changes to the actual code in that time. So, I restarted the ColdFusion service again and suddenly it started working again. I have no idea what the heck is going on here.
I created a post on the Adobe ColdFusion Discussion forum to see if anyone else has any ideas.
Ok, I think I may have found a crazy path forward. It seems that when the application got into this bad state where the datasource couldn't be found, then any "touch" of the
this.datasourcesstructure (ie, changing any of the properties) was enough to clear whatever buggy caching is happening and fix the application state.This got me wondering if including a throw-away, dynamic property in the datasource configuration could act as this touch? To test, I started including a date/time-stamp of when the config was cache in the
serverscope; and then, including that in the datasource:I know this is 🤪 crazy 🤪 but, it seems to work! So, sometimes you gotta get a bit crazy. Hopefully the fix wasn't a false-positive. I'll update the post with some of this information.
I've updated the blog post with a section on debugging this issue. I'm still seeing something funky in the bootstrapping process. But, at least the app is starting up.
OK, I think I finally found a way to solve this problem in a way that doesn't cause fall-out issues as well. Ultimately, I just have to use
applicationStop()when the state of the ColdFusion internal cache is corrupted. I'll post another update to the blog shortly.I've updated the post with a new section outlining the latest
applicationStop()approach to fixing the wonky state issue. I am feeling good about this one - least clever, most brute force.I noticed that I was seeing a deprecation warning in my startup logging:
I went into the per-application data source and changed:
class: "com.mysql.jdbc.Driver",to:
class: "com.mysql.cj.jdbc.Driver",... and I'm seeing that this deprecation warning is no longer showing up. Apparently this is part of the changes in the Connector/J 8 API.
I have a somewhat similar issue. I suspect it's related in some way.
I have a CF2021 using MariaDB. I have installed the JDBC connector. My app runs for maybe an hour and then starts throwing java.io.IOException: java.lang.reflect.InvocationTargetException. All it takes to get rid of the error is for me to go into the cfadmin and open that DSN and hit submit without changing anything. I know you are setting the DSN in your Application.cfc and I am not but I'm wondering if you are spinning your wheels trying to figure out why application.datasources isn't acting right when the real issue is buried deeper.
@Keith,
I really wish I understood more about how all the datasource stuff worked. Well, I guess, I really just wish that it worked better 🤣 I'd be fine not having to know about it.
At work, we ran into a lot of issues when trying to tweak timeout settings on the connections. Our data-services team wanted to go in and make all the connections timeout after 5-mins. And, no matter what we did on the ColdFusion side, we could not get our data-sources to lower how long they stays in the connection pool. So, every few minutes, we'd get a load of errors about broken connections. We tried every setting we could think of, and nothing seemed to work.
When ColdFusion's abstractions work, it's a huge productivity boost; but, when they go wonky, you gotta pay that price. I wish I had better advice.
@Keith,
A very similar issue to what Keith experienced happened to me.
I had a lucee app in a docker-compose project.
The lucee container was not connecting to the MySQL container. But both could talk to each other. When I would check if the database server was up, using some 3rd party tools. It would work and the tool could connect. As soon as it was touched by the tool, the lucee container would connect to the database.
But not until something "touched" or as I was putting it "primed" the server with the other tool. Same thing would happen in lucee admin if I just verified the database.
In the end the solution was to move the data source to application.cfc and the problem went away.
Took me 3 days of chasing "computer ghosts" to figure out this most bizarre issue.
All the clues were here in Ben's post and your comment lit the light above my head.
Big love to Ben as his blog is my "rock" and has been my entire 20 year cfml journey!
@Stephen,
That sounds like a super frustrating problem! Glad you were able to solve it, at least, by moving the datasource configuration into the app code. Personally, I am trying to move more and more of my configuration into the code so that it can be persisted in the Git repository along with everything else. It feels more collocated and less magical.
And thank you for the kind words! 😊 This CFML journey has been awesome. I'm not ready to get off the ride yet 💪
@Stephen,
I think the way we ended up resolving it was to roll back to an older version of the JDBC driver but honestly I've slept since then and once the issue went away I moved on. I should have come back here and updated.
Oh, that is interesting. Not sure how that can even be done in a container the way things are packaged up these days.
In my troubleshooting I attempted downgrading Lucee to see if that would help and same with the mysql container. But that all failed. Especially since it wasn't actually mysql at fault (but it sure seemed like it for a bit there).
Anyway I sure hope the fellows at Ortis Solutions can solve this for the next version of the commandbox container.
But as Ben stated, seems like going forward best practice should be to use Application.cfc.
Cheers for the response fellas! Keep up the good work!
PS. I'm just noticing now that Ben puts a pic of Arnie for everyone that doesn't have a pic on his blog. For years i've noticed the Arnie and thought it was just one guy using his likeness. 🤣😂 Now its suddenly me! That made my morning.
So honoured for the reply Ben. Love your work. You've been inspiring me for years. And I found your podcast last year and I love it. I work from home so I don't have fellow colleagues to banter with. Your podcast gives me a bit of banter that I crave. I sometimes fantasize about being on it with you guys. Maybe one day!
Biggups for running such a useful resource of a blog!
Stephen
@Stephen,
Ha ha, I keep forgetting that Arnold is the default avatar :D I should come up with a way for people to set one.
@Stephen,
🙌 Thanks for listening to the podcast! We have a good time. Though, I want to talk about code more than they will let me -- I'll wear them down eventually.
I just tried to update my local development environment to use Adobe ColdFusion 2023 and my
this.datasourcesis no longer working. No error, it simply says, "Datasource bennadel could not be found.". Nothing I seem to do is working.I also tried creating an instance of the CFAdmin API and accessing the datasources that way (to see the details). I had to completely disable CFAdmin security to even get that to work (locally only, of course). And, even if I take the values in that output and copy/paste them into my
Application.cfc, still broken.Very frustrating! 😱
I'm continuing to have trouble figuring out how to get this working in ColdFusion 2023. I've been dropping some comments on the Adobe Blog:
https://coldfusion.adobe.com/2022/03/the-struct-keys-in-this-datasources/
The last thing I've tried is using the most simple, hardcoded approach that I can use. Which works when I build my Docker image with 2021. But, when I build the same exact code with 2023, the code breaks.
I created a GitHub reproduction repo for the Adobe ColdFusion 2021 vs 2023 issue:
https://github.com/bennadel/ColdFusion-Per-App-Datasource-Test
So, Dan G. Switzer had a great insight - he suggested that the GraphQL error that I'm seeing might be breaking the whole startup process. So, I tried installing the GraphQL package (even though I'm not using it) and suddenly the MySQL datasource is working 😳
I've add this note to the GitHub repo.
Mark Takata - ColdFusion technical evangelist - has confirmed that this is a bug (2023 per-app datasources being broken). They are intending to have it fixed in the next updater.
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →