[Bug]: RESTORE ACCOUNT fails on legacy CHECK metadata and drops table privileges

[zhangxu19830126]

Agent | grok | grok-4.7

Summary

RESTORE ACCOUNT fails with Error 20301: invalid input: legacy CHECK metadata is not a CREATE TABLE statement on a tenant that has already been restored once. The first in-place restore of a fresh account can succeed. A later restore of that same account fails. RESTORE ACCOUNT ... TO ACCOUNT of a workspace account that already contains CREATE TABLE ... CLONE ... metadata also fails with the same error.

This blocks using account snapshots as a time-travel mechanism: after one successful restore, the account can no longer be restored to another snapshot.

When RESTORE ACCOUNT ... TO ACCOUNT does succeed, table privileges are not preserved. The role still exists, SHOW GRANTS for that role is empty, and a user who held the role can no longer read the table. Losing authorization on restore is a severe failure even when the data rows come back.

Version

Local MatrixOne 8.1.1-149 v4.2.4 (SELECT version()).

What works

Fresh account ws_08acbe57 (a newly created workspace, zero prior restores):

CREATE SNAPSHOT tm_123_self FOR ACCOUNT ws_08acbe57;
RESTORE ACCOUNT ws_08acbe57 {SNAPSHOT = 'tm_123_self'};

This in-place restore succeeded. The tenant database moi still had 527 tables.

CREATE SNAPSHOT tm_123_s1 FOR ACCOUNT ws_08acbe57;
CREATE ACCOUNT tm_123_bak ADMIN_NAME 'root' IDENTIFIED BY 'test-pass';
RESTORE ACCOUNT ws_08acbe57 {SNAPSHOT = 'tm_123_s1'} TO ACCOUNT tm_123_bak;

Copying that fresh account into a new account also succeeded. Both accounts had 527 tables in moi.

What fails

1. In-place restore of an account that was itself produced by RESTORE ... TO ACCOUNT:

CREATE SNAPSHOT tm_123_s2 FOR ACCOUNT tm_123_bak;
RESTORE ACCOUNT tm_123_bak {SNAPSHOT = 'tm_123_s2'};

Result:

ERROR 20301 (HY000): invalid input: legacy CHECK metadata is not a CREATE TABLE statement

2. A second in-place restore of the original account, after the first in-place restore had succeeded:

CREATE SNAPSHOT tm_123_s1 FOR ACCOUNT ws_08acbe57;
RESTORE ACCOUNT ws_08acbe57 {SNAPSHOT = 'tm_123_s1'};

Same Error 20301.

3. Restoring a snapshot onto a different account than the one named in the snapshot:

RESTORE ACCOUNT tm_123_bak {SNAPSHOT = 'tm_123_s1'};

tm_123_s1 was created FOR ACCOUNT ws_08acbe57. Result:

ERROR 20101 (HY000): internal error: accountName(tm_123_bak) does not match snapshot.accountName(ws_08acbe57)

4. On an older workspace account that had already been restored (ws_09e1f518), RESTORE ACCOUNT ... TO ACCOUNT failed immediately with the same Error 20301. CREATE SNAPSHOT ... FOR ACCOUNT itself succeeded.

Metadata involved

For ws_08acbe57, mo_catalog.mo_tables.rel_createsql contains CHECK on:

• information_schema.files, partitions, tables, views
• 9 tables in the tenant database moi

One of those tenant rows is not a normal CREATE TABLE. Its stored SQL starts with:

create table `moi`.`structured_load_runtime_checkpoint` clone `moi`.`structured_load_runtime_checkpoint` ...

pkg/sql/plan/build_ddl.go recoverLegacyChecks parses Createsql when it contains CHECK and the structured constraint list is empty. It returns 20301 unless the statement is a CREATE TABLE. A stored CREATE TABLE ... CLONE ... statement, and the information_schema views, take that path during RESTORE ACCOUNT.

Privileges are dropped

On the same server, a small account was restored back onto itself with RESTORE ACCOUNT backup {SNAPSHOT = ...} TO ACCOUNT live. The live account was not dropped. Account id stayed the same. The table rows returned to the snapshot contents.

Before the write-back the account had:

CREATE ROLE r1;
GRANT SELECT ON TABLE app_db.t TO r1;
CREATE USER u1 IDENTIFIED BY '...';
GRANT r1 TO u1;

After the write-back:

• r1 still existed.
• SHOW GRANTS FOR r1 returned no rows.
• u1 selecting app_db.t failed with ERROR 20101 (HY000): internal error: do not have privilege to execute the statement.

Table id changed across this restore. That id change is expected. The privilege disappearing is not.

Expected

• RESTORE ACCOUNT of a tenant should be repeatable. A successful restore must not leave CHECK or CLONE metadata that makes the next restore fail.
• CREATE TABLE ... CLONE ... stored in rel_createsql should not be parsed as legacy CHECK metadata.
• Account restore should not depend on information_schema view definitions being CREATE TABLE statements.
• Roles, users, and table grants that exist in the snapshot must still authorize the same tables after restore.

Database and table ids changing after CREATE DATABASE ... CLONE or RESTORE ACCOUNT is expected and is not part of this bug.

Repro notes

The failing statements were run as the cluster root user against 127.0.0.1:6001. Temporary snapshots and the temporary account tm_123_bak were dropped after the test. The original workspace account was left in place.