BIT fields always return 0 even when the value is 1 (affects version 3.x)

Question

BIT fields always return 0 even when the value is 1 (affects version 3.x)

cfsimplicity opened this issue 3 years ago · 20 comments

QueryExecute( "DROP TABLE IF EXISTS `bitfields`" )
QueryExecute( "CREATE TABLE `bitfields` ( `test` bit(1) NOT NULL DEFAULT b'0' )" )
QueryExecute( "INSERT INTO bitfields ( test ) VALUES ( 1 )" )
// value verified to be saved as 1 using another client tool 
result = QueryExecute( "SELECT * FROM bitfields" )
// expect( result.test[ 1 ] ).toBe( 1 )
dump( result.test[ 1 ] ) //returns 0 instead of 1

Using the connector directly via Java returns the correct value which suggests this is something to do with the way Lucee is handling the 3.x driver result.

Downgrading the driver to 2.7.1 fixes the issue.

Answer 1 · 2022-04-06T14:07:20.000Z

This issue appeared with the 3.0.4 release, which I have withdrawn as a result.

Answer 2 · 2022-05-18T10:31:17.000Z

Hey, sorry, know this is closed, but wondering what version of Maria DB you tried 3.0.4 on?

Answer 3 · 2022-05-18T12:01:58.000Z

This issue (BIT fields) is still open. I'm using MariaDB 10.6.5.

Answer 4 · 2022-06-29T15:16:34.000Z

@andyj Good news, 3.0.6 just released appears to fix this issue. Would you mind testing develop (i.e. manually install the .lex file on the updated develop branch) before I release?

Answer 5 · 2022-06-30T09:50:57.000Z

Here are the relevant Connector/J tickets marked as fixed in 3.0.6:

ResultSet.getObject() returns ByteSet instead of Byte[] for BIT
ResultSet.getObject() returns Byte instead of Boolean for tinyint(1)

Answer 6 · 2022-07-05T10:54:17.000Z

@cfsimplicity I'm using Debian 11.3 MariaDB 10.5.15-MariaDB-0+deb11u1-log with Lucee 5.3.8.206.
I'm experienced Java developer, old ColdFusion developer who is new to Lucee and ".lex" files.
What can I do help resolve this issue and get 3.0.6 released?

Answer 7 · 2022-07-05T11:06:34.000Z

Thanks @Conrad-T-Pino . If you could simply test 3.0.6 in your environment that would be great. I've been doing so for a few days and not yet encountered any issues so am hopeful it's safe to release this time.

To install the latest extension manually, download the .lex file from:

https://github.com/cfsimplicity/lucee-mariadb/blob/develop/lucee-mariadb.lex

Then go into your Lucee Server admin UI and look for "Upload new extension" at the bottom of the Extension > Applications section.

It should immediately replace your current MariaDB extension.

Answer 8 · 2022-07-05T14:25:48.000Z

Thank you @cfsimplicity!
What are directions to revert back to 2.7.1 after manual update?
Should I be looking for specific MaraiDB failure modes?

Answer 9 · 2022-07-05T14:30:21.000Z

To revert, you can just go into the extension in the admin UI and downgrade (at the bottom of the page). If that fails for any reason you could manually upload the 2.7.1 lex downloaded from the master branch.

Answer 10 · 2022-07-05T14:30:53.000Z

@cfsimplicity Didn't like that .lex
lucee.runtime.exp.ApplicationException: The Extension [/home/tomcat/webapps/ROOT/WEB-INF/lucee/temp/lucee-mariadb.lex] is invalid,no Manifest file was found at [META-INF/MANIFEST.MF]. at lucee.runtime.extension.RHExtension.load(RHExtension.java:382) at lucee.runtime.extension.RHExtension.init(RHExtension.java:235) at lucee.runtime.extension.RHExtension.(RHExtension.java:224) at lucee.runtime.config.XMLConfigAdmin.updateRHExtension(XMLConfigAdmin.java:4682) at lucee.runtime.config.XMLConfigAdmin._updateRHExtension(XMLConfigAdmin.java:4672) at lucee.runtime.tag.Admin.doUpdateRHExtension(Admin.java:4288) at lucee.runtime.tag.Admin._doStartTag(Admin.java:739) at lucee.runtime.tag.Admin.doStartTag(Admin.java:349) at ext_applications_upload_cfm966$cf.call(/admin/ext.applications.upload.cfm:53) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1034) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:926) at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:907) at ext_applications_cfm184$cf.call(/admin/ext.applications.cfm:113) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1034) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:926) at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:907) at web_cfm$cf.call(/admin/web.cfm:492) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1034) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:926) at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:907) at server_cfm$cf.call(/admin/server.cfm:2) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1034) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:926) at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:217) at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44) at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2460) at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2450) at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2421) at lucee.runtime.engine.Request.exe(Request.java:45) at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1179) at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1125) at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97) at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51) at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:667) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346) at org.apache.coyote.http2.StreamProcessor.service(StreamProcessor.java:404) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.http2.StreamProcessor.process(StreamProcessor.java:74) at org.apache.coyote.http2.StreamRunnable.run(StreamRunnable.java:35) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.base/java.lang.Thread.run(Thread.java:829)

