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?

Answer 1

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

Answer 2

At the present time, there is a simple way to fix this problem:

1. Go to Tools → MarketPlace and search for "PDI MySQL Plugin"
2. Install it ( this will automatically install the missing driver here: data-integration\plugins\databases\pdi-mysql-plugin\lib )
3. Restart Pentaho
4. Done.

Answer 3

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:

1. Download the MySQL Java Connector / Driver that is compatible with your kettle version
2. Unzip the zip file (in my case it was mysql-connector-java-5.1.31.zip)

3. 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

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

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

Answer 8

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.

Answer 9

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.