{:error, "Table 'requests' could not be found"}

Hey, great library. I almost started writing this myself, but glad I didn't, because yours is so much better than what I had imagined.

So. Works in dev, deployed to fly.io and it produces an error. No events are logged, but the page opens.

2024-11-01T12:31:23Z app[2871ed6b664758] arn [info]** (MatchError) no match of right hand side value: {:error, "Table 'requests' could not be found"}
2024-11-01T12:31:23Z app[2871ed6b664758] arn [info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/repo.ex:215: PhoenixAnalytics.Repo.insert_many/1
2024-11-01T12:31:23Z app[2871ed6b664758] arn [info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/services/batcher.ex:101: PhoenixAnalytics.Services.Batcher.handle_info/2

and then some lines later it's

2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]** (MatchError) no match of right hand side value: {:error, "Catalog Error: Table with name requests does not exist!\nDid you mean \"pg_sequences\"?\nLINE 1: ...urce': path, 'visits': COUNT(*) } FROM requests\n                                                  ^"}
2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/repo.ex:258: PhoenixAnalytics.Repo.execute_fetch/1
2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/web/live/components/charts/popular_chart.ex:54: PhoenixAnalytics.Web.Live.Components.PopularChart.fetch_data/3
2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/services/cache.ex:92: PhoenixAnalytics.Services.Cache.fetch/2
2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/web/live/components/charts/popular_chart.ex:41: PhoenixAnalytics.Web.Live.Components.PopularChart.chart_data/2
2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/web/live/components/charts/popular_chart.ex:34: anonymous fn/2 in PhoenixAnalytics.Web.Live.Components.PopularChart.update/2
2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]    (phoenix_live_view 1.0.0-rc.7) lib/phoenix_live_view/async.ex:148: anonymous fn/2 in Phoenix.LiveView.Async.assign_async/4
2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]    (phoenix_live_view 1.0.0-rc.7) lib/phoenix_live_view/async.ex:220: Phoenix.LiveView.Async.do_async/5
2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]    (elixir 1.17.2) lib/task/supervised.ex:101: Task.Supervised.invoke_mfa/2
2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]Function: #Function<7.88405273/0 in Phoenix.LiveView.Async.run_async_task/5>
2024-11-01T12:31:38Z app[2871ed6b664758] arn [info]    Args: []

I would suspect that the migrations haven't been run, but as you know fly.io handles that and I haven't had any issues with migrations before.

Just to be clear I do have this migration.

defmodule MyApp.Repo.Migrations.AddPhoenixAnalytics do
  use Ecto.Migration

  def up, do: PhoenixAnalytics.Migration.up()
  def down, do: PhoenixAnalytics.Migration.down()
end

I checked manually and select * from schema_migrations; confirms the migrations have run.

and my config.exs is

config :phoenix_analytics,
  duckdb_path: System.get_env("DUCKDB_PATH") || "analytics.duckdb",
  app_domain: System.get_env("PHX_HOST") || "example.com"

I have not set the DUCKDB_PATH, as I understand there is no need? Prod does have HOST configured.

I'm experiencing the same issue.

It seems in PhoenixAnalytics.Queries.Table, @db_alias is being appended directly to the table name, and attempt to create a table "postgres_db.requests" rather than just "requests".

