[Testbox 4.5.0] Datasource Verification Failed with Cachebox + JDBC Store

I have an integration test for an API that utilizes the JDBC store in Cachebox. My test checks to make sure the API can properly cache and return data from the database.

However, the integration test always fails and returns an error when using an ACF engine (ACF 2018+):

General application error: Datasource myDsn verification failed.
Detail: The root cause was that: java.sql.SQLException: Usernames and Passwords for all the database tags within the cftransaction tag must be the same.

StackTrace: coldfusion.tagext.sql.QueryTag$DataSourceVerificationException: Datasource myDsn verification failed.
   at coldfusion.tagext.sql.QueryTag.validate(QueryTag.java:634 undefined)
   at coldfusion.tagext.sql.QueryTag.doValidate(QueryTag.java:775 undefined)
   at coldfusion.tagext.sql.QueryUtils.executeQuery(QueryUtils.java:68 undefined)
   at coldfusion.runtime.CFPage.QueryExecute(CFPage.java:12477 undefined)
   at cfJDBCStore2ecfc1077390540$funcGET.runFunction(D:\...\coldbox\system\cache\store\JDBCStore.cfc:198 undefined)
...

I suspect this issue is the result of my BaseIntegrationSpec which wraps everything in a transaction

/**
 * This function is tagged as an around each handler.  All the integration tests we build
 * will be automatically rolled backed. No database corruption
 * 
 * @aroundEach
 */
function wrapInTransaction( spec ) {
    transaction action="begin" {
        try {
            arguments.spec.body();
        } catch ( any e ){
            rethrow;
        } finally {
            transaction action="rollback";
        }
    }
}

…and I know the JDBC store also uses transaction in the get() method:

/**
     * Get an object from the store with metadata tracking, or null if not found
     *
     * @objectKey The key to retrieve
     */
    function get( required objectKey ){
        var normalizedID = getNormalizedID( arguments.objectKey );

        transaction{
            // select entry
            var q = queryExecute(
                "SELECT *
                 FROM #variables.table#
                 WHERE id = ?
                ",
                [ normalizedID ],
                {
                    datsource   = variables.dsn,
                    username    = variables.dsnUsername,
                    password    = variables.dsnPassword
                }
            );

            // Update stats if found
            ...
            
        } // end transaction

The nested transaction seems to cause problems likely because there are other queries present in the request that don’t specify credentials identical to the JDBC store (yet they all use the same DSN). Any help or workarounds that someone could provide would be most welcome!

Edit:
If I manually edit JDBCStore.cfc and remove the credentials from each query so…

{
    datsource   = variables.dsn,
    username    = variables.dsnUsername,
    password    = variables.dsnPassword
}

…becomes…

{}

…everything works. However, I understand that JDBCStore needs the flexibility to be able to define a datasource.

Update: I filed a bug with Adobe in the meantime. Maybe if they get enough votes they will remove this limitation.

Is that just a typo here? Didn’t test if this should throw an exception within queryExecute(), but shouldn’t it just be datasource?

Yeah, that typo exists in the current release of Coldbox. It is fixed in the development branch. However, I’ve tested it by manually fixing the typo myself but ACF still barfs when trying to validate the datasource.

I created a pull request for Coldbox that creates a workaround for ACF’s limitation. I tested it locally with Lucee + ACF and it works as expected. For some reason, the Github tests fail, but it doesn’t seem to be related to the changes I made as far as I can tell.

     [exec] Executing tests http://localhost:8599/tests/runner-integration.cfm?&recurse=true&reporter=json&verbose=false please wait...
     [exec] 
     [exec] !! tests.specs.integration.EventCaching (1099 ms)
     [exec] [Passed: 8] [Failed: 0] [Errors: 1] [Skipped: 0] [Suites/Specs: 2/9]
     [exec] 
     [exec]     !! Event Caching 
     [exec]         !! can do cached events with custom provider annotations (44 ms) 
     [exec]             -> Error: The invalidEventHandler event (main.onInvalidEvent) is also invalid: 
     [exec]             -> at /home/runner/work/coldbox-platform/coldbox-platform/system/web/services/HandlerService.cfc:473 
     [exec] 
     [exec]             471: 				);
     [exec]             472: 				// Now throw the exception
     [exec]             473: 				throw( message: exceptionMessage, type: "HandlerService.InvalidEventHandlerException" );
     [exec]             474: 			}
     [exec]             475: 
     [exec]             
     [exec]             -> at /home/runner/work/coldbox-platform/coldbox-platform/system/web/services/HandlerService.cfc:324 
     [exec]             -> at /home/runner/work/coldbox-platform/coldbox-platform/system/web/Controller.cfc:757 
     [exec]             -> at /home/runner/work/coldbox-platform/coldbox-platform/system/web/Controller.cfc:667 
     [exec]             -> at /home/runner/work/coldbox-platform/coldbox-platform/test-harness/models/ControllerDecorator.cfc:71 
1 Like

I also created a “DSN Store” for anyone to use in the meantime until my change is implemented into Coldbox. The DSN Store works nearly identical to the JDBC Store except it will use the DSN set in your Application.cfc.

Gist: DSN Store for Cachebox which uses the DSN set in Application.cfc. This was derived from the default JDBC Store. · GitHub

If you put the CFC in a folder called /models/cache/ your configuration might look something like this if you wanted to keep your cached data for a week with 1000 items cached max:

// Cachebox.cfc
myDsnCache = {
    provider = "coldbox.system.cache.providers.CacheBoxColdBoxProvider",
	properties = {
		objectDefaultTimeout = 10080, // 1 week
		useLastAccessTimeouts = false,
		freeMemoryPercentageThreshold = 0,
		reapFrequency = 60,
		evictionPolicy = "LRU",
		evictCount = 10,
		maxObjects = 1000,
        resetTimeoutOnAccess = false,
		objectStore = "models.cache.DSNStore",
        tableAutoCreate = true,
        table="dsnCache"
	}
}