CMDB Query Builder fails to prevent the first run of the query being done from a non-Global application scope, causing a table with the wrong name to be created - Known Error

CMDB Query Builder fails to prevent the first run of the query being done from a non-Global application scope, causing a table with the wrong name to be created Description CMDB Query Builder fails to prevent the first run of the query being done from a non-Global application scope, causing a table with the wrong name to be created. The table name is determined when the query is Saved, but it doesn't get created until it is first Run. The platform will prefix new tables with the current application scope at the point they are created. The table name that Query Builder thinks it created does not exist.

This could happen the first time a saved query is run after it has been added to the instance after testing in a sub-prod instance. This could happen if a user has their session set to a non-Global scope and runs it. This could also happen if a scheduled job to run the query on a regular basis, is running as a non-global scope, causes the query to run.

Subsequent runs as Global scope will also fail, because the wrong table name is already created.

Steps to Reproduce

In Query Builder, Create a new Saved Query, and Save it. Note that in the qb_saved_query record, the result table will be set as something like u_cmdb_qb_result_7607c99e83504a2b1ebd529e20c1ad1216eb87a8 Switch your scope to something non-Global, e.g. CMDB CI Class Models Run the query Expected behaviour: Creating the result table should be prevented, and an Error given about the current scope needing to be global before the query can be Run.

Actual behaviour: The query fails to run. Instead of creating a table named e.g. "u_cmdb_qb_result_7607c99e83504a2b1ebd529e20c1ad1216eb87a8", a table named with teh scope prefix is created instead e.g. "sn_cmdb_ci_class_u_cmdb_qb_result_7607c99e83504a2b1ebd529e20c1ad1216eb87a8"

Errors are seen in the app node logs for the progress worker scheduler worker thread that ran the query:

