Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Cannot execute 'ANALYZE TABLE' by using step 'Execute SQL statements' in PDI 4.4.0

  1. #1
    Join Date
    May 2013
    Posts
    16

    Default Cannot execute 'ANALYZE TABLE' by using step 'Execute SQL statements' in PDI 4.4.0

    Problem resolved by upgrading mysql jdbc driver from 5.0.8 to 5.1.14.

    Hi all,

    I need some helps. I met a problem when I tried to use step 'Execute SQL statements' to execute a statement 'ANALYZE TABLE table_name ;' against a MySQL server in PDI 4.4.0.

    I don't know is it a bug or my mistake? However, I can execute the same step in PDI 4.0.1.

    The following is the transformation.
    Name:  1trans.png
Views: 31
Size:  13.8 KB

    The following is the detail of this step.
    Name:  2step.jpg
Views: 37
Size:  26.9 KB

    The following is the configure of the databasewarehouse connection.
    Name:  3dbconnection.jpg
Views: 34
Size:  24.7 KB

    The following is the error message when I execute the transformation by Kitchen.
    Code:
    INFO  03-06 12:18:30,659 - Analyze tables - Dispatching started for transformation [ESP Analyze Tables Transformation]
    ERROR 03-06 12:18:30,771 - Analyze tables - An error occurred, processing will be stopped: 
    Couldn't execute SQL: ANALYZE TABLE esp_address
    
    java.lang.NullPointerException
    
    ERROR 03-06 12:18:30,772 - Analyze tables - Error initializing step [Analyze tables]
    ERROR 03-06 12:18:30,772 - Analyze tables - Step [Analyze tables.0] failed to initialize!
    INFO  03-06 12:18:30,772 - Analyze tables - Finished reading query, closing connection.
    ERROR 03-06 12:18:30,772 - Analyze tables - Unable to prepare for execution of the transformation
    ERROR 03-06 12:18:30,772 - Analyze tables - org.pentaho.di.core.exception.KettleException: 
    We failed to initialize at least one step.  Execution can not begin!
    
    
        at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:932)
        at org.pentaho.di.trans.Trans.execute(Trans.java:504)
        at org.pentaho.di.job.entries.trans.JobEntryTrans.execute(JobEntryTrans.java:1034)
        at org.pentaho.di.job.Job.execute(Job.java:589)
        at org.pentaho.di.job.Job.execute(Job.java:728)
        at org.pentaho.di.job.Job.execute(Job.java:495)
        at org.pentaho.di.job.entries.job.JobEntryJobRunner.run(JobEntryJobRunner.java:68)
        at java.lang.Thread.run(Thread.java:662)
    The following is the error message when I execute the transformation in Spoon.
    Code:
    2014/06/03 12:38:35 - ESP Analyze Tables Transformation -  Transformation is about to allocate step [Analyze tables] of type [ExecSQL]
    2014/06/03 12:38:35 - ESP Analyze Tables Transformation -   Step has nrcopies=1
    2014/06/03 12:38:35 - ESP Analyze Tables Transformation -  Transformation has allocated a new step: [Analyze tables].0
    2014/06/03 12:38:35 - ESP Analyze Tables Transformation - This transformation can be replayed with replay date: 2014/06/03 12:38:35
    2014/06/03 12:38:35 - ESP Analyze Tables Transformation - Initialising 1 steps...
    2014/06/03 12:38:36 - ESP Analyze Tables Transformation - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Step [Analyze tables.0] failed to initialize!
    
    
        at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:930)
        at org.pentaho.di.ui.spoon.trans.TransGraph.debug(TransGraph.java:3444)
        at org.pentaho.di.ui.spoon.delegates.SpoonTransformationDelegate.executeTransformation(SpoonTransformationDelegate.java:920)
        at org.pentaho.di.ui.spoon.Spoon$31$1.run(Spoon.java:7619)
        at org.eclipse.swt.widgets.RunnableLock.run(Unknown Source)
        at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Unknown Source)
        at org.eclipse.swt.widgets.Display.runAsyncMessages(Unknown Source)
        at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
        at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1221)
        at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7044)
        at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:8304)
        at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:580)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.pentaho.commons.launcher.Launcher.main(Launcher.java:134)
    I am pretty confused. Could you kindly help me?

    Thank you.
    Last edited by AlbertWang; 06-03-2014 at 06:10 PM.

  2. #2
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi Albert,

    I don't think you can have just exec SQL step. You need a starting point. Try adding in a Generate rows - set Limit to 1 and have nothing else. Connect to the exec SQL.

    HTH
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  3. #3
    Join Date
    May 2013
    Posts
    16

    Default

    Hi Tnewman,

    Thank you for your reply. I tried your suggestion but still have the same error.

    The following is the new transformation.
    Name:  4.png
Views: 28
Size:  17.3 KB

    The following is the detail of Generate Rows.
    Name:  5.jpg
Views: 30
Size:  13.6 KB

    Thank you.

    Regards,
    Albert

  4. #4
    Join Date
    May 2013
    Posts
    16

    Default

    Hi Tnewman,

    Problem resolved. The reason is improper MySQL Jdbc driver. I used 5.0.8. After I upgrade to 5.1.14, the error disappeared.

    Thank you.

    Regards,
    Albert

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.