3 Replies Latest reply on Jul 13, 2018 12:28 PM by rareddy

TEIID40095 TEIID31079 View materialized.testViewCache is not defined by a query expression.

huangyun Jul 12, 2018 5:54 AM

When I tried external materialized view .I encountered a problem :

2018-07-12 16:32:31 ERROR [main] (SpringApplication.java:833) - Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'oneEmberdedServer': Invocation of init method failed; nested exception is org.teiid.deployers.VirtualDatabaseException: TEIID40095 TEIID31079 View materialized.testViewCache is not defined by a query expression.

at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:138)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:423)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1702)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:583)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502)

at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312)

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:760)

at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:868)

at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549)

at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:140)

at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:752)

at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:388)

at org.springframework.boot.SpringApplication.run(SpringApplication.java:327)

at org.springframework.boot.SpringApplication.run(SpringApplication.java:1246)

at org.springframework.boot.SpringApplication.run(SpringApplication.java:1234)

at com.hiocs.daas.vdb.Application.main(Application.java:29)

Caused by: org.teiid.deployers.VirtualDatabaseException: TEIID40095 TEIID31079 View materialized.testViewCache is not defined by a query expression.

at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:830)

at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:748)

at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:713)

at com.hiocs.daas.vdb.dbsourcemodel.OneEmberdedServer.initDeploy(OneEmberdedServer.java:150)

at com.hiocs.daas.vdb.dbsourcemodel.OneEmberdedServer.startOnlyServer(OneEmberdedServer.java:95)

at com.hiocs.daas.vdb.dbsourcemodel.OneEmberdedServer.init(OneEmberdedServer.java:58)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:369)

at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:312)

at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:135)

... 18 more

This is my VDB xml file

<vdb name="matworkspace" version="1.0"> 
<model name="tableModel_mysql_1"> 
<source name="Model_23d0ba8f6d5f430891cb40653ea9139c" translator-name="translator-mysql" connection-jndi-name="Connector_bb9278d3190f4342b240a3241c38762e" /> 
<metadata type="DDL" > 
 <![CDATA[ 
 CREATE foreign TABLE vdb_folder(
folderId INTEGER,
folderName VARCHAR,
folderType VARCHAR,
creatorId VARCHAR,
creator VARCHAR,
datasourceType VARCHAR,
createTime TIMESTAMP,
updateTime TIMESTAMP,
folderDesc VARCHAR)OPTIONS(UPDATABLE 'TRUE'); 
 CREATE foreign TABLE vbd_data_source(
dataSourceId INTEGER,
dataSourceName VARCHAR,
dataSourceInfo VARCHAR,
creatorId VARCHAR,
dataSourceType VARCHAR,
connector VARCHAR,
creator VARCHAR,
createTime TIMESTAMP,
updateTime TIMESTAMP,
removed INTEGER,
dataSourceDesc VARCHAR,
folderId INTEGER,
origin VARCHAR)OPTIONS(UPDATABLE 'TRUE'); 
 ]]> 
 </metadata> 
 </model> 



<model name="viewModel" type="VIRTUAL"> 
 <metadata type="DDL"> 
  <![CDATA[ 
create view  testtab ( folderName  VARCHAR , dataSourceName  VARCHAR )OPTIONS (
   materialized 'true',
               UPDATABLE 'true',
               MATERIALIZED_TABLE 'materialized.testViewCache',
"teiid_rel:MATVIEW_TTL" 2000,
            "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
            "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
            "teiid_rel:MATVIEW_STATUS_TABLE" 'materialized.status')
 as select  vdb_folder.folderName as folderName ,vbd_data_source.dataSourceName as dataSourceName  from  vbd_data_source  vbd_data_source  left join  vdb_folder  vdb_folder  on  vbd_data_source.folderId = vdb_folder.folderId where 1=1 ; 
  ]]></metadata> 
 </model> 

  <model name="materialized" type="PHYSICAL">
 <source name="matview"  translator-name="translator-mysql" connection-jndi-name="Connector_14d0f340db46433b8698d00e56e83055"/>
  <metadata type="DDL">
  <![CDATA[


  create view  testViewCache ( folderName  VARCHAR , dataSourceName  VARCHAR ,LoadNumber long);
  
  CREATE foreign TABLE status (
            VDBName varchar(50) not null,
            VDBVersion varchar(50) not null,
            SchemaName varchar(50) not null,
            Name varchar(256) not null,
            TargetSchemaName varchar(50),
            TargetName varchar(256) not null,
            Valid boolean not null,
            LoadState varchar(25) not null,
            Cardinality long,
            Updated timestamp not null,
            LoadNumber long not null,
            NodeName varchar(25) not null,
            StaleCount long,
            PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
          ) OPTIONS (UPDATABLE true);
  
  
  ]]>
  </metadata>
 </model>
</vdb>

and I had create physical table on my database

1. Re: TEIID40095 TEIID31079 View materialized.testViewCache is not defined by a query expression.

rareddy Jul 12, 2018 9:06 AM (in response to huangyun)
You defined
create view testViewCache ( folderName VARCHAR , dataSourceName VARCHAR ,LoadNumber long);

