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 · Accepted Answer · 2020-01-10 17:08:33Z

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

does you know what the corresponding folder would be for the BI-server? — decal, Commented Jun 11, 2013 at 22:31
Currently latest version doesn't have *-bin.jar hence refer to the previous version here. — Nae, Commented Jun 29, 2018 at 9:26

FremenFreedom · Accepted Answer · 2012-09-18 15:02:02Z

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.

user979331 · Accepted Answer · 2012-07-24 16:07:43Z

13

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, 2012 at 16:07

user979331

11.5k80 gold badges243 silver badges438 bronze badges

1

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
Commented Apr 7, 2014 at 6:13
1

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: stackoverflow.com/a/52163066/349659
– CTS_AE
Commented Aug 22, 2019 at 0:39
1

That's it. I get the jar file and solved my problem. Thank you
– no-stale-reads
Commented Dec 9, 2020 at 23:07

Add a comment |

Dean Schulze · Accepted Answer · 2018-07-21 16:09:40Z

10

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, 2018 at 16:09

Dean Schulze

10k25 gold badges110 silver badges177 bronze badges

1

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

Add a comment |

ryantuck · Accepted Answer · 2015-09-30 19:14:32Z

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

h3t1 · Accepted Answer · 2020-05-05 18:47:51Z

4

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 5, 2020 at 18:47

h3t1

1,2082 gold badges19 silver badges30 bronze badges

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
Commented Oct 8, 2020 at 22:36

Add a comment |

Hamed Sanaei · Accepted Answer · 2020-07-10 19:42:39Z

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

answered Jul 10, 2020 at 19:42

Hamed Sanaei

1311 silver badge13 bronze badges

Add a comment |

Shubham Chopra · Accepted Answer · 2019-03-05 12:44:13Z

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 · Accepted Answer · 2019-08-08 10:51:36Z

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 8, 2019 at 10:51

answered Aug 5, 2019 at 6:11

Primit

8557 silver badges13 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Pentaho Data Integration SQL connection

9 Answers 9

Not the answer you're looking for? Browse other questions tagged
mysql
pentaho
or ask your own question.

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

Not the answer you're looking for? Browse other questions tagged mysqlpentaho or ask your own question.

Linked

Related

Not the answer you're looking for? Browse other questions tagged
mysql
pentaho
or ask your own question.