cockroachdb / cockroachdb/cockroach

SHOW TABLES does not properly display the zone configs for a table that is created on a database with SECONDARY REGIONS

Open
#117,281 3 comments 0 reactions 0 assignees View on GitHub
A-multiregion C-bug E-quick-win E-starter T-sql-foundations
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Reproduction steps. Start a demo cluster:

```
cockroach demo \
--insecure \
--no-example-database \
--nodes 6 \
--demo-locality=region=us-east1,az=a:region=us-central1,az=b:region=us-west1,az=c:region=eu-west1,az=a:region=eu-central1,az=b:region=eu-north1,az=c --multitenant=false
```

Create a database with secondary region:
```
CREATE DATABASE bank PRIMARY REGION "us-east1" SECONDARY REGION "us-west1";
```

Create a table in that database

```
USE bank;
CREATE TABLE login (a INT) LOCALITY REGIONAL;
```

Observe the output for SHOW CREATE login;
```
root@127.0.0.1:26257/bank> SHOW CREATE login;
table_name | create_statement
-------------+--------------------------------------------------------------
login | CREATE TABLE public.login (
| a INT8 NULL,
| rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
| CONSTRAINT login_pkey PRIMARY KEY (rowid ASC)
| ) LOCALITY REGIONAL BY TABLE IN PRIMARY REGION;
| ALTER TABLE bank.public.login CONFIGURE ZONE USING
|
(1 row)
```

It has an incomplete `ALTER TABLE bank.public.login CONFIGURE ZONE USING` at the end. This is likely pointing to the face that there is a zone config that sets the lease preference which has the secondary region but the output seems truncated.

If you create the database WITHOUT the secondary region, this ALTER is not present, further confirming that secondary region to be the issue.

```
CREATE DATABASE hol PRIMARY REGION "us-east1";
USE hol;
CREATE TABLE login (a INT) LOCALITY REGIONAL;
```

And then we get
```
root@127.0.0.1:26257/hol> SHOW CREATE login;
table_name | create_statement
-------------+--------------------------------------------------------------
login | CREATE TABLE public.login (
| a INT8 NULL,
| rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
| CONSTRAINT login_pkey PRIMARY KEY (rowid ASC)
| ) LOCALITY REGIONAL BY TABLE IN PRIMARY REGION
(1 row)

Time: 29ms total (execution 29ms / network 0ms)
```

We should either remove the ALTER or make it say that the table itself also has a secondary region since it inherits that from the database.

Jira issue: CRDB-35103

Epic CRDB-45396

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.