MariaDB is a MySQL-compatible relational database system. Its XtraDB storage engine is equivalent to MySQL with InnoDB.
To use it in your application, add it to
1 2 3 4
mydatabase: # supported versions: 5.5, 10.0, 10.1, 10.2 type: mysql:10.2 disk: 1024
And wire it in
relationships: database: "mydatabase:mysql"
The configuration is exposed via the following environment variables (where
DATABASE is the upper-cased version of the key defined in the relationship
DATABASE_URL: The database URL (in the PHP or Go format depending on your application)
DATABASE_SERVER: The database server
DATABASE_DRIVER: The database driver
DATABASE_VERSION: The database version
DATABASE_HOST: The database host
DATABASE_PORT: The database port
DATABASE_NAME: The database name
DATABASE_DATABASE: Alias for
DATABASE_USERNAME: The database username
DATABASE_PASSWORD: The database password
The database version and a default charset is included in the database URL.
One can override them using the
DATABASE_CHARSET environment variables respectively.
The password is usually empty when using only the default endpoint on MySQL/MariaDB. It will be filled in when you define custom endpoints. This does not create a security risk as port access is tightly restricted to your application container.
Connecting to the Database¶
From the Application Container¶
On Symfony projects using Doctrine or Laravel projects, no configuration is needed.
For other applications, use the defined environment variables to configure your framework of choice.
1. There is a single MySQL user, so you can not use "DEFINER" Access Control mechanism for Stored Programs and Views.
2. MySQL Errors such as "PDO Exception 'MySQL server has gone away'" are
usually simply the result of exhausting your existing diskspace. Be sure you
have sufficient space allocated to the service in
From the Command Line¶
To run commands on your MariaDB server, run the following command:
$ symfony sql 'SHOW TABLES'
Export your database in an SQL file or in a compressed file and import it via the following command:
$ symfony sql < database.sql
Dump data stored in your SymfonyCloud database via the following command:
$ symfony db:dump > database.sql
The system timezone is set to UTC by default. Change it for the current
SQL SET time_zone = <timezone>;.
If you are using version 10.0 or later of this service it is possible to define
multiple databases as well as multiple users with different permissions. To do
so requires defining multiple endpoints. Under the
configuration key of your
service, there are two additional keys:
schemas: This is a YAML array listing the databases that should be created. If not specified, a single database named
mainwill be created.
endpoints: This is a nested YAML array defining different credentials. Each endpoint may have access to one or more schemas (databases), and may have different levels of permission on each. The valid permission levels are:
ro: Only SELECT queries are allowed
rw: SELECT queries as well INSERT/UPDATE/DELETE queries are allowed
admin: All queries are allowed, including DDL queries (CREATE TABLE, DROP TABLE, ...)
Consider the following illustrative example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
mysqldb: type: mysql:10.2 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
This creates a MySQL/MariaDB service named
mysqldb which has two databases,
legacy. There will be three endpoints created. The first, named
admin, will have full access to both databases. The second,
will have SELECT query access to the
main DB but no access to
importer user will have SELECT/INSERT/UPDATE/DELETE access (but not
DDL access) to the
legacy database but no access to
If a given endpoint has access to multiple databases you should also specify
which will be listed by default in the relationships array. If one isn't
specified the path property of the relationship will be
null. While that
may be acceptable for an application that knows the name of the database to
connect to, it would mean that automated tools such as the Symfony CLI will
not be able to access the database on that relationship. For that reason the
default_schema property is always recommended.
Once those endpoints are defined, you need to expose them to your application as
a relationship. Continuing with our example, this would be a possible
corresponding block from
1 2 3 4
relationships: database: "mysqldb:admin" reports: "mysqldb:reporter" imports: "mysqldb:importer"
This block defines three relationships,
imports. They'll be available as environment variables with the
IMPORTS_ prefixes. Use those to connect to
the appropriate database with the specified restrictions.
configuration block is specified at all, it is equivalent to the
1 2 3 4 5 6 7 8
configuration: schemas: - main endpoints: mysql: default_schema: main privileges: main: admin
If either schemas or endpoints are defined, then no default will be applied and you must specify the full configuration.
For version 10.2 and later, some MariaDB configuration properties from the
my.cnf file can be customized:
1 2 3 4 5 6 7
mysqldb: type: mysql:10.2 disk: 2048 configuration: properties: # size in Mb (defaults to 16) max_allowed_packet: 64
Note that increasing the size of the maximum packet will also automatically
max_connections value. The number of connections allowed will
depend on the packet size and the memory available to the service. In most cases
leaving this value at the default is recommended.
Another possible cause of "MySQL server has gone away" errors is a server timeout. MySQL has a built-in timeout for idle connections, which defaults to 10 minutes. Most typical web connections end long before that is ever approached, but it's possible that a long-running worker may idle and not need the database for longer than the timeout. In that case the same "server has gone away" message may appear.
If that's the case, the best way to handle it is to wrap your connection logic in code that detects a "server has gone away" exception and tries to re-establish the connection.
Alternatively, if your worker is idle for too long it can self-terminate. SymfonyCloud will automatically restart the worker process, and the new process can establish its own new database connection.
This work, including the code samples, is licensed under a Creative Commons BY-SA 3.0 license.