On upgrade Truncate_versioning_privilege was not correct set

Fixed that Truncate_versioning_privilege works as any other privilege
during upgrade:

- If the privilege field does not exists, add it to the user and db tables.
  If the user had super_privilege then the user will also get the new
  Truncate_versioning_privilege.

This is done to ensure that if one has GRANT ALL PRIVILEGE before, one
will continue to have it after running mysql_upgrade.

This also fixes a bug where the Truncate_versioning_privilege

3 files changed, 53 insertions, 6 deletions

diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result
index 5da7e3149ec..12e52463ce4 100644
--- a/mysql-test/r/mysql_upgrade.result
+++ b/mysql-test/r/mysql_upgrade.result
@@ -571,3 +571,21 @@ t1	CREATE TABLE `t1` (
 DROP TABLE test.t1;
 SET GLOBAL enforce_storage_engine=NULL;
 End of 10.1 tests
+Start of 10.3 tests
+#
+# Ensure that mysql_upgrade correctly sets truncate_versioning_priv
+# on upgrade from 10.2
+#
+flush privileges;
+CREATE USER 'user3'@'%';
+GRANT USAGE ON *.* TO 'user3'@'%';
+GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%';
+alter table mysql.user drop column Truncate_versioning_priv;
+flush privileges;
+Run mysql_upgrade with all privileges on a user
+flush privileges;
+SHOW GRANTS FOR 'user3'@'%';
+Grants for user3@%
+GRANT USAGE ON *.* TO 'user3'@'%'
+GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%'
+DROP USER 'user3'@'%';
diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test
index a19be30e68f..c8992d58378 100644
--- a/mysql-test/t/mysql_upgrade.test
+++ b/mysql-test/t/mysql_upgrade.test
@@ -219,3 +219,23 @@ DROP TABLE test.t1;
 SET GLOBAL enforce_storage_engine=NULL;
 
 --echo End of 10.1 tests
+
+--echo Start of 10.3 tests
+
+--echo #
+--echo # Ensure that mysql_upgrade correctly sets truncate_versioning_priv
+--echo # on upgrade from 10.2
+--echo #
+
+flush privileges;
+CREATE USER 'user3'@'%';
+GRANT USAGE ON *.* TO 'user3'@'%';
+GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%';
+alter table mysql.user drop column Truncate_versioning_priv;
+flush privileges;
+--echo Run mysql_upgrade with all privileges on a user
+--exec $MYSQL_UPGRADE --force --silent 2>&1
+--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
+flush privileges;
+SHOW GRANTS FOR 'user3'@'%';
+DROP USER 'user3'@'%';
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
index 44a95483564..39277f8975c 100644
--- a/scripts/mysql_system_tables_fix.sql
+++ b/scripts/mysql_system_tables_fix.sql
@@ -645,6 +645,21 @@ ALTER TABLE user MODIFY Create_tablespace_priv enum('N','Y') COLLATE utf8_genera
 
 UPDATE user SET Create_tablespace_priv = Super_priv WHERE @hadCreateTablespacePriv = 0;
 
+#
+# System versioning
+#
+
+SET @had_truncate_versioning_priv := 0;
+SELECT @had_truncate_versioning_priv :=1 FROM user WHERE Truncate_versioning_priv LIKE '%';
+
+ALTER TABLE user add Truncate_versioning_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N' after Create_tablespace_priv;
+ALTER TABLE user modify Truncate_versioning_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N';
+ALTER TABLE db add Truncate_versioning_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N' after Trigger_priv;
+ALTER TABLE db modify Truncate_versioning_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N';
+
+UPDATE user SET Truncate_versioning_priv = Super_priv WHERE @had_truncate_versioning_priv = 0;
+
+
 ALTER TABLE user ADD plugin char(64) DEFAULT '',  ADD authentication_string TEXT;
 ALTER TABLE user ADD password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
 ALTER TABLE user ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
@@ -744,9 +759,3 @@ ALTER TABLE help_topic MODIFY url TEXT NOT NULL;
 
 # MDEV-7383 - varbinary on mix/max of column_stats
 alter table column_stats modify min_value varbinary(255) DEFAULT NULL, modify max_value varbinary(255) DEFAULT NULL;
-
-# System versioning
-ALTER TABLE user add Truncate_versioning_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N' after Trigger_priv;
-ALTER TABLE user modify Truncate_versioning_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N';
-ALTER TABLE db add Truncate_versioning_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N' after Trigger_priv;
-ALTER TABLE db modify Truncate_versioning_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N';