Use postgresql as a database backend

Description of the Issue
I’m trying to install LinkAce with a postgresql as a database backend (because here https://www.linkace.org/docs/v1/setup/setup-without-docker/ is stated that postgresql can be used as such). For some reason LinkAce cannot establish connection with postgresql server, stating that:

Database could not be configured. Please check your connection details. Details: SQLSTATE[HY000] [2006] MySQL server has gone away (SQL: SHOW FULL TABLES WHERE table_type = ‘BASE TABLE’)

I believe, it tries to use mysql driver to communicate with postgresql.

Steps to reproduce
I’m using this distribution:
https://github.com/Kovah/LinkAce/releases/download/v1.2.0/linkace-1.2.0.zip

This is my connection details:
# egrep ‘^DB_’ .env | grep -v PASSWORD
DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=links
DB_USERNAME=links

My OS is Ubuntu 18.04.5 LTS server with latest updates.

I’m using php8.0-fpm from this very popular ppa:
https://launchpad.net/~ondrej/+archive/ubuntu/php/

This php packages are installed:
php8.0-bcmath 8.0.1-1+ubuntu18.04.1+deb.sury.org+1 amd64
php8.0-cli 8.0.1-1+ubuntu18.04.1+deb.sury.org+1 amd64
php8.0-common 8.0.1-1+ubuntu18.04.1+deb.sury.org+1 amd64
php8.0-fpm 8.0.1-1+ubuntu18.04.1+deb.sury.org+1 amd64
php8.0-mbstring 8.0.1-1+ubuntu18.04.1+deb.sury.org+1 amd64
php8.0-mysql 8.0.1-1+ubuntu18.04.1+deb.sury.org+1 amd64
php8.0-opcache 8.0.1-1+ubuntu18.04.1+deb.sury.org+1 amd64
php8.0-pgsql 8.0.1-1+ubuntu18.04.1+deb.sury.org+1 amd64
php8.0-readline 8.0.1-1+ubuntu18.04.1+deb.sury.org+1 amd64
php8.0-xml 8.0.1-1+ubuntu18.04.1+deb.sury.org+1 amd64

I’m using stock ubuntu postgresql distribution:
postgresql 10+190ubuntu0.1 all
postgresql-10 10.15-0ubuntu0.18.04.1 amd64
postgresql-client-10 10.15-0ubuntu0.18.04.1 amd64
postgresql-client-common 190ubuntu0.1 all
postgresql-common 190ubuntu0.1 all

postgresql is up and running, listening on 127.0.0.1:5432:

# netstat -atun | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN

This is a postgresql log gathered with highest verbosity level (debug5) after button ‘Configure database’ on page “/setup/database” were pressed:

2021-01-25 14:42:07.903 MSK [21282] DEBUG:  forked new backend, pid=24433 socket=12
2021-01-25 14:42:19.782 MSK [24434] DEBUG:  InitPostgres
2021-01-25 14:42:19.782 MSK [24434] DEBUG:  my backend ID is 3
2021-01-25 14:42:19.783 MSK [24434] DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0
2021-01-25 14:42:19.786 MSK [24434] DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0
2021-01-25 14:42:19.787 MSK [24434] DEBUG:  autovacuum: processing database "links"
2021-01-25 14:42:19.798 MSK [24434] DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0
2021-01-25 14:42:19.799 MSK [24434] DEBUG:  pg_statistic: vac: 0 (threshold 129), anl: 0 (threshold 89)
2021-01-25 14:42:19.799 MSK [24434] DEBUG:  pg_type: vac: 0 (threshold 125), anl: 0 (threshold 88)
2021-01-25 14:42:19.799 MSK [24434] DEBUG:  pg_authid: vac: 1 (threshold 51), anl: 2 (threshold 51)
2021-01-25 14:42:19.799 MSK [24434] DEBUG:  pg_subscription: vac: 0 (threshold 50), anl: 0 (threshold 50)
2021-01-25 14:42:19.800 MSK [24434] DEBUG:  pg_attribute: vac: 0 (threshold 566), anl: 0 (threshold 308)
2021-01-25 14:42:19.800 MSK [24434] DEBUG:  pg_proc: vac: 0 (threshold 629), anl: 0 (threshold 339)
2021-01-25 14:42:19.800 MSK [24434] DEBUG:  pg_class: vac: 0 (threshold 118), anl: 0 (threshold 84)
2021-01-25 14:42:19.800 MSK [24434] DEBUG:  pg_index: vac: 0 (threshold 77), anl: 0 (threshold 63)
2021-01-25 14:42:19.800 MSK [24434] DEBUG:  pg_operator: vac: 0 (threshold 207), anl: 0 (threshold 129)
2021-01-25 14:42:19.800 MSK [24434] DEBUG:  pg_opclass: vac: 0 (threshold 77), anl: 0 (threshold 63)
2021-01-25 14:42:19.800 MSK [24434] DEBUG:  pg_am: vac: 0 (threshold 51), anl: 0 (threshold 51)
2021-01-25 14:42:19.801 MSK [24434] DEBUG:  pg_amop: vac: 0 (threshold 192), anl: 0 (threshold 121)
2021-01-25 14:42:19.801 MSK [24434] DEBUG:  pg_amproc: vac: 0 (threshold 132), anl: 0 (threshold 91)
2021-01-25 14:42:19.801 MSK [24434] DEBUG:  pg_cast: vac: 0 (threshold 94), anl: 0 (threshold 72)
2021-01-25 14:42:19.801 MSK [24434] DEBUG:  pg_namespace: vac: 0 (threshold 51), anl: 0 (threshold 51)
2021-01-25 14:42:19.801 MSK [24434] DEBUG:  pg_database: vac: 3 (threshold 50), anl: 5 (threshold 50)
2021-01-25 14:42:19.801 MSK [24434] DEBUG:  pg_db_role_setting: vac: 0 (threshold 50), anl: 0 (threshold 50)
2021-01-25 14:42:19.801 MSK [24434] DEBUG:  pg_tablespace: vac: 0 (threshold 50), anl: 0 (threshold 50)
2021-01-25 14:42:19.801 MSK [24434] DEBUG:  pg_shdepend: vac: 0 (threshold 51), anl: 1 (threshold 51)
2021-01-25 14:42:19.802 MSK [24434] DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0
2021-01-25 14:42:19.802 MSK [24434] DEBUG:  shmem_exit(0): 1 before_shmem_exit callbacks to make
2021-01-25 14:42:19.802 MSK [24434] DEBUG:  shmem_exit(0): 7 on_shmem_exit callbacks to make
2021-01-25 14:42:19.803 MSK [24434] DEBUG:  proc_exit(0): 2 callbacks to make
2021-01-25 14:42:19.803 MSK [24434] DEBUG:  exit(0)
2021-01-25 14:42:19.804 MSK [24434] DEBUG:  shmem_exit(-1): 0 before_shmem_exit callbacks to make
2021-01-25 14:42:19.804 MSK [24434] DEBUG:  shmem_exit(-1): 0 on_shmem_exit callbacks to make
2021-01-25 14:42:19.804 MSK [24434] DEBUG:  proc_exit(-1): 0 callbacks to make
2021-01-25 14:42:19.809 MSK [21282] DEBUG:  reaping dead processes
2021-01-25 14:42:19.809 MSK [21282] DEBUG:  server process (PID 24434) exited with exit code 0

This is how tcpdump on lo interface with 5432 port filter looks like:

tcpdump: listening on lo, link-type EN10MB (Ethernet), capture size 262144 bytes
14:48:39.044729 IP (tos 0x0, ttl 64, id 17712, offset 0, flags [DF], proto TCP (6), length 60)
    localhost.40740 > localhost.postgresql: Flags [S], cksum 0xfe30 (incorrect -> 0xb0cc), seq 744724756, win 65495, options [mss 65495,sackOK,TS val 325194239 ecr 0,nop,wscale 7], length 0
E..<[email protected]@............$.8,c...........0.........
.b..........
14:48:39.044740 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto TCP (6), length 60)
    localhost.postgresql > localhost.40740: Flags [S.], cksum 0xfe30 (incorrect -> 0x45da), seq 515516114, ack 744724757, win 65483, options [mss 65495,sackOK,TS val 325194239 ecr 325194239,nop,wscale 7], length 0
