MariaDB: Using MariaDB with PHP

This documentation is part of the Getting started guide. View the full guide here: How to get started with MariaDB.

👋 Welcome to the Stackhero documentation!

Stackhero offers a ready-to-use MariaDB cloud solution that provides a host of benefits, including:

Unlimited connections and transfers.

phpMyAdmin web UI included.

Effortless updates with just a click.

Optimal performance and robust security powered by a private and dedicated VM.

Save time and simplify your life: it only takes 5 minutes to try Stackhero's MariaDB cloud hosting solution!

Here are a few examples that show how you can connect to MariaDB from PHP using different extensions. While these examples use the "root" database, it is best to create a dedicated database and user for your application, especially for production environments.

Using MariaDB with PHP and MySQLi (object-oriented style)

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port     = '<PORT>';
$user     = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // For demonstration only. For best practice, create your own database and user in phpMyAdmin and use those credentials.

$mysqli = mysqli_init();
$mysqliConnected = $mysqli->real_connect($hostname, $user, $password, $database, $port, NULL, MYSQLI_CLIENT_SSL);
if (!$mysqliConnected) {
  die('Connection Error: ' . $mysqli->connect_error);
}

echo 'Connection successful... ' . $mysqli->host_info . "\n";

$mysqli->close();

?>

Using MariaDB with PHP and MySQLi (procedural style)

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port     = '<PORT>';
$user     = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // For demonstration only. For best practice, create your own database and user in phpMyAdmin and use those credentials.

$mysqli = mysqli_init();
$mysqliConnected = mysqli_real_connect($mysqli, $hostname, $user, $password, $database, $port, NULL, MYSQLI_CLIENT_SSL);
if (!$mysqliConnected) {
  die('Connection error: ' . mysqli_connect_error($mysqli));
}

echo 'Success: ' . mysqli_get_host_info($mysqli) . "\n";

mysqli_close($mysqli);

?>

Using MariaDB with PHP and PDO

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port     = '<PORT>';
$user     = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // For demonstration only. For best practice, create your own database and user in phpMyAdmin and use those credentials.

$dsn = "mysql:host=$hostname;port=$port;dbname=$database";

$options = array(
  // If you get an error like "Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed", make sure your /etc/ssl/certs/ directory contains CA certificates.
  PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/',
  // PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem',
  PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
);

$pdo = new PDO($dsn, $user, $password, $options);

$stm = $pdo->query('SELECT VERSION()');
$version = $stm->fetch();

echo 'You are connected to a database running version ' . $version[0] . "\n";

?>

How to resolve the "SSL operation failed with code 1" error

If you see this error:

Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed

it is likely because the /etc/ssl/certs/ directory is missing the necessary CA certificates. If you have system access, here are some suggestions for installing them:

On Ubuntu, you might run:
```
apt-get install ca-certificates
```
On Alpine Linux, try:
```
apk add ca-certificates
```

If you do not have system-level access, you can add the certificate manually:

Download the certificate: https://letsencrypt.org/certs/isrgrootx1.pem
Place the isrgrootx1.pem file into your PHP project.
Comment out the line with PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/'
Uncomment the line with PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem'

How to resolve "Fatal error: Uncaught Error: Undefined constant PDO::MYSQL_ATTR_SSL_CAPATH"

If you see an error like this:

Fatal error: Uncaught Error: Undefined constant PDO::MYSQL_ATTR_SSL_CAPATH

or a similar message referencing an undefined constant for PDO MySQL attributes, your PDO installation probably does not include MySQL support.

On Ubuntu/Debian

You can install the required PHP MySQL extension with:

sudo apt-get install php-mysql

If you are using Docker

To make sure MySQL support is available, you can add the following to your Dockerfile:

RUN docker-php-ext-install pdo pdo_mysql

Using MariaDB with Symfony and Doctrine

To get started, edit your .env file and set the DATABASE_URL variable like this:

DATABASE_URL="mysql://<USER>:<PASSWORD>@<XXXXXX>.stackhero-network.com:<PORT>/<DATABASE>"

Next, update your config/packages/doctrine.yaml file to set the driver and options:

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        driver: 'pdo_mysql'
        options:
            # PDO::MYSQL_ATTR_SSL_CAPATH
            1010: '/etc/ssl/certs'
            # PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT
            1014: true

How to fix the "SSL operation failed with code 1" error

If you run into this error:

Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed

it is probably because your system does not have the CA certificates installed. Here are a couple of ways you can install them:

On Ubuntu/Debian, you can run:
```
sudo apt-get install ca-certificates
```
On Alpine Linux, try:
```
apk add ca-certificates
```

If you cannot install CA certificates system-wide, you can add them manually:

Download the certificate: https://letsencrypt.org/certs/isrgrootx1.pem
Place the isrgrootx1.pem file in your Symfony project.

Update your config/packages/doctrine.yaml file:

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        driver: 'pdo_mysql'
        options:
            # PDO::MYSQL_ATTR_SSL_CA
            1009: 'isrgrootx1.pem'
            # PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT
            1014: true

Using MariaDB with Laravel

To configure MariaDB with Laravel, open config/database.php and update the mysql configuration like this:

'mysql' => [
  'driver'   => 'mysql',
  'host'     => env('STACKHERO_MARIADB_HOST'),
  'port'     => env('STACKHERO_MARIADB_PORT'),
  'username' => env('STACKHERO_MARIADB_USER'),
  'password' => env('STACKHERO_MARIADB_PASSWORD'),
  'database' => env('STACKHERO_MARIADB_USER'),
  'charset'  => 'utf8mb4',
  'collation'=> 'utf8mb4_unicode_ci',
  'prefix'   => '',
  'prefix_indexes' => true,
  'strict'   => true,
  'engine'   => null,
  'sslmode'  => 'require',
  'options'  => extension_loaded('pdo_mysql')
    ? array_filter([
      // If you run into SSL errors like "Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed", see the troubleshooting steps above.
      PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/',
      // PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem',
      PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
    ])
    : [],
],

Using MariaDB with PHP CodeIgniter

Inside your database.php file, you can set up the following configuration:

$db['default'] = array(
  'hostname' => getenv('STACKHERO_MARIADB_HOST'),
  'port'     => getenv('STACKHERO_MARIADB_PORT'),
  'username' => getenv('STACKHERO_MARIADB_USER'),
  'password' => getenv('STACKHERO_MARIADB_PASSWORD'),
  'database' => getenv('STACKHERO_MARIADB_USER'), // By convention, the database name matches the username.
  'dbdriver' => 'mysqli',
  'dbprefix' => '',
  'pconnect' => TRUE,
  'char_set' => 'utf8',
  'dbcollat' => 'utf8_general_ci',
  'encrypt'  => array() // Important: activate TLS encryption
);

Connecting MariaDB with PHP using environment variables

A good practice is to keep your credentials out of your code by using environment variables. You can retrieve them like this:

$hostname = getenv('STACKHERO_MARIADB_HOST');
$port     = getenv('STACKHERO_MARIADB_PORT');
$user     = getenv('STACKHERO_MARIADB_USER');
$password = getenv('STACKHERO_MARIADB_PASSWORD');
$database = getenv('STACKHERO_MARIADB_USER'); // By convention, the database name matches the username.