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

[30 Mar 2005 17:32] Anders Karlsson
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
[30 Mar 2005 17:39] Anders Karlsson
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.
[31 Mar 2005 16:41] Peter Zaitsev
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.
[15 Apr 2005 20:55] Bugs System
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
[25 Apr 2005 22:40] Jim Winstead
Fixed in 5.0.4.
[26 Apr 2005 0:51] Paul Dubois
Noted in 5.0.4 changelog.