Saturday 28 August 2010

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 :-)

4 comments:

Andrew Frayling said...

Glad you're having fun with MySQL, but you shouldn't have to become root at the OS level to access. You should be able to use:

mysql -u root -p

as any logged in user.

Dave Hay said...

Cheers, Andrew, that worked a treat, have updated the post to reflect your insight :-)

Andrew Frayling said...

Cool, glad that worked. Another tip if you don't want your MySQL databases to be accessed as root by your application is to run the following SQL statement after logging in to MySQL:

grant all on [dbname].* to '[user]'@'localhost' identified by '[password]';

then:

flush privileges;

After that you'll be able to access with:

mysql -u [user] -p [password]

You've helped me a lot with WebSphere so thought I'd return the favour.

Dave Hay said...

Cheers again, Andrew, much obliged.

Visual Studio Code - Wow 🙀

Why did I not know that I can merely hit [cmd] [p]  to bring up a search box allowing me to search my project e.g. a repo cloned from GitHub...