br cannot restore mysql.user table to v5.0.2 tidb

[cyliu0]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

• Start a 5.1.0 tidb cluster and a 5.0.2 tidb cluster.
• Create a user 'u1'@'%' on 5.1.0 tidb cluster
• br backup full from 5.1.0 tidb cluster
• br restore full -f "*.*" to 5.0.2 tidb cluster
• select * from mysql.user

2. What did you expect to see?
   User u1 will be restored to 5.0.2 tidb cluster

3. What did you see instead?
   No user u1

4. What version of BR and TiDB/TiKV/PD are you using?

[root@node2 ~]# tiup br:v5.1.0-20210615 -V
Starting component `br`: /root/.tiup/components/br/v5.1.0-20210615/br -V
Release Version: v5.1.0-20210615
Git Commit Hash: 14387f934c9fff4a866a70f9c12381126166648f
Git Branch: heads/refs/tags/v5.1.0-20210615
Go Version: go1.16.4
UTC Build Time: 2021-06-15 11:45:07
Race Enabled: false

5. Operation logs
   • Please upload br.log for BR if possible

[root@node2 ~]# tiup br:v5.1.0-20210615 restore full -s "local:///tmp/br-backup-full-1100" -f "*.*" -u 127.0.0.1:42141
Starting component `br`: /root/.tiup/components/br/v5.1.0-20210615/br restore full -s local:///tmp/br-backup-full-1100 -f *.* -u 127.0.0.1:42141
Detail BR log in /tmp/br.log.2021-06-17T11.04.22+0800
Full restore <----------------------------------------------------------------------------------------------------------------/> 100.00%[2021/06/17 11:04:27.969 +08:00] [WARN] [systable_restore.go:77] ["error during merging temporary tables into system tables"] [error="[BR:Restore:ErrUnsupportedSysTable]restoring stats via `mysql` schema isn't support yet: the table ID is out-of-date and may corrupt existing statistics"] [table=stats_extended]
Full restore <-----------------------------------------------------------------------------------------------------------------> 100.00%[2021/06/17 11:04:29.010 +08:00] [WARN] [systable_restore.go:77] ["error during merging temporary tables into system tables"] [error="[BR:Common:ErrUnknown]failed to execute REPLACE INTO `mysql`.`user` SELECT * FROM `__tidb_br_temporary_mysql`.`user`;"] [table=user]
[2021/06/17 11:04:29.010 +08:00] [WARN] [systable_restore.go:77] ["error during merging temporary tables into system tables"] [error="[BR:Restore:ErrUnsupportedSysTable]restoring stats via `mysql` schema isn't support yet: the table ID is out-of-date and may corrupt existing statistics"] [table=stats_meta]
[2021/06/17 11:04:29.010 +08:00] [WARN] [systable_restore.go:77] ["error during merging temporary tables into system tables"] [error="[BR:Restore:ErrUnsupportedSysTable]restoring stats via `mysql` schema isn't support yet: the table ID is out-of-date and may corrupt existing statistics"] [table=stats_top_n]
[2021/06/17 11:04:29.017 +08:00] [WARN] [systable_restore.go:77] ["error during merging temporary tables into system tables"] [error="[BR:Restore:ErrUnsupportedSysTable]restoring unsupported `mysql` schema table"] [table=tidb]
[2021/06/17 11:04:29.035 +08:00] [WARN] [systable_restore.go:77] ["error during merging temporary tables into system tables"] [error="[BR:Restore:ErrUnsupportedSysTable]restoring stats via `mysql` schema isn't support yet: the table ID is out-of-date and may corrupt existing statistics"] [table=stats_histograms]
[2021/06/17 11:04:29.035 +08:00] [WARN] [systable_restore.go:77] ["error during merging temporary tables into system tables"] [error="[BR:Restore:ErrUnsupportedSysTable]restoring stats via `mysql` schema isn't support yet: the table ID is out-of-date and may corrupt existing statistics"] [table=stats_buckets]
[2021/06/17 11:04:29.037 +08:00] [WARN] [systable_restore.go:77] ["error during merging temporary tables into system tables"] [error="[BR:Restore:ErrUnsupportedSysTable]restoring stats via `mysql` schema isn't support yet: the table ID is out-of-date and may corrupt existing statistics"] [table=stats_feedback]
[2021/06/17 11:04:29.037 +08:00] [WARN] [systable_restore.go:77] ["error during merging temporary tables into system tables"] [error="[BR:Restore:ErrUnsupportedSysTable]restoring stats via `mysql` schema isn't support yet: the table ID is out-of-date and may corrupt existing statistics"] [table=stats_fm_sketch]
[2021/06/17 11:04:29.037 +08:00] [WARN] [systable_restore.go:77] ["error during merging temporary tables into system tables"] [error="[BR:Restore:ErrUnsupportedSysTable]restoring unsupported `mysql` schema table"] [table=GLOBAL_VARIABLES]
[2021/06/17 11:04:29.051 +08:00] [WARN] [systable_restore.go:87] ["error during reconfigurating the system tables"] [database=mysql] [error="restored user info may not take effect, until you should execute `FLUSH PRIVILEGES` manually: [BR:Restore:ErrUnsupportedSysTable]the system table isn't supported for restoring yet"]
Full restore <-----------------------------------------------------------------------------------------------------------------> 100.00%
[2021/06/17 11:04:29.588 +08:00] [INFO] [collector.go:66] ["Full restore success summary"] [total-ranges=26] [ranges-succeed=26] [ranges-failed=0] [restore-checksum=24.329872ms] [split-region=142.683892ms] [restore-ranges=23] [total-take=6.733655168s] ["restore data size(after decompressed)"=177.4kB] [total-kv=1475] [total-kv-size=86.5kB] [average-speed=1.005MB/s]

