mirror of
https://github.com/MariaDB/server.git
synced 2025-10-08 00:39:14 +02:00
eeb00ceffd
Follow-up patch with adjustments of test files and updates of result files for tests. Some of tests were rewritten slighlty. Everywhere where common pattern used: ----- CREATE USER userA; --connect con1 ... userA ... <sql statements...> --disconnect con1 DROP USER userA; ----- the DROP USER statement has been eclosed into the directive --disable_warnings --enable_warnings This change is caused by the race conddition between --disconnect and DROP USER since a number of currently running sessions established on behalf the user being dropped is counted by holding the rw_lock THD_list_iterator::lock that is not acquired on execution the DROP USER statement but the lock is taken as the last step on handling disconnection (when the client is already sending the next statement). Therefore, for the cases where the command --disconnect precedes the DROP USER statement we hide the possible warnings about presence of active sessions for the user being deleted to make tests deterministic.
316 lines
16 KiB
Text
316 lines
16 KiB
Text
call mtr.add_suppression("Cannot load from mysql.proc. The table is probably corrupted");
|
|
create database mysqltest1;
|
|
use mysqltest1;
|
|
create table save_global_priv as select * from mysql.global_priv;
|
|
create table save_tables_priv as select * from mysql.tables_priv;
|
|
create table save_proxies_priv as select * from mysql.proxies_priv;
|
|
create table mysql.save_proc like mysql.proc;
|
|
insert into mysql.save_proc select * from mysql.proc;
|
|
set @save_sql_mode= @@sql_mode;
|
|
use mysql;
|
|
# make old definition of gis procedures and user view
|
|
drop view user;
|
|
CREATE DEFINER='root'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS SELECT
|
|
Host,
|
|
User,
|
|
IF(JSON_VALUE(Priv, '$.plugin') IN ('mysql_native_password', 'mysql_old_password'), IFNULL(JSON_VALUE(Priv, '$.authentication_string'), ''), '') AS Password,
|
|
IF(JSON_VALUE(Priv, '$.access') & 1, 'Y', 'N') AS Select_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 2, 'Y', 'N') AS Insert_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 4, 'Y', 'N') AS Update_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 8, 'Y', 'N') AS Delete_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 16, 'Y', 'N') AS Create_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 32, 'Y', 'N') AS Drop_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 64, 'Y', 'N') AS Reload_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 128, 'Y', 'N') AS Shutdown_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 256, 'Y', 'N') AS Process_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 512, 'Y', 'N') AS File_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 1024, 'Y', 'N') AS Grant_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 2048, 'Y', 'N') AS References_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 4096, 'Y', 'N') AS Index_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 8192, 'Y', 'N') AS Alter_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 16384, 'Y', 'N') AS Show_db_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 32768, 'Y', 'N') AS Super_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 65536, 'Y', 'N') AS Create_tmp_table_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 131072, 'Y', 'N') AS Lock_tables_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 262144, 'Y', 'N') AS Execute_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 524288, 'Y', 'N') AS Repl_slave_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 1048576, 'Y', 'N') AS Repl_client_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 2097152, 'Y', 'N') AS Create_view_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 4194304, 'Y', 'N') AS Show_view_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 8388608, 'Y', 'N') AS Create_routine_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 16777216, 'Y', 'N') AS Alter_routine_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 33554432, 'Y', 'N') AS Create_user_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 67108864, 'Y', 'N') AS Event_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 134217728, 'Y', 'N') AS Trigger_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 268435456, 'Y', 'N') AS Create_tablespace_priv,
|
|
IF(JSON_VALUE(Priv, '$.access') & 536870912, 'Y', 'N') AS Delete_history_priv,
|
|
ELT(IFNULL(JSON_VALUE(Priv, '$.ssl_type'), 0) + 1, '', 'ANY','X509', 'SPECIFIED') AS ssl_type,
|
|
IFNULL(JSON_VALUE(Priv, '$.ssl_cipher'), '') AS ssl_cipher,
|
|
IFNULL(JSON_VALUE(Priv, '$.x509_issuer'), '') AS x509_issuer,
|
|
IFNULL(JSON_VALUE(Priv, '$.x509_subject'), '') AS x509_subject,
|
|
CAST(IFNULL(JSON_VALUE(Priv, '$.max_questions'), 0) AS UNSIGNED) AS max_questions,
|
|
CAST(IFNULL(JSON_VALUE(Priv, '$.max_updates'), 0) AS UNSIGNED) AS max_updates,
|
|
CAST(IFNULL(JSON_VALUE(Priv, '$.max_connections'), 0) AS UNSIGNED) AS max_connections,
|
|
CAST(IFNULL(JSON_VALUE(Priv, '$.max_user_connections'), 0) AS SIGNED) AS max_user_connections,
|
|
IFNULL(JSON_VALUE(Priv, '$.plugin'), '') AS plugin,
|
|
IFNULL(JSON_VALUE(Priv, '$.authentication_string'), '') AS authentication_string,
|
|
'N' AS password_expired,
|
|
ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role,
|
|
IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role,
|
|
CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) AS max_statement_time
|
|
FROM global_priv;
|
|
SET sql_mode='';
|
|
DROP PROCEDURE IF EXISTS mysql.AddGeometryColumn;
|
|
DROP PROCEDURE IF EXISTS mysql.DropGeometryColumn;
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE AddGeometryColumn(catalog varchar(64), t_schema varchar(64),
|
|
t_name varchar(64), geometry_column varchar(64), t_srid int) SQL SECURITY INVOKER
|
|
begin
|
|
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end |
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE DropGeometryColumn(catalog varchar(64), t_schema varchar(64),
|
|
t_name varchar(64), geometry_column varchar(64)) SQL SECURITY INVOKER
|
|
begin
|
|
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end |
|
|
set @@sql_mode= @save_sql_mode;
|
|
drop user 'mariadb.sys'@'localhost';
|
|
# check old definitions mysql_upgrade
|
|
SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'root@localhost';
|
|
count(*)
|
|
1
|
|
SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost';
|
|
count(*)
|
|
0
|
|
SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
|
|
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
|
|
SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
|
|
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
|
|
# Run mysql_upgrade
|
|
Phase 1/8: Checking and upgrading mysql database
|
|
Processing databases
|
|
mysql
|
|
mysql.column_stats OK
|
|
mysql.columns_priv OK
|
|
mysql.db OK
|
|
mysql.event OK
|
|
mysql.func OK
|
|
mysql.global_priv OK
|
|
mysql.gtid_slave_pos OK
|
|
mysql.help_category OK
|
|
mysql.help_keyword OK
|
|
mysql.help_relation OK
|
|
mysql.help_topic OK
|
|
mysql.index_stats OK
|
|
mysql.innodb_index_stats
|
|
Error : Unknown storage engine 'InnoDB'
|
|
error : Corrupt
|
|
mysql.innodb_table_stats
|
|
Error : Unknown storage engine 'InnoDB'
|
|
error : Corrupt
|
|
mysql.plugin OK
|
|
mysql.proc OK
|
|
mysql.procs_priv OK
|
|
mysql.proxies_priv OK
|
|
mysql.roles_mapping OK
|
|
mysql.save_proc 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
|
|
Error : Unknown storage engine 'InnoDB'
|
|
error : Corrupt
|
|
|
|
Repairing tables
|
|
mysql.innodb_index_stats
|
|
Error : Unknown storage engine 'InnoDB'
|
|
error : Corrupt
|
|
mysql.innodb_table_stats
|
|
Error : Unknown storage engine 'InnoDB'
|
|
error : Corrupt
|
|
mysql.transaction_registry
|
|
Error : Unknown storage engine 'InnoDB'
|
|
error : Corrupt
|
|
Phase 2/8: Installing used storage engines... Skipped
|
|
Phase 3/8: Running 'mysql_fix_privilege_tables'
|
|
Phase 4/8: Fixing views
|
|
mysql.user
|
|
Warning : The user specified as a definer ('mariadb.sys'@'localhost') does not exist
|
|
status : OK
|
|
sys.host_summary OK
|
|
sys.host_summary_by_file_io OK
|
|
sys.host_summary_by_file_io_type OK
|
|
sys.host_summary_by_stages OK
|
|
sys.host_summary_by_statement_latency OK
|
|
sys.host_summary_by_statement_type OK
|
|
sys.innodb_buffer_stats_by_schema OK
|
|
sys.innodb_buffer_stats_by_table OK
|
|
sys.innodb_lock_waits OK
|
|
sys.io_by_thread_by_latency OK
|
|
sys.io_global_by_file_by_bytes OK
|
|
sys.io_global_by_file_by_latency OK
|
|
sys.io_global_by_wait_by_bytes OK
|
|
sys.io_global_by_wait_by_latency OK
|
|
sys.latest_file_io OK
|
|
sys.memory_by_host_by_current_bytes OK
|
|
sys.memory_by_thread_by_current_bytes OK
|
|
sys.memory_by_user_by_current_bytes OK
|
|
sys.memory_global_by_current_bytes OK
|
|
sys.memory_global_total OK
|
|
sys.metrics OK
|
|
sys.privileges_by_table_by_level OK
|
|
sys.processlist OK
|
|
sys.ps_check_lost_instrumentation OK
|
|
sys.schema_auto_increment_columns OK
|
|
sys.schema_index_statistics OK
|
|
sys.schema_object_overview OK
|
|
sys.schema_redundant_indexes OK
|
|
sys.schema_table_lock_waits OK
|
|
sys.schema_table_statistics OK
|
|
sys.schema_table_statistics_with_buffer OK
|
|
sys.schema_tables_with_full_table_scans OK
|
|
sys.schema_unused_indexes OK
|
|
sys.session OK
|
|
sys.session_ssl_status OK
|
|
sys.statement_analysis OK
|
|
sys.statements_with_errors_or_warnings OK
|
|
sys.statements_with_full_table_scans OK
|
|
sys.statements_with_runtimes_in_95th_percentile OK
|
|
sys.statements_with_sorting OK
|
|
sys.statements_with_temp_tables OK
|
|
sys.user_summary OK
|
|
sys.user_summary_by_file_io OK
|
|
sys.user_summary_by_file_io_type OK
|
|
sys.user_summary_by_stages OK
|
|
sys.user_summary_by_statement_latency OK
|
|
sys.user_summary_by_statement_type OK
|
|
sys.version OK
|
|
sys.wait_classes_global_by_avg_latency OK
|
|
sys.wait_classes_global_by_latency OK
|
|
sys.waits_by_host_by_latency OK
|
|
sys.waits_by_user_by_latency OK
|
|
sys.waits_global_by_latency OK
|
|
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$memory_by_host_by_current_bytes OK
|
|
sys.x$memory_by_thread_by_current_bytes OK
|
|
sys.x$memory_by_user_by_current_bytes OK
|
|
sys.x$memory_global_by_current_bytes OK
|
|
sys.x$memory_global_total 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 OK
|
|
sys.x$schema_flattened_keys OK
|
|
sys.x$schema_index_statistics OK
|
|
sys.x$schema_table_lock_waits OK
|
|
sys.x$schema_table_statistics OK
|
|
sys.x$schema_table_statistics_with_buffer OK
|
|
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 5/8: Fixing table and database names
|
|
Phase 6/8: Checking and upgrading tables
|
|
Processing databases
|
|
information_schema
|
|
mtr
|
|
mtr.global_suppressions OK
|
|
mtr.test_suppressions OK
|
|
mysqltest1
|
|
mysqltest1.save_global_priv OK
|
|
mysqltest1.save_proxies_priv OK
|
|
mysqltest1.save_tables_priv OK
|
|
performance_schema
|
|
sys
|
|
sys.sys_config OK
|
|
test
|
|
Phase 7/8: uninstalling plugins
|
|
Phase 8/8: Running 'FLUSH PRIVILEGES'
|
|
OK
|
|
# check new definitions mysql_upgrade
|
|
SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'root@localhost';
|
|
count(*)
|
|
0
|
|
SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost';
|
|
count(*)
|
|
1
|
|
SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
|
|
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
|
|
'mariadb.sys'@'localhost' def USAGE NO
|
|
SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
|
|
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
|
|
'mariadb.sys'@'localhost' def mysql global_priv SELECT NO
|
|
'mariadb.sys'@'localhost' def mysql global_priv DELETE NO
|
|
# check non root
|
|
CREATE USER 'not_root'@'localhost';
|
|
GRANT ALL PRIVILEGES ON *.* TO 'not_root'@'localhost';
|
|
GRANT PROXY ON ''@'%' TO 'not_root'@'localhost' WITH GRANT OPTION;
|
|
connect con1,localhost,not_root,,;
|
|
connection con1;
|
|
DROP USER 'root'@'localhost';
|
|
Warnings:
|
|
Note 4226 Dropped users ['root'@'localhost'] have active connections. Use KILL CONNECTION if they should not be used anymore.
|
|
DROP USER 'root'@'127.0.0.1';
|
|
DROP USER 'root'@'::1';
|
|
use mysqltest1;
|
|
create table t1 (a int);
|
|
call mysql.AddGeometryColumn("def", "mysqltest1", "t1", "g", 101);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`g` geometry DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
use mysql;
|
|
select count(*) from user;
|
|
count(*)
|
|
3
|
|
# restore environment
|
|
delete from global_priv;
|
|
delete from tables_priv;
|
|
delete from proxies_priv;
|
|
delete from proc;
|
|
insert into mysql.global_priv select * from mysqltest1.save_global_priv;
|
|
insert into mysql.tables_priv select * from mysqltest1.save_tables_priv;
|
|
insert into mysql.proxies_priv select * from mysqltest1.save_proxies_priv;
|
|
rename table proc to bad_proc;
|
|
rename table save_proc to proc;
|
|
drop table bad_proc;
|
|
flush privileges;
|
|
disconnect default;
|
|
connect default,localhost,root,,;
|
|
connection default;
|
|
disconnect con1;
|
|
drop database mysqltest1;
|
|
# End of 10.4 tests (but better do not add other tests here)
|