Connecting TermWeb to MySQL

These instructions will help you connect TermWeb to a MySQL database.

1. Configuring MySQL

MySQL should allow packet sizes at least the size of maximum file attachment size, by default 15000 KB.

The default max packet size is 1MB, and you can configure this in your MySQL configuration file by setting the property max_allowed_packet. See the MySQL manual for more information.

2. Create database

Create the TermWeb database by opening a DOS prompt and enter the following:
```
mysql -u root -p < createdb.sql
```
This creates an empty database called termweb and a user termwebuser with password termwebpwd. The user name and password can be changed as desired. The user currently needs all permissions (except GRANT) in the database.
Import the termweb.sql file to the database, by running the following at the DOS prompt:
```
mysql -u termwebuser -ptermwebpwd termweb < termweb.sql
```

3. Copy the MySQL JDBC Driver to Tomcat

Download the MySQL Connector/J JDBC driver from http://dev.mysql.com/downloads/connector/j/
After unpacking the file you have downloaded, you will find a file called something like mysql-connector-java-5.1.x-bin.jar.
Copy the file into <tomcat-install>/lib.
Please note

MySQL Connector/J v5.1.36 contains a bug which causes it not to work together with MySQL 5.0.x.

4. Configure a DataSource for TermWeb in Tomcat

Edit <tomcat-install>/conf/context.xml.

Within the Context tags, insert the DataSource Resource tag:

<Resource name="jdbc/TermWebDS"
  auth="Container"
  type="javax.sql.DataSource"
  username="termwebuser"
  password="termwebpwd"
  driverClassName="com.mysql.jdbc.Driver"
  url="jdbc:mysql://localhost/termweb?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8&amp;rewriteBatchedStatements=true"
  maxTotal="20"  
  maxIdle="10"
  minIdle="2"
  testWhileIdle="true" 
  validationQuery="Select 1"/>

If MySQL does not run on the same server as Tomcat, replace localhost in url with the name of the database server.
If you named the database something else than termweb, replace termweb in url with your database name.
Replace termwebuser and termwebpwd in url with your database user and password.
Check Version specific upgrade notes for any actions needed for your TermWeb version.

The configuration properties for Tomcat's standard data source resource factory (org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory) are as follows:

- driverClassName – Fully qualified Java class name of the JDBC driver to be used.
- maxTotal – The maximum number of active connections that can be allocated from this pool at the same time.
- maxIdle – The maximum number of connections that can sit idle in this pool at the same time.
- minIdle – The minimum number of idle connections that will remain in this pool at the same time.
- maxWait – The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception.
- password – Database password to be passed to our JDBC driver.
- url – Connection URL to be passed to our JDBC driver. (For backwards compatibility, the property driverName is also recognized.)
- user – Database username to be passed to our JDBC driver.
- testWhileIdle – If the validity of the connection should be tested also when idle. Recommended to set this to true to avoid hanging connections.
- validationQuery – SQL query that can be used by the pool to validate connections before they are returned to the application. If specified, this query must be an SQL SELECT statement that returns at least one row.

Congratulations, you now have TermWeb connected to your MySQL database.