Platform.sh User Documentation

MariaDB/MySQL (database service)

Try for 30 days
Flexible, version-controlled infrastructure provisioning and development-to-production workflows
Activate your trial

Platform.sh supports both MariaDB and Oracle MySQL to manage your relational databases. Their infrastructure setup is nearly identical, though they differ in some features. See the MariaDB documentation or MySQL documentation for more information.

Use a framework Anchor to this heading

If you use one of the following frameworks, follow its guide:

Supported versions Anchor to this heading

You can select the major and minor version.

Patch versions are applied periodically for bug fixes and the like. When you deploy your app, you always get the latest available patches.

The service types mariadb and mysql both refer to MariaDB. The service type oracle-mysql refers to MySQL as released by Oracle, Inc. Other than the value for their type, MySQL and MariaDB have the same behavior and the rest of this page applies to both of them.

mariadb mysql oracle-mysql
  • 11.4
  • 11.2
  • 11.0
  • 10.11
  • 10.6
  • 10.5
  • 10.4
  • 11.0
  • 10.11
  • 10.6
  • 10.5
  • 10.4
  • 10.3
  • 8.0
  • 5.7

Supported versions on Dedicated environments Anchor to this heading

oracle-mysql is not yet available for Dedicated Gen 3 environments. It also isn’t available for Dedicated Gen 2 environments.

On Dedicated environments, MariaDB is available with Galera for replication. Supported versions are the following:

Dedicated Gen 2 Dedicated Gen 3
  • 10.11 Galera
  • 10.8 Galera
  • 10.7 Galera
  • 10.6 Galera
  • 10.5 Galera
  • 10.4 Galera
  • 10.3 Galera
  • 10.11 Galera
  • 10.6 Galera
  • 10.5 Galera
  • 10.4 Galera
  • 10.3 Galera

Dedicated environments only support the InnoDB storage engine. Tables created on Dedicated environments using the MyISAM storage engine don’t replicate between all hosts in the cluster. See how to convert tables to the InnoDB engine.

Deprecated versions Anchor to this heading

The following versions are deprecated. They’re available, but they aren’t receiving security updates from upstream and aren’t guaranteed to work. They’ll be removed in the future, so migrate to one of the supported versions.

mariadb mysql oracle-mysql
  • 10.2
  • 10.1
  • 10.3
  • 10.0
  • 5.5
  • 10.2
  • 10.1
  • 10.3
  • 10.0
  • 5.5

Upgrade Anchor to this heading

When upgrading your service, skipping versions may result in data loss. Upgrade sequentially from one supported version to another (10.5 -> 10.6 -> 10.11 -> 11.0), and check that each upgrade commit translates into an actual deployment.

To upgrade, update the service version in your service configuration.

Change the service type Anchor to this heading

To change the service type:

  1. Export your data.
  1. Remove the old service from your service configuration.
  2. Specify a new service type.
  3. Import your data into the new service.

Downgrade Anchor to this heading

You can’t downgrade to a previous version and retain your data. To downgrade your database, follow these steps:

  1. Export your data.
  2. Remove the old service from your service configuration.
  3. Add a new service with a different name and your desired version.
  4. Import your data into the new service.

Usage example Anchor to this heading

Configure your service with at least 256 MB in disk space.

1. Configure the service Anchor to this heading

To define the service, use the mariadb or mysql type for MariaDB or the oracle-mysql type for Oracle MySQL:

.platform/services.yaml
# The name of the service container. Must be unique within a project.
<SERVICE_NAME>:
    type: mariadb:<VERSION>
    disk: 256

Note that changing the name of the service replaces it with a brand new service and all existing data is lost. Back up your data before changing the service.

2. Add the relationship Anchor to this heading

To define the relationship, use the following configuration:

.platform/applications.yaml
# Relationships enable access from this app to a given service.
# The example below shows simplified configuration leveraging a default service
# (identified from the relationship name) and a default endpoint.
# See the Application reference for all options for defining relationships and endpoints.
relationships:
    <SERVICE_NAME>: 

You can define <SERVICE_NAME> as you like, so long as it’s unique between all defined services and matches in both the application and services configuration.

