Pentaho Data Integration SQL connection

Question

I am using Pentaho Data Integration and I am trying to connect to my database via MySQL but when I do I get this error       Error connecting to database  devdb2    org pentaho di core exception KettleDatabaseException   Error occured while trying to connect to the database  Exception while loading class org gjt mm mysql Driver   org pentaho di core exception KettleDatabaseException   Error occured while trying to connect to the database  Exception while loading class org gjt mm mysql Driver       at org pentaho di core database Database normalConnect Database java 368      at org pentaho di core database Database connect Database java 317      at org pentaho di core database Database connect Database java 279      at org pentaho di core database Database connect Database java 269      at org pentaho di core database DatabaseFactory getConnectionTestReport DatabaseFactory java 86      at org pentaho di core database DatabaseMeta testConnection DatabaseMeta java 2464      at org pentaho ui database event DataHandler testDatabaseConnection DataHandler java 533      at sun reflect GeneratedMethodAccessor55 invoke Unknown Source      at sun reflect DelegatingMethodAccessorImpl invoke Unknown Source      at java lang reflect Method invoke Unknown Source      at org pentaho ui xul impl AbstractXulDomContainer invoke AbstractXulDomContainer java 329      at org pentaho ui xul impl AbstractXulComponent invoke AbstractXulComponent java 139      at org pentaho ui xul impl AbstractXulComponent invoke AbstractXulComponent java 123      at org pentaho ui xul swt tags SwtButton access 500 SwtButton java 26      at org pentaho ui xul swt tags SwtButton 4 widgetSelected SwtButton java 119      at org eclipse swt widgets TypedListener handleEvent Unknown Source      at org eclipse swt widgets EventTable sendEvent Unknown Source      at org eclipse swt widgets Widget sendEvent Unknown Source      at org eclipse swt widgets Display runDeferredEvents Unknown Source      at org eclipse swt widgets Display readAndDispatch Unknown Source      at org eclipse jface window Window runEventLoop Window java 820      at org eclipse jface window Window open Window java 796      at org pentaho ui xul swt tags SwtDialog show SwtDialog java 378      at org pentaho ui xul swt tags SwtDialog show SwtDialog java 304      at org pentaho di ui core database dialog XulDatabaseDialog open XulDatabaseDialog java 115      at org pentaho di ui core database dialog DatabaseDialog open DatabaseDialog java 62      at org pentaho di ui spoon delegates SpoonDBDelegate newConnection SpoonDBDelegate java 493      at org pentaho di ui spoon delegates SpoonDBDelegate newConnection SpoonDBDelegate java 478      at org pentaho di ui spoon Spoon newConnection Spoon java 7770      at sun reflect NativeMethodAccessorImpl invoke0 Native Method      at sun reflect NativeMethodAccessorImpl invoke Unknown Source      at sun reflect DelegatingMethodAccessorImpl invoke Unknown Source      at java lang reflect Method invoke Unknown Source      at org pentaho ui xul impl AbstractXulDomContainer invoke AbstractXulDomContainer java 329      at org pentaho ui xul impl AbstractXulComponent invoke AbstractXulComponent java 139      at org pentaho ui xul impl AbstractXulComponent invoke AbstractXulComponent java 123      at org pentaho ui xul swt tags SwtMenuitem access 100 SwtMenuitem java 27      at org pentaho ui xul swt tags SwtMenuitem 1 widgetSelected SwtMenuitem java 77      at org eclipse swt widgets TypedListener handleEvent Unknown Source      at org eclipse swt widgets EventTable sendEvent Unknown Source      at org eclipse swt widgets Widget sendEvent Unknown Source      at org eclipse swt widgets Display runDeferredEvents Unknown Source      at org eclipse swt widgets Display readAndDispatch Unknown Source      at org pentaho di ui spoon Spoon readAndDispatch Spoon java 1183      at org pentaho di ui spoon Spoon start Spoon java 6966      at org pentaho di ui spoon Spoon main Spoon java 567      at sun reflect NativeMethodAccessorImpl invoke0 Native Method      at sun reflect NativeMethodAccessorImpl invoke Unknown Source      at sun reflect DelegatingMethodAccessorImpl invoke Unknown Source      at java lang reflect Method invoke Unknown Source      at org pentaho commons launcher Launcher main Launcher java 134  Caused by  org pentaho di core exception KettleDatabaseException   Exception while loading class org gjt mm mysql Driver      at org pentaho di core database Database connectUsingClass Database java 423      at org pentaho di core database Database normalConnect Database java 352          50 more Caused by  java lang ClassNotFoundException  org gjt mm mysql Driver     at java net URLClassLoader 1 run Unknown Source      at java security AccessController doPrivileged Native Method      at java net URLClassLoader findClass Unknown Source      at java lang ClassLoader loadClass Unknown Source      at java lang ClassLoader loadClass Unknown Source      at java lang Class forName0 Native Method      at java lang Class forName Unknown Source      at org pentaho di core database Database connectUsingClass Database java 414          51 more   If I used SQLite as my Connection Type it works but no data is returned when I goto explore it  So my question is how do I get MySQL working or get the data using SQLite   Am I missing a library or a class

