Elena Stepanova added a comment - 2020-05-21 22:33 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.

Manuel Arostegui added a comment - 2020-05-22 06:44 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?

Daniel Black added a comment - 2021-02-02 01:17 > 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

Manuel Arostegui added a comment - 2021-02-02 05:54 Thanks Daniel - excellent news!