The example above leverages default endpoint configuration for relationships. That is, it uses default endpoints behind-the-scenes, providing a relationship (the network address a service is accessible from) that is identical to the name of that service.

Depending on your needs, instead of default endpoint configuration, you can use explicit endpoint configuration.

With the above definition, the application container now has access to the service via the relationship <RELATIONSHIP_NAME> and its corresponding PLATFORM_RELATIONSHIPS environment variable.

MariaDB example Anchor to this heading

Service definition Anchor to this heading

.platform/services.yaml
# The name of the service container. Must be unique within a project.
mariadb:
    type: mariadb:11.4
    disk: 256

App configuration Anchor to this heading

.platform/applications.yaml
# Relationships enable access from this app to a given service.
# The example below shows simplified configuration leveraging a default service
# (identified from the relationship name) and a default endpoint.
# See the Application reference for all options for defining relationships and endpoints.
relationships:
    mariadb: 

OracleMySQL example Anchor to this heading

Service definition Anchor to this heading

.platform/services.yaml
# The name of the service container. Must be unique within a project.
oraclemysql:
    type: oracle-mysql:8.0
    disk: 256

App configuration Anchor to this heading

.platform/applications.yaml
# Relationships enable access from this app to a given service.
# The example below shows simplified configuration leveraging a default service
# (identified from the relationship name) and a default endpoint.
# See the Application reference for all options for defining relationships and endpoints.
relationships:
    oraclemysql: 

Use in app Anchor to this heading

To use the configured service in your app, add a configuration file similar to the following to your project.

package examples

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	psh "github.com/platformsh/config-reader-go/v2"
	sqldsn "github.com/platformsh/config-reader-go/v2/sqldsn"
)

func UsageExampleMySQL() string {

	// Create a NewRuntimeConfig object to ease reading the Platform.sh environment variables.
	// You can alternatively use os.Getenv() yourself.
	config, err := psh.NewRuntimeConfig()
	checkErr(err)

	// The 'database' relationship is generally the name of the primary SQL database of an application.
	// That's not required, but much of our default automation code assumes it.
	credentials, err := config.Credentials("database")
	checkErr(err)

	// Using the sqldsn formatted credentials package.
	formatted, err := sqldsn.FormattedCredentials(credentials)
	checkErr(err)

	db, err := sql.Open("mysql", formatted)
	checkErr(err)

	defer db.Close()

	// Force MySQL into modern mode.
	db.Exec("SET NAMES=utf8")
	db.Exec("SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES," +
		"NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO," +
		"NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY'")

	// Creating a table.
	sqlCreate := "CREATE TABLE IF NOT EXISTS People (" +
		"id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY," +
		"name VARCHAR(30) NOT NULL," +
		"city VARCHAR(30) NOT NULL)"

	_, err = db.Exec(sqlCreate)
	checkErr(err)

	// Insert data.
	sqlInsert := "INSERT INTO People (name, city) VALUES" +
		"('Neil Armstrong', 'Moon')," +
		"('Buzz Aldrin', 'Glen Ridge')," +
		"('Sally Ride', 'La Jolla');"

	_, err = db.Exec(sqlInsert)
	checkErr(err)

	table := "<table>" +
		"<thead>" +
		"<tr><th>Name</th><th>City</th></tr>" +
		"</thead>" +
		"<tbody>"

	var id int
	var name string
	var city string

	rows, err := db.Query("SELECT * FROM People")
	if err != nil {
		panic(err)
	} else {
		for rows.Next() {
			err = rows.Scan(&id, &name, &city)
			checkErr(err)
			table += fmt.Sprintf("<tr><td>%s</td><td>%s</td><tr>\n", name, city)
		}
		table += "</tbody>\n</table>\n"
	}

	_, err = db.Exec("DROP TABLE People;")
	checkErr(err)

	return table
}
package sh.platform.languages.sample;

import sh.platform.config.Config;
import sh.platform.config.MySQL;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.function.Supplier;

public class MySQLSample implements Supplier<String> {

    @Override
    public String get() {
        StringBuilder logger = new StringBuilder();

        // Create a new config object to ease reading the Platform.sh environment variables.
        // You can alternatively use getenv() yourself.
        Config config = new Config();

        // The 'database' relationship is generally the name of primary SQL database of an application.
        // That's not required, but much of our default automation code assumes it.
        MySQL database = config.getCredential("database", MySQL::new);
        DataSource dataSource = database.get();

        // Connect to the database
        try (Connection connection = dataSource.getConnection()) {

            // Creating a table.
            String sql = "CREATE TABLE IF NOT EXISTS People (" +
                    " id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY," +
                    "name VARCHAR(30) NOT NULL," +
                    "city VARCHAR(30) NOT NULL)";

            final Statement statement = connection.createStatement();
            statement.execute(sql);

            // Insert data.
            sql = "INSERT INTO People (name, city) VALUES" +
                    "('Neil Armstrong', 'Moon')," +
                    "('Buzz Aldrin', 'Glen Ridge')," +
                    "('Sally Ride', 'La Jolla')";

            statement.execute(sql);

            // Show table.
            sql = "SELECT * FROM People";
            final ResultSet resultSet = statement.executeQuery(sql);
            logger.append("<table><thead><tr><th>Name</th><th>City</th></tr></thhead><tbody>");
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String city = resultSet.getString("city");
                logger.append(String.format("<tr><td>%s</td><td>%s</td></tr>", name, city));
                logger.append('\n');
            }
            logger.append("</tbody></table>");
            statement.execute("DROP TABLE People");
            return logger.toString();
        } catch (SQLException exp) {
            throw new RuntimeException("An error when execute MySQL", exp);
        }
    }
}
const mysql = require("mysql2/promise");
const config = require("platformsh-config").config();

exports.usageExample = async function () {
    const credentials = config.credentials("database");

    const connection = await mysql.createConnection({
        host: credentials.host,
        port: credentials.port,
        user: credentials.username,
        password: credentials.password,
        database: credentials.path,
    });

    // Creating a table.
    await connection.query(
        `CREATE TABLE IF NOT EXISTS People (
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(30) NOT NULL,
            city VARCHAR(30) NOT NULL
        )`
    );

    // Insert data.
    await connection.query(
        `INSERT INTO People (name, city)
        VALUES
            ('Neil Armstrong', 'Moon'),
            ('Buzz Aldrin', 'Glen Ridge'),
            ('Sally Ride', 'La Jolla');`
    );

    // Show table.
    const [rows] = await connection.query("SELECT * FROM People");

    // Drop table.
    await connection.query("DROP TABLE People");

    const outputRows = rows
        .map(({ name, city }) => `<tr><td>${name}</td><td>${city}</td></tr>\n`)
        .join("\n");

    return `
    <table>
        <thead>
            <tr>
                <th>Name</th><th>City</th>
            </tr>
        </thhead>
        <tbody>
            ${outputRows}
        </tbody>
    </table>
    `;
};
<?php

declare(strict_types=1);

use Platformsh\ConfigReader\Config;

// Create a new config object to ease reading the Platform.sh environment variables.
// You can alternatively use getenv() yourself.
$config = new Config();

// The 'database' relationship is generally the name of primary SQL database of an application.
// That's not required, but much of our default automation code assumes it.
$credentials = $config->credentials('database');

try {
    // Connect to the database using PDO.  If using some other abstraction layer you would
    // inject the values from $database into whatever your abstraction layer asks for.
    $dsn = sprintf('mysql:host=%s;port=%d;dbname=%s', $credentials['host'], $credentials['port'], $credentials['path']);
    $conn = new \PDO($dsn, $credentials['username'], $credentials['password'], [
        // Always use Exception error mode with PDO, as it's more reliable.
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        // So we don't have to mess around with cursors and unbuffered queries by default.
        \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
        // Make sure MySQL returns all matched rows on update queries including
        // rows that actually didn't have to be updated because the values didn't
        // change. This matches common behavior among other database systems.
        \PDO::MYSQL_ATTR_FOUND_ROWS => TRUE,
    ]);

    // Creating a table.
    $sql = "CREATE TABLE People (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(30) NOT NULL,
      city VARCHAR(30) NOT NULL
      )";
    $conn->query($sql);

    // Insert data.
    $sql = "INSERT INTO People (name, city) VALUES
        ('Neil Armstrong', 'Moon'),
        ('Buzz Aldrin', 'Glen Ridge'),
        ('Sally Ride', 'La Jolla');";
    $conn->query($sql);

    // Show table.
    $sql = "SELECT * FROM People";
    $result = $conn->query($sql);
    $result->setFetchMode(\PDO::FETCH_OBJ);

    if ($result) {
        print <<<TABLE
<table>
<thead>
<tr><th>Name</th><th>City</th></tr>
</thead>
<tbody>
TABLE;
        foreach ($result as $record) {
            printf("<tr><td>%s</td><td>%s</td></tr>\n", $record->name, $record->city);
        }
        print "</tbody>\n</table>\n";
    }

    // Drop table
    $sql = "DROP TABLE People";
    $conn->query($sql);

} catch (\Exception $e) {
    print $e->getMessage();
}
import pymysql
from platformshconfig import Config


def usage_example():

    # Create a new Config object to ease reading the Platform.sh environment variables.
    # You can alternatively use os.environ yourself.
    config = Config()

    # The 'database' relationship is generally the name of primary SQL database of an application.
    # That's not required, but much of our default automation code assumes it.'
    credentials = config.credentials('database')

    try:
        # Connect to the database using PDO. If using some other abstraction layer you would inject the values
        # from `database` into whatever your abstraction layer asks for.

        conn = pymysql.connect(host=credentials['host'],
                               port=credentials['port'],
                               database=credentials['path'],
                               user=credentials['username'],
                               password=credentials['password'])

        sql = '''
                CREATE TABLE People (
                id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(30) NOT NULL,
                city VARCHAR(30) NOT NULL
                )
                '''

        cur = conn.cursor()
        cur.execute(sql)

        sql = '''
                INSERT INTO People (name, city) VALUES
                ('Neil Armstrong', 'Moon'),
                ('Buzz Aldrin', 'Glen Ridge'),
                ('Sally Ride', 'La Jolla');
                '''

        cur.execute(sql)

        # Show table.
        sql = '''SELECT * FROM People'''
        cur.execute(sql)
        result = cur.fetchall()

        table = '''<table>
<thead>
<tr><th>Name</th><th>City</th></tr>
</thead>
<tbody>'''

        if result:
            for record in result:
                table += '''<tr><td>{0}</td><td>{1}</td><tr>\n'''.format(record[1], record[2])
            table += '''</tbody>\n</table>\n'''

        # Drop table
        sql = '''DROP TABLE People'''
        cur.execute(sql)

        # Close communication with the database
        cur.close()
        conn.close()

        return table

    except Exception as e:
        return e

Configure connections Anchor to this heading

There may be cases where you want to configure a database connection manually.

To get the URL to connect to the database, run the following command:

platform relationships

The result is the complete information for all relationships with an additional url property. Use the url property as your connection.

Note that the information about the relationship can change when an app is redeployed or restarted or the relationship is changed. So your apps should only rely on the PLATFORM_RELATIONSHIPS environment variable directly rather than hard coding any values.

You can also see a guide on how to convert the PLATFORM_RELATIONSHIPS environment variable to a different form.

Configuration options Anchor to this heading

You can configure your MySQL service in the services configuration with the following options:

Name Type Version Description
schemas An array of strings 10.0+ All databases to be created. Defaults to a single main database.
endpoints An endpoints dictionary 10.0+ Endpoints with their permissions. See multiple databases.
properties A properties dictionary MariaDB: 10.1+; Oracle MySQL: 8.0+ Additional properties for the database. Equivalent to using a my.cnf file. See property options.

Example configuration:

.platform/services.yaml
# The name of the service container. Must be unique within a project.
mariadb:
    type: mariadb:11.4
    disk: 2048
    configuration:
        schemas:
            - main
        endpoints:
            mysql:
                default_schema: main
                privileges:
                    main: admin
        properties:
            max_allowed_packet: 64

Relationship reference Anchor to this heading

Example information available through the PLATFORM_RELATIONSHIPS environment variable or by running platform relationships.

Note that the information about the relationship can change when an app is redeployed or restarted or the relationship is changed. So your apps should only rely on the PLATFORM_RELATIONSHIPS environment variable directly rather than hard coding any values.

MariaDB reference Anchor to this heading

