CFadmin UpdateDatasource syntax

Nick_Batt · March 7, 2025, 11:41pm

Still on my journey to launch a server instance from a user data script, I’m getting there, but I’m stuck in the adding a datasource step.
I’m creating a temporary .cfm
file to run once then be deleted, this contains:

<cfadmin
    action="updateDatasource"
    type="server"
    password="adminpass"
    classname="com.mysql.cj.jdbc.Driver"
    dbdriver="mysql"
    dsn="mydsn"
    name="mydsn"
    newName="mydsn"
    host="mydburl"
    database="sql5"
    port="3306"
    dbusername="myuser"
    dbpassword="mypass"
    allowMultiQueries = "true"
    blob="false"
    clob="false"
    allowed_select="true"
    allowed_insert="true"
    allowed_update="true"
    allowed_delete="true"
    allowed_alter="false"
    allowed_drop="false"
    allowed_revoke="false"
    allowed_create="false"
    allowed_grant="false"
    remoteClients="arrayOfClients"
>

But this now throws the error:

Cannot invoke "java.sql.Connection.close()" because "this.connection" is null

Which is nort terribly informative. and the documentation is not 100% clear… any ideas?
Don’t forget to tell us about your stack!

OS: Alma Linux 9
Java Version: 21.0.6
Tomcat Version: 10.1.36
Lucee Version: 6.2.0.321

Zackster · March 8, 2025, 9:05am

Hmm, can you file a bug, we can improve that

I think there’s a verify attribute? If you look at the src code for the lucee admin you’ll find the working examples

github.com

lucee/Lucee/blob/6.2/core/src/main/cfml/context/admin/services.datasource.create.cfm

<cfparam name="error" default="#struct(message:"",detail:"")#">
<cfset stText.Settings.requestExclusive="Exclusive connections for request">
<cfset stText.Settings.requestExclusiveDesc="If set to true, the connections of this datasource are exclusive to one request, guaranteeing that you always have the same connection to the datasource for the request. Keep in mind that this limits the maximum possible concurrent requests to the maximum possible datasource connections and can cause a lot more open connections. Only use the requestExclusive setting when it is absolutely necessary to always have the same connection within a request, for example when your connection has set a specific state.">

<cfset stText.Settings.alwaysResetConnections="Always reset connection to default values before use">
<cfset stText.Settings.alwaysResetConnectionsDesc="This is only necessary in case you are using ""SET TRANSACTION ISOLATION LEVEL"" or ""SET AUTOCOMMIT"" within your SQL code. Enable this feature will increase traffic for this datasource.">




<!--- ACTIONS --->
<cftry>
	<cfif StructKeyExists(form,"_run") and form._run EQ stText.Settings.flushCache>
		<cfset DatasourceFlushMetaCache(form.name)>
		
	<cfelseif StructKeyExists(form,"run") and form.run EQ "create2">
		
		<cfset dbdriver = form.type>

		<cfset driver=createObject("component", drivernames[ dbdriver ])>

This file has been truncated. show original

Nick_Batt · March 8, 2025, 9:50am

I confirmed this works on my local CommandBox dev machine (Lucee 5.4.6.9)
Servlet container: WildFly / Undertow - 2.2.37.Final
Java 11.0.26
Mac OS X (12.7.2) 64bit

Does not work on Alma 9 install using the latest Lucee installer (Lucee 6.2.0.321)

I’ll look at a submitting a bug report, not done before…

Zackster · March 8, 2025, 11:11am

Always include the stacktrace, it really helps

You can snip out the middle section (where you see the cfc/cfml line), it’s always the same with Catalina etc, but there’s often a caused by section (s) at the bottom which should be always included

Nick_Batt · March 8, 2025, 11:49am

Ok sorry! here it is:

