MariaDB Enterprise

Page last updated:

Integrating the Service With Your App

After the creation of the service and the binding of the service to the application, the environment variable VCAP_SERVICES is created. Information about the credentials are stored in this variable as shown here:

"mariadbent": [
  {
    "credentials": {
      "host": "galera-service.service.consul",
      "hostname": "galera-service.service.consul",
      "port": 3306,
      "name": "CF_9B81CA4D_E5BE_427E_991D_99FA13ABAE21",
      "database": "CF_9B81CA4D_E5BE_427E_991D_99FA13ABAE21",
      "username": "9raLhQ5kFQaBLSYj",
      "password": "LlsrR1V9hs0xXOAu",
      "database_uri": "mysql://9raLhQ5kFQaBLSYj:LlsrR1V9hs0xXOAu@galera-service.service.consul:3306/CF_9B81CA4D_E5BE_427E_991D_99FA13ABAE21?reconnect=true",
      "uri": "mysql://9raLhQ5kFQaBLSYj:LlsrR1V9hs0xXOAu@galera-service.service.consul:3306/CF_9B81CA4D_E5BE_427E_991D_99FA13ABAE21?reconnect=true",
      "jdbcUrl": "jdbc:mysql://galera-service.service.consul:3306/CF_9B81CA4D_E5BE_427E_991D_99FA13ABAE21?user=9raLhQ5kFQaBLSYj&password=LlsrR1V9hs0xXOAu"
    },
    "syslog_drain_url": null,
    "volume_mounts": [],
    "label": "mariadbent",
    "provider": null,
    "plan": "usage",
    "name": "my-db",
    "tags": []
  }
]

Please be aware that the number of concurrent connections (max_connections) to MariaDB is limited. You can manage this by using connection pooling.

Administrating Your MariaDB Instances

To connect to a running MariaDB instance with your local development tools, you can use the cf ssh feature of the cf CLI.

You can deploy PivotalMySQLWeb for MariaDB management on the Swisscom Application Cloud. It’s not the most feature rich GUI client for MariaDB, but a nice cloud native app with a simple deployment process.

You can use the cf-mysql-plugin for the Cloud Foundry CLI. This plugin installs the mysqland mysqldump clients and integrates them into the Cloud Foundry ecosystem.

Use Cases

A typical use case for MariaDB is a Cloud Foundry hosted web app which needs a relational data store.

Our setup is not suitable for expensive analytics or “Big Data” processing.

Sample Application

Cloud Foundry: Spring Music Example

Known Limitations

Our HA MariaDB offering is based on Galera, which brings some known limitations (from mariadb.com) with it. Please read this article carefully.

A summary of Galera limitations is on Tips on Converting to Galera (blockquote modified for our offering):

Schema Design:

• InnoDB storage engine only, every table must have a Primary Key. The legacy storage engine MyISAM is disabled with enforce_storage_engine=InnoDB in our offering.
• Please use indexes according to your query pattern.

Application Development:

• Check for errors, even after COMMIT.
• Moderate sized transactions.
• Don’t make assumptions about AUTO_INCREMENT values other than uniqueness. Not monotonicity, not gapless. (This applies for non-Galera, but probably happens more often here.)

Administration and Schema Migrations:

• ALTERs are handled differently. See SCHEMA UPGRADES for more details. Please be careful and test schema migrations.
• TRIGGERs and EVENTs may need checking. By default, with row-based replication, triggers run on the master, and the effects of their executions are replicated to the slaves.

Please add a primary key to every table you create. This is because of Row Based Replication. Tables without primary key may cause a downtime of the Galera Cluster (depending on the number of records).

Please contact Swisscom Application Cloud support when you need to issue long running DDL changes (for example ALTER TABLE with thousends of records). Long running DDL causes an outage for all customers on the cluster.

Our Galera aaS is a shared cluster. Each customer has their own database. Put simply, the service broker executes the following statements:

CREATE DATABSE CF_DATABASE_NAME
GRANT ALL ON `CF_DATABASE_NAME`.* TO 'USER'@'SUBNET'
REVOKE LOCK TABLES ON `CF_DATABASE_NAME`.* FROM 'USER'@'SUBNET'

Minimize Downtime for Schema Migrations

Lots of customers complained that when doing an ALTER TABLE statement in Galera, the whole cluster is read-locked for the duration of the statement. On big tables (millions of records) this can result in many hours of “outage”. Here are some tips to minimize that behavior.

• Add ALGORITHM=INPLACE, LOCK=NONE for Online DDL Operations in your ALTER TABLE statements. For example, to add a new index, write ALTER TABLE test ADD INDEX idx_test (test), ALGORITHM=INPLACE, LOCK=NONE. Be warned Online DDL is not always online and may incur locks.
• ALGORITHM=INPLACE, LOCK=NONE has some limitations and not supported operations. If you it doesn’t work for your case, use pt-online-schema-change. The tool pt-online-schema-change alters a table’s structure without blocking reads or writes. Please do not use this tool before reading its linked documentation and checking your service backups carefully. In our current offering you don’t have the necessary permissions for pt-online-schema-change. Please ask our support to GRANT PROCESS, REPLICATION SLAVE ON *.* TO for your serice-key. We can’t grant that on shared cluster, it means you must have your own dedicated Galera/MariaDB cluster.
• Don’t use TRUNCATE with huge tables. Please DROP the table and recreate.

Database Frameworks Integration tips

• Liquibase is not fully Galera compatible. See bug report primary key on DATABASECHANGELOG, PK_DATABASECHANGELOG, is no longer created for solutions.

Definer and bind-service (Change Credentials)

With cf bind-service the service broker (Cloud Foundry component) creates new credentials for security reasons (cf env APP_NAME to view them). This will break existing routines, triggers, views and events because they are still using the old user.

Error code from MariaDB backend:

Error: 1449 SQLSTATE: HY000 (ER_NO_SUCH_USER)
Message: The user specified as a definer ('%s'@'%s') does not exist

See the MariaDB blog post Changing the DEFINER of MySQL stored routines etc. for ideas on how to fix this.

Migrate your service instance

See Migrate from legacy MariaDB to MariaDB Ent how to migrate your service instance to a new database cluster.

Backup

Within your MariaDB service instance, you can start a backup and also restore it afterwards. For more information about this feature, check the service backup guide.

If you need more fine-grained control over your backups there is also an app available that allows for automatic scheduled backups: backman - the appcloud backup manager. Any MariaDB service instance bound to this app will be automatically backed up, can be downloaded and also restored on-demand.

If you accidentally DROP a table or other error happens, we can not restore your table unfortunately. Please make sure you create a service backup in the Portal, via the CLI with the Swisscom Plugin or with backman before you do changes.

More info from upstream open source project

Our MariaDB Enterprise offering is based on the the open source Cloud Foundry MySQL Service.

Compatibility to MySQL

Please be aware that MariaDB and MySQL are not fully compatible and have a few different features for developers (and administrators).

We deploy MariaDB 10.1.x series.

See MariaDB versus MySQL - Compatibility and MariaDB versus MySQL - Features for furhter infos.