Databases

Contents:

Information on connecting to different databases with Pentaho.

See the Pentaho Special database issues and experiences wiki page for some tips on what to look out for when using different databases with Pentaho.

JDBC drivers

See our JDBC drivers page.

MySQL

See also: Using My SQL

The Pentaho BI server can be downloaded from Sourceforge in a version that uses MySQL as its backend. For production purposes, this is vastly preferable to using the HSQL version (which really only exists for testing/evaluation).

Using Pentaho to connect to a MySQL database is quite well documented elsewhere.

PostgreSQL

Using Pentaho to connect to a PostgreSQL is quite well documented elsewhere.

We have instructions for employing PostgreSQL more directly as the backend of Pentaho BI Server 1.x.

Other resources:

Microsoft SQL Server and Sybase

Jboss and Pentaho use and recommend jTDS as a connector to Microsoft SQL server (6.5, 7, 2000 and 2005) and Sybase (10, 11, 12, 15). The FAQ answers many questions that you may have, including database connections strings. Keep the results sets types in mind when connecting.

One thing to make sure of is whether the database is accepting connections over TCP/IP.

The Spoon 3 Users' Guide covers MS SQL Server briefly on page 33.

More help at the Pentaho Wiki.

Testing database connections

If you're having trouble establishing a connection to a database, answer these questions:

  • are the appropriate JDBC drivers installed?
  • check your JNDI URL and authentication (username and password)
  • does the network allow you to connect to the database port?
  • are the database permissions/grants allowing the connection?
You can use telnet to check if the database is available on the address and port you'd expect:

$ telnet <addr> <port>

Where <addr> is the address of the host you're connecting to, and <port> is the port to connect to. It is better to use an IP address instead of a host name to avoid name resolution (DNS or hosts file) issues.

Here's a set of default ports for different databases:

  • MySQL - 3306
  • PostgreSQL - 5432
  • Oracle (thin) - 1521
  • Microsoft SQL Server - 1433
If you can't connect with telnet, you won't be able to with anything else. Check the connectivity of the database host, including the database's TCP/IP configuration and its settings for allowing connections.

Here are some resources to find the connection settings for your database:

I still can't connect (MySQL example)

If a telnet to the address does not connect, you should check the database itself.

Here's an example for MySQL. This type of problem is seen commonly on (but is not exclusive to) Debian GNU/Linux and derivatives such as Ubuntu.

Assume you're running MySQL on your local machine but are having trouble connecting to it on localhost (127.0.0.1). Your LAN IP address is 10.8.8.123.

  1. Verify that MySQL is running.
    $ ps aux|grep mysql
root     30561  0.0  0.1   1772   524 pts/0    S    12:27   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    30601  0.1  3.2 127224 16488 pts/0    Sl   12:27   0:07 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
root     30602  0.0  0.1   2920   688 pts/0    S    12:27   0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
user     30707  0.0  0.1   3012   772 pts/0    S+   13:37   0:00 grep mysql
  2. On the machine running the database, check what address(es) MySQL is listening on.
    $ netstat -tan|grep 3306
tcp        0      0 10.8.8.123:3306       0.0.0.0:*               LISTEN
    In this configuration, you will have to connect to 10.8.8.123. Notice how there's no mention of 127.0.0.1 or 0.0.0.0, so even if you're running MySQL on localhost you'll still have to connect to 10.8.8.123.
  3. Telnet to the address(es).
    $ telnet 10.8.8.123 3306
Trying 10.8.8.123...
Connected to 10.8.8.123.
Escape character is '^]'
    A connection was accepted — MySQL is listening.
    $ telnet localhost 3306
Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused
    MySQL won't work on this address. This verifies what we saw in the previous step.
How do we deal with this? You can change your connection settings to point to 10.8.8.123 instead of localhost or 127.0.0.1. Alternatively, you can reconfigure MySQL to listen on localhost. To do this:

  1. Open your my.cnf file (in /etc/ or /etc/mysql/) in a text editor.
  2. Look for the line defining the 'bind-address' parameter.
    bind-address            = 10.8.8.123
  3. You can set this to 127.0.0.1 if you're only intending to connect locally. Otherwise, you can just disable/delete that line. This is less secure, as it allows connections from any connected network. Use with care on an Internet-facing computer. Fedora/RHEL/CentOS are configured this way, but SELinux handles the security aspects.
Resources:
Creator: Sridhar Dhanapalan on 2008/05/28 23:19
XWiki Enterprise 1.7.2.16857 - Documentation