MySQL on Linux with WebSphere Application Server - My First Adventure

Having worked with enterprise databases like DB2 UDB, Oracle and SQL Server, I thought I'd give MySQL a try.

Specifically, I wanted to run it on my native Linux 10.04 desktop, and then use the DB from within WebSphere Application Server.

Installing it was absolutely simple - I fired up Synaptic Package Manager, search for mysql and select to install one single package - mysql-server - and let Synaptic take care of the dependancies. After a fairly brief download/installation, I was asked to set the MySQL password ( in the DB2 world, this would be the instance owner's password ) using the following command: -

sudo dpkg-reconfigure mysql-server-5.1

This command can be re-run at any time, as/if required.

For the record, these are the packages that I installed: -

root@dmhw500:~# sudo dpkg --list | grep -i mysql

ii  libdbd-mysql-perl                                        4.012-1ubuntu1                                  A Perl5 database interface to the MySQL databa

ii  libmysql-java                                            5.1.10+dfsg-2ubuntu1                            Java database (JDBC) driver for MySQL

ii  libmysqlclient16                                         5.1.41-3ubuntu12.6                              MySQL database client library

ii  mysql-client-5.1                                         5.1.41-3ubuntu12.6                              MySQL database client binaries

ii  mysql-client-core-5.1                                    5.1.41-3ubuntu12.6                              MySQL database core client binaries

ii  mysql-common                                             5.1.41-3ubuntu12.6                              MySQL database common files (e.g. /etc/mysql/m

ii  mysql-server                                             5.1.41-3ubuntu12.6                              MySQL database server (metapackage depending o

ii  mysql-server-5.1                                         5.1.41-3ubuntu12.6                              MySQL database server binaries

ii  mysql-server-core-5.1                                    5.1.41-3ubuntu12.6                              MySQL database core server files

* The JDBC driver isn't part of the core package, but I needed it for WebSphere.

Having installed it, it took me 5 minutes to realise that I can't use it without being a root user: -

hayd@dmhw500:~$ mysql -p
Enter password:
ERROR 1045 (28000): Access denied for user 'hayd'@'localhost' (using password: YES)

whereas once I "became" root: -

hayd@dmhw500:~$ sudo bash
[sudo] password for hayd:
root@dmhw500:~# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.1.41-3ubuntu12.6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

I was in like Flynn.

*UPDATE*

That nice Mr Andrew Frayling pointed out that I could easily overcome this issue by, as my normal non-root user, run the command: -

hayd@dmhw500:~$ mysql -u root -p

*UPDATE*


The next step was to create a database: -

mysql> create database loanrequest;
Query OK, 1 row affected (0.00 sec)

and then make that my current database ( equivalent of DB2 CONNECT TO LOANREQUEST ): -

mysql> use loanrequest;
Database changed

and then create a table: -

mysql> create table loanrequests(name varchar(30) not null,custnum varchar(10) not null primary key,amount float not null);
Query OK, 0 rows affected (0.11 sec)

and then look at the table just created: -

mysql> describe loanrequests;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(30) | NO   |     | NULL    |       |
| custnum | varchar(10) | NO   | PRI | NULL    |       |
| amount  | float       | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

and then populate the table: -

mysql> insert into loanrequests values('Dave Hay','006734',12345.67);
Query OK, 1 row affected (0.00 sec)

mysql> insert into loanrequests values('Homer Simpson','123456',456.78);
Query OK, 1 row affected (0.00 sec)

mysql> insert into loanrequests value('Marge Simpson','661222',123.42);
Query OK, 1 row affected (0.00 sec)

mysql> insert into loanrequests value('Lisa Simpson','123123',21323.23);
Query OK, 1 row affected (0.00 sec)

mysql> insert into loanrequests value('Bart Simpson','43215',6651.21);
Query OK, 1 row affected (0.00 sec)

and then query the table: -

mysql> select * from loanrequests;
+---------------+---------+---------+
| name          | custnum | amount  |
+---------------+---------+---------+
| Dave Hay      | 006734  | 12345.7 |
| Homer Simpson | 123456  |  456.78 |
| Marge Simpson | 661222  |  123.42 |
| Lisa Simpson  | 123123  | 21323.2 |
| Bart Simpson  | 43215   | 6651.21 |
+---------------+---------+---------+
5 rows in set (0.00 sec)

mysql> quit

So far, so good.

I then wanted to access the database from WebSphere Application Server - I'm using 6.1.0.29.

This is a relatively simple matter that's covered elsewhere, so I'll merely provide the key points: -

a) Copy the JDBC driver to the WebSphere lib/ext directory

cp /usr/share/java/mysql-connector-java-5.1.10.jar /opt/IBM/WebSphere/AppServer/lib/ext

b) Log into the WAS admin console ( Integrated Solutions Console ) ->  https://dmhw500.uk.ibm.com:10041/ibm/console/login.do?action=secure

c) Navigate to Resources -> JDBC -> JDBC Providers

d) Create a new provider: -

    Name:                      MySQL
    Class path:                /opt/IBM/WebSphere/AppServer/lib/ext/mysql-connector-java-5.1.10.jar
    Implementation class name: com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource

e) Create a new datasource: -

    Name:                                     loanRequest
    JNDI name:                                jdbc/loanRequest

f) Create the following three custom properties ( you may have a large number created via the WAS template; I chose to delete them all and merely add these three ): -

    serverName:                               dmhw500
    databaseName:                             loanrequest
    port:                                     3306

g) Create a JAAS - J2C authentication data alias ( keeps credentials separate from your data sources ): -

     User ID:                                 root
     Password:                                passw0rd

Having saved the configuration, and restarted WebSphere, I was able to successfully test the connection, which returned the message: -

The test connection operation for data source loanRequest on server WebSphere_Portal at node dmhw500 was successful with 1 warning(s)

For the record, the warning, as seen in SystemOut.log, is: -

[28/08/10 17:03:52:613 BST] 00000036 DSConfigurati W   DSRA0174W: Warning: GenericDataStoreHelper is being used.

Obvious really :-)

For me, the next step was to then use WebSphere Portlet Factory Designer to access the newly created datasource, and add some create/read/update/delete methods to my database from a portlet ... which is nice :-)