this should be

create foreign table testViewCache ( folderName VARCHAR , dataSourceName VARCHAR ,LoadNumber long);

Ramesh..
Actions
2. Re: TEIID40095 TEIID31079 View materialized.testViewCache is not defined by a query expression.

huangyun Jul 12, 2018 11:48 PM (in response to rareddy)

Thank you ! You've been a great help .But another problem has arisen:

2018-07-13 11:31:07 INFO [Asynch Worker8] (JBossLogger.java:60) - START USER COMMAND: startTime=2018-07-13 11:31:07.206 requestID=M6yFSTIz7w1/.0 txID=null sessionID=M6yFSTIz7w1/ applicationName=internal principal=embedded-async vdbName=matworkspace vdbVersion=1.0.0 sql=execute SYSADMIN.matViewStatus('viewModel', 'testtab')
2018-07-13 11:31:07 INFO [Worker1_QueryProcessorQueue234] (JBossLogger.java:60) - END USER COMMAND: endTime=2018-07-13 11:31:07.268 requestID=M6yFSTIz7w1/.0 txID=null sessionID=M6yFSTIz7w1/ principal=embedded-async vdbName=matworkspace vdbVersion=1.0.0 finalRowCount=0
2018-07-13 11:31:07 INFO [Asynch Worker8] (JBossLogger.java:60) - START USER COMMAND: startTime=2018-07-13 11:31:07.269 requestID=57ZM0pL+lQZ/.0 txID=null sessionID=57ZM0pL+lQZ/ applicationName=internal principal=embedded-async vdbName=matworkspace vdbVersion=1.0.0 sql=execute SYSADMIN.loadMatView('viewModel','testtab', false, true)
2018-07-13 11:31:07 INFO [Worker1_QueryProcessorQueue235] (JBossLogger.java:60) - ERROR USER COMMAND: endTime=2018-07-13 11:31:07.28 requestID=57ZM0pL+lQZ/.0 txID=null sessionID=57ZM0pL+lQZ/ principal=embedded-async vdbName=matworkspace vdbVersion=1.0.0 finalRowCount=null
2018-07-13 11:31:07 WARN [Worker1_QueryProcessorQueue235] (JBossLogger.java:63) - TEIID30020 Processing exception for request 57ZM0pL+lQZ/.0 'TEIID31113 1 extra positional parameter(s) passed to SYSADMIN.loadMatView.'. Originally QueryResolverException ExecResolver.java:274. Enable more detailed logging to see the entire stacktrace.
2018-07-13 11:31:07 WARN [Worker1_QueryProcessorQueue235] (JBossLogger.java:63) - org.teiid.api.exception.query.QueryResolverException: TEIID31113 1 extra positional parameter(s) passed to SYSADMIN.loadMatView.
java.util.concurrent.ExecutionException: org.teiid.api.exception.query.QueryResolverException: TEIID31113 1 extra positional parameter(s) passed to SYSADMIN.loadMatView.
at org.teiid.client.util.ResultsFuture.convertResult(ResultsFuture.java:100)
at org.teiid.client.util.ResultsFuture.get(ResultsFuture.java:95)
at org.teiid.runtime.MaterializationManager$3.onCompletion(MaterializationManager.java:269)
at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:131)
at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:36)
at org.teiid.client.util.ResultsFuture$1.exceptionOccurred(ResultsFuture.java:64)
at org.teiid.dqp.internal.process.DQPCore$12$1.onCompletion(DQPCore.java:1010)
at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:131)
at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:36)
at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:75)
at org.teiid.dqp.internal.process.RequestWorkItem.sendError(RequestWorkItem.java:1150)
at org.teiid.dqp.internal.process.RequestWorkItem.close(RequestWorkItem.java:596)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:375)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: org.teiid.api.exception.query.QueryResolverException: TEIID31113 1 extra positional parameter(s) passed to SYSADMIN.loadMatView.
at org.teiid.query.resolver.command.ExecResolver.findCommandMetadata(ExecResolver.java:274)
at org.teiid.query.resolver.command.ExecResolver.resolveProceduralCommand(ExecResolver.java:299)
at org.teiid.query.resolver.ProcedureContainerResolver.resolveCommand(ProcedureContainerResolver.java:140)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:282)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:128)
at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:282)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:418)
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:486)
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:660)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
... 8 more
2018-07-13 11:31:07 INFO [Worker1_QueryProcessorQueue235] (JBossLogger.java:60) - END USER COMMAND: endTime=2018-07-13 11:31:07.305 requestID=57ZM0pL+lQZ/.0 txID=null sessionID=57ZM0pL+lQZ/ principal=embedded-async vdbName=matworkspace vdbVersion=1.0.0 finalRowCount=-1
Actions
3. Re: TEIID40095 TEIID31079 View materialized.testViewCache is not defined by a query expression.

rareddy Jul 13, 2018 12:28 PM (in response to huangyun)

How are you executing this? I mean what did operation did you do when above error occurred?
Actions

Go to original post