13 Replies Latest reply on Sep 23, 2013 8:59 AM by Steven Hawkins

    Exception when executing certain queries

    skethire Newbie

      I am seeing the following exception when executing some queries whose resultset has string columns that can be large sometimes.

      Is this is a limit on the client side or the server side?   If it is a client side limit, what does it actually mean?  Does it indicate that a single column cannot be larger than the maxObjectSize?  Should we indefinitely increase the maxObjectSize, or is it pointing to some other problem that needs to be addressed?    I am looking for some background on this error message, and what needs to be done to properly address it.

       

             at java.lang.Thread.run(Thread.java:722)

      Caused by: org.teiid.jdbc.TeiidSQLException: TEIID20028 data length too big: 129,804,208 > max of 33,554,432.  You may need to adjust the maxObjectSize client setting.

             at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

             at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

             at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:533)

             at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1023)

             at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:312)


      Thanks

      Srini

        • 1. Re: Exception when executing certain queries
          Steven Hawkins Master

          > Is this is a limit on the client side or the server side?

           

          This exception is originating from the client side.

           

          > If it is a client side limit, what does it actually mean?

           

          The server is sending a response with a batch of data that is quite large.  To prevent memory issues, the client has a default limit of 32 megs and won't attempt to read messages of a larger size.

           

          > Does it indicate that a single column cannot be larger than the maxObjectSize?

           

          No it applies to the entire response message, which should be dominated by the size of the data.

           

          > Should we indefinitely increase the maxObjectSize, or is it pointing to some other problem that needs to be addressed?

           

          If you have configured the server to use a large batch size, you are using large fetch sizes, and issuing a query with a large number of columns, then you should increase the maxObjectSize.  This can be done via the property org.teiid.sockets.maxObjectSize that can be set as a system property or in teiid-client-settings.properties file in the classpath of the client jar - see also https://docs.jboss.org/author/display/TEIID/SSL+Client+Connections (but I do need to add a general reference for the non-ssl properties). 

           

          If you do not think that the server should be sending such large results then you'll want to tune some of the server batching parameters.

           

          Steve

          1 of 1 people found this helpful
          • 2. Re: Exception when executing certain queries
            skethire Newbie

            Steve,

            Thanks for your response.  Can you point me to the server batching parameters that are relevant here?  We are running the server with default settings right now.

             

            Thanks

            srini

            • 3. Re: Exception when executing certain queries
              Steven Hawkins Master

              The primary server side batching parameter is the processor batch size.  It defaults to 256 and the engine attempts to adjust the value up or down depending upon the calculated data width of the result.  Approximately how many large string columns are you selecting in your query?

               

              Another approach you can take is to limit the fetch size on the client side.  That can be done with Statement.setFetchSize or Teiid accepts the fetch size via the fetchSize connection property.

              • 4. Re: Exception when executing certain queries
                skethire Newbie

                Steve,

                 

                The query has 1 string/clob column - but sometimes these clobs tend to be large.  Perhaps even 256 is too much.  If we set this to a smaller number, how does it impact a client that explicitly sets a higher fetch size?  Which setting takes precedence?

                 

                Thanks

                Srini

                • 5. Re: Exception when executing certain queries
                  Steven Hawkins Master

                  Is it typed as string or clob?  Under most circumstances we'll limit string length to 4000 characters and only inline clob value with about the same number.  Have you adjusted the maxStringLength setting?  Or are you for example reading large values directly from your source?

                   

                  > Perhaps even 256 is too much. If we set this to a smaller number, how does it impact a client that explicitly sets a higher fetch size?  Which setting takes precedence?

                   

                  Changing the processor batch size affects all of server processing, so generally you change the value based upon how much memory is available to your Teiid instance and not for specific data issues - as setting too small of a value increases processing overhead.  The client's fetch size is effectively limited (less so with Teiid 8.5) by the processor batch size.

                   

                  If you want these values to remain in memory, then you would likely want to do a combination of reducing the processor batch size, increasing the client maxObjectSize, and setting a smaller fetch size specifically for this query.

                   

                  If you just need these as clob values and are ok with streaming their contents to the client on demand, then I'll see if there is a built-in path that would use are lob buffering instead.

                  • 6. Re: Exception when executing certain queries
                    Amit Jahagirdar Newbie

                    Hi,

                    We are planning to take following approach instead of changing the maxObjectSize or maxStringLength values.

                    Would be great to have feedback/suggestions for this:

                    For now using JDBC client only :

                    1) To write a custom translator and put it as part of  the delegate chain in the vdb.

                    2) Override the next() method to handle this LOB.

                    When this LOB is encountered create a BlobImpl and pass to it a custom implementation of inputStreamfactory which implements Datasource( JAF ).

                    3) The custom inputStreamfactory would in turn have a callback implementation.

                         BlobImpl b = new BlobImpl( MyInputStreamFactory ( MyDatabaseCallBackImpl()) ).;==> The value of the LOB column is not part of the resultset.

                    4) The MyDatabaseCallBackImpl implements Callback .Callback interface has a method to hook into say getStream() which returns an Input Stream.

                     

                    5) So now from a JDBC client perspective when resultset.getBlob().getBinaryStream() would be called the callback method would kick in.

                    6) The getStream() method implementation would create a new connection to underlying database and get the value of the column on the fly using some query like select HugeDataColumn From MyTable where Id ="100";

                    Would pass the value as BinaryStream back to the client.

                    7) So this way the LOB would be streamed to the jdbc client on the getBinaryStream() call and the actual value would not be part of the Resultset ...so the resultset would be limited well between the teiid permissible length of maxObjectSize.

                     

                    Thanks

                    Amit

                    • 7. Re: Exception when executing certain queries
                      Steven Hawkins Master

                      This seems reasonable, but I'm a little unsure as to what exactly was happening initially.  Were you directly returning a large string?  If you were returning a lob reference (that was backed by a stream) then you shouldn't have been hitting a memory problem in the first place.

                       

                      I think we should make a small tweak in our handling so that you can directly return an InputStreamFactory and then we'll handle the conversion to Blob - we'll do that now, but only if the InputStreamFactory is also a javax.activation.DataSource

                       

                      Steve

                      • 8. Re: Exception when executing certain queries
                        Anil Allewar Newbie

                        The use case is that the translator creates a virtual column when the resultset is requested. This virtual column is fairly verbose and hence causes the Teiid client to hit the maxObjectSize threshold. If we are now able to provide a handle to this virtual column as a Blob and actually construct the object when the stream is requested on the Blob(using JavaBeans Activation Framework); then we remove the overhead of embedding the column content in the resultset.

                         

                        I didn't understand the point about handling the conversion to Blob; can you please elaborate?

                        • 9. Re: Exception when executing certain queries
                          Steven Hawkins Master

                          > I didn't understand the point about handling the conversion to Blob; can you please elaborate?

                           

                          I just mean that we have a set of conversion logic already as we reconcile the values sent back from the translator to the desired runtime types.  We should be turning any InputStreamFactory instance to a Blob if that is the desired type, not just those that are also DataSource instances.  But given that it's a trivial step of just wrapping with BlobImpl it's not a big deal.

                           

                          Steve

                          • 10. Re: Exception when executing certain queries
                            Amit Jahagirdar Newbie

                            Hi All,

                            Firstly thanks for validating the approach and now we have got it implemented and its working as expected with a small issue as follows:

                            1) I have a CustomInputFactory which extends InputStreamFactory implements DataSource.

                            @Override

                              public InputStream getInputStream() throws IOException {

                              return this.CustomCallBackClass.provideStreamingForVirtualColumn();

                              }

                            Now in the provideStreamingForVirtualColumn() method of the CustomCallBackClass with constructor as CustomCallBackClass (Object connection) I am passing the connection that is passed to the translator,from following code:

                            createResultSetExecution(QueryExpression command, ExecutionContext executionContext,

                              RuntimeMetadata metadata, Object connection) .

                             

                            This connection while accessed in the provideStreamingForVirtualColumn() method shows up as WrappedConnectionJDK6,and its value is null.

                            So getting a NullPointerexception:).

                            Is there a way to pass the actual  javax.resource.cci.Connection ?.

                             

                            Thanks

                            Amit

                            • 11. Re: Exception when executing certain queries
                              Amit Jahagirdar Newbie

                              Hi all,

                              Not a prompt reply is very unusual on Teiid Forum

                              Thinking that I haven't posted enough info: I am actually posting the code snippets for actual implementation.

                              The problem with this code is the  ConnectorWorkItem class closes the connection and we want the old connection/or create a new connection to the same data-source from within the callback implementation.(MySqlStreamBlobCallBackImpl).

                               

                              It would be of great help if you guys could take a look at the snippets and provide us a way out.

                              One thought is to somehow get the jndi-name and get the same connection from the pool.

                              Snippets:

                              =============Custom Translator Code=================================

                              @Override

                                public List<?> next() throws TranslatorException, DataNotAvailableException {

                                     result = this.getResultSetExecution().next();

                                     if (result == null || result.size() <= 0) {

                                          return result;

                                     }

                                     List<Object> vals = new ArrayList<Object>(result.size());

                                     final Select projectedQuery = command.getProjectedQuery();

                                     final List<DerivedColumn> derivedColumns = projectedQuery.getDerivedColumns();

                                     for (int j = 0; j < derivedColumns.size(); j++) {

                                          final Expression expression = derivedColumns.get(j).getExpression();   

                                          if (expression.getClass().isAssignableFrom(ColumnReference.class)) {        

                                          if (((ColumnReference) expression).getName().equals("Photo")) {        

                                               String tableName = "Inventory";             

                                               String columnName = "Photo";                  

                                          Object value = new BlobType(new BlobImpl(new VirtualColumnFactory(new MySqlStreamBlobCallBackImpl(   

                                          1, tableName, columnName, this.connection))));        

                                          vals.add(value);

                                     } else {

                                               vals.add(result.get(j));   

                                          }

                                     }

                                }

                              ================================VirtualColumnCallBack ==========================

                              public interface VirtualColumnCallBack {

                                     InputStream provideStreamingForVirtualColumn();

                              }

                              =============================== MySqlStreamBlobCallBackImpl =====================

                              public class MySqlStreamBlobCallBackImpl implements VirtualColumnCallBack {

                                   //Constructor

                                   public MySqlStreamBlobCallBackImpl(int id, String tableName, String columnName, Object connection) {

                                     this.id = id;

                                     this.tableName = tableName;

                                     this.columnName = columnName;

                                     this.connection = connection;

                                     }

                              @Override

                                public InputStream provideStreamingForVirtualColumn() {

                                String sql = "Select " + columnName + " from " + tableName + " where ID =" + id;

                                Connection con = null;

                                try {

                                     con = ((Connection) this.connection);

                                     Statement stmt = con .createStatement();

                                     ResultSet rs = stmt.executeQuery(sql);

                                     while (rs.next()) {

                                     return rs.getBinaryStream(columnName);

                                }catch(){

                                   //close connection;

                              }

                               

                              ===============================VirtualColumnFactory============================

                              public class VirtualColumnFactory extends InputStreamFactory implements DataSource {

                                     private VirtualColumnCallBack streamBlobCallBack;

                                public VirtualColumnFactory(VirtualColumnCallBack userStreamBlobCallBack) {

                                     this.streamBlobCallBack = userStreamBlobCallBack;    

                                     } 

                                @Override

                                public InputStream getInputStream() throws IOException {

                                     return this.streamBlobCallBack.provideStreamingForVirtualColumn();

                                }

                              =============================================================================

                               

                              Thanks

                              Amit

                              • 12. Re: Exception when executing certain queries
                                Amit Jahagirdar Newbie

                                Hi All,

                                Still one thing that is stopping me from achieving what is expected:

                                With all the above code changes things are working fine as expected,except at one place where the teiid engine tries to set the length of the blob from the InputStreamfactory.

                                the getLength() method actually reads the entire stream and then sets the length.

                                Is there a way to get the length without reading the stream or byspass it?

                                 

                                I have overridden the getLength() method to return a +ve non zero number say 999.

                                This is currently working for me but would be great if you could suggest a better way to bypass the reading of stream for getting the length value.

                                 

                                Thanks

                                Amit

                                • 13. Re: Exception when executing certain queries
                                  Steven Hawkins Master

                                  Amit,