defmodule PhoenixAnalytics.Queries.Table do
  @moduledoc false
  alias PhoenixAnalytics.Services.Utility

  @db_alias "postgres_db"
  @requests if Utility.mode() == :duck_postgres, do: "#{@db_alias}.requests", else: "requests"

  def name() do
    @requests
  end

  def create_requests do
    query = """
    CREATE TABLE IF NOT EXISTS #{@requests} (
      request_id UUID PRIMARY KEY,
      method VARCHAR NOT NULL,
      ...

docs to the Utility,.mode() are here: https://hexdocs.pm/phoenix_analytics/PhoenixAnalytics.Services.Utility.html#mode/0

so basically if both duck and postgres are configured that db_alias is used. in my case indeed both are used. question is what's different from my dev to prod.

Hi @KristerV ,

Thank you for raising issue, I never worked with fly io. Could you please provide example how connection to db looks there?

As I can add simple parameter "ignore_alias" or something similar.

oh, right, i thought i read that you use fly.io actually.

i'll provide verbose info, who knows what will explain the issue.

First of all fly.io servers are shown as such:

The only secrets the app has are DATABASE_URL and SECRET_KEY_BASE.

Postgres server info:

It's worth noting that I tried looking for a requests table with a prefix or otherwise and could not find one. But I also couldn't find one in local dev (where analytics do work) so I guess I just don't know what I'm looking for. Is it really a DuckDB table?

Other than that there's not much more i can talk about the Postgres server. Don't think there's anything special about it.

App server

Is it possible that since DuckDB tries to make a local file that the app server is important to discuss? Fly creates a docker image and deploys that. Here's the Dockerfile:

# Find eligible builder and runner images on Docker Hub. We use Ubuntu/Debian
# instead of Alpine to avoid DNS resolution issues in production.
#
# https://hub.docker.com/r/hexpm/elixir/tags?page=1&name=ubuntu
# https://hub.docker.com/_/ubuntu?tab=tags
#
# This file is based on these images:
#
#   - https://hub.docker.com/r/hexpm/elixir/tags - for the build image
#   - https://hub.docker.com/_/debian?tab=tags&page=1&name=bullseye-20240904-slim - for the release image
#   - https://pkgs.org/ - resource for finding needed packages
#   - Ex: hexpm/elixir:1.17.2-erlang-27.0.1-debian-bullseye-20240904-slim
#
ARG ELIXIR_VERSION=1.17.2
ARG OTP_VERSION=27.0.1
ARG DEBIAN_VERSION=bullseye-20240904-slim

ARG BUILDER_IMAGE="hexpm/elixir:${ELIXIR_VERSION}-erlang-${OTP_VERSION}-debian-${DEBIAN_VERSION}"
ARG RUNNER_IMAGE="debian:${DEBIAN_VERSION}"

FROM ${BUILDER_IMAGE} as builder

# install build dependencies
RUN apt-get update -y && apt-get install -y build-essential git \
    && apt-get clean && rm -f /var/lib/apt/lists/*_*

# prepare build dir
WORKDIR /app

# install hex + rebar
RUN mix local.hex --force && \
    mix local.rebar --force

# set build ENV
ENV MIX_ENV="prod"

# install mix dependencies
COPY mix.exs mix.lock ./
RUN mix deps.get --only $MIX_ENV
RUN mkdir config

# copy compile-time config files before we compile dependencies
# to ensure any relevant config change will trigger the dependencies
# to be re-compiled.
COPY config/config.exs config/${MIX_ENV}.exs config/
RUN mix deps.compile

COPY priv priv

COPY lib lib

COPY assets assets

# compile assets
RUN mix assets.deploy

# Compile the release
RUN mix compile

# Changes to config/runtime.exs don't require recompiling the code
COPY config/runtime.exs config/

COPY rel rel
RUN mix release

# start a new build stage so that the final image will only contain
# the compiled release and other runtime necessities
FROM ${RUNNER_IMAGE}

RUN apt-get update -y && \
  apt-get install -y libstdc++6 openssl libncurses5 locales ca-certificates \
  && apt-get clean && rm -f /var/lib/apt/lists/*_*

# Set the locale
RUN sed -i '/en_US.UTF-8/s/^# //g' /etc/locale.gen && locale-gen

ENV LANG en_US.UTF-8
ENV LANGUAGE en_US:en
ENV LC_ALL en_US.UTF-8

WORKDIR "/app"
RUN chown nobody /app

# set runner ENV
ENV MIX_ENV="prod"

# Only copy the final release from the build stage
COPY --from=builder --chown=nobody:root /app/_build/${MIX_ENV}/rel/kids ./

USER nobody

# If using an environment that doesn't automatically reap zombie processes, it is
# advised to add an init process such as tini via `apt-get install`
# above and adding an entrypoint. See https://github.com/krallin/tini for details
# ENTRYPOINT ["/tini", "--"]

CMD ["/app/bin/server"]

Then anything interesting for the deployment itself should be in fly.toml:

# fly.toml app configuration file generated for kids on 2024-10-28T16:19:07+02:00
#
# See https://fly.io/docs/reference/configuration/ for information about how to use this file.
#

app = 'kids'
primary_region = 'arn'
kill_signal = 'SIGTERM'

[build]

[deploy]
  release_command = '/app/bin/migrate'

[env]
  PHX_HOST = 'example.com'
  PORT = '8080'

[http_service]
  internal_port = 8080
  force_https = true
  auto_stop_machines = true
  auto_start_machines = true
  min_machines_running = 1
  processes = ['app']

  [http_service.concurrency]
    type = 'connections'
    hard_limit = 1000
    soft_limit = 1000

[[vm]]
  memory = '1gb'
  cpu_kind = 'shared'
  cpus = 1

Anything else that could help understand the issue?

What confuses me is that the mode is determined from the config:

https://github.com/lalabuy948/PhoenixAnalytics/blob/6b94e9d5970371c097413f0fbe4cf388a3523cba/lib/phoenix_analytics/services/utility.ex#L105-L114

But in my config I don't have postgres configured..

edit: well i deployed a debug version of this repo (master) and this particular error is fixed apparently. it now knows it's not using postgres in the config. so the current state is better than the last version (0.2.1).

yet i get another error. from the code it seems like it's looking for the DuckDB table, but it doesn't exist.

[info]09:54:13.802 [error] Task #PID<0.5566.0> started from #PID<0.5558.0> terminating
[info]** (MatchError) no match of right hand side value: {:error, "Catalog Error: Table with name requests does not exist!\nDid you mean \"pg_sequences\"?\nLINE 1: SELECT count(DISTINCT remote_ip) FROM requests\n                                              ^"}
[info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/repo.ex:258: PhoenixAnalytics.Repo.execute_fetch/1
[info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/web/live/components/stats/single_stat.ex:71: PhoenixAnalytics.Web.Live.Components.SingleStat.fetch_stat_data/3
[info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/services/cache.ex:92: PhoenixAnalytics.Services.Cache.fetch/2
[info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/web/live/components/stats/single_stat.ex:55: PhoenixAnalytics.Web.Live.Components.SingleStat.stat_data/2
[info]    (phoenix_analytics 0.2.1) lib/phoenix_analytics/web/live/components/stats/single_stat.ex:45: anonymous fn/2 in PhoenixAnalytics.Web.Live.Components.SingleStat.update/2
[info]    (phoenix_live_view 1.0.0-rc.7) lib/phoenix_live_view/async.ex:148: anonymous fn/2 in Phoenix.LiveView.Async.assign_async/4
[info]    (phoenix_live_view 1.0.0-rc.7) lib/phoenix_live_view/async.ex:220: Phoenix.LiveView.Async.do_async/5
[info]    (elixir 1.17.2) lib/task/supervised.ex:101: Task.Supervised.invoke_mfa/2

so I SSH into the container and find that the duckdb file is right next to the executables, which are not persistent. in fact now that i think about it the whole container is ephermal. and creating a volume for it doesn't really make sense as fly.io gives you multiple physical machines per app by default. no point in scaling down.

so i'm going to go the other way and see if i can configure this on postgres.

if I configure like this:

config :phoenix_analytics,
  app_domain: System.get_env("PHX_HOST") || "example.com",
  postgres_conn: "dbname=phoenixanalytics user=postgres password=postgres host=localhost",
  cache_ttl: 120

Then I just get an error:

14:20:50.835 [info] == Running 20241101122212 MyApp.Repo.Migrations.AddPhoenixAnalytics.up/0 forward
** (FunctionClauseError) no function clause matching in Duckdbex.open/1    

    The following arguments were given to Duckdbex.open/1:

        # 1
        nil

    Attempted function clauses (showing 2 out of 2):

        def open(path) when is_binary(path)
        def open(%Duckdbex.Config{} = config)

    (duckdbex 0.3.6) Duckdbex.open/1
    (phoenix_analytics 0.2.1) lib/phoenix_analytics/migration.ex:11: PhoenixAnalytics.Migration.up/0
    (ecto_sql 3.12.1) lib/ecto/migration/runner.ex:310: Ecto.Migration.Runner.perform_operation/3
    (stdlib 6.0.1) timer.erl:590: :timer.tc/2
    (ecto_sql 3.12.1) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8
    (ecto_sql 3.12.1) lib/ecto/migrator.ex:365: Ecto.Migrator.attempt/8
    (ecto_sql 3.12.1) lib/ecto/migrator.ex:282: anonymous fn/5 in Ecto.Migrator.do_up/5
    (ecto_sql 3.12.1) lib/ecto/migrator.ex:337: anonymous fn/6 in Ecto.Migrator.async_migrate_maybe_in_transaction/7

So this means that PostgreSQL alone isn't enough. It must have DuckDB configured also (but why?), in which case I still get the error in the above post. I'm full of frustration.

All in all it seems PhoenixAnalytics does not support fly.io or clusters.

@lalabuy948 if you want to debug this maybe it makes sense to try fly.io? it's very popular in the Elixir community. They have a free tier and to deploy all you do is fly launch and it'll ask some questions and done soon. Very small effort. Please! :)

Hi @KristerV

Could you add as well duck db path into your config?

In case you use Postgres as backend, duck db file will be empty, but it's still needed for duck db to operate properly.

okay, so

config.exs

config :phoenix_analytics,
  duckdb_path: System.get_env("DUCKDB_PATH") || "analytics.duckdb",
  app_domain: System.get_env("PHX_HOST") || "example.com",
  postgres_conn: "dbname=phoenixanalytics user=postgres password=postgres host=localhost",
  cache_ttl: 120

runtime.exs

  config :phoenix_analytics,
    app_domain: host,
    postgres_conn: database_url,
    cache_ttl: 120

so both postgres and duck have configs. but still same error:

[info]** (MatchError) no match of right hand side value: {:error, "Catalog Error: Table with name requests does not exist!\nDid you mean \"analytics.pg_catalog.pg_sequences\"?"}

what else can we try?

lalabuy948 / PhoenixAnalytics

{:error, "Table 'requests' could not be found"} #31