cockroachdb / cockroachdb/cockroach
SHOW TABLES does not properly display the zone configs for a table that is created on a database with SECONDARY REGIONS
- 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
Assessment
This issue has not been assessed yet.