MySQL

Collaborator supports MySQL versions 4.1, 5.0, 5.1, 5.4, 5.5, 5.6, 5.7 and 8.0.

Required components

Optional components

For downloads and documentation for all platforms, see the MySQL Developer Zone website.

First, install the database server component. Once you have installed the server, the last screen will give you the option to configure the MySQL server. If you select this, it will open a new wizard for configuration. Two parts in the configuration that are especially important: Port Number and Root Password. You will need to know both while getting your database setup and while configuring Collaborator to work with your database. For information on configuring your database server, go to the MySQL Documentation and select your MySQL version.

It is important to make sure you are using InnoDB tables. Using InnoDB tables will scale better for multiple users than MyISAM. For questions regarding configuring you MySQL database tables, see https://dev.mysql.com/doc/refman/5.5/en/mysql-config-wizard-database-usage.html. Collaborator does not support the non-default values of the auto_increment_increment and auto_increment_offset MySQL system variables.

To install the driver:

1. Download the Connector/J driver.

2. Stop the Collaborator server, if it is running.

3. For MySQL 8.0: Open <Collaborator Server>/tomcat/conf/Catalina/localhost/ROOT.xml. Locate the Resource element and replace the driverClassName="com.mysql.jdbc.Driver" with the driverClassName="com.mysql.cj.jdbc.Driver":

   <Resource driverClassName="com.mysql.cj.jdbc.Driver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxIdle="20" maxTotal="100" maxWaitMillis="10000" name="/jdbc/collabserver" password="<DB_password>" scope="Sharable" testOnBorrow="true" type="javax.sql.DataSource" url="jdbc:mysql://localhost:3306/<DB_schema>?useServerPrepStmts=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true" username="<DB_user>" validationQuery="SELECT 1"/>

4. Go to the <Collaborator Server>/tomcat/lib folder.

5. Copy the mysql-connector-java-*.jar file that comes with Connector/J to that folder.

6. Start the Collaborator server.

For Collaborator, you have to create a separate database, since the MySQL Server install will not create a database for you. This is a manual step that you need to do.

You can create a database using the MySQL Command Line Client, or using MySQL Workbench. If you plan to use the MySQL GUI client, it is important to know that the MySQL GUI Tools Bundle is reaching end of life. The instructions given in this documentation use the functionality in the current GUI client, MySQL Workbench.

The steps below for creating a database were written using MySQL Workbench 5.2.16 and are subject to change. As always, the most reliable source for database creation steps is MySQL Workbench Documentation.

1. Open MySQL Workbench.

2. Create a connection to your database server.

3. From the Home screen, under Server Administration, select New Server Instance.

4. Follow the steps in the New Server Instance wizard.

5. Create the database.

6. Go back to Home.

7. Under SQL Development, select the database server connection you provided in step 2.

8. In the Object Explorer, right-click and select Create Schema.

9. A screen will appear and prompt you to provide a schema name. Once you have done that, click Apply.

10. You will be shown the command used to create the schema, and given an option to edit it.

11. Once the create command is as you want it, click Apply.

12. You will be returned to the screen you saw in step 3. Click Finish.

We recommend that you create a user specifically for your Collaborator database rather than using the super-user, root. The database user must have the following priveleges: ALTER, CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, UPDATE. To learn how to create users in MySQL Workbench, see the documentation at MySQL Schema Privileges.

All of these changes go into effect immediately. You do not have to restart the MySQL server for changes to take effect.

During the GUI installation screens for the Collaborator server, you will be prompted for the MySQL server host name, TCP/IP port, database name, user name, and password. The installer will report any connectivity errors. When you visit the web page for Collaborator it will detect that you have a new database and will create all tables, indexes, and views for you automatically.

The following SQL script can be used to create a database and a database user for Collaborator and configure the required permissions for that user (be sure to change the database name, user login and password as appropriate):

• MySQL 4.x does not support the concept of a database VIEW object. Therefore custom reporting views are only present with v5.x servers.

• The MySQL native restore feature does not automatically clear the database before restoring from backup. You can use the MySQL native restore feature, but you must manually drop all tables in the database before you run the restore. By doing this, you will ensure an exact reproduction of your backed-up database. Option two is to use the alternative method provided by Collaborator.