E..<[email protected]@.<..........8.$..&.,c.......0.........
.b...b......
14:48:39.044751 IP (tos 0x0, ttl 64, id 17713, offset 0, flags [DF], proto TCP (6), length 52)
    localhost.40740 > localhost.postgresql: Flags [.], cksum 0xfe28 (incorrect -> 0x6c96), seq 1, ack 1, win 512, options [nop,nop,TS val 325194239 ecr 325194239], length 0
[email protected]@............$.8,c....&......(.....
.b...b..

(yes, this is it. There is nothing else.)

I can connect to database manually with psql client using credentials that declared in .env:

# psql -Ulinks -h 127.0.0.1 -d links -W
Password for user links:
psql (10.15 (Ubuntu 10.15-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

links=>

I can manipulate with the data, for example:

links=> CREATE TABLE accounts (
links(>  user_id serial PRIMARY KEY,
links(>  username VARCHAR ( 50 ) UNIQUE NOT NULL,
links(>  password VARCHAR ( 50 ) NOT NULL,
links(>  email VARCHAR ( 255 ) UNIQUE NOT NULL,
links(>  created_on TIMESTAMP NOT NULL,
links(>         last_login TIMESTAMP
links(> );

CREATE TABLE
links=> \dt
 public | accounts | table | links

links=> drop table accounts;
DROP TABLE

I can also provide you with php-fpm uwsgi stack trace if requested.

Expected behavior
I think, there should be some sort of communication between database server and web application. Maybe some tables created, some fields filled with some data. Installation process should not be stopped with error ‘MySQL server has gone away’.

LinkAce setup (please complete the following information):

  • Version: [e.g. 0.0.43]
  • Installed via: [e.g. PHP, Docker]
  • OS: [e.g. Ubuntu, CentOS]

Desktop (please complete the following information if applicable):

  • OS: [e.g. iOS]
  • Browser [e.g. chrome, safari]
  • Version [e.g. 22]

Smartphone (please complete the following information if applicable):

  • Device: [e.g. iPhone6]
  • OS: [e.g. iOS8.1]
  • Browser [e.g. stock browser, safari]
  • Version [e.g. 22]

Additional context
Add any other context about the problem here.

I guess you get this error while running the setup?
If yes: it does not support Postgres yet. Please run php artisan migrate first to setup the database. Then use the php artisan registeruser command to register your user account.

1 Like