UI - DB_TYPE : postgres / sqlite in k8s

[qdrddr]

Describe the bug The DB_TYPE : postgres or sqlite for the UI container using the correct backend and seems to be working. In both cases, the UI in k8s cannot create test data. There is no issue with it if running via docker-compose locally and it seems to be related to the k8s environment only.

To Reproduce Steps to reproduce the behavior:

1. Replace DB_TYPE with postgres value in the docker-compose or k8s manifest/ConfigMap
2. Deploy wrenai to your k8s
3. in the UI press "Play around with sample data".
4. Observe logs in the wren-ui container

Expected behavior UI starting with version 0.60 is expected to be able to use the PostgreSQL backend

Screenshots

Server (please complete the following information):

• OS: Linux, k8s

WrenAI Information

• WREN_UI_VERSION : 0.6.0
• WREN_ENGINE_VERSION : 0.4.7
• WREN_PRODUCT_VERSION : 0.3.6
• wren-bootstrap: 0.1.4
• wren-ai-service: 0.4.0

Additional context

Using Postgres
Batch 1 run: 17 migrations
  ▲ Next.js 13.5.6
  - Local:        http://localhost:3000
  - Network:      http://0.0.0.0:3000

 ✓ Ready in 359ms
[2024-06-06T15:03:59.138] [INFO] TELEMETRY - Telemetry not enabled.
using pg
[2024-06-06T15:03:59.197] [INFO] AskingService - Background tracker started
[2024-06-06T15:03:59.221] [INFO] AskingService - Initialization: adding unfininshed thread responses (total: 0) to background tracker
Persisted queries are enabled and are using an unbounded cache. Your server is vulnerable to denial of service attacks via memory exhaustion. Set `cache: "bounded"` or `persistedQueries: false` in your ApolloServer constructor, or see https://go.apollo.dev/s/cache-backends for other alternatives.
[2024-06-06T15:04:00.689] [DEBUG] DataSourceResolver - {
  initSql: "CREATE TABLE game AS FROM read_csv('https://wrenai-public.s3.amazonaws.com/demo/v0.3.0/NBA/game.csv',header=true, columns={'SeasonId': 'BIGINT', 'TeamIdHome': 'BIGINT', 'Id': 'BIGINT', 'GameDate': 'DATE', 'WlHome': 'VARCHAR', 'Min': 'BIGINT', 'FgmHome': 'BIGINT', 'FgaHome': 'BIGINT', 'FgPct_home': 'DOUBLE', 'threepHome': 'BIGINT', 'threepaHome': 'BIGINT', 'fg3_pct_home': 'DOUBLE', 'FtmHome': 'BIGINT', 'FtaHome': 'BIGINT', 'ft_pct_home': 'DOUBLE', 'OrebHome': 'BIGINT', 'DrebHome': 'BIGINT', 'RebHome': 'BIGINT', 'AstHome': 'BIGINT', 'StlHome': 'BIGINT', 'BlkHome': 'BIGINT', 'TovHome': 'BIGINT', 'PfHome': 'BIGINT', 'PtsHome': 'BIGINT', 'PlusMinusHome': 'BIGINT', 'TeamIdAway': 'BIGINT', 'WlAway': 'VARCHAR', 'FgmAway': 'BIGINT', 'FgaAway': 'BIGINT', 'fg_pct_away': 'DOUBLE', 'threepAway': 'BIGINT', 'threepaAway': 'BIGINT', 'Fg3_pct_away': 'DOUBLE', 'FtmAway': 'BIGINT', 'FtaAway': 'BIGINT', 'Ft_pct_away': 'DOUBLE', 'OrebAway': 'BIGINT', 'DrebAway': 'BIGINT', 'RebAway': 'BIGINT', 'AstAway': 'BIGINT', 'StlAway': 'BIGINT', 'BlkAway': 'BIGINT', 'TovAway': 'BIGINT', 'PfAway': 'BIGINT', 'PtsAway': 'BIGINT', 'PlusMinusAway': 'BIGINT', 'SeasonType': 'VARCHAR'});\n" +
    "CREATE TABLE line_score AS FROM read_csv('https://wrenai-public.s3.amazonaws.com/demo/v0.3.0/NBA/line_score.csv',header=true, columns={'GameDate': 'DATE', 'GameSequence': 'BIGINT', 'GameId': 'BIGINT', 'TeamIdHome': 'BIGINT', 'TeamWinsLossesHome': 'VARCHAR', 'PtsQtr1Home': 'BIGINT', 'PtsQtr2Home': 'BIGINT', 'PtsQtr3Home': 'BIGINT', 'PtsQtr4Home': 'BIGINT', 'PtsOt1Home': 'BIGINT', 'PtsHome': 'BIGINT', 'TeamIdAway': 'BIGINT', 'TeamWinsLossesAway': 'VARCHAR', 'PtsQtr1Away': 'BIGINT', 'PtsQtr2Away': 'BIGINT', 'PtsQtr3Away': 'BIGINT', 'PtsQtr4Away': 'BIGINT', 'PtsOt1Away': 'BIGINT', 'PtsAway': 'BIGINT'});\n" +
    "CREATE TABLE player_games AS FROM read_csv('https://wrenai-public.s3.amazonaws.com/demo/v0.3.0/NBA/player_game.csv',header=true, columns={'Id': 'BIGINT', 'PlayerID': 'BIGINT', 'GameID': 'BIGINT', 'Date': 'DATE', 'Age': 'VARCHAR', 'Tm': 'VARCHAR', 'Opp': 'VARCHAR', 'MP': 'VARCHAR', 'FG': 'BIGINT', 'FGA': 'BIGINT', 'threeP': 'BIGINT', 'threePA': 'BIGINT', 'FT': 'BIGINT', 'FTA': 'BIGINT', 'ORB': 'BIGINT', 'DRB': 'BIGINT', 'TRB': 'BIGINT', 'AST': 'BIGINT', 'STL': 'BIGINT', 'BLK': 'BIGINT', 'TOV': 'BIGINT', 'PF': 'BIGINT', 'PTS': 'BIGINT'});\n" +
    "CREATE TABLE player AS FROM read_csv('https://wrenai-public.s3.amazonaws.com/demo/v0.3.0/NBA/player.csv',header=true, columns={'Id': 'BIGINT', 'TeamId': 'BIGINT', 'FullName': 'VARCHAR', 'FirstName': 'VARCHAR', 'LastName': 'VARCHAR'});\n" +
    "CREATE TABLE team AS FROM read_csv('https://wrenai-public.s3.amazonaws.com/demo/v0.3.0/NBA/team.csv',header=true, columns={'Id': 'BIGINT', 'FullName': 'VARCHAR', 'Abbreviation': 'VARCHAR', 'Nickname': 'VARCHAR', 'City': 'VARCHAR', 'State': 'VARCHAR', 'YearFounded': 'INT'});"
}
[2024-06-06T15:04:00.692] [DEBUG] WrenEngineAdaptor - Endpoint: https://wren-ui.myhost.net/v1/data-source/duckdb/settings/init-sql
[2024-06-06T15:04:51.078] [DEBUG] DataSourceResolver - {
  initSql: "CREATE TABLE game AS FROM read_csv('https://wrenai-public.s3.amazonaws.com/demo/v0.3.0/NBA/game.csv',header=true, columns={'SeasonId': 'BIGINT', 'TeamIdHome': 'BIGINT', 'Id': 'BIGINT', 'GameDate': 'DATE', 'WlHome': 'VARCHAR', 'Min': 'BIGINT', 'FgmHome': 'BIGINT', 'FgaHome': 'BIGINT', 'FgPct_home': 'DOUBLE', 'threepHome': 'BIGINT', 'threepaHome': 'BIGINT', 'fg3_pct_home': 'DOUBLE', 'FtmHome': 'BIGINT', 'FtaHome': 'BIGINT', 'ft_pct_home': 'DOUBLE', 'OrebHome': 'BIGINT', 'DrebHome': 'BIGINT', 'RebHome': 'BIGINT', 'AstHome': 'BIGINT', 'StlHome': 'BIGINT', 'BlkHome': 'BIGINT', 'TovHome': 'BIGINT', 'PfHome': 'BIGINT', 'PtsHome': 'BIGINT', 'PlusMinusHome': 'BIGINT', 'TeamIdAway': 'BIGINT', 'WlAway': 'VARCHAR', 'FgmAway': 'BIGINT', 'FgaAway': 'BIGINT', 'fg_pct_away': 'DOUBLE', 'threepAway': 'BIGINT', 'threepaAway': 'BIGINT', 'Fg3_pct_away': 'DOUBLE', 'FtmAway': 'BIGINT', 'FtaAway': 'BIGINT', 'Ft_pct_away': 'DOUBLE', 'OrebAway': 'BIGINT', 'DrebAway': 'BIGINT', 'RebAway': 'BIGINT', 'AstAway': 'BIGINT', 'StlAway': 'BIGINT', 'BlkAway': 'BIGINT', 'TovAway': 'BIGINT', 'PfAway': 'BIGINT', 'PtsAway': 'BIGINT', 'PlusMinusAway': 'BIGINT', 'SeasonType': 'VARCHAR'});\n" +
    "CREATE TABLE line_score AS FROM read_csv('https://wrenai-public.s3.amazonaws.com/demo/v0.3.0/NBA/line_score.csv',header=true, columns={'GameDate': 'DATE', 'GameSequence': 'BIGINT', 'GameId': 'BIGINT', 'TeamIdHome': 'BIGINT', 'TeamWinsLossesHome': 'VARCHAR', 'PtsQtr1Home': 'BIGINT', 'PtsQtr2Home': 'BIGINT', 'PtsQtr3Home': 'BIGINT', 'PtsQtr4Home': 'BIGINT', 'PtsOt1Home': 'BIGINT', 'PtsHome': 'BIGINT', 'TeamIdAway': 'BIGINT', 'TeamWinsLossesAway': 'VARCHAR', 'PtsQtr1Away': 'BIGINT', 'PtsQtr2Away': 'BIGINT', 'PtsQtr3Away': 'BIGINT', 'PtsQtr4Away': 'BIGINT', 'PtsOt1Away': 'BIGINT', 'PtsAway': 'BIGINT'});\n" +
    "CREATE TABLE player_games AS FROM read_csv('https://wrenai-public.s3.amazonaws.com/demo/v0.3.0/NBA/player_game.csv',header=true, columns={'Id': 'BIGINT', 'PlayerID': 'BIGINT', 'GameID': 'BIGINT', 'Date': 'DATE', 'Age': 'VARCHAR', 'Tm': 'VARCHAR', 'Opp': 'VARCHAR', 'MP': 'VARCHAR', 'FG': 'BIGINT', 'FGA': 'BIGINT', 'threeP': 'BIGINT', 'threePA': 'BIGINT', 'FT': 'BIGINT', 'FTA': 'BIGINT', 'ORB': 'BIGINT', 'DRB': 'BIGINT', 'TRB': 'BIGINT', 'AST': 'BIGINT', 'STL': 'BIGINT', 'BLK': 'BIGINT', 'TOV': 'BIGINT', 'PF': 'BIGINT', 'PTS': 'BIGINT'});\n" +
    "CREATE TABLE player AS FROM read_csv('https://wrenai-public.s3.amazonaws.com/demo/v0.3.0/NBA/player.csv',header=true, columns={'Id': 'BIGINT', 'TeamId': 'BIGINT', 'FullName': 'VARCHAR', 'FirstName': 'VARCHAR', 'LastName': 'VARCHAR'});\n" +
    "CREATE TABLE team AS FROM read_csv('https://wrenai-public.s3.amazonaws.com/demo/v0.3.0/NBA/team.csv',header=true, columns={'Id': 'BIGINT', 'FullName': 'VARCHAR', 'Abbreviation': 'VARCHAR', 'Nickname': 'VARCHAR', 'City': 'VARCHAR', 'State': 'VARCHAR', 'YearFounded': 'INT'});"
}
[2024-06-06T15:04:51.080] [DEBUG] WrenEngineAdaptor - Endpoint: https://wren-ui.myhost.net/v1/data-source/duckdb/settings/init-sql
[2024-06-06T15:06:10.813] [DEBUG] WrenEngineAdaptor - Got error when init database: Error: connect ETIMEDOUT 88.119.55.13:443
[2024-06-06T15:06:10.815] [ERROR] APOLLO - == original error ==
[2024-06-06T15:06:10.816] [ERROR] APOLLO - Error: connect ETIMEDOUT 88.119.55.13:443
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1278:16)
[2024-06-06T15:07:02.012] [DEBUG] WrenEngineAdaptor - Got error when init database: Error: connect ETIMEDOUT 88.119.55.13:443
[2024-06-06T15:07:02.013] [ERROR] APOLLO - == original error ==
[2024-06-06T15:07:02.014] [ERROR] APOLLO - Error: connect ETIMEDOUT 88.119.55.13:443
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1278:16)

