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

Errors upgrading from Mariadb 10.2.15 to 10.6.7

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Incomplete
    • 10.6.7
    • N/A
    • Upgrades
    • None
    • RHEL 3.10.0-1160.25.1.el7.x86_64

    Description

      Was performing an upgrade from Mariadb 10.2.15 to 10.6.7, by first completely unistalling and then installing the upgraded version:

      yum remove MariaDB-server MariaDB-client
      yum clean all
      yum install MariaDB-server MariaDB-client

      Proceeded to start mariadb service:

      systemctl start mariadb

      Run upgrade:

      sudo mysql_upgrade -uroot -p

      Got the following errors:

      sys.x$waits_by_host_by_latency OK
      sys.x$waits_by_user_by_latency OK
      sys.x$waits_global_by_latency OK
      Phase 4/7: Running 'mysql_fix_privilege_tables'
      ERROR 1449 (HY000) at line 32: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 41: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 84: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 93: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 111: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 197: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 206: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 208: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 219: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 224: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 227: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 353: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 356: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 425: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 430: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 432: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 435: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 439: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 444: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 465: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1449 (HY000) at line 473: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
      ERROR 1 (HY000) at line 553: Can't create/write to file '/tmp/#sql-temptable-4c02-12-195.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 567: Can't create/write to file '/tmp/#sql-temptable-4c02-12-197.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 568: Can't create/write to file '/tmp/#sql-temptable-4c02-12-198.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 570: Can't create/write to file '/tmp/#sql-temptable-4c02-12-199.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 629: Can't create/write to file '/tmp/#sql-temptable-4c02-12-19a.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 757: Can't create/write to file '/tmp/#sql-temptable-4c02-12-19c.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 787: Can't create/write to file '/tmp/#sql-temptable-4c02-12-1a1.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 2287: Can't create/write to file '/tmp/#sql-temptable-4c02-12-1b0.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 2306: Can't create/write to file '/tmp/#sql-temptable-4c02-12-1b1.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 2451: Can't create/write to file '/tmp/#sql-temptable-4c02-12-1b7.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 2476: Can't create/write to file '/tmp/#sql-temptable-4c02-12-1b8.MAI' (Errcode: 13 "Permission denied")

      Reading from other similar issues, attempted to fix the definer does not exist by running the following commands:

      insert into mysql.global_priv values ('localhost','mariadb.sys','{"access":0,"plugin":"mysql_native_password","authentication_string":"","account_locked":true,"password_last_changed":0}');

      GRANT ALL PRIVILEGES ON . TO 'mariadb.sys'@'localhost' WITH GRANT OPTION;

      flush privileges;

      Re-running mysql upgrade, see the permission denied errors only:

      sys.x$wait_classes_global_by_avg_latency OK
      sys.x$wait_classes_global_by_latency OK
      sys.x$waits_by_host_by_latency OK
      sys.x$waits_by_user_by_latency OK
      sys.x$waits_global_by_latency OK
      Phase 4/7: Running 'mysql_fix_privilege_tables'
      ERROR 1 (HY000) at line 553: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-100.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 567: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-102.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 568: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-103.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 570: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-104.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 629: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-105.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 757: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-107.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 787: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-10c.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 2287: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-11b.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 2306: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-11c.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 2451: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-122.MAI' (Errcode: 13 "Permission denied")
      ERROR 1 (HY000) at line 2476: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-123.MAI' (Errcode: 13 "Permission denied")
      FATAL ERROR: Upgrade failed

      Here are the privileges for /tmp:

      [root@xsdclxmdndb001 hscadmin]$ namei -om /tmp
      f: /tmp
      dr-xr-xr-x root root /
      drwxrwxrwt root root tmp

      Attachments

        Activity

          twix1312 Ritwik C Pandav added a comment - - edited

          Added config to read another temp directory in /etc/my.cnf

          [mysqld]
          tmpdir = /logsdb/

          Where

          [root@xsdclxmdndb001 logsdb]$ namei -om /logsdb/
          f: /logsdb/
          dr-xr-xr-x root root /
          drwxr-xr-x mysql mysql logsdb

          This seems to work

          twix1312 Ritwik C Pandav added a comment - - edited Added config to read another temp directory in /etc/my.cnf [mysqld] tmpdir = /logsdb/ Where [root@xsdclxmdndb001 logsdb] $ namei -om /logsdb/ f: /logsdb/ dr-xr-xr-x root root / drwxr-xr-x mysql mysql logsdb This seems to work
          twix1312 Ritwik C Pandav added a comment - - edited

          Noticed the following errors in the mysql upgrade script:

          [root@xsdclxmdndb001 my.cnf.d]$ sudo mysql_upgrade -uroot -phsc321
          Phase 1/7: Checking and upgrading mysql database
          Processing databases
          mysql
          mysql.column_stats OK
          mysql.columns_priv OK
          mysql.db OK
          mysql.event OK
          mysql.func OK
          mysql.global_priv OK
          mysql.gtid_slave_pos OK
          mysql.help_category OK
          mysql.help_keyword OK
          mysql.help_relation OK
          mysql.help_topic OK
          mysql.index_stats OK
          mysql.innodb_index_stats OK
          mysql.innodb_table_stats OK
          mysql.plugin OK
          mysql.proc OK
          mysql.procs_priv OK
          mysql.proxies_priv OK
          mysql.roles_mapping OK
          mysql.servers OK
          mysql.table_stats OK
          mysql.tables_priv OK
          mysql.time_zone OK
          mysql.time_zone_leap_second OK
          mysql.time_zone_name OK
          mysql.time_zone_transition OK
          mysql.time_zone_transition_type OK
          mysql.transaction_registry OK
          Phase 2/7: Installing used storage engines... Skipped
          Phase 3/7: Fixing views
          mysql.user
          Error : SELECT command denied to user 'mariadb.sys'@'localhost' for table 'global_priv'
          Error : View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
          error : Corrupt
          sys.host_summary OK
          sys.host_summary_by_file_io OK
          sys.host_summary_by_file_io_type OK

          Any idea why this happens? What's the repercussion of this error and how to avoid it?

          twix1312 Ritwik C Pandav added a comment - - edited Noticed the following errors in the mysql upgrade script: [root@xsdclxmdndb001 my.cnf.d] $ sudo mysql_upgrade -uroot -phsc321 Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.global_priv OK mysql.gtid_slave_pos OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.transaction_registry OK Phase 2/7: Installing used storage engines... Skipped Phase 3/7: Fixing views mysql.user Error : SELECT command denied to user 'mariadb.sys'@'localhost' for table 'global_priv' Error : View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error : Corrupt sys.host_summary OK sys.host_summary_by_file_io OK sys.host_summary_by_file_io_type OK Any idea why this happens? What's the repercussion of this error and how to avoid it?

          First, try to select from mysql.user. If it works — the view is fine.

          If not:
          Repercussion: if you have tools that use mysql.user they might not work.
          Better not use those tools anyway, as mysql.user is an incomplete representation of the true account data mysql.global_priv, so you'll miss information if you'll use tools that rely on mysql.user.
          But anyway, you can try to drop the view and re-run mariadb-upgrade, it could recreate the view with the correct definition.

          serg Sergei Golubchik added a comment - First, try to select from mysql.user . If it works — the view is fine. If not: Repercussion: if you have tools that use mysql.user they might not work. Better not use those tools anyway, as mysql.user is an incomplete representation of the true account data mysql.global_priv , so you'll miss information if you'll use tools that rely on mysql.user . But anyway, you can try to drop the view and re-run mariadb-upgrade, it could recreate the view with the correct definition.

          FLUSH PRIVILEGES or server restart should do the trick, I think.

          The mystery here is why the permission denied error occurred. The sticky bit alone doesn't cause it, not even on RHEL 7 (I checked).
          Also, if the directory weren't writable, the server probably wouldn't even start, InnoDB tries to write something into the tmpdir on startup.
          The presence of temporary files with the same names but wrong ownership could have done it, but such a coincidence seems to be very unlikely.
          So, I'm out of ideas.

          But assuming there was a valid reason for permission denied errors, the rest seems clear enough.

          • Upon the very first mysql_upgrade attempt after 10.2=>10.6, only permission denied errors would occur (this run is not shown in the bug description, the description contains an output of a re-run). At this point, mysql.user is still a table and no mariadb.sys user exists; mysql_upgrade attempts to create mariadb.sys user first, fails to do so because it cannot create a temporary table, but proceeds far enough to create mysql.user view and sysschema views, and eventually aborts. So, it ends up with a bunch of views with a non-existing definer.
          • Upon further attempts, it can't read the views (hence the errors about mysql.user and complaints about non-existing definer), and still can't create temporary tables, hence permission denied errors.
          • After the tmpdir is pointed to something writable and the server is restarted, the next mysql_upgrade succeeds at creating mariadb.sys; but it does so by inserting into the table, without flushing privileges, so authentication remains unaware of the existence of mariadb.sys. FLUSH PRIVILEGES is executed at the very end of mysql_upgrade, but it aborts before that, so it never happens. Thus, this and consequent mysql_upgrade runs still show the error for mysql.user and complain about "non-existing" mariadb.sys. To get rid of the errors, either FLUSH PRIVILEGES should be run explicitly, or the server should be restarted, and then mysql_upgrade should be performed again, to make sure everything is executed to the end.

          Maybe it's possible to reshuffle mysql_upgrade actions a bit or make it less tolerant to intermediate errors (so that it didn't create views after failing to create the definer, for example), but I'm not sure it's worth the risk of causing some other problems.

          It would be still good to understand the cause of the "access denied" errors. twix1312, did you ever figure out why they were happening?

          elenst Elena Stepanova added a comment - FLUSH PRIVILEGES or server restart should do the trick, I think. The mystery here is why the permission denied error occurred. The sticky bit alone doesn't cause it, not even on RHEL 7 (I checked). Also, if the directory weren't writable, the server probably wouldn't even start, InnoDB tries to write something into the tmpdir on startup. The presence of temporary files with the same names but wrong ownership could have done it, but such a coincidence seems to be very unlikely. So, I'm out of ideas. But assuming there was a valid reason for permission denied errors, the rest seems clear enough. Upon the very first mysql_upgrade attempt after 10.2=>10.6, only permission denied errors would occur (this run is not shown in the bug description, the description contains an output of a re-run). At this point, mysql.user is still a table and no mariadb.sys user exists; mysql_upgrade attempts to create mariadb.sys user first, fails to do so because it cannot create a temporary table, but proceeds far enough to create mysql.user view and sysschema views, and eventually aborts. So, it ends up with a bunch of views with a non-existing definer. Upon further attempts, it can't read the views (hence the errors about mysql.user and complaints about non-existing definer), and still can't create temporary tables, hence permission denied errors. After the tmpdir is pointed to something writable and the server is restarted, the next mysql_upgrade succeeds at creating mariadb.sys; but it does so by inserting into the table, without flushing privileges, so authentication remains unaware of the existence of mariadb.sys. FLUSH PRIVILEGES is executed at the very end of mysql_upgrade, but it aborts before that, so it never happens. Thus, this and consequent mysql_upgrade runs still show the error for mysql.user and complain about "non-existing" mariadb.sys. To get rid of the errors, either FLUSH PRIVILEGES should be run explicitly, or the server should be restarted, and then mysql_upgrade should be performed again, to make sure everything is executed to the end. Maybe it's possible to reshuffle mysql_upgrade actions a bit or make it less tolerant to intermediate errors (so that it didn't create views after failing to create the definer, for example), but I'm not sure it's worth the risk of causing some other problems. It would be still good to understand the cause of the "access denied" errors. twix1312 , did you ever figure out why they were happening?

          People

            Unassigned Unassigned
            twix1312 Ritwik C Pandav
            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.