1# Test uses lower_case_table_names=2. So test needs case sensitive file system 2# 3# Bug#30248138 - adding a function once mysql.func is converted to myisam 4# leads to crash 5# 6#----------------------------------------------------------------------- 7# Test cases to verify system table's behavior with storage engines 8# InnoDB and MyISAM. 9# 10# Table name comparison is "case insensitive" with lower_case_table_name=2. 11# Run "system_tables_storage_engine_tests.inc" tests with upper case 12# system table names . 13#----------------------------------------------------------------------- 14CREATE TABLE system_tables (ID INT PRIMARY KEY AUTO_INCREMENT, 15table_name VARCHAR(100)); 16INSERT INTO system_tables(table_name) 17SELECT UPPER(concat(table_schema, ".", table_name)) 18FROM INFORMATION_SCHEMA.tables WHERE table_schema = 19'mysql' AND table_name NOT IN('general_log', 'slow_log', 20'ndb_binlog_index'); 21#----------------------------------------------------------------------- 22# Test case to verify altering system table's engine to MyISAM. Changing 23# system table's engine to MyISAM is not allowed. 24#----------------------------------------------------------------------- 25CREATE PROCEDURE test_system_table_alter_engine() 26BEGIN 27DECLARE n INT DEFAULT 0; 28DECLARE i INT DEFAULT 1; 29-- ER_UNSUPPORTED_ENGINE(1726) is reported for all the system engines except for 30-- innodb_index_stats and innodb_table_stats. ER_TOO_LONG_KEY(1071) is reported for 31-- innodb_index_stats and ER_NOT_ALLOWED_COMMAND(1148) is reported for innodb_table_stats 32-- for now. 33DECLARE CONTINUE HANDLER FOR 1726, 1071, 1148 34BEGIN 35GET DIAGNOSTICS CONDITION 1 @message = MESSAGE_TEXT; 36SELECT @message AS ERROR; 37END; 38SELECT count(*) FROM system_tables INTO n; 39WHILE i <= n DO 40SELECT table_name FROM system_tables WHERE id = i INTO @table_name; 41SELECT @table_name; 42SET @sql_text = concat("ALTER TABLE ", @table_name, " ENGINE = MyISAM"); 43PREPARE stmt FROM @sql_text; 44EXECUTE stmt; 45DEALLOCATE PREPARE stmt; 46SET i = i + 1; 47END WHILE; 48END$ 49CALL test_system_table_alter_engine(); 50@table_name 51MYSQL.COLUMNS_PRIV 52ERROR 53Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv] 54@table_name 55MYSQL.COMPONENT 56ERROR 57Storage engine 'MyISAM' does not support system tables. [mysql.component] 58@table_name 59MYSQL.DB 60ERROR 61Storage engine 'MyISAM' does not support system tables. [mysql.db] 62@table_name 63MYSQL.DEFAULT_ROLES 64ERROR 65Storage engine 'MyISAM' does not support system tables. [mysql.default_roles] 66@table_name 67MYSQL.ENGINE_COST 68ERROR 69Storage engine 'MyISAM' does not support system tables. [mysql.engine_cost] 70@table_name 71MYSQL.FUNC 72ERROR 73Storage engine 'MyISAM' does not support system tables. [mysql.func] 74@table_name 75MYSQL.GLOBAL_GRANTS 76ERROR 77Storage engine 'MyISAM' does not support system tables. [mysql.global_grants] 78@table_name 79MYSQL.GTID_EXECUTED 80ERROR 81Storage engine 'MyISAM' does not support system tables. [mysql.gtid_executed] 82@table_name 83MYSQL.HELP_CATEGORY 84ERROR 85Storage engine 'MyISAM' does not support system tables. [mysql.help_category] 86@table_name 87MYSQL.HELP_KEYWORD 88ERROR 89Storage engine 'MyISAM' does not support system tables. [mysql.help_keyword] 90@table_name 91MYSQL.HELP_RELATION 92ERROR 93Storage engine 'MyISAM' does not support system tables. [mysql.help_relation] 94@table_name 95MYSQL.HELP_TOPIC 96ERROR 97Storage engine 'MyISAM' does not support system tables. [mysql.help_topic] 98@table_name 99MYSQL.INNODB_INDEX_STATS 100ERROR 101Specified key was too long; max key length is 1000 bytes 102@table_name 103MYSQL.INNODB_TABLE_STATS 104ERROR 105The used command is not allowed with this MySQL version 106@table_name 107MYSQL.PASSWORD_HISTORY 108ERROR 109Storage engine 'MyISAM' does not support system tables. [mysql.password_history] 110@table_name 111MYSQL.PLUGIN 112ERROR 113Storage engine 'MyISAM' does not support system tables. [mysql.plugin] 114@table_name 115MYSQL.PROCS_PRIV 116ERROR 117Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv] 118@table_name 119MYSQL.PROXIES_PRIV 120ERROR 121Storage engine 'MyISAM' does not support system tables. [mysql.proxies_priv] 122@table_name 123MYSQL.ROLE_EDGES 124ERROR 125Storage engine 'MyISAM' does not support system tables. [mysql.role_edges] 126@table_name 127MYSQL.SERVER_COST 128ERROR 129Storage engine 'MyISAM' does not support system tables. [mysql.server_cost] 130@table_name 131MYSQL.SERVERS 132ERROR 133Storage engine 'MyISAM' does not support system tables. [mysql.servers] 134@table_name 135MYSQL.SLAVE_MASTER_INFO 136ERROR 137Storage engine 'MyISAM' does not support system tables. [mysql.slave_master_info] 138@table_name 139MYSQL.SLAVE_RELAY_LOG_INFO 140ERROR 141Storage engine 'MyISAM' does not support system tables. [mysql.slave_relay_log_info] 142@table_name 143MYSQL.SLAVE_WORKER_INFO 144ERROR 145Storage engine 'MyISAM' does not support system tables. [mysql.slave_worker_info] 146@table_name 147MYSQL.TABLES_PRIV 148ERROR 149Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv] 150@table_name 151MYSQL.TIME_ZONE 152ERROR 153Storage engine 'MyISAM' does not support system tables. [mysql.time_zone] 154@table_name 155MYSQL.TIME_ZONE_LEAP_SECOND 156ERROR 157Storage engine 'MyISAM' does not support system tables. [mysql.time_zone_leap_second] 158@table_name 159MYSQL.TIME_ZONE_NAME 160ERROR 161Storage engine 'MyISAM' does not support system tables. [mysql.time_zone_name] 162@table_name 163MYSQL.TIME_ZONE_TRANSITION 164ERROR 165Storage engine 'MyISAM' does not support system tables. [mysql.time_zone_transition] 166@table_name 167MYSQL.TIME_ZONE_TRANSITION_TYPE 168ERROR 169Storage engine 'MyISAM' does not support system tables. [mysql.time_zone_transition_type] 170@table_name 171MYSQL.USER 172ERROR 173Storage engine 'MyISAM' does not support system tables. [mysql.user] 174DROP PROCEDURE test_system_table_alter_engine; 175#----------------------------------------------------------------------- 176# Test case to verify system table creation in MyISAM engine. Creating 177# system table in MyISAM is allowed to support logical upgrade. 178#----------------------------------------------------------------------- 179CREATE PROCEDURE execute_stmt(stmt VARCHAR(255)) 180BEGIN 181SET @error_no = 0; 182SET @sql_stmt = stmt; 183PREPARE stmt FROM @sql_stmt; 184EXECUTE stmt; 185GET DIAGNOSTICS CONDITION 1 @error_no = MYSQL_ERRNO, @error_message = MESSAGE_TEXT; 186IF @error_no > 0 THEN 187SELECT "Warning" AS SEVERITY, @error_no as ERRNO, @error_message as MESSAGE; 188END IF; 189DEALLOCATE PREPARE stmt; 190END$ 191CREATE PROCEDURE test_create_system_table() 192BEGIN 193DECLARE n INT DEFAULT 0; 194DECLARE i INT DEFAULT 1; 195-- Error ER_NO_SYSTEM_TABLE_ACCESS(3554) is reported for innodb_table_stats and 196-- innodb_index_stats. For other tables ER_UNSUPPORTED_ENGINE "warning" is reported. 197-- Warning is handled in the execute_stmt(). 198DECLARE CONTINUE HANDLER FOR 3554 199BEGIN 200GET DIAGNOSTICS CONDITION 1 @error = MYSQL_ERRNO, @error_message = MESSAGE_TEXT; 201END; 202SELECT count(*) FROM system_tables INTO n; 203WHILE i <= n DO 204SET @error = 0; 205SELECT table_name FROM system_tables WHERE id = i INTO @table_name; 206SET @sql_text = concat('RENAME TABLE ', @table_name, ' TO mysql.backup_table'); 207CALL execute_stmt(@sql_text); 208SET @sql_text = concat('CREATE TABLE ', @table_name, ' ENGINE=InnoDB AS SELECT * FROM mysql.backup_table'); 209CALL execute_stmt(@sql_text); 210SET @sql_text = concat('DROP TABLE ', @table_name); 211CALL execute_stmt(@sql_text); 212SET @sql_text = concat('CREATE TABLE ', @table_name, ' ENGINE=MyISAM AS SELECT * FROM mysql.backup_table'); 213CALL execute_stmt(@sql_text); 214IF @error > 0 THEN 215SELECT "ERROR" AS SEVERITY, @error AS ERRNO, @error_message AS MESSAGE; 216ELSE 217SET @sql_text = concat('DROP TABLE ', @table_name); 218CALL execute_stmt(@sql_text); 219END IF; 220SET @sql_text = concat('RENAME TABLE mysql.backup_table TO ', @table_name); 221CALL execute_stmt(@sql_text); 222SET i = i + 1; 223END WHILE; 224END$ 225CALL test_create_system_table(); 226SEVERITY ERRNO MESSAGE 227Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv] 228SEVERITY ERRNO MESSAGE 229Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.component] 230SEVERITY ERRNO MESSAGE 231Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.db] 232SEVERITY ERRNO MESSAGE 233Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.default_roles] 234SEVERITY ERRNO MESSAGE 235Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.engine_cost] 236SEVERITY ERRNO MESSAGE 237Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.func] 238SEVERITY ERRNO MESSAGE 239Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.global_grants] 240SEVERITY ERRNO MESSAGE 241Warning 3129 Please do not modify the gtid_executed table. This is a mysql internal system table to store GTIDs for committed transactions. Modifying it can lead to an inconsistent GTID state. 242SEVERITY ERRNO MESSAGE 243Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.gtid_executed] 244SEVERITY ERRNO MESSAGE 245Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.help_category] 246SEVERITY ERRNO MESSAGE 247Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.help_keyword] 248SEVERITY ERRNO MESSAGE 249Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.help_relation] 250SEVERITY ERRNO MESSAGE 251Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.help_topic] 252SEVERITY ERRNO MESSAGE 253ERROR 3554 Access to system table 'mysql.innodb_index_stats' is rejected. 254SEVERITY ERRNO MESSAGE 255ERROR 3554 Access to system table 'mysql.innodb_table_stats' is rejected. 256SEVERITY ERRNO MESSAGE 257Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.password_history] 258SEVERITY ERRNO MESSAGE 259Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.plugin] 260SEVERITY ERRNO MESSAGE 261Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv] 262SEVERITY ERRNO MESSAGE 263Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.proxies_priv] 264SEVERITY ERRNO MESSAGE 265Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.role_edges] 266SEVERITY ERRNO MESSAGE 267Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.server_cost] 268SEVERITY ERRNO MESSAGE 269Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.servers] 270SEVERITY ERRNO MESSAGE 271Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.slave_master_info] 272SEVERITY ERRNO MESSAGE 273Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.slave_relay_log_info] 274SEVERITY ERRNO MESSAGE 275Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.slave_worker_info] 276SEVERITY ERRNO MESSAGE 277Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv] 278SEVERITY ERRNO MESSAGE 279Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.time_zone] 280SEVERITY ERRNO MESSAGE 281Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.time_zone_leap_second] 282SEVERITY ERRNO MESSAGE 283Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.time_zone_name] 284SEVERITY ERRNO MESSAGE 285Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.time_zone_transition] 286SEVERITY ERRNO MESSAGE 287Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.time_zone_transition_type] 288SEVERITY ERRNO MESSAGE 289Warning 1726 Storage engine 'MyISAM' does not support system tables. [mysql.user] 290DROP PROCEDURE test_create_system_table; 291DROP PROCEDURE execute_stmt; 292DROP TABLE system_tables; 293