Connecting to MariaDB with unix_socket

Questions
,
1

Silverstripe Version 3 and 4

Does anyone know how to connect to a db using unix_socket?

I.e., What parameters do I need in my environment file?

2

If you do not specify a port and a host (or, according to this comment, if your host is localhost) the PDO-MySQL module will try to use the socket specified in default_socket.

3

Thanks for the reply, sorry to respond so late…

I would expect exactly that behavior, but using this in .env:

SS_DATABASE_CLASS      ="MySQLPDODatabase"
SS_DATABASE_NAME       ="mydb"
SS_ENVIRONMENT_TYPE    ="live"

or any combination of adding the hostname of localhost or 127.0.0.1 results in this error:

[Unknown Error] Uncaught PDOException: SQLSTATE[HY000] [1045] Access denied for user ''@'localhost' (using password: NO)
GET /
Line 207 in /var/www/html/mywebsite/vendor/silverstripe/framework/src/ORM/Connect/PDOConnector.php

198         if (self::is_emulate_prepare()) {
199             $options[PDO::ATTR_EMULATE_PREPARES] = true;
200         }
201 
202         // May throw a PDOException if fails
203         $this->pdoConnection = new PDO(
204             $driver . implode(';', $dsn),
205             empty($parameters['username']) ? '' : $parameters['username'],
206             empty($parameters['password']) ? '' : $parameters['password'],
207             $options
208         );
209 
210         // Show selected DB if requested
211         if ($this->pdoConnection && $selectDB && !empty($parameters['database'])) {
212             $this->databaseName = $parameters['database'];
213         }

Seems like PDOConnector.php is expecting a user/pass authentication and not checking for unix_socket otherwise?

4

You’ll need to provide a username at a minimum, yes… regardless of how you connect, the database server still needs to know what privileges to load for the connection. If you have the user configured for password-free access, then you don’t need a password, otherwise you’ll need that too.

5

OK, so this seems to be getting me a step closer, kinda.

Using this:

SS_DATABASE_CLASS           ="MySQLPDODatabase"
SS_DATABASE_NAME            ="mydatabase"
SS_DATABASE_USERNAME        ="myuser"
SS_ENVIRONMENT_TYPE         ="dev"

Gets me this error:

[Unknown Error] Uncaught PDOException: SQLSTATE[HY000] [1698] Access denied for user 'myuser'@'localhost'
Source
198         if (self::is_emulate_prepare()) {
199             $options[PDO::ATTR_EMULATE_PREPARES] = true;
200         }
201 
202         // May throw a PDOException if fails
203         $this->pdoConnection = new PDO(
204             $driver . implode(';', $dsn),
205             empty($parameters['username']) ? '' : $parameters['username'],
206             empty($parameters['password']) ? '' : $parameters['password'],
207             $options
208         );
209 
210         // Show selected DB if requested
211         if ($this->pdoConnection && $selectDB && !empty($parameters['database'])) {
212             $this->databaseName = $parameters['database'];
213         }
Trace
PDO->__construct(<filtered>, <filtered>, <filtered>, <filtered>) 
PDOConnector.php:207

So I’m no longer getting no user/pass error, but I still don’t seem to understand what I’m missing.

EDIT: This user does in fact have privileges to access this database. This all works perfectly fine if I remove the unix_socket plugin and authenticate via user/pass.

6

Just to clarify… is the user set up with the privileges to access the database using a password or without a password?

If the user needs a password to access the database, then you need to add it to your .env file. As I said earlier, no matter whether you’re using a socket or a TCP connection, the database server still needs to authenticate you to load privileges.

Using ‘localhost’ (or nothing) in the DB host, should make the PDO connector try and use a socket to make the connection. Once that connection to the database server is made, then the authentication takes place.

7

This is currently how my mysql.user table looks:

+-----------+----------+-------------+
| user      | password | plugin      |
+-----------+----------+-------------+
| root      |          | unix_socket |
| myuser    |          | unix_socket |
+-----------+----------+-------------+

I get a general access denied error when connecting with either user. And both users have permissions to access the database. I’ve tried adding a password, but still get a general access denied error.

The only way to make a connection is to remove the unix_socket plugin, add a password and connect via user/pass.

I feel like I’ve tried every possible combination possible and no luck.

FWIW: From the command line while SSH’d in I can access the database by simply typing mysql and I’m in.

8

OK, I’ve just twigged how you are set up! This is the unix_socket plugin for MariaBD, rather than just the PDO connector using a unix socket for the connection.

If you’re using SSH as a root user, that makes sense, since the plugin will get the username of the current process and use that… root is a valid user.

If the webserver is initiating the connection, then the plugin will be trying to use that for the username. Is your webserver process running as myuser?

It may be necessary to change some of the PDO connection parameters to make it work with that authentication plugin…

9

You, sir, just made my day.

I failed to note that Apache is using the ‘www-data’ user, while from the command line I’m using another privileged user. Of course!

Thank you! This works now.

Added the www-data user, granted privileges to the database and set to connect with unix_socket:

+-----------+-----------+-------------+
| user      | password  | plugin      |
+-----------+-----------+-------------+
| root      |           | unix_socket |
| myuser    |           | unix_socket |
| www-data  |           | unix_socket |
+-----------+-----------+-------------+

Then my .env file looks like this now:

SS_DATABASE_CLASS     ="MySQLPDODatabase"
SS_DATABASE_NAME      ="mydatabase"
SS_DATABASE_USERNAME  ="www-data"
SS_ENVIRONMENT_TYPE   ="dev"
1 Like