Skip to content

Commit 796fad1

Browse files
author
[email protected]
committed
Bug#11815557 60269: MYSQL SHOULD REJECT ATTEMPTS TO CREATE SYSTEM
 TABLES IN INCORRECT ENGINE PROBLEM: CREATE/ALTER TABLE currently can move system tables like mysql.db, user, host etc, to engines other than MyISAM. This is not completely supported as of now, by mysqld. When some of system tables like plugin, servers, event, func, *_priv, time_zone* are moved to innodb, mysqld restart crashes. Currently system tables can be moved to BLACKHOLE also!!!. ANALYSIS: The problem is that there is no check before creating or moving a system table to some particular engine. System tables are suppose to be residing in MyISAM. We can think of restricting system tables to exist only in MyISAM. But, there could be future needs of these system tables to be part of other engines by design. For eg, NDB cluster expects some tables to be on innodb or ndb engine. This calls for a solution, by which system tables can be supported by any desired engine, with minimal effort. FIX: The solution provides a handlerton interface using which, mysqld server can query particular storage engine handlerton for system tables that it supports. This way each storage engine layer can define their own system database and system tables. The check_engine() function uses the new handlerton function ha_check_if_supported_system_table() to check if db.tablename provided in the DDL is supported by the SE. Note: This fix has modified a test in help.test, which was moving mysql.help_* to innodb. The primary intention of the test was not to move them between engines.
1 parent d3782bf commit 796fad1

10 files changed

Lines changed: 628 additions & 134 deletions

File tree

‎mysql-test/r/alter_table.result‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1433,3 +1433,58 @@ rename table t2 to t1;
14331433
execute stmt1;
14341434
deallocate prepare stmt1;
14351435
drop table t2;
1436+
#
1437+
# Bug#11815557 60269: MYSQL SHOULD REJECT ATTEMPTS TO CREATE SYSTEM
1438+
# TABLES IN INCORRECT ENGINE
1439+
#
1440+
# Note: This test assumes that only MyISAM supports system tables.
1441+
# If other engines are made to support system tables,
1442+
# then this test needs to be updated
1443+
#
1444+
use mysql;
1445+
ALTER TABLE db ENGINE=innodb;
1446+
ERROR HY000: Storage engine 'InnoDB' does not support system tables. [mysql.db]
1447+
ALTER TABLE user ENGINE=memory;
1448+
ERROR HY000: Storage engine 'MEMORY' does not support system tables. [mysql.user]
1449+
ALTER TABLE proc ENGINE=heap;
1450+
ERROR HY000: Storage engine 'MEMORY' does not support system tables. [mysql.proc]
1451+
ALTER TABLE func ENGINE=csv;
1452+
ERROR HY000: Storage engine 'CSV' does not support system tables. [mysql.func]
1453+
ALTER TABLE event ENGINE=merge;
1454+
ERROR HY000: Storage engine 'MRG_MYISAM' does not support system tables. [mysql.event]
1455+
ALTER TABLE servers ENGINE=innodb;
1456+
ERROR HY000: Storage engine 'InnoDB' does not support system tables. [mysql.servers]
1457+
ALTER TABLE procs_priv ENGINE=memory;
1458+
ERROR HY000: Storage engine 'MEMORY' does not support system tables. [mysql.procs_priv]
1459+
ALTER TABLE tables_priv ENGINE=heap;
1460+
ERROR HY000: Storage engine 'MEMORY' does not support system tables. [mysql.tables_priv]
1461+
ALTER TABLE columns_priv ENGINE=csv;
1462+
ERROR HY000: Storage engine 'CSV' does not support system tables. [mysql.columns_priv]
1463+
ALTER TABLE time_zone ENGINE=merge;
1464+
ERROR HY000: Storage engine 'MRG_MYISAM' does not support system tables. [mysql.time_zone]
1465+
ALTER TABLE help_topic ENGINE=innodb;
1466+
ERROR HY000: Storage engine 'InnoDB' does not support system tables. [mysql.help_topic]
1467+
CREATE TABLE db (dummy int) ENGINE=innodb;
1468+
ERROR HY000: Storage engine 'InnoDB' does not support system tables. [mysql.db]
1469+
CREATE TABLE user (dummy int) ENGINE=memory;
1470+
ERROR HY000: Storage engine 'MEMORY' does not support system tables. [mysql.user]
1471+
CREATE TABLE proc (dummy int) ENGINE=heap;
1472+
ERROR HY000: Storage engine 'MEMORY' does not support system tables. [mysql.proc]
1473+
CREATE TABLE func (dummy int) ENGINE=csv;
1474+
ERROR HY000: Storage engine 'CSV' does not support system tables. [mysql.func]
1475+
CREATE TABLE event (dummy int) ENGINE=merge;
1476+
ERROR HY000: Storage engine 'MRG_MYISAM' does not support system tables. [mysql.event]
1477+
CREATE TABLE servers (dummy int) ENGINE=innodb;
1478+
ERROR HY000: Storage engine 'InnoDB' does not support system tables. [mysql.servers]
1479+
CREATE TABLE procs_priv (dummy int) ENGINE=memory;
1480+
ERROR HY000: Storage engine 'MEMORY' does not support system tables. [mysql.procs_priv]
1481+
CREATE TABLE tables_priv (dummy int) ENGINE=heap;
1482+
ERROR HY000: Storage engine 'MEMORY' does not support system tables. [mysql.tables_priv]
1483+
CREATE TABLE columns_priv (dummy int) ENGINE=csv;
1484+
ERROR HY000: Storage engine 'CSV' does not support system tables. [mysql.columns_priv]
1485+
CREATE TABLE time_zone (dummy int) ENGINE=merge;
1486+
ERROR HY000: Storage engine 'MRG_MYISAM' does not support system tables. [mysql.time_zone]
1487+
CREATE TABLE help_topic (dummy int) ENGINE=innodb;
1488+
ERROR HY000: Storage engine 'InnoDB' does not support system tables. [mysql.help_topic]
1489+
use test;
1490+
# End of Bug#11815557

