operationBinding.skipRowCount caused tableClause to be ignored?

[leeyuiwah]

This thread is related to the other one but I give it a separate thread to make the question more apparent.

http://forums.smartclient.com/showthread.php?t=33046

I have a data source with multiple operationBinding's. It has been working well until I add one more operationBinding with skipRowCount set to true.

<DataSource
    ID="rs358223955"
    serverType="sql"
    progressiveLoading="false"
    progressiveLoadingThreshold="-1"
    tableName="env"
>
    <fields>
        ...
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" operationId="RsUseQueryNonProgressive"  
                progressiveLoading="false"  >
            <selectClause><![CDATA[
                  env.timeDate as timeDate
                , env.app as app
                , env.totalBytesRatioInSysTotal as totalBytesRatioInSysTotal
                , env.downBytesRatioInSysTotal as downBytesRatioInSysTotal
                , env.pktLossRate as pktLossRate
                , env.downThruput as downThruput
                , env.satDownThruput as satDownThruput
            ]]></selectClause>
            <tableClause>nara_db.rs358223955 as env
            </tableClause>
        </operationBinding>
        <operationBinding operationType="fetch" operationId="RsUseQuerySkipRowCount"  
                progressiveLoading="false"  skipRowCount="true"  >
            <selectClause><![CDATA[
                  env.timeDate as timeDate
                , env.app as app
                , env.totalBytesRatioInSysTotal as totalBytesRatioInSysTotal
                , env.downBytesRatioInSysTotal as downBytesRatioInSysTotal
                , env.pktLossRate as pktLossRate
                , env.downThruput as downThruput
                , env.satDownThruput as satDownThruput
            ]]></selectClause>
            <tableClause>nara_db.rs358223955 as env
            </tableClause>
        </operationBinding>
        ...
    </operationBindings>
</DataSource>

The first operationBinding (with Id="RsUseQueryNonProgressive") worked perfectly, but the second operationBinding (with Id="RsUseQuerySkipRowCount") did not work.

First of all, I noticed that even I had specified skipRowCount="true", I still saw that SmartGWT issued a counting query.

And then the query also ignored the tableClause -- it should have say "from nara_db.rs358223955 as env" but it didn't -- it just said "from env", which is wrong and caused an SQL error of "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'dw_db.env' doesn't exist"

Below is the debug log I obtained.

=== 2015-06-17 17:50:33,757 [6-35] INFO  SQLDataSource - Performing fetch operation with
        criteria: {}    values: {}
=== 2015-06-17 17:50:33,757 [6-35] INFO  SQLWhereClause - empty condition
=== 2015-06-17 17:50:33,757 [6-35] INFO  SQLDataSource - derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause ORDER BY $defaultOrderClause
=== 2015-06-17 17:50:33,758 [6-35] DEBUG SQLDataSource - Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2015-06-17 17:50:33,758 [6-35] DEBUG SQLDataSource - Eval'd row count query: SELECT COUNT(*) FROM env WHERE ('1'='1')
=== 2015-06-17 17:50:33,758 [6-35] DEBUG SQLDriver - About to execute SQL query in 'central_aware_db_4100' using connection '1259753404'
=== 2015-06-17 17:50:33,758 [6-35] INFO  SQLDriver - Executing SQL query on 'central_aware_db_4100': SELECT COUNT(*) FROM env WHERE ('1'='1')
=== 2015-06-17 17:50:33,763 [6-35] INFO  SQLDriver - Execute of select: SELECT COUNT(*) FROM env WHERE ('1'='1') on db: central_aware_db_4100 threw exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'dw_db.env' doesn't exist - assuming stale connection and retrying query.

To show why the above was wrong, I am also showing the debug log for the other operationBinding that worked well. For this one, the equivalent debug log was:

=== 2015-06-17 17:47:45,303 [6-35] DEBUG SQLDataSource - Executing row count query: SELECT COUNT(*) FROM nara_db.rs358223955 as env
             WHERE $defaultWhereClause
=== 2015-06-17 17:47:45,303 [6-35] DEBUG SQLDataSource - Eval'd row count query: SELECT COUNT(*) FROM nara_db.rs358223955 as env
             WHERE ('1'='1')
=== 2015-06-17 17:47:45,303 [6-35] DEBUG PoolableSQLConnectionFactory - Executing pingTest 'select 1 from dual' on connection 1259753404
=== 2015-06-17 17:47:45,326 [6-35] DEBUG SQLConnectionManager - Borrowed connection '1259753404'
=== 2015-06-17 17:47:45,326 [6-35] DEBUG SQLDriver - About to execute SQL query in 'central_aware_db_4100' using connection '1259753404'
=== 2015-06-17 17:47:45,326 [6-35] INFO  SQLDriver - Executing SQL query on 'central_aware_db_4100': SELECT COUNT(*) FROM nara_db.rs358223955 as env
             WHERE ('1'='1')

Our environment:

1. SmartClient Version: v10.0p_2015-02-03/PowerEdition Deployment (built 2015-02-03)

2. Browser: (not applicable)

3. Server log: (not applicable)

[Isomorphic]

Just a note to say we have a developer assigned to this and will follow up when we have more information for you.

[leeyuiwah]

Thanks. We need this to be fixed because we need skipRowCount to work to address some of the server reliability problems when the data sets are huge.

