[JBoss JIRA] (TEIIDSB-208) Use the same google spreadsheet logic as teiid
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-208?page=com.atlassian.jira.plug... ]
Steven Hawkins commented on TEIIDSB-208:
----------------------------------------
Moved the common logic into the api module (which is now a little misnamed). And updated the spring boot side. There was a similar issue with the salesforce logic in that several properties had different cases from the teiid side (spreadSheet vs. spreadsheet), so now we're using the teiid case on both sides. The new spreadsheets map property is expected to be set via spring map properties - which works for application.properties or yaml, but I'm not sure about as env properties. If it does not, then I'll need to add the same string property map parsing as on the teiid side.
> Use the same google spreadsheet logic as teiid
> ----------------------------------------------
>
> Key: TEIIDSB-208
> URL: https://issues.redhat.com/browse/TEIIDSB-208
> Project: Teiid Spring Boot
> Issue Type: Quality Risk
> Components: datasource
> Reporter: Steven Hawkins
> Priority: Major
> Fix For: 1.6.0
>
> Original Estimate: 5 hours
> Remaining Estimate: 5 hours
>
> With a refinement to support multiple spreadsheets we should pull this logic forward by sharing the implementation.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 6 months
[JBoss JIRA] (TEIID-5969) The "already removed" exception in case of copyLobs=true translator property for MSSQL
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5969?page=com.atlassian.jira.plugi... ]
Steven Hawkins commented on TEIID-5969:
---------------------------------------
There are a couple of things getting mixed together here.
> in the case of MSSQL connector and copyLobs=true in its translator property.
That is not expected. We should create a disk / memory backed copy that will live as long as the query is open.
> After debugging I discovered that the error occurred actually because of not enough value in
That is not really correlated to copyLobs - it controls how much of a lob we may hold in memory / inline vs. disk. You may have found a code path though were that is inhibiting the full copying and thus seeing the already removed exception.
> But if someone put a data with text field type in MSSQL having more than, say, 20000 symbols then the field won't be read by Teiid at all, Teiid will just throw out the exception mentioned above.
Again that is not expected. What should happen is that the initial copy will happen, then the cast will truncate the lob value.
> Will it be possible to improve LobManager so that it can read Text data dynamically increasing needed memory for the process? Otherwise even setting the org.teiid.maxStringLength to 20000 Teiid can raise the exception because of a Text data with length more than 20000 symbols.
No, that is really a separate consideration. You are asking about having dynamic / unlimited length string values by default - that is dangerous from a memory management perspective.
> The "already removed" exception in case of copyLobs=true translator property for MSSQL
> --------------------------------------------------------------------------------------
>
> Key: TEIID-5969
> URL: https://issues.redhat.com/browse/TEIID-5969
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Affects Versions: 13.1
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Major
>
> We're getting the following exception:
> {code:java}
> 2020-06-10 16:01:45,326 WARN [org.teiid.PROCESSOR] (Worker21_QueryProcessorQueue272) 1bxj3NJqJLpO TEIID30020 Processing exception for request 1bxj3NJqJLpO.10 'TEIID30328 Unable to evaluate convert(ms_dwh.v1.expr1, string): TEIID30384 Error while evaluating function convert'. Originally ExpressionEvaluationException 'already removed' FileStore.java:156. Enable more detailed logging to see the entire stacktrace. {code}
> in the case of MSSQL connector and copyLobs=true in its translator property.
> After debugging I discovered that the error occurred actually because of not enough value in
> MAX_LOB_MEMORY_BYTES which is used in LobManager. The variable depends on
> org.teiid.maxStringLength which is set usually to 4000 by default. But if someone put a data with text field type in MSSQL having more than, say, 20000 symbols then the field won't be read by Teiid at all, Teiid will just throw out the exception mentioned above. Will it be possible to improve LobManager so that it can read Text data dynamically increasing needed memory for the process? Otherwise even setting the org.teiid.maxStringLength to 20000 Teiid can raise the exception because of a Text data with length more than 20000 symbols.
>
> Steps to reproduce:
> # create the v1 table in MSSQL:
> {code:java}
> CREATE TABLE [dbo].[v1](CREATE TABLE [dbo].[v1]( [expr1] [text] NULL, [b] [int] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] {code}
> 2. insert in the table the following data (I did it via standard MSSQL Server Management Studio):
> {code:java}
> insert into test_dwh.dbo.v1 values (replicate(convert(varchar(max),'a'), 20000), 1) ; {code}
> 3. add MSSQL as a source to Teiid:
> {code:java}
> <datasource jndi-name="java:/mssql-test-dwh" pool-name="mssql-test-dwh" enabled="true" use-java-context="true">
> <connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_dwh</connection-url>
> <driver-class&gt;com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class&gt;
> <driver>com.microsoft.sqlserver</driver>
> <new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
> <pool>
> <min-pool-size>2</min-pool-size>
> <max-pool-size>70</max-pool-size>
> </pool>
> <security>
> <user-name>*****</user-name>
> <password>*****</password>
> </security>
> <validation>
> <check-valid-connection-sql>select 1</check-valid-connection-sql>
> </validation>
> <timeout>
> <blocking-timeout-millis>120000</blocking-timeout-millis>
> <idle-timeout-minutes>5</idle-timeout-minutes>
> </timeout>
> </datasource>{code}
> 4. add ms model:
> {code:java}
> <model name="ms_dwh">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="importer.tableTypes" value="TABLE,VIEW"/>
> <property name="importer.importKeys" value="false"/>
> <property name="importer.schemaPattern" value="dbo"/>
> <source name="dwh_ms" translator-name="mySqlserver" connection-jndi-name="java:/mssql-test-dwh"/>
> </model>{code}
> 5. add mySqlserver translator:
> {code:java}
> <translator name="mySqlserver" type="sqlserver">
> <property name="SupportsNativeQueries" value="true"/>
> <property name="copyLobs" value="true"/>
> </translator> {code}
> 6. try to run the following query:
> {code:java}
> select cast(expr1 as string) from ms_dwh.v1 ;; {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 6 months
[JBoss JIRA] (TEIID-5969) The "already removed" exception in case of copyLobs=true translator property for MSSQL
by Dmitrii Pogorelov (Jira)
[ https://issues.redhat.com/browse/TEIID-5969?page=com.atlassian.jira.plugi... ]
Dmitrii Pogorelov updated TEIID-5969:
-------------------------------------
Description:
We're getting the following exception:
{code:java}
2020-06-10 16:01:45,326 WARN [org.teiid.PROCESSOR] (Worker21_QueryProcessorQueue272) 1bxj3NJqJLpO TEIID30020 Processing exception for request 1bxj3NJqJLpO.10 'TEIID30328 Unable to evaluate convert(ms_dwh.v1.expr1, string): TEIID30384 Error while evaluating function convert'. Originally ExpressionEvaluationException 'already removed' FileStore.java:156. Enable more detailed logging to see the entire stacktrace. {code}
in the case of MSSQL connector and copyLobs=true in its translator property.
After debugging I discovered that the error occurred actually because of not enough value in
MAX_LOB_MEMORY_BYTES which is used in LobManager. The variable depends on
org.teiid.maxStringLength which is set usually to 4000 by default. But if someone put a data with text field type in MSSQL having more than, say, 20000 symbols then the field won't be read by Teiid at all, Teiid will just throw out the exception mentioned above. Will it be possible to improve LobManager so that it can read Text data dynamically increasing needed memory for the process? Otherwise even setting the org.teiid.maxStringLength to 20000 Teiid can raise the exception because of a Text data with length more than 20000 symbols.
Steps to reproduce:
# create the v1 table in MSSQL:
{code:java}
CREATE TABLE [dbo].[v1](CREATE TABLE [dbo].[v1]( [expr1] [text] NULL, [b] [int] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] {code}
2. insert in the table the following data (I did it via standard MSSQL Server Management Studio):
{code:java}
insert into test_dwh.dbo.v1 values (replicate(convert(varchar(max),'a'), 20000), 1) ; {code}
3. add MSSQL as a source to Teiid:
{code:java}
<datasource jndi-name="java:/mssql-test-dwh" pool-name="mssql-test-dwh" enabled="true" use-java-context="true">
<connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_dwh</connection-url>
<driver-class&gt;com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class&gt;
<driver>com.microsoft.sqlserver</driver>
<new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
</pool>
<security>
<user-name>*****</user-name>
<password>*****</password>
</security>
<validation>
<check-valid-connection-sql>select 1</check-valid-connection-sql>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>{code}
4. add ms model:
{code:java}
<model name="ms_dwh">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<property name="importer.schemaPattern" value="dbo"/>
<source name="dwh_ms" translator-name="mySqlserver" connection-jndi-name="java:/mssql-test-dwh"/>
</model>{code}
5. add mySqlserver translator:
{code:java}
<translator name="mySqlserver" type="sqlserver">
<property name="SupportsNativeQueries" value="true"/>
<property name="copyLobs" value="true"/>
</translator> {code}
6. try to run the following query:
{code:java}
select cast(expr1 as string) from ms_dwh.v1 ;; {code}
was:
We're getting the following exception:
{code:java}
2020-06-10 16:01:45,326 WARN [org.teiid.PROCESSOR] (Worker21_QueryProcessorQueue272) 1bxj3NJqJLpO TEIID30020 Processing exception for request 1bxj3NJqJLpO.10 'TEIID30328 Unable to evaluate convert(ms_dwh.v1.expr1, string): TEIID30384 Error while evaluating function convert'. Originally ExpressionEvaluationException 'already removed' FileStore.java:156. Enable more detailed logging to see the entire stacktrace. {code}
in the case of MSSQL connector and copyLobs=true in its translator property.
After debugging I discovered that the error occurred actually because of not enough value in
MAX_LOB_MEMORY_BYTES which is used in LobManager. The variable depends on
org.teiid.maxStringLength which is set usually to 4000 by default. But if someone put a data with text field type in MSSQL having more than, say, 20000 symbols then the field won't be read by Teiid at all, Teiid will just throw out the exception mentioned above. Will it be possible to improve LobManager so that it can read Text data dynamically increasing needed memory for the process? Otherwise even setting the org.teiid.maxStringLength to 20000 Teiid can raise the exception because of a Text data with length more than 20000 symbols.
Steps to reproduce:
# create the v1 table in MSSQL:
{code:java}
CREATE TABLE [dbo].[v1](CREATE TABLE [dbo].[v1]( [expr1] [text] NULL, [b] [int] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] {code}
2. insert in the table the following data:
{code:java}
insert into test_dwh.dbo.v1 values (replicate(convert(varchar(max),'a'), 20000), 1) ; {code}
3. add MSSQL as a source to Teiid:
{code:java}
<datasource jndi-name="java:/mssql-test-dwh" pool-name="mssql-test-dwh" enabled="true" use-java-context="true">
<connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_dwh</connection-url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<driver>com.microsoft.sqlserver</driver>
<new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
</pool>
<security>
<user-name>*****</user-name>
<password>*****</password>
</security>
<validation>
<check-valid-connection-sql>select 1</check-valid-connection-sql>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>{code}
4. add ms model:
{code:java}
<model name="ms_dwh">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<property name="importer.schemaPattern" value="dbo"/>
<source name="dwh_ms" translator-name="mySqlserver" connection-jndi-name="java:/mssql-test-dwh"/>
</model>{code}
5. add mySqlserver translator:
{code:java}
<translator name="mySqlserver" type="sqlserver">
<property name="SupportsNativeQueries" value="true"/>
<property name="copyLobs" value="true"/>
</translator> {code}
6. try to run the following query:
{code:java}
select cast(expr1 as string) from ms_dwh.v1 ;; {code}
> The "already removed" exception in case of copyLobs=true translator property for MSSQL
> --------------------------------------------------------------------------------------
>
> Key: TEIID-5969
> URL: https://issues.redhat.com/browse/TEIID-5969
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Affects Versions: 13.1
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Major
>
> We're getting the following exception:
> {code:java}
> 2020-06-10 16:01:45,326 WARN [org.teiid.PROCESSOR] (Worker21_QueryProcessorQueue272) 1bxj3NJqJLpO TEIID30020 Processing exception for request 1bxj3NJqJLpO.10 'TEIID30328 Unable to evaluate convert(ms_dwh.v1.expr1, string): TEIID30384 Error while evaluating function convert'. Originally ExpressionEvaluationException 'already removed' FileStore.java:156. Enable more detailed logging to see the entire stacktrace. {code}
> in the case of MSSQL connector and copyLobs=true in its translator property.
> After debugging I discovered that the error occurred actually because of not enough value in
> MAX_LOB_MEMORY_BYTES which is used in LobManager. The variable depends on
> org.teiid.maxStringLength which is set usually to 4000 by default. But if someone put a data with text field type in MSSQL having more than, say, 20000 symbols then the field won't be read by Teiid at all, Teiid will just throw out the exception mentioned above. Will it be possible to improve LobManager so that it can read Text data dynamically increasing needed memory for the process? Otherwise even setting the org.teiid.maxStringLength to 20000 Teiid can raise the exception because of a Text data with length more than 20000 symbols.
>
> Steps to reproduce:
> # create the v1 table in MSSQL:
> {code:java}
> CREATE TABLE [dbo].[v1](CREATE TABLE [dbo].[v1]( [expr1] [text] NULL, [b] [int] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] {code}
> 2. insert in the table the following data (I did it via standard MSSQL Server Management Studio):
> {code:java}
> insert into test_dwh.dbo.v1 values (replicate(convert(varchar(max),'a'), 20000), 1) ; {code}
> 3. add MSSQL as a source to Teiid:
> {code:java}
> <datasource jndi-name="java:/mssql-test-dwh" pool-name="mssql-test-dwh" enabled="true" use-java-context="true">
> <connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_dwh</connection-url>
> <driver-class&gt;com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class&gt;
> <driver>com.microsoft.sqlserver</driver>
> <new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
> <pool>
> <min-pool-size>2</min-pool-size>
> <max-pool-size>70</max-pool-size>
> </pool>
> <security>
> <user-name>*****</user-name>
> <password>*****</password>
> </security>
> <validation>
> <check-valid-connection-sql>select 1</check-valid-connection-sql>
> </validation>
> <timeout>
> <blocking-timeout-millis>120000</blocking-timeout-millis>
> <idle-timeout-minutes>5</idle-timeout-minutes>
> </timeout>
> </datasource>{code}
> 4. add ms model:
> {code:java}
> <model name="ms_dwh">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="importer.tableTypes" value="TABLE,VIEW"/>
> <property name="importer.importKeys" value="false"/>
> <property name="importer.schemaPattern" value="dbo"/>
> <source name="dwh_ms" translator-name="mySqlserver" connection-jndi-name="java:/mssql-test-dwh"/>
> </model>{code}
> 5. add mySqlserver translator:
> {code:java}
> <translator name="mySqlserver" type="sqlserver">
> <property name="SupportsNativeQueries" value="true"/>
> <property name="copyLobs" value="true"/>
> </translator> {code}
> 6. try to run the following query:
> {code:java}
> select cast(expr1 as string) from ms_dwh.v1 ;; {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 6 months
[JBoss JIRA] (TEIID-5969) The "already removed" exception in case of copyLobs=true translator property for MSSQL
by Dmitrii Pogorelov (Jira)
[ https://issues.redhat.com/browse/TEIID-5969?page=com.atlassian.jira.plugi... ]
Dmitrii Pogorelov updated TEIID-5969:
-------------------------------------
Description:
We're getting the following exception:
{code:java}
2020-06-10 16:01:45,326 WARN [org.teiid.PROCESSOR] (Worker21_QueryProcessorQueue272) 1bxj3NJqJLpO TEIID30020 Processing exception for request 1bxj3NJqJLpO.10 'TEIID30328 Unable to evaluate convert(ms_dwh.v1.expr1, string): TEIID30384 Error while evaluating function convert'. Originally ExpressionEvaluationException 'already removed' FileStore.java:156. Enable more detailed logging to see the entire stacktrace. {code}
in the case of MSSQL connector and copyLobs=true in its translator property.
After debugging I discovered that the error occurred actually because of not enough value in
MAX_LOB_MEMORY_BYTES which is used in LobManager. The variable depends on
org.teiid.maxStringLength which is set usually to 4000 by default. But if someone put a data with text field type in MSSQL having more than, say, 20000 symbols then the field won't be read by Teiid at all, Teiid will just throw out the exception mentioned above. Will it be possible to improve LobManager so that it can read Text data dynamically increasing needed memory for the process? Otherwise even setting the org.teiid.maxStringLength to 20000 Teiid can raise the exception because of a Text data with length more than 20000 symbols.
Steps to reproduce:
# create the v1 table in MSSQL:
{code:java}
CREATE TABLE [dbo].[v1](CREATE TABLE [dbo].[v1]( [expr1] [text] NULL, [b] [int] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] {code}
2. insert in the table the following data:
{code:java}
insert into test_dwh.dbo.v1 values (replicate(convert(varchar(max),'a'), 20000), 1) ; {code}
3. add MSSQL as a source to Teiid:
{code:java}
<datasource jndi-name="java:/mssql-test-dwh" pool-name="mssql-test-dwh" enabled="true" use-java-context="true">
<connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_dwh</connection-url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<driver>com.microsoft.sqlserver</driver>
<new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
</pool>
<security>
<user-name>*****</user-name>
<password>*****</password>
</security>
<validation>
<check-valid-connection-sql>select 1</check-valid-connection-sql>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>{code}
4. add ms model:
{code:java}
<model name="ms_dwh">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<property name="importer.schemaPattern" value="dbo"/>
<source name="dwh_ms" translator-name="mySqlserver" connection-jndi-name="java:/mssql-test-dwh"/>
</model>{code}
5. add mySqlserver translator:
{code:java}
<translator name="mySqlserver" type="sqlserver">
<property name="SupportsNativeQueries" value="true"/>
<property name="copyLobs" value="true"/>
</translator> {code}
6. try to run the following query:
{code:java}
select cast(expr1 as string) from ms_dwh.v1 ;; {code}
was:
We're getting the following exception:
{code:java}
2020-06-10 16:01:45,326 WARN [org.teiid.PROCESSOR] (Worker21_QueryProcessorQueue272) 1bxj3NJqJLpO TEIID30020 Processing exception for request 1bxj3NJqJLpO.10 'TEIID30328 Unable to evaluate convert(ms_dwh.v1.expr1, string): TEIID30384 Error while evaluating function convert'. Originally ExpressionEvaluationException 'already removed' FileStore.java:156. Enable more detailed logging to see the entire stacktrace. {code}
in the case of MSSQL connector and copyLobs=true in its translator property.
After debugging I discovered that the error occurred actually because of not enough value in
MAX_LOB_MEMORY_BYTES which is used in LobManager. The variable depends on
org.teiid.maxStringLength which is set usually to 4000 by default. But if someone put a data with text field type in MSSQL having more than, say, 20000 symbols then the field won't be read by Teiid at all, teiid will just throw out the exception mentioned above. Will it be possible to improve LobManager so that it can read Text data dynamically increasing needed memory for the process? Otherwise even setting the org.teiid.maxStringLength to 20000 Teiid can get stuck on a Text data with length more than 20000 symbols.
Steps to reproduce:
# create the v1 table in MSSQL:
{code:java}
CREATE TABLE [dbo].[v1](CREATE TABLE [dbo].[v1]( [expr1] [text] NULL, [b] [int] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] {code}
2. insert in the table the following data:
{code:java}
insert into test_dwh.dbo.v1 values (replicate(convert(varchar(max),'a'), 20000), 1) ; {code}
3. add MSSQL as a source to Teiid:
{code:java}
<datasource jndi-name="java:/mssql-test-dwh" pool-name="mssql-test-dwh" enabled="true" use-java-context="true">
<connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_dwh</connection-url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<driver>com.microsoft.sqlserver</driver>
<new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
</pool>
<security>
<user-name>*****</user-name>
<password>*****</password>
</security>
<validation>
<check-valid-connection-sql>select 1</check-valid-connection-sql>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>{code}
4. add ms model:
{code:java}
<model name="ms_dwh">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<property name="importer.schemaPattern" value="dbo"/>
<source name="dwh_ms" translator-name="mySqlserver" connection-jndi-name="java:/mssql-test-dwh"/>
</model>{code}
5. add mySqlserver translator:
{code:java}
<translator name="mySqlserver" type="sqlserver">
<property name="SupportsNativeQueries" value="true"/>
<property name="copyLobs" value="true"/>
</translator> {code}
6. try to run the following query:
{code:java}
select cast(expr1 as string) from ms_dwh.v1 ;; {code}
> The "already removed" exception in case of copyLobs=true translator property for MSSQL
> --------------------------------------------------------------------------------------
>
> Key: TEIID-5969
> URL: https://issues.redhat.com/browse/TEIID-5969
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Affects Versions: 13.1
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Major
>
> We're getting the following exception:
> {code:java}
> 2020-06-10 16:01:45,326 WARN [org.teiid.PROCESSOR] (Worker21_QueryProcessorQueue272) 1bxj3NJqJLpO TEIID30020 Processing exception for request 1bxj3NJqJLpO.10 'TEIID30328 Unable to evaluate convert(ms_dwh.v1.expr1, string): TEIID30384 Error while evaluating function convert'. Originally ExpressionEvaluationException 'already removed' FileStore.java:156. Enable more detailed logging to see the entire stacktrace. {code}
> in the case of MSSQL connector and copyLobs=true in its translator property.
> After debugging I discovered that the error occurred actually because of not enough value in
> MAX_LOB_MEMORY_BYTES which is used in LobManager. The variable depends on
> org.teiid.maxStringLength which is set usually to 4000 by default. But if someone put a data with text field type in MSSQL having more than, say, 20000 symbols then the field won't be read by Teiid at all, Teiid will just throw out the exception mentioned above. Will it be possible to improve LobManager so that it can read Text data dynamically increasing needed memory for the process? Otherwise even setting the org.teiid.maxStringLength to 20000 Teiid can raise the exception because of a Text data with length more than 20000 symbols.
>
> Steps to reproduce:
> # create the v1 table in MSSQL:
> {code:java}
> CREATE TABLE [dbo].[v1](CREATE TABLE [dbo].[v1]( [expr1] [text] NULL, [b] [int] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] {code}
> 2. insert in the table the following data:
> {code:java}
> insert into test_dwh.dbo.v1 values (replicate(convert(varchar(max),'a'), 20000), 1) ; {code}
> 3. add MSSQL as a source to Teiid:
> {code:java}
> <datasource jndi-name="java:/mssql-test-dwh" pool-name="mssql-test-dwh" enabled="true" use-java-context="true">
> <connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_dwh</connection-url>
> <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
> <driver>com.microsoft.sqlserver</driver>
> <new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
> <pool>
> <min-pool-size>2</min-pool-size>
> <max-pool-size>70</max-pool-size>
> </pool>
> <security>
> <user-name>*****</user-name>
> <password>*****</password>
> </security>
> <validation>
> <check-valid-connection-sql>select 1</check-valid-connection-sql>
> </validation>
> <timeout>
> <blocking-timeout-millis>120000</blocking-timeout-millis>
> <idle-timeout-minutes>5</idle-timeout-minutes>
> </timeout>
> </datasource>{code}
> 4. add ms model:
> {code:java}
> <model name="ms_dwh">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="importer.tableTypes" value="TABLE,VIEW"/>
> <property name="importer.importKeys" value="false"/>
> <property name="importer.schemaPattern" value="dbo"/>
> <source name="dwh_ms" translator-name="mySqlserver" connection-jndi-name="java:/mssql-test-dwh"/>
> </model>{code}
> 5. add mySqlserver translator:
> {code:java}
> <translator name="mySqlserver" type="sqlserver">
> <property name="SupportsNativeQueries" value="true"/>
> <property name="copyLobs" value="true"/>
> </translator> {code}
> 6. try to run the following query:
> {code:java}
> select cast(expr1 as string) from ms_dwh.v1 ;; {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 6 months
[JBoss JIRA] (TEIID-5969) The "already removed" exception in case of copyLobs=true translator property for MSSQL
by Dmitrii Pogorelov (Jira)
Dmitrii Pogorelov created TEIID-5969:
----------------------------------------
Summary: The "already removed" exception in case of copyLobs=true translator property for MSSQL
Key: TEIID-5969
URL: https://issues.redhat.com/browse/TEIID-5969
Project: Teiid
Issue Type: Quality Risk
Components: Query Engine
Affects Versions: 13.1
Reporter: Dmitrii Pogorelov
Assignee: Steven Hawkins
We're getting the following exception:
{code:java}
2020-06-10 16:01:45,326 WARN [org.teiid.PROCESSOR] (Worker21_QueryProcessorQueue272) 1bxj3NJqJLpO TEIID30020 Processing exception for request 1bxj3NJqJLpO.10 'TEIID30328 Unable to evaluate convert(ms_dwh.v1.expr1, string): TEIID30384 Error while evaluating function convert'. Originally ExpressionEvaluationException 'already removed' FileStore.java:156. Enable more detailed logging to see the entire stacktrace. {code}
in the case of MSSQL connector and copyLobs=true in its translator property.
After debugging I discovered that the error occurred actually because of not enough value in
MAX_LOB_MEMORY_BYTES which is used in LobManager. The variable depends on
org.teiid.maxStringLength which is set usually to 4000 by default. But if someone put a data with text field type in MSSQL having more than, say, 20000 symbols then the field won't be read by Teiid at all, teiid will just throw out the exception mentioned above. Will it be possible to improve LobManager so that it can read Text data dynamically increasing needed memory for the process? Otherwise even setting the org.teiid.maxStringLength to 20000 Teiid can get stuck on a Text data with length more than 20000 symbols.
Steps to reproduce:
# create the v1 table in MSSQL:
{code:java}
CREATE TABLE [dbo].[v1](CREATE TABLE [dbo].[v1]( [expr1] [text] NULL, [b] [int] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] {code}
2. insert in the table the following data:
{code:java}
insert into test_dwh.dbo.v1 values (replicate(convert(varchar(max),'a'), 20000), 1) ; {code}
3. add MSSQL as a source to Teiid:
{code:java}
<datasource jndi-name="java:/mssql-test-dwh" pool-name="mssql-test-dwh" enabled="true" use-java-context="true">
<connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_dwh</connection-url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<driver>com.microsoft.sqlserver</driver>
<new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
</pool>
<security>
<user-name>*****</user-name>
<password>*****</password>
</security>
<validation>
<check-valid-connection-sql>select 1</check-valid-connection-sql>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>{code}
4. add ms model:
{code:java}
<model name="ms_dwh">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<property name="importer.schemaPattern" value="dbo"/>
<source name="dwh_ms" translator-name="mySqlserver" connection-jndi-name="java:/mssql-test-dwh"/>
</model>{code}
5. add mySqlserver translator:
{code:java}
<translator name="mySqlserver" type="sqlserver">
<property name="SupportsNativeQueries" value="true"/>
<property name="copyLobs" value="true"/>
</translator> {code}
6. try to run the following query:
{code:java}
select cast(expr1 as string) from ms_dwh.v1 ;; {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 6 months
[JBoss JIRA] (TEIIDSB-197) Add a module for each external source
by Ramesh Reddy (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-197?page=com.atlassian.jira.plug... ]
Ramesh Reddy updated TEIIDSB-197:
---------------------------------
Fix Version/s: 1.6.0
> Add a module for each external source
> -------------------------------------
>
> Key: TEIIDSB-197
> URL: https://issues.redhat.com/browse/TEIIDSB-197
> Project: Teiid Spring Boot
> Issue Type: Enhancement
> Reporter: Steven Hawkins
> Assignee: Ramesh Reddy
> Priority: Major
> Fix For: 1.6.0
>
> Original Estimate: 3 days
> Time Spent: 3 days
> Remaining Estimate: 0 minutes
>
> To simplify the logic in the operator each external source should be represented by a maven artifact - org.teiid:spring-data-x. Any dependency management would be handled in that module and not in the operator - to remove the need for a config file containing driver versions. Additionally property handling should be moved into Teiid Spring Boot such that the operator does not need to know what property prefix is expected.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 6 months
[JBoss JIRA] (TEIID-5937) Union All followed by except returns wrong results
by Dmitrii Pogorelov (Jira)
[ https://issues.redhat.com/browse/TEIID-5937?page=com.atlassian.jira.plugi... ]
Dmitrii Pogorelov commented on TEIID-5937:
------------------------------------------
[~shawkins] thx a lot!
> Union All followed by except returns wrong results
> --------------------------------------------------
>
> Key: TEIID-5937
> URL: https://issues.redhat.com/browse/TEIID-5937
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 13.1
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 15.0, 13.1.2, 14.0.1
>
> Original Estimate: 4 hours
> Time Spent: 4 hours, 30 minutes
> Remaining Estimate: 0 minutes
>
> The following queries return wrong results:
> {code:sql}
> -- <null> though should return 1
> select * from views.test2 except select * from views.test1
> UNION ALL
> select * from views.test1 except select * from views.test2 ;;
>
> -- <null> though should return 1
> (select * from views.test2 except select * from views.test1
> UNION ALL
> select * from views.test1) except select * from views.test2 ;;
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 6 months
[JBoss JIRA] (TEIID-5040) Add translator override to change the Google spreadsheet
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5040?page=com.atlassian.jira.plugi... ]
Steven Hawkins resolved TEIID-5040.
-----------------------------------
Resolution: Done
Added a new property as described in the last comment and added this to the release not highlights.
> Add translator override to change the Google spreadsheet
> --------------------------------------------------------
>
> Key: TEIID-5040
> URL: https://issues.redhat.com/browse/TEIID-5040
> Project: Teiid
> Issue Type: Enhancement
> Components: Misc. Connectors
> Reporter: Deborah Steigner
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 15.0
>
> Original Estimate: 5 hours
> Remaining Estimate: 5 hours
>
> We would like to add a "sheet name" override to the googlesheet resource adapter. So we can configure multiple google sheets without having to configure multiple resource adaptors (1 for each google sheet.)
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 6 months
[JBoss JIRA] (TEIID-5040) Add translator override to change the Google spreadsheet
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5040?focusedWorklogId=12451397&pag... ]
Steven Hawkins logged work on TEIID-5040:
-----------------------------------------
Author: Steven Hawkins
Created on: 09/Jun/20 10:27 PM
Start Date: 09/Jun/20 10:27 PM
Worklog Time Spent: 5 hours
Issue Time Tracking
-------------------
Remaining Estimate: 0 minutes (was: 5 hours)
Time Spent: 5 hours
Worklog Id: (was: 12451397)
> Add translator override to change the Google spreadsheet
> --------------------------------------------------------
>
> Key: TEIID-5040
> URL: https://issues.redhat.com/browse/TEIID-5040
> Project: Teiid
> Issue Type: Enhancement
> Components: Misc. Connectors
> Reporter: Deborah Steigner
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 15.0
>
> Original Estimate: 5 hours
> Time Spent: 5 hours
> Remaining Estimate: 0 minutes
>
> We would like to add a "sheet name" override to the googlesheet resource adapter. So we can configure multiple google sheets without having to configure multiple resource adaptors (1 for each google sheet.)
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 6 months
[JBoss JIRA] (TEIID-5962) Teiid locks many threads when Salesforce is disconnected
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5962?page=com.atlassian.jira.plugi... ]
Steven Hawkins updated TEIID-5962:
----------------------------------
Fix Version/s: 15.0
> Teiid locks many threads when Salesforce is disconnected
> --------------------------------------------------------
>
> Key: TEIID-5962
> URL: https://issues.redhat.com/browse/TEIID-5962
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Affects Versions: 12.3.1
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 15.0
>
> Attachments: Снимок экрана от 2020-05-31 11-09-10.png
>
>
> We had two incidents in our experimental service with teiid spring boot app. When Salesforce is down to 20min and service with teiid trying to call SQL from Salesforce we have many threads in tomcat server (in spring boot app). Then when 200 threads is activated service do not reply to requests because is "max threads" in tomcat of service.
> Why teiid do not send response as "Salesforce is down - error" or connection timeout?
> Micro schema:
> HTTP Requests to REST api -> Teiid spring boot app -> {broken 20min}Salesforce
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 6 months