{
  "username": "user",
  "scheme": "mysql",
  "service": "mariadb",
  "fragment": null,
  "ip": "123.456.78.90",
  "hostname": "azertyuiopqsdfghjklm.mariadb.service._.eu-1.platformsh.site",
  "port": 3306,
  "cluster": "azertyuiop-main-7rqtwti",
  "host": "mariadb.internal",
  "rel": "mysql",
  "path": "main",
  "query": {
    "is_master": true
  },
  "password": "",
  "type": "mariadb:11.4",
  "public": false,
  "host_mapped": false
}

Oracle MySQL reference Anchor to this heading

{
  "username": "user",
  "scheme": "mysql",
  "service": "oracle-mysql",
  "fragment": null,
  "ip": "123.456.78.90",
  "hostname": "azertyuiopqsdfghjklm.oracle-mysql.service._.eu-1.platformsh.site",
  "port": 3306,
  "cluster": "azertyuiop-main-afdwftq",
  "host": "oraclemysql.internal",
  "rel": "mysql",
  "path": "main",
  "query": {
    "is_master": true
  },
  "password": "",
  "type": "oracle-mysql:8.0",
  "public": false,
  "host_mapped": false
}

Access the service directly Anchor to this heading

You can access the service using the Platform.sh CLI by running platform sql.

You can also access it from you app container via SSH. From your relationship data, you need: host, port, user, path. Then run the following command:

mysql -h HOST -P PORT -u USER PATH

Assuming the values from the MariaDB reference, that would be:

mysql -h mariadb.internal -P 3306 -u user main

If your database relationship has a password, pass the -p switch and enter the password when prompted:

mysql -p -h mariadb.internal -P 3306 -u user main

Define permissions Anchor to this heading

With version 10.0 or later, you can define multiple users with different permissions for your database. To do so, define multiple endpoints in your service configuration.

For each endpoint you add, you can define the following properties:

Name Type Required Description
default_schema string Which of the defined schemas to default to. If not specified, the path property of the relationship is null and so tools such as the Platform.sh CLI can’t access the relationship.
privileges A permissions dictionary For each of the defined schemas, what permissions the given endpoint has.

Possible permissions:

Name Type Description
Read-only ro Can select, create temporary tables, and see views.
Read-write rw In addition to read-only permissions, can also insert, update, delete, manage and execute events, execute routines, create and drop indexes, manage and execute triggers, and lock tables.
Admin admin In addition to read-write permissions, can also create, drop, and alter tables; create views; and create and alter routines.
Replication replication For replicating databases. In addition to read-only permissions, can also lock tables.

Restrict access to database replicas only Anchor to this heading

Feature availability

This feature is available on Grid HA (High Availability) and Dedicated Gen 3 projects. For more information, contact Sales.

Your main database lives on one of the three nodes provided on Grid HA and Dedicated Gen 3. The two other nodes can each accommodate a replica of your main database.

For security reasons, you can grant your app access to a replica instead of your main database. To do so, when defining the relationship between your app and database, make sure you do the following:

  1. Use the explicit endpoint syntax.
  2. Add the -replica suffix to the name of the endpoint you want to use.

This results in the following configuration:

.platform.app.yaml
relationships:
    RELATIONSHIP_NAME:
        service: SERVICE_NAME
        endpoint: ENDPOINT_NAME-replica

For example, if you define a mariadb database as follows:

.platform/services.yaml
mariadb:
  type: mariadb:11.4
  disk: 2048
    configuration:
      schemas:
        - main
    endpoints:
      admin:
        default_schema: main
        privileges:
          main: admin
      reporter:
        privileges:
          main: ro

To create a replica of the mariadb database and allow your app to connect to it through the admin endpoint with admin permissions, use the following configuration:

.platform.app.yaml
relationships:
  mariadb:
    service: mariadb
    endpoint: admin-replica

To create a replica of the mariadb database and allow your app to connect to it through the reporter endpoint with read-only permissions instead, use the following configuration:

.platform.app.yaml
relationships:
  mariadb:
    service: mariadb
    endpoint: reporter-replica

Grant access to the main database and its replicas Anchor to this heading

Feature availability

This feature is available on Grid HA (High Availability) and Dedicated Gen 3 projects. For more information, contact Sales.

