Pentaho Data Integration SQL connection

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?

Solution 1:

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

Solution 2:

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.

Solution 3:

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.

Solution 4:

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.

Solution 5:

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