cube-js/cube

Pre-aggregation queries are not GTID safe - MySQL

Open

#1,810 opened on Jan 19, 2021

View on GitHub
 (5 comments) (0 reactions) (0 assignees)Rust (1,965 forks)batch import
driver:mysqlhelp wanted

Repository metrics

Stars
 (19,563 stars)
PR merge metrics
 (Avg merge 5d 16h) (138 merged PRs in 30d)

Description

Describe the bug

Queries to create pre-agg tables use CREATE TABLE ... SELECT syntax. This syntax is not valid when running MySQL with GTID based replication. see here

GTID based replication also happens to be used by Google CloudSQL and is not configurable. This means that pre-aggregations cannot by used.

Note: This issue theoretically will also appear when using statement based replication.

To Reproduce Run a MySQL database using GTIDs for replication

Add a cube with a pre-aggregation. Will fail with an error like "Error: ER_GTID_UNSAFE_CREATE_SELECT: Statement violates GTID consistency: CREATE TABLE ... SELECT."

Expected behavior Pre-aggregation queries should be safe for statement based replication as well as replication with GTIDs.

Contributor guide

Research direction
Investigate changing the pre aggregation query from 'CREATE TABLE ... SELECT' to a two step process: first create the table, then use 'INSERT ... SELECT'. Ensure the change works with GTID and statement based replication without breaking existing functionality.
Tech stack
rustsql
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-2 days
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
Active
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
Clear
Prerequisites
SQLMySQLreplication
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
30