Is it possible that UI still have a dependency on having a persistent volume or volume that shared with the engine (that's the case for docker-compose but is not for k8s)? @wwwy3y3

[onlyjackfrost]

Hi @qdrddr, nice to see you again =D

The log here indicates that you can connect to your Postgres server and run migration successfully.

Using Postgres
Batch 1 run: 17 migrations

The error logs below show that wren-ui is trying to connect to wren-engine with the endpoint https://wren-ui.myhost.net/v1/data-source/duckdb/settings/init-sql. I guess the Wren Engine endpoint might be like http://{wren-engine-service-name}:{port}/v1/..... since you are running in k8s

[2024-06-06T15:04:51.080] [DEBUG] WrenEngineAdaptor - Endpoint: https://wren-ui.myhost.net/v1/data-source/duckdb/settings/init-sql
[2024-06-06T15:06:10.813] [DEBUG] WrenEngineAdaptor - Got error when init database: Error: connect ETIMEDOUT 86.109.5.12:443
[2024-06-06T15:06:10.815] [ERROR] APOLLO - == original error ==
[2024-06-06T15:06:10.816] [ERROR] APOLLO - Error: connect ETIMEDOUT 86.109.5.12:443
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1278:16)

[qdrddr]

@wwwy3y3 but what about the SQLite then? Why it’s also not working then?

