3

I'd like to have my PHP applications such as Joomla make use of the UNIX socket to connect to the local mysql database.

I've already configured the php.ini with:

mysqli.default_socket = /var/run/mysqld/mysqld.sock
mysql.default_socket = /var/run/mysqld/mysqld.sock
pdo_mysql.default_socket=/var/run/mysqld/mysqld.sock

But in Joomla I can only set localhost or 127.0.0.1 as database, is it possible to override this? How can I test the UNIX socket is actually being used?

My MySQL/MariaDB is configured like:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
bind-address        = 127.0.0.1
max_connections     = 500
connect_timeout     = 10
wait_timeout        = 600
max_allowed_packet  = 16M
thread_cache_size       = 1000
sort_buffer_size    = 512M
bulk_insert_buffer_size = 16M
tmp_table_size      = 8G
max_heap_table_size = 8G

myisam_recover          = FORCE,BACKUP
key_buffer_size     = 128M
open-files-limit    = 65535
table_open_cache    = 10240
table_open_cache_instances = 8
table-definition-cache = 4096
myisam_sort_buffer_size = 512M
key-cache-segments=8
concurrent_insert   = 2
read_buffer_size    = 32M
read_rnd_buffer_size    = 64M

query_cache_limit       = 96M
query_cache_size        = 128M
query_cache_min_res_unit=7108
query_cache_type        = 1

log_warnings        = 2

slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_verbosity  = query_plan


log_bin         = /var/log/mysql/mariadb-bin
log_bin_index       = /var/log/mysql/mariadb-bin.index
sync_binlog     = 1
expire_logs_days    = 14
max_binlog_size         = 100M

default_storage_engine  = InnoDB
innodb_log_file_size    = 50M
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances=8
innodb_log_buffer_size  = 32M
innodb_file_per_table   = 1
innodb_concurrency_tickets=5000
innodb_open_files   = 240000
innodb_io_capacity  = 240000
innodb_flush_method = O_DIRECT
innodb-log-files-in-group      = 2
innodb-flush-log-at-trx-commit = 1

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 512M

!includedir /etc/mysql/conf.d/
Andrew Schulman
  • 8,561
  • 21
  • 31
  • 47
gijs007
  • 107
  • 1
  • 4
  • 17

2 Answers2

4

127.0.0.1 is used for TCP socket.

'localhost' is used for Unix filesystem socket.

We can use netstat -ln | grep 'mysql' to determine the connection method.And explore option to enforce a particular type

shell> mysql --host=127.0.0.1
shell> mysql --protocol=TCP
shell> mysql --host=localhost
shell> mysql --host=localhost --protocol=TCP

The connection parameters should be like this

$link = mysql_connect('localhost:/var/run/mysqld/mysqld.sock', 'mysql_user', 'mysql_password');

var $host = ':/var/run/mysqld/mysqld.sock';
var $user = 'your_user_db_name';
var $db = 'your_db_name';
var $password = 'your_db_password';
koustuv
  • 656
  • 7
  • 11
  • When using localhost joomla is unable to connect to the MySQL server: Error displaying the error page: Application Instantiation Error: Could not connect to MySQL. I thought it was because of the line; skip-name-resolve in the mysql config, but after removing this and rebooting the mysql server the issue persists. – gijs007 Mar 08 '15 at 17:20
  • checkout ':/var/run/mysqld/mysqld.sock' and confirm. I mean $config['db_hostname'] = ':/var/run/mysqld/mysqld.sock'; Make sure you have right permission on the file in use. Reboot mysql if needed. – koustuv Mar 08 '15 at 17:23
  • 1
    How can I open the socket "file"? Nano says it doesn't exist but when looking it up with the LS command its there (in pink) Where should I place the: $config['db_hostname'] = ':/var/run/mysqld/mysqld.sock' – gijs007 Mar 08 '15 at 17:27
  • Joomla configuration.php you will get public $host – koustuv Mar 08 '15 at 17:30
  • When I do that I get a blank page. – gijs007 Mar 08 '15 at 17:34
  • Can you post $host and $dbtype here – koustuv Mar 08 '15 at 17:35
  • Let us [continue this discussion in chat](http://chat.stackexchange.com/rooms/21760/discussion-between-mysqlrockstar-and-gijs007). – koustuv Mar 08 '15 at 17:36
  • Using "localhost" as the hostname means that PHP will try to connect to the local UNIX socket before trying via TCP/IP. –  Mar 08 '15 at 19:09
  • 1
    If you are using mysqli instead of the now-deprecated mysql, the socket path is now a separate parameter. Trying to apply this answer to mysqli will not work. – Vladimir Panteleev Oct 26 '18 at 23:23
0

In libraries/joomla/database/database.php is defined the abstract class JDatabase, which then is implemented for different database types in libraries/joomla/database/driver/, in this case mysql or mysqli..

In mysqli.php (Joomla! 3.3.6) __construct($options) sets defaults if nothing specified in configuration.php, which will change it into 'localhost'. From line 64:

public function __construct($options)
{
// Get some basic values from the options.
$options['host']     = (isset($options['host'])) ? $options['host'] : 'localhost';
$options['user']     = (isset($options['user'])) ? $options['user'] : 'root';
$options['password'] = (isset($options['password'])) ? $options['password'] : '';
$options['database'] = (isset($options['database'])) ? $options['database'] : '';
$options['select']   = (isset($options['select'])) ? (bool) $options['select'] : true;
$options['port']     = null;
$options['socket']   = null;

// Finalize initialisation.
parent::__construct($options);
}

Then, function connect(), finally uses them to create connection on lines 163-165:

    $this->connection = @mysqli_connect(
        $this->options['host'], $this->options['user'], $this->options['password'],
        null, $this->options['port'], $this->options['socket']
    );

If nothing else given, mysqli_connect() uses defaults from php.ini for hostname, port and socket

mysqli mysqli_connect ([ string $host = ini_get("mysqli.default_host") 
   [, string $username = ini_get("mysqli.default_user") 
   [, string $passwd = ini_get("mysqli.default_pw") 
   [, string $dbname = "" 
   [, int $port = ini_get("mysqli.default_port") 
   [, string $socket = ini_get("mysqli.default_socket") ]]]]]] )

Now you know the whole track affecting these connection parameters. By editing mysqli.php you should be able to enforce settings the configuration.php doesn't allow you to change. However, you should be careful and consider documenting these modifications well, since

  1. configuration.php doesn't work like documented anymore
  2. Joomla updates can (and eventually will) override your modifications
  3. Something else may be depending on this particular configuration
Esa Jokinen
  • 43,252
  • 2
  • 75
  • 122