Behat error.

[xgitxhubx]

Environment:

Postgresql: v9.6

oro/redis-config | 2.0.0-beta.1 | MIT
-- | -- | --
oro/doctrine-extensions | 1.1.2 | MIT
oro/platform | 2.3.16 | MIT
oro/platform-enterprise | 2.3.9 | Commercial
oro/marketing | 2.3.7 | OSL-3.0
oro/calendar-bundle | 2.3.3 | OSL-3.0
oro/platform-serialised-fields | 2.3.1 | OSL-3.0
oro/customer-portal | 2.3.7 | OSL-3.0
oro/commerce | 1.3.16 | OSL-3.0
oro/commerce-enterprise | 1.3.9 | Commercial
oro/crm-call-bundle | 2.3.2 | OSL-3.0
oro/crm-task-bundle | 2.3.2 | OSL-3.0
oro/crm | 2.3.12 | OSL-3.0
oro/crm-enterprise | 2.3.3 | Commercial
oro/commerce-crm | 1.3.2 | OSL-3.0
oro/crm-hangouts-call-bundle | 2.3.1 | OSL-3.0
oro/crm-mail-chimp | 2.3.4 | OSL-3.0
oro/crm-abandoned-cart | 2.3.1 | OSL-3.0
oro/crm-magento-embedded-contact-us | 2.3.1 | OSL-3.0
oro/crm-dotmailer | 2.3.3 | OSL-3.0
oro/crm-zendesk | 2.3.4 | OSL-3.0
oro/crm-pro-ldap-bundle | 2.3.1 | OSL-3.0
oro/commerce-crm-ee | 1.3.1 | Commercial

Error after test success:

Process message queue
Stop message queue
time: 1024 ms
Restore database from dump

  [Symfony\Component\Process\Exception\ProcessFailedException]                                                       
  The command "PGPASSWORD="dbpassword" psql -h localhost --port= -U dbname -t -c "SELECT pg_terminate_backe  
  nd(pid) FROM pg_stat_activity WHERE datname in ('dbname', 'dbnamedb5a3391c5bf8ed235967443') AND pid <  
  > pg_backend_pid()"" failed.                                                                                       
  Exit Code: 2(Misuse of shell builtins)                                                                             
  Working directory: /home/user/oro/                                                          
  Output:                                                                                                            
  ================                                                                                                   
  Error Output:                                                                                                      
  ================                                                                                                   
  psql: FATAL:  database "dbname" does not exist 

I think because we drop database before killing connections: https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L90 https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L91 https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L197 https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L234

[spolischook]

something wrong with port --port=. Could you please provide your parameters.yml

[xgitxhubx]

parameters.yml

parameters:
    database_driver: pdo_pgsql
    database_host: localhost
    database_port: null

I don't think so, because the test is successful, it ran as I expected, and it dropped database successfully, only restore database process has error. If I comment out this line, it works: $this->killConnections(); https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L197

[spolischook]

@xgitxhubx the issues with database name, it can't find database.

[xgitxhubx]

@spolischook But the test is successful. It can backup database, run test, drop database and only error when restore the original database. ie: The original database is "dbname". After run tests, and error occurs, I have only 1 database: "dbnamedb5a3391c5bf8ed235967443" The system tried to restore the original database (dbname) from dump database (dbnamedb5a3391c5bf8ed235967443) but error occurred before this process (on kill connections process). Don't know why the error occurs. I tried to revoke connection before killing connections but have no luck. https://stackoverflow.com/questions/5108876/kill-a-postgresql-session-connection

[spolischook]

this is issue with compose command for restore database, try to debug behat pg isolator. try var_dump($process); sleep(90); here https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L234 and check command by hands while pause in behat execution.

[xgitxhubx]

The result:

PGPASSWORD="dbpassword" psql -h localhost --port= -U dbuser -t -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname in ('dbname', 'dbnamedb5a3a158b157ce712224359') AND pid <> pg_backend_pid()"
psql: FATAL:  database "dbname" does not exist

[spolischook]

yep, this mean that db, does not exist any more, did you know why this happen?

[spolischook]

check carefully db name

[xgitxhubx]

If the database name is mistake, why it can run test, backup database, and drop database?

[spolischook]

