aspose file tools*
The moose likes Cattle Drive and the fly likes Basic instructions for setting up/running mySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » This Site » Cattle Drive
Bookmark "Basic instructions for setting up/running mySQL" Watch "Basic instructions for setting up/running mySQL" New topic
Author

Basic instructions for setting up/running mySQL

Joyce Lee
Ranch Hand

Joined: Jul 11, 2003
Posts: 1392
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

Joined: Jan 19, 2001
Posts: 4012
    
    6
Hey, thanks Joyce.
Joyce Lee
Ranch Hand

Joined: Jul 11, 2003
Posts: 1392
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

Joined: Jun 18, 2003
Posts: 15
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

Joined: Jul 11, 2003
Posts: 1392
Glad that it's useful.

Joyce
Carol Murphy
village idiot
Bartender

Joined: Mar 15, 2001
Posts: 1197
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

Joined: Jul 11, 2003
Posts: 1392
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

Joined: Jul 22, 2000
Posts: 9052
    
  12
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.


JavaBeginnersFaq
"Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
Joyce Lee
Ranch Hand

Joined: Jul 11, 2003
Posts: 1392
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

Joined: Oct 31, 2002
Posts: 67
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

Joined: Jul 11, 2003
Posts: 1392
Kate, thanks a lot for the info. Definitely helpful!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Basic instructions for setting up/running mySQL