Fix mysql alter table error in agile yii book
This is the second post regarding my learning yii through this book: Agile Web Application Development with Yii1.1 and PHP5. The previous post regarding a gii 403 error can be found here.
The code on page 96, Chapter 6 shows the mandatory steps to create the relationships between the tables and the one didn’t get run successfully is this:
ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_project' FOREIGN KEY ('project_id') REFERENCES 'tbl_project' ('id') ON DELETE CASCADE ON UPDATE RESTRICT;
Changing ‘ to ` (or just remove the single quotation marks) only solves the syntax error problem, the real trouble is this:
ERROR 1005 (HY000): Can't create table './trackstar/#sql-xxxx_xx.frm' (errno: 150)
The cause turns out to be, the table tbl_project was created using a non-InnoDB database engine, which is the engine type for the rest of the tables in the same database. The reason for that is, if we look at the code on page 63, tbl_project is created using the default database engine – MyISAM – which is the case on my ubuntu 9.04 and Centos 5.5. So the fix is pretty obvious – to change the engine type of tbl_project from MyISAM to InnoDB:
alter table tbl_project ENGINE=InnoDB;
After that, running the alter table statement returns OK.
[ added moments later ]
If Issue model is created before the above problem was solved, it needs to be re-created:
1) delete projected/models/Issue.php
2) go to index.php?r=gii/model and re-create model Issue