diff options
author | Monty <monty@mariadb.org> | 2022-03-23 21:17:32 +0200 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2022-04-04 16:14:22 +0300 |
commit | c4ebb2bd04974807b3b81001fd2d733e75dfc1fb (patch) | |
tree | e14045f19ee08850fe90f008ddd95915959339f1 /scripts | |
parent | 09c7f78c2ef8c43d519eb4bad61d1be177a2c9b2 (diff) | |
download | mariadb-git-c4ebb2bd04974807b3b81001fd2d733e75dfc1fb.tar.gz |
Fixed that mysql_upgrade doesn't give errors about mariadb.sys
The reason for this fix was that when I tried to run mysql_upgrade
at home to update an old 10.5 installation, mysql_upgrade failed
with warnings about mariadb.sys user not existing.
If the server was started with --skip-grants, there would be no warnings
from mysql_upgrade, but in some cases running mysql_upgrade again could
produce new warnings.
The reason for the warnings was that any access of the mysql.user view
will produce a warning if the mariadb.sys user does not exists.
Fixed with the following changes:
- Disable warnings about mariadb.sys user not existing
- Don't overwrite old mariadb.sys entries in tables_priv and global_priv
- Ensure that tables_priv has an entry for mariadb.sys if the user exists.
This fixes an issue that tables_priv would not be updated if there
was a failure directly after global_priv was updated.
Diffstat (limited to 'scripts')
-rw-r--r-- | scripts/mysql_system_tables.sql | 8 |
1 files changed, 3 insertions, 5 deletions
diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index 020385992ea..26639f613de 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -35,17 +35,15 @@ set @had_db_table= @@warning_count != 0; CREATE TABLE IF NOT EXISTS global_priv (Host char(60) binary DEFAULT '', User char(80) binary DEFAULT '', Priv JSON NOT NULL DEFAULT '{}' CHECK(JSON_VALID(Priv)), PRIMARY KEY (Host,User)) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; -set @had_sys_user= 0 <> (select count(*) from mysql.global_priv where Host="localhost" and User="mariadb.sys"); - set @exists_user_view= EXISTS (SELECT * FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user'); set @exists_user_view_by_root= EXISTS (SELECT * FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost'); -set @need_sys_user_creation= (NOT @had_sys_user) AND (( NOT @exists_user_view) OR @exists_user_view_by_root); +set @need_sys_user_creation= (( NOT @exists_user_view) OR @exists_user_view_by_root); CREATE TEMPORARY TABLE tmp_user_sys LIKE global_priv; INSERT INTO tmp_user_sys (Host,User,Priv) VALUES ('localhost','mariadb.sys','{"access":0,"plugin":"mysql_native_password","authentication_string":"","account_locked":true,"password_last_changed":0}'); -INSERT INTO global_priv SELECT * FROM tmp_user_sys WHERE 0 <> @need_sys_user_creation; +INSERT IGNORE INTO global_priv SELECT * FROM tmp_user_sys WHERE 0 <> @need_sys_user_creation; DROP TABLE tmp_user_sys; @@ -117,7 +115,7 @@ CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT NUL CREATE TEMPORARY TABLE tmp_user_sys LIKE tables_priv; INSERT INTO tmp_user_sys (Host,Db,User,Table_name,Grantor,Timestamp,Table_priv) VALUES ('localhost','mysql','mariadb.sys','global_priv','root@localhost','0','Select,Delete'); -INSERT INTO tables_priv SELECT * FROM tmp_user_sys WHERE 0 <> @need_sys_user_creation; +INSERT IGNORE INTO tables_priv SELECT * FROM tmp_user_sys WHERE 0 <> @need_sys_user_creation; DROP TABLE tmp_user_sys; CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Column privileges'; |