Your main database lives on one of the three nodes provided on Grid HA and Dedicated Gen 3. The two other nodes can each accommodate a replica of your main database.

You may want to grant access to both your main database and its replicas. To do so, when defining the relationship between your app and database, make sure you do the following:

  1. Use the explicit endpoint syntax.
  2. Add the -all suffix to the name of the endpoint you want to use.

This results in the following configuration, which creates a replica on each of the secondary nodes:

.platform.app.yaml
relationships:
    RELATIONSHIP_NAME:
        service: SERVICE_NAME
        endpoint: ENDPOINT_NAME-all

For example, if you define a mariadb database as follows:

.platform/services.yaml
mariadb:
  type: mariadb:11.4
  disk: 2048
    configuration:
      schemas:
        - main
    endpoints:
      admin:
        default_schema: main
        privileges:
          main: admin
      reporter:
        privileges:
          main: ro

To allow your app to connect to your main database and both its replicas through the admin endpoint with admin permissions, use the following configuration:

.platform.app.yaml
relationships:
  mariadb:
    service: mariadb
    endpoint: admin-all

To allow your app to connect to your main database and both its replicas through the reporter endpoint with read-only permissions, use the following configuration:

.platform.app.yaml
relationships:
  mariadb:
    service: mariadb
    endpoint: reporter-all

Multiple databases Anchor to this heading

With version 10.0 or later, you can define multiple databases.

To do so, define multiple schemas in your service configuration.

You can also specify multiple endpoints for permissions. If neither schemas nor endpoints is included, it’s equivalent to the following default:

.platform/services.yaml
# The name of the service container. Must be unique within a project.
mariadb:
    type: mariadb:11.4
    disk: 2048
    configuration:
        schemas:
            - main
        endpoints:
            mysql:
                default_schema: main
                privileges:
                    main: admin

If either schemas or endpoints are defined, no default is applied and you have to specify the full configuration.

Multiple databases example Anchor to this heading

The following configuration example creates a single MariaDB service named mariadb with two databases, main and legacy. Access to the database is defined through three endpoints:

  • admin has full access to both databases.
  • reporter has SELECT query access to main but no access to legacy.
  • importer has SELECT/INSERT/UPDATE/DELETE (but not DDL) access to legacy but no access to main.
.platform/services.yaml
# The name of the service container. Must be unique within a project.
mariadb:
    type: mariadb:11.4
    disk: 2048
    configuration:
        schemas:
            - main
            - legacy
        endpoints:
            admin:
                default_schema: main
                privileges:
                    main: admin
                    legacy: admin
            reporter:
                privileges:
                    main: ro
            importer:
                default_schema: legacy
                privileges:
                    legacy: rw

Expose these endpoints to your app as relationships in your app configuration:

.platform.app.yaml
name: myapp

[...]

# Relationships enable an app container's access to a service.
relationships:
    database: "mariadb:admin"
    reports: "mariadb:reporter"
    imports: "mariadb:importer"

These relationships are then available in the PLATFORM_RELATIONSHIPS environment variable. Each has its own credentials you can use to connect to the given database.

Configure the database Anchor to this heading

For MariaDB 10.1 and later and Oracle MySQL 8.0 and later, you can set some configuration properties (equivalent to using a my.cnf file).

In your settings, add the properties key to the configuration key. It offers the following properties:

Name Type Default Description
max_allowed_packet integer 16 The maximum size for packets in MB. Can be from 1 to 100.
default_charset string utf8mb4 after February 2020 and latin1 before The default character set. Affects any tables created after it’s set.
default_collation string utf8mb4_unicode_ci after February 2020 and latin1 before The default collation. Affects any tables created after it’s set.
optimizer_switch string A place to set various server optimization variables. See the MariaDB documentation.
optimizer_use_condition_selectivity integer 4 in version 10.4.1+ and 1 before that Which statistics are used by the optimizer. From 1 to 5. See the MariaDB documentation.
innodb_adaptive_hash_index integer 0 in version 10.5+ and 1 before that Enable/Disable InnoDB Hash Index. See the MariaDB documentation.
max_heap_table_size integer 32 The maximum size for user-created MEMORY tables in MB. Can be from 1 to 4096.
table_definition_cache integer 400 The number of table definitions that can be cached. See the MariaDB documentation.
table_open_cache integer 400 The maximum number of open tables cached in one table cache instance. See the MariaDB documentation.
wsrep_sync_wait integer 0 (Disabled) Ensure execution of statements in fully synced nodes. See the MariaDB documentation.

