Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5969

The "already removed" exception in case of copyLobs=true translator property for MSSQL

    Details

    • Sprint:
      DV Sprint 65
    • Story Points:
      0.5

      Description

      We're getting the following exception:

      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. 

      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:

      1. create the v1 table in MSSQL:
        CREATE TABLE [dbo].[v1](CREATE TABLE [dbo].[v1]( [expr1] [text] NULL, [b] [int] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

        2. insert in the table the following data (I did it via standard MSSQL Server Management Studio):

        insert into test_dwh.dbo.v1 values (replicate(convert(varchar(max),'a'), 20000), 1) ; 

        3. add MSSQL as a source to Teiid:

                        <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&amp;amp;gt;com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class&amp;amp;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>

        4. add ms model:

        <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>

        5. add mySqlserver translator:

        <translator name="mySqlserver" type="sqlserver">
                <property name="SupportsNativeQueries" value="true"/>
                <property name="copyLobs" value="true"/>
            </translator> 

        6. try to run the following query:

        select cast(expr1 as string) from ms_dwh.v1 ;; 

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 Dmitrii Pogorelov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 2 hours
                  2h
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 2 hours
                  2h