Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Basic instructions for setting up/running mySQL

 
Joyce Lee
Ranch Hand
Posts: 1392
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've encountered a few problems while using the mysql. Thanks God, the problems have been resolved.

Here is a summary of I've done to make it works. Btw, my system is win98 and I'm using tomcat 4.1 as the web container.


To install mysql database server
================================
* Go to http://dev.mysql.com/downloads/

* Download "MySQL 4.0 -- Production release (recommended)"

* Unzip the mysql-4.0.20a-win.zip file in a temporary directory

* Run setup.exe

* Update the PATH for "c:\mysql\bin" in the autoexec.bat (win98) so that you can execute the mysql*** commands anywhere you like.

* Now you should be able to start the mysql server, launch the mysql prompt and execute sql commands.


To start/shutdown the server
============================
* To start the server, open a dos window and run the following command:
c:\>mysqld
Note: the default port for running this server is 3306 and you can check the active ports using "netstat -na".

* To shutdown the server:
c:\>mysqladmin -u root shutdown


To start/exit mysql prompt
==========================
* Start mysql prompt using user "root":
c:\>mysql -u root
Note: To add a new user, go to http://dev.mysql.com/doc/mysql/en/Adding_users.html for instructions.

* Exit mysql prompt:
mysql> quit


Basic sql commands
==================
Notes: The sql commands are case-insensitive.

* To display all databases:
mysql> show databases;

* To create a new database:
mysql> create database soup;

* To use a specific database:
mysql> use soup;

* To create a table "videos" (in database soup):
mysql> CREATE TABLE VIDEOS ( TITLE VARCHAR(80) NOT NULL, STAR VARCHAR(40) NULL, TYPE VARCHAR(15) NULL, VHS CHAR(1) NULL, DVD CHAR(1) NULL, DESCRIPTION VARCHAR(200) NULL);

* To show the table soup's contents:
mysql> select * from videos;

* To insert a new row to the table soup:
mysql> INSERT INTO VIDEOS ( TITLE , STAR , TYPE , VHS , DVD , DESCRIPTION ) VALUES ( 'The Gods Must Be Crazy' , 'a coke bottle' , 'comedy' , 'Y' , 'N' , 'A bushman is introduced to civilization by a coke bottle.' );


To setup the mysql-connector
============================
* Go to http://dev.mysql.com/downloads/

* Download MySQL Connector/J 3.0 -- production release

* Unzip the mysql-connector-java-3.0.14-production.zip file in a temporary directory

* Create a new directory"c:\mysql-connector". Copy the file mysql-connector-java-3.0.14-production-bin.jar under the directory mysql-connector-java 3.0.14-production into this new directory. Rename the file mysql-connector-java-3.0.14-production-bin.jar to a shorter name "mysql-connector.jar". Renaming the jar file is just an extra step cos I feel that jar file name is too long.

* Update the CLASSPATH for "c:\mysql-connector\mysql-connector.jar" in the autoexec.bat (win98)

* Copy "mysql-connector.jar" into c:\tomcat\lib.

* Now you can run the Test.java and TestServlet.java code.

Note: You can place the "mysql-connector.jar" anywhere you like as long as you set the CLASSPATH for it. Also, don't place the "mysql-connector.jar" under c:\java\jre\lib\ext, it gave me a lot of headaches by throwing java.lang.NoClassDefFoundError. If you have any idea of what causing this error, please share with us. And if you encountered ClassNotFoundException, that's because the CLASSPATH for the mysql-connector jar was not set properly.


To run the Cattle Drive codes Test.java and TestServlet.java
============================================================
* You can find the Test.java and TestServlet.java at Javaranch Cattle Drive - JDBC.

* Modify the following line:

To


* Before you execute the code, make sure the mysql server is running.

* Note that you need javaRanchCommon.jar for LogServlet and CastMap classes in TestServlet.java. You can get the javaRanchCommon.jar at JavaRanch Cattle Drive - Servlets. If you do not wish touch on javaRanchCommon.jar at this point, modify the following code:

