20.2.1 Setting up a Direct SQL Connection Shared Database

This section has instructions for setting up a new Shared Database. For instructions on how to connect to a Shared Database from Geneious Prime refer to section 20.1.3 .

Supported Database Systems

To use a database as a Shared Database, Geneious requires that it support transactions with an isolation level set to READ COMMITTED. Supported databases systems include Microsoft SQL Server, PostgreSQL, Oracle and MySQL. It is possible to use other database systems if you provide the database driver, see section 20.2.1 .

Shared Databases have been tested using

• Microsoft SQL Server 2008 R2 and above
• PostgreSQL 9-12
• Oracle Database, up to 18c
• MySQL 5.7 and 8.0

Document Size Limitations

For Direct SQL Connection Shared Databases, document size is limited by the database’s maximum binary large object (BLOB) size. This varies between 1-4 GB. Refer to the documentation of your database system for the maximum BLOB size.

Recommended SQL configurations for the Shared Database

In most cases, the default settings for whatever SQL database system you are using are sufficient. The character set encoding for the database should be set to UTF-8 to avoid any potential problems indexing documents containing unusual characters.

There are a few exceptions where we recommend changes to the default configuration, listed below:

MYSQL

For MySQL 5.6 and 5.7, the default character encoding is latin1. This should be changed to CHARACTER SET utf8 and COLLATE utf8_unicode_520_ci. For MySQL 8.0, the default encoding should already be set to utf8 so no changes are required.

See the MySQL 5.7 Reference Manual for further information.

Other changes you may need to make to the MySQL configuration file (my.cnf) to improve performance are as follows:

• innodb_buffer_pool_size=[On a dedicated database server you may set this parameter up to 80% of the machine physical memory size]
• innodb_flush_log_at_trx_commit=2
• query_cache_limit=2M
• query_cache_size=128M
• max_allowed_packet= 1073741824 (Note this is the maximum size for max_allowed_packet and can prevent errors when handling large files)

Further optional improvements:

• innodb_log_file_size=256M
• innodb_log_buffer_size=16M

Note: If you change the innodb_log_file_size then you will need to delete the current log files before the server will start up again.

Microsoft SQL Server

Generally the default settings for MS SQL server are sufficient. However, we strongly recommend using Snapshot Isolation to avoid deadlocks. These can occur when multiple users use the shared database at once and may lead to failure of some actions. This setting becomes necessary if there will be more than a handful of users using the database concurrently. Read more about Snapshot Isolation here.

Setting up an SQL database to use with Geneious Prime

Follow these steps to set up a Direct SQL Connection shared database to use with Geneious Prime. The instructions in this section require knowledge of how to create and administer an SQL database and are intended for your IT department or system administrator. For instructions on connecting to your shared database once it has been set up, refer to section 20.1.3 .

1. Create database
   • Install a supported database management system if you do not already have one (see section 20.2.1 ).
   • Create a new database with your desired database name. Make sure that you have a user that has rights to create tables.
   • Make sure to create the database with a suitable character set and collation - UTF8 (or USC-2 for Microsoft SQL Server) is recommended.
   • You should consider possible limitations to document size due to the database’s maximum BLOB size when selecting a database system.
2. Add users to your database using the database management system
   • Make sure all users of the database have SELECT, INSERT, UPDATE and DELETE rights, otherwise they will not be able to use the Shared Database as intended.
3. Set up database with Geneious Prime
   • Run Geneious Prime and click on the Shared Database icon in the Sources panel, then click the New database connection button to connect to your database. Geneious will automatically set up the database when you connect, if it has not yet been set up.
   • This will only succeed if you have permission to create tables on the database.
4. Set the first Database Admin
   • To create your first Database Admin, right click on the root folder of your database and select Make the current user a Database Admin. This option is only available when your shared database does not have any Database Admins.
   • Additional Database Admins can be added by setting the user’s role in the group Everybody to ADMIN.
5. Set up sharing permissions for your database
   • By default, all folders in your database will be shared with all users. This will allow you to use your Shared Database like a shared local database. If this is what you want, no further setup is required.
   • If you want to restrict access to particular folders you can do so using Groups and Roles – refer to section 20.2.2 for more details. Groups can only be created by a Database Admin.

Your database should now be ready to use with Geneious Prime. Users can connect to the database by clicking on Shared Databases in the Sources panel and then clicking New database connection. This will bring up a dialog for the user to enter in the database details. If you wish to preconfigure the connection settings for users, you can do so via the geneious.properties file (see section 21.3 ).

Supplying Your Own Database Driver

Shared Databases were designed with the supported databases in mind and packaged with database drivers for them. However, Geneious allows you to supply your own jdbc database driver if you want to, for example if you have an updated driver, or want to use a driver for an unsupported database. It is not guaranteed that Shared Databases will work with an unsupported database system but it is likely that it will if you provide the correct driver.

You can supply your own driver under More Options in the New database connection dialog, or the Edit Account Details dialog (available by right clicking on the root folder of your shared database) for an existing shared database.