User · Accepted Answer

Turns out I will missing a class called mysql-connector-java-5 1 2 jar  I added it this folder  C  Program Files pentaho design-tools data-integration lib  and it worked with a MySQL connection and my data and tables appear

User · Answer

At the present time  there is a simple way to fix this problem    Go to Tools   MarketPlace and search for  PDI MySQL Plugin  Install it   this will automatically install the missing driver here  data-integration plugins databases pdi-mysql-plugin lib   Restart Pentaho Done

User · Answer

Missing driver file   This error is really common for people just getting started with PDI   Drivers go in  pentaho design-tools data-integration libext JDBC for PDI   If you are using other tools in the Pentaho suite  you may need to copy drivers to additional locations for those tools   For reference  here are the appropriate folders for some of the other design tools    Aggregation Designer   pentaho design-tools aggregation-designer drivers Metadata Editor    pentaho design-tools metadata-editor libext JDBC Report Designer    pentaho design-tools report-designer lib jdbc Schema Workbench    pentaho design-tools schema-workbench drivers   If this transformation or job will run on another box  such as a test or production server  don t forget to include copying the jar file and restarting PDI or the Data Integration Server in your deployment considerations

User · Answer

In addition to the other answers here  here s how you can do it on Ubuntu  14 04    sudo apt-get install libmysql-java  this will download mysql-connector-java-5 x x jar to  usr share java   which i believe also automatically creates a symlink named mysql-connector-java jar    Then  create a symlink in  your path to data-integration lib    ln -s  usr share java mysql-connector-java jar  your path to data-integration lib mysql-connector-java jar

User · Answer

I just came across the same issue while trying to query a MySQL Database from Pentaho      Error connecting to database  Local MySQL DB    org pentaho di core exception KettleDatabaseException     Error occured while trying to connect to the database      Exception while loading class   org gjt mm mysql Driver   Expanding post by  user979331 the solution is    Download the MySQL Java Connector   Driver that is compatible with your kettle version  Unzip the zip file  in my case it was mysql-connector-java-5 1 31 zip  copy the  jar file  mysql-connector-java-5 1 31-bin jar  and paste it in your Lib folder   PC  C  Program Files pentaho design-tools data-integration lib  Mac   Applications data-integration lib   Restart Pentaho  Data Integration  and re-test the MySQL Connection    Additional interesting replies from others that could also help    think to download the good JDBC driver version  compatible with your PDI version  https   help pentaho com Documentation 8 1 Setup JDBC Drivers Reference MY SQL take the zip version   platform independant   to extract the jar file take into lib folder see also proper way to handle it proposed by Ryan Tuck

User · Answer

You need to download mysql-connector-java-5 1 46 tar gz  not the latest version   The Driver class that Pentaho uses is not included in mysql-connector-java-8 xx yy versions

User · Answer

Above answers were helpful  but for unknown reasons they did not seem to work   So if you have already installed MySql workbench on your system  instead of downloading the jar files and struggling with the correct version just go to   C  Program Files  x86  MySQL Connector J 8 0   and copy mysql-connector-java-8 0 12  does not matter what version it is  the jar file in that location and paste it to C  Program Files pentaho design-tools data-integration lib

User · Answer

To be concise and precise download the compatible jdbc   jar  file compatible with your MySql version and put it in lib folder  For example for MySQL 8 0 2 download Connector J 8 0 20

User · Answer

First of all you need to download Mysql connector which is compatible with your pentaho version after that paste it to data-integration lib folder and restart your pentaho  check this  https   help pentaho com Documentation 8 1 Setup JDBC Drivers Reference MY SQL

[mysql] Pentaho Data Integration SQL connection

Examples related to mysql

Examples related to pentaho