Laravel artisan cannot access ENV variables (causing: db connection refused)-CodePudding

I'm aware that there are many questions that look like this, but usually they have obvious solutions (stopped mysql server, errors in the .env file and stuff like that). This is different.

My app is working fine in the dev environment. Instead in production, whenever it tries to use the DB, it produces a 500 error, and it shows an alternance of these two errors in the logs:

[previous exception] [object] (PDOException(code: 2002): SQLSTATE[HY000] [2002] Connection refused at /var/www/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70)

[2022-07-26 15:52:44] local.ERROR: SQLSTATE[HY000] [2002] Connection refused (SQL: select * from `users` where `email` = [email protected] limit 1) {"exception":"[object] (Illuminate\\Database\\QueryException(code: 2002): SQLSTATE[HY000] [2002] Connection refused (SQL: select * from `users` where `email` = [email protected] limit 1) at /var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:759)

That seems very odd, since if I docker exec into the container, I can see that all the environmental variables are set correctly, that the mysql client can connect to the db:

mysql -h $DB_HOST -u $DB_USERNAME -p

and that also tinker works, and that I can run artisan command:mycommands without any issues.

What could it be? I already ran

php artisan config:clear
php artisan route:clear

and also

php artisan config:cache
php artisan route:cache

without any luck.

It might be interesting that here the PDO construct appears to be empty:

[previous exception] [object] (PDOException(code: 2002): SQLSTATE[HY000] [2002] Connection refused at /var/www/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70)
[stacktrace]
#0 /var/www/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php(70): PDO->__construct()

Both my dev and production environments have PDO and pdo_mysql PHP extensions installed. But I'm not supplying any value for this env variable MYSQL_ATTR_SSL_CA. Should I??

It appears here in the config/database.php file:

'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' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

DATABASE_URL and DB_SOCKET are also null. Is that ok?

UPDATE: answers to the comments:

What is host and port in .env? That depends on the environment. In production testing I have:

DB_HOST=mariadb (name of the maria db docker service, the same I use for the dev environment)
DB_PORT=3306

While in actual production (Fargate service serverless RDS), I have:

DB_HOST=url.of.the.rds.resource
DB_PORT=3306

Both environments show the same behavior.

can you restart you appache

This is what happens in production-testing. /var/www is a bind mount of docker composer.

root@abb0b3cf202c:/var/www# service apache2 restart
Restarting Apache httpd web server: apache2AH00112: Warning: DocumentRoot [/var/www/html] does not exist
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 172.23.0.3. Set the 'ServerName' directive globally to suppress this message
(98)Address already in use: AH00072: make_sock: could not bind to address 0.0.0.0:80
no listening sockets available, shutting down
AH00015: Unable to open logs
Action 'start' failed.
The Apache error log may have more information.
 failed!

and this after fixing the document root path:

root@abb0b3cf202c:/var/www# service apache2 restart
Restarting Apache httpd web server: apache2AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 172.23.0.3. Set the 'ServerName' directive globally to suppress this message
(98)Address already in use: AH00072: make_sock: could not bind to address 0.0.0.0:80
no listening sockets available, shutting down
AH00015: Unable to open logs
Action 'start' failed.
The Apache error log may have more information.
 failed!

I start the webserver with php artisan serve.

UPDATE

this UI test fails https://ralphjsmit.com/check-laravel-database-connection (and artisan says that Command "dump-server" is not defined.)

UPDATE I think it's relevant to list the things that differ between the dev environment, where things work, and the production environment, where they do not:

I'm not copying over the Vendors folder. The Storage folder is also emptied
I'm rebuilding the Vendors folder with composer install --no-dev --optimize-autoloads and npm install --omit-dev
When the image is started, I run

php artisan config:clear
php artisan config:cache
php artisan route:clear
php artisan route:cache
php artisan view:clear
php artisan view:cache

UPDATE I found a clue!

If I run docker exec I can see that PHP can access ENV variables correctly:

root@7f904d0a469a:/var/www# echo $DB_HOST
mariadb

root@7f904d0a469a:/var/www# php -a
Interactive shell

php > var_dump(getenv('DB_HOST'));
string(7) "mariadb"

But if in a laravel blade I put:

<p>DB HOST: <?php var_dump(getenv('DB_HOST'), $_ENV['DB_HOST']); ?> </p>

Then I get

ErrorException
PHP 8.1.8
9.21.6
Undefined array key "DB_HOST"

Therefore the real problem is that Artisan cannot access ENV variables. I'm changing the title.

UPDATE: nevermind. It does read the ENV variables, just not if you use cache. Strange "feature" of Laravel...

So, when not using config:cache, this snippet of code:

<strong>Database Connected: </strong>
<?php
    try {
        \DB::connection()->getPDO();
        echo \DB::connection()->getDatabaseName();
    } catch (\Exception $e) {
        echo 'None';
    }
?>
<p>DB HOST: <?php var_dump(getenv('DB_HOST'), $_ENV['DB_HOST']); ?> </p>

results in:

Database Connected: None
DB HOST: string(7) "mariadb" string(7) "mariadb"

CodePudding user response：

This issue arised from my misunderstanding of the use of the .env file.

I thought that the variables set in the .env file could be overwritten by actual environment variables set in the OS.

I also thought that it was bad practice to use .env files in production.

Therefore, the only variable I had set in my env file was APP_KEY and nothing else, and I was setting everything else in Fargate (or docker compose for testing).

It turns out that instead Laravel requires an .env file, and ignores the actual environment variables.

My testing configuration was half working because it was using cached settings, causing a great deal of confusion for me.

SOLUTION: use the env file in production.