‎mysql-test/r/help.result‎

Lines changed: 0 additions & 94 deletions
Original file line numberDiff line numberDiff line change
@@ -148,100 +148,6 @@ help 'impossible_category_1';
148148
source_category_name name is_it_category
149149
impossible_category_1 impossible_function_1 N
150150
impossible_category_1 impossible_function_2 N
151-
alter table mysql.help_relation engine=innodb;
152-
alter table mysql.help_keyword engine=innodb;
153-
alter table mysql.help_topic engine=innodb;
154-
alter table mysql.help_category engine=innodb;
155-
help 'function_of_my_dream';
156-
name is_it_category
157-
help '%possible_f%';
158-
name is_it_category
159-
impossible_function_1 N
160-
impossible_function_2 N
161-
impossible_function_3 N
162-
impossible_function_4 N
163-
impossible_function_7 N
164-
help 'impossible_func%';
165-
name is_it_category
166-
impossible_function_1 N
167-
impossible_function_2 N
168-
impossible_function_3 N
169-
impossible_function_4 N
170-
impossible_function_7 N
171-
help 'impossible_category%';
172-
name is_it_category
173-
impossible_category_1 Y
174-
impossible_category_2 Y
175-
impossible_category_3 Y
176-
help 'impossible_%';
177-
name is_it_category
178-
impossible_function_1 N
179-
impossible_function_2 N
180-
impossible_function_3 N
181-
impossible_function_4 N
182-
impossible_function_7 N
183-
impossible_category_1 Y
184-
impossible_category_2 Y
185-
impossible_category_3 Y
186-
help '%function_1';
187-
name description example
188-
impossible_function_1 description of
189-
impossible_function1
190-
example of
191-
impossible_function1
192-
help '%function_2';
193-
name description example
194-
impossible_function_2 description of
195-
impossible_function2
196-
example of
197-
impossible_function2
198-
help '%function_3';
199-
name description example
200-
impossible_function_3 description of
201-
impossible_function3
202-
example of
203-
impossible_function3
204-
help '%function_4';
205-
name description example
206-
impossible_function_4 description of
207-
impossible_function4
208-
example of
209-
impossible_function4
210-
help '%function_5';
211-
name description example
212-
impossible_function_1 description of
213-
impossible_function1
214-
example of
215-
impossible_function1
216-
help '%function_6';
217-
name is_it_category
218-
impossible_function_3 N
219-
impossible_function_4 N
220-
help '%function_7';
221-
name description example
222-
impossible_function_7 description of
223-
impossible_function5
224-
example of
225-
impossible_function7
226-
help '%category_2';
227-
source_category_name name is_it_category
228-
impossible_category_2 impossible_function_3 N
229-
impossible_category_2 impossible_function_4 N
230-
impossible_category_2 impossible_category_3 Y
231-
help 'impossible_function_1';
232-
name description example
233-
impossible_function_1 description of
234-
impossible_function1
235-
example of
236-
impossible_function1
237-
help 'impossible_category_1';
238-
source_category_name name is_it_category
239-
impossible_category_1 impossible_function_1 N
240-
impossible_category_1 impossible_function_2 N
241-
alter table mysql.help_relation engine=myisam;
242-
alter table mysql.help_keyword engine=myisam;
243-
alter table mysql.help_topic engine=myisam;
244-
alter table mysql.help_category engine=myisam;
245151
delete from mysql.help_topic where help_topic_id=@topic1_id;
246152
delete from mysql.help_topic where help_topic_id=@topic2_id;
247153
delete from mysql.help_topic where help_topic_id=@topic3_id;

