Advanced database tips

# Time zone

MySQL has time zone support. Our Nodes are defaulting to the standard time zone UTC+00 (aka GMT). If you want to change this time zone, you can do so on a "per connection" basis.

There are two approaches to tackle this issue: handle the time zone on application level or handle the time zone on database level. Each has its merits and which one is better strongly depends on the use case.

# GTID flag for import/export

The --set-gtid-purged=OFF option prevents GTID-related permission errors during import. This is required when:

• Your source database has GTID enabled
• You're using MariaDB locally
• You encounter #1227 - Access denied; you need SUPER privilege(s) errors

# Setting time zone in MySQL

The syntax to change the time zone is:

-- set time zone to +3 hours
SET time_zone = '+03:00';

-- set time zone to -7 hours
SET time_zone = '-07:00';
sql

You can query the current time zone like so:

SELECT @@session.time_zone;
sql

# Setting time zone with PDO

PDO offers configurable options when setting up the connection. One of them allows you to issue commands right after initialization (connection).

$pdo = new PDO(
    'mysql:host='. getenv('MYSQL_HOST'). ';dbname='. getenv('MYSQL_DATABASE'),
    getenv('MYSQL_USER'),
    getenv('MYSQL_PASSWORD'),
    array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET time_zone = \'+02:00\''
    )
);
php

# Resetting the MySQL password

Instead of looking up the existing MySQL password, you can also reset it. Do so in the Dashboard > app > environment > MySQL. Please mind that this comes with consequences:

• Unless your are using env vars: You'll need to change the password in your code configuration files
• Your coworkers need to change their password in their locally configured remote access tools (see below)

# Accessing MySQL from a different environment

It is possible to access a MySQL from another environment within the same region (EU, US). The database from environment-A can be accessed from environment-B. This works since both environments are within the same network. You will only need to update the MySQL access credentials to do so. There are not many good use cases for this, but it might be good to know.

# Accessing the remote MySQL from your local environment

It is also possible to access the fortrabbit database from the local installation. You will need to open a tunnel, as described above to do so. While possible we do not recommend that approach. You local App will feel slow, unless you have a very good internet connection. Further, you will run the risk of messing up the database if several apps are writing to it at the same time. The best practice here is separating the local development environment from production.

# MySQL limits

Each App has one database named like the App. The mysql-user you have received is not granted the privileges to CREATE DATABASE. Please mind that CREATE SCHEMA requires the same permission.

# Using MySQL functions, procedures and triggers

By default you don't have permissions to create MySQL functions, procedures and triggers as it requires SUPER privileges.