Connection in Pentaho with PostgreSQL

Question

I have the driver postgresql-9.3-1103.jdbc3.jar in folder ...data-integration\lib and classpah is configured too

I have the postgresql 9.3 Installed and i have the db prueba in postgresql but this is the error

Error connecting to database [PostgresDB] : org.pentaho.di.core.exception.KettleDatabaseException: 
Error occurred while trying to connect to the database

Error connecting to database: (using class org.postgresql.Driver)
Invalid connection URL url jdbc:postgresql://localhost:5432/prueba

org.pentaho.di.core.exception.KettleDatabaseException: 
Error occurred while trying to connect to the database

Error connecting to database: (using class org.postgresql.Driver)
Invalid connection URL url jdbc:postgresql://localhost:5432/prueba

at org.pentaho.di.core.database.Database.normalConnect(Database.java:428)
at org.pentaho.di.core.database.Database.connect(Database.java:358)
at org.pentaho.di.core.database.Database.connect(Database.java:311)
at org.pentaho.di.core.database.Database.connect(Database.java:301)
at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)
at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2686)
at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:546)
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:313)
at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)
at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)
at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:43)
at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:138)
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:389)
at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:318)
at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:116)
at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:59)
at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.editConnection(SpoonDBDelegate.java:87)
at org.pentaho.di.ui.spoon.Spoon.doubleClickedInTree(Spoon.java:3084)
at org.pentaho.di.ui.spoon.Spoon.doubleClickedInTree(Spoon.java:3019)
at org.pentaho.di.ui.spoon.Spoon.access$2400(Spoon.java:345)
at org.pentaho.di.ui.spoon.Spoon$27.widgetDefaultSelected(Spoon.java:6113)
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:1319)
at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7939)
at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9190)
at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:654)
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:92)

Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
Error connecting to database: (using class org.postgresql.Driver)
Invalid connection URL url jdbc:postgresql://localhost:5432/prueba

at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:594)
at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:4697)
at org.pentaho.di.core.database.Database.normalConnect(Database.java:414)
... 45 more

Caused by: java.lang.IllegalArgumentException: Invalid connection URL url jdbc:postgresql://localhost:5432/prueba
at org.mariadb.jdbc.JDBCUrl.parse(JDBCUrl.java:144)
at org.mariadb.jdbc.Driver.connect(Driver.java:95)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:574)
... 47 more

Hostname : localhost Port : 5432 Database name : prueba

Answer 1

In JDBC, the DriverManager will query all registered drivers to see if they can use the URL to connect. If a driver doesn't support the provided URL (eg because the driver queried is for Firebird, and the URL is for PostgreSQL), the Driver.connect implementation should return null:

The driver should return "null" if it realizes it is the wrong kind of driver to connect to the given URL. This will be common, as when the JDBC driver manager is asked to connect to a given URL it passes the URL to each loaded driver in turn.

Long story short, if you look at the stacktrace you will see at the bottom:

Caused by: java.lang.IllegalArgumentException: Invalid connection URL url jdbc:postgresql://localhost:5432/prueba
at org.mariadb.jdbc.JDBCUrl.parse(JDBCUrl.java:144)
at org.mariadb.jdbc.Driver.connect(Driver.java:95)
at java.sql.DriverManager.getConnection(Unknown Source)

In other words, the org.mariadb.jdbc.Driver is misbehaving and throwing a java.lang.IllegalArgumentException instead of returning null as it should. This causes the DriverManager to stop trying other drivers, and as a result it never even tries to use the PostgreSQL driver.

Possible solutions or workarounds are:

• Remove the MariaDB driver from the classpath of Pentaho
• Check if there is a newer version of the MariaDB driver that has this bug fixed
• Check if there is an older version of the MariaDB driver that doesn't have this bug

A quick search shows this is bug CONJ-167 which should be fixed in the MariaDB Connector/J 1.2.2, which hasn't been released yet.