An example of setting these properties:

.platform/services.yaml
# The name of the service container. Must be unique within a project.
mariadb:
    type: mariadb:11.4
    disk: 2048
    configuration:
        properties:
            max_allowed_packet: 64
            default_charset: utf8mb4
            default_collation: utf8mb4_unicode_ci

You can also change a table’s character set and collation through ALTER TABLE commands:

-- To change defaults when creating new tables:
ALTER DATABASE main CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- To change defaults when creating new columns:
ALTER TABLE table_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- To convert existing data:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For further details, see the MariaDB documentation.

Password generation Anchor to this heading

When you connect your app to a database, an empty password is generated for the database by default. This can cause issues with your app.

To generate real passwords for your database, define custom endpoints in your service configuration. For each custom endpoint you create, you get an automatically generated password, similarly to when you create multiple databases. Note that you can’t customize these automatically generated passwords.

After your custom endpoints are exposed as relationships in your app configuration, you can retrieve the password for each endpoint through the PLATFORM_RELATIONSHIPS environment variable within your application containers. The password value changes automatically over time, to avoid downtime its value has to be read dynamically by your app. Globally speaking, having passwords hard-coded into your codebase can cause security issues and should be avoided.

When you switch from the default configuration with an empty password to custom endpoints, make sure your service name remains unchanged. Failure to do so results in the creation of a new service, which removes any existing data from your database.

Storage Engine Anchor to this heading

It’s best to use the InnoDB storage engine wherever possible. MyISAM is only properly supported in non-Dedicated environments. In Dedicated environments, there is no replication of MyISAM tables.

If MyISAM tables have been inadvertently created or imported in a Dedicated environment (if you see ENGINE=MyISAM in the response to SHOW CREATE TABLE EXISTING_TABLE), convert them to use the InnoDB storage engine as follows:

  1. Rename the existing table.

    RENAME TABLE EXISTING_TABLE OLD_TABLE;
  2. Create a new table from the data in the existing table.

    CREATE TABLE EXISTING_TABLE SELECT * from OLD_TABLE;

Now when you run SHOW CREATE TABLE EXISTING_TABLE, you see ENGINE=InnoDB.

Service timezone Anchor to this heading

To change the timezone for a given connection, run SET time_zone = TIMEZONE;.

Exporting data Anchor to this heading

To download all data from your SQL database, use the Platform.sh CLI. If you have a single SQL database, the following command exports all data to a local file:

platform db:dump

If you have multiple SQL databases, you are prompted for which one to export. You can also specify a database by its relationship name:

platform db:dump --relationship RELATIONSHIP_NAME

Compression Anchor to this heading

By default, the file is uncompressed. To compress it, use the --gzip (-z) option:

platform db:dump --gzip

Using the output in bash Anchor to this heading

To pipe the result to another command, use the --stdout option. For example, to create a bzip2-compressed file, run:

platform db:dump --stdout | bzip2 > dump.sql.bz2

Importing data Anchor to this heading

To load data into a database, pipe an SQL dump through the platform sql command, like so:

platform sql < my_database_backup.sql

That runs the database backup against the SQL database on Platform.sh. That works for any SQL file, so the usual caveats about importing an SQL dump apply (for example, it’s best to run against an empty database).

As with exporting, you can specify a specific environment and a specific database relationship to use:

platform sql --relationship RELATIONSHIP_NAME -e BRANCH_NAME < my_database_backup.sql

Sanitizing data Anchor to this heading

To ensure people who review code changes can’t access personally identifiable information stored in your database, sanitize your preview environments.

Replication Anchor to this heading

In non-Dedicated environments, there is no on-site primary/replica supports. In Dedicated environments, it’s provided automatically as part of the default configuration.

In rare cases (such as for certain backup purposes), you can also enable remote replication to your own replica data. The replica isn’t available to your application.

Troubleshoot Anchor to this heading

If you run into issues, troubleshoot MySQL.

Is this page helpful?