MySQL

Collaborator supports MySQL versions 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.

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.

When installing Collaborator on a server, the installation wizard will prompt for the MySQL server host name, TCP/IP port, database name, user name, password and path to JDBC driver. 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):

• 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.

• Connector/J have different driver class names in versions 5.x and 8.x. During installation the wizard assigns the appropriate name based on the JDBC driver you have specified. To change the driver name afterwards, you may either re-run the installation wizard, or modify the Collaborator's ROOT.xml manually, as described below:

  1. Download the Connector/J driver.

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

  3. Open <Collaborator Server>/tomcat/conf/Catalina/localhost/ROOT.xml and locate the Resource element.

  4. For Connector/J 5.x specify driverClassName="com.mysql.jdbc.Driver":

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

     For Connector/J 8.x specify driverClassName="com.mysql.cj.jdbc.Driver" and serverTimezone property:

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

     Tips:

     The ampersand (&) has to be SGML-encoded to &, since the configuration file is an XML document. The Connector's serverTimezone value should coinside with the timezone in which your MySQL server is running, and it should be a valid Java timezone identifier.

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

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

  7. Start the Collaborator server.