[MDEV-22645] default_role gets removed when migrating from 10.1 to 10.4 Created: 2020-05-20  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.1.43, 10.4.13
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Manuel Arostegui Assignee: Vicențiu Ciorbaru
Resolution: Unresolved Votes: 0
Labels: None
Environment:

debian

Issue Links:
Relates
relates to MDEV-23630 mysqldump to logically dump system ta... Closed

 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;



 Comments   
Comment by Elena Stepanova [ 2020-05-21 ]

First, what you are loading into and upgrading is not a native 10.4 schema. It's possible that we have overlooked an upgrade path, but we need to know for sure what it is you are upgrading. From the errors it looks more like former MySQL 5.7 maybe, although I can't reproduce it right away with a borrowed 5.7 datadir, either. However, according to your counts, not all default roles are lost, so it may be tricky to reproduce.

There is also a possibility that mydumper / myloader don't dump/load all the system tables consistently. We don't maintain it and don't test upgrade through it, so I can't say if it's so or not.

If you could provide a part of a dump which demonstrates the problem, it would be very helpful. Naturally you can obfuscate the confidential data, as long as the structure remains intact.

Comment by Manuel Arostegui [ 2020-05-22 ]

We are extracting it from a 10.1 host:

+-----------------+
 
| @@version       |
 
+-----------------+
 
| 10.1.43-MariaDB |
 
+-----------------+
 
 
 
select * from user where user like 'u15343'\G
 
*************************** 1. row ***************************
 
                  Host: %
 
                  User: u15343
 
              Password: *x
 
           Select_priv: N
 
           Insert_priv: N
 
           Update_priv: N
 
           Delete_priv: N
 
           Create_priv: N
 
             Drop_priv: N
 
           Reload_priv: N
 
         Shutdown_priv: N
 
          Process_priv: N
 
             File_priv: N
 
            Grant_priv: N
 
       References_priv: N
 
            Index_priv: N
 
            Alter_priv: N
 
          Show_db_priv: N
 
            Super_priv: N
 
 Create_tmp_table_priv: N
 
      Lock_tables_priv: N
 
          Execute_priv: N
 
       Repl_slave_priv: N
 
      Repl_client_priv: N
 
      Create_view_priv: N
 
        Show_view_priv: N
 
   Create_routine_priv: N
 
    Alter_routine_priv: N
 
      Create_user_priv: N
 
            Event_priv: N
 
          Trigger_priv: N
 
Create_tablespace_priv: N
 
              ssl_type:
 
            ssl_cipher:
 
           x509_issuer:
 
          x509_subject:
 
         max_questions: 0
 
           max_updates: 0
 
       max_connections: 0
 
  max_user_connections: 10
 
                plugin:
 
 authentication_string:
 
      password_expired: N
 
               is_role: N
 
          default_role: labsdbuser
 
    max_statement_time: 0.000000
 
1 row in set (0.00 sec)

This is a grep on the mydumper files, which are just plain text, they are nothing special.

grep -iR u15343 *
 
mysql.roles_mapping.sql:("%","u15343","labsdbuser","N"),
 
mysql.user.sql:("%","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),

And after importing the files:

mysql:root@localhost [mysql]> select @@version;
 
+---------------------+
 
| @@version           |
 
+---------------------+
 
| 10.4.12-MariaDB-log |
 
+---------------------+
 
1 row in set (0.000 sec)
 
 
 
mysql:root@localhost [mysql]> select * from user where user like 'u15343'\G
 
*************************** 1. row ***************************
 
                  Host: %
 
                  User: u15343
 
              Password: *x
 
           Select_priv: N
 
           Insert_priv: N
 
           Update_priv: N
 
           Delete_priv: N
 
           Create_priv: N
 
             Drop_priv: N
 
           Reload_priv: N
 
         Shutdown_priv: N
 
          Process_priv: N
 
             File_priv: N
 
            Grant_priv: N
 
       References_priv: N
 
            Index_priv: N
 
            Alter_priv: N
 
          Show_db_priv: N
 
            Super_priv: N
 
 Create_tmp_table_priv: N
 
      Lock_tables_priv: N
 
          Execute_priv: N
 
       Repl_slave_priv: N
 
      Repl_client_priv: N
 
      Create_view_priv: N
 
        Show_view_priv: N
 
   Create_routine_priv: N
 
    Alter_routine_priv: N
 
      Create_user_priv: N
 
            Event_priv: N
 
          Trigger_priv: N
 
Create_tablespace_priv: N
 
              ssl_type:
 
            ssl_cipher:
 
           x509_issuer:
 
          x509_subject:
 
         max_questions: 0
 
           max_updates: 0
 
       max_connections: 0
 
  max_user_connections: 10
 
                plugin:
 
 authentication_string:
 
      password_expired: N
 
               is_role: N
 
          default_role: labsdbuser
 
    max_statement_time: 0.000000
 
1 row in set (0.009 sec)
 
 
 
mysql:root@localhost [mysql]> select * from roles_mapping where user like 'u15343';
 
+------+--------+------------+--------------+
 
| Host | User   | Role       | Admin_option |
 
+------+--------+------------+--------------+
 
| %    | u15343 | labsdbuser | N            |
 
+------+--------+------------+--------------+
 
1 row in set (0.003 sec)


Now we run mysql_upgrade:

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
 
--redacted -- (all OK)
 
Phase 7/7: Running 'FLUSH PRIVILEGES'
 
OK
 
root@db1077:~/export-20200522-055607# mysql
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 606136
 
Server version: 10.4.12-MariaDB-log 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:root@localhost [(none)]> Ctrl-C -- exit!
 
Aborted
 
root@db1077:~/export-20200522-055607# mysql  mysql -A
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 606137
 
Server version: 10.4.12-MariaDB-log 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:root@localhost [mysql]> select * from user where user like 'u15343'\G
 
Empty set (0.002 sec)
 
 
 
mysql:root@localhost [mysql]> select * from roles_mapping where user like 'u15343';
 
+------+--------+------------+--------------+
 
| Host | User   | Role       | Admin_option |
 
+------+--------+------------+--------------+
 
| %    | u15343 | labsdbuser | N            |
 
+------+--------+------------+--------------+
 
1 row in set (0.004 sec)
 
 
 
mysql:root@localhost [mysql]> select * from user where user like 'u15343'\G
 
Empty set (0.001 sec)

we use mydumper/myloader because it allows us export and import things in pararell, as mysqldump doesn't provide that feature.
I have tried to do it by mysqldump, but I run into other issues:

ERROR 1471 (HY000): The target table user of the INSERT is not insertable-into

I assume this is because the user table on 10.4 is a view but then what is the correct way of importing users via mysqldump into from 10.1 into 10.4?

Comment by Daniel Black [ 2021-02-02 ]

> but then what is the correct way of importing users via mysqldump into from 10.1 into 10.4?

MDEV-23630 adds `mysqldump --system=users` which can migrate users between all versions by outputing CREATE USER sql

Comment by Manuel Arostegui [ 2021-02-02 ]

Thanks Daniel - excellent news!

Generated at Thu Feb 08 09:16:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.