Getting SQL server working on Docker

[tomMoulard]

Describe the bug
Hi there ! I've just made a repository to host my Dockerfile (and docker-compose.yml) for this project. But I can't figure out how to get a proper SQL server working with it.

To Reproduce
Steps to reproduce the behavior:

1. Clone my repo and change to head to the first commit
2. make

Expected behavior
Make should work and the server should be working localy. But instead, I get this:

2fauth_1           |    Illuminate\Database\QueryException 
2fauth_1           | 
2fauth_1           |   could not find driver (SQL: insert into `oauth_clients` (`user_id`, `name`, `secret`, `provider`, `redirect`, `personal_access_client`, `password_client`, `revoked`, `updated_at`, `created_at`) values (?, 2FAuth Personal Access Client, G7....HA, ?, http://localhost, 1, 0, 0, 2021-01-10 20:00:00, 2021-01-10 20:00:00))
2fauth_1           | 
2fauth_1           |   at vendor/laravel/framework/src/Illuminate/Database/Connection.php:671
2fauth_1           |     667|         // If an exception occurs when attempting to run a query, we'll format the error
2fauth_1           |     668|         // message to include the bindings with SQL, which will make this exception a
2fauth_1           |     669|         // lot more helpful to the developer instead of just the database's errors.
2fauth_1           |     670|         catch (Exception $e) {
2fauth_1           |   > 671|             throw new QueryException(
2fauth_1           |     672|                 $query, $this->prepareBindings($bindings), $e
2fauth_1           |     673|             );
2fauth_1           |     674|         }
2fauth_1           |     675|
2fauth_1           | 
2fauth_1           |       +51 vendor frames 
2fauth_1           |   52  artisan:37
2fauth_1           |       Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

Desktop (please complete the following information):

• base image composer:2.0
• Browser : Google Chrome 87.0.4280.141
• Version : lastest (commit hash: 4a9f73f6a1650d106b0844f2849b9cf091ca880c)

[Bubka]

Hello Tom,

I'm not familiar with docker, but I think the issue stands in your start.sh file. You run artisan passport:install before artisan migrate refresh.

The artisan migrate refresh command creates tables used by artisan passport:install so you need to run it first.

[tomMoulard]

Oh! nice, thanks !
But now I get this error, when doing php artisan migrate:refresh:

could not find driver (SQL: select * from information_schema.tables where table_schema = homestead and table_name = migrations and table_type = 'BASE TABLE')

If you want my environment variables:

2fauth_1        | + env
2fauth_1        | DB_PASSWORD=mysql
2fauth_1        | SITE_OWNER=changeme@example.com
2fauth_1        | MAIL_PORT=2525
2fauth_1        | MAIL_DRIVER=log
2fauth_1        | REDIS_PASSWORD=null
2fauth_1        | HOSTNAME=9a37ed2e7212
2fauth_1        | PHP_VERSION=7.4.14
2fauth_1        | REDIS_HOST=2fauth-redis
2fauth_1        | PHP_INI_DIR=/usr/local/etc/php
2fauth_1        | GPG_KEYS=42670A7FE4D0441C8E4632349E4FDC074A4EF02D 5A52880781F755608BF815FC910DEB46F53EA312
2fauth_1        | PHP_LDFLAGS=-Wl,-O1 -pie
2fauth_1        | MIX_PUSHER_APP_CLUSTER=
2fauth_1        | PWD=/2fauth
2fauth_1        | CACHE_DRIVER=file
2fauth_1        | MAIL_FROM_ADDRESS=null
2fauth_1        | APP_LOG_LEVEL=notice
2fauth_1        | DB_PORT=3306
2fauth_1        | DB_USER=mysql
2fauth_1        | HOME=/
2fauth_1        | PUSHER_APP_CLUSTER=mt1
2fauth_1        | MAIL_USERNAME=null
2fauth_1        | APP_KEY=
2fauth_1        | APP_ENV=local
2fauth_1        | MAIL_PASSWORD=null
2fauth_1        | APP_DEBUG=false
2fauth_1        | IS_DEMO_APP=false
2fauth_1        | APP_URL=http://localhost
2fauth_1        | PHP_SHA256=f9f3c37969fcd9006c1dbb1dd76ab53f28c698a1646fa2dde8547c3f45e02886
2fauth_1        | PHPIZE_DEPS=autoconf 		dpkg-dev dpkg 		file 		g++ 		gcc 		libc-dev 		make 		pkgconf 	re2c
2fauth_1        | DB_USERNAME=homestead
2fauth_1        | PUSHER_APP_ID=
2fauth_1        | DB_CONNECTION=mysql
2fauth_1        | DB_HOST=2fauth-mysql
2fauth_1        | PHP_URL=https://www.php.net/distributions/php-7.4.14.tar.xz
2fauth_1        | PUSHER_APP_KEY=
2fauth_1        | PUSHER_APP_SECRET=
2fauth_1        | MAIL_FROM=changeme@example.com
2fauth_1        | MIX_PUSHER_APP_KEY=
2fauth_1        | APP_NAME=2FAuth
2fauth_1        | SHLVL=1
2fauth_1        | COMPOSER_ALLOW_SUPERUSER=1
2fauth_1        | COMPOSER_VERSION=2.0.8
2fauth_1        | LOG_CHANNEL=daily
2fauth_1        | PHP_CFLAGS=-fstack-protector-strong -fpic -fpie -O2 -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64
2fauth_1        | BROADCAST_DRIVER=log
2fauth_1        | MAIL_FROM_NAME=null
2fauth_1        | REDIS_PORT=6379
2fauth_1        | DB_NAME=mysql
2fauth_1        | SESSION_DRIVER=file
2fauth_1        | PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
2fauth_1        | MAIL_ENCRYPTION=null
2fauth_1        | MAIL_HOST=smtp.mailtrap.io
2fauth_1        | DB_DATABASE=homestead
2fauth_1        | SESSION_LIFETIME=12
2fauth_1        | QUEUE_DRIVER=sync
2fauth_1        | COMPOSER_HOME=/tmp
2fauth_1        | PHP_ASC_URL=https://www.php.net/distributions/php-7.4.14.tar.xz.asc
2fauth_1        | MIX_ENV=local
2fauth_1        | PHP_CPPFLAGS=-fstack-protector-strong -fpic -fpie -O2 -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64
2fauth_1        | _=/usr/bin/env

[Bubka]

Try with php artisan migrate:fresh instead, it will drop tables and rerun the migration properly

[Bubka]

If the script is used by Docker to initialize a brand new instance of 2FAuth (with an empty database) each time it is run, you can simply use php artisan migrate, by the way.

[cn-ml]

Hi @tomMoulard. Any progress with your Docker setup? I see you had problems with your sql server. Have you managed to set the project up with mysql before?

[tomMoulard]

Hi @OnlyFreeUsername,

No chance on my side, I've been using 2FAuth with sqlite since.

I've push a new branch on my repository to try to get postgresql working on Docker.
To build the stack localy, do: make.

Once all is complete, I still have this log (see attached file)
log.txt

If any of you get an idea on how to fix this, I'm all ears. Thanks !

[Bubka]

Hello Tom,

looks like the DB_CONNECTION=sqlite declaration in your dockerfile has priority over the DB_CONNECTION=postgres of your docker-compose.yml file 🤔

See line 125 of your logs:

2fauth_1           |   Database (/2fauth/database/database.sqlite) does not exist. (SQL: PRAGMA foreign_keys = ON;)

But this should not occurs according to the docker-compose manual:

When you set the same environment variable in multiple files, here’s the priority used by Compose to choose which value to use:

Compose file
Shell environment variables
Environment file
Dockerfile
Variable is not defined

Regarding your first issue

I tried to build/run with your setup (main branch of your repo, head detached to first commit) but without success: the php requirement fails at composer install:

• 2FAuth composer.json ask for php 7.4
• The composer:2.0 base image called in your Dockerfile is itself based on php:8-alpine.

How did you get the build to end successfully?

[Bubka]

Regarding your first issue

I tried to build/run with your setup (main branch of your repo, head detached to first commit) but without success: the php requirement fails at composer install:

* 2FAuth `composer.json` ask for php 7.4

* The `composer:2.0` base image called in your `Dockerfile` is itself based on `php:8-alpine`.

How did you get the build to end successfully?

arf, composer have upgraded the base image on 7 Jan, few days before your post : composer/docker@2577b32

[cn-ml]

I built my composer image myself based on your composer.lock file using php 7.3. I didn't get the configuration right but maybe you can start off with this dockerfile snippet:

FROM php:7.3

WORKDIR /app

# install zip unzip extensions for composer
RUN apt-get update \
	&& apt-get -y install zip unzip \
	&& rm -rf /var/lib/apt/lists/*

# install composer on top of php:7.3 for composer.lock compatibility
RUN php -r "copy('https://getcomposer.org/installer', 'composer-setup.php');" \
	&& php -r "if (hash_file('sha384', 'composer-setup.php') === '756890a4488ce9024fc62c56153228907f1545c228516cbf63f885e036d37e9a59d27d63f46af1d4d07ee0f76181c7d3') { echo 'Installer verified'; } else { echo 'Installer corrupt'; unlink('composer-setup.php'); } echo PHP_EOL;" \
	&& php composer-setup.php \
	&& php -r "unlink('composer-setup.php');"

[Bubka]

@OnlyFreeUsername You need to downgrade composer to its 1.10 branch because 2FAuth is not yet compatible with the 2.0 branch.

Simply add RUN composer self-update --1 before running RUN composer install

[tomMoulard]

Oh! I did not know that 2fauth did not work with composer 2. It is strange that I could build the image in the first place.
I found out that the docker image composer:1.10.19 uses composer v1 and php 7.4.

But there is something strange when building:

Database (/2fauth/database/database.sqlite) does not exist. (SQL: PRAGMA foreign_keys = ON;)

Even when the enviromement variable is set to DB_CONNECTION=postgres.

See the log attached, and the repository to build (using make).

build.log
run.log

[Bubka]

Oh! I did not know that 2fauth did not work with composer 2.

I'm not absolutely sure about this. Actually I have had some complains from the composer cli about the version during some of my builds so I downgrade to 1.10 branch to prevent any issue. I plan to push a new release of 2FAuth for some dependency updates with an upgraded composer.lock to clarify the situation.

Regarding your postgres issue, i pushed my investigation further. I can get all containers loading correctly by I face another pb, i'm working on it. What I can tell for now is that:

• The base image you use in the dockerfile composer:1.10 does not contain postgres_pdo php extension, you have to install it
• You run all artisan commands with DB_CONNECTION=sqlite in dockerfile so you populate a sqlite db at build, then you try to set a postgres database in docker-compose but you did not run artisan commands on this db. artisan commands should be moved to startup.sh imho.
• DB_CONNECTION value for postgres is pgsql with Laravel
• The migration command to run is php artisan migrate otherwise you will reset your db at every container startup

More to come soon

[tomMoulard]

Regarding your postgres issue, i pushed my investigation further.

Thank you very much 😄
I've made some changes too: You can see now the result of the build directly in GitHub action.

And I've changed things:

• I've installed php7-pgsql, php7-pdo, php7-pdo_pgsql.
• I've moved all artisan commands in the startup.sh script.
• I've updated the value of DB_CONNECTION to pgsql.
• I've kept php artisan migrate for testing purposes.

And even with all those changes, I get an error:

could not find driver (SQL: select * from information_schema.tables where table_schema = public and table_name = migrations and table_type = 'BASE TABLE')

[Bubka]

hum... not sure your addition for postgres_pdo installation is sufficient.
I use this from my side and I don't have the could not find driver error:

RUN set -eux \
  && apk --no-cache add \
    postgresql-dev

RUN docker-php-ext-install pdo_pgsql

[tomMoulard]

Thanks for the tip :)

Doing the changes allow building and run the app, but I go an error when doing a cURL on the app:

2fauth_1           | [Fri Mar 19 12:58:03 2021] 172.21.0.1:55292 Accepted
2fauth-postgres_1  | ERROR:  relation "options" does not exist at character 15
2fauth-postgres_1  | STATEMENT:  select * from "options" where "key" = $1 limit 1
2fauth_1           | [Fri Mar 19 12:58:03 2021] 172.21.0.1:55292 Closing

Also, do you think I should install more dependencies for mysql of sqlsrv databases ?

[Bubka]

This is where I stand.

And there is something really weird: When I look into the tommoulard/2fauth running container, /2fauth/database folder is empty! There should be all the migration files that bootstrap the database.

I checked existing tables in the postgres container and there is no trace of 2FAuth migrations, only those run by php artisan passport. This explains the postgres error you quoted, the options table is the first hit by Laravel at run time.

It is beyond me why the hell all migrations have vanished!

Can you confirm you have the same state?

[Bubka]

Ok, here is the error, in docker-compose.yml

You bind an empty folder to a folder that already contains something, so docker empties it.

    volumes:
      - './2fauth/db/:/2fauth/database'

Simply remove the volume definition as it is useless with the postgres container and 2FAuth will run 😀

[tomMoulard]

I've tried removing the volume, but the 500 persists. tomMoulard/2FAuth-docker@ad94c99

Test:

curl -f http://localhost:8080
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed

  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
curl: (22) The requested URL returned error: 500 Internal Server Error

[Bubka]

You cURLed localhost but the Laravel dev server starts on 0.0.0.0 (via startup.sh)
I'm not an expert on network interface, but localhost shouldn't be a loopback to 127.0.0.1?

[tomMoulard]

Well, Laravel binds the 0.0.0.0 address, so it can receive all incoming requests.
And I curl localhost so I can reach the Laravel service.
Also, If you had put Laravel to listen on localhost, you would not be able to communicate with it outside the container.

[tomMoulard]

Either way, after the successful start of the server, I still get a 500 at this point:

> curl localhost:8080
{
    "message": "Server Error"
}{
    "message": "Server Error"
}

2fauth_1           | [Fri Mar 26 16:38:39 2021] 192.168.240.1:56798 Accepted
2fauth_1           | [Fri Mar 26 16:38:39 2021] 192.168.240.1:56798 Closing