QoQ: data exception: string data, right truncation ; size limit: 1

dev support
1

What other “odd things” should we be on lookout for? Running 5.4.1.4 and many of our QoQ are blowing up (all of our QoQ are multi table queries from different databases.) Latest error is:

data exception: string data, right truncation ; size limit: 1 table: QCOMPONENTS column: SCANNEDBY
any idea what that means? If I remove the SCANNEDBY column from the QoQ select I still get same error.

Full stacktrace:

lucee.runtime.exp.IllegalQoQException: QoQ HSQLDB: error executing sql statement on query.
    at lucee.runtime.db.HSQLDBHandler.__execute(HSQLDBHandler.java:357)
    at lucee.runtime.db.HSQLDBHandler._execute(HSQLDBHandler.java:306)
    at lucee.runtime.db.HSQLDBHandler.execute(HSQLDBHandler.java:294)
    at lucee.runtime.tag.Query.executeQoQ(Query.java:1111)
    at lucee.runtime.tag.Query._doEndTag(Query.java:681)
    at lucee.runtime.tag.Query.doEndTag(Query.java:566)
    at lucee.runtime.functions.query.QueryExecute.call(QueryExecute.java:86)
    at modules_app.fox.modules_app.orders.models.po.foxpurchaseorderdao_cfc$cf$3v.udfCall1(/modules_app/Fox/modules_app/Orders/models/po/FoxPurchaseOrderDAO.cfc:61)
    at modules_app.fox.modules_app.orders.models.po.foxpurchaseorderdao_cfc$cf$3v.udfCall(/modules_app/Fox/modules_app/Orders/models/po/FoxPurchaseOrderDAO.cfc)
    at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
    at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
    at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
    at lucee.runtime.type.scope.UndefinedImpl.call(UndefinedImpl.java:786)
    at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
    at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775)
    at modules_app.fox.modules_app.orders.models.po.foxpurchaseorderdao_cfc$cf$3v.udfCall1(/modules_app/Fox/modules_app/Orders/models/po/FoxPurchaseOrderDAO.cfc:48)
    at modules_app.fox.modules_app.orders.models.po.foxpurchaseorderdao_cfc$cf$3v.udfCall(/modules_app/Fox/modules_app/Orders/models/po/FoxPurchaseOrderDAO.cfc)
    at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
    at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
    at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
    at lucee.runtime.ComponentImpl._call(ComponentImpl.java:664)
    at lucee.runtime.ComponentImpl._call(ComponentImpl.java:586)
    at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1933)
    at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
    at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775)
    at modules_app.fox.modules_app.help.handlers.help_cfc$cf.udfCall(/modules_app/Fox/modules_app/Help/handlers/Help.cfc:53)
    at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
    at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
    at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
    at lucee.runtime.ComponentImpl._call(ComponentImpl.java:665)
    at lucee.runtime.ComponentImpl._call(ComponentImpl.java:586)
    at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1952)
    at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:900)
    at lucee.runtime.functions.dynamicEvaluation.Invoke.call(Invoke.java:49)
    at system.web.controller_cfc$cf$b.udfCall4(/coldbox/system/web/Controller.cfc:1257)
    at system.web.controller_cfc$cf$b.udfCall(/coldbox/system/web/Controller.cfc)
    at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
    at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
    at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
    at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804)
    at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866)
    at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794)
    at system.web.controller_cfc$cf$b.udfCall3(/coldbox/system/web/Controller.cfc:1006)
    at system.web.controller_cfc$cf$b.udfCall(/coldbox/system/web/Controller.cfc)
    at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
    at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
    at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
    at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804)
    at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866)
    at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794)
    at system.web.controller_cfc$cf$b.udfCall3(/coldbox/system/web/Controller.cfc:713)
    at system.web.controller_cfc$cf$b.udfCall(/coldbox/system/web/Controller.cfc)
    at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
    at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
    at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
    at lucee.runtime.ComponentImpl._call(ComponentImpl.java:665)
    at lucee.runtime.ComponentImpl._call(ComponentImpl.java:586)
    at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1952)
    at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866)
    at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794)
    at system.bootstrap_cfc$cf.udfCall1(/coldbox/system/Bootstrap.cfc:290)
    at system.bootstrap_cfc$cf.udfCall(/coldbox/system/Bootstrap.cfc)
    at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
    at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
    at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
    at lucee.runtime.type.scope.UndefinedImpl.call(UndefinedImpl.java:786)
    at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
    at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775)
    at system.bootstrap_cfc$cf.udfCall1(/coldbox/system/Bootstrap.cfc:506)
    at system.bootstrap_cfc$cf.udfCall(/coldbox/system/Bootstrap.cfc)
    at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
    at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
    at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
    at lucee.runtime.ComponentImpl._call(ComponentImpl.java:664)
    at lucee.runtime.ComponentImpl._call(ComponentImpl.java:586)
    at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1933)
    at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
    at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775)
    at application_cfc$cf.udfCall(/Application.cfc:34)
    at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
    at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
    at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
    at lucee.runtime.ComponentImpl._call(ComponentImpl.java:664)
    at lucee.runtime.ComponentImpl._call(ComponentImpl.java:586)
    at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1933)
    at lucee.runtime.listener.ModernAppListener.call(ModernAppListener.java:444)
    at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:135)
    at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44)
    at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2493)
    at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2478)
    at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2449)
    at lucee.runtime.engine.Request.exe(Request.java:45)
    at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1216)
    at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1162)
    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:590)
    at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)
    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
    at org.cfmlprojects.regexpathinfofilter.RegexPathInfoFilter.doFilter(RegexPathInfoFilter.java:47)
    at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67)
    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
    at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
    at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
    at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
    at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
    at runwar.undertow.SSLClientCertHeaderHandler.handleRequest(SSLClientCertHeaderHandler.java:144)
    at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:257)
    at io.undertow.servlet.handlers.ServletInitialHandler.dispatchToPath(ServletInitialHandler.java:182)
    at io.undertow.servlet.spec.RequestDispatcherImpl.forwardImpl(RequestDispatcherImpl.java:188)
    at io.undertow.servlet.spec.RequestDispatcherImpl.forwardImplSetup(RequestDispatcherImpl.java:136)
    at io.undertow.servlet.spec.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:99)
    at org.cfmlprojects.regexpathinfofilter.RegexPathInfoFilter.doFilter(RegexPathInfoFilter.java:45)
    at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67)
    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
    at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
    at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
    at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
    at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
    at runwar.undertow.SSLClientCertHeaderHandler.handleRequest(SSLClientCertHeaderHandler.java:144)
    at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:257)
    at io.undertow.servlet.handlers.ServletInitialHandler.dispatchToPath(ServletInitialHandler.java:182)
    at io.undertow.servlet.spec.RequestDispatcherImpl.forwardImpl(RequestDispatcherImpl.java:188)
    at io.undertow.servlet.spec.RequestDispatcherImpl.forwardImplSetup(RequestDispatcherImpl.java:136)
    at io.undertow.servlet.spec.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:99)
    at org.tuckey.web.filters.urlrewrite.NormalRewrittenUrl.doRewrite(NormalRewrittenUrl.java:215)
    at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:171)
    at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
    at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92)
    at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:405)
    at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67)
    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
    at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
    at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
    at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
    at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
    at runwar.undertow.SSLClientCertHeaderHandler.handleRequest(SSLClientCertHeaderHandler.java:144)
    at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)
    at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:117)
    at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
    at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
    at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
    at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
    at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.servlet.handlers.SendErrorPageHandler.handleRequest(SendErrorPageHandler.java:52)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:275)
    at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:79)
    at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:134)
    at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:131)
    at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
    at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
    at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
    at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:255)
    at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:79)
    at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:100)
    at io.undertow.server.Connectors.executeRootHandler(Connectors.java:393)
    at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:852)
    at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
    at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:2019)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1558)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1449)
    at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1282)
    at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.sql.SQLDataException: data exception: string data, right truncation ; size limit: 1 table: QCOMPONENTS column: SCANNEDBY
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)
    at lucee.runtime.db.HSQLDBHandler.addTable(HSQLDBHandler.java:152)
    at lucee.runtime.db.HSQLDBHandler.__execute(HSQLDBHandler.java:341)
    ... 172 more
