Error "invalid integer value "tcp://172.17.0.3:5432"" when use PostgreSQL in Gitlab CI

[danny50610]

• Testbench Version: v8.21.1
• Laravel Version: v10.45.1
• PHP Version: 8.1

Description:

When using PostgreSQL in Gitlab CI, executing ./vendor/bin/testbench migrate will encounter the following error message

In Connection.php line 829:

  SQLSTATE[08006] [7] invalid integer value "tcp://172.17.0.3:5432" for conne  
  ction option "port" (Connection: pgsql, SQL: select * from information_sche  
  ma.tables where table_catalog = myDatabase and table_schema = public and ta  
  ble_name = migrations and table_type = 'BASE TABLE')                         

In Connector.php line 65:

  SQLSTATE[08006] [7] invalid integer value "tcp://172.17.0.3:5432" for conne  
  ction option "port"    

But using Mysql will not

The following is the result of executing ./vendor/bin/testbench config:show database

database ...................................................................  
  default .............................................................. pgsql  
  connections ⇁ sqlite ⇁ driver ....................................... sqlite  
  connections ⇁ sqlite ⇁ url ............................................ null  
  connections ⇁ sqlite ⇁ database ................................. myDatabase  
  connections ⇁ sqlite ⇁ prefix ..............................................  
  connections ⇁ sqlite ⇁ foreign_key_constraints ........................ true  
  connections ⇁ mysql ⇁ driver ......................................... mysql  
  connections ⇁ mysql ⇁ url ............................................. null  
  connections ⇁ mysql ⇁ host ........................................ postgres  
  connections ⇁ mysql ⇁ port ............................................ 5432  
  connections ⇁ mysql ⇁ database .................................. myDatabase  
  connections ⇁ mysql ⇁ username ................................... homestead  
  connections ⇁ mysql ⇁ password ...................................... secret  
  connections ⇁ mysql ⇁ unix_socket ..........................................  
  connections ⇁ mysql ⇁ charset ...................................... utf8mb4  
  connections ⇁ mysql ⇁ collation ......................... utf8mb4_unicode_ci  
  connections ⇁ mysql ⇁ prefix ...............................................  
  connections ⇁ mysql ⇁ prefix_indexes .................................. true  
  connections ⇁ mysql ⇁ strict .......................................... true  
  connections ⇁ mysql ⇁ engine .......................................... null  
  connections ⇁ mysql ⇁ options ........................................... []  
  connections ⇁ pgsql ⇁ driver ......................................... pgsql  
  connections ⇁ pgsql ⇁ url ............................................. null  
  connections ⇁ pgsql ⇁ host ........................................ postgres  
  connections ⇁ pgsql ⇁ port ........................... tcp://172.17.0.3:5432  
  connections ⇁ pgsql ⇁ database .................................. myDatabase  
  connections ⇁ pgsql ⇁ username ................................... homestead  
  connections ⇁ pgsql ⇁ password ...................................... secret  
  connections ⇁ pgsql ⇁ charset ......................................... utf8  
  connections ⇁ pgsql ⇁ prefix ...............................................  
  connections ⇁ pgsql ⇁ prefix_indexes .................................. true  
  connections ⇁ pgsql ⇁ search_path ................................... public  
  connections ⇁ pgsql ⇁ sslmode ....................................... prefer  
  connections ⇁ pgsql ⇁ collation ....................................... null  
  connections ⇁ pgsql ⇁ schema .......................................... null  
  connections ⇁ sqlsrv ⇁ driver ....................................... sqlsrv  
  connections ⇁ sqlsrv ⇁ url ............................................ null  
  connections ⇁ sqlsrv ⇁ host ....................................... postgres  
  connections ⇁ sqlsrv ⇁ port ........................................... 5432  
  connections ⇁ sqlsrv ⇁ database ................................. myDatabase  
  connections ⇁ sqlsrv ⇁ username .................................. homestead  
  connections ⇁ sqlsrv ⇁ password ..................................... secret  
  connections ⇁ sqlsrv ⇁ charset ........................................ utf8  
  connections ⇁ sqlsrv ⇁ prefix ..............................................  
  connections ⇁ sqlsrv ⇁ prefix_indexes ................................. true  
  connections ⇁ sqlsrv ⇁ collation ...................................... null  
  connections ⇁ testing ⇁ driver ...................................... sqlite  
  connections ⇁ testing ⇁ database ................................... :memory  
  connections ⇁ testing ⇁ foreign_key_constraints ...................... false  
  migrations ...................................................... migrations  
  redis ⇁ client .................................................... phpredis  
  redis ⇁ options ⇁ cluster ............................................ redis  
  redis ⇁ options ⇁ prefix ................................. laravel_database_  
  redis ⇁ default ⇁ url ................................................. null  
  redis ⇁ default ⇁ host ........................................... 127.0.0.1  
  redis ⇁ default ⇁ username ............................................ null  
  redis ⇁ default ⇁ password ............................................ null  
  redis ⇁ default ⇁ port ................................................ 6379  
  redis ⇁ default ⇁ database ............................................... 0  
  redis ⇁ cache ⇁ url ................................................... null  
  redis ⇁ cache ⇁ host ............................................. 127.0.0.1  
  redis ⇁ cache ⇁ username .............................................. null  
  redis ⇁ cache ⇁ password .............................................. null  
  redis ⇁ cache ⇁ port .................................................. 6379  
  redis ⇁ cache ⇁ database ................................................. 1  

