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?

nelsonic · Answer 1 · 2020-01-10T17:08:33.033

139

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

edited Jan 10 '20 at 17:08

answered Oct 11 '12 at 10:32

nelsonic

25,667
19
83
109

does you know what the corresponding folder would be for the BI-server? – decal Jun 11 '13 at 22:31
1

Dude, you are great!! – Stormsson Sep 17 '13 at 14:09
4

i pasted the jar file latest connection but error still persists – noobie-php Oct 29 '13 at 14:42
@noobie-php Can you connect to MySQL from the command line or through another admin interface? – nelsonic Nov 02 '13 at 17:51
Working gr8 from spoon , but issue on scheduling the job . Cannot find the dirver . i have included the driver in *tomcat\webapps\pentaho\WEB-INF\lib* but still .... Any idea where mysql driver is missing ..? – Surya Apr 07 '14 at 06:11
1

@Surya no, sorry that sounds like it might be different issue. you could try posting a new question here on StackOverflow with a bit more detail (your precise configuration, version of Spoon etc) so the developers can trouble-shoot the issue. – nelsonic Jun 17 '14 at 22:16
Just add this problem on OS X Yosemite. I add to install it in `/Application/Pentaho Report Designer.app/lib` and it worked. – mb14 Feb 05 '15 at 12:02
This works for me. You're indications are better than that of pentaho itself who says the folder is lebext/JDBC/ and not lib/: http://infocenter.pentaho.com/help48/index.jsp?topic=%2Fadmin_guide%2Fconcept_adding_a_jdbc_driver.html – jgomo3 Feb 11 '16 at 22:53
1

you saved my day dude, great answer – jemsnaban Mar 31 '16 at 07:42
6

Currently latest version doesn't have *-bin.jar hence refer to the previous version [here](https://dev.mysql.com/downloads/connector/j/5.1.html). – Nae Jun 29 '18 at 09:26
@Nae link solves the problem with the newest version of Mysql connector. Tks! – jfneis Sep 17 '18 at 20:31
1

...it's year 2020 and this is still current – 6opko Jan 07 '20 at 15:59
Saved me some time. Thx!! – thatsallfolks Dec 09 '20 at 23:05

score 14 · Answer 2 · answered Sep 18 '12 at 15:02

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.

score 12 · Accepted Answer · answered Jul 24 '12 at 16:07

12

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.

answered Jul 24 '12 at 16:07

user979331

10,209
56
186
339

Working gr8 from spoon , but issue on scheduling the job .Cannot find the dirver . i have included the driver in tomcat\webapps\pentaho\WEB-INF\lib but still .... Any idea in which location mysql driver is missing ..? – Surya Apr 07 '14 at 06:13
I ended up removing the version from the file name (not sure if that helped) then had to set up a _Generic Connection_ following this answer: https://stackoverflow.com/a/52163066/349659 – CTS_AE Aug 22 '19 at 00:39
That's it. I get the jar file and solved my problem. Thank you – thatsallfolks Dec 09 '20 at 23:07

score 9 · Answer 4 · answered Jul 21 '18 at 16:09

9

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.

answered Jul 21 '18 at 16:09

Dean Schulze

7,882
18
77
125

1

Here is where you can find all the previous versions: http://ftp.iij.ad.jp/pub/db/mysql/Downloads/Connector-J/ – Talha Imam Oct 18 '18 at 12:03

score 7 · Answer 5 · answered Sep 30 '15 at 19:14

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

score 3 · Answer 6 · answered May 05 '20 at 18:47

3

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.

answered May 05 '20 at 18:47

h3t1

976
2
13
23

1

For recent versions of Pentaho (PDI), this is the only step you need to take, it installs all necessary drivers and works fine after a reboot – Efrain Plaza Oct 08 '20 at 22:36

score 0 · Answer 7 · answered Mar 05 '19 at 12:44

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

Primit · Answer 8 · 2019-08-08T10:51:36.080

0

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

edited Aug 08 '19 at 10:51

answered Aug 05 '19 at 06:11

Primit

685
5
12

score 0 · Answer 9 · answered Jul 10 '20 at 19:42

0

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

answered Jul 10 '20 at 19:42

Hamed Sanaei

71
9

Pentaho Data Integration SQL connection

9 Answers9

Linked