Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.43, 10.4.13
-
None
-
debian
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
- relates to
-
MDEV-23630 mysqldump to logically dump system tables
-
- Closed
-