• Please upload tidb-lightning.log for TiDB-Lightning if possible
• Please upload tikv-importer.log from TiKV-Importer if possible
• Other interesting logs

6. Configuration of the cluster and the task

   • tidb-lightning.toml for TiDB-Lightning if possible
   • tikv-importer.toml for TiKV-Importer if possible
   • topology.yml if deployed by TiUP

7. Screenshot/exported-PDF of Grafana dashboard or metrics' graph in Prometheus if possible

[cyliu0]

br-restore.log

[kennytm]

The schema of mysql.user is different between 5.0.2 and 5.1.0 i guess.

[2021/06/17 11:04:29.009 +08:00] [WARN] [systable_restore.go:140] ["failed to execute SQL restore system database"] [table=user] [database=mysql] [sql="REPLACE INTO `mysql`.`user` SELECT * FROM `__tidb_br_temporary_mysql`.`user`;"] [error="[planner:1136]Column count doesn't match value count at row 1"] [errorVerbose="[planner:1136]Column count doesn't match value count at row 1\ngithub.com/pingcap/errors.AddStack\n\tgithub.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/errors.go:174\ngithub.com/pingcap/errors.(*Error).GenWithStackByArgs\n\tgithub.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/normalize.go:156\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelectPlanOfInsert\n\tgithub.com/pingcap/tidb@v1.1.0-beta.0.20210611041035-25dc28ee80f4/planner/core/planbuilder.go:3053\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildInsert\n\tgithub.com/pingcap/tidb@v1.1.0-beta.0.20210611041035-25dc28ee80f4/planner/core/planbuilder.go:2715\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).Build\n\tgithub.com/pingcap/tidb@v1.1.0-beta.0.20210611041035-25dc28ee80f4/planner/core/planbuilder.go:642\ngithub.com/pingcap/tidb/planner.optimize\n\tgithub.com/pingcap/tidb@v1.1.0-beta.0.20210611041035-25dc28ee80f4/planner/optimize.go:234\ngithub.com/pingcap/tidb/planner.Optimize\n\tgithub.com/pingcap/tidb@v1.1.0-beta.0.20210611041035-25dc28ee80f4/planner/optimize.go:124\ngithub.com/pingcap/tidb/executor.(*Compiler).Compile\n\tgithub.com/pingcap/tidb@v1.1.0-beta.0.20210611041035-25dc28ee80f4/executor/compiler.go:61\ngithub.com/pingcap/tidb/session.(*session).ExecuteStmt\n\tgithub.com/pingcap/tidb@v1.1.0-beta.0.20210611041035-25dc28ee80f4/session/session.go:1526\ngithub.com/pingcap/tidb/session.(*session).ExecuteInternal\n\tgithub.com/pingcap/tidb@v1.1.0-beta.0.20210611041035-25dc28ee80f4/session/session.go:1268\ngithub.com/pingcap/br/pkg/gluetidb.(*tidbSession).Execute\n\tgithub.com/pingcap/br/pkg/gluetidb/glue.go:108\ngithub.com/pingcap/br/pkg/restore.(*Client).replaceTemporaryTableToSystable.func1\n\tgithub.com/pingcap/br/pkg/restore/systable_restore.go:139\ngithub.com/pingcap/br/pkg/restore.(*Client).replaceTemporaryTableToSystable\n\tgithub.com/pingcap/br/pkg/restore/systable_restore.go:180\ngithub.com/pingcap/br/pkg/restore.(*Client).RestoreSystemSchemas\n\tgithub.com/pingcap/br/pkg/restore/systable_restore.go:76\ngithub.com/pingcap/br/pkg/task.RunRestore\n\tgithub.com/pingcap/br/pkg/task/restore.go:435\nmain.runRestoreCommand\n\tcommand-line-arguments/restore.go:34\nmain.newFullRestoreCommand.func1\n\tcommand-line-arguments/restore.go:120\ngithub.com/spf13/cobra.(*Command).execute\n\tgithub.com/spf13/cobra@v1.0.0/command.go:842\ngithub.com/spf13/cobra.(*Command).ExecuteC\n\tgithub.com/spf13/cobra@v1.0.0/command.go:950\ngithub.com/spf13/cobra.(*Command).Execute\n\tgithub.com/spf13/cobra@v1.0.0/command.go:887\nmain.main\n\tcommand-line-arguments/main.go:56\nruntime.main\n\truntime/proc.go:225\nruntime.goexit\n\truntime/asm_amd64.s:1371"]