I think the reason is that in Gitlab CI, services(i.e. PostgreSQL) is connected using docker link.

And according to the way Docker operates, the environment variable: POSTGRES_PORT will be created, and the value is tcp://172.17.0.3:5432

Then testbench will use this environment variable in HandlesDatabaseConnections.php and set it in laravel config

However, PostgreSQL does not accept this content, so it prints an error

Reference 1: https://stackoverflow.com/questions/42976181/postgresql-on-aws-ecs-psycopg2-operationalerror-invalid-port-number-5432 Reference 2: https://docs.gitlab.com/ee/ci/services/#how-services-are-linked-to-the-job Reference 3: https://docs.docker.com/network/links/#environment-variables

Steps To Reproduce:

I create demo project: https://gitlab.com/danny60072/testbench-postgres-port-test

The fail job: https://gitlab.com/danny60072/testbench-postgres-port-test/-/jobs/6284037647

Steps To Reproduce:

1. Create project only use testbench
2. Add .gitlab-ci.yml file

   
   stages:
   - test

test-mysql8.0: stage: test image: danny60072/gitlab-ci-pipeline-php:8.1-node18 variables:

laravel

DB_CONNECTION: mysql
DB_HOST: mysql
DB_PORT: 3306
DB_DATABASE: myDatabase
DB_USERNAME: homestead
DB_PASSWORD: secret

MYSQL_ROOT_PASSWORD: root_password
MYSQL_DATABASE: $DB_DATABASE
MYSQL_USER: $DB_USERNAME
MYSQL_PASSWORD: $DB_PASSWORD

services:

• name: mysql:8.0 script:
  • echo "$DB_PORT"
  • echo "$MYSQL_PORT"
  • echo "$POSTGRES_PORT"
  • composer install
  • ./vendor/bin/testbench config:show database
  • ./vendor/bin/testbench migrate

test-postgres:15: stage: test image: danny60072/gitlab-ci-pipeline-php:8.1-node18 variables:

laravel

DB_CONNECTION: pgsql
DB_HOST: postgres
DB_PORT: 5432
DB_DATABASE: myDatabase
DB_USERNAME: homestead
DB_PASSWORD: secret

POSTGRES_DB: $DB_DATABASE
POSTGRES_USER: $DB_USERNAME
POSTGRES_PASSWORD: $DB_PASSWORD

services:

• name: postgres:15 script:
  • echo "$DB_PORT"
  • echo "$MYSQL_PORT"
  • echo "$POSTGRES_PORT"
  • composer install
  • ./vendor/bin/testbench config:show database
  • ./vendor/bin/testbench migrate

3. Push to Gitlab, then wait CI

# Possible temporary solution:
At present, I unset the `POSTGRES_PORT` environment variable at the very beginning of CI, but I think maybe testbench can add a flag in `testbench.yaml` to prevent testbench from executing the actions in `HandlesDatabaseConnections.php`