Caused by: org.hsqldb.HsqlException: data exception: string data, right truncation ; size limit: 1 table: QCOMPONENTS column: SCANNEDBY
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.Table.enforceTypeLimits(Unknown Source)
    at org.hsqldb.Table.generateAndCheckData(Unknown Source)
    at org.hsqldb.Table.insertSingleRow(Unknown Source)
    at org.hsqldb.StatementDML.insertSingleRow(Unknown Source)
    at org.hsqldb.StatementInsert.getResult(Unknown Source)
    at org.hsqldb.StatementDMQL.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 176 more
Caused by: org.hsqldb.HsqlException: data exception: string data, right truncation
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.types.CharacterType.convertToTypeLimits(Unknown Source)
    ... 184 more
2

Yeah, I’m working thru the issues. removing the col won’t work until my data loading is implemented as QoQ currently always loads all the data.

can you share the DDL for the source tables causing the problem and their corresponding getMetaData(q_whatever) outputs?

3

The issue seems to be related to the scannedby field being CHAR(2)? If I remove that field from the first query, the issue goes away. DDL of that table:

CREATE TABLE `serials_pos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ponumber` int DEFAULT NULL,
  `ref` int DEFAULT NULL,
  `partSKU` varchar(32) DEFAULT NULL,
  `serial_number` varchar(150) DEFAULT NULL,
  `scannedon` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `scannedby` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ponumber` (`ponumber`),
  KEY `serial_number` (`serial_number`)
) ENGINE=InnoDB AUTO_INCREMENT=255692 DEFAULT CHARSET=utf8mb3;
) AS scannedby
FROM	serials_pos
4

can you knock up an example

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

5

done!

6

cool, i found the problem

1 Like
7

fixed in 5.4.1.5 & 6.0.0.499, currently building

keep on testing and i’ll keep on fixing :slight_smile:

4 Likes