Answer 11 · 2022-07-05T14:36:15.000Z

@Conrad-T-Pino let's check file download
tomcat@quartz:~$ sha256sum /home/tomcat/webapps/ROOT/WEB-INF/lucee/temp/failed-to-deploy/lucee-mariadb.lex
4696a7969e97c7c8ba65ae75e1ed22accf42d57b228d5e5f22e687d1edc7200b /home/tomcat/webapps/ROOT/WEB-INF/lucee/temp/failed-to-deploy/lucee-mariadb.lex

Answer 12 · 2022-07-05T14:36:41.000Z

That's strange. It worked fine for me and I've just uninstalled and repeated the manual upload without any problems. Your error message says there's no manifest, but the .lex definitely has one. Try opening it up with 7-zip to check if it's been corrupted for some reason.

Answer 13 · 2022-07-05T14:38:45.000Z

@cfsimplicity I'm running on Debian; never used 7-zip - sha256sum agrees on download host and on Lucee host.

Answer 14 · 2022-07-05T14:42:22.000Z

@cfsimplicity - found it; link you posted is link to GitHub page ABOUT the file and NOT link TO the file.

Answer 15 · 2022-07-05T14:43:54.000Z

Ah that would explain it! There's a download button on that page. I should have been clearer, sorry.

Answer 16 · 2022-07-05T14:47:32.000Z

@cfsimplicity OK, 3.0.6 is running. Anything that you'd like to see get specific attention?

Answer 17 · 2022-07-05T14:51:24.000Z

@cfsimplicity Existing Lucee datasources working AS IS.

Answer 18 · 2022-07-05T14:52:19.000Z

If you have any tests that connect to the database, run those. Otherwise just check that things are as you expect.

I'm just putting together a very simple test suite to ensure the basic values/object types that have caused problems lately are checked going forward.

Answer 19 · 2022-07-05T15:02:01.000Z

I don't have test code per se; I'm developing new MariaDB system versioned tables intensive event registration system.
I'll look for your update regarding proposed simple test suite.

Answer 20 · 2022-07-09T03:36:31.000Z

@cfsimplicity - Houston, we have a problem with BOOLEAN type.

CREATE TABLE IF NOT EXISTS contact (
	ContactId INT UNSIGNED NOT NULL,
...
	PhoneMobile varchar(32) NULL,
	MobileNotice BOOLEAN NULL,
...
	RowStart BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,
	RowStops BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,
	PERIOD FOR SYSTEM_TIME(RowStart,RowStops),
	PRIMARY KEY (ContactId),
) ENGINE=InnoDB WITH SYSTEM VERSIONING;

https://mariadb.com/kb/en/sql-language-structure-boolean-literals/
v2.7.1 accepts above Boolean Literals v3.0.6 throws following exception.
Reverting back to v2.7.1 restores expected operation.

"Severity","ThreadID","Date","Time","Application","Message"
"ERROR","catalina-exec-23","07/08/2022","18:21:06","StoredProc","(conn=303) Incorrect integer value: 'true' for column ``.``.`inMobileNotice` at row 1;(conn=303) Incorrect integer value: 'true' for column ``.``.`inMobileNotice` at row 1;java.sql.SQLSyntaxErrorException: (conn=303) Incorrect integer value: 'true' for column ``.``.`inMobileNotice` at row 1
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:280)
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:368)
	at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:137)
	at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:824)
	at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:763)
	at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:682)
	at org.mariadb.jdbc.client.impl.StandardClient.executePipeline(StandardClient.java:559)
	at org.mariadb.jdbc.ServerPreparedStatement.executePipeline(ServerPreparedStatement.java:119)
	at org.mariadb.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:93)
	at org.mariadb.jdbc.ServerPreparedStatement.execute(ServerPreparedStatement.java:375)
	at lucee.runtime.tag.StoredProc.doEndTag(StoredProc.java:627)
	at webreg.delektest.action.billing_cfm$cf.call(/webreg/DelekTest/action/billing.cfm:110)
	at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1034)
	at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:926)
	at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:217)
	at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44)
	at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2460)
	at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2450)
	at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2421)
	at lucee.runtime.engine.Request.exe(Request.java:45)
	at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1179)
	at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1125)
	at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
	at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