‎mysql-test/t/alter_table.test‎

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1203,3 +1203,61 @@ rename table t2 to t1;
12031203
execute stmt1;
12041204
deallocate prepare stmt1;
12051205
drop table t2;
1206+
1207+
--echo #
1208+
--echo # Bug#11815557 60269: MYSQL SHOULD REJECT ATTEMPTS TO CREATE SYSTEM
1209+
--echo # TABLES IN INCORRECT ENGINE
1210+
--echo #
1211+
--echo # Note: This test assumes that only MyISAM supports system tables.
1212+
--echo # If other engines are made to support system tables,
1213+
--echo # then this test needs to be updated
1214+
--echo #
1215+
1216+
use mysql;
1217+
--error ER_UNSUPPORTED_ENGINE
1218+
ALTER TABLE db ENGINE=innodb;
1219+
--error ER_UNSUPPORTED_ENGINE
1220+
ALTER TABLE user ENGINE=memory;
1221+
--error ER_UNSUPPORTED_ENGINE
1222+
ALTER TABLE proc ENGINE=heap;
1223+
--error ER_UNSUPPORTED_ENGINE
1224+
ALTER TABLE func ENGINE=csv;
1225+
--error ER_UNSUPPORTED_ENGINE
1226+
ALTER TABLE event ENGINE=merge;
1227+
--error ER_UNSUPPORTED_ENGINE
1228+
ALTER TABLE servers ENGINE=innodb;
1229+
--error ER_UNSUPPORTED_ENGINE
1230+
ALTER TABLE procs_priv ENGINE=memory;
1231+
--error ER_UNSUPPORTED_ENGINE
1232+
ALTER TABLE tables_priv ENGINE=heap;
1233+
--error ER_UNSUPPORTED_ENGINE
1234+
ALTER TABLE columns_priv ENGINE=csv;
1235+
--error ER_UNSUPPORTED_ENGINE
1236+
ALTER TABLE time_zone ENGINE=merge;
1237+
--error ER_UNSUPPORTED_ENGINE
1238+
ALTER TABLE help_topic ENGINE=innodb;
1239+
1240+
--error ER_UNSUPPORTED_ENGINE
1241+
CREATE TABLE db (dummy int) ENGINE=innodb;
1242+
--error ER_UNSUPPORTED_ENGINE
1243+
CREATE TABLE user (dummy int) ENGINE=memory;
1244+
--error ER_UNSUPPORTED_ENGINE
1245+
CREATE TABLE proc (dummy int) ENGINE=heap;
1246+
--error ER_UNSUPPORTED_ENGINE
1247+
CREATE TABLE func (dummy int) ENGINE=csv;
1248+
--error ER_UNSUPPORTED_ENGINE
1249+
CREATE TABLE event (dummy int) ENGINE=merge;
1250+
--error ER_UNSUPPORTED_ENGINE
1251+
CREATE TABLE servers (dummy int) ENGINE=innodb;
1252+
--error ER_UNSUPPORTED_ENGINE
1253+
CREATE TABLE procs_priv (dummy int) ENGINE=memory;
1254+
--error ER_UNSUPPORTED_ENGINE
1255+
CREATE TABLE tables_priv (dummy int) ENGINE=heap;
1256+
--error ER_UNSUPPORTED_ENGINE
1257+
CREATE TABLE columns_priv (dummy int) ENGINE=csv;
1258+
--error ER_UNSUPPORTED_ENGINE
1259+
CREATE TABLE time_zone (dummy int) ENGINE=merge;
1260+
--error ER_UNSUPPORTED_ENGINE
1261+
CREATE TABLE help_topic (dummy int) ENGINE=innodb;
1262+
use test;
1263+
--echo # End of Bug#11815557

‎mysql-test/t/help.test‎

Lines changed: 0 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -63,38 +63,6 @@ help 'impossible_function_1';
6363
help 'impossible_category_1';
6464
##############
6565

66-
--disable_warnings
67-
alter table mysql.help_relation engine=innodb;
68-
alter table mysql.help_keyword engine=innodb;
69-
alter table mysql.help_topic engine=innodb;
70-
alter table mysql.help_category engine=innodb;
71-
--enable_warnings
72-
73-
##############
74-
help 'function_of_my_dream';
75-
help '%possible_f%';
76-
help 'impossible_func%';
77-
help 'impossible_category%';
78-
help 'impossible_%';
79-
80-
help '%function_1';
81-
help '%function_2';
82-
help '%function_3';
83-
help '%function_4';
84-
help '%function_5';
85-
help '%function_6';
86-
help '%function_7';
87-
88-
help '%category_2';
89-
help 'impossible_function_1';
90-
help 'impossible_category_1';
91-
##############
92-
93-
alter table mysql.help_relation engine=myisam;
94-
alter table mysql.help_keyword engine=myisam;
95-
alter table mysql.help_topic engine=myisam;
96-
alter table mysql.help_category engine=myisam;
97-
9866
delete from mysql.help_topic where help_topic_id=@topic1_id;
9967
delete from mysql.help_topic where help_topic_id=@topic2_id;
10068
delete from mysql.help_topic where help_topic_id=@topic3_id;

0 commit comments

Comments
 (0)