[php] PDOException SQLSTATE[HY000] [2002] No such file or directory

I believe that I've successfully deployed my (very basic) site to fortrabbit, but as soon as I connect to SSH to run some commands (such as php artisan migrate or php artisan db:seed) I get an error message:

[PDOException]
SQLSTATE[HY000] [2002] No such file or directory

At some point the migration must have worked, because my tables are there - but this doesn't explain why it isn't working for me now.

This question is related to php mysql laravel pdo

The answer is


I encountered the [PDOException] SQLSTATE[HY000] [2002] No such file or directory error for a different reason. I had just finished building a brand new LAMP stack on Ubuntu 12.04 with Apache 2.4.7, PHP v5.5.10 and MySQL 5.6.16. I moved my sites back over and fired them up. But, I couldn't load my Laravel 4.2.x based site because of the [PDOException] above. So, I checked php -i | grep pdo and noticed this line:

pdo_mysql.default_socket => /tmp/mysql.sock => /tmp/mysql.sock

But, in my /etc/my.cnf the sock file is actually in /var/run/mysqld/mysqld.sock.

So, I opened up my php.ini and set the value for pdo_mysql.default_socket:

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

Then, I restarted apache and checked php -i | grep pdo:

pdo_mysql.default_socket => /var/run/mysqld/mysqld.sock => /var/run/mysqld/mysqld.sock

That fixed it for me.


I got the same problem and I'm running Mac OS X 10.10 Yosemite. I have enabled the Apache Server and PHP that already comes with the OS. Then I just configured the mCrypt library to get started. After that when I was working with models and DB I got the error:

[PDOException]
SQLSTATE[HY000] [2002] No such file or directory

The reason I found is just because PHP and MySQL can't get connected themselves. To get this problem fixed, I follow the next steps:

  1. Open a terminal and connect to the mysql with:

    mysql -u root -p
    
  2. It will ask you for the related password. Then once you get the mysql promt type the next command:

    mysql> show variables like '%sock%'
    
  3. You will get something like this:

    +-----------------------------------------+-----------------+
    | Variable_name                           | Value           |
    +-----------------------------------------+-----------------+
    | performance_schema_max_socket_classes   | 10              |
    | performance_schema_max_socket_instances | 322             |
    | socket                                  | /tmp/mysql.sock |
    +-----------------------------------------+-----------------+
    
  4. Keep the value of the last row:

    /tmp/mysql.sock
    
  5. In your laravel project folder, look for the database.php file there is where you configure the DB connection parameters. In the mysql section add the next line at the end:

    'unix_socket' => '/tmp/mysql.sock'
    
  6. You must have something like this:

    'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'SchoolBoard',
            'username'  => 'root',
            'password'  => 'venturaa',
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
            'unix_socket' => '/tmp/mysql.sock',
        ),
    

Now just save changes, and reload the page and it must work!


In my case, I was running php artisan migrate on my mac terminal, when I needed to ssh into vagrant and run it from there. Hope that helps someone the headache.


If you are using Laravel Homestead, make sure you're calling the commands on the server.

homestead ssh

Then simply cd to the right directory and fire your command there.


My answer is specific to Laravel.

I had this message after creating a new connection in the database.php configuration file to a local Docker MySQL service and setting it as the default connection. I forgot that I was setting a different connection by overwriting it in the Model:

class Model extends \Illuminate\Database\Eloquent\Model
{
    protected $connection;

    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);
        $this->connection = 'some_other_connection';
    }
...

So even if my default connection in the database.php file was pointing to the right credentials, the model was still using the remote database connection configuration which I had removed from the local environment file.


If anyone are still looking for the answer, just check your .env file. For some reason laravel create a .env.example file, so all this answers didn't work for me. I fixed my issue renamming .env.example to .env


In may case, I'd simply used

vagrant up

instead of

homestead up

for my forge larval setup using homestead. I'm assuming this meant the site was getting served, but the MySQL server wasn't ever booted. When I used the latter command to launch my vagrant box, the error went away.


For anyone trying to create a fresh db connection not on laravel but bumped here seeking for answers to run PDO from the Terminal. This would be of help to you. And you can refactor it to work best for you.

<?php

class db
{
   private $DBHOST = 'localhost'; // you don't need 127.0.0.1
   private $DRIVER = 'mysql';
   private $PORT   = '8888'; // database port. 8888 is mine
   private $DB     = 'example-db';
   private $PASS   = 'example-pass';
   private $USER   = 'root';
   private $SOCKS  = ''; // can fill this or leave blank.


   // - connect (dummy connection)
   private function con()
   {
       if ($this->SOCKS == '')
       {
           // run shell command to get 
           $socks = shell_exec('netstat -ln | grep mysql');
           $socks = trim(substr($socks, strpos($socks, '/')));

           $this->SOCKS = strlen($socks) > 0 ? ';unix_socket='.$socks : '';
       }
       else
       {
          $this->SOCKS = ';unix_socket='.$this->SOCKS;
       }

       $dsn = $this->DRIVER.':host='.$this->DBHOST.';dbname='.$this->DB;

       // add socks
       $dsn .= $this->SOCKS;

       // add port
       $dsn .= (strlen($this->PORT) > 0) ? ';port='.$this->PORT : '';

       // extablish connection
       $con = new PDO($dsn, $user, $pass);

       // return PDO instance.
       return $con;
   }
   // - ends here

   // now you can call $this->con() within class to use connection 
   // would run fine on any terminal

} 

hope it helps!


All these answers seem like heavy lifting...

I just created a .env file; edited my bootstrap/app.php file, and uncommented the following line...

Dotenv::load(__DIR__.'/../');

Hope this helps someone


I'm running on MAMP Pro and had this similar problem when trying to migrate (create db tables). Tried a few of these mentioned suggestions as well but didn't do it for me.

So, simply (after an hour googling), I added two things to the /config/database.php.

'port' => '1234',
'unix_socket' => '/path/to/my/socket/mysqld.sock'

Works fine now!


Mamp user enable option Allow network access to MYSQL

enter image description here


I had the same problem using Docker and MySQL service name db in docker_compose.yml file:

I added the following in the .env file:

DB_HOST=db

you should also assure that your host is discoverable from the php app.

It was because PHP didn't figure out which host to use to connect.


The answer from @stuyam solved the "No such file or directory" issue for me

Short answer: Change "host" in the /app/config/database.php file from "localhost" to "127.0.0.1"

But then I had a "Connection refused" error. If anyone had the same issue, my solution for this was to update the app/config/local/database.php file so the port is 8889:

'mysql' => array(
        'driver'    => 'mysql',
        'host'      => '127.0.0.1',
        'port'      => '8889',
        'database'  => 'databaseName',
        'username'  => 'root',
        'password'  => 'root',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
    ),

In my case i had no problem at all, just forgot to start the mysql service...

sudo service mysqld start

I ran into this problem when running PHPUnit in Elixir/Gulp, and Homestead as my Vagrant enviroment.

In my case I edited the .env file from DB_HOST=localhost to DB_HOST=192.168.10.10 where 192.168.10.10 is the IP of my Vagrant/Homestead host.


In my case I had to remove the bootstrap/cache folder and try it again.

My cenario was after a server migration.


This happened to me because MySQL wasn't running. MySQL was failing to start because I had a missing /usr/local/etc/my.cnf.d/ directory.

This was being required by my /usr/local/etc/my.cnf config file as a glob include (include /usr/local/etc/my.cnf.d/*.cnf).

Running mkdir /usr/local/etc/my.cnf.d, and then starting MySQL, fixed the issue.


If you are using Laravel Homestead, here is settings

(include Vagrant-Virtual Machine)

.bash-profile

alias vm="ssh [email protected] -p 2222"

database.php

    'mysql' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST', '127.0.0.1'),
        'database'  => env('DB_DATABASE', 'homestead'),
        'username'  => env('DB_USERNAME', 'homestead'),
        'password'  => env('DB_PASSWORD', 'secret'),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
    ],

Terminal

vm

vagrant@homestead:~/Code/projectFolder  php artisan migrate:install

I had similar problems accessing my Drupal website. I fixed it by opening the command line, and restarting my MySQL server or service:

service mysqld restart

This should work. If it doesn't, restart your local webserver:

service httpd restart

That should be enough. Hope it works for other environments, too. Note that these commands generally require superuser privileges.


Laravel 4: Change "host" in the app/config/database.php file from "localhost" to "127.0.0.1"

Laravel 5+: Change "DB_HOST" in the .env file from "localhost" to "127.0.0.1"

I had the exact same problem. None of the above solutions worked for me. I solved the problem by changing the "host" in the /app/config/database.php file from "localhost" to "127.0.0.1".

Not sure why "localhost" doesn't work by default but I found this answer in a similar question solved in a symfony2 post. https://stackoverflow.com/a/9251924/1231563

Update: Some people have asked as to why this fix works so I have done a little bit of research into the topic. It seems as though they use different connection types as explained in this post https://stackoverflow.com/a/9715164/1231563

The issue that arose here is that "localhost" uses a UNIX socket and can not find the database in the standard directory. However "127.0.0.1" uses TCP (Transmission Control Protocol), which essentially means it runs through the "local internet" on your computer being much more reliable than the UNIX socket in this case.


It worked after I change from DB_HOST=localhost to DB_HOST=127.0.0.1 at .env file


This is because PDO treats "localhost" host specially:

Note: Unix only: When the host name is set to "localhost", then the connection to the server is made thru a domain socket. If PDO_MYSQL is compiled against libmysqlclient then the location of the socket file is at libmysqlclient's compiled in location. If PDO_MYSQL is compiled against mysqlnd a default socket can be set thru the pdo_mysql.default_socket setting.

(from http://php.net/manual/en/ref.pdo-mysql.connection.php)

Changing localhost to 127.0.0.1 will "force" the use of TCP.

Note: mysqli_connect is working fine with localhost.


Add mysql.sock path in database.php file like below example

'unix_socket' => '/Applications/MAMP/tmp/mysql/mysql.sock',

Eample

'mysql' => [
        'driver' => 'mysql',
        'unix_socket' => '/Applications/MAMP/tmp/mysql/mysql.sock',
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '8889'),

As of Laravel 5 the database username and password goes in the .env file that exists in the project directory, e.g.

DB_HOST=127.0.0.1
DB_DATABASE=db1
DB_USERNAME=user1
DB_PASSWORD=pass1

As you can see these environment variables are overriding the 'forge' strings here so changing them has no effect:

    'mysql' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST', 'localhost'),
        'database'  => env('DB_DATABASE', 'forge'),
        'username'  => env('DB_USERNAME', 'forge'),
        'password'  => env('DB_PASSWORD', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
    ],

More information is here https://mattstauffer.co/blog/laravel-5.0-environment-detection-and-environment-variables


I got the same problem in ubuntu 18.04 with nginx. By following the below steps my issue has been fixd:

First open terminal and enter into mysql CLI. To check mysql socket location I write the following command.

mysql> show variables like '%sock%'

I got something like the below :

+-----------------------------------------+-----------------------------+
| Variable_name                           | Value                       |
+-----------------------------------------+-----------------------------+
| mysqlx_socket                           | /var/run/mysqld/mysqlx.sock |
| performance_schema_max_socket_classes   | 10                          |
| performance_schema_max_socket_instances | -1                          |
| socket                                  | /var/run/mysqld/mysqld.sock |
+-----------------------------------------+-----------------------------+
4 rows in set (0.00 sec)

In laravel project folder, look for the database.php file in the config folder. In the mysql section I modified unix_socket according to the above table.

'mysql' => array(
        'driver'    => 'mysql',
        'host'      => '127.0.0.1',
        'database'  => 'database_name',
        'username'  => 'username',
        'password'  => 'password',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'unix_socket' => '/var/run/mysqld/mysqld.sock',
    ),

Now just save changes, and reload the page and it worked.


Check your port carefully . In my case it was 8889 and i am using 8888. change "DB_HOST" from "localhost" to "127.0.0.1" and vice versa


When using a VirtualMachine make sure you ssh into that machine and navigate to your App folder and call the php artisan migrate command from there.


Step 1

Find the path to your unix_socket, to do that just run netstat -ln | grep mysql

You should get something like this

unix  2      [ ACC ]     STREAM     LISTENING     17397    /var/run/mysqld/mysqld.sock

Step 2

Take that and add it in your unix_socket param

'mysql' => array(
            'driver'    => 'mysql',
            'host'      => '67.25.71.187',
            'database'  => 'dbname',
            'username'  => 'username',
            'password'  => '***',
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
            'unix_socket'    => '/var/run/mysqld/mysqld.sock' <-----
            ),
        ),

Hope it helps !!


Attempt to connect to localhost:

SQLSTATE[HY000] [2002] No such file or directory

Attempt to connect to 127.0.0.1:

SQLSTATE[HY000] [2002] Connection refused

OK, just comment / remove the following setting from my.cnf (on OS X 10.5: /opt/local/etc/mysqlxx/my.cnf) to obtain:

[mysqld]
# skip-networking

Of course, stop and start MySQL Server.


I had this problems when I was running my application using docker containers.

The solution was put the name of the MySQL service container I was using in docker_compose.yml on DB_HOST. In my case, it was db :

DB_HOST=db

Hope it helps.


Building on the answer from @dcarrith ...

Instead of editing the config files, I created an alias in the location that PHP is looking that connects to the real mysql.sock. (source)

Just run these two commands (no restart needed):

mkdir /var/mysql
ln -s /tmp/mysql.sock /var/mysql/mysql.sock

Examples related to php

I am receiving warning in Facebook Application using PHP SDK Pass PDO prepared statement to variables Parse error: syntax error, unexpected [ Preg_match backtrack error Removing "http://" from a string How do I hide the PHP explode delimiter from submitted form results? Problems with installation of Google App Engine SDK for php in OS X Laravel 4 with Sentry 2 add user to a group on Registration php & mysql query not echoing in html with tags? How do I show a message in the foreach loop?

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to laravel

Parameter binding on left joins with array in Laravel Query Builder Laravel 4 with Sentry 2 add user to a group on Registration Target class controller does not exist - Laravel 8 Visual Studio Code PHP Intelephense Keep Showing Not Necessary Error The POST method is not supported for this route. Supported methods: GET, HEAD. Laravel How to fix 'Unchecked runtime.lastError: The message port closed before a response was received' chrome issue? Post request in Laravel - Error - 419 Sorry, your session/ 419 your page has expired Expected response code 250 but got code "530", with message "530 5.7.1 Authentication required How can I run specific migration in laravel Laravel 5 show ErrorException file_put_contents failed to open stream: No such file or directory

Examples related to pdo

Pass PDO prepared statement to variables PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes PHP 7 RC3: How to install missing MySQL PDO PHP Connection failed: SQLSTATE[HY000] [2002] Connection refused ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it php artisan migrate throwing [PDO Exception] Could not find driver - Using Laravel How to check if a row exists in MySQL? (i.e. check if an email exists in MySQL) PDOException SQLSTATE[HY000] [2002] No such file or directory How do I configure php to enable pdo and include mysqli on CentOS?