dumpling view failed: sql: SHOW COLUMNS FROM xtest.v1: Error 1356 (HY000): View 'xtest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

[fubinzh]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. There is a table and view in TiDB:

mysql> show create table `xtest`.`v1`;
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                  | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v1` (`id`, `id1`) AS SELECT `xtest`.`t1`.`id` AS `id`,`xtest`.`t1`.`id1` AS `id1` FROM `xtest`.`t1` | utf8                 | utf8_general_ci      |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql> show create table `xtest`.`t1`;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `id1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2. Use dumpling to dump it

[root@dumpling-0 /]# /dumpling  -h "downstream-tidb.brie-acceptance-tps-7609834-1-966" -P "4000" -u "root" --output "/tmp/dumpout1" --filetype "csv" --rows "200" --threads "20" --no-views=false --params 'tidb_enable_chunk_rpc=0'
Release version: v7.5.3
Git commit hash: 6faf15688a69dd32c3edd55dfeabc918b475bfbf
Git branch:      HEAD
Build timestamp: 2024-07-26 03:50:38Z
Go version:      go version go1.21.10 linux/amd64

[2024/07/26 10:01:16.431 +00:00] [INFO] [versions.go:54] ["Welcome to dumpling"] ["Release Version"=v7.5.3] ["Git Commit Hash"=6faf15688a69dd32c3edd55dfeabc918b475bfbf] ["Git Branch"=HEAD] ["Build timestamp"="2024-07-26 03:50:38"] ["Go Version"="go version go1.21.10 linux/amd64"]
[2024/07/26 10:01:16.434 +00:00] [INFO] [version.go:434] ["detect server version"] [type=TiDB] [version=7.5.3]
[2024/07/26 10:01:16.460 +00:00] [INFO] [pd_service_discovery.go:631] ["[pd] switch leader"] [new-leader=http://downstream-pd-0.downstream-pd-peer.brie-acceptance-tps-7609834-1-966.svc:2379] [old-leader=]
[2024/07/26 10:01:16.460 +00:00] [INFO] [pd_service_discovery.go:197] ["[pd] init cluster id"] [cluster-id=7395854179773479925]
[2024/07/26 10:01:16.460 +00:00] [INFO] [client.go:600] ["[pd] changing service mode"] [old-mode=UNKNOWN_SVC_MODE] [new-mode=PD_SVC_MODE]
[2024/07/26 10:01:16.460 +00:00] [INFO] [tso_client.go:231] ["[tso] switch dc tso global allocator serving address"] [dc-location=global] [new-address=http://downstream-pd-0.downstream-pd-peer.brie-acceptance-tps-7609834-1-966.svc:2379]
[2024/07/26 10:01:16.460 +00:00] [INFO] [tso_dispatcher.go:323] ["[tso] tso dispatcher created"] [dc-location=global]
[2024/07/26 10:01:16.460 +00:00] [INFO] [client.go:648] ["[pd] service mode changed"] [old-mode=UNKNOWN_SVC_MODE] [new-mode=PD_SVC_MODE]
[2024/07/26 10:01:16.461 +00:00] [INFO] [dump.go:1522] ["generate dumpling gc safePoint id"] [id=dumpling_1721988076461644463]
[2024/07/26 10:01:16.471 +00:00] [INFO] [dump.go:151] ["begin to run Dump"] [conf="{\"s3\":{\"endpoint\":\"\",\"region\":\"\",\"storage-class\":\"\",\"sse\":\"\",\"sse-kms-key-id\":\"\",\"acl\":\"\",\"access-key\":\"\",\"secret-access-key\":\"\",\"session-token\":\"\",\"provider\":\"\",\"force-path-style\":true,\"use-accelerate-endpoint\":false,\"role-arn\":\"\",\"external-id\":\"\",\"object-lock-enabled\":false},\"gcs\":{\"endpoint\":\"\",\"storage-class\":\"\",\"predefined-acl\":\"\",\"credentials-file\":\"\"},\"azblob\":{\"endpoint\":\"\",\"account-name\":\"\",\"account-key\":\"\",\"access-tier\":\"\",\"sas-token\":\"\",\"encryption-scope\":\"\",\"encryption-key\":\"\"},\"SpecifiedTables\":false,\"AllowCleartextPasswords\":false,\"SortByPk\":true,\"NoViews\":false,\"NoSequences\":true,\"NoHeader\":false,\"NoSchemas\":false,\"NoData\":false,\"CompleteInsert\":false,\"TransactionalConsistency\":true,\"EscapeBackslash\":true,\"DumpEmptyDatabase\":true,\"PosAfterConnect\":false,\"CompressType\":0,\"Host\":\"downstream-tidb.brie-acceptance-tps-7609834-1-966\",\"Port\":4000,\"Threads\":20,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/tmp/dumpout1\",\"StatusAddr\":\":8281\",\"Snapshot\":\"451408842307665925\",\"Consistency\":\"snapshot\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"CsvLineTerminator\":\"\\r\\n\",\"Databases\":[],\"Where\":\"\",\"FileType\":\"csv\",\"ServerInfo\":{\"ServerType\":3,\"ServerVersion\":\"7.5.3\",\"HasTiKV\":true},\"Rows\":200,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":0,\"StatementSize\":1000000,\"SessionParams\":{\"tidb_enable_chunk_rpc\":\"0\",\"tidb_snapshot\":\"451408842307665925\"},\"Tables\":{},\"CollationCompatible\":\"loose\",\"IOTotalBytes\":null,\"Net\":\"\"}"]
[2024/07/26 10:01:16.583 +00:00] [INFO] [writer.go:272] ["no data written in table chunk"] [database=xtest] [table=t1] [chunkIdx=0]
[2024/07/26 10:01:16.587 +00:00] [INFO] [writer.go:272] ["no data written in table chunk"] [database=xtest] [table=t2] [chunkIdx=0]
[2024/07/26 10:01:16.587 +00:00] [INFO] [conn.go:70] ["cannot execute query"] [retryTime=1] [sql="SHOW COLUMNS FROM `xtest`.`v1`"] [args=null] [error="Error 1356 (HY000): View 'xtest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them"]
[2024/07/26 10:01:16.587 +00:00] [INFO] [collector.go:224] ["units canceled"] [cancel-unit=0]
[2024/07/26 10:01:16.587 +00:00] [INFO] [collector.go:225] ["backup failed summary"] [total-ranges=0] [ranges-succeed=0] [ranges-failed=0]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=3]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=5]
[2024/07/26 10:01:16.588 +00:00] [INFO] [tso_dispatcher.go:186] ["exit tso requests cancel loop"]
[2024/07/26 10:01:16.588 +00:00] [INFO] [pd_service_discovery.go:248] ["[pd] exit member loop due to context canceled"]
[2024/07/26 10:01:16.588 +00:00] [INFO] [tso_dispatcher.go:240] ["exit tso dispatcher loop"]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=6]
[2024/07/26 10:01:16.588 +00:00] [INFO] [resource_manager_client.go:271] ["[resource manager] exit resource token dispatcher"]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=19]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=14]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=0]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=10]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=2]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=7]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=12]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=13]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=18]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=9]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=8]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=15]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=16]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=17]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=1]
[2024/07/26 10:01:16.588 +00:00] [INFO] [tso_dispatcher.go:410] ["[tso] stop fetching the pending tso requests due to context canceled"] [dc-location=global]
[2024/07/26 10:01:16.588 +00:00] [INFO] [tso_dispatcher.go:344] ["[tso] exit tso dispatcher"] [dc-location=global]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=4]
[2024/07/26 10:01:16.588 +00:00] [INFO] [writer.go:85] ["context has been done, the writer will exit"] ["writer ID"=11]
[2024/07/26 10:01:16.588 +00:00] [ERROR] [main.go:78] ["dump failed error stack info"] [error="sql: SHOW COLUMNS FROM `xtest`.`v1`: Error 1356 (HY000): View 'xtest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them"] [errorVerbose="Error 1356 (HY000): View 'xtest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them\nsql: SHOW COLUMNS FROM `xtest`.`v1`\ngithub.com/pingcap/tidb/dumpling/export.(*BaseConn).QuerySQLWithColumns.func1\n\tgithub.com/pingcap/tidb/dumpling/export/conn.go:72\ngithub.com/pingcap/tidb/br/pkg/utils.WithRetry.func1\n\tgithub.com/pingcap/tidb/br/pkg/utils/retry.go:217\ngithub.com/pingcap/tidb/br/pkg/utils.WithRetryV2[...]\n\tgithub.com/pingcap/tidb/br/pkg/utils/retry.go:235\ngithub.com/pingcap/tidb/br/pkg/utils.WithRetry\n\tgithub.com/pingcap/tidb/br/pkg/utils/retry.go:216\ngithub.com/pingcap/tidb/dumpling/export.(*BaseConn).QuerySQLWithColumns\n\tgithub.com/pingcap/tidb/dumpling/export/conn.go:59\ngithub.com/pingcap/tidb/dumpling/export.buildSelectField\n\tgithub.com/pingcap/tidb/dumpling/export/sql.go:1045\ngithub.com/pingcap/tidb/dumpling/export.dumpTableMeta\n\tgithub.com/pingcap/tidb/dumpling/export/dump.go:1190\ngithub.com/pingcap/tidb/dumpling/export.(*Dumper).dumpDatabases\n\tgithub.com/pingcap/tidb/dumpling/export/dump.go:443\ngithub.com/pingcap/tidb/dumpling/export.(*Dumper).Dump\n\tgithub.com/pingcap/tidb/dumpling/export/dump.go:315\nmain.main\n\t./main.go:75\nruntime.main\n\truntime/proc.go:267\nruntime.goexit\n\truntime/asm_amd64.s:1650"]

