Details
Description
On MariaDB 10.5 the mysql.global_priv table has following definition:
|
|
MariaDB [(none)]> desc mysql.global_priv;
|
+-------+----------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+----------+------+-----+---------+-------+
|
| Host | char(60) | NO | PRI | | | |
| User | char(80) | NO | PRI | | | |
| Priv | longtext | NO | | '{}' | | |
+-------+----------+------+-----+---------+-------+
|
3 rows in set (0.001 sec) |
MariaDB 10.6 has:
MariaDB [mysql]> desc mysql.global_priv;
|
+-------+-----------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+-----------+------+-----+---------+-------+
|
| Host | char(255) | NO | PRI | | | |
| User | char(128) | NO | PRI | | | |
| Priv | longtext | NO | | '{}' | | |
+-------+-----------+------+-----+---------+-------+
|
3 rows in set (0,005 sec) |
After updating 10.5 to 10.6 the mysql_upgrade tool is not modifying, as expected on 10.6, the global_priv ARIA table.
it is executing following statements keeping the 10.5 definition:
CREATE TABLE IF NOT EXISTS global_priv (Host char(255) binary DEFAULT '', User char(128) binary DEFAULT '', Priv JSON NOT NULL DEFAULT '{}' CHECK(JSON_VALID(Priv)), PRIMARY KEY (Host,User)) engine=Aria transactional=1 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin comment='Users and global privileges' |
|
|
|
|
CREATE DEFINER='mariadb.sys'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS ..... |
|
Also, it is running many ALTERs/UPDATEs on user VIEW, which fail for obvious reasons, such as:
ALTER TABLE user MODIFY Host char(255) NOT NULL default '', MODIFY User char(128) binary NOT NULL default '', ENGINE=Aria, CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin |
|
|
UPDATE user LEFT JOIN db USING (Host,User) SET Create_user_priv='Y' WHERE @hadCreateUserPriv = 0 AND (user.Grant_priv = 'Y' OR db.Grant_priv = 'Y') |
|
Also, maybe there is an error in the script 'script/mysql_system_tables_fix.sql' on query:
IF 1 = ( |
SELECT count(*) FROM information_schema.VIEWS
|
WHERE
|
TABLE_CATALOG = 'def' and |
TABLE_SCHEMA = 'mysql' and |
TABLE_NAME='user' and |
(DEFINER = 'root@localhost' or (DEFINER = 'mariadb.sys@localhost' and VIEW_DEFINITION LIKE "%'N' AS `password_expired`%")) |
) THEN
|
DROP VIEW IF EXISTS mysql.user;
|
given the `password_expired` column definition on VIEW user:
if(ifnull(json_value(`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired` |
|
Is it correct to say that there is a parenthesis missing after the 'N' on LIKE condition?
I mean, probably should be:
VIEW_DEFINITION LIKE "%'N') AS `password_expired`%") |
|
|
|
it seems this issue is related to tables/view having a 'user' and/or 'host' columns:
mysql.user
mysql.global_priv
mysql.procs_priv
mysql.proxies_priv
mysql.roles_mapping
Attachments
Issue Links
- is caused by
-
MDEV-24312 master_host has 60 character limit, increase to 255 bytes
-
- Closed
-
-
MDEV-26363 Passwords incorrectly expiring after MySQL5.7 -> MariaDB10.3 -> 10.4+ upgrades.
-
- Closed
-