MySQL Bugs: #60269: mysql should reject attempts to create system tables in incorrect engine

Bug #60269	mysql should reject attempts to create system tables in incorrect engine
Submitted:	27 Feb 2011 9:22	Modified:	3 May 2012 19:40
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.55,5.5.9, 5.5.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
currently innodb disallows the db,user,host tables from being altered to innodb engine.  please disallow all the tables in the mysql database, as problems can still be seen such as debug build crashed like this on next startup:

110227 11:13:53 InnoDB: 1.1.5 started; log sequence number 2699693
Assertion failed: thd->transaction.stmt.is_empty(), file ..\..\mysqlcom-pro-5.5.9\sql\sql_base.cc, line 9097

mysqld-debug.exe!my_sigabrt_handler()[my_thr_init.c:520]
mysqld-debug.exe!raise()[winsig.c:590]
mysqld-debug.exe!abort()[abort.c:71]
mysqld-debug.exe!_wassert()[assert.c:163]
mysqld-debug.exe!close_mysql_tables()[sql_base.cc:9097]
mysqld-debug.exe!my_tz_init()[tztime.cc:1780]
mysqld-debug.exe!win_main()[mysqld.cc:4463]
mysqld-debug.exe!mysql_service()[mysqld.cc:4625]
mysqld-debug.exe!mysqld_main()[mysqld.cc:4811]
mysqld-debug.exe!main()[main.cc:24]
mysqld-debug.exe!__tmainCRTStartup()[crt0.c:266]
mysqld-debug.exe!mainCRTStartup()[crt0.c:182]
kernel32.dll!BaseThreadInitThunk()
ntdll.dll!RtlInitializeExceptionChain()

How to repeat:
alter table mysql.columns_priv engine=innodb;
alter table mysql.db engine=innodb;
alter table mysql.event engine=innodb;
alter table mysql.func engine=innodb;
alter table mysql.general_log engine=innodb;
alter table mysql.help_category engine=innodb;
alter table mysql.help_keyword engine=innodb;
alter table mysql.help_relation engine=innodb;
alter table mysql.help_topic engine=innodb;
alter table mysql.host engine=innodb;
alter table mysql.ndb_binlog_index engine=innodb;
alter table mysql.plugin engine=innodb;
alter table mysql.proc engine=innodb;
alter table mysql.procs_priv engine=innodb;
alter table mysql.proxies_priv engine=innodb;
alter table mysql.servers engine=innodb;
alter table mysql.slow_log engine=innodb;
alter table mysql.tables_priv engine=innodb;
alter table mysql.time_zone engine=innodb;
alter table mysql.time_zone_leap_second engine=innodb;
alter table mysql.time_zone_name engine=innodb;
alter table mysql.time_zone_transition engine=innodb;
alter table mysql.time_zone_transition_type engine=innodb;
alter table mysql.user engine=innodb;
show table status from mysql where engine='innodb';

Suggested fix:
no dba should be converting these tables to innodb, but often scripts don't know this and forget to exclude them during some mass conversion process.

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html
"Warning
Do not convert MySQL system tables in the mysql database from MyISAM to InnoDB tables! This is an unsupported operation. If you do this, MySQL does not restart until you restore the old system tables from a backup or re-generate them with the mysql_install_db script.
"

For the record, 5.1.55 cannot startup and crashes unless you give --skip-grant-tables option:

mysqld.exe!ha_resolve_by_name()[handler.cc:135]
mysqld.exe!open_binary_frm()[table.cc:896]
mysqld.exe!open_table_def()[table.cc:643]
mysqld.exe!get_table_share()[sql_base.cc:379]
mysqld.exe!get_table_share_with_create()[sql_base.cc:458]
mysqld.exe!open_unireg_entry()[sql_base.cc:3875]
mysqld.exe!open_table()[sql_base.cc:2931]
mysqld.exe!open_tables()[sql_base.cc:4624]
mysqld.exe!open_and_lock_tables_derived()[sql_base.cc:5042]
mysqld.exe!plugin_load()[sql_plugin.cc:1417]
mysqld.exe!plugin_init()[sql_plugin.cc:1249]
mysqld.exe!init_server_components()[mysqld.cc:3997]
mysqld.exe!win_main()[mysqld.cc:4466]
mysqld.exe!mysql_service()[mysqld.cc:4644]
mysqld.exe!main()[mysqld.cc:4830]
mysqld.exe!__tmainCRTStartup()[crt0.c:327]

It's not really up to InnoDB. It's the server that should reject anything but MyISAM for system tables.

Davi, I agree with that of course. I just chose innodb because they already do it :)

"Version: '5.5.9'  socket: ''  port: 3306  MySQL Community Server (GPL)
InnoDB: Error: trying to create a MySQL system table mysql/db of type InnoDB.
InnoDB: MySQL system tables must be of the MyISAM type!"

row_create_table_for_mysql() calls row_mysql_is_system_table() which only checks:

return(0 == strcmp(name + 6, "host")
       || 0 == strcmp(name + 6, "user")
       || 0 == strcmp(name + 6, "db"));

That was pretty clear in the description ("currently innodb") and is also a part of why this is buggy.

Noted in 5.5.24, 5.5.6 changelogs.

The server did not reject attempts to modify tables in the mysql
database to non-MyISAM storage engines.