0

I have installed MariaDB v10.2.8 using Homebrew on my Macbook pro OSX Sierra, installation went ok, Service started on port 3816 and I can connect and interact with the databases via the command line: Screenshot Command Line

And I have Sequel Pro, which came with MAMP Pro (That I have installed and running with a copy of MySQL 5.6.35 on port 3306). Which only let me connect via socket to this database apparently on port 3306 as per this screenshot: Screenshot SequelPro

Now I'm trying to connect a PHP (Codeigniter) app to this database and nothing works, I get "Unable to connect to your database server using the provided settings."

This are Codeigniter settings

$active_group = 'default';
$active_record = TRUE;
$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'user';
$db['default']['password'] = '123';
$db['default']['port'] = 3816;
$db['default']['database'] = 'dbname';
$db['default']['dbdriver'] = 'mysqli';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = FALSE;
$db['default']['stricton'] = FALSE;

Any ideas what could be wrong and how to fix it?

ramono
  • 462
  • 5
  • 16
  • Did you try using an IP address? Many clients will only use the socket for "localhost". Even 127.0.0.1 should work. – ldg Aug 25 '17 at 00:14
  • Using 127.0.0.1 on the CI application I get (HY000/2002): Connection refused – ramono Aug 25 '17 at 00:19
  • [From this link](https://stackoverflow.com/a/29400976/3585500) that error message is because the port number isn't right. To set the port number in config use `$db['default']['port'] = 5432;` [which is documented at the bottom of this page](https://www.codeigniter.com/user_guide/database/configuration.html). – ourmandave Aug 25 '17 at 00:28
  • CI has the correct port set in the config `$db['default']['port'] = 3816;` as per the first screenshot – ramono Aug 25 '17 at 00:30
  • You should show us your config from CodeIgniter rather than show us some useless pics. – Brian Gottier Aug 25 '17 at 00:38
  • Maybe useless to you, maybe not for others, don't understand the attitude. I've added the config from CI, which I don't believe is the problem here. – ramono Aug 25 '17 at 00:45
  • Your CI config username, password, and database name are correct? – Brian Gottier Aug 25 '17 at 00:51
  • Yes, they are the same details shown above in the command line and you can see they work fine in there – ramono Aug 25 '17 at 00:53
  • terminal shows user name as ciosa, but CI config shows "user" – Brian Gottier Aug 25 '17 at 00:55
  • The real one is fine, I've also edited the db name for short, I'm sure that's not the problem. – ramono Aug 25 '17 at 00:58
  • You should attempt to connect via a plain PHP script using http://php.net/manual/en/function.mysqli-connect.php or http://php.net/manual/en/pdo.connections.php . That should satisfy you that the problem isn't CodeIgniter, but probably a setting internal to PHP itself. I'm on Linux, not Mac, but have experienced connection problems when switching out MySQL for MariaDB. It sounds like you have both on the same server, which I expect would cause some sort of conflict. I'll look around for you and report back if I find anything. – Brian Gottier Aug 25 '17 at 01:26

1 Answers1

1

The problem was MAMP Pro, and the path for mysql.default_socket:

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
mysql.default_socket = /Applications/MAMP/tmp/mysql/mysql.sock

I left the value empty, restarted the server and it worked.

To elaborate: after a lot of time and reading, I've noticed how Sequel Pro doesn't allow me to use localhost as a host if the "Standard" connection with an error message saying "You have entered 'localhost' for a non-socket connection. [ Connect via socket ] [ Use 127.0.0.1 ]".

Having tried the IP, tried the socket and first attempted to use the same path as in the my.cfn.default file which contains the settings for MariaDB, and it failed, so I left the field empty and it worked.

An online post pointed out how the php.ini file contains the path for mysql sockets, I went and found they were pointing to MAMP specific versions of it, so I deleted the values as the comment mentions if empty it uses the built-in

This means other virtual hosts using this particular version of PHP + the regular MySQL will probably not work, but at least I know where to fix it.

ramono
  • 462
  • 5
  • 16