[Isomorphic]

First issue when skipRowCount="true" setting could be ignored is fixed. You may download next nightly build (2015-06-23) and try it out - row count query should not be performed if configured to be skipped.
NOTE that this may not affect your issue if the assumption about miss typed operationId is true (see below).

Second issue with ignored tableClause - we couldn't reproduce that. It seems like you could have miss typed operationId in dsRequest addressing operationBinding with id="RsUseQuerySkipRowCount". In that case operationConfig would not be picked up at all, which would result in using default tableName in FROM statement, declared in DataSource.tableName.
Could you please check if this is the case and inform us?

[leeyuiwah]

Indeed second issue was a mistake on my side (I loaded a previously generated XML string which did not include the correct operationId). Sorry for the confusion.

But for the first issue, I still saw the row-count query being issued. I was using yesterday's build 5.0-p20150623. On your other post, you suggested to use 5.0-p20150624 instead. I am going to try that and I will report back. Thanks!

[leeyuiwah]

Hi,

Unfortunately I tried both the build this morning and yesterday and I found that the code is still issuing the row-counting query, with the unnecessary WHERE ('1' = '1') clause.

This was what I saw from the log

=== 2015-06-24 19:52:18,260 [4-38] DEBUG SQLDataSource - Executing row count query: SELECT COUNT(*) FROM nara_db.rs373666409 as env
             WHERE $defaultWhereClause
=== 2015-06-24 19:52:18,260 [4-38] DEBUG SQLDataSource - Eval'd row count query: SELECT COUNT(*) FROM nara_db.rs373666409 as env
             WHERE ('1'='1')
=== 2015-06-24 19:52:18,260 [4-38] DEBUG SQLDriver - About to execute SQL query in 'central_aware_db_4100' using connection '1905775291'
=== 2015-06-24 19:52:18,260 [4-38] INFO  SQLDriver - Executing SQL query on 'central_aware_db_4100': SELECT COUNT(*) FROM nara_db.rs373666409 as env
             WHERE ('1'='1')

I also checked my version. I am already using the latest build

SmartClient Version: v10.0p_2015-06-24/PowerEdition Deployment (built 2015-06-24)

I am including my operationBinding below. Can you check if I am setting skipRowCount correctly?

By the way, my system does not load the XML from a file. Instead, it generates the string on the fly, and then uses DataSource.fromXML() to create the DataSource object. Would this causes a different execution path that could lead to the problem?

<operationBinding operationType="fetch" operationId="RsUseQuerySkipRowCount"  
        progressiveLoading="false"  skipRowCount="true"  >
    <selectClause><![CDATA[
          env.timeMinute as timeMinute
        , env.cell as cell
        , env.totalBytes as totalBytes
    ]]></selectClause>
    <tableClause>nara_db.rs373666409 as env
    </tableClause>
</operationBinding>

[Isomorphic]

Well, we could not reproduce this. The bug we found is already fixed. Could you please do the following to help us identify the problem:

- first, just in case if the fix was somehow not included in the builds you tried, it would be great if you try to reproduce the issue with the newest (2015-06-26) build? It does not contain any new fixes, it's just to be 100% sure that you are experience same issue against the build that has the fix.

- second, the 2015-06-27 build (NOTE it is 06-27!) will have some additional logging info that could help clarify what's going on. You should enabled logging category "com.isomorphic.sql.SQLDataSource.temp DEBUG":

<category name="com.isomorphic.sql.SQLDataSource.temp">
  <priority value="DEBUG" />
</category>

and reproduce the issue with this additional logging - see below log messages that should appear:

=== 2015-06-27 00:21:18,161 [11-3] DEBUG temp - [builtinApplication._nonProgressive] operationBinding: {
    operationId:"_nonProgressive",
    operationType:"fetch",
    progressiveLoading:false,
    selectClause:"\n                  env.id as id,\n                  env.name as name,\n                  env.population as population,\n                  env.countryId as countryId\n            ",
    tableClause:"indirectIncFromCity as env\n            "
}
=== 2015-06-27 00:21:18,161 [11-3] DEBUG temp - [builtinApplication._nonProgressive] skipRowCount: false
=== 2015-06-27 00:21:18,161 [11-3] DEBUG temp - [builtinApplication._nonProgressive] progressiveLoading: false
=== 2015-06-27 00:21:18,161 [11-3] DEBUG temp - [builtinApplication._nonProgressive] Row count query will be used.

Note, this is just a fragment to help you identify that additional logging is working. You should send us complete log starting at the beginning of dsRequest processing, not just query count execution fragment.

- finally, could you please isolate the issue and reproduce it in standalone use case? Try to keep it as simple as possible. If the issue will be there it will be easier to get to the bottom of the problem. It is also better to post complete ds.xml definition and, as it was said earlier, you should send us more complete log fragments starting at the beginning of dsRequest processing, not just count query execution.

Thank you for you help

[Isomorphic]

another shot on our fix not working - could it be libs issue? Are you sure your application uses libraries exactly from the build you intend to use? May be your environment appears to have multiple versions of Smartclient server libs? Also, just FYI the fix is located in isomorphic_sql.jar. Could you please check if this is the case?

[leeyuiwah]

Hi,

I made a new build with 5.0-p20150628 and this time I did not see the counting queries any more.

Thank you very much!