Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Table input step : strange error message

  1. #1
    Join Date
    Oct 2011
    Posts
    20

    Default Table input step : strange error message

    Hi all,

    I'm working on PDI 4.2.1 (OS : CentOS 5.4, JAVA : openJDK IceTea6 1.9.8, DB : Mysql 5.1.53) and I've a strange stack message from pan.
    Basicly, I launch a transformation which contains only one step (Table input).
    SQL query as bellow :
    Code:
    SELECT ProjectId,CASE WHEN SUM(ReductionInRegistration) / COUNT(ProjectId) = ReductionInRegistration THEN
        ReductionInRegistration / (DAYOFYEAR(DATE_FORMAT(CONCAT(`Year`, "12", "31"), "%Y-%m-%d"))) ELSE "NOT"
    END AS pi
    FROM (
        SELECT mnmx.*, de.* FROM Dwh_ERIC de
        INNER JOIN (
                    SELECT ProjectId AS pId, MIN(Year) AS mn, MAX(Year) AS mx FROM Dwh_ERIC WHERE ReductionInRegistration is not null
                      GROUP BY ProjectId) mnmx
        ON de.ProjectId = mnmx.pId AND Year Between mnmx.mn + 1 AND mnmx.mx - 1
       ) t
    GROUP BY ProjectId;
    Description of Dwh_ERIC table :
    Code:
    +-----------------------------+------------------+------+-----+---------+----------------+
    | Field                       | Type             | Null | Key | Default | Extra          |
    +-----------------------------+------------------+------+-----+---------+----------------+
    | Id                          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | ProjectId                   | int(10) unsigned | NO   | MUL | NULL    |                |
    | CreditingPeriodId           | int(10) unsigned | YES  |     | NULL    |                |
    | Year                        | int(10) unsigned | NO   |     | NULL    |                |
    | Issued                      | double           | YES  |     | NULL    |                |
    | ReductionInValidation       | double           | YES  |     | NULL    |                |
    | ReductionInRegistration     | double           | YES  |     | NULL    |                |
    | Requested                   | int(10) unsigned | YES  |     | NULL    |                |
    | TotalDays                   | int(10) unsigned | YES  |     | NULL    |                |
    | DaysRequested               | int(10) unsigned | YES  |     | NULL    |                |
    | DaysIssued                  | int(10) unsigned | YES  |     | NULL    |                |
    | PlannedMP                   | double           | YES  |     | NULL    |                |
    | PlannedIP                   | double           | YES  |     | NULL    |                |
    | PlannedPerDay               | double           | YES  |     | NULL    |                |
    | AdjustReductionInValidation | double           | YES  |     | NULL    |                |
    | AdjustReduction             | double           | YES  |     | NULL    |                |
    | IssuedVsPlannedIP           | double           | YES  |     | NULL    |                |
    | IssuedVsRequested           | double           | YES  |     | NULL    |                |
    | TimeProgress                | double           | YES  |     | NULL    |                |
    | CreditsMonitoredProgress    | double           | YES  |     | NULL    |                |
    | CreditsIssuedProgress       | double           | YES  |     | NULL    |                |
    | AddedAt                     | datetime         | NO   |     | NULL    |                |
    | AddedBy                     | varchar(128)     | NO   |     | NULL    |                |
    | UpdatedAt                   | datetime         | YES  |     | NULL    |                |
    | UpdatedBy                   | varchar(128)     | YES  |     | NULL    |                |
    +-----------------------------+------------------+------+-----+---------+----------------+
    And the transformation stop with this java's stack :
    Code:
     ERROR in part: openQuery : get rowinfo
    PlannedPI - Unexpected errorPlannedPI - org.pentaho.di.core.exception.KettleDatabaseException:
    An error occurred executing SQL in part [openQuery : get rowinfo]:
    SELECT ProjectId,
    CASE WHEN SUM(ReductionInRegistration) / COUNT(ProjectId) = ReductionInRegistration THEN
            ReductionInRegistration / (DAYOFYEAR(DATE_FORMAT(CONCAT(`Year`, "12", "31"), "%Y-%m-%d"))) ELSE "NOT"
    END AS pi
    FROM (
    SELECT mnmx.*, de.* FROM Dwh_ERIC de
            INNER JOIN (
            SELECT ProjectId AS pId, MIN(Year) AS mn, MAX(Year) AS mx FROM Dwh_ERIC WHERE ReductionInRegistration is not null
            GROUP BY ProjectId) mnmx ON de.ProjectId = mnmx.pId AND Year Between mnmx.mn + 1 AND mnmx.mx - 1) t
    -- WHERE PlannedPerDay IS NULL
    -- AND projectid = 46988
    GROUP BY ProjectId;
    
    
    Error getting row information from database:
    Streaming result set com.mysql.jdbc.RowDataDynamic@46e585 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
    
    
    
    
            at org.pentaho.di.core.database.Database.openQuery(Database.java:1918)
            at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:223)
            at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:133)
            at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
            at java.lang.Thread.run(Thread.java:636)
    Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    Error getting row information from database:
    Streaming result set com.mysql.jdbc.RowDataDynamic@46e585 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
    
    
            at org.pentaho.di.core.database.Database.getRowInfo(Database.java:2444)
            at org.pentaho.di.core.database.Database.openQuery(Database.java:1905)
            ... 4 more
    Caused by: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@46e585 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
            at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:2735)
            at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1899)
            at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
            at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
            at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
            at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1521)
            at com.mysql.jdbc.ConnectionImpl.getMaxBytesPerChar(ConnectionImpl.java:3003)
            at com.mysql.jdbc.Field.getMaxBytesPerCharacter(Field.java:602)
            at com.mysql.jdbc.ResultSetMetaData.getColumnDisplaySize(ResultSetMetaData.java:213)
            at org.pentaho.di.core.database.Database.getValueFromSQLType(Database.java:2627)
            at org.pentaho.di.core.database.Database.getRowInfo(Database.java:2437)
            ... 5 more
    After many test on the step, I find out that the query can work if I change it by this one :
    Code:
    SELECT ProjectId,
    CASE WHEN SUM(ReductionInRegistration) / COUNT(ProjectId) = ReductionInRegistration THEN
        ReductionInRegistration / (DAYOFYEAR(DATE_FORMAT(CONCAT(`Year`, "12", "31"), "%Y-%m-%d"))) ELSE 0
    END AS pi
    FROM (
        SELECT mnmx.*, de.* FROM Dwh_ERIC de
        INNER JOIN (
                    SELECT ProjectId AS pId, MIN(Year) AS mn, MAX(Year) AS mx FROM Dwh_ERIC WHERE ReductionInRegistration is not null
                      GROUP BY ProjectId) mnmx
        ON de.ProjectId = mnmx.pId AND Year Between mnmx.mn + 1 AND mnmx.mx - 1
       ) t
    GROUP BY ProjectId;
    I only changed "NOT" by 0. In my opinion, the only explanation for this is that
    Code:
    ReductionInRegistration / (DAYOFYEAR(DATE_FORMAT(CONCAT(`Year`, "12", "31"), "%Y-%m-%d")))
    returns a number so pentaho expect that we return a number in the else case too. I've no other explanation .

    Anybody else had this issue ?

  2. #2
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hey there,
    having conditional constructs returning different data types from each branch may confuse the driver, and it may report the data type of a field incorrectly. In your case, you should probably cast both branches to be strings if you want to preserve the "NOT" value. Can't have a SQL result column have different types. So make it a number (as you did) or a string (the other option), but it should be consistent.

    As for the MySQL exception in the log, it obfuscates the underlying error a bit. You may try turning off the streaming result cursor or replacing the mysql driver in libext/JDBC with the older one (from PDI < 4.2.0 GA). The more recent driver shows some issues with certain mysql server versions.

    Cheers
    Slawo

  3. #3
    Join Date
    Oct 2011
    Posts
    20

    Default

    Thank you slawomir !

    Same type for the same column. The error message is not really explicit, why tell us that there is stream problem instead of type problem ? It comes from mysql driver or from pentaho ?
    Thank you again, it was very helpful .
    Last edited by agindre; 12-08-2011 at 04:38 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.