I believe that it issue (it's bug actually) in command, but I don't see where. Please double check dbname, the error is about that it unable to find db with specified name, but it should be, isn't it?

[xgitxhubx]

I think the problem occurs after dropping the test database process. As I commented above, if I comment out this line, it works normally. https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L197

I think the system tried to connect to "dbname" when run the kill connections command. If I change the command to connect to other database, it works. ie: PGPASSWORD="dbpassword" psql -h localhost --port= -U dbuser --dbname=dbnamedb5a3a158b157ce712224359 -t -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname in ('dbname', 'dbnamedb5a3a158b157ce712224359') AND pid <> pg_backend_pid()" Should we explicit set the database name in kill connections command?

[spolischook]

psql: FATAL:  database "dbname" does not exist

this mean that db does not exist. It unable to connect to DB that was dropped or not exist at all. You should recognize why this happen

[xgitxhubx]

Same as this case: https://superuser.com/questions/655399/calling-the-psql-command-without-selecting-any-database I think the problem is it.

[spolischook]

The db name get here

[xgitxhubx]

Please understand me, the problem is not in the database name, it is in the kill connection command. Because the kill connection command does not select any database, so it'll use the username as database name. In my case, the username and database name are same: "dbname" => "dbname" is used but "dbname" does not exist. Should we explicit select database name in kill connection command?

[spolischook]

As I see there are parameters for dbname see https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L242-L243 Can you recognize where issue is?

[xgitxhubx]

https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L235 The command does not select any database to connect to. It only specifies host, port, username. https://www.postgresql.org/docs/9.2/static/app-psql.html Please try to forget the database name and understand this question, our issue is same as this question: https://superuser.com/questions/655399/calling-the-psql-command-without-selecting-any-database

[spolischook]

why not, see https://github.com/oroinc/platform/blob/b90343b4464f9aea5094b1ea911c5aa8639f6fef/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L237

WHERE datname in (\'%s\', \'%s\')

[xgitxhubx]

PGPASSWORD="dbpassword" psql -h localhost --port= -U dbuser -t -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname in ('dbname', 'dbnamedb5a3a158b157ce712224359') AND pid <> pg_backend_pid()" It just a command to execute, before execute command, you must connect to a database.

[xgitxhubx]

It is equal to: PGPASSWORD="dbpassword" psql -h localhost --port= -U dbuser -t -c "Command to execute"

[xgitxhubx]

Should we specify the database name? ie: PGPASSWORD="dbpassword" psql -h localhost --port= -U dbuser -t -c "Command to execute" -d dbname Or PGPASSWORD="dbpassword" psql -h localhost --port= -U dbuser -t -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname in ('dbname', 'dbnamedb5a3a158b157ce712224359') AND pid <> pg_backend_pid()" -d dbname

[spolischook]

yep. If it will resolve your issue. Please check the solution and let me know, I'll check it on our CI

[dxops]

@xgitxhubx please setup port 5432 in your parameters file as a workaround, --port= is a wrong way to define bash options, should be --port=5432

[xgitxhubx]

@sergeyz It works without the port. Because I used prod env for testing. If it's wrong, the drop and create database failed, but the system can create and drop database, error occurs only in restore database process. Can you try with normal database user (not root database user)? https://github.com/oroinc/platform/blob/b90343b4464f9aea5094b1ea911c5aa8639f6fef/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L217 -> work https://github.com/oroinc/platform/blob/b90343b4464f9aea5094b1ea911c5aa8639f6fef/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L168 -> work https://github.com/oroinc/platform/blob/b90343b4464f9aea5094b1ea911c5aa8639f6fef/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L197 -> not work

[dxops]

I suggest to connect to template0 or template1 database then

[nevoeiro]

@sergeyz and @spolischook I have the same issue as @xgitxhubx. The behavior is the same. And the error is like:

[Symfony\Component\Process\Exception\ProcessFailedException]                                                                                                                                            
  The command "PGPASSWORD="root" psql -h db --port=5432 -U root -t -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname in ('orocommerce', 'orocommercedb5a7b1a5994c54003429856') AN  
  D pid <> pg_backend_pid()"" failed.                                                                                                                                                                     

  Exit Code: 2(Misuse of shell builtins)                                                                                                                                                                  

  Working directory: /var/www/htdocs                                                                                                                                                                      

  Output:                                                                                                                                                                                                 
  ================                                                                                                                                                                                        

  Error Output:                                                                                                                                                                                           
  ================                                                                                                                                                                                        
  psql: FATAL:  database "root" does not exist 

I tried to add the -d dbname to command and I works when tries to create the dump but then fails on "Restore database from dump".

@xgitxhubx how did you fix that?

[nevoeiro]

I created a new database with username = 'root' and dbname='root' to test, and it worked on "Dumping current application database" step. But it failed after when it tried to execute the step "Run message queue"

Dumping current application database
time: 595 ms
Booting the Kernel
Application ready for tests
Run message queue

  [Doctrine\DBAL\Exception\DriverException]                                                       
  An exception occurred while executing 'SELECT * FROM oro_message_queue':                        

  SQLSTATE[57P01]: Admin shutdown: 7 FATAL:  terminating connection due to administrator command  
  server closed the connection unexpectedly                                                       
        This probably means the server terminated abnormally                                           
        before or while processing the request. 

Looks like this is caused by the killConnections() method, am I right? How to fix this?

Regarding this I create a new issue: https://github.com/oroinc/platform/issues/807

[xgitxhubx]

@nevoeiro in my case, I commented this line https://github.com/oroinc/platform/blob/b90343b4464f9aea5094b1ea911c5aa8639f6fef/src/Oro/Bundle/TestFrameworkBundle/Behat/Isolation/UnixPgsqlIsolator.php#L197 to fix it temporarily.

[aivus]

This issue has been fixed in https://github.com/oroinc/platform/commit/efbcc19604e2334039192a989cdcca3462a5a293