github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/ccl/backupccl/show_test.go (about)

     1  // Copyright 2018 The Cockroach Authors.
     2  //
     3  // Licensed as a CockroachDB Enterprise file under the Cockroach Community
     4  // License (the "License"); you may not use this file except in compliance with
     5  // the License. You may obtain a copy of the License at
     6  //
     7  //     https://github.com/cockroachdb/cockroach/blob/master/licenses/CCL.txt
     8  
     9  package backupccl_test
    10  
    11  import (
    12  	"fmt"
    13  	"regexp"
    14  	"strconv"
    15  	"strings"
    16  	"testing"
    17  
    18  	"github.com/cockroachdb/cockroach/pkg/keys"
    19  	"github.com/cockroachdb/cockroach/pkg/roachpb"
    20  	"github.com/cockroachdb/cockroach/pkg/sql/sqlbase"
    21  	"github.com/cockroachdb/cockroach/pkg/util/leaktest"
    22  	"github.com/stretchr/testify/require"
    23  )
    24  
    25  func TestShowBackup(t *testing.T) {
    26  	defer leaktest.AfterTest(t)()
    27  
    28  	const numAccounts = 11
    29  	_, tc, sqlDB, tempDir, cleanupFn := backupRestoreTestSetup(t, singleNode, numAccounts, initNone)
    30  	kvDB := tc.Server(0).DB()
    31  	_, _, sqlDBRestore, cleanupEmptyCluster := backupRestoreTestSetupEmpty(t, singleNode, tempDir, initNone)
    32  	defer cleanupFn()
    33  	defer cleanupEmptyCluster()
    34  
    35  	const full, inc, inc2 = localFoo + "/full", localFoo + "/inc", localFoo + "/inc2"
    36  
    37  	beforeTS := sqlDB.QueryStr(t, `SELECT now()::string`)[0][0]
    38  	sqlDB.Exec(t, fmt.Sprintf(`BACKUP DATABASE data TO $1 AS OF SYSTEM TIME '%s'`, beforeTS), full)
    39  
    40  	res := sqlDB.QueryStr(t, `SELECT table_name, start_time::string, end_time::string, rows, is_full_cluster FROM [SHOW BACKUP $1]`, full)
    41  	require.Equal(t, [][]string{{"bank", "NULL", beforeTS, strconv.Itoa(numAccounts), "false"}}, res)
    42  
    43  	// Mess with half the rows.
    44  	affectedRows, err := sqlDB.Exec(t,
    45  		`UPDATE data.bank SET id = -1 * id WHERE id > $1`, numAccounts/2,
    46  	).RowsAffected()
    47  	require.NoError(t, err)
    48  	require.Equal(t, numAccounts/2, int(affectedRows))
    49  
    50  	// Backup the changes by appending to the base and by making a separate
    51  	// inc backup.
    52  	incTS := sqlDB.QueryStr(t, `SELECT now()::string`)[0][0]
    53  	sqlDB.Exec(t, fmt.Sprintf(`BACKUP DATABASE data TO $1 AS OF SYSTEM TIME '%s'`, incTS), full)
    54  	sqlDB.Exec(t, fmt.Sprintf(`BACKUP DATABASE data TO $1 AS OF SYSTEM TIME '%s' INCREMENTAL FROM $2`, incTS), inc, full)
    55  
    56  	// Check the appended base backup.
    57  	res = sqlDB.QueryStr(t, `SELECT table_name, start_time::string, end_time::string, rows, is_full_cluster FROM [SHOW BACKUP $1]`, full)
    58  	require.Equal(t, [][]string{
    59  		{"bank", "NULL", beforeTS, strconv.Itoa(numAccounts), "false"},
    60  		{"bank", beforeTS, incTS, strconv.Itoa(int(affectedRows * 2)), "false"},
    61  	}, res)
    62  
    63  	// Check the separate inc backup.
    64  	res = sqlDB.QueryStr(t, `SELECT start_time::string, end_time::string, rows FROM [SHOW BACKUP $1]`, inc)
    65  	require.Equal(t, [][]string{
    66  		{beforeTS, incTS, strconv.Itoa(int(affectedRows * 2))},
    67  	}, res)
    68  
    69  	// Create two new tables, alphabetically on either side of bank.
    70  	sqlDB.Exec(t, `CREATE TABLE data.auth (id INT PRIMARY KEY, name STRING)`)
    71  	sqlDB.Exec(t, `CREATE TABLE data.users (id INT PRIMARY KEY, name STRING)`)
    72  	sqlDB.Exec(t, `INSERT INTO data.users VALUES (1, 'one'), (2, 'two'), (3, 'three')`)
    73  
    74  	// Backup the changes again, by appending to the base and by making a
    75  	// separate inc backup.
    76  	inc2TS := sqlDB.QueryStr(t, `SELECT now()::string`)[0][0]
    77  	sqlDB.Exec(t, fmt.Sprintf(`BACKUP DATABASE data TO $1 AS OF SYSTEM TIME '%s'`, inc2TS), full)
    78  	sqlDB.Exec(t, fmt.Sprintf(`BACKUP DATABASE data TO $1 AS OF SYSTEM TIME '%s' INCREMENTAL FROM $2, $3`, inc2TS), inc2, full, inc)
    79  
    80  	// Check the appended base backup.
    81  	res = sqlDB.QueryStr(t, `SELECT table_name, start_time::string, end_time::string, rows FROM [SHOW BACKUP $1]`, full)
    82  	require.Equal(t, [][]string{
    83  		{"bank", "NULL", beforeTS, strconv.Itoa(numAccounts)},
    84  		{"bank", beforeTS, incTS, strconv.Itoa(int(affectedRows * 2))},
    85  		{"bank", incTS, inc2TS, "0"},
    86  		{"auth", incTS, inc2TS, "0"},
    87  		{"users", incTS, inc2TS, "3"},
    88  	}, res)
    89  
    90  	// Check the separate inc backup.
    91  	res = sqlDB.QueryStr(t, `SELECT table_name, start_time::string, end_time::string, rows FROM [SHOW BACKUP $1]`, inc2)
    92  	require.Equal(t, [][]string{
    93  		{"bank", incTS, inc2TS, "0"},
    94  		{"auth", incTS, inc2TS, "0"},
    95  		{"users", incTS, inc2TS, "3"},
    96  	}, res)
    97  
    98  	const details = localFoo + "/details"
    99  	sqlDB.Exec(t, `CREATE TABLE data.details1 (c INT PRIMARY KEY)`)
   100  	sqlDB.Exec(t, `INSERT INTO data.details1 (SELECT generate_series(1, 100))`)
   101  	sqlDB.Exec(t, `ALTER TABLE data.details1 SPLIT AT VALUES (1), (42)`)
   102  	sqlDB.Exec(t, `CREATE TABLE data.details2()`)
   103  	sqlDB.Exec(t, `BACKUP data.details1, data.details2 TO $1;`, details)
   104  
   105  	details1Desc := sqlbase.GetTableDescriptor(tc.Server(0).DB(), keys.SystemSQLCodec, "data", "details1")
   106  	details2Desc := sqlbase.GetTableDescriptor(tc.Server(0).DB(), keys.SystemSQLCodec, "data", "details2")
   107  	details1Key := roachpb.Key(sqlbase.MakeIndexKeyPrefix(keys.SystemSQLCodec, details1Desc, details1Desc.PrimaryIndex.ID))
   108  	details2Key := roachpb.Key(sqlbase.MakeIndexKeyPrefix(keys.SystemSQLCodec, details2Desc, details2Desc.PrimaryIndex.ID))
   109  
   110  	sqlDBRestore.CheckQueryResults(t, fmt.Sprintf(`SHOW BACKUP RANGES '%s'`, details), [][]string{
   111  		{"/Table/56/1", "/Table/56/2", string(details1Key), string(details1Key.PrefixEnd())},
   112  		{"/Table/57/1", "/Table/57/2", string(details2Key), string(details2Key.PrefixEnd())},
   113  	})
   114  
   115  	var showFiles = fmt.Sprintf(`SELECT start_pretty, end_pretty, size_bytes, rows
   116  		FROM [SHOW BACKUP FILES '%s']`, details)
   117  	sqlDBRestore.CheckQueryResults(t, showFiles, [][]string{
   118  		{"/Table/56/1/1", "/Table/56/1/42", "369", "41"},
   119  		{"/Table/56/1/42", "/Table/56/2", "531", "59"},
   120  	})
   121  	sstMatcher := regexp.MustCompile(`\d+\.sst`)
   122  	pathRows := sqlDB.QueryStr(t, `SELECT path FROM [SHOW BACKUP FILES $1]`, details)
   123  	for _, row := range pathRows {
   124  		path := row[0]
   125  		if matched := sstMatcher.MatchString(path); !matched {
   126  			t.Errorf("malformatted path in SHOW BACKUP FILES: %s", path)
   127  		}
   128  	}
   129  	if len(pathRows) != 2 {
   130  		t.Fatalf("expected 2 files, but got %d", len(pathRows))
   131  	}
   132  
   133  	// SCHEMAS: Test the creation statement.
   134  	var showBackupRows [][]string
   135  	var expected []string
   136  
   137  	// Test that tables, views and sequences are all supported.
   138  	{
   139  		viewTableSeq := localFoo + "/tableviewseq"
   140  		sqlDB.Exec(t, `CREATE TABLE data.tableA (a int primary key, b int, INDEX tableA_b_idx (b ASC))`)
   141  		sqlDB.Exec(t, `CREATE VIEW data.viewA AS SELECT a from data.tableA`)
   142  		sqlDB.Exec(t, `CREATE SEQUENCE data.seqA START 1 INCREMENT 2 MAXVALUE 20`)
   143  		sqlDB.Exec(t, `BACKUP data.tableA, data.viewA, data.seqA TO $1;`, viewTableSeq)
   144  
   145  		// Create tables with the same ID as data.tableA to ensure that comments
   146  		// from different tables in the restoring cluster don't appear.
   147  		tableA := sqlbase.GetTableDescriptor(kvDB, keys.SystemSQLCodec, "data", "tablea")
   148  		for i := keys.MinUserDescID; i < int(tableA.ID); i++ {
   149  			tableName := fmt.Sprintf("foo%d", i)
   150  			sqlDBRestore.Exec(t, fmt.Sprintf("CREATE TABLE %s ();", tableName))
   151  			sqlDBRestore.Exec(t, fmt.Sprintf("COMMENT ON TABLE %s IS 'table comment'", tableName))
   152  		}
   153  
   154  		expectedCreateTable := `CREATE TABLE tablea (
   155  		a INT8 NOT NULL,
   156  		b INT8 NULL,
   157  		CONSTRAINT "primary" PRIMARY KEY (a ASC),
   158  		INDEX tablea_b_idx (b ASC),
   159  		FAMILY "primary" (a, b)
   160  	)`
   161  		expectedCreateView := `CREATE VIEW viewa (a) AS SELECT a FROM data.public.tablea`
   162  		expectedCreateSeq := `CREATE SEQUENCE seqa MINVALUE 1 MAXVALUE 20 INCREMENT 2 START 1`
   163  
   164  		showBackupRows = sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP SCHEMAS '%s'`, viewTableSeq))
   165  		expected = []string{
   166  			expectedCreateTable,
   167  			expectedCreateView,
   168  			expectedCreateSeq,
   169  		}
   170  		for i, row := range showBackupRows {
   171  			createStmt := row[7]
   172  			if !eqWhitespace(createStmt, expected[i]) {
   173  				t.Fatalf("mismatched create statement: %s, want %s", createStmt, expected[i])
   174  			}
   175  		}
   176  	}
   177  
   178  	// Test that foreign keys that reference tables that are in the backup
   179  	// are included.
   180  	{
   181  		includedFK := localFoo + "/includedFK"
   182  		sqlDB.Exec(t, `CREATE TABLE data.FKSrc (a INT PRIMARY KEY)`)
   183  		sqlDB.Exec(t, `CREATE TABLE data.FKRefTable (a INT PRIMARY KEY, B INT REFERENCES data.FKSrc(a))`)
   184  		sqlDB.Exec(t, `CREATE DATABASE data2`)
   185  		sqlDB.Exec(t, `CREATE TABLE data2.FKRefTable (a INT PRIMARY KEY, B INT REFERENCES data.FKSrc(a))`)
   186  		sqlDB.Exec(t, `BACKUP data.FKSrc, data.FKRefTable, data2.FKRefTable TO $1;`, includedFK)
   187  
   188  		wantSameDB := `CREATE TABLE fkreftable (
   189  				a INT8 NOT NULL,
   190  				b INT8 NULL,
   191  				CONSTRAINT "primary" PRIMARY KEY (a ASC),
   192  				CONSTRAINT fk_b_ref_fksrc FOREIGN KEY (b) REFERENCES fksrc(a),
   193  				INDEX fkreftable_auto_index_fk_b_ref_fksrc (b ASC),
   194  				FAMILY "primary" (a, b)
   195  			)`
   196  		wantDiffDB := `CREATE TABLE fkreftable (
   197  				a INT8 NOT NULL,
   198  				b INT8 NULL,
   199  				CONSTRAINT "primary" PRIMARY KEY (a ASC),
   200  				CONSTRAINT fk_b_ref_fksrc FOREIGN KEY (b) REFERENCES data.public.fksrc(a),
   201  				INDEX fkreftable_auto_index_fk_b_ref_fksrc (b ASC),
   202  				FAMILY "primary" (a, b)
   203  			)`
   204  
   205  		showBackupRows = sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP SCHEMAS '%s'`, includedFK))
   206  		createStmtSameDB := showBackupRows[1][7]
   207  		if !eqWhitespace(createStmtSameDB, wantSameDB) {
   208  			t.Fatalf("mismatched create statement: %s, want %s", createStmtSameDB, wantSameDB)
   209  		}
   210  
   211  		createStmtDiffDB := showBackupRows[2][7]
   212  		if !eqWhitespace(createStmtDiffDB, wantDiffDB) {
   213  			t.Fatalf("mismatched create statement: %s, want %s", createStmtDiffDB, wantDiffDB)
   214  		}
   215  	}
   216  
   217  	// Foreign keys that were not included in the backup are not mentioned in
   218  	// the create statement.
   219  	{
   220  		missingFK := localFoo + "/missingFK"
   221  		sqlDB.Exec(t, `BACKUP data2.FKRefTable TO $1;`, missingFK)
   222  
   223  		want := `CREATE TABLE fkreftable (
   224  				a INT8 NOT NULL,
   225  				b INT8 NULL,
   226  				CONSTRAINT "primary" PRIMARY KEY (a ASC),
   227  				INDEX fkreftable_auto_index_fk_b_ref_fksrc (b ASC),
   228  				FAMILY "primary" (a, b)
   229  			)`
   230  
   231  		showBackupRows = sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP SCHEMAS '%s'`, missingFK))
   232  		createStmt := showBackupRows[0][7]
   233  		if !eqWhitespace(createStmt, want) {
   234  			t.Fatalf("mismatched create statement: %s, want %s", createStmt, want)
   235  		}
   236  	}
   237  
   238  	{
   239  		full_cluster := localFoo + "/full_cluster"
   240  		sqlDB.Exec(t, `BACKUP TO $1;`, full_cluster)
   241  
   242  		showBackupRows = sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP '%s'`, full_cluster))
   243  		is_full_cluster := showBackupRows[0][6]
   244  		if !eqWhitespace(is_full_cluster, "true") {
   245  			t.Fatal("expected show backup to indicate that backup was full cluster")
   246  		}
   247  
   248  		full_cluster_inc := localFoo + "/full_cluster_inc"
   249  		sqlDB.Exec(t, `BACKUP TO $1 INCREMENTAL FROM $2;`, full_cluster_inc, full_cluster)
   250  
   251  		showBackupRows = sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP '%s'`, full_cluster))
   252  		is_full_cluster = showBackupRows[0][6]
   253  		if !eqWhitespace(is_full_cluster, "true") {
   254  			t.Fatal("expected show backup to indicate that backup was full cluster")
   255  		}
   256  	}
   257  
   258  	// Show privileges of descriptors that are backed up.
   259  	{
   260  		showPrivs := localFoo + "/show_privs"
   261  		sqlDB.Exec(t, `CREATE TABLE data.top_secret (id INT PRIMARY KEY, name STRING)`)
   262  		sqlDB.Exec(t, `CREATE USER agent_bond`)
   263  		sqlDB.Exec(t, `CREATE USER agent_thomas`)
   264  		sqlDB.Exec(t, `CREATE USER m`)
   265  		sqlDB.Exec(t, `CREATE ROLE agents`)
   266  		sqlDB.Exec(t, `GRANT agents TO agent_bond`)
   267  		sqlDB.Exec(t, `GRANT agents TO agent_thomas`)
   268  		sqlDB.Exec(t, `GRANT ALL ON data.top_secret TO m`)
   269  		sqlDB.Exec(t, `GRANT INSERT on data.top_secret TO agents`)
   270  		sqlDB.Exec(t, `GRANT SELECT on data.top_secret TO agent_bond`)
   271  		sqlDB.Exec(t, `GRANT UPDATE on data.top_secret TO agent_bond`)
   272  		sqlDB.Exec(t, `BACKUP data.top_secret TO $1;`, showPrivs)
   273  
   274  		want := `GRANT ALL ON top_secret TO admin; GRANT SELECT, UPDATE ON top_secret TO agent_bond; GRANT INSERT ON top_secret TO agents; GRANT ALL ON top_secret TO m; GRANT ALL ON top_secret TO root; `
   275  
   276  		showBackupRows := sqlDBRestore.QueryStr(t, fmt.Sprintf(`SHOW BACKUP '%s' WITH privileges`, showPrivs))
   277  		privs := showBackupRows[0][7]
   278  		if !eqWhitespace(privs, want) {
   279  			t.Fatalf("mismatched privileges: %s, want %s", privs, want)
   280  		}
   281  	}
   282  }
   283  
   284  func eqWhitespace(a, b string) bool {
   285  	return strings.Replace(a, "\t", "", -1) == strings.Replace(b, "\t", "", -1)
   286  }