[wwwy3y3]

@qdrddr I don't think UI still have a dependency on volume if choosing postgres as backend. I'm actually testing deployment on GKE. Let me come back to this issue later.

[qdrddr]

Hi @qdrddr, nice to see you again =D

The log here indicates that you can connect to your Postgres server and run migration successfully.

Using Postgres
Batch 1 run: 17 migrations

The error logs below show that wren-ui is trying to connect to wren-engine with the endpoint https://wren-ui.myhost.net/v1/data-source/duckdb/settings/init-sql. I guess the Wren Engine endpoint might be like http://{wren-engine-service-name}:{port}/v1/..... since you are running in k8s

[2024-06-06T15:04:51.080] [DEBUG] WrenEngineAdaptor - Endpoint: https://wren-ui.myhost.net/v1/data-source/duckdb/settings/init-sql
[2024-06-06T15:06:10.813] [DEBUG] WrenEngineAdaptor - Got error when init database: Error: connect ETIMEDOUT 88.119.55.13:443
[2024-06-06T15:06:10.815] [ERROR] APOLLO - == original error ==
[2024-06-06T15:06:10.816] [ERROR] APOLLO - Error: connect ETIMEDOUT 88.119.55.13:443
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1278:16)

Does this mean that the WREN_ENGINE_ENDPOINT variable is expected to be not with the external URL publicly available to access UI, but instead should be populated with internal DNS name? @onlyjackfrost

I guess then the WREN_ENGINE_ENDPOINT supposed to be set to http://wren-engine-svc:8080 in the k8s. Opened PR https://github.com/Canner/WrenAI/pull/388

https://github.com/Canner/WrenAI/blob/main/deployment/kustomizations/base/cm.yaml#L26

[qdrddr]

Th issue is resolved. Problem was with WREN_ENGINE_ENDPOINT supposed to be set to http://wren-engine-svc:8080 in the k8s.

[qdrddr]

Th issue is resolved.