..."
"ERROR","catalina-exec-23","07/08/2022","18:21:36","StoredProc","(conn=303) Incorrect integer value: 'false' for column ``.``.`inMobileNotice` at row 1;(conn=303) Incorrect integer value: 'false' for column ``.``.`inMobileNotice` at row 1;java.sql.SQLSyntaxErrorException: (conn=303) Incorrect integer value: 'false' for column ``.``.`inMobileNotice` at row 1
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:280)
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:368)
	at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:137)
	at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:824)
	at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:763)
	at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:682)
	at org.mariadb.jdbc.client.impl.StandardClient.executePipeline(StandardClient.java:559)
	at org.mariadb.jdbc.ServerPreparedStatement.executePipeline(ServerPreparedStatement.java:119)
	at org.mariadb.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:93)
	at org.mariadb.jdbc.ServerPreparedStatement.execute(ServerPreparedStatement.java:375)
	at lucee.runtime.tag.StoredProc.doEndTag(StoredProc.java:627)
	at webreg.delektest.action.billing_cfm$cf.call(/webreg/DelekTest/action/billing.cfm:110)
	at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1034)
	at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:926)
	at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:217)
	at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44)
	at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2460)
	at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2450)
	at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2421)
	at lucee.runtime.engine.Request.exe(Request.java:45)
	at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1179)
	at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1125)
	at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
	at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
..."

Lucee HTML excerpt:

<div>
	<label for="billingPhoneMobile">Mobile Phone:</label>
	<input id="billingPhoneMobile" name="billingPhoneMobile"
		type="text" maxlength="20"
		autocomplete="tel"
		value="#data.billingPhoneMobile#">
</div>
<div>
	<label for="billingMobileNotice">Mobile Notification:</label>
	<input id="billingMobileNoticeAgree" name="billingMobileNotice"
		type="radio" value="true"<cfif data.billingMobileNotice eq "true"> checked</cfif>>
	<label for="billingMobileNoticeAgree">Agree</label>
	<input id="billingMobileNoticeDecline" name="billingMobileNotice"
		type="radio" value="false"<cfif data.billingMobileNotice eq "false"> checked</cfif>>
	<label for="billingMobileNoticeDecline">Decline</label>
</div>

Lucee CFML excerpt:

local.isnull.billingPhoneMobile = local.data.billingPhoneMobile.len() <= 0;
local.isnull.billingMobileNotice = local.data.billingMobileNotice.len() <= 0;
storedproc procedure="dhContactUpdate" {
	procparam type="IN" sqltype="CF_SQL_INTEGER" value=local.data.billingContactId;
...
	procparam type="IN" sqltype="CF_SQL_VARCHAR"
		null=local.isnull.billingPhoneMobile value=local.data.billingPhoneMobile;
	procparam type="IN" sqltype="CF_SQL_BOOLEAN"
		null=local.isnull.billingMobileNotice value=local.data.billingMobileNotice;
...
	procresult resultset=1 name="local.billing.contact";
	procresult resultset=2 name="local.billing.sponsor_contact_role";
};

MariaDB stored procedure excerpt:

CREATE OR REPLACE
	DEFINER=root@localhost
	PROCEDURE dhContactUpdate(
		IN inContactId INT UNSIGNED,
...
		IN inPhoneMobile varchar(32),
		IN inMobileNotice BOOLEAN,
...
	)
	LANGUAGE SQL
	MODIFIES SQL DATA
	SQL SECURITY INVOKER
BEGIN
	DECLARE dhContactId INT UNSIGNED DEFAULT NULL;
	#
	IF inContactId IS NOT NULL THEN
		SET dhContactId = inContactId;
	ELSEIF inEmailLogin IS NOT NULL THEN
		SELECT ContactId INTO dhContactId
		FROM contact WHERE EmailLogin = inEmailLogin;
	END IF;
	#
	IF dhContactId IS NULL THEN
		SET dhContactId = dhContactId();
		INSERT IGNORE INTO contact (
			ContactId,
...
			PhoneMobile,
			MobileNotice,
...
		) VALUES (
			dhContactId,
...
			inPhoneMobile,
			inMobileNotice,
...
		);
	ELSE
		UPDATE contact SET
...
			PhoneMobile = inPhoneMobile,
			MobileNotice = inMobileNotice,
...
		WHERE ContactId = dhContactId;
	END IF;
	#
	CALL dhContactLatest(dhContactId);
END