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 19:32	Modified:	26 Apr 2005 2:51
Reporter:	Anders Karlsson
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:
Assigned to:	Jim Winstead	Target Version:

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.