lucee.runtime.exp.NativeException: Cannot invoke "java.sql.Connection.close()" because "this.connection" is null
 at lucee.runtime.db.DatasourceConnectionImpl.close(DatasourceConnectionImpl.java:295)
 at lucee.runtime.tag.Admin._doVerifyDatasource(Admin.java:2920)
 at lucee.runtime.tag.Admin.doUpdateDatasource(Admin.java:2699)
 at lucee.runtime.tag.Admin._doStartTag(Admin.java:729)
 at lucee.runtime.tag.Admin.doStartTag(Admin.java:359)
 at config_db_cfm$cf$4d.call(/config_db.cfm:25)
 at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1063)
 at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:987)
 at lucee.runtime.listener.ClassicAppListener._onRequest(ClassicAppListener.java:63)
 at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:42)
 at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2785)
 at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2772)
 at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2743)
 at lucee.runtime.engine.Request.exe(Request.java:45)
 at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1099)
 at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1056)
 at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
 at lucee.loader.servlet.jakarta.CFMLServlet.service(CFMLServlet.java:49)
 at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
 at org.apache.catalina.filters.RemoteIpFilter.doFilter(RemoteIpFilter.java:924)
 at org.apache.catalina.filters.RemoteIpFilter.doFilter(RemoteIpFilter.java:980)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
 at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:663)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
 at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397)
 at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
 at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905)
 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
 at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
 at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
 at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
 at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NullPointerException: Cannot invoke "java.sql.Connection.close()" because "this.connection" is null
 ... 45 more

Nick_Batt · March 8, 2025, 12:02pm

I tried adding the verify attribute (my local dev box doesn’t require this) same error.
I also checked in on another Alma 9 working install I have running serving my dev site and got the same error.

Zackster · March 8, 2025, 12:15pm

the connection is coming back null, hence the error

github.com