2022-12-01 12:31:51 (801) worker.0 worker.0 txid=a0f0e84d1b23 QueryBuilderProcessor : Begin to process query 24f0ec8d1b239158938fdc66b04bcb7d. ... 2022-12-01 12:31:51 (853) worker.0 worker.0 txid=a0f0e84d1b23 SEVERE *** ERROR *** Exception during batch statement commit to glide: _1: :jdbc:mysql://x.x.service-now.com:3406/, falling back to single commits: (conn=3680935) Table ' _1.7607c99e83504a2b1ebd529e20c1ad' doesn't exist java.sql.BatchUpdateException: (conn=3680935) Table ' _1.7607c99e83504a2b1ebd529e20c1ad' doesn't exist at org.mariadb.jdbc.MariaDbStatement.executeBatchExceptionEpilogue(MariaDbStatement.java:289) at org.mariadb.jdbc.MariaDbStatement.executeBatch(MariaDbStatement.java:1356) at jdk.internal.reflect.GeneratedMethodAccessor195.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at com.glide.db.StatementWrapper.invoke(StatementWrapper.java:38) at com.glide.db.AConnectionWrapperProxy.invoke(AConnectionWrapperProxy.java:33) at com.glide.db.StatementWrapper.invoke(StatementWrapper.java:18) at com.sun.proxy.$Proxy25.executeBatch(Unknown Source) at com.glide.db.BatchStatement.executeBatch(BatchStatement.java:86) at com.glide.db.StatementBatcher.commitBatch(StatementBatcher.java:624) at com.glide.db.StatementBatcher.commit(StatementBatcher.java:447) at com.glide.db.StatementBatcher.flush(StatementBatcher.java:371) at com.snc.cmdb.query.QBTableUtil.saveQueryResultByBatch(QBTableUtil.java:341) at com.snc.cmdb.query.processor.QueryBuilderProcessor.saveResultsToTable(QueryBuilderProcessor.java:602) at com.snc.cmdb.query.processor.QueryBuilderProcessor.saveResults(QueryBuilderProcessor.java:804) at com.snc.cmdb.query.processor.QueryBuilderProcessor.processQuery(QueryBuilderProcessor.java:301) at com.snc.cmdb.query.processor.QueryBuilderProcessor.processQuery(QueryBuilderProcessor.java:350) at com.snc.cmdb.query.processor.QueryBuilderProcessor.processOneBatchQuery(QueryBuilderProcessor.java:290) at com.snc.cmdb.query.processor.QueryBuilderProcessor.processQueryGraphInternal(QueryBuilderProcessor.java:228) at com.snc.cmdb.query.processor.QueryBuilderProcessor.processQueryGraph(QueryBuilderProcessor.java:166) at com.snc.cmdb.query.background.CMDBQBExecuteWorker.startWork(CMDBQBExecuteWorker.java:40) at com.glide.worker.AbstractProgressWorker.startAndWait(AbstractProgressWorker.java:126) at com.glide.worker.ProgressWorker.startAndWait(ProgressWorker.java:52) at com.glide.worker.BackgroundProgressJob.execute(BackgroundProgressJob.java:59) at com.glide.schedule.JobExecutor.lambda$executeJob$0(JobExecutor.java:129) at com.snc.db.data_replicate.replicator.DataReplicationAdvisors.runInOriginatorContext(DataReplicationAdvisors.java:73) at com.glide.schedule.JobExecutor.lambda$inDataReplicationContext$2(JobExecutor.java:159) at com.glide.schedule.JobExecutor.executeJob(JobExecutor.java:132) at com.glide.schedule.JobExecutor.execute(JobExecutor.java:116) at com.glide.schedule_v2.SchedulerWorkerThread.executeJob(SchedulerWorkerThread.java:338) at com.glide.schedule_v2.SchedulerWorkerThread.lambda$process$0(SchedulerWorkerThread.java:220) at com.glide.worker.TransactionalWorkerThread.executeInTransaction(TransactionalWorkerThread.java:35) at com.glide.schedule_v2.SchedulerWorkerThread.process(SchedulerWorkerThread.java:220) at com.glide.schedule_v2.SchedulerWorkerThread.run(SchedulerWorkerThread.java:101) Caused by: java.sql.SQLSyntaxErrorException: (conn=3680935) Table ' _1.7607c99e83504a2b1ebd529e20c1ad' doesn't exist at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:236) at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165) at org.mariadb.jdbc.MariaDbStatement.executeBatchExceptionEpilogue(MariaDbStatement.java:286) ... 34 more Caused by: java.sql.SQLException: Table ' _1.7607c99e83504a2b1ebd529e20c1ad' doesn't exist Query is: INSERT INTO 7607c99e83504a2b1ebd529e20c1ad () VALUES() /* 107, gs:glide.scheduler.worker.0, tx:a0f0e84d1b239158938fdc66b04bcb96 */ java thread: MariaDb-bulk-4 at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:126) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol$2.handleResultException(AbstractQueryProtocol.java:756) at org.mariadb.jdbc.internal.protocol.AsyncMultiRead.call(AsyncMultiRead.java:142) at org.mariadb.jdbc.internal.protocol.AsyncMultiRead.call(AsyncMultiRead.java:66) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:829) 2022-12-01 12:31:51 (853) worker.0 worker.0 txid=a0f0e84d1b23 SEVERE *** ERROR *** Exception during statement to _1: INSERT INTO 7607c99e83504a2b1ebd529e20c1ad () VALUES() 2022-12-01 12:31:51 (854) worker.0 worker.0 txid=a0f0e84d1b23 SEVERE *** ERROR *** FAILED TRYING TO EXECUTE ON CONNECTION glide.9 (connpid=3680935): INSERT INTO 7607c99e83504a2b1ebd529e20c1ad () VALUES() /* 107, gs:glide.scheduler.worker.0, tx:a0f0e84d1b239158938fdc66b04bcb96 */ Syntax Error or Access Rule Violation detected by database ((conn=3680935) Table ' _1.7607c99e83504a2b1ebd529e20c1ad' doesn't exist)

Workaround This problem has no workaround, is currently under review and targeted to be fixed in a future release. Subscribe to this Known Error article to receive notifications when more information will be available.

To repair this, you will need to delete the saved query, and delete the bad table, and then with your session in Global scope recreate and run the query.