Getting Started
Architecture
NServiceBus
Transports
ServiceInsight
ServicePulse
ServiceControl
Monitoring
Samples

SQL Persistence - MySQL dialect

Component: Sql Persistence
Target Version: NServiceBus 8.x

This persistence will run on the free version of the above engines, i.e. MySQL Community Edition. However, it is strongly recommended to use commercial versions for any production system. It is also recommended to ensure that support agreements are in place. See MySQL support for details.

Supported database versions

SQL persistence supports MySQL 5.7 and above, as well as AWS Aurora MySQL. It does not support lower versions due to the use of JSON Data Type.

Usage

Using the MySql.Data NuGet Package.

var connection = "server=localhost;user=root;database=dbname;port=3306;password=pass;AllowUserVariables=True;AutoEnlist=false";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var subscriptions = persistence.SubscriptionSettings();
subscriptions.CacheFor(TimeSpan.FromMinutes(1));
persistence.SqlDialect<SqlDialect.MySql>();
persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new MySqlConnection(connection);
    });

The following settings are required for MySQL connections string.

  • AllowUserVariables=True: since the Persistence uses user variables.
  • AutoEnlist=false: To prevent auto enlistment in a Distributed Transaction which the MySql .NET connector does not currently support.

Unicode support

SQL persistence itself supports Unicode characters, however data may become corrupted during saving if the database settings are incorrect. If Unicode support is required, follow the guidelines for each database engine. In particular set the correct character set and collation for databases storing persistence data.

Refer to the dedicated MySQL documentation for details.

Supported name lengths

SQL persistence automatically generates names of database objects such as tables, indexes and procedures used internally. Every database engine has its own rules and limitations regarding maximum allowed name length.

MySQL supports max. 64 characters.

The SQL persistence will not validate name length, for two reasons. Firstly, the supported name length values are higher and should be sufficient for typical scenarios. Secondly, it is possible to modify the database setting locally to support longer names. In case of long names for sagas, etc. the database engine may perform automatic name truncation.