dump failed: sql: SHOW COLUMNS FROM `xtest`.`v1`: Error 1356 (HY000): View 'xtest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

2. What did you expect to see? (Required)

dumpling success

3. What did you see instead (Required)

dumple failed. "dump failed: sql: SHOW COLUMNS FROM xtest.v1: Error 1356 (HY000): View 'xtest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them"

TiDB Log:

{"level":"ERROR","log":"[logical_plan_builder.go:5787] [\"build plan for view failed\"] [error=\"InfoSchema is not SessionExtendedInfoSchema\"]","container":"tidb","pod":"downstream-tidb-0","namespace":"brie-acceptance-tps-7609834-1-966"}
{"level":"ERROR","log":"[preprocess.go:1904] [\"InfoSchema is not SessionExtendedInfoSchema\"] [stack=\"github.com/pingcap/tidb/pkg/planner/core.tryLockMDLAndUpdateSchemaIfNecessary\\n\\t/workspace/source/tidb/pkg/planner/core/preprocess.go:1904\\ngithub.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).buildDataSource\\n\\t/workspace/source/tidb/pkg/planner/core/logical_plan_builder.go:5087\\ngithub.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).buildResultSetNode\\n\\t/workspace/source/tidb/pkg/planner/core/logical_plan_builder.go:538\\ngithub.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).buildJoin\\n\\t/workspace/source/tidb/pkg/planner/core/logical_plan_builder.go:1028\\ngithub.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).buildResultSetNode\\n\\t/workspace/source/tidb/pkg/planner/core/logical_plan_builder.go:524\\ngithub.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).buildTableRefs\\n\\t/workspace/source/tidb/pkg/planner/core/logical_plan_builder.go:516\\ngithub.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).buildSelect\\n\\t/workspace/source/tidb/pkg/planner/core/logical_plan_builder.go:4461\\ngithub.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).Build\\n\\t/workspace/source/tidb/pkg/planner/core/planbuilder.go:859\\ngithub.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).BuildDataSourceFromView\\n\\t/workspace/source/tidb/pkg/planner/core/logical_plan_builder.go:5785\\ngithub.com/pingcap/tidb/pkg/executor.tryFillViewColumnType.func1\\n\\t/workspace/source/tidb/pkg/executor/show.go:2375\\ngithub.com/pingcap/tidb/pkg/executor.runWithSystemSession\\n\\t/workspace/source/tidb/pkg/executor/show.go:2409\\ngithub.com/pingcap/tidb/pkg/executor.tryFillViewColumnType\\n\\t/workspace/source/tidb/pkg/executor/show.go:2371\\ngithub.com/pingcap/tidb/pkg/executor.(*ShowExec).fetchShowColumns\\n\\t/workspace/source/tidb/pkg/executor/show.go:668\\ngithub.com/pingcap/tidb/pkg/executor.(*ShowExec).fetchAll\\n\\t/workspace/source/tidb/pkg/executor/show.go:183\\ngithub.com/pingcap/tidb/pkg/executor.(*ShowExec).Next\\n\\t/workspace/source/tidb/pkg/executor/show.go:132\\ngithub.com/pingcap/tidb/pkg/executor/internal/exec.Next\\n\\t/workspace/source/tidb/pkg/executor/internal/exec/executor.go:283\\ngithub.com/pingcap/tidb/pkg/executor.(*ExecStmt).next\\n\\t/workspace/source/tidb/pkg/executor/adapter.go:1216\\ngithub.com/pingcap/tidb/pkg/executor.(*recordSet).Next\\n\\t/workspace/source/tidb/pkg/executor/adapter.go:156\\ngithub.com/pingcap/tidb/pkg/server/internal/resultset.(*tidbResultSet).Next\\n\\t/workspace/source/tidb/pkg/server/internal/resultset/resultset.go:62\\ngithub.com/pingcap/tidb/pkg/server.(*clientConn).writeChunks\\n\\t/workspace/source/tidb/pkg/server/conn.go:2290\\ngithub.com/pingcap/tidb/pkg/server.(*clientConn).writeResultSet\\n\\t/workspace/source/tidb/pkg/server/conn.go:2233\\ngithub.com/pingcap/tidb/pkg/server.(*clientConn).handleStmt\\n\\t/workspace/source/tidb/pkg/server/conn.go:2101\\ngithub.com/pingcap/tidb/pkg/server.(*clientConn).handleQuery\\n\\t/workspace/source/tidb/pkg/server/conn.go:1838\\ngithub.com/pingcap/tidb/pkg/server.(*clientConn).dispatch\\n\\t/workspace/source/tidb/pkg/server/conn.go:1325\\ngithub.com/pingcap/tidb/pkg/server.(*clientConn).Run\\n\\t/workspace/source/tidb/pkg/server/conn.go:1098\\ngithub.com/pingcap/tidb/pkg/server.(*Server).onConn\\n\\t/workspace/source/tidb/pkg/server/server.go:737\"]","container":"tidb","pod":"downstream-tidb-0","namespace":"brie-acceptance-tps-7609834-1-966"}

4. What is your TiDB version? (Required)

Release Version: v7.5.3
Edition: Community
Git Commit Hash: 6faf156
Git Branch: HEAD
UTC Build Time: 2024-07-26 03:50:38

[fubinzh]

/severity critical

[fubinzh]

/label affects-7.5

[lance6716]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> use test
Database changed
mysql> create table t(c int);
Query OK, 0 rows affected (0.05 sec)

mysql> create view v as select * from t;
Query OK, 0 rows affected (0.04 sec)

wait a second

mysql> set @@tidb_snapshot = NOW();
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> show columns in v;
ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

2. What did you expect to see? (Required)

no error

3. What did you see instead (Required)

mysql> show columns in v;
ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

4. What is your TiDB version? (Required)

master

[lance6716]

introduced by #54791

[D3Hunter]

this issue is part of #54343, so will not add release note for it separately