[3pointer]

So. I think it's expected. but we need to consider how to handle this case in restore system tables in future. cc @IANTHEREAL

[kennytm]

We should compare the backupmeta's BootstrapVersion(A) and the cluster's BootstrapVersion(B), and then perform the corresponding upgradeToVerXXX DDLs from (A) to (B).

v5.0.2 is BootstrapVersion 68, and release-5.1 is BootstrapVersion 70,

upgradeToVer69 is:

CREATE TABLE IF NOT EXISTS mysql.global_grants (
	USER char(32) NOT NULL DEFAULT '',
	HOST char(255) NOT NULL DEFAULT '',
	PRIV char(32) NOT NULL DEFAULT '',
	WITH_GRANT_OPTION enum('N','Y') NOT NULL DEFAULT 'N',
	PRIMARY KEY (USER,HOST,PRIV)
);

upgradeToVer70 is:

ALTER TABLE mysql.user ADD COLUMN plugin CHAR(64) AFTER authentication_string;
UPDATE HIGH_PRIORITY mysql.user SET plugin='mysql_native_password';

[kennytm]

For the downgrade direction, we should

1. Fetch the column names from the target mysql.user

2. Instead of doing REPLACE INTO mysql.user SELECT * FROM temp_mysql.user, explicitly list out the columns:

   REPLACE INTO mysql.user SELECT `Host`, `User`, `Select_priv`, … FROM temp_mysql.user;

[3pointer]

#1048 fixed it