cube-js/cube

Refresh key every doesn't work for oracle db

Open

#1,591 opened on Dec 13, 2020

View on GitHub
 (3 comments) (0 reactions) (0 assignees)Rust (19,563 stars) (1,965 forks)batch import
driver:oraclehelp wanted

Description

Describe the bug Im using Cube.js in windows env with oracle db. the connection is up.

To Reproduce

After launching the cube server, (npm run dev) i got

Error querying db: scheduler-c97ed59a-7aed-4951-a832-1cade1474c4c
--
"SELECT 1"
--
{}
Error: ORA-00923: mot-clé FROM absent à l'emplacement prévu ....

Expected behavior

After searching i change the refresh script but i got the same error later with

SELECT FLOOR((((cast (systimestamp at time zone 'UTC' as date) - date '1970-01-01') * 86400)) / 10)

Screenshots If applicable, add screenshots to help explain your problem.

Version: [e.g. 0.4.5]

Additional context

Workaround: I change the script RefreshScheduler.js

      await orchestratorApi.executeQuery({
        ...sqlQuery,
        preAggregations: [],
        query: 'SELECT 1', // TODO get rid off it
        continueWait: true,
        renewQuery: true,
        requestId: context.requestId
      });
    }));

Contributor guide

Tech stack
javascriptnodejssql
Domain
backenddatabase
Issue type
bug
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
3
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
1-3 hours
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
stale
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
clear
Prerequisites
JavaScriptNode.jsOracle SQL dialectCube.js refresh scheduler
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
45
Research direction
Investigate the SQL queries generated in RefreshScheduler.js (or related files) for the refresh key. The issue indicates that 'SELECT 1' and the timestamp function are not compatible with Oracle. Research Oracle's SQL syntax to replace them with equivalents like 'SELECT 1 FROM DUAL' and appropriate date functions. Check for any existing Oracle specific logic in the codebase. Provide a fix that handles Oracle as a special case without breaking other databases.