Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Posted by: Anoxe (---.dip0.t-ipconnect.de)
Date: November 09, 2018 10:58AM

Hi,
I have obtained the following error when connecting to a database from an external host:

Can not connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I have a website in an external domain and I try to access a database that I have at home. I have a fixed IP in the connection where the database is.

I have Wampserver in windows 10 installed and it works perfectly locally and remotely. The problem appears only when I access from the web that is in an external domain.

WampServer Version 3.1.3 64bit
______________________ Versions used ______________________
Apache 2.4.33 - PHP 7.2.4
MySQL 5.7.21
MariaDB 10.2.14
PHP 5.6.35 for CLI (Command-Line Interface)

Regards,
Anoxe

Options: ReplyQuote
Re: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Posted by: Otomatic (Moderator)
Date: November 10, 2018 12:35PM

Hi,

On the MySQL server you must create the appropriate users with the IP of origin, i.e. the one of the machine from which you want to connect

I apologize for my bad English (I'm French). I hope you understand what I am trying to explain.

With MySQL users, it is necessary to pay attention to a possible ambiguity of accounts.
The user-host-password triplet words given by the one who wants to connect will not be one that will be taken into account by MySQL due to priority rules.

Different accounts created coexist, possibly with passwords or different privileges. Because wildcards and anonymous connections, it is common that a connection attempt may correspond to multiple accounts.

Here, for example, some accounts may be created.
Example User table reading via MySQL console:
SELECT User, Host, Password FROM mysql.User ;
User        Host           Password
root        localhost
tartempion  %              *EE33294C97C42FFF14EA73AB81E428A6FC7B9A8B
lui         localhost      *14E65567ABD85135DOCFD9A7083032C179A49EE7
            %
foo         localhost      *14E65567ABDB5135DOCFD9A70B3032C179A49EE7
foo         monordi
foo         192.168.1.123
foo         192.168.%
foo         %.microapp.com
foo         %
            localhost
If foo attempts to connect locally, this not only corresponds to foo@localhost but also foo@'%', ''@localhost '@'%'. However, some of these accounts are protected by a password and some not. How MySQL does choose?
-- MySQL first selects the host expressed in the most specific way (so localhost preferably at %).
-- It then chooses the name of the most specific user (so foo rather than anonymous account).
In this case, it will be well foo@localhost will prevail; foo will have to use his password.

What will happen if tartempion attempts to connect locally with his password?
C:\>mysql --user=tartempion --password=sesame --host=localhost
ERROR 1045 (28000): Access denied for user 'tartempion'8'localhost' (using password : YES)

Indeed, applying the above rules, MySQL prefers an anonymous account on a specific host to an account nominated on a generic host. It is therefore ''@localhost without password, which overrides tartempion@'%'.

------------------------------------------------------------------------------------------------------------
Wampserver 3.1.5 32 bit - Apache 2.4.37 - PHP 7.2.12/7.1.24/7.0.32/5.6.38 - MySQL 5.7.23 - MariaDB 10.3.10
Wampserver 3.1.5 64 bit - Apache 2.4.37 - PHP 7.2.12/7.1.24/7.0.32/5.6.38 - MySQL 5.7.23 - MariaDB 10.3.10
PhPMyadmin 4.8.3 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote


Sorry, only registered users may post in this forum.