Home > mysql, Programming, yii > Fix mysql alter table error in agile yii book

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

Advertisements
Categories: mysql, Programming, yii
  1. Alex
    September 20, 2010 at 3:55 pm

    You saved me! Thanks!

    • ricoch3n
      September 20, 2010 at 6:10 pm

      Glad it helps.

  2. February 20, 2012 at 5:39 am

    Thanks a lot!

  3. February 20, 2012 at 5:45 am

    Another Fix for people using MySql is, dont use ‘ (quotes) use ` (backticks), using quotes in Mysql is treated as if you are altering into a string and not a table or column.

    • ricoch3n
      February 20, 2012 at 6:21 am

      Better yet, when it comes to db, table or column name, it’s totally fine to drop the back ticks all together. For example: select id from mydb.mytable where name like “a%”

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: