Connection refused SQL: select * from information_schema.tables where table_schema = firstdb and table_name = migrations and table_type = 'BASE TABLE

Question

I have XAMPP installed and turned on which is how I access my phpmyadmin page. I have created a database in phpmyadmin called "firstdb".

I have also created auth in laravel files stored locally. I am trying to migrate tables using php artisan migrate and I am getting the error below.

user@Andress-MacBook-Pro admin-panel % php artisan migrate

   Illuminate\Database\QueryException 

  SQLSTATE[HY000] [2002] Connection refused (SQL: select * from information_schema.tables where table_schema = firstdb and table_name = migrations and table_type = 'BASE TABLE')

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:671
    667|         // If an exception occurs when attempting to run a query, we'll format the error
    668|         // message to include the bindings with SQL, which will make this exception a
    669|         // lot more helpful to the developer instead of just the database's errors.
    670|         catch (Exception $e) {
  > 671|             throw new QueryException(
    672|                 $query, $this->prepareBindings($bindings), $e
    673|             );
    674|         }
    675| 

      +37 vendor frames 
  38  artisan:37
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

I have looked everywhere, please help.

Zaragoza

The error here is "Connection refused". The query it's trying to run is incidental to that, so your title isn't going to help you get an answer. Presumably, this is just a configuration issue in your Laravel setup. – Stanleigh

To add to what Greg has said, check that database host, username, password, etc. are correctly set based on what your XAMPP environment requires. – Conveyance

Answer 1

It could be the wrong mysql port number in your .env file. Check your .env file and make sure the port number matches the same one mysql is using.

Fabri

yes, to add to what Nelson said, always make sure you add the correct values in .env file and also add the such connection and enviornment specific variables in .env file. – Jenine

Since you are using XAMPP, the answer may be different. For me, I am using Homestead, my solution was simple. Change the .env DB_Username to homestead and the DB_Password to secret as per Laravel's documentation. Then I could do the migration. So just set in your .env files the same credentials that you used to connect to your XAMPP database. – Gilley

Good answer @MichaelBerrios. – Fabri

@NelsonKatale glad that it helped. – Gilley

yes, in my case, in .env was 3306 and docker was 3307... – Vullo

Thank you @Nelson Katale. This needs to be marked as the answer. Just like Edward Ramos I was using docker which had an alternate port number. Easy fix. – Electrokinetics

Answer 2

I noticed something weird regarding the password that was in the command line output vs what was actually in the env file. Turned out I had a number sign in my password and it was getting cut off.

So check the output after you run php artisan migrate and make sure that the password actually matches the password in your env (and all information for that matter). The fix is to just add quotes if there is a mismatch.

DB_PASSWORD=abcdefghijklmonp5#Q

would become

DB_PASSWORD='abcdefghijklmonp5#Q'

Woodprint

I had already created multiple other mysql databases on the same server without a quoted password and migrated successfully. But this time this same error was coming up and was solved using the quote even after verifying the password matched exactly. Really weird and inconsistent behavior. Thanks for this suggestion – Sad

Answer 3

Try to set your DB_HOST on your .env to

DB_HOST=localhost

Weapon

Answer 4

In addition to making sure all of your configs are correct escaping password with quotes I ran into a similar issue for a clean install on linux.

SQLSTATE[HY000] [1045] Access denied for user 'username'@'localhost' (using password: YES) (SQL: select * from information_schema.tables where table_schema = studentaffairs and table_name = migrations and table_type = 'BASE TABLE')

To solve the issue I made sure to run composer update and for good measure I ran npm run dev

Then it was able to migrate without issue.

Malissamalissia

Answer 5

Try to set on php.ini and search:

;extension=pdo_mysql.so

Delete ";" to uncomment

Godbey

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Newspaper

Answer 6

First, check the the user plugin:

mysql> SELECT User,Host,plugin FROM mysql.user WHERE user='root';

If the user is using caching_sha2_password change to mysql_native_password, because PHP doesn't yet understand caching_sha2_password

mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'

Herman

Answer 7

In order to set your database host, you need to edit the .env file and specify the desired hostname instead of IP address. To do this, locate the DB_HOST variable in the .env file and assign it the value of "localhost". This will instruct your application to connect to a local database server, which is typically running on the same machine as the application itself.

For example, you might see a line like this in your .env file:

DB_HOST=

To set the hostname to "localhost", you would simply update the line to read:

DB_HOST=localhost

Once you have saved your changes to the .env file, your application should be able to connect to the local database server using the hostname or IP address that you specified.

Adsorbate

Answer 8

add this to your .env SQL_REQUIRE_PRIMARY_KEY=0

altering your laravel database config file, modify the options like so:

'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET SESSION sql_require_primary_key=' . env('SQL_REQUIRE_PRIMARY_KEY'),
            ]) : [],

the whole mysql config block will then look something like this;

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',
            #'ssl_mode' => env('SSL_MODE'),
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET SESSION sql_require_primary_key=' . env('SQL_REQUIRE_PRIMARY_KEY'),
            ]) : [],
        ],

Precious

Answer 9

change DB_CONNECTION=127.0.0.7 TO mysql in .env file

Vieira