I have a symfony 6.3 web app in docker container (php-fpm-alpine) which used a mysql container.
After migrate mysql database to postgresql, I update .env DATABASE_URL to switch to postgresql database and it works fine in dev, it could retrieve datas in database !
I would like to push in production, and if I first don't set correctly the postgresql password (error 500 docker logs return : connection to server at "my_postgres_service_name" (x.x.x.x), port xxxx failed: FATAL: password authentication failed for user, after set it correctly, I have no more errors… BUT I don't retrieve any datas !
php bin/console doctrine:mapping:info returns all entities [OK]
php bin/console doctrine:schema:validate returns :
Mapping
-------
[FAIL] The entity-class App\Entity\Sample mapping is invalid:
* The field App\Entity\Sample#result is on the inverse side of a bi-directional relationship, but the specified mappedBy association on the target-entity App\Entity\Result#sample does not contain the required 'inversedBy="result"' attribute.
Database
--------
[OK] The query yielded an empty result set.
After correction :
Mapping
-------
[OK] The mapping files are correct.
Database
--------
[critical] Error thrown while running command "'d:s:v'". Message: "Environment variable not found: "MESSENGER_TRANSPORT_DSN"."
In EnvVarProcessor.php line 187:
Environment variable not found: "MESSENGER_TRANSPORT_DSN".
2023-10-20T13:21:49+00:00 [info] User Deprecated: The annotation mapping driver is deprecated and will be removed in Doctrine ORM 3.0, please migrate to the attribute or XML driver. (AnnotationDriver.php:68 called by App_KernelDevDebugContainer.php:1355, https://github.com/doctrine/orm/issues/10098, package doctrine/orm)
2023-10-20T13:21:49+00:00 [info] User Deprecated: Column::setCustomSchemaOptions() is deprecated. Use setPlatformOptions() instead. (Column.php:424 called by Column.php:92, https://github.com/doctrine/dbal/pull/5476, package doctrine/dbal)
but in dev, it is always running fine.
I had different version to PHP, so I switched dev and prod to php 8.2.11 without changes regarding my issue
I checked env file, change in DATABASE_URL database password -> error 500, put back, no error… but always no datas
I tested a DBAL access SELECT * FROM an_usual_table -> ok en dev, error 404 in prod
If I connect to database container docker exec -it database_postgresql_container psql -U read-write-username database-name and run above request, it returns correctly records, so used user role have correct permissions
As explain en https://stackoverflow.com/a/49045589/6614155, I tried to add @ORM\Table(schema="my-schema-name") in an entity, no change in dev (always run correctly), no change in prod (always no datas)
I also have a postgREST container to have API on postgresql database, with same port in dev and prod.
I spent several days to search and I don't understand. Help welcome !
EDIT :
to get rid of symfony and doctrine, I created a file with the minimal code to read a table. If I run it on my dev host, it's fine, but if I run it from my container, I get the error
Call to undefined function pg_connect()
phpinfo() return on my (dev) host (php installed with phpbrew to have the same version as in dev/prod) :
Configure Command => './configure' … '--with-pgsql=/usr/bin' '--with-pdo-pgsql=/usr/bin'
PDO
PDO support => enabled
PDO drivers => mysql, pgsql pdo_mysql
pdo_pgsql
PDO Driver for PostgreSQL => enabled
PostgreSQL(libpq) Version => 14.9
…
and in docker php container (dev and prod):
I've got
PDO
PDO support => enabled
PDO drivers => mysql, pgsql pdo_mysql
pdo_pgsql
PDO Driver for PostgreSQL => enabled
PostgreSQL(libpq) Version => 15.4
but no --with-pdo-pgsql nor --with-pgsql !
Even if I've got in my Dockerfile :
RUN apk --no-cache update \
&& apk --no-cache add bash icu-dev libzip-dev zip postgresql-dev \
&& docker-php-ext-configure intl \
&& docker-php-ext-configure pgsql -with-pgsql=/usr/local/pgsql \
&& docker-php-ext-install pdo pdo_pgsql intl zip \
&& echo "Europe/Paris" > /etc/timezone
I change nothing in my php.ini, but I understand correctly, docker-php-ext-configure or install do it
Even if it's a clue, it doesn't explain why it works in dev and not in prod!
After many tests, I run a simple php connection to postgres without using symfony/doctrine, based on https://www.php.net/manual/en/pgsql.examples-basic.php and the same issue, no datas, no error.
So I connected to my postgresql container, run
\dn
List of schemas
Name | Owner
----------+-------------------------
my-schema-name | <user_name>
public | postgres
(2 rows)
<my_data_base_name>=# select * from period_list; # request on default schema -> public !!!!
id | fr_name | gb_name | wiki_data_alignment | bnf_alignment
----+---------+---------+---------------------+---------------
(0 rows)
<my_data_base_name>=# select * from my-schema-name.period_list; # if I define my app schema name, I have got correct datas
id | fr_name | gb_name | wiki_data_alignment | bnf_alignment
----+-----------------------+---------------------+---------------------+---------------
1 | Paléolithique | Paleolithic | Q40203 |
2 | …
and understand than I probably make an error than define same tables in public schema.
SOLUTION:
BE CAREFUL, if you use postgis, postgis default installation uses public schema (cf. https://www.postgis.net/workshops/postgis-intro/schemas.html)
DROP SCHEMA public CASCADE; # delete public default schema, unused
ALTER DATABASE <my_data_base_name> SET search_path = <my-user-name>,<my-schema-name>
The second line is a workaround, I test @ORM\Table('my-schema-name.my-table') in SF but not works in prod (perhaps a cache issue)… I should find the right solution to define schema in SF.
link about default schema that help me https://stackoverflow.com/a/9067777/6614155