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
-
Activity
@Daniel Black
We are currently on 10.6.15 and I can confirm that the column limits did not increase during our 10.5 to 10.6 upgrade.
I am planning on upgrading to 10.11.9 - would this fix the limits?
Yes, anything above the fixedVersions listed at the top of this MDEV. 10.6.18+ also fixes it.
Even on 10.6.15 you can run the ALTER TABLE instructions highlighted in green on the right hand side of https://github.com/MariaDB/server/pull/3144/files#diff-773930212c131b69bdf9e5944d12142364419730bb32c19a1266dedd77aa72f8 and achieve this fix.
I don't this password_expired expression is incorrect - it was just adding it as an expression where previously in <=10.4.3 where there was no password expiry implemented and the view was returning a fixed value.
Ref commit
So the mysql.user view is correct, the table definitions do need user,host to be of the larger width however.
$ podman volume create m105m105$ podman run -v m105:/var/lib/mysql --env MARIADB_ROOT_PASSWORD=bob -d --name m105 mariadb:10.591831e8f44010ebb3c67f3116cf0c99aa5863c2ba2aa3bd00f78eea82c78a7ea$ podman exec m105 mariadb -pbob -e 'show create table mysql.global_priv; shutdown'Table Create Tableglobal_priv CREATE TABLE `global_priv` (\n `Host` char(60) NOT NULL DEFAULT '',\n `User` char(80) NOT NULL DEFAULT '',\n `Priv` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '{}' CHECK (json_valid(`Priv`)),\n PRIMARY KEY (`Host`,`User`)\n) ENGINE=Aria DEFAULT CHARSET=utf8 COLLATE=utf8_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Users and global privileges'$ podman run -v m105:/var/lib/mysql --env MARIADB_AUTO_UPGRADE=1 -d --name m106 mariadb:10.64789e8483b8806ffd1b52cbfe7c143cf6cf197ef33b8fbb4150236e8ecd5ee03$ podman exec m106 mariadb -pbob -e 'show create table mysql.global_priv'Table Create Tableglobal_priv CREATE TABLE `global_priv` (\n `Host` char(60) NOT NULL DEFAULT '',\n `User` char(80) NOT NULL DEFAULT '',\n `Priv` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '{}' CHECK (json_valid(`Priv`)),\n PRIMARY KEY (`Host`,`User`)\n) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Users and global privileges'$ podman exec m106 mariadb -pbob -e 'show create table mysql.procs_priv'Table Create Tableprocs_priv CREATE TABLE `procs_priv` (\n `Host` char(60) NOT NULL DEFAULT '',\n `Db` char(64) NOT NULL DEFAULT '',\n `User` char(128) NOT NULL DEFAULT '',\n `Routine_name` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',\n `Routine_type` enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL,\n `Grantor` varchar(384) NOT NULL DEFAULT '',\n `Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',\n `Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),\n PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),\n KEY `Grantor` (`Grantor`)\n) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Procedure privileges'$ podman exec m106 mariadb -pbob -e 'show create table mysql.proxies_priv'Table Create Tableproxies_priv CREATE TABLE `proxies_priv` (\n `Host` char(60) NOT NULL DEFAULT '',\n `User` char(128) NOT NULL DEFAULT '',\n `Proxied_host` char(60) NOT NULL DEFAULT '',\n `Proxied_user` char(128) NOT NULL DEFAULT '',\n `With_grant` tinyint(1) NOT NULL DEFAULT 0,\n `Grantor` varchar(384) NOT NULL DEFAULT '',\n `Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),\n PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),\n KEY `Grantor` (`Grantor`)\n) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='User proxy privileges'$ podman exec m106 mariadb -pbob -e 'show create table mysql.roles_mapping'Table Create Tableroles_mapping CREATE TABLE `roles_mapping` (\n `Host` char(60) NOT NULL DEFAULT '',\n `User` char(80) NOT NULL DEFAULT '',\n `Role` char(80) NOT NULL DEFAULT '',\n `Admin_option` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',\n UNIQUE KEY `Host` (`Host`,`User`,`Role`)\n) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Granted roles'