Error! Could not create tables (errno: 150)

[shefik]

When executing a new installation of PostCalendar 8.0.1, the following error may occur:

"Error! Could not create tables (SQLSTATE[HY000]: General error: 1005 Can't create table (errno: 150))."

This can be due to the database being set to InnoDB. However, you have some existing tables in MyISAM (or vice versa). When PostCalendar attempts to install, there are tables that set Constraints and Foreign Keys. It is required for a column to be the same column type as the table being referenced. Meaning, there can't be a mismatch of the engines between the new tables being created via PostCalendar and the existing tables that PostCalendar references with Constraints and Foreign Keys.

The PostCalendar installation process should either auto-correct these table discrepancies, or at the very least, return a more informative error message that describes the issue above (along with the names of the specific tables that need to be altered).

[craigh]

Thank you for digging in to this. I don't understand much about DB engines. Why would someone have mixed table types? Doesn't the core pick one or the other type? I don't know of any way that a module can control table type (or DB type for that matter). I do know that Doctrine has some issues with table types.

[shefik]

For example, you might have mixed table types if you want to use FULLTEXT with MyISAM and row-level locking with InnoDB. Although MySQL 5.6 now supports FULLTEXT with InnoDB. While it's true that the Zikula core currently only allows you to set one engine in config.php, a module can easily change the engine via an "ALTER TABLE" query with DBUtil:executeSQL(). That way, the database can run a mixture of engines.

Here is more info on MyISAM vs. InnoDB:

http://tag1consulting.com/mysql_engines_myisam_vs_innodb

Regarding Doctrine and table types, you can also easily extend support to allow types such as LONGBLOB and ENUM (or any other type you may need).