To


Joyce
[ September 22, 2004: Message edited by: Joyce Lee ]
 
Pauline McNamara
Sheriff
Posts: 4012
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey, thanks Joyce.
 
Joyce Lee
Ranch Hand
Posts: 1392
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Note: You can place the "mysql-connector.jar" anywhere you like as long as you set the CLASSPATH for it. Also, don't place the "mysql-connector.jar" under c:\java\jre\lib\ext, it gave me a lot of headaches by throwing java.lang.NoClassDefFoundError.

Here is an ARTICLE that explains why strange thing happens if you placed *.jar under java extension directory.

Joyce
 
Terry Broman
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Joyce!!

I was getting access denied on Windows XP Professional until I read this from you:

To start/exit mysql prompt
==========================
* Start mysql prompt using user "root":
c:\>mysql -u root
 
Joyce Lee
Ranch Hand
Posts: 1392
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Glad that it's useful.

Joyce
 
Carol Murphy
village idiot
Bartender
Posts: 1202
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joyce, that's a bit different from my setup. I'm using Windows 98 and the orion server and it seems a bit less complicated to me, but maybe it's a case of the devil you know being better than the devil you don't know........
Thanks for the research!
 
Joyce Lee
Ranch Hand
Posts: 1392
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Carol, I was not familiar with mySQL, just reading enough info to run the server But I did spend some time debugging the Class exception. Initially, I placed the *.jar file under java extension directory because I didn't want to modify the CLASSPATH. Unfortunately, it has a side-effect with the Class.forName() method.

If anyone of you have other approach of setting up the mySQL, please do share with us.

Joyce
[ June 09, 2004: Message edited by: Joyce Lee ]
 
Marilyn de Queiroz
Sheriff
Posts: 9063
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I downloaded the
Windows 95/98/NT/2000/XP/2003 (x86) 4.0.20a Without installer (unzip in C:\)
version from http://dev.mysql.com/downloads/mysql/4.0.html
I unzipped it, typed mysqld at the command line in the mysql directory, and it started.

However, I appreciate knowing that they updated their web site and I will adjust the directions on the assignment page accordingly.

Regarding Tomcat, one reason we picked and still use orion for this course is its ease of use. It is much, much simpler to set up and use than Tomcat is.
 
Joyce Lee
Ranch Hand
Posts: 1392
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, Marilyn. You're right. To start the server, simply type "mysqld" instead of "mysqld-nt --standalone".

I didn't use Orion because Tomcat was already installed on my system. Will try the Orion someday.

Joyce
 
Kate Head
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi there,
Just as an addition to your brilliant work on how to get MySQL working, if you want to add -user and -password to the MySQL login/connection in a single command then it is as follows:

where you had the connection, user and password separately
Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/soup" , "root" , null );

you can enter them like this
Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/soup?user=root&password=null" );
or like this
Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/soup?user=root&null" );
or like this
Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/soup?user=root" );

If you're going to use the web.xml <context-parameter> tags in orion\default-web-app\WEB-INF then this is the one that works (null at the end confuses it!)


I have only been able to get it to work with the javax.servlet.* classes rather than com.javaranch.common.* ones - you use the following to access the parameters


When I wrote my techvalley.co.uk/khead project I used MySQL on my PC, and Oracle on the server, so had to keep switching the drivers, but using a different web.xml file on my PC and the server was a lot easier than recompiling each time!
The Oracle command format is of course different from the MySQL one!


where tcpipaddress was the server tcp/ip address e.g. 127.0.0.1 and the servername was the server name e.g. localhost

Many thanks for all your hard work - hope this will be of interest to you.
All the best,
Kate!!
[ August 06, 2004: Message edited by: Kate Head ]
 
Joyce Lee
Ranch Hand
Posts: 1392
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kate, thanks a lot for the info. Definitely helpful!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic