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

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