MySQL Bugs: #9496: Table mysql.proc created by mysql_create_system

Bug #9496	Table mysql.proc created by mysql_create_system_tables lacks ENGINE-specificati
Submitted:	30 Mar 2005 17:32	Modified:	26 Apr 2005 0:51
Reporter:	Anders Karlsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
When running mysql_install_db with 5.0, the table "proc" that keeps track of stored procedures is created without specifying ENGINE=MyISAM (This is in mysql_create_system_tables). This means that if my.cnf contains default-table-type=InnoDB for example, the proc table will be created as an InnoDB table, and procedurs cannot be created, as PROC must be a MyISAM table. The error one get is that the file proc.ibd cannot be created.
The same is of course true if default-storage-engine is set to InnoDB.

How to repeat:
- Install 5.0 from scratch.
- Set default-table-type=InnoDB in my.cnf
- Run mysql_insatll_db
- Create a simple procedure. An error will be returned.

Suggested fix:
ENGINE=MyISAM should be added to the part of mysql_create_system_tables where the PROC table is created.
A workaround is to either remove default-table-type from my.cnf when running mysql_install_db, or to run mysql_install_db like this:
mysql_install_db --default-table-type=MyISAM

mysql_fix_privilege_tables.sql also needs this fix, as this will create the proc table if it doesn't exist, again without specifying an ENGINE.

This practically to the fact we're fixing symptoms rather than problem by trying to block these tables to be Innodb or other storage engine.     Besides this we also should ensure one can't alter tables to 
Innodb (and other storage engines) .... each time new tables are added to mysql database.

Much better solution would be finally fixing so these tables can be Innodb.   This is needed anyway to archive ACID behavior, as currently if  will crash your system at unlucky time when adding user or storage procedure you may get table corrupted with system failing to start or other effects. 

The NDB Cluster is other example when you should have system tables in different storage engine.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24064

Fixed in 5.0.4.

Noted in 5.0.4 changelog.