Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[YSQL][backup] Backup-restore failed due to unexpected NULLABLE column attribute. #6886

Closed
OlegLoginov opened this issue Jan 15, 2021 · 1 comment
Assignees
Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug

Comments

@OlegLoginov
Copy link
Contributor

YB implementation for SQL statements CREATE TABLE and ALTER TABLE ADD can set internal NULLABLE attribute differently. As result the column attributes can be different if it was created via CREATE TABLE and via ALTER TABLE ADD.

Reproducible scenario:

  1. CREATE TABLE test_tbl(id int);
  2. ALTER TABLE test_tbl ADD a int NOT NULL;
  3. Create backup for the DB.
  4. Restore the backup into a new DB. Error:
Error: Internal error (yb/master/catalog_manager_ent.cc:1157): Unable to import snapshot meta file /tmp/yb_backup_mlplorihrpnhczll/SnapshotInfoPB: Invalid created table schema={Schema [
	10:ybrowid[binary NOT NULL PARTITION KEY],
	11:id[int32 NULLABLE NOT A PARTITION KEY],
	12:a[int32 NULLABLE NOT A PARTITION KEY],
]
properties: contain_counters: false is_transactional: true consistency_level: STRONG use_mangled_column_name: false num_tablets: 6 is_ysql_catalog_table: false retain_delete_markers: false}, expected={Schema [
	10:ybrowid[binary NOT NULL PARTITION KEY],
	11:id[int32 NULLABLE NOT A PARTITION KEY],
	12:a[int32 NOT NULL NOT A PARTITION KEY],
]
properties: contain_counters: false is_transactional: true consistency_level: STRONG use_mangled_column_name: false num_tablets: 6 is_ysql_catalog_table: false retain_delete_markers: false}: SNAPSHOT_FAILED (master error 23)

In the example ALTER TABLE ADD created a column with internal attribute 'NOT NULL. But on the backup-restore step the same column is created via CREATE TABLE with attribute NULLABLE`.

@OlegLoginov OlegLoginov added kind/bug This issue is a bug area/ysql Yugabyte SQL (YSQL) labels Jan 15, 2021
@OlegLoginov OlegLoginov self-assigned this Jan 15, 2021
@OlegLoginov
Copy link
Contributor Author

Fix:

  1. ALTER TABLE ADD and CREATE TABLE statements must add columns using the same logic for the column attributes.
  2. For backward compatibility backup-restore should ignore the 'nullable' column attribute to be able restoring from backups created by older releases.

OlegLoginov added a commit that referenced this issue Jan 15, 2021
…NULLABLE column attribute value.

Summary:
YB implementation for SQL statements CREATE TABLE and ALTER TABLE ADD can set internal attribute 'nullable' differently. As result the column attributes can be different if it was created via CREATE TABLE and via ALTER TABLE ADD. It can be a reason of backup-restore failure.

Changes:
1. PgAlterTable::AddColumn() should not set the 'nullable' column attribute as PgCreateTable::AddColumn() does not do it.
2. For backward compatibility backup-restore should ignore the 'nullable' column attribute to be able restoring from backups created by older releases.
3. Schema::Equals() now has a new template argument 'ColumnComparator' for flexibility in the schema comparison.

Test Plan:
ybd --java-test org.yb.pgsql.TestYbBackup#testAlteredYSQLTableBackupWithNotNull
Jenkins: hot

Reviewers: mihnea

Reviewed By: mihnea

Subscribers: bogdan, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D10365
OlegLoginov added a commit that referenced this issue Jan 15, 2021
…to unexpected NULLABLE column attribute value.

Summary:
YB implementation for SQL statements CREATE TABLE and ALTER TABLE ADD can set internal attribute 'nullable' differently. As result the column attributes can be different if it was created via CREATE TABLE and via ALTER TABLE ADD. It can be a reason of backup-restore failure.

Changes:
1. PgAlterTable::AddColumn() should not set the 'nullable' column attribute as PgCreateTable::AddColumn() does not do it.
2. For backward compatibility backup-restore should ignore the 'nullable' column attribute to be able restoring from backups created by older releases.
3. Schema::Equals() now has a new template argument 'ColumnComparator' for flexibility in the schema comparison.

Original diff: D10365 / d4b6ece

Test Plan:
ybd --java-test org.yb.pgsql.TestYbBackup#testAlteredYSQLTableBackupWithNotNull
Jenkins: rebase: 2.4, hot

Reviewers: mihnea

Reviewed By: mihnea

Subscribers: yql, bogdan

Differential Revision: https://phabricator.dev.yugabyte.com/D10368
polarweasel pushed a commit to lizayugabyte/yugabyte-db that referenced this issue Mar 9, 2021
…xpected NULLABLE column attribute value.

Summary:
YB implementation for SQL statements CREATE TABLE and ALTER TABLE ADD can set internal attribute 'nullable' differently. As result the column attributes can be different if it was created via CREATE TABLE and via ALTER TABLE ADD. It can be a reason of backup-restore failure.

Changes:
1. PgAlterTable::AddColumn() should not set the 'nullable' column attribute as PgCreateTable::AddColumn() does not do it.
2. For backward compatibility backup-restore should ignore the 'nullable' column attribute to be able restoring from backups created by older releases.
3. Schema::Equals() now has a new template argument 'ColumnComparator' for flexibility in the schema comparison.

Test Plan:
ybd --java-test org.yb.pgsql.TestYbBackup#testAlteredYSQLTableBackupWithNotNull
Jenkins: hot

Reviewers: mihnea

Reviewed By: mihnea

Subscribers: bogdan, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D10365
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug
Projects
None yet
Development

No branches or pull requests

1 participant