Setting up your Windows computer to run JPetStore 3.x with MySQL and Tomcat

Table of Contents

Revision HistoryVersion 2.2.2 - 02/13/04

Added new troubleshooting ticket on setting up transactions on Red Hat 9.0.

Added new comment on setting up transactions on Red Hat 9.0. Version 2.2.1 - 01/27/04

Updated JPetStore sql scripts to use a lowercase jpetstore database name for better compatibility with *nix systems.

Added Christoph's comment on installing JPetStore on Linux.

Added comment on installing JPetStore on Solaris. Version 2.2 - 01/22/04

Added 'USE JPETSTORE;' line to top of 'jpetstore-mysql-dataload.sql' file as Chris Smith recommends.

Added link to 'jpetstore-mysql-schema-innodb.sql' file Chris sent me.

Added a comments section where helpful correspondence will go. Version 2.1 - 01/11/04

Shifted hosting to my personal website.

Added links to sql scripts and made minor changes to JPetStore - Database. Version 2 - 12/30/03

Changed tutorial to HTML format.

Included Linux-Friendly MySQL scripts (thanks to Maria Tzvetanova). Version 1 - 10/13/03

Original Writing

Motivation

I made this walkthrough in the hopes of saving someone some time when installing Clinton Begin's excellent JPetStore application.

This guide will walk you through the steps involved in setting up a Windows system to run JPetstore. It does not assume any prior installation/configuration of dependent software.

Linux / Solaris 9 Note:

The steps in this walkthrough are generally the same for a Linux or Solaris 9 installation.

George let me know how he got JPetStore to work on Linux and Christoph let me know how for Solaris 9.

Maria Tzvetanova pointed out that the lowercase table names in the mysql setup scripts In Version 1 caused errors when used on Linux systems. This is because (unlike Windows) MySQL on Linux is case-sensitive. She has supplied updated mysql scripts with uppercase table names which are included in Version 2.

Special Note: Transactions

The MySQL scripts that come with this walkthrough set up tables of the default (ISAM) type. These tables do NOT support transactions but are very fast. The MySQL driver is smart enough to act as if transactions occur so that transaction-aware software (like JPetStore) will still run fine. But in reality, each SQL command is a seperate "transaction".

If you need transaction support, use the MySQL script that comes with the JPetStore distribution (in the ddl/mysql directory). That file uses INNODB tables which do support transactions.

You will still need to set up a user so you can still use the jpetstore-mysql-create-user.sql script.

Note: Check out Chris Smith's helpful comment regarding the use of INNODB tables.

Chris sent me a modified version of the INNODB schema script that comes with JPetStore which supports re-running the script to reset the database to the starting state. See the above comment.

Walkthrough

Java

Download Java SDK 1.4.2_01 (had trouble using the NetBeans bundle so I recommend using the regular SDK package).

Tomcat

Download and install the latest version of Tomcat from http://jakarta.apache.org/site/binindex.cgi.

I install all my Apache apps to "c:\program files\apache" to keep it simple.

Just keep all the other defaults the same, and input your preferred admin password when prompted.

Start Tomcat. You can use either the Windows Service or the Start Menu shortcut, "Start->Programs->Apache Tomcat 5.0->Start Tomcat".

Verify that Tomcat is working correctly.

Goto http://localhost:8080. If you see a Tomcat base page you're good.

Goto http://localhost:8080/jsp-examples and try out a couple of the examples to confirm that Tomcat is working properly.

Note: I had trouble getting the example, http://localhost:8080/jsp-examples/jsp2/misc/config.jsp to work. To fix this exception I followed the on-screen instructions and copied "$JAVA_HOME/lib/tools.jar from the JDK to the common/lib directory of the Tomcat server, followed by a Tomcat restart." :)

JPetStore - Download

Download and unzip the latest version of JPetStore to a temp directory.

MySQL

Download MySQL 4.0.14b, unzip, and run Setup.

Goto your default MySQL directory (C:\mysql for me) and copy my-medium.cnf to my.cnf. Now move my.cnf to C:\.

Open a command prompt (Dos prompt). Now goto, "C:\mysql\bin" and run, "mysqld-nt -install". This will install the MySQL daemon (server) as a Windows Service that will start automatically when Windows starts. You can verify this by going to Start->Control Panel->Administrative Tools->Services under Win2000.

Start the MySQL Service.

Win2000:

Go to Start->Control Panel->Administrative Tools->Services.

Double-click the MySQL service

click the Start button.

Set Root MySQL password. *** Very Important Step ***

Goto "C:\mysql\bin".

Run "mysqladmin -uroot password NEW-PASS" where NEW-PASS is your password of choice.

