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

default_role gets removed when migrating from 10.1 to 10.4

    XMLWordPrintable

    Details

      Description

      We have a host running 10.1.43 with around 4k roles.
      We did a full logical copy (using mydumper and myloader) to migrate all the data to a 10.4.13 host.

      The import gave no errors, but all of a sudden we saw that the existing users with their assigned roles were not able to perform actions, they were getting permission denied.

      {root@labsdb1011.eqiad.wmnet[(none)]> show grants for 'u15343'@'%';
       
      +------------------------------------------------------------------------------------------------------------------------------------+
      | Grants for u15343@%                                                                                                                |
      +------------------------------------------------------------------------------------------------------------------------------------+
      | GRANT labsdbuser TO 'u15343'@'%'                                                                                                   |
      | GRANT USAGE ON *.* TO 'u15343'@'%' IDENTIFIED BY PASSWORD '*xx' WITH MAX_USER_CONNECTIONS 10 |
      +------------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)
       
      root@labsdb1011.eqiad.wmnet[(none)]> show grants for labsdbuser;
      +--------------------------------------------------------------------------+
      | Grants for labsdbuser                                                    |
      +--------------------------------------------------------------------------+
      | GRANT USAGE ON *.* TO 'labsdbuser'                                       |
      | GRANT SELECT, SHOW VIEW ON `sahwiki\_p`.* TO 'labsdbuser'                |
      ...
       
      root@labsdb1011.eqiad.wmnet[(none)]> show grants for labsdbuser;
      | GRANT SELECT, SHOW VIEW ON `enwiki\_p`.* TO 'labsdbuser'                 |
      | GRANT SELECT, SHOW VIEW ON `enwikiquote\_p`.* TO 'labsdbuser'            |
      | GRANT SELECT, SHOW VIEW ON `tenwiki\_p`.* TO 'labsdbuser'                |
      | GRANT SELECT, SHOW VIEW ON `enwikivoyage\_p`.* TO 'labsdbuser'           |
      | GRANT SELECT, SHOW VIEW ON `enwikiversity\_p`.* TO 'labsdbuser'          |
      | GRANT SELECT, SHOW VIEW ON `enwikisource\_p`.* TO 'labsdbuser'           |
      | GRANT SELECT, SHOW VIEW ON `enwikinews\_p`.* TO 'labsdbuser'             |
      etc
      

      Looks like the default_role is lost somehow on the migrations?

       
      mysql:u15343@localhost [(none)]> SELECT CURRENT_ROLE;
      +--------------+
      | CURRENT_ROLE |
      +--------------+
      | NULL         |
      +--------------+
      1 row in set (0.000 sec)
       
      mysql:u15343@localhost [(none)]> SET ROLE labsdbuser;
      Query OK, 0 rows affected (0.000 sec)
       
      mysql:u15343@localhost [(none)]> use enwiki_p
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Database changed
      mysql:u15343@localhost [enwiki_p]>
       
      mysql:root@localhost [(none)]> grant labsdbuser to u15343;
      Query OK, 0 rows affected (0.000 sec)
       
      mysql:root@localhost [(none)]> set default role labsdbuser for u15343;
      Query OK, 0 rows affected (0.001 sec)
       
      mysql:root@localhost [(none)]> Ctrl-C -- exit!
      Aborted
      root@labsdb1011:~# mysql --skip-ssl -uu15343 -p
      Enter password:
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 17259
      Server version: 10.4.12-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      mysql:u15343@localhost [(none)]> SELECT CURRENT_ROLE;
      +--------------+
      | CURRENT_ROLE |
      +--------------+
      | labsdbuser   |
      +--------------+
      1 row in set (0.000 sec)
       
      mysql:u15343@localhost [(none)]> use enwiki_p
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Database changed
      mysql:u15343@localhost [enwiki_p]>
      

      Doing a quick test over two hosts where we imported this into:
      labsdb1009 -> 10.1
      labsdb1011 -> 10.4
      db1141 -> 10.4

      mysql -e "select count(*) from user where default_role != ''";done
      *** labsdb1009 ***
      +----------+
      | count(*) |
      +----------+
      | 4025 |
      +----------+
      *** labsdb1011 ***
      +----------+
      | count(*) |
      +----------+
      | 34 |
      +----------+
      *** db1141 ***
      +----------+
      | count(*) |
      +----------+
      | 28 |
      +----------+
      

      Trying to isolate this a bit more

       
      root@db1077:~/mysql_dump# zcat mysql.user.sql.gz | grep -i u15343
      ("%","u15343","*x","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","","","","",0,0,0,10,"","","N","N","labsdbuser",0.000000),
       
      mysql database imported from labsdb1009:
       
      root@db1077:~/mysql_dump# myloader -o -B mysql -d .
      root@db1077:~/mysql_dump# mysql mysql -e "select * from user where user like 'u15343'"
      +------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+------------------+---------+--------------+--------------------+
      | Host | User   | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | is_role | default_role | max_statement_time |
      +------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+------------------+---------+--------------+--------------------+
      | %    | u15343 | *x | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                   10 |        |                       | N                | N       | labsdbuser   |           0.000000 |
      +------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+------------------+---------+--------------+--------------------+
       
      root@db1077:~/mysql_dump# mysql_upgrade --force
      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.host                                         OK
      mysql.index_stats                                  OK
      mysql.innodb_index_stats                           OK
      mysql.innodb_table_stats                           OK
      mysql.ndb_binlog_index                             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
      mysql.user                                         OK
      Phase 2/7: Installing used storage engines... Skipped
      Phase 3/7: Fixing views
      racktables.Location
      Error    : The user specified as a definer ('racktables'@'%') does not exist
      error    : Corrupt
      racktables.Rack
      Error    : The user specified as a definer ('racktables'@'%') does not exist
      error    : Corrupt
      racktables.RackObject
      Error    : The user specified as a definer ('racktables'@'%') does not exist
      error    : Corrupt
      racktables.Row
      Error    : The user specified as a definer ('racktables'@'%') does not exist
      error    : Corrupt
      sys.host_summary
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.host_summary_by_file_io
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.host_summary_by_file_io_type
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.host_summary_by_stages
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.host_summary_by_statement_latency
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.host_summary_by_statement_type
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.innodb_buffer_stats_by_schema
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.innodb_buffer_stats_by_table
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.innodb_lock_waits
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.io_by_thread_by_latency
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.io_global_by_file_by_bytes
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.io_global_by_file_by_latency
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.io_global_by_wait_by_bytes
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.io_global_by_wait_by_latency
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.latest_file_io
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.metrics                                        OK
      sys.processlist
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.ps_check_lost_instrumentation                  OK
      sys.schema_auto_increment_columns                  OK
      sys.schema_index_statistics
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.schema_object_overview                         OK
      sys.schema_redundant_indexes                       OK
      sys.schema_table_statistics
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.schema_table_statistics_with_buffer
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.schema_tables_with_full_table_scans
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.schema_unused_indexes                          OK
      sys.session
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.statement_analysis
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.statements_with_errors_or_warnings
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.statements_with_full_table_scans
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.statements_with_runtimes_in_95th_percentile
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.statements_with_sorting
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.statements_with_temp_tables
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.user_summary
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.user_summary_by_file_io
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.user_summary_by_file_io_type
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.user_summary_by_stages
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.user_summary_by_statement_latency
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.user_summary_by_statement_type
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.version                                        OK
      sys.wait_classes_global_by_avg_latency
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.wait_classes_global_by_latency
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.waits_by_host_by_latency
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.waits_by_user_by_latency
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.waits_global_by_latency
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.x$host_summary                                 OK
      sys.x$host_summary_by_file_io                      OK
      sys.x$host_summary_by_file_io_type                 OK
      sys.x$host_summary_by_stages                       OK
      sys.x$host_summary_by_statement_latency            OK
      sys.x$host_summary_by_statement_type               OK
      sys.x$innodb_buffer_stats_by_schema                OK
      sys.x$innodb_buffer_stats_by_table                 OK
      sys.x$innodb_lock_waits                            OK
      sys.x$io_by_thread_by_latency                      OK
      sys.x$io_global_by_file_by_bytes                   OK
      sys.x$io_global_by_file_by_latency                 OK
      sys.x$io_global_by_wait_by_bytes                   OK
      sys.x$io_global_by_wait_by_latency                 OK
      sys.x$latest_file_io                               OK
      sys.x$processlist                                  OK
      sys.x$ps_digest_95th_percentile_by_avg_us          OK
      sys.x$ps_digest_avg_latency_distribution           OK
      sys.x$ps_schema_table_statistics_io
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.x$schema_flattened_keys                        OK
      sys.x$schema_index_statistics                      OK
      sys.x$schema_table_statistics
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.x$schema_table_statistics_with_buffer
      Error    : Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted
      error    : Corrupt
      sys.x$schema_tables_with_full_table_scans          OK
      sys.x$session                                      OK
      sys.x$statement_analysis                           OK
      sys.x$statements_with_errors_or_warnings           OK
      sys.x$statements_with_full_table_scans             OK
      sys.x$statements_with_runtimes_in_95th_percentile  OK
      sys.x$statements_with_sorting                      OK
      sys.x$statements_with_temp_tables                  OK
      sys.x$user_summary                                 OK
      sys.x$user_summary_by_file_io                      OK
      sys.x$user_summary_by_file_io_type                 OK
      sys.x$user_summary_by_stages                       OK
      sys.x$user_summary_by_statement_latency            OK
      sys.x$user_summary_by_statement_type               OK
      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'
      Phase 5/7: Fixing table and database names
      Phase 6/7: Checking and upgrading tables
      Processing databases
      <removed - no errors>
      Phase 7/7: Running 'FLUSH PRIVILEGES'
      OK
      root@db1077:~/mysql_dump# mysql mysql -e "select * from user where user like 'u15343'"
      root@db1077:~/mysql_dump# 

       Looks like the default role gets lost after mysql_upgrade runs? 

       mysql:root@localhost [mysql]> select * from roles_mapping where User='u15343';
      +------+--------+------------+--------------+
      | Host | User   | Role       | Admin_option |
      +------+--------+------------+--------------+
      | %    | u15343 | labsdbuser | N            |
      +------+--------+------------+--------------+
      1 row in set (0.003 sec)
       
      mysql:root@localhost [mysql]> select default_role from user where user like 'u15343';
      Empty set (0.002 sec)
       

       
      The work around for this seems to be: 

       set default role labsdbuser for $user;

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              marostegui Manuel Arostegui
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration