[JBoss JIRA] (TEIID-4127) Command DB Logging - SQLCMD attribute VARCHAR(8196) is too small for large queries
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4127?page=com.atlassian.jira.plugin... ]
Van Halbert updated TEIID-4127:
-------------------------------
Steps to Reproduce:
1. Setup MySQL
2. execute politie_mysql.sql sample database // see attachment
3. Setup DV with connection to mysql
4. Setup datasource in DV/EAP to mysql // java:Politie
5. Deploy VDB named DutchPolice.vdb // see attachment
6. Execute VDB DutchPolice and execute following SQL statement:
SELECT * FROM Politie_VBL.joined;
> Command DB Logging - SQLCMD attribute VARCHAR(8196) is too small for large queries
> ------------------------------------------------------------------------------------
>
> Key: TEIID-4127
> URL: https://issues.jboss.org/browse/TEIID-4127
> Project: Teiid
> Issue Type: Bug
> Components: Tools
> Reporter: Van Halbert
> Assignee: Steven Hawkins
>
> Command DB Logging - SQLCMD attribute VARCHAR(8196) is too small for large queries
> Setup DV to use the command logging database instead of log files.
> Was testing a large join with a left outer join to test another issue and run into
> an issue with
> Version-Release number of selected component (if applicable):
> 6.4.3
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 11 months
[JBoss JIRA] (TEIID-4127) Command DB Logging - SQLCMD attribute VARCHAR(8196) is too small for large queries
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4127?page=com.atlassian.jira.plugin... ]
Van Halbert edited comment on TEIID-4127 at 4/11/16 10:49 AM:
--------------------------------------------------------------
Steps to Reproduce:
1. Setup MySQL
2. execute politie_mysql.sql sample database // see attachment
3. Setup DV with connection to mysql
4. Setup datasource in DV/EAP to mysql // java:MySQLDS
5. Deploy VDB named MyVDB.vdb // see attachment
6. Execute VDB MyVDB and execute following SQL statement:
SELECT * FROM Mytable.joined;
was (Author: van.halbert):
Steps to Reproduce:
1. Setup MySQL
2. execute politie_mysql.sql sample database // see attachment
3. Setup DV with connection to mysql
4. Setup datasource in DV/EAP to mysql // java:Politie
5. Deploy VDB named DutchPolice.vdb // see attachment
6. Execute VDB DutchPolice and execute following SQL statement:
SELECT * FROM Politie_VBL.joined;
> Command DB Logging - SQLCMD attribute VARCHAR(8196) is too small for large queries
> ------------------------------------------------------------------------------------
>
> Key: TEIID-4127
> URL: https://issues.jboss.org/browse/TEIID-4127
> Project: Teiid
> Issue Type: Bug
> Components: Tools
> Reporter: Van Halbert
> Assignee: Steven Hawkins
>
> Command DB Logging - SQLCMD attribute VARCHAR(8196) is too small for large queries
> Setup DV to use the command logging database instead of log files.
> Was testing a large join with a left outer join to test another issue and run into
> an issue with
> Version-Release number of selected component (if applicable):
> 6.4.3
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 11 months
[JBoss JIRA] (TEIID-4127) Command DB Logging - SQLCMD attribute VARCHAR(8196) is too small for large queries
by Van Halbert (JIRA)
Van Halbert created TEIID-4127:
----------------------------------
Summary: Command DB Logging - SQLCMD attribute VARCHAR(8196) is too small for large queries
Key: TEIID-4127
URL: https://issues.jboss.org/browse/TEIID-4127
Project: Teiid
Issue Type: Bug
Components: Tools
Reporter: Van Halbert
Assignee: Steven Hawkins
Command DB Logging - SQLCMD attribute VARCHAR(8196) is too small for large queries
Setup DV to use the command logging database instead of log files.
Was testing a large join with a left outer join to test another issue and run into
an issue with
Version-Release number of selected component (if applicable):
6.4.3
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 11 months
[JBoss JIRA] (TEIID-4121) Enhancing the External Materialization
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4121?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4121:
-------------------------------------
Requiring a RDBMS is something we never forced upon, and this will drive us towards that path. Another option is doing a file based persistence, or providing a optional add on component that can use Hibernate component for the access to the Status table.
> Enhancing the External Materialization
> --------------------------------------
>
> Key: TEIID-4121
> URL: https://issues.jboss.org/browse/TEIID-4121
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Affects Versions: 9.x
> Reporter: Kylin Soong
> Assignee: Steven Hawkins
> Fix For: 9.0
>
>
> The intention of move "status" table to physical database is to increase durable and fully control refresh and loading, but it increase the complexity.
> The "status" table by design should unique for whole VDB, if you look the https://teiid.gitbooks.io/documents/content/caching/External_Materializat..., the table structure:
> {code:sql}
> CREATE TABLE status
> (
> VDBName varchar(50) not null,
> VDBVersion integer not null,
> SchemaName varchar(50) not null,
> Name varchar(256) not null,
> TargetSchemaName varchar(50),
> TargetName varchar(256) not null,
> Valid boolean not null,
> LoadState varchar(25) not null,
> Cardinality long,
> Updated timestamp not null,
> LoadNumber long not null,
> PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
> );
> {code}
> but currently, one VDB may have multiple "status" table, each view may have it's own "status" table. Further more, we can consider create status table automatically, which like internal, status create once VDB start, and configured in VDB scope.
> From finishedDeployment logic in MaterializationManager, MATERIALIZED_TABLE be used to determine whether the Mat is internal or external, But we lack the validation in metadata loading, in my previous test, the Internal Mat view configured lots of external view's properties like "status" table, the validation not throw excepton.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 11 months
[JBoss JIRA] (TEIID-4115) XMLTABLE single element in sequence invalid conversion
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4115?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4115:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1323996|https://bugzilla.redhat.com/show_bug.cgi?id=1323996] from NEW to MODIFIED
> XMLTABLE single element in sequence invalid conversion
> ------------------------------------------------------
>
> Key: TEIID-4115
> URL: https://issues.jboss.org/browse/TEIID-4115
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.4
>
>
> There is a possibility to handle xml element sequences as arrays when using XMLTABLE function.
> When there's only one element in the sequence, an exception is thrown:
> {code:plain}
> org.teiid.jdbc.TeiidSQLException: TEIID10076 Invalid conversion from type class java.lang.String with value 'first' to type class [Ljava.lang.String;
> at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
> at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
> at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:703)
> at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)
> at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542)
> at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)
> at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)
> at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)
> at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:268)
> at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:497)
> at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)
> at com.sun.proxy.$Proxy6.read(Unknown Source)
> at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:405)
> at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:551)
> at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:404)
> at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:338)
> at org.jboss.qe.functions.xml.XmlTableTest.testXmlTableSequenceArrayOneElement(XmlTableTest.java:74)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:497)
> at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:80)
> at org.testng.internal.Invoker.invokeMethod(Invoker.java:714)
> at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:901)
> at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1231)
> at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127)
> at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111)
> at org.testng.TestRunner.privateRun(TestRunner.java:767)
> at org.testng.TestRunner.run(TestRunner.java:617)
> at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
> at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
> at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
> at org.testng.SuiteRunner.run(SuiteRunner.java:240)
> at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
> at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
> at org.testng.TestNG.runSuitesSequentially(TestNG.java:1198)
> at org.testng.TestNG.runSuitesLocally(TestNG.java:1123)
> at org.testng.TestNG.run(TestNG.java:1031)
> at org.apache.maven.surefire.testng.TestNGExecutor.run(TestNGExecutor.java:70)
> at org.apache.maven.surefire.testng.TestNGDirectoryTestSuite.execute(TestNGDirectoryTestSuite.java:108)
> at org.apache.maven.surefire.testng.TestNGProvider.invoke(TestNGProvider.java:111)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:497)
> at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164)
> at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110)
> at org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:172)
> at org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:104)
> at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:70)
> Caused by: org.teiid.core.types.TransformationException: TEIID10076 Invalid conversion from type class java.lang.String with value 'first' to type class [Ljava.lang.String;
> at org.teiid.core.types.DataTypeManager.transformValue(DataTypeManager.java:940)
> at org.teiid.core.types.DataTypeManager.transformValue(DataTypeManager.java:928)
> at org.teiid.query.function.FunctionDescriptor.importValue(FunctionDescriptor.java:322)
> at org.teiid.query.processor.relational.XMLTableNode.getValue(XMLTableNode.java:376)
> at org.teiid.query.processor.relational.XMLTableNode.processRow(XMLTableNode.java:341)
> at org.teiid.query.processor.relational.XMLTableNode.nextBatchDirect(XMLTableNode.java:225)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:470)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)
> 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:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> {code}
> The invocation of XMLTABLE function is as follows:
> {code:sql}
> CREATE VIEW SEQUENCE_AS_ARRAY_ONE_ELEMENT(x string[]) AS select * from xmltable('/a' passing convert('<a><b>first</b></a>', xml) columns x string[] path 'b') as x;
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 11 months
[JBoss JIRA] (TEIID-4121) Enhancing the External Materialization
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4121?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4121:
---------------------------------------
> I want to create a global STATUS table, which save all View's status, no matter where materialized table located, status will always goes to that global STATUS table. It should be better if STATUS table can be created automatically in deployment, that the datasource should be verified in advance, like H2, Mysql, Infinspan.
The reluctance to move this direction has been around scope creep - there is a lot of management that comes with presuming ownership/creation of source tables. Also there isn't really a notion of accessing a source outside of the scope of a VDB, so without some new configuration constructs the broadest level for this would be the VDB.
> From user-friendly aspect, I feel not good for configuring lots of SQL scripts and create tables manually each time if I want to use external mat. Especially, different data sources are behave differently, for example, the STATUS table's Column "Cardinality long", in latest MariaDB, the 'long' type will silently change to 'MEDIUMTEXT', so we have to use 'bigint' instead. The same as other truncate table, alter table syntax, etc, different data source also behave differently.
Except in data migration scenarios, it would not be expected that materialized views will be retargeted. Typically materialized tables will be associated with a source based upon their usage such that the materialization enhances push down - and that shouldn't change over time. So yes there is more burden on the user - and it is not expected that we can abstract effectively all of the creation / post creation tasks effectively without significant feature creep. Accounting for every possible constraint, index type (hash, function based, bit map, etc.), source specific notion of truncate/swap (which is only applicable to full snapshot), etc. is a significant undertaking even for only a small number of sources.
> IMO, we should enhance external in this term, made it more automatically, the materialized data source should be verified in advance, during configuring one option can make all things work. If not enhance in this way, at least, the document should be enhanced.
Documentation changes can of course be made at any time.
It seems like you would first start down this path by trying to elevate / automate the mechanics of the status table, then move to the materialization tables.
> Enhancing the External Materialization
> --------------------------------------
>
> Key: TEIID-4121
> URL: https://issues.jboss.org/browse/TEIID-4121
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Affects Versions: 9.x
> Reporter: Kylin Soong
> Assignee: Steven Hawkins
> Fix For: 9.0
>
>
> The intention of move "status" table to physical database is to increase durable and fully control refresh and loading, but it increase the complexity.
> The "status" table by design should unique for whole VDB, if you look the https://teiid.gitbooks.io/documents/content/caching/External_Materializat..., the table structure:
> {code:sql}
> CREATE TABLE status
> (
> VDBName varchar(50) not null,
> VDBVersion integer not null,
> SchemaName varchar(50) not null,
> Name varchar(256) not null,
> TargetSchemaName varchar(50),
> TargetName varchar(256) not null,
> Valid boolean not null,
> LoadState varchar(25) not null,
> Cardinality long,
> Updated timestamp not null,
> LoadNumber long not null,
> PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
> );
> {code}
> but currently, one VDB may have multiple "status" table, each view may have it's own "status" table. Further more, we can consider create status table automatically, which like internal, status create once VDB start, and configured in VDB scope.
> From finishedDeployment logic in MaterializationManager, MATERIALIZED_TABLE be used to determine whether the Mat is internal or external, But we lack the validation in metadata loading, in my previous test, the Internal Mat view configured lots of external view's properties like "status" table, the validation not throw excepton.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 11 months
[JBoss JIRA] (TEIID-4121) Enhancing the External Materialization
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-4121?page=com.atlassian.jira.plugin... ]
Kylin Soong commented on TEIID-4121:
------------------------------------
>From user-friendly aspect, I feel not good for configuring lots of SQL scripts and create tables manually each time if I want to use external mat. Especially, different data sources are behave differently, for example, the STATUS table's Column "Cardinality long", in latest MariaDB, the 'long' type will silently change to 'MEDIUMTEXT', so we have to use 'bigint' instead. The same as other truncate table, alter table syntax, etc, different data source also behave differently.
IMO, we should enhance external in this term, made it more automatically, the materialized data source should be verified in advance, during configuring one option can make all things work. If not enhance in this way, at least, the document should be enhanced.
> Enhancing the External Materialization
> --------------------------------------
>
> Key: TEIID-4121
> URL: https://issues.jboss.org/browse/TEIID-4121
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Affects Versions: 9.x
> Reporter: Kylin Soong
> Assignee: Steven Hawkins
> Fix For: 9.0
>
>
> The intention of move "status" table to physical database is to increase durable and fully control refresh and loading, but it increase the complexity.
> The "status" table by design should unique for whole VDB, if you look the https://teiid.gitbooks.io/documents/content/caching/External_Materializat..., the table structure:
> {code:sql}
> CREATE TABLE status
> (
> VDBName varchar(50) not null,
> VDBVersion integer not null,
> SchemaName varchar(50) not null,
> Name varchar(256) not null,
> TargetSchemaName varchar(50),
> TargetName varchar(256) not null,
> Valid boolean not null,
> LoadState varchar(25) not null,
> Cardinality long,
> Updated timestamp not null,
> LoadNumber long not null,
> PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
> );
> {code}
> but currently, one VDB may have multiple "status" table, each view may have it's own "status" table. Further more, we can consider create status table automatically, which like internal, status create once VDB start, and configured in VDB scope.
> From finishedDeployment logic in MaterializationManager, MATERIALIZED_TABLE be used to determine whether the Mat is internal or external, But we lack the validation in metadata loading, in my previous test, the Internal Mat view configured lots of external view's properties like "status" table, the validation not throw excepton.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 11 months