[JBoss JIRA] (TEIID-4294) External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4294?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4294:
---------------------------------------
I should add that the rename approach can have issues with sources that do not allow an active transaction - such as mysql http://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html
Also having the teiid_rel:MATVIEW_ONERROR_ACTION as IGNORE provides more of an opportunity to see invalid states.
We may also want to offer an option to have the ttl reload invalidate by default (which would help as the materialization table would be seen as invalid until load completed). However even that is not foolproof unless all read access to the materialization table is transactional.
> External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
> --------------------------------------------------------------------------
>
> Key: TEIID-4294
> URL: https://issues.jboss.org/browse/TEIID-4294
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: dv-2016-06-23-mat-view.log
>
>
> Firstly, this issue appears even after *TEIID-4283* fix, and is db independent.
> When during materialized view's loading there is a query on the view performed, timing issues appear. In MATVIEW_AFTER_LOAD_SCRIPT there might be more commands separated by semicolon, but these commands are not performed as a single operation. In specific timing a query on such view might fail when you have a MATVIEW_AFTER_LOAD_SCRIPT like this:
> {code:sql}
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');
> {code}
> user might end up querying the view right in the moment, when 'primary' materialized table (dv_matviews_mat_view) was renamed (dv_matviews_mat_view_stage) and thus there's no such table like defined in the materialized view.
> {code:xml}
> CREATE VIEW external_long_ttl (
> customer_id integer NOT NULL,
> total_amount integer
> ) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'Source.JSTASTNY.dv_matviews_mat_view',
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'Source.JSTASTNY.dv_matviews_statustable',
> "teiid_rel:ON_VDB_START_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_create) SELECT id, vdb_create+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:ON_VDB_DROP_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_drop) SELECT id, vdb_drop+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
> "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'exec Source.native(''truncate table dv_matviews_mat_view_stage'');MERGE INTO dv_matviews_check_table(id,before_load) SELECT id, before_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');MERGE INTO dv_matviews_check_table(id,after_load) SELECT id, after_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_ONERROR_ACTION" 'IGNORE',
> "teiid_rel:MATVIEW_TTL" 20000)
> AS SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;
> {code}
> Then for some queries I get:
> {code:plain}
> 10:40:19,252 WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue130) Connector worker process failed for atomic-request=+k/MyS3sUhQX.14.7.113: org.teiid.translator.jdbc.JDBCExecutionException: 259 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."CUSTOMER_ID", g_0."TOTAL_AMOUNT" FROM "JSTASTNY".dvqe_X_XjdkY_YDV_MATVIEWS_MAT_VIEW AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
> at sun.reflect.GeneratedMethodAccessor158.invoke(Unknown Source) [:1.7.0_79]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_79]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_79]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy80.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_79]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_79]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_79]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_79]
> Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [259]: invalid table name: Could not find table/view DVQE_X_XJDKY_YDV_MATVIEWS_MAT_VIEW in schema JSTASTNY: line 1 col 62 (at pos 61)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:185)
> at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:100)
> at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1130)
> at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:877)
> at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:410)
> at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:266)
> at com.sap.db.jdbc.CallableStatementSapDB.executeQuery(CallableStatementSapDB.java:756)
> at com.sap.db.jdbc.trace.PreparedStatement.executeQuery(PreparedStatement.java:161)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
> ... 17 more
> {code}
> From the logs it seems that the after load script has completed, but probably not in the source db. In logs just before the exception I see:
> {code:plain}
> 12:34:59,247 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) Source-specific command: RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Executed command
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing MORE request
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Getting results from connector
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Obtained last batch, total row count: 1
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Remove State
> 12:34:59,664 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing Close : EXEC Source.native('RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view')
> {code}
> I attached the logs to this issue.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4287) An exception is thrown by PostgreSQL when nested CTEs are pushed down
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4287?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4287:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1349030|https://bugzilla.redhat.com/show_bug.cgi?id=1349030] from NEW to MODIFIED
> An exception is thrown by PostgreSQL when nested CTEs are pushed down
> ---------------------------------------------------------------------
>
> Key: TEIID-4287
> URL: https://issues.jboss.org/browse/TEIID-4287
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 9.1, 8.12.5, 8.13.6, 9.0.1
>
>
> I defined the following views in my VDB, based on a table in PostgreSQL:
> {code:sql}
> create view tv0 as
> WITH
> cte1 as (SELECT 1 as a),
> alias2 as (select a from cte1),
> cte3 as (select a from alias2)
> SELECT cte3.a FROM alias2 join cte3 on cte3.a=alias2.a;
> create view tv1 as
> WITH
> cte1 as (SELECT a from pg.test_a),
> alias2 as (select a from cte1),
> cte3 as (select a from alias2)
> SELECT cte3.a FROM alias2 join cte3 on cte3.a=alias2.a;
> create view tv2 as
> WITH
> alias2 as (select b, a from pg.test_a),
> cte4 as (select a from alias2)
> SELECT cte4.a FROM cte4 join alias2 on cte4.a=alias2.a ;
> {code}
> The following query, based on views tv0 and tv1:
> {code:sql}
> with
> CTE1 as (
> select a from (
> with CTE11 as (select a from views.tv0)
> select a from CTE11
> ) as SUBQ1),
> CTE2 as (
> select a from (
> with CTE21 as (select a from views.tv1)
> select a from CTE21
> ) as SUBQ2)
> select * from CTE1 as T1 join CTE2 as T2 on T1.a=T2.a
> {code}
> throws this exception:
> {code:sql}
> 18:55:23,260 WARN [org.teiid.PROCESSOR] (Worker24_QueryProcessorQueue773) qPWXuJPW78go TEIID30020 Processing exception for request qPWXuJPW78go.3 'TEIID30504 test_tables_pg: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH alias2__1 (a) AS (SELECT g_0."a" FROM "public"."test_a" AS g_0) SELECT g_1.a AS c_0 FROM alias2__1 AS g_0, alias2 AS g_1 WHERE g_1.a = g_0.a AND g_1.a = 1 ORDER BY c_0]'. Originally TeiidProcessingException 'ERROR: Relation "alias2" does not exist
> Position: 113' QueryExecutorImpl.java:2157. Enable more detailed logging to see the entire stacktrace.
> 19:00:13,379 WARN [org.teiid.CONNECTOR] (Worker28_QueryProcessorQueue775) qPWXuJPW78go Connector worker process failed for atomic-request=qPWXuJPW78go.4.7.144: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH alias2__1 (a) AS (SELECT g_0."a" FROM "public"."test_a" AS g_0) SELECT g_1.a AS c_0 FROM alias2__1 AS g_0, alias2 AS g_1 WHERE g_1.a = g_0.a AND g_1.a = 1 ORDER BY c_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
> at sun.reflect.GeneratedMethodAccessor86.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy56.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.postgresql.util.PSQLException: ERROR: Relation "alias2" does not exist
> Position: 113
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 17 more
> {code}
> A similar query based on view tv2, throws a different exception:
> {code:sql}
> with
> CTE1 as (
> select a from (
> with CTE11 as (select a from views.tv2)
> select a from CTE11
> ) as SUBQ1),
> CTE2 as (
> select a from (
> with CTE21 as (select a from views.tv2)
> select a from CTE21
> ) as SUBQ2)
> select * from CTE1 as T1 join CTE2 as T2 on T1.a=T2.a
> {code}
> {code:sql}
> 19:05:02,784 WARN [org.teiid.CONNECTOR] (Worker30_QueryProcessorQueue778) qPWXuJPW78go Connector worker process failed for atomic-request=qPWXuJPW78go.5.2.145: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH alias2 (b, a) AS (SELECT NULL, g_0."a" FROM "public"."test_a" AS g_0), alias2__1 (a, b) AS (SELECT NULL, g_0."a" FROM "public"."test_a" AS g_0) SELECT g_2.a, g_0.a FROM alias2 AS g_0, alias2 AS g_1, alias2 AS g_2, alias2__1 AS g_3 WHERE g_2.a = g_3.a AND g_0.a = g_1.a AND g_0.a = g_2.a]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
> at sun.reflect.GeneratedMethodAccessor86.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy56.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.postgresql.util.PSQLException: ERROR: failed to find conversion function from unknown to integer
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 17 more
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4294) External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4294?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4294:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1349377|https://bugzilla.redhat.com/show_bug.cgi?id=1349377] from NEW to CLOSED
> External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
> --------------------------------------------------------------------------
>
> Key: TEIID-4294
> URL: https://issues.jboss.org/browse/TEIID-4294
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: dv-2016-06-23-mat-view.log
>
>
> Firstly, this issue appears even after *TEIID-4283* fix, and is db independent.
> When during materialized view's loading there is a query on the view performed, timing issues appear. In MATVIEW_AFTER_LOAD_SCRIPT there might be more commands separated by semicolon, but these commands are not performed as a single operation. In specific timing a query on such view might fail when you have a MATVIEW_AFTER_LOAD_SCRIPT like this:
> {code:sql}
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');
> {code}
> user might end up querying the view right in the moment, when 'primary' materialized table (dv_matviews_mat_view) was renamed (dv_matviews_mat_view_stage) and thus there's no such table like defined in the materialized view.
> {code:xml}
> CREATE VIEW external_long_ttl (
> customer_id integer NOT NULL,
> total_amount integer
> ) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'Source.JSTASTNY.dv_matviews_mat_view',
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'Source.JSTASTNY.dv_matviews_statustable',
> "teiid_rel:ON_VDB_START_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_create) SELECT id, vdb_create+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:ON_VDB_DROP_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_drop) SELECT id, vdb_drop+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
> "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'exec Source.native(''truncate table dv_matviews_mat_view_stage'');MERGE INTO dv_matviews_check_table(id,before_load) SELECT id, before_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');MERGE INTO dv_matviews_check_table(id,after_load) SELECT id, after_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_ONERROR_ACTION" 'IGNORE',
> "teiid_rel:MATVIEW_TTL" 20000)
> AS SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;
> {code}
> Then for some queries I get:
> {code:plain}
> 10:40:19,252 WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue130) Connector worker process failed for atomic-request=+k/MyS3sUhQX.14.7.113: org.teiid.translator.jdbc.JDBCExecutionException: 259 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."CUSTOMER_ID", g_0."TOTAL_AMOUNT" FROM "JSTASTNY".dvqe_X_XjdkY_YDV_MATVIEWS_MAT_VIEW AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
> at sun.reflect.GeneratedMethodAccessor158.invoke(Unknown Source) [:1.7.0_79]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_79]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_79]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy80.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_79]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_79]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_79]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_79]
> Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [259]: invalid table name: Could not find table/view DVQE_X_XJDKY_YDV_MATVIEWS_MAT_VIEW in schema JSTASTNY: line 1 col 62 (at pos 61)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:185)
> at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:100)
> at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1130)
> at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:877)
> at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:410)
> at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:266)
> at com.sap.db.jdbc.CallableStatementSapDB.executeQuery(CallableStatementSapDB.java:756)
> at com.sap.db.jdbc.trace.PreparedStatement.executeQuery(PreparedStatement.java:161)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
> ... 17 more
> {code}
> From the logs it seems that the after load script has completed, but probably not in the source db. In logs just before the exception I see:
> {code:plain}
> 12:34:59,247 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) Source-specific command: RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Executed command
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing MORE request
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Getting results from connector
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Obtained last batch, total row count: 1
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Remove State
> 12:34:59,664 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing Close : EXEC Source.native('RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view')
> {code}
> I attached the logs to this issue.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4294) External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4294?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4294.
-----------------------------------
Resolution: Rejected
An XA transaction is wrapping the whole before/load/after sequence. If the source is non-XA there's not much we can do to ensure atomicity. If the commands can be put together in the same native call, then that should be done.
> External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
> --------------------------------------------------------------------------
>
> Key: TEIID-4294
> URL: https://issues.jboss.org/browse/TEIID-4294
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: dv-2016-06-23-mat-view.log
>
>
> Firstly, this issue appears even after *TEIID-4283* fix, and is db independent.
> When during materialized view's loading there is a query on the view performed, timing issues appear. In MATVIEW_AFTER_LOAD_SCRIPT there might be more commands separated by semicolon, but these commands are not performed as a single operation. In specific timing a query on such view might fail when you have a MATVIEW_AFTER_LOAD_SCRIPT like this:
> {code:sql}
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');
> {code}
> user might end up querying the view right in the moment, when 'primary' materialized table (dv_matviews_mat_view) was renamed (dv_matviews_mat_view_stage) and thus there's no such table like defined in the materialized view.
> {code:xml}
> CREATE VIEW external_long_ttl (
> customer_id integer NOT NULL,
> total_amount integer
> ) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'Source.JSTASTNY.dv_matviews_mat_view',
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'Source.JSTASTNY.dv_matviews_statustable',
> "teiid_rel:ON_VDB_START_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_create) SELECT id, vdb_create+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:ON_VDB_DROP_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_drop) SELECT id, vdb_drop+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
> "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'exec Source.native(''truncate table dv_matviews_mat_view_stage'');MERGE INTO dv_matviews_check_table(id,before_load) SELECT id, before_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');MERGE INTO dv_matviews_check_table(id,after_load) SELECT id, after_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_ONERROR_ACTION" 'IGNORE',
> "teiid_rel:MATVIEW_TTL" 20000)
> AS SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;
> {code}
> Then for some queries I get:
> {code:plain}
> 10:40:19,252 WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue130) Connector worker process failed for atomic-request=+k/MyS3sUhQX.14.7.113: org.teiid.translator.jdbc.JDBCExecutionException: 259 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."CUSTOMER_ID", g_0."TOTAL_AMOUNT" FROM "JSTASTNY".dvqe_X_XjdkY_YDV_MATVIEWS_MAT_VIEW AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
> at sun.reflect.GeneratedMethodAccessor158.invoke(Unknown Source) [:1.7.0_79]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_79]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_79]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy80.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_79]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_79]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_79]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_79]
> Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [259]: invalid table name: Could not find table/view DVQE_X_XJDKY_YDV_MATVIEWS_MAT_VIEW in schema JSTASTNY: line 1 col 62 (at pos 61)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:185)
> at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:100)
> at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1130)
> at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:877)
> at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:410)
> at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:266)
> at com.sap.db.jdbc.CallableStatementSapDB.executeQuery(CallableStatementSapDB.java:756)
> at com.sap.db.jdbc.trace.PreparedStatement.executeQuery(PreparedStatement.java:161)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
> ... 17 more
> {code}
> From the logs it seems that the after load script has completed, but probably not in the source db. In logs just before the exception I see:
> {code:plain}
> 12:34:59,247 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) Source-specific command: RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Executed command
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing MORE request
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Getting results from connector
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Obtained last batch, total row count: 1
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Remove State
> 12:34:59,664 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing Close : EXEC Source.native('RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view')
> {code}
> I attached the logs to this issue.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4294) External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4294?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-4294:
-------------------------------------------
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1349377
Bugzilla Update: Perform
> External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
> --------------------------------------------------------------------------
>
> Key: TEIID-4294
> URL: https://issues.jboss.org/browse/TEIID-4294
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: dv-2016-06-23-mat-view.log
>
>
> Firstly, this issue appears even after *TEIID-4283* fix, and is db independent.
> When during materialized view's loading there is a query on the view performed, timing issues appear. In MATVIEW_AFTER_LOAD_SCRIPT there might be more commands separated by semicolon, but these commands are not performed as a single operation. In specific timing a query on such view might fail when you have a MATVIEW_AFTER_LOAD_SCRIPT like this:
> {code:sql}
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');
> {code}
> user might end up querying the view right in the moment, when 'primary' materialized table (dv_matviews_mat_view) was renamed (dv_matviews_mat_view_stage) and thus there's no such table like defined in the materialized view.
> {code:xml}
> CREATE VIEW external_long_ttl (
> customer_id integer NOT NULL,
> total_amount integer
> ) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'Source.JSTASTNY.dv_matviews_mat_view',
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'Source.JSTASTNY.dv_matviews_statustable',
> "teiid_rel:ON_VDB_START_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_create) SELECT id, vdb_create+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:ON_VDB_DROP_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_drop) SELECT id, vdb_drop+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
> "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'exec Source.native(''truncate table dv_matviews_mat_view_stage'');MERGE INTO dv_matviews_check_table(id,before_load) SELECT id, before_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');MERGE INTO dv_matviews_check_table(id,after_load) SELECT id, after_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_ONERROR_ACTION" 'IGNORE',
> "teiid_rel:MATVIEW_TTL" 20000)
> AS SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;
> {code}
> Then for some queries I get:
> {code:plain}
> 10:40:19,252 WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue130) Connector worker process failed for atomic-request=+k/MyS3sUhQX.14.7.113: org.teiid.translator.jdbc.JDBCExecutionException: 259 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."CUSTOMER_ID", g_0."TOTAL_AMOUNT" FROM "JSTASTNY".dvqe_X_XjdkY_YDV_MATVIEWS_MAT_VIEW AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
> at sun.reflect.GeneratedMethodAccessor158.invoke(Unknown Source) [:1.7.0_79]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_79]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_79]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy80.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_79]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_79]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_79]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_79]
> Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [259]: invalid table name: Could not find table/view DVQE_X_XJDKY_YDV_MATVIEWS_MAT_VIEW in schema JSTASTNY: line 1 col 62 (at pos 61)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:185)
> at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:100)
> at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1130)
> at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:877)
> at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:410)
> at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:266)
> at com.sap.db.jdbc.CallableStatementSapDB.executeQuery(CallableStatementSapDB.java:756)
> at com.sap.db.jdbc.trace.PreparedStatement.executeQuery(PreparedStatement.java:161)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
> ... 17 more
> {code}
> From the logs it seems that the after load script has completed, but probably not in the source db. In logs just before the exception I see:
> {code:plain}
> 12:34:59,247 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) Source-specific command: RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Executed command
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing MORE request
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Getting results from connector
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Obtained last batch, total row count: 1
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Remove State
> 12:34:59,664 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing Close : EXEC Source.native('RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view')
> {code}
> I attached the logs to this issue.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4294) External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
by Jan Stastny (JIRA)
Jan Stastny created TEIID-4294:
----------------------------------
Summary: External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
Key: TEIID-4294
URL: https://issues.jboss.org/browse/TEIID-4294
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.12.5
Reporter: Jan Stastny
Assignee: Steven Hawkins
Priority: Blocker
Attachments: dv-2016-06-23-mat-view.log
Firstly, this issue appears even after *TEIID-4283* fix, and is db independent.
When during materialized view's loading there is a query on the view performed, timing issues appear. In MATVIEW_AFTER_LOAD_SCRIPT there might be more commands separated by semicolon, but these commands are not performed as a single operation. In specific timing a query on such view might fail when you have a MATVIEW_AFTER_LOAD_SCRIPT like this:
{code:sql}
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');
{code}
user might end up querying the view right in the moment, when 'primary' materialized table (dv_matviews_mat_view) was renamed (dv_matviews_mat_view_stage) and thus there's no such table like defined in the materialized view.
{code:xml}
CREATE VIEW external_long_ttl (
customer_id integer NOT NULL,
total_amount integer
) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'Source.JSTASTNY.dv_matviews_mat_view',
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'Source.JSTASTNY.dv_matviews_statustable',
"teiid_rel:ON_VDB_START_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_create) SELECT id, vdb_create+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_drop) SELECT id, vdb_drop+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
"teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'exec Source.native(''truncate table dv_matviews_mat_view_stage'');MERGE INTO dv_matviews_check_table(id,before_load) SELECT id, before_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');MERGE INTO dv_matviews_check_table(id,after_load) SELECT id, after_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'IGNORE',
"teiid_rel:MATVIEW_TTL" 20000)
AS SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;
{code}
Then for some queries I get:
{code:plain}
10:40:19,252 WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue130) Connector worker process failed for atomic-request=+k/MyS3sUhQX.14.7.113: org.teiid.translator.jdbc.JDBCExecutionException: 259 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."CUSTOMER_ID", g_0."TOTAL_AMOUNT" FROM "JSTASTNY".dvqe_X_XjdkY_YDV_MATVIEWS_MAT_VIEW AS g_0]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
at sun.reflect.GeneratedMethodAccessor158.invoke(Unknown Source) [:1.7.0_79]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_79]
at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_79]
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy80.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_79]
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_79]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_79]
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_79]
Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [259]: invalid table name: Could not find table/view DVQE_X_XJDKY_YDV_MATVIEWS_MAT_VIEW in schema JSTASTNY: line 1 col 62 (at pos 61)
at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345)
at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:185)
at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:100)
at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1130)
at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:877)
at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:410)
at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:266)
at com.sap.db.jdbc.CallableStatementSapDB.executeQuery(CallableStatementSapDB.java:756)
at com.sap.db.jdbc.trace.PreparedStatement.executeQuery(PreparedStatement.java:161)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
... 17 more
{code}
>From the logs it seems that the after load script has completed, but probably not in the source db. In logs just before the exception I see:
{code:plain}
12:34:59,247 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) Source-specific command: RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view
12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Executed command
12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing MORE request
12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Getting results from connector
12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Obtained last batch, total row count: 1
12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Remove State
12:34:59,664 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing Close : EXEC Source.native('RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view')
{code}
I attached the logs to this issue.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4293) Some Management CLI should setAllowedValues
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-4293?page=com.atlassian.jira.plugin... ]
Kylin Soong updated TEIID-4293:
-------------------------------
Description:
* /subsystem=teiid:write-attribute(name=authentication-type,value=USERPASSWORD) - the allowed values should be USERPASSWORD,GSS
* /subsystem=teiid:clear-cache(cache-type=PREPARED_PLAN_CACHE) - the cach-type allowed values should be PREPARED_PLAN_CACHE, QUERY_SERVICE_RESULT_SET_CACHE
* /subsystem=teiid/transport=jdbc:write-attribute(name=protocol,value=teiid) - the protocol allowed values should be teiid, pg,?
* /subsystem=teiid/transport=jdbc:write-attribute(name=keystore-type,value=JKS) - the keystore-type allowed values should be JKS, and ?
was:
* /subsystem=teiid:write-attribute(name=authentication-type,value=USERPASSWORD) - the allowed values should be USERPASSWORD,GSS
* /subsystem=teiid:clear-cache(cache-type=PREPARED_PLAN_CACHE) - the cach-type allowed values should be PREPARED_PLAN_CACHE, QUERY_SERVICE_RESULT_SET_CACHE
* /subsystem=teiid/transport=jdbc:write-attribute(name=protocol,value=teiid) - the protocol allowed values should be teiid, pg,?
> Some Management CLI should setAllowedValues
> -------------------------------------------
>
> Key: TEIID-4293
> URL: https://issues.jboss.org/browse/TEIID-4293
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Affects Versions: 9.1
> Reporter: Kylin Soong
> Assignee: Kylin Soong
> Fix For: 9.x
>
>
> * /subsystem=teiid:write-attribute(name=authentication-type,value=USERPASSWORD) - the allowed values should be USERPASSWORD,GSS
> * /subsystem=teiid:clear-cache(cache-type=PREPARED_PLAN_CACHE) - the cach-type allowed values should be PREPARED_PLAN_CACHE, QUERY_SERVICE_RESULT_SET_CACHE
> * /subsystem=teiid/transport=jdbc:write-attribute(name=protocol,value=teiid) - the protocol allowed values should be teiid, pg,?
> * /subsystem=teiid/transport=jdbc:write-attribute(name=keystore-type,value=JKS) - the keystore-type allowed values should be JKS, and ?
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4293) Some Management CLI should setAllowedValues
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-4293?page=com.atlassian.jira.plugin... ]
Kylin Soong updated TEIID-4293:
-------------------------------
Description:
* /subsystem=teiid:write-attribute(name=authentication-type,value=USERPASSWORD) - the allowed values should be USERPASSWORD,GSS
* /subsystem=teiid:clear-cache(cache-type=PREPARED_PLAN_CACHE) - the cach-type allowed values should be PREPARED_PLAN_CACHE, QUERY_SERVICE_RESULT_SET_CACHE
* /subsystem=teiid/transport=jdbc:write-attribute(name=protocol,value=teiid) - the protocol allowed values should be teiid, pg,?
was:
* /subsystem=teiid:write-attribute(name=authentication-type,value=USERPASSWORD) - the allowed values should be USERPASSWORD,GSS
* /subsystem=teiid:clear-cache(cache-type=PREPARED_PLAN_CACHE) - the cach-type allowed values should be PREPARED_PLAN_CACHE, QUERY_SERVICE_RESULT_SET_CACHE
> Some Management CLI should setAllowedValues
> -------------------------------------------
>
> Key: TEIID-4293
> URL: https://issues.jboss.org/browse/TEIID-4293
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Affects Versions: 9.1
> Reporter: Kylin Soong
> Assignee: Kylin Soong
> Fix For: 9.x
>
>
> * /subsystem=teiid:write-attribute(name=authentication-type,value=USERPASSWORD) - the allowed values should be USERPASSWORD,GSS
> * /subsystem=teiid:clear-cache(cache-type=PREPARED_PLAN_CACHE) - the cach-type allowed values should be PREPARED_PLAN_CACHE, QUERY_SERVICE_RESULT_SET_CACHE
> * /subsystem=teiid/transport=jdbc:write-attribute(name=protocol,value=teiid) - the protocol allowed values should be teiid, pg,?
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4293) Some Management CLI should setAllowedValues
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-4293?page=com.atlassian.jira.plugin... ]
Kylin Soong updated TEIID-4293:
-------------------------------
Description:
* /subsystem=teiid:write-attribute(name=authentication-type,value=USERPASSWORD) - the allowed values should be USERPASSWORD,GSS
* /subsystem=teiid:clear-cache(cache-type=PREPARED_PLAN_CACHE) - the cach-type allowed values should be PREPARED_PLAN_CACHE, QUERY_SERVICE_RESULT_SET_CACHE
was:* /subsystem=teiid:write-attribute(name=authentication-type,value=USERPASSWORD) - the allowed values should be USERPASSWORD,GSS
> Some Management CLI should setAllowedValues
> -------------------------------------------
>
> Key: TEIID-4293
> URL: https://issues.jboss.org/browse/TEIID-4293
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Affects Versions: 9.1
> Reporter: Kylin Soong
> Assignee: Kylin Soong
> Fix For: 9.x
>
>
> * /subsystem=teiid:write-attribute(name=authentication-type,value=USERPASSWORD) - the allowed values should be USERPASSWORD,GSS
> * /subsystem=teiid:clear-cache(cache-type=PREPARED_PLAN_CACHE) - the cach-type allowed values should be PREPARED_PLAN_CACHE, QUERY_SERVICE_RESULT_SET_CACHE
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years