Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24363

mysql.user view 'N' AS `password_expired` is incorrect

Details

    Description

      since MDEV-7597 this should be a JSON expression.

      also missing from MDEV-13095 account_locked

      So from:

      mysql-8.0 mysql.user

          `password_expired` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
        `password_last_changed` timestamp NULL DEFAULT NULL,
        `password_lifetime` smallint unsigned DEFAULT NULL,
        `account_locked` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
      

      So these columns should be in the mysql.user view too.

      Attachments

        Activity

          danblack Daniel Black added a comment -

          > mysql_fix_privilege_tables_sql.c

          Thanks for the explanation.

          > But it seems to me that we need to drop the view unconditionally for every mysql_upgrade to make this patch work on current 10.4+ versions. I need to check with Vicentiu or Sergei

          See the 3rd comment.

          > Or of course, drop the view conditionally based on parsing the value of the query view from SHOW CREATE VIEW, but this is very very ugly IMO.

          Its very very practical IMO. There is no ALTER VIEW, a view's existence is parsed. As a system VIEW its definition is rather fixed and providing you're are matching something fairly strict there should be a low change of failure. And the consequence of a mismatch - the view gets recreated.

          danblack Daniel Black added a comment - > mysql_fix_privilege_tables_sql.c Thanks for the explanation. > But it seems to me that we need to drop the view unconditionally for every mysql_upgrade to make this patch work on current 10.4+ versions. I need to check with Vicentiu or Sergei See the 3rd comment. > Or of course, drop the view conditionally based on parsing the value of the query view from SHOW CREATE VIEW, but this is very very ugly IMO. Its very very practical IMO. There is no ALTER VIEW, a view's existence is parsed. As a system VIEW its definition is rather fixed and providing you're are matching something fairly strict there should be a low change of failure. And the consequence of a mismatch - the view gets recreated.
          robertbindar Robert Bindar added a comment -

          Hey danblack, after struggling to understand how to fix some failing tests and how to add a new test for this mdev without listing again all the fields of the mysql.user view and add one more maintainance nightmare for the future, I think I managed to find a solution that would do.

          Feel free to review it: https://github.com/MariaDB/server/commit/8fbf872b6e7d3c2d743619b796f55c94964d4850

          Tests are green on both buildbots on bb-10.4-robert

          robertbindar Robert Bindar added a comment - Hey danblack , after struggling to understand how to fix some failing tests and how to add a new test for this mdev without listing again all the fields of the mysql.user view and add one more maintainance nightmare for the future, I think I managed to find a solution that would do. Feel free to review it: https://github.com/MariaDB/server/commit/8fbf872b6e7d3c2d743619b796f55c94964d4850 Tests are green on both buildbots on bb-10.4-robert
          robertbindar Robert Bindar added a comment -

          Also I proposed something like this to Sanja to further reduce future pain, but let's wait till he gets back from vacation and see if he agrees. https://gist.github.com/robertbindar/63e24ee79eb99c60e961d5cbb026cc89

          robertbindar Robert Bindar added a comment - Also I proposed something like this to Sanja to further reduce future pain, but let's wait till he gets back from vacation and see if he agrees. https://gist.github.com/robertbindar/63e24ee79eb99c60e961d5cbb026cc89
          robertbindar Robert Bindar added a comment -

          Pushed into 10.4. Followup fix to refactor MDEV-24363 tests also pushed into 10.4

          robertbindar Robert Bindar added a comment - Pushed into 10.4. Followup fix to refactor MDEV-24363 tests also pushed into 10.4
          danblack Daniel Black added a comment -

          thanks robertbindar

          danblack Daniel Black added a comment - thanks robertbindar

          People

            robertbindar Robert Bindar
            danblack Daniel Black
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.