[JBoss JIRA] (TEIID-4960) Problems when using External Materialized Views
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4960?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4960:
-------------------------------------
A materialized view depending upon another materialized view may be not such good idea, as you never know how stale the information is. It is evident from the error message that this not currently supported. I believe the previous one works because of maybe timing issues, rather than functionality, as the code paths for both should be same.
> Problems when using External Materialized Views
> -----------------------------------------------
>
> Key: TEIID-4960
> URL: https://issues.jboss.org/browse/TEIID-4960
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3
> Environment: * Teiid Server 9.3.0
> * Wildfly 10
> * Mysql 5.7.18
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Attachments: server.log
>
>
> The following problem occurs when, apparently, we have two views where one depend on another, and also we use External Materialization.
> The dependent view is never updated due to a "Transaction already associated with request." exception.
> Logs attached.
> The error:
> {panel:title=The exception in logs}
> 2017-06-14 13:51:51,753 WARN [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue365) Hlbq3jmDWXNl org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate mvstatus('NumberingPlan', 'numbering_plan'): TEIID30384 Error while evaluating function mvstatus
> 2017-06-14 13:52:51,803 INFO [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue368) XAtcLA8RBrLm Materialization of view NumberingPlanRaw.numbering_plan_raw started.
> 2017-06-14 13:52:51,867 ERROR [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue372) XAtcLA8RBrLm TEIID30019 Unexpected exception for request XAtcLA8RBrLm.-2514910280603581440: java.lang.AssertionError: Transaction already associated with request.
> {panel}
> The VDB:
> {code:xml}
> <model name="MnomMaterialized" type="PHYSICAL">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="query-timeout" value="600000"/>
> <source name="MnomMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/mnomDs"/>
> </model>
>
> <model name="NumberingPlanCsvData">
> <source name="numberingPlanCsv-connector" translator-name="file" connection-jndi-name="java:/numberingPlanCsvDs"/>
> </model>
> <model name="NumberingPlan" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE VIEW numbering_plan (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'TRUE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY cns) as id,
> cns
> FROM (EXEC NumberingPlanCsvData.getTextFiles('NumberingPlan.csv')) AS f,
> TEXTTABLE(f.file COLUMNS cns string DELIMITER ';' SKIP 1) AS A;
>
> ]]>
> </metadata>
> </model>
>
> <model name="NumberingPlanRaw" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[
> CREATE VIEW numbering_plan_raw (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY network) as id,
> global_title
> FROM numbering_plan np;
>
> ]]>
> </metadata>
> </model>
> </model>
> {code}
> The Materialized table:
> {code:sql}
> CREATE 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 BIGINT,
> Updated TIMESTAMP NOT NULL,
> LoadNumber BIGINT NOT NULL,
> NodeName varchar(25) not null,
> StaleCount BIGINT,
> PRIMARY KEY (VDBName , VDBVersion , SchemaName , Name)
> );
> CREATE TABLE numbering_plan_cache (
> id integer,
> global_title varchar(20),
> LoadNumber BIGINT,
> PRIMARY KEY(id)
> );
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years
[JBoss JIRA] (TEIID-4960) Problems when using External Materialized Views
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4960?page=com.atlassian.jira.plugin... ]
Ramesh Reddy reassigned TEIID-4960:
-----------------------------------
Assignee: Ramesh Reddy (was: Steven Hawkins)
> Problems when using External Materialized Views
> -----------------------------------------------
>
> Key: TEIID-4960
> URL: https://issues.jboss.org/browse/TEIID-4960
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3
> Environment: * Teiid Server 9.3.0
> * Wildfly 10
> * Mysql 5.7.18
> Reporter: Pedro Inácio
> Assignee: Ramesh Reddy
> Attachments: server.log
>
>
> The following problem occurs when, apparently, we have two views where one depend on another, and also we use External Materialization.
> The dependent view is never updated due to a "Transaction already associated with request." exception.
> Logs attached.
> The error:
> {panel:title=The exception in logs}
> 2017-06-14 13:51:51,753 WARN [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue365) Hlbq3jmDWXNl org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate mvstatus('NumberingPlan', 'numbering_plan'): TEIID30384 Error while evaluating function mvstatus
> 2017-06-14 13:52:51,803 INFO [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue368) XAtcLA8RBrLm Materialization of view NumberingPlanRaw.numbering_plan_raw started.
> 2017-06-14 13:52:51,867 ERROR [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue372) XAtcLA8RBrLm TEIID30019 Unexpected exception for request XAtcLA8RBrLm.-2514910280603581440: java.lang.AssertionError: Transaction already associated with request.
> {panel}
> The VDB:
> {code:xml}
> <model name="MnomMaterialized" type="PHYSICAL">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="query-timeout" value="600000"/>
> <source name="MnomMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/mnomDs"/>
> </model>
>
> <model name="NumberingPlanCsvData">
> <source name="numberingPlanCsv-connector" translator-name="file" connection-jndi-name="java:/numberingPlanCsvDs"/>
> </model>
> <model name="NumberingPlan" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE VIEW numbering_plan (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'TRUE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY cns) as id,
> cns
> FROM (EXEC NumberingPlanCsvData.getTextFiles('NumberingPlan.csv')) AS f,
> TEXTTABLE(f.file COLUMNS cns string DELIMITER ';' SKIP 1) AS A;
>
> ]]>
> </metadata>
> </model>
>
> <model name="NumberingPlanRaw" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[
> CREATE VIEW numbering_plan_raw (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY network) as id,
> global_title
> FROM numbering_plan np;
>
> ]]>
> </metadata>
> </model>
> </model>
> {code}
> The Materialized table:
> {code:sql}
> CREATE 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 BIGINT,
> Updated TIMESTAMP NOT NULL,
> LoadNumber BIGINT NOT NULL,
> NodeName varchar(25) not null,
> StaleCount BIGINT,
> PRIMARY KEY (VDBName , VDBVersion , SchemaName , Name)
> );
> CREATE TABLE numbering_plan_cache (
> id integer,
> global_title varchar(20),
> LoadNumber BIGINT,
> PRIMARY KEY(id)
> );
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years
[JBoss JIRA] (TEIID-4960) Problems when using External Materialized Views
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4960?page=com.atlassian.jira.plugin... ]
Ramesh Reddy reassigned TEIID-4960:
-----------------------------------
Assignee: Steven Hawkins (was: Ramesh Reddy)
> Problems when using External Materialized Views
> -----------------------------------------------
>
> Key: TEIID-4960
> URL: https://issues.jboss.org/browse/TEIID-4960
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3
> Environment: * Teiid Server 9.3.0
> * Wildfly 10
> * Mysql 5.7.18
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Attachments: server.log
>
>
> The following problem occurs when, apparently, we have two views where one depend on another, and also we use External Materialization.
> The dependent view is never updated due to a "Transaction already associated with request." exception.
> Logs attached.
> The error:
> {panel:title=The exception in logs}
> 2017-06-14 13:51:51,753 WARN [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue365) Hlbq3jmDWXNl org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate mvstatus('NumberingPlan', 'numbering_plan'): TEIID30384 Error while evaluating function mvstatus
> 2017-06-14 13:52:51,803 INFO [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue368) XAtcLA8RBrLm Materialization of view NumberingPlanRaw.numbering_plan_raw started.
> 2017-06-14 13:52:51,867 ERROR [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue372) XAtcLA8RBrLm TEIID30019 Unexpected exception for request XAtcLA8RBrLm.-2514910280603581440: java.lang.AssertionError: Transaction already associated with request.
> {panel}
> The VDB:
> {code:xml}
> <model name="MnomMaterialized" type="PHYSICAL">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="query-timeout" value="600000"/>
> <source name="MnomMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/mnomDs"/>
> </model>
>
> <model name="NumberingPlanCsvData">
> <source name="numberingPlanCsv-connector" translator-name="file" connection-jndi-name="java:/numberingPlanCsvDs"/>
> </model>
> <model name="NumberingPlan" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE VIEW numbering_plan (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'TRUE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY cns) as id,
> cns
> FROM (EXEC NumberingPlanCsvData.getTextFiles('NumberingPlan.csv')) AS f,
> TEXTTABLE(f.file COLUMNS cns string DELIMITER ';' SKIP 1) AS A;
>
> ]]>
> </metadata>
> </model>
>
> <model name="NumberingPlanRaw" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[
> CREATE VIEW numbering_plan_raw (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY network) as id,
> global_title
> FROM numbering_plan np;
>
> ]]>
> </metadata>
> </model>
> </model>
> {code}
> The Materialized table:
> {code:sql}
> CREATE 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 BIGINT,
> Updated TIMESTAMP NOT NULL,
> LoadNumber BIGINT NOT NULL,
> NodeName varchar(25) not null,
> StaleCount BIGINT,
> PRIMARY KEY (VDBName , VDBVersion , SchemaName , Name)
> );
> CREATE TABLE numbering_plan_cache (
> id integer,
> global_title varchar(20),
> LoadNumber BIGINT,
> PRIMARY KEY(id)
> );
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years
[JBoss JIRA] (TEIID-4961) External Materialized View With State Loaded but 0 Cardinality
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4961?page=com.atlassian.jira.plugin... ]
Pedro Inácio updated TEIID-4961:
--------------------------------
Attachment: server.log
> External Materialized View With State Loaded but 0 Cardinality
> --------------------------------------------------------------
>
> Key: TEIID-4961
> URL: https://issues.jboss.org/browse/TEIID-4961
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3
> Environment: * Teiid 9.3.0
> * MySQL 5.7.18
> * Wildfly 10
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Attachments: server.log
>
>
> *Scenario*: Having several views that depend on others (ex: V1-> V2->V3, where V3 depends on V2 and V2 depends on V1), all with external materialization.
> Although all views LoadState is *LOADED *even after failures due to the dependent ones being loaded, in the end some views have zero (0) of cardinality, when there are actually records. Running the queries by hand return records.
> Log attached.
> Example:
> {panel}
> 2017-06-14 17:14:39,521 INFO [org.teiid.MATVIEWS] (Worker63_QueryProcessorQueue19799) WDpoyHmXlkvS Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 0 Load Number = 1
> {panel}
> After forcing the view update:
> {panel}
> 2017-06-14 17:23:38,042 INFO [org.teiid.MATVIEWS] (Worker65_QueryProcessorQueue27597) +IbRaNRtrZ1K Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 1816 Load Number = 2
> {panel}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years
[JBoss JIRA] (TEIID-4961) External Materialized View With State Loaded but 0 Cardinality
by Pedro Inácio (JIRA)
Pedro Inácio created TEIID-4961:
-----------------------------------
Summary: External Materialized View With State Loaded but 0 Cardinality
Key: TEIID-4961
URL: https://issues.jboss.org/browse/TEIID-4961
Project: Teiid
Issue Type: Bug
Affects Versions: 9.3
Environment: * Teiid 9.3.0
* MySQL 5.7.18
* Wildfly 10
Reporter: Pedro Inácio
Assignee: Steven Hawkins
*Sceanrio*: Having several views that depend on others (ex: V1-> V2->V3, where V3 depends on V2 and V2 depends on V1), all with external materialization.
Although all views LoadState is *LOADED *even after failures due to the dependent ones being loaded, in the end some views have zero (0) of cardinality, when there are actually records. Running the queries by hand return records.
Log attached.
Example:
{panel}
2017-06-14 17:14:39,521 INFO [org.teiid.MATVIEWS] (Worker63_QueryProcessorQueue19799) WDpoyHmXlkvS Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 0 Load Number = 1
{panel}
After forcing the view update:
{panel}
2017-06-14 17:23:38,042 INFO [org.teiid.MATVIEWS] (Worker65_QueryProcessorQueue27597) +IbRaNRtrZ1K Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 1816 Load Number = 2
{panel}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years
[JBoss JIRA] (TEIID-4961) External Materialized View With State Loaded but 0 Cardinality
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4961?page=com.atlassian.jira.plugin... ]
Pedro Inácio updated TEIID-4961:
--------------------------------
Description:
*Scenario*: Having several views that depend on others (ex: V1-> V2->V3, where V3 depends on V2 and V2 depends on V1), all with external materialization.
Although all views LoadState is *LOADED *even after failures due to the dependent ones being loaded, in the end some views have zero (0) of cardinality, when there are actually records. Running the queries by hand return records.
Log attached.
Example:
{panel}
2017-06-14 17:14:39,521 INFO [org.teiid.MATVIEWS] (Worker63_QueryProcessorQueue19799) WDpoyHmXlkvS Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 0 Load Number = 1
{panel}
After forcing the view update:
{panel}
2017-06-14 17:23:38,042 INFO [org.teiid.MATVIEWS] (Worker65_QueryProcessorQueue27597) +IbRaNRtrZ1K Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 1816 Load Number = 2
{panel}
was:
*Sceanrio*: Having several views that depend on others (ex: V1-> V2->V3, where V3 depends on V2 and V2 depends on V1), all with external materialization.
Although all views LoadState is *LOADED *even after failures due to the dependent ones being loaded, in the end some views have zero (0) of cardinality, when there are actually records. Running the queries by hand return records.
Log attached.
Example:
{panel}
2017-06-14 17:14:39,521 INFO [org.teiid.MATVIEWS] (Worker63_QueryProcessorQueue19799) WDpoyHmXlkvS Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 0 Load Number = 1
{panel}
After forcing the view update:
{panel}
2017-06-14 17:23:38,042 INFO [org.teiid.MATVIEWS] (Worker65_QueryProcessorQueue27597) +IbRaNRtrZ1K Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 1816 Load Number = 2
{panel}
> External Materialized View With State Loaded but 0 Cardinality
> --------------------------------------------------------------
>
> Key: TEIID-4961
> URL: https://issues.jboss.org/browse/TEIID-4961
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3
> Environment: * Teiid 9.3.0
> * MySQL 5.7.18
> * Wildfly 10
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
>
> *Scenario*: Having several views that depend on others (ex: V1-> V2->V3, where V3 depends on V2 and V2 depends on V1), all with external materialization.
> Although all views LoadState is *LOADED *even after failures due to the dependent ones being loaded, in the end some views have zero (0) of cardinality, when there are actually records. Running the queries by hand return records.
> Log attached.
> Example:
> {panel}
> 2017-06-14 17:14:39,521 INFO [org.teiid.MATVIEWS] (Worker63_QueryProcessorQueue19799) WDpoyHmXlkvS Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 0 Load Number = 1
> {panel}
> After forcing the view update:
> {panel}
> 2017-06-14 17:23:38,042 INFO [org.teiid.MATVIEWS] (Worker65_QueryProcessorQueue27597) +IbRaNRtrZ1K Materialization of view VodafoneNlBaseSource.vodafone_nl_base_source completed. Rows updated = 1816 Load Number = 2
> {panel}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years
[JBoss JIRA] (TEIID-4960) Problems when using External Materialized Views
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4960?page=com.atlassian.jira.plugin... ]
Pedro Inácio edited comment on TEIID-4960 at 6/14/17 12:16 PM:
---------------------------------------------------------------
When using the now deprecated strategy, all goes well.
Options for numbering_plan:
{code:xml}
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute MnomMaterialized.native(''truncate table mnom.numbering_plan_cache_staging;'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute MnomMaterialized.native(''RENAME TABLE mnom.numbering_plan_cache TO mnom.numbering_plan_cache_temp, mnom.numbering_plan_cache_staging TO mnom.numbering_plan_cache, mnom.numbering_plan_cache_temp TO mnom.numbering_plan_cache_staging'');',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM MnomMaterialized.status WHERE Name=''numbering_plan'' AND schemaname = ''NumberingPlan''',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'IMPORTED',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'MnomMaterialized.numbering_plan_cache_staging',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION'
)
{code}
Options for numbering_plan_raw:
{code:xml}
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute MnomMaterialized.native(''truncate table mnom.numbering_plan_raw_cache_staging;'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute MnomMaterialized.native(''RENAME TABLE mnom.numbering_plan_raw_cache TO mnom.numbering_plan_raw_cache_temp, mnom.numbering_plan_raw_cache_staging TO mnom.numbering_plan_raw_cache, mnom.numbering_plan_raw_cache_temp TO mnom.numbering_plan_raw_cache_staging'');',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM MnomMaterialized.status WHERE Name=''numbering_plan_raw'' AND schemaname = ''NumberingPlanRaw''',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'IMPORTED',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'MnomMaterialized.numbering_plan_raw_cache_staging',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION'
)
{code}
was (Author: pringi):
When using the now deprecated strategy, all goes well.
Options for numbering_plan:
{code:xml}
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute MnomMaterialized.native(''truncate table mnom.numbering_plan_cache_staging;'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute MnomMaterialized.native(''RENAME TABLE mnom.numbering_plan_cache TO mnom.numbering_plan_cache_temp, mnom.numbering_plan_cache_staging TO mnom.numbering_plan_cache, mnom.numbering_plan_cache_temp TO mnom.numbering_plan_cache_staging'');',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM MnomMaterialized.status WHERE Name=''numbering_plan'' AND schemaname = ''NumberingPlan''',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'IMPORTED',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'MnomMaterialized.numbering_plan_cache_staging',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION'
)
{code}
Options for numbering_plan_raw:
{code_xml}
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute MnomMaterialized.native(''truncate table mnom.numbering_plan_raw_cache_staging;'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute MnomMaterialized.native(''RENAME TABLE mnom.numbering_plan_raw_cache TO mnom.numbering_plan_raw_cache_temp, mnom.numbering_plan_raw_cache_staging TO mnom.numbering_plan_raw_cache, mnom.numbering_plan_raw_cache_temp TO mnom.numbering_plan_raw_cache_staging'');',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM MnomMaterialized.status WHERE Name=''numbering_plan_raw'' AND schemaname = ''NumberingPlanRaw''',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'IMPORTED',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'MnomMaterialized.numbering_plan_raw_cache_staging',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION'
)
{code}
> Problems when using External Materialized Views
> -----------------------------------------------
>
> Key: TEIID-4960
> URL: https://issues.jboss.org/browse/TEIID-4960
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3
> Environment: * Teiid Server 9.3.0
> * Wildfly 10
> * Mysql 5.7.18
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Attachments: server.log
>
>
> The following problem occurs when, apparently, we have two views where one depend on another, and also we use External Materialization.
> The dependent view is never updated due to a "Transaction already associated with request." exception.
> Logs attached.
> The error:
> {panel:title=The exception in logs}
> 2017-06-14 13:51:51,753 WARN [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue365) Hlbq3jmDWXNl org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate mvstatus('NumberingPlan', 'numbering_plan'): TEIID30384 Error while evaluating function mvstatus
> 2017-06-14 13:52:51,803 INFO [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue368) XAtcLA8RBrLm Materialization of view NumberingPlanRaw.numbering_plan_raw started.
> 2017-06-14 13:52:51,867 ERROR [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue372) XAtcLA8RBrLm TEIID30019 Unexpected exception for request XAtcLA8RBrLm.-2514910280603581440: java.lang.AssertionError: Transaction already associated with request.
> {panel}
> The VDB:
> {code:xml}
> <model name="MnomMaterialized" type="PHYSICAL">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="query-timeout" value="600000"/>
> <source name="MnomMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/mnomDs"/>
> </model>
>
> <model name="NumberingPlanCsvData">
> <source name="numberingPlanCsv-connector" translator-name="file" connection-jndi-name="java:/numberingPlanCsvDs"/>
> </model>
> <model name="NumberingPlan" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE VIEW numbering_plan (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'TRUE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY cns) as id,
> cns
> FROM (EXEC NumberingPlanCsvData.getTextFiles('NumberingPlan.csv')) AS f,
> TEXTTABLE(f.file COLUMNS cns string DELIMITER ';' SKIP 1) AS A;
>
> ]]>
> </metadata>
> </model>
>
> <model name="NumberingPlanRaw" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[
> CREATE VIEW numbering_plan_raw (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY network) as id,
> global_title
> FROM numbering_plan np;
>
> ]]>
> </metadata>
> </model>
> </model>
> {code}
> The Materialized table:
> {code:sql}
> CREATE 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 BIGINT,
> Updated TIMESTAMP NOT NULL,
> LoadNumber BIGINT NOT NULL,
> NodeName varchar(25) not null,
> StaleCount BIGINT,
> PRIMARY KEY (VDBName , VDBVersion , SchemaName , Name)
> );
> CREATE TABLE numbering_plan_cache (
> id integer,
> global_title varchar(20),
> LoadNumber BIGINT,
> PRIMARY KEY(id)
> );
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years
[JBoss JIRA] (TEIID-4960) Problems when using External Materialized Views
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4960?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-4960:
--------------------------------
Description:
The following problem occurs when, apparently, we have two views where one depend on another, and also we use External Materialization.
The dependent view is never updated due to a "Transaction already associated with request." exception.
Logs attached.
The error:
{panel:title=The exception in logs}
2017-06-14 13:51:51,753 WARN [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue365) Hlbq3jmDWXNl org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate mvstatus('NumberingPlan', 'numbering_plan'): TEIID30384 Error while evaluating function mvstatus
2017-06-14 13:52:51,803 INFO [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue368) XAtcLA8RBrLm Materialization of view NumberingPlanRaw.numbering_plan_raw started.
2017-06-14 13:52:51,867 ERROR [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue372) XAtcLA8RBrLm TEIID30019 Unexpected exception for request XAtcLA8RBrLm.-2514910280603581440: java.lang.AssertionError: Transaction already associated with request.
{panel}
The VDB:
{code:xml}
<model name="MnomMaterialized" type="PHYSICAL">
<property name="importer.useFullSchemaName" value="false"/>
<property name="query-timeout" value="600000"/>
<source name="MnomMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/mnomDs"/>
</model>
<model name="NumberingPlanCsvData">
<source name="numberingPlanCsv-connector" translator-name="file" connection-jndi-name="java:/numberingPlanCsvDs"/>
</model>
<model name="NumberingPlan" type="VIRTUAL">
<metadata type="DDL"><![CDATA[
CREATE VIEW numbering_plan (
id integer PRIMARY KEY,
global_title varchar(20)
)
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'TRUE',
MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
)
AS
SELECT ROW_NUMBER() OVER (ORDER BY cns) as id,
cns
FROM (EXEC NumberingPlanCsvData.getTextFiles('NumberingPlan.csv')) AS f,
TEXTTABLE(f.file COLUMNS cns string DELIMITER ';' SKIP 1) AS A;
]]>
</metadata>
</model>
<model name="NumberingPlanRaw" type="VIRTUAL">
<metadata type="DDL">
<![CDATA[
CREATE VIEW numbering_plan_raw (
id integer PRIMARY KEY,
global_title varchar(20)
)
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
)
AS
SELECT ROW_NUMBER() OVER (ORDER BY network) as id,
global_title
FROM numbering_plan np;
]]>
</metadata>
</model>
</model>
{code}
The Materialized table:
{code:sql}
CREATE 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 BIGINT,
Updated TIMESTAMP NOT NULL,
LoadNumber BIGINT NOT NULL,
NodeName varchar(25) not null,
StaleCount BIGINT,
PRIMARY KEY (VDBName , VDBVersion , SchemaName , Name)
);
CREATE TABLE numbering_plan_cache (
id integer,
global_title varchar(20),
LoadNumber BIGINT,
PRIMARY KEY(id)
);
{code}
was:
The following problem occurs when, apparently, we have two views where one depend on another, and also we use External Materialization.
The dependent view is never updated due to a "Transaction already associated with request." exception.
Logs attached.
The error:
{panel:title=The exception in logs}
2017-06-14 13:51:51,753 WARN [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue365) Hlbq3jmDWXNl org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate mvstatus('NumberingPlan', 'numbering_plan'): TEIID30384 Error while evaluating function mvstatus
2017-06-14 13:52:51,803 INFO [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue368) XAtcLA8RBrLm Materialization of view NumberingPlanRaw.numbering_plan_raw started.
2017-06-14 13:52:51,867 ERROR [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue372) XAtcLA8RBrLm TEIID30019 Unexpected exception for request XAtcLA8RBrLm.-2514910280603581440: java.lang.AssertionError: Transaction already associated with request.
{panel}
The VDB:
{code:xml}
<model name="MnomMaterialized" type="PHYSICAL">
<property name="importer.useFullSchemaName" value="false"/>
<property name="query-timeout" value="600000"/>
<source name="MnomMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/mnomDs"/>
</model>
<model name="NumberingPlanCsvData">
<source name="numberingPlanCsv-connector" translator-name="file" connection-jndi-name="java:/numberingPlanCsvDs"/>
</model>
<model name="NumberingPlan" type="VIRTUAL">
<metadata type="DDL"><![CDATA[
CREATE VIEW numbering_plan (
id integer PRIMARY KEY,
global_title varchar(20)
)
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'TRUE',
MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
)
AS
SELECT ROW_NUMBER() OVER (ORDER BY cns) as id,
cns
FROM (EXEC NumberingPlanCsvData.getTextFiles('NumberingPlan.csv')) AS f,
TEXTTABLE(f.file COLUMNS cns string DELIMITER ';' SKIP 1) AS A;
]]>
</metadata>
</model>
<model name="NumberingPlanRaw" type="VIRTUAL">
<metadata type="DDL">
<![CDATA[
CREATE VIEW numbering_plan_raw (
id integer PRIMARY KEY,
global_title varchar(20)
)
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
)
AS
SELECT ROW_NUMBER() OVER (ORDER BY network) as id,
global_title
FROM numbering_plan np;
]]>
</metadata>
</model>
</model>
{code}
The Materialized table:
[code:sql}
CREATE 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 BIGINT,
Updated TIMESTAMP NOT NULL,
LoadNumber BIGINT NOT NULL,
NodeName varchar(25) not null,
StaleCount BIGINT,
PRIMARY KEY (VDBName , VDBVersion , SchemaName , Name)
);
CREATE TABLE numbering_plan_cache (
id integer,
global_title varchar(20),
LoadNumber BIGINT,
PRIMARY KEY(id)
);
{code}
> Problems when using External Materialized Views
> -----------------------------------------------
>
> Key: TEIID-4960
> URL: https://issues.jboss.org/browse/TEIID-4960
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3
> Environment: * Teiid Server 9.3.0
> * Wildfly 10
> * Mysql 5.7.18
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Attachments: server.log
>
>
> The following problem occurs when, apparently, we have two views where one depend on another, and also we use External Materialization.
> The dependent view is never updated due to a "Transaction already associated with request." exception.
> Logs attached.
> The error:
> {panel:title=The exception in logs}
> 2017-06-14 13:51:51,753 WARN [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue365) Hlbq3jmDWXNl org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate mvstatus('NumberingPlan', 'numbering_plan'): TEIID30384 Error while evaluating function mvstatus
> 2017-06-14 13:52:51,803 INFO [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue368) XAtcLA8RBrLm Materialization of view NumberingPlanRaw.numbering_plan_raw started.
> 2017-06-14 13:52:51,867 ERROR [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue372) XAtcLA8RBrLm TEIID30019 Unexpected exception for request XAtcLA8RBrLm.-2514910280603581440: java.lang.AssertionError: Transaction already associated with request.
> {panel}
> The VDB:
> {code:xml}
> <model name="MnomMaterialized" type="PHYSICAL">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="query-timeout" value="600000"/>
> <source name="MnomMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/mnomDs"/>
> </model>
>
> <model name="NumberingPlanCsvData">
> <source name="numberingPlanCsv-connector" translator-name="file" connection-jndi-name="java:/numberingPlanCsvDs"/>
> </model>
> <model name="NumberingPlan" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE VIEW numbering_plan (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'TRUE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY cns) as id,
> cns
> FROM (EXEC NumberingPlanCsvData.getTextFiles('NumberingPlan.csv')) AS f,
> TEXTTABLE(f.file COLUMNS cns string DELIMITER ';' SKIP 1) AS A;
>
> ]]>
> </metadata>
> </model>
>
> <model name="NumberingPlanRaw" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[
> CREATE VIEW numbering_plan_raw (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY network) as id,
> global_title
> FROM numbering_plan np;
>
> ]]>
> </metadata>
> </model>
> </model>
> {code}
> The Materialized table:
> {code:sql}
> CREATE 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 BIGINT,
> Updated TIMESTAMP NOT NULL,
> LoadNumber BIGINT NOT NULL,
> NodeName varchar(25) not null,
> StaleCount BIGINT,
> PRIMARY KEY (VDBName , VDBVersion , SchemaName , Name)
> );
> CREATE TABLE numbering_plan_cache (
> id integer,
> global_title varchar(20),
> LoadNumber BIGINT,
> PRIMARY KEY(id)
> );
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years
[JBoss JIRA] (TEIID-4959) VDB Resource usage restriction options
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4959?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4959:
-------------------------------------
RAM is something that Teiid controls through memory management, but CPU and even RAM & disk sometimes are not in control of Teiid as drivers to external systems could be the culprit.
> VDB Resource usage restriction options
> --------------------------------------
>
> Key: TEIID-4959
> URL: https://issues.jboss.org/browse/TEIID-4959
> Project: Teiid
> Issue Type: Feature Request
> Affects Versions: 8.12.10.6_3
> Reporter: Debbie Steigner
> Assignee: Steven Hawkins
>
> It would be very useful to have configurable options to limit resource usage (CPU, RAM, disk...) by user, query or connection. This would prevent any one user or query from consuming all/disproportionate system resources.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years
[JBoss JIRA] (TEIID-4960) Problems when using External Materialized Views
by Pedro Inácio (JIRA)
[ https://issues.jboss.org/browse/TEIID-4960?page=com.atlassian.jira.plugin... ]
Pedro Inácio commented on TEIID-4960:
-------------------------------------
When using the now deprecated strategy, all goes well.
Options for numbering_plan:
{code:xml}
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute MnomMaterialized.native(''truncate table mnom.numbering_plan_cache_staging;'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute MnomMaterialized.native(''RENAME TABLE mnom.numbering_plan_cache TO mnom.numbering_plan_cache_temp, mnom.numbering_plan_cache_staging TO mnom.numbering_plan_cache, mnom.numbering_plan_cache_temp TO mnom.numbering_plan_cache_staging'');',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM MnomMaterialized.status WHERE Name=''numbering_plan'' AND schemaname = ''NumberingPlan''',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'IMPORTED',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'MnomMaterialized.numbering_plan_cache_staging',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION'
)
{code}
Options for numbering_plan_raw:
{code_xml}
OPTIONS(
MATERIALIZED 'TRUE',
UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
"teiid_rel:MATVIEW_TTL" 86400000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute MnomMaterialized.native(''truncate table mnom.numbering_plan_raw_cache_staging;'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute MnomMaterialized.native(''RENAME TABLE mnom.numbering_plan_raw_cache TO mnom.numbering_plan_raw_cache_temp, mnom.numbering_plan_raw_cache_staging TO mnom.numbering_plan_raw_cache, mnom.numbering_plan_raw_cache_temp TO mnom.numbering_plan_raw_cache_staging'');',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM MnomMaterialized.status WHERE Name=''numbering_plan_raw'' AND schemaname = ''NumberingPlanRaw''',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'IMPORTED',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'MnomMaterialized.numbering_plan_raw_cache_staging',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION'
)
{code}
> Problems when using External Materialized Views
> -----------------------------------------------
>
> Key: TEIID-4960
> URL: https://issues.jboss.org/browse/TEIID-4960
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3
> Environment: * Teiid Server 9.3.0
> * Wildfly 10
> * Mysql 5.7.18
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Attachments: server.log
>
>
> The following problem occurs when, apparently, we have two views where one depend on another, and also we use External Materialization.
> The dependent view is never updated due to a "Transaction already associated with request." exception.
> Logs attached.
> The error:
> {panel:title=The exception in logs}
> 2017-06-14 13:51:51,753 WARN [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue365) Hlbq3jmDWXNl org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate mvstatus('NumberingPlan', 'numbering_plan'): TEIID30384 Error while evaluating function mvstatus
> 2017-06-14 13:52:51,803 INFO [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue368) XAtcLA8RBrLm Materialization of view NumberingPlanRaw.numbering_plan_raw started.
> 2017-06-14 13:52:51,867 ERROR [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue372) XAtcLA8RBrLm TEIID30019 Unexpected exception for request XAtcLA8RBrLm.-2514910280603581440: java.lang.AssertionError: Transaction already associated with request.
> {panel}
> The VDB:
> {code:xml}
> <model name="MnomMaterialized" type="PHYSICAL">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="query-timeout" value="600000"/>
> <source name="MnomMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/mnomDs"/>
> </model>
>
> <model name="NumberingPlanCsvData">
> <source name="numberingPlanCsv-connector" translator-name="file" connection-jndi-name="java:/numberingPlanCsvDs"/>
> </model>
> <model name="NumberingPlan" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE VIEW numbering_plan (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'TRUE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY cns) as id,
> cns
> FROM (EXEC NumberingPlanCsvData.getTextFiles('NumberingPlan.csv')) AS f,
> TEXTTABLE(f.file COLUMNS cns string DELIMITER ';' SKIP 1) AS A;
>
> ]]>
> </metadata>
> </model>
>
> <model name="NumberingPlanRaw" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[
> CREATE VIEW numbering_plan_raw (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY network) as id,
> global_title
> FROM numbering_plan np;
>
> ]]>
> </metadata>
> </model>
> </model>
> {code}
> The Materialized table:
> [code:sql}
> CREATE 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 BIGINT,
> Updated TIMESTAMP NOT NULL,
> LoadNumber BIGINT NOT NULL,
> NodeName varchar(25) not null,
> StaleCount BIGINT,
> PRIMARY KEY (VDBName , VDBVersion , SchemaName , Name)
> );
> CREATE TABLE numbering_plan_cache (
> id integer,
> global_title varchar(20),
> LoadNumber BIGINT,
> PRIMARY KEY(id)
> );
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years