0

I'm having trouble using Postgresql 12.3 with Laravel 7.24.0 Here's the error message I get:

Illuminate\Database\QueryException
could not find driver (SQL: insert into "posts" ("title", "content", "updated_at", "created_at") values (aze, bcb, 2020-08-11 18:02:26, 2020-08-11 18:02:26) returning "id")

Here's my setup (I tested the settings (host, port, username, password, and db_name) with DBeaver and with the command line):

.env :

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=<db_name>
DB_USERNAME=<user_name>
DB_PASSWORD=<password>

database.php:

    'default' => env('DB_CONNECTION', 'pgsql'),
    ...
    'connections' => [
        'pgsql' => [
            'driver' => 'pgsql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', '<db_name>'),
            'username' => env('DB_USERNAME', '<user_name>'),
            'password' => env('DB_PASSWORD', '<password>'),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'schema' => 'public',
            'sslmode' => 'prefer',
        ],
    ],

And here's what I've tried:

  1. composer require doctrine/dbal
  2. Edit /etc/php/7.4/apache2/php.ini and uncommented extension=pdo_pgsql and extension=pgsql
  3. apt-get install php-pgsql && sudo systemctl restart apache2
  4. As the double quotes around the fields name and not the values confused me, I executed a raw SQL query to make sure the syntax was correct (I tested it with DBeaver).
$sqlQuery = "insert into posts 
             (title, post, updated_at, created_at) values
             ('aze', 'bcb', '2020-08-11 17:10:47', '2020-08-11 17:10:48') returning id";
$result = DB::select(DB::raw($sqlQuery));
  1. I ran php artisan tinker, then DB::connection()->getPdo(); on command line and got this result:
=> Doctrine\DBAL\Driver\PDOConnection {#3085
     inTransaction: false,
     attributes: {
       CASE: NATURAL,
       ERRMODE: EXCEPTION,
       PERSISTENT: false,
       DRIVER_NAME: "pgsql",
       SERVER_INFO: "PID: 27281; Client Encoding: UTF8; Is Superuser: off; Session Authorization: mespas; Date Style: ISO, DMY",
       ORACLE_NULLS: NATURAL,
       CLIENT_VERSION: "10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)",
       SERVER_VERSION: "12.3 (Ubuntu 12.3-1.pgdg18.04+1)",
       STATEMENT_CLASS: [
         "Doctrine\DBAL\Driver\PDOStatement",
         [],
       ],
       EMULATE_PREPARES: false,
       CONNECTION_STATUS: "Connection OK; waiting to send.",
       DEFAULT_FETCH_MODE: BOTH,
     },
   }

Which is apparently what what I'm supposed to get

  1. I removed DATABASE_URL from database.php since it was not defined. I figure if all the other params are set, it is not necessary.

I constantly get the same message and after a few hours of research, I'm kind of out of ideas...

Olivier
  • 11
  • 1
  • 3
  • Hi Olivier! So when you did you raw query (step #4) did that produce the same error? Or did the raw query work? – nibnut Aug 11 '20 at 20:00
  • Hi @nibnut! No, it didn't work. Whatever I tried I always got the exact same error message – Olivier Aug 11 '20 at 20:50
  • Ok - and this is going to sound silly but... Are you sure the php.ini file you edited is the one used by apache or by the CLI? (depending on where/how you run your tests) Have you tried phphinfo() to confirm that the PDO and PGSWL driver are indeed activated? – nibnut Aug 11 '20 at 21:14
  • Yes I am :-) I edited the php.ini in the directory apache2, not cli (and it's the one mentioned in phpinfo). I test in the browser. Now about the PDO, I can see in phpinfo() that it doesn't say if it's enabled: under "PDO", it says "PDO drivers" => "enabled" = "no value". (but to reiterate, pdo_pgsql and pgsql are enabled in php.ini) And PGSWL is not mentioned at all. – Olivier Aug 11 '20 at 21:27
  • Ok - so under "PDO", you don't see: PDO PDO support => enabled PDO drivers => dblib, mysql, odbc, pgsql, sqlite ? – nibnut Aug 11 '20 at 21:31
  • Is it possible you have multiple versions of PHP running? https://stackoverflow.com/questions/56408731/php-pdo-for-postgres-could-not-find-driver – nibnut Aug 11 '20 at 21:33
  • No mention of dblib. mysql, odbc, sqlite are only mentioned in the "Module authors", so I don't think we care. And pgsql is mentioned - in the Environment section under DB_CONNECTION - in the PHP Variables section under $_SERVER['DB_CONNECTION'] and $_ENV['DB_CONNECTION'] – Olivier Aug 11 '20 at 21:38
  • In /etc/php I have 7.2 and 7.4, but it is the 7.2 that is used -- and just in case, I have enabled the extensions in both php.ini – Olivier Aug 11 '20 at 21:39
  • Ok: I'm pretty certain the $_SERVER and $_ENV values are set by Laravel, so it's normal that you see your settings there. I would try disabling php7.4 if you can. just to be sure there's nothing funky going on... – nibnut Aug 11 '20 at 21:43

1 Answers1

1

All right, so following @nibnut's advice I went and disabled php 7.2 for Apache and enabled 7.4 While I was at it, I checked the version for CLI and got this message:

PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_pgsql' (tried: /usr/lib/php/20190902/pdo_pgsql (/usr/lib/php/20190902/pdo_pgsql: cannot open shared object file: No such file or directory), /usr/lib/php/20190902/pdo_pgsql.so (/usr/lib/php/20190902/pdo_pgsql.so: undefined symbol: pdo_parse_params)) in Unknown on line 0

For those interested, I found how to install it here.

It works now and my data are saved in the database, even though strangely enough I still get the message when running php -v

Thanks for the help @nibnut !

Olivier
  • 11
  • 1
  • 3