MySQL is a popular enterprise-class, open-source database. This is your best choice if you need a free, open-source database.
Supported MySQL Versions
Collaborator supports MySQL versions 5.6, 5.7, 8.0 & 9.0.
Note: MySQL database is supported in Collaborator Team and Collaborator Enterprise. For a complete list of differences between Collaborator editions, please see the comparison page.
Required components
| • | MySQL Community Server - database server, |
| • | Connector/J - database driver for Java platforms. Supported driver versions are 5.1.x - 8.0.x |
When upgrading to Connector/J 8.0.x be aware that it performs time offset adjustments, so your server should either use one of canonical time zones or use the serverTimezone property to specify time zone.
Optional components
| • | MySQL Workbench - GUI tool for graphical server administration. A GUI tool alternative to MySQL Command Line Client, which is a part of part of the database server. |
For downloads and documentation for all platforms, see the MySQL Developer Zone web site.
Install and Configure Database
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/mysql-windows-excerpt/8.0/en/mysql-installer-workflow-server.html.
Collaborator does not support the non-default values of the auto_increment_increment and auto_increment_offset MySQL system variables.
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. |
| a) | From the Home screen, under Server Administration, select New Server Instance. |
| b) | Follow the steps in the New Server Instance Wizard. |
| 3. | Create the database. |
| a) | Go back to the Home tab. |
| b) | Under SQL Development, select the database server connection you provided in step 2. |
| c) | In the Object Explorer, right-click and select Create Schema. |
| d) | A screen will appear and prompt you to provide a schema name. Once you have done that, click Apply. |
| e) | You will be shown the command used to create the schema, and given an option to edit it. |
| f) | Once the create command is as you want it, click Apply. |
| g) | You will be returned to the screen you saw in step 3d. Click Finish. |
We recommend that you create a user specifically for your Collaborator database rather than using the super-user, root. To do this 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):
CREATE DATABASE IF NOT EXISTS ccollabdb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE USER 'collabuser' IDENTIFIED BY 'password';
GRANT
ALTER,
CREATE,
CREATE TEMPORARY TABLES,
CREATE VIEW,
DELETE,
DROP,
EXECUTE,
INDEX,
INSERT,
LOCK TABLES,
SELECT,
UPDATE
ON TABLE ccollabdb.*
TO collabuser;
FLUSH PRIVILEGES;
Technical details and limitations
| • | 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 file and locate the Resource element.
4a. 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&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true" username="<DB_user>" validationQuery="SELECT 1"/>
4b. 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&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=UTC" username="<DB_user>" validationQuery="SELECT 1"/>
Tips: The ampersand (&), needs to be SGML encoded to "&" because 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.