Top
首页 > 老文章 > 正文

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

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

Setting up your Windows computer to run JPetStore 3.x with MySQL and Tomcat
发布时间:2006-03-09 02:33        来源:        作者:

Revision History

Version 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

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

  2. Tomcat
    1. Download and install the latest version of Tomcat from http://jakarta.apache.org/site/binindex.cgi.
      1. I install all my Apache apps to "c:\program files\apache" to keep it simple.
      2. Just keep all the other defaults the same, and input your preferred admin password when prompted.
    2. Start Tomcat. You can use either the Windows Service or the Start Menu shortcut, "Start->Programs->Apache Tomcat 5.0->Start Tomcat".
    3. Verify that Tomcat is working correctly.
    4. Goto http://localhost:8080. If you see a Tomcat base page you're good.
    5. 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." :)

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

  4. MySQL
    1. Download MySQL 4.0.14b, unzip, and run Setup.
    2. Goto your default MySQL directory (C:\mysql for me) and copy my-medium.cnf to my.cnf. Now move my.cnf to C:\.
    3. 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.
    4. Start the MySQL Service.
      Win2000:
      Go to Start->Control Panel->Administrative Tools->Services.
      Double-click the MySQL service
      click the Start button.
    5. Set Root MySQL password.     *** Very Important Step ***
      1. Goto "C:\mysql\bin".
      2. Run "mysqladmin -uroot password NEW-PASS" where NEW-PASS is your password of choice.

        Note:Don't type the quotes...

    6. Verify MySQL is running correctly.
      1. Run "mysql -uroot -p". You will be prompted for a password. Just enter in whatever you typed for NEW-PASS.
      2. You should see a "mysql>" prompt. If not, check Google for whatever error code you received.
      3. Run "show databases;".

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

      4. If you don't see,
        +----------+
        | Database |
        +----------+
        | mysql    |
        | test     |
        +----------+
        2 rows in set (0.02 sec)
        	
        then you know something is wrong...try Google.

  5. MySQL JDBC Driver
    1. Download the MySQL JDBC Driver, mysql-connector-java-3.0.9-stable.
    2. Unzip the driver. Copy the file, "mysql-connector-java-3.0.9-stable-bin.jar" to Tomcat's common/lib directory.

  6. JPetStore - Database
    1. 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
    2. Make sure you logged out of mysql, jump back to your shell prompt, and execute the following commands.
      1. "mysql -uroot -p < jpetstore-mysql-schema.sql"   OR  "mysql -uroot -p < jpetstore-mysql-schema-innodb.sql" for transaction support.
      2. "mysql -uroot -p < jpetstore-mysql-dataload.sql"
      3. "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.
    3. If you don't see any errors, it worked correctly.

  7. JPetStore - Deploy
    1. Find jpetstore.war in the build/wars folder of wherever you unzipped the JPetStore download to.
    2. 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.
    3. Don't try to access (run) the app yet.

  8. JPetStore - Driver Setup
    1. Find /webapps/jpetstore/WEB-INF/classes/properties/petstore.properties from your Tomcat directory. (c:\program files\apache\tomcat 5.0, for me)
    2. 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
      	
    3. Save it!

  9. Test
    1. Shutdown and restart your Tomcat Web Server by either stopping the Service or using the System Tray icon.
    2. Once Tomcat has restarted, click here (link will open a new window): http://localhost:8080/jpetstore .
    3. 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 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

    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.


  • 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 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 let me know.
加载更多

专题访谈

合作站点
stat