Note:Don't type the quotes...

Verify MySQL is running correctly.

Run "mysql -uroot -p". You will be prompted for a password. Just enter in whatever you typed for NEW-PASS.

You should see a "mysql>" prompt. If not, check Google for whatever error code you received.

Run "show databases;".

Note: Don't forget the ';'. If you ever see a prompt with just a " ->" then you know you forgot the ';'.

If you don't see, +----------+

| Database |

+----------+

| mysql |

| test |

+----------+

2 rows in set (0.02 sec)

then you know something is wrong...try Google.

MySQL JDBC Driver

Download the MySQL JDBC Driver, mysql-connector-java-3.0.9-stable.

Unzip the driver. Copy the file, "mysql-connector-java-3.0.9-stable-bin.jar" to Tomcat's common/lib directory.

JPetStore - Database

Now download these files to your computer:

jpetstore-mysql-schema.sql

or jpetstore-mysql-schema-innodb.sql if you want transaction support (see Chris's comment).

jpetstore-mysql-dataload.sql

jpetstore-mysql-create-user.sql

Make sure you logged out of mysql, jump back to your shell prompt, and execute the following commands.

"mysql -uroot -p < jpetstore-mysql-schema.sql" OR "mysql -uroot -p < jpetstore-mysql-schema-innodb.sql" for transaction support.

"mysql -uroot -p < jpetstore-mysql-dataload.sql"

"mysql -uroot -p < jpetstore-mysql-create-user.sql" Note: If you get an error like this: "mysql: command not found" that means you don't have the mysql program in your path. Just copy the '*.sql' files to your mysql/bin folder and run the commands from there.

If you don't see any errors, it worked correctly.

JPetStore - Deploy

Find jpetstore.war in the build/wars folder of wherever you unzipped the JPetStore download to.

Copy jpetstore.war to your Tomcat/webapps folder. For me this was at "C:\Program Files\Apache\Tomcat 5.0\webapps". Tomcat will automatically deploy your app if you have it started. If it's not started just follow the instructions in step 2.

Don't try to access (run) the app yet.

JPetStore - Driver Setup

Find /webapps/jpetstore/WEB-INF/classes/properties/petstore.properties from your Tomcat directory. (c:\program files\apache\tomcat 5.0, for me)

Change the default (oracle) values to match the following: SimpleDriver=org.gjt.mm.mysql.Driver

SimpleUrl=jdbc:mysql://localhost:3306/jpetstore

SimpleUsername=jpetstore

SimplePassword=ibatis9977

Save it!

Test

Shutdown and restart your Tomcat Web Server by either stopping the Service or using the System Tray icon.

Once Tomcat has restarted, click here (link will open a new window): http://localhost:8080/jpetstore .

Have fun!

Troubleshooting

I figured I'd try to do things wrong and see what errors I got in case it happens to one of you. :)

No action instance for path /shop/index could be created Message: HTTP Status 500 - No action instance for path /shop/index could be

created

Description: The server encountered an internal error (No action instance

for path /shop/index could be created) that prevented it from fulfilling

this request.

I got this error by deleting the JDBC driver from Tomcat's common/lib directory. If you see this error, follow the directions in Step 5, and then restart the Tomcat server.

Note: The "No action instance for path ..." error will occur ANY time Tomcat can not instantiate an action (usually because it can't find a required library / jar file) so there are many different reasons why you might be getting this error.

In general, check the log files in %CATALINA_HOME%/logs and see if you can find what's causeing the error.

Access Denied / Error getting transaction Unhandled Exception: com.ibatis.db.dao.DaoException: Error getting transaction.

Cause: java.sql.SQLException: Invalid authorization specification, message from

server: "Access denied for user: 'jpetstore@localhost' (Using password: YES)"

<---- Caused by: java.sql.SQLException: Invalid authorization specification,

message from server: "Access denied for user: 'jpetstore@localhost' (Using

password: YES)" ---->

I got this error by deleting the jpetstore@localhost user from MySQL. See Step 6.

Note: While porting the iBATIS JPetStore to use MySQL I had a big problem where I got this error even when I had clearly created a 'jpetstore' user. After trying a few things out, I found out you have to make the user as 'jpetstore@localhost' rather than 'jpetstore' in order for this to work. If anybody knows why this occurs, please let me know.

Note: Several people have emailed me with helpful comments regarding this error. See Chris's comment and Marc's comment below.

Can't get Transactions to work on Red Hat 9.0! / Edited my.cnf - Now Mysql doesn't start! 040213 22:34:42 mysqld ended

040213 22:34:48 mysqld started

InnoDB: Error: log file /var/lib/mysql/ib_logfile0

is of different size 0 5242880 bytes

InnoDB: than specified in the .cnf file 0 67108864 bytes!

040213 22:34:48 Can't init databases

040213 22:34:48 Aborting

For a more thorough look at this problem see my comment below.

I recently set up INNODB transactions on Red Hat 9.0 and had problems. My Red Hat 9.0 came with mysql installed. I thought transactions were working but they weren't. I found there was no my.cnf file (usually at /etc/my.cnf).

I copied my-large.cnf to /etc/my.cnf and changed it to fit my system as per the

InnoDB Reference Manual

Then I tried restarting mysql...no luck. Nothing happened.

Found the above error in the innodb error log file.

Solution

This only works if you don't care about your innodb transaction backups! Although to get this error, InnoDB probably wasn't working in the first place.

Just move those log files to ib_logfile0.bak, ib_logfile1.bak, etc. and start mysql again.

Consequences

This solution is not elegant but it works. At least it worked for me because I didn't have any real data in the innodb tables anyways. INNODB stores all transactions in it's log files so by moving those, I probably erased all the backups of my transactions. If you have data you need to save, you'll have to get help from someone wiser than me. ;)

Comments

One of the best parts about sharing online is getting great feedback. I've included what I felt to be the most useful / most frequently asked comments from the community.

My personal site / Tutorial Requests - by Adrian Lanning If anyone has any tutorial requests or would like to collaborate with me

on writing tutorials / walkthroughs for other apps ?Subject=Tutorial%20Collaboration"email me and let's talk.

Curious about me? Here's a link to my personal website

hosted by the great people at SDF.

Note: SDF is a non-profit corp. offering a website, mail, and unix shell access for a $36 one-time fee! (And no I'm not getting paid to say that.)

I've been a member for about a year now and a friend of mine has been for over 10 years.

Linux - Transactions / INNODB / my.cnf - by Adrian Lanning

Chris writes in his comment below: Transactions:

If you are going to use the INNODB table structures, I think the INNODB

entries in my.cnf will also need to be uncommented and set to point to

existing paths.

I recently set up INNODB transactions on Red Hat 9.0 and had problems. I'd like to share their solutions here.

Ok, my Red Hat 9.0 came with mysql installed. The regular INNODB files were also in the mysql data directory: ibdata1

ib_logfile0

ib_logfile1

...

So I thought transactions might be working. I tried my app out and found that although I had set the table type to INNODB, transactions were, in fact, NOT working.

Then I remembered Chris's comment and checked the my.cnf file. On Linux it is usually located at: /etc/my.cnf

I checked but it wasn't there. So I searched the whole computer: find / -name my.cnf -print

Still no luck.

Ok, so I'll make my own.

I copied my-large.cnf to /etc/my.cnf and changed a few settings to fit my system as per the InnoDB Reference Manual.

Then I started up mysql again using the Red Hat service script... service mysql start

Nothing happened.

Now on Red Hat 9.0, the mysql service script (located at /etc/rc.d/init.d/mysql) uses safe_mysqld. safe_mysqld will not output any error messages to the console, they all get logged. So checking the SERVERNAME.err log file (where SERVERNAME is your server's name), I found this error message: 040213 22:34:42 mysqld ended

040213 22:34:48 mysqld started

InnoDB: Error: log file /var/lib/mysql/ib_logfile0

is of different size 0 5242880 bytes

InnoDB: than specified in the .cnf file 0 67108864 bytes!

040213 22:34:48 Can't init databases

040213 22:34:48 Aborting

So now I know what the problem is...

The solution?

Well I just moved those log files to ib_logfile0.bak, ib_logfile1.bak, etc. and started mysql again.

Consequences

Transactions / INNODB / my.cnf - by Chris Smith

Transactions:

If you are going to use the INNODB table structures, I think the INNODB

entries in my.cnf will also need to be uncommented and set to point to

existing paths.

...

I've included a db create script that I've used for MySQL and JPetstore

that uses INNODB tables where the tables may be used in transactions,

otherwise MYISAM tables are used. The script started out as the one

included in the jpetstore distribution but modified to enable re-running

without any errors - i.e. tables always dropped and recreated.

jpetstore-mysql-schema-innodb.sql

---------------

Chris Smith

Pegasus Consultants (South West) Ltd

http://pegasus-sw.com

Note: I use INNODB tables and upon checking my my.cnf file, I found that I did not have the INNODB sections uncommented. Transactions appear to be working for me so those lines may just override some default settings in MySQL.

In any case, I'm gonna be safe and follow Chris's advice.

Create User / GRANT usage - by Chris Smith

Creation of the jpetstore user.

MySQL provides for the same user from different hosts being assigned

different privileges. This is done through the granting of rights, the

MySQL manual gives all the necessary details.

http://www.mysql.com/doc/en/GRANT.html

The following extract of this page details the usage:

In order to accommodate granting rights to users from arbitrary hosts, MySQL

supports specifying the user_name value in the form user@host. If you want

to specify a user string containing special characters (such as `-'), or a

host string containing special characters or wildcard characters (such as

`%'), you can quote the username or hostname (for example,

'test-user'@'test-hostname').

You can specify wildcards in the hostname. For example, user@'%.loc.gov'

applies to user for any host in the loc.gov domain, and user@'144.155.166.%'

applies to user for any host in the 144.155.166 class C subnet.

The simple form user is a synonym for user@"%".

If you want a user jpetstore to have access to the database from any host,

just define it with a wildcard to specify the host (

mysql> grant select, insert, update, delete on jpetstore.* to jpetstore@"%"

identified by 'password';

)

To verify from which hosts have access to your database from the jpetstore

user:

mysql> use mysql;

mysql> select user, host from user where user= 'jpetstore';

+-----------+------+

| user | host |

+-----------+------+

| jpetstore | % |

+-----------+------+

---------------

Chris Smith

Pegasus Consultants (South West) Ltd

http://pegasus-sw.com

Access Denied / GRANT usage - by Marc Mulzer

A very common error is to insert a new entry with Host='%' and User='some

user', thinking that this will allow you to specify localhost to connect

from the same machine. The reason that this doesn't work is that the default

privileges include an entry with Host='localhost' and User=''. Because that

entry has a Host value 'localhost' that is more specific than '%', it is

used in preference to the new entry when connecting from localhost! The

correct procedure is to insert a second entry with Host='localhost' and

User='some_user', or to remove the entry with Host='localhost' and User=''.

http://www.mysql.com/doc/en/Access_denied.html

I prefer to grant my users access on a table-level, which does not seem to

have this issue, because there are no default privileges defined on the

table space itself.

So, next time try something like

GRANT SELECT, INSERT, UPDATE, DELETE ON somedatabase.sometable TO someuser

IDENTIFIED BY 'somepass';

You should see that the access is actually stored in the TABLES_PRIV table

when you grant on a table level. With no hostname specified, mysql assumes

'localhost' and if Tomcat is running on the same system you should have no

problems.

Works much better for me than granting on a database level, is more granular

and therefore more secure also.

Sidenote: Remember that when you mess with the access-level tables directly,

you have to issue a 'mysqladmin flush-privileges' to activate the permissions.

So, always stick with GRANT and REVOKE.

Give it a shot and let me know if it worked for you!

---------------

Marc Mulzer

Team Lead

Maximus Server Provisioning

DELL Inc.

Linux Install - by George Entenman

Well, I'm happy to say that someone finally sent me instructions on how to install JPetStore for Linux! Thanks George! Thank you for your wonderful guide to setting up jpetstore on windows.

I used it for my linux setup and thought you'd like to know the changes

I had to make to get your setup to work.

First, I had to lowercase the name of the database from "JPETSTORE" to

"jpetstore" in these three files:

jpetstore-mysql-schema-innodb.sql

jpetstore-mysql-dataload.sql

jpetstore-mysql-create-user.sql

Secondly, after creating the user, I had to run:

mysqladmin -u root -p reload

I hope this is of some use to you. Thanks so much for your help.

--ge (George Entenman, Chapel Hill, North Carolina, US)

Note: MySQL database url's on Solaris and Linux are apparently case-sensitive. I would imagine that if you changed the SimpleUrl attribute in petstore.properties file to:

SimpleUrl=jdbc:mysql://localhost:3306/JPETSTORE

you wouldn't have to change the sql files...

If anybody tests this out and it works, please ?subject=JPetStore SimpleURL"let me know.

Solaris Install - by Christoph Kutzinski

Christoph got JPetStore working on Solaris, and let me know how. Thanks Christoph! ...

Now I tried it on Solaris 9 where I got a "table JPETSTORE" not found

found message when running JPetStore.

After changing the JPETSTORE in the SQL-scripts to lowercase it worked.

-Christoph Kutzinski

Note: MySQL database url's on Solaris and Linux are apparently case-sensitive. I would imagine that if you changed the SimpleUrl attribute in petstore.properties file to:

SimpleUrl=jdbc:mysql://localhost:3306/JPETSTORE

you wouldn't have to change the sql files...

If anybody tests this out and it works, please ?subject=JPetStore SimpleURL"let me know.