lucee/Lucee/blob/6.2/core/src/main/java/lucee/runtime/tag/Admin.java#L2920


      
          		}
          		catch (Throwable t) {
          			ExceptionUtil.rethrowIfNecessary(t);
          			throw Caster.toPageException(t);
          		}
          	}
          
          	private void _doVerifyDatasource(DataSourcePro ds, String username, String password) throws PageException {
          		try {
          			DatasourceConnectionImpl dc = new DatasourceConnectionImpl(null, ds.getConnection(config, username, password), ds, username, password);
          			dc.close();
          		}
          		catch (Exception e) {
          			throw Caster.toPageException(e);
          		}
          	}
          
          	private void _doVerifyDatasource(String name, String username, String password) throws PageException {
          
          		DataSourceManager manager = pageContext.getDataSourceManager();
          		manager.releaseConnection(pageContext, manager.getConnection(pageContext, name, username, password));

does adding the datasource via the admin work with the same config?

Nick_Batt · March 8, 2025, 12:21pm

It may be because this DSN needs SSL to connect - I dont see an explicit attribute for that in the examples

Nick_Batt · March 8, 2025, 5:02pm

Yes it does, though its not entirely obvious which attributes in the cfadmin tag map to which items in the actual admin interface and there appears to be no clear definition I can find. Most use values that are placeholders of variables rather than examples of the format of the data

I have now got pretty much everything working - apart from the add datasource…

Nick_Batt · March 12, 2025, 10:39am

I’ve double checked that the IP authorized to connect to the DB instance and created a DSN with the same connection details s via the admin interface.
But this does not work using CFadmin.

Hoping to find a solution so I can complete the scripted launch

Zackster · March 12, 2025, 10:50am

the admin is using the exact same cfadmin tag, so there’s a problem with your code somehow.

let me check

but these days you can just grab a snippet of the configured json from your local instance and either use configImport, or drop the .CFConfig.json file in the lucee-server/deploy directory

Zackster · March 12, 2025, 10:56am

so I added verify=false to you code and it worked without error,

but the connection was invalid, I then updated with my local db name, host and creds via the admin and it verified ok

<cfadmin
    action="updateDatasource"
    type="server"
    password="adminpass"
    classname="com.mysql.cj.jdbc.Driver"
    dbdriver="mysql"
    dsn="mydsn"
    name="mydsn"
    newName="mydsn"
    host="mydburl"
    database="sql5"
    port="3306"
    dbusername="myuser"
    dbpassword="mypass"
    allowMultiQueries = "true"
    blob="false"
    clob="false"
    allowed_select="true"
    allowed_insert="true"
    allowed_update="true"
    allowed_delete="true"
    allowed_alter="false"
    allowed_drop="false"
    allowed_revoke="false"
    allowed_create="false"
    allowed_grant="false"
    verify="false"
    remoteClients="arrayOfClients"
>

Nick_Batt · March 12, 2025, 11:24am

~~Beautiful~~, ~~that~~ ~~got~~ it! ~~Final~~ ~~version~~ of the ~~code~~ ~~that~~ ~~worked~~ is:

<cfadmin       
    action="updateDatasource"
    type="server"
    password="mypword"
    classname="com.mysql.cj.jdbc.Driver"
    dsn="mydsn"
    name="mydsn"
    newName="mydsn"
    host="myhost"
    database="mydb"
    port="myport"
    dbusername="myuser"
    dbpassword="mypass"
    blob="false"
    clob="false"
    allowed_select="true"
    allowed_insert="true"
    allowed_update="true"
    allowed_delete="true"
    allowed_alter="false"
    allowed_drop="false"
    allowed_revoke="false"
    allowed_create="false"
    allowed_grant="false"
    verify="false">

Nick_Batt · March 12, 2025, 12:55pm

Ah I spoke too soon - the Type is not being set correctly, I am, using:

 classname="com.mysql.cj.jdbc.Driver"
    dbdriver="mysql"

Results in the Type being set to : Other - JDBC Driver as displayed in the DS Admin , so it will not connect when verified.
The code does run though

Nick_Batt · March 12, 2025, 1:37pm

Sorry to keep adding, I have some unexpected behaviour now.

<cfadmin
    action="updateDatasource"
    type="server"
    password="myparam"
    classname="com.mysql.cj.jdbc.Driver"
    dbdriver="MySQL" \\ does not seem to be set correctly
    dsn="myparam"
    name="myparam"
    newName="myparam"
    host="myparam"
    database="myparam"
    allowMultiQueries = "true" \\ this is not set
        port="25060"
    useUnicode="true" \\ this is not set
    useSSL="true"   \\this is not set
    dbusername="myparam"
    dbpassword="myparam"
    blob="false"
    clob="false"
    allowed_select="true"  \\  all of these work
    allowed_insert="true"   \\
    allowed_update="true" \\
    allowed_delete="true" \\
    allowed_alter="false" \\
    allowed_drop="false" \\
    allowed_revoke="false" \\
    allowed_create="false" \\
    allowed_grant="false" \\

    verify="false">

Results in the DSN being created with the Other JDBC type. This wont verify.
However, editing that DS manually with use SSL, Unicode and allow mulitple queries (which dont appear to be set in the CFADMIN tag this way, nor in custom=“useSSL=True etc”) and then checking verify, seems to magically set the driver Type to MySQL and then it will verify.

Then running the above Cfadmin tag with verify=“true”, results in this error:

Cannot invoke "java.sql.Connection.close()" because "this.connection" is null

So I guess ultimately there is some wonky behaviour

Stack trace:

lucee.runtime.exp.NativeException: Cannot invoke "java.sql.Connection.close()" because "this.connection" is null
 at lucee.runtime.db.DatasourceConnectionImpl.close(DatasourceConnectionImpl.java:295)
 at lucee.runtime.tag.Admin._doVerifyDatasource(Admin.java:2920)
 at lucee.runtime.tag.Admin.doUpdateDatasource(Admin.java:2699)
 at lucee.runtime.tag.Admin._doStartTag(Admin.java:729)
 at lucee.runtime.tag.Admin.doStartTag(Admin.java:359)
 at config_db_cfm315$cf$cp.call(/config-db.cfm:30)
 at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1063)
 at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:987)
 at lucee.runtime.listener.ClassicAppListener._onRequest(ClassicAppListener.java:63)
 at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:42)
 at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2785)
 at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2772)
 at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2743)
 at lucee.runtime.engine.Request.exe(Request.java:45)
 at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1099)
 at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1056)
 at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
 at lucee.loader.servlet.jakarta.CFMLServlet.service(CFMLServlet.java:49)
 at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
 at org.apache.catalina.filters.RemoteIpFilter.doFilter(RemoteIpFilter.java:924)
 at org.apache.catalina.filters.RemoteIpFilter.doFilter(RemoteIpFilter.java:980)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
 at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:670)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
 at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397)
 at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
 at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905)
 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
 at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
 at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
 at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
 at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NullPointerException: Cannot invoke "java.sql.Connection.close()" because "this.connection" is null
 ... 45 more
Timestamp	3/12/25 1:14:11 PM UTC

Nick_Batt · March 12, 2025, 11:58pm

Blockquote
but these days you can just grab a snippet of the configured json from your local instance and either use configImport , or drop the .CFConfig.json file in the lucee-server/deploy directory

That got it. I got the script to write the contents of my .CFconfig.json taken from a working server and bingo. Thanks so much for your patience and help.
No need for any CFadmin stuff at all.