ERROR 42P01 (undefined_table) relation "myrelation" does not exist

JulienCorb

I am creating a Docker container for my umbrella app that so far contains just one phoenix API app.

This application has simply one DB table that is made of political parties, called “parties”.

when I run docker-compose up and visit http://localhost:4001/api/v1/parties I get the following error: ERROR 42P01 (undefined_table) relation "parties" does not exist

Obviously my table does not exists. However I do run my migrations in my Dockerfile:


# ./Dockerfile
# Elixir base image to start with
FROM elixir:1.8

# install hex package manager
RUN mix local.hex --force
RUN mix local.rebar --force

# install the latest Phoenix
RUN mix archive.install https://github.com/phoenixframework/archives/raw/master/phoenix_new.ez --force

# install NodeJS and NPM
RUN curl -sL https://deb.nodesource.com/setup_10.x -o nodesource_setup.sh
RUN bash nodesource_setup.sh
RUN apt-get install nodejs
RUN apt-get install -y inotify-tools

# create our app folder and copy our code in it and set it as default
RUN mkdir /app
COPY . /app
WORKDIR /app

# install dependencies
RUN mix deps.get
RUN mix deps.compile

# We can't separate the command as each command create a temporary container
# and each container as it own temporary variables

# run migrations
CMD mix ecto.migrate

# run phoenix server. The CMD is run each time the container is launched.
CMD mix phx.server

and here is my docker-compose.yml :

# the version of docker compose we use
version: '2'

services:
    # the first container will be called postgres
    postgres:
        # the image is the last official postgres image
        image: postgres
        # the volumes allow us to have a shared space between our computer and the docker vm
        volumes:
            - "./.data/postgres:/var/lib/postgresql"
         # set up environment variable for the postgres instance
        environment:
            POSTGRES_USER: ${PSQL_USER}
            POSTGRES_PASSWORD: ${PSQL_PWD}
            POOL: 100
        # the port to listen
        ports:
            - "5432:5432"
    # the second container is called redis
    redis:
        # the image is the last official redis image of the version 5
        image: redis:5
        ports:
            - "6379:6379"
        volumes:
            - ./.data/redis:/var/lib/redis
        # set up environment variable for the redis instance
        environment:
            REDIS_PASSWORD: ${REDIS_PWD}
    # our last container is called elixir
    elixir:
        # build use a path to a Dockerfile
        build: .
        # we set multiple ports as each of our application (but database) will use a different port
        ports:
            - "4001:4001"
            - "4101:4101"
        # we share the entire app with the container, but the libs
        volumes:
            - ".:/app"
            - "/app/deps"
            - "/app/apps/admin/assets/node_modules"
        # the container will not start if the postgres container isn't running
        depends_on:
            - postgres
        # set up environment variable for the phoenix instance
        environment:
            POSTGRES_USER: ${PSQL_USER}
            POSTGRES_PASSWORD: ${PSQL_PWD}
            POSTGRES_DB_TEST: ${PSQL_DB_TEST}
            POSTGRES_DB_DEV: ${PSQL_DB}
            POSTGRES_HOST: ${PSQL_HOST}

What is wrong with my code ?

9 comments

/phoenix #ecto #docker #umbrella

0 5587 9

2019-03-21 11:20:07 UTC

Showing Posts 1 to 9

Show Best Posts
Show All (oldest first)
Show All (newest first)

NobbZ

When you build the container, the database does not run. The build of your container should fail.

You need to move creating and migrating the database to your entrypoint.

Post #1

JulienCorb

Hi @NobbZ, thank you very much for your help !

Indeed I was silencing the postgres’ container logs, I just realised I get [error] Postgrex.Protocol (#PID<0.279.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (172.18.0.2:5432): connection refused - :econnrefused

I manually created my DB though by hand :
docker-compose exec postgres psql user user
CREATE DATABASE test_dev; CREATE DATABASE test_test;

So I guess all I need is to run the migrations ?

Also I do not understand exactly what you mean by

Does it mean I have to move something in my app’s endpoint.ex ?

Post #2

NobbZ

Entrypoint in this context, is what docker runs as ENTRYPOINT. You are using CMD though.

I never really got the difference of these 2, but started to use ENTRYPOINT only, about 2 years back, and never had any issues because of this.

Basically, you need to run a script which as CMD or ENTRYPOINT that creates your DB if it did not exist yet, then runs your migrations and only then starts the application.

Usually a shell script like this is used:

#!/usr/bin/env sh

set -ex # prints each command and fails the script on first error

mix ecto.create || echo "Could not create database, assume it exist already"
mix ecto.migrate
exec mix phx.server

Put this as entrypoint.sh in your project, and chmod +x it. Make sure its in your container and then use ENTRYPOINT entrypoint.sh instead of CMD.

Post #3

NobbZ

I just realise, you are using two CMD, this is not allowed, only the last CMD specified will be used, it overwrites any previous set CMD.

On my first read I saw this as a RUN.

Post #4

JulienCorb

Thank you very much !!

I think I’m almost there
I created entrypoint.sh at the root of my project, then ran chmod +x /Users/juliencorbin/political_project/entrypoint.sh
but got elixir_1 | /bin/sh: 1: entrypoint.sh: not found

did I run the chmod command wrong ?

Post #5

NobbZ

Try using /app/entrypoint.sh just to be sure.

Post #6

JulienCorb

Ok it worked !

but it couldn’t migrate the DB

here are the logs :

postgres_1  | 2019-03-21 11:07:55.419 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres_1  | 2019-03-21 11:07:55.419 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres_1  | 2019-03-21 11:07:55.421 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
elixir_1    | + mix ecto.create
postgres_1  | 2019-03-21 11:07:55.435 UTC [22] LOG:  database system was shut down at 2019-03-21 10:49:47 UTC
postgres_1  | 2019-03-21 11:07:55.441 UTC [1] LOG:  database system is ready to accept connections
elixir_1    | 
elixir_1    | 11:08:02.895 [error] GenServer #PID<0.220.0> terminating
elixir_1    | ** (DBConnection.ConnectionError) tcp connect (172.18.0.2:5432): connection refused - :econnrefused
elixir_1    |     (db_connection) lib/db_connection/connection.ex:84: DBConnection.Connection.connect/2
elixir_1    |     (connection) lib/connection.ex:622: Connection.enter_connect/5
elixir_1    |     (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
elixir_1    | Last message: nil
elixir_1    | State: Postgrex.Protocol
elixir_1    | ** (Mix) The database for Comitia.Repo couldn't be created: killed
elixir_1    | + echo Could not create database, assume it exist already
elixir_1    | + mix ecto.migrate
elixir_1    | Could not create database, assume it exist already
elixir_1    | [error] Postgrex.Protocol (#PID<0.249.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (172.18.0.2:5432): connection refused - :econnrefused
elixir_1    | [error] Postgrex.Protocol (#PID<0.248.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (172.18.0.2:5432): connection refused - :econnrefused
elixir_1    | [error] Postgrex.Protocol (#PID<0.248.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (172.18.0.2:5432): connection refused - :econnrefused
elixir_1    | [error] Postgrex.Protocol (#PID<0.249.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (172.18.0.2:5432): connection refused - :econnrefused
elixir_1    | [error] Could not create schema migrations table. This error usually happens due to the following:
elixir_1    | 
elixir_1    |   * The database does not exist
elixir_1    |   * The "schema_migrations" table, which Ecto uses for managing
elixir_1    |     migrations, was defined by another library
elixir_1    | 
elixir_1    | To fix the first issue, run "mix ecto.create".
elixir_1    | 
elixir_1    | To address the second, you can run "mix ecto.drop" followed by
elixir_1    | "mix ecto.create". Alternatively you may configure Ecto to use
elixir_1    | another table for managing migrations:
elixir_1    | 
elixir_1    |     config :comitia, Comitia.Repo,
elixir_1    |       migration_source: "some_other_table_for_schema_migrations"
elixir_1    | 
elixir_1    | The full error report is shown below.
elixir_1    | 
elixir_1    | ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2727ms. You can configure how long requests wait in the queue using :queue_target and :queue_interval. See DBConnection.start_link/2 for more information
elixir_1    |     (ecto_sql) lib/ecto/adapters/sql.ex:624: Ecto.Adapters.SQL.raise_sql_call_error/1
elixir_1    |     (elixir) lib/enum.ex:1327: Enum."-map/2-lists^map/1-0-"/2
elixir_1    |     (ecto_sql) lib/ecto/adapters/sql.ex:711: Ecto.Adapters.SQL.execute_ddl/4
elixir_1    |     (ecto_sql) lib/ecto/migrator.ex:493: Ecto.Migrator.verbose_schema_migration/3
elixir_1    |     (ecto_sql) lib/ecto/migrator.ex:269: Ecto.Migrator.run/4
elixir_1    |     (ecto_sql) lib/mix/tasks/ecto.migrate.ex:110: anonymous fn/4 in Mix.Tasks.Ecto.Migrate.run/2
elixir_1    |     (elixir) lib/enum.ex:769: Enum."-each/2-lists^foreach/1-0-"/2
elixir_1    |     (elixir) lib/enum.ex:769: Enum.each/2
elixir_1    |     (mix) lib/mix/task.ex:331: Mix.Task.run_task/3
elixir_1    |     (mix) lib/mix/cli.ex:79: Mix.CLI.run_task/2
elixir_1    |     (elixir) lib/code.ex:767: Code.require_file/2
political_project_elixir_1 exited with code 1

maybe I should remove the DB I created by hand ?

Post #7

NobbZ

No, use the hostname postgres to connect to the database rather than an IP that you’ll never know if it will be correct after the next restart of the database.

Never rely on IPs inside of your composed network, only ever use the service names, docker provides a DNS under the hood that will let them resolve properly.

Post #8