Thursday, November 11, 2010

Yii CRUD: "Please fix the following input errors: {MODEL} cannot be blank"

Today I generated a CRUD scaffold for my 'User' model using Yii Framework's GUI code generation tool, Gii. When I attempted to insert some data to my User table with the Gii-generated CRUD scaffolding I got a rather unhelpful error message:

Please fix the following input errors:
* User cannot be blank

It didn't name a specific property/column so I didn't immediately know what the problem was. When I took a look at the 'User' model I found that the 'user_id' column's label alias was 'User'. That prompted me to look at my 'User' table in the database where I discovered that I forgot to set the 'user_id' primary key column to AUTOINCREMENT. I set that column to AUTOINCREMENT but there were still issues in my 'User' model.

The Yii code generator analyzes the database table metadata during model creation, and because my primary key column was not set to AUTOINCREMENT when I generated it the first time it figured this was a field that accepts user input and must therefore be validated. That's why my 'User' model had my primary key column set to 'required' in its 'rules' function. This validation rule is why I got an unhelpful error message; if it wasn't validating the presence of the auto-incrementing primary key prior to record insertion (which it should not be), it would have gone ahead and attempted to insert the record and I would have received a more useful error from MySQL like the following:

SQLSTATE[HY000]: General error: 1364 Field 'user_id' doesn't have a default value

After I changed the 'user_id' column to AUTOINCREMENT in the database I then used Gii again to regenerate the 'User' model and this time it did not create a 'required' validation rule for the 'user_id' column as expected.

Sunday, November 7, 2010

MySQL ERROR 1061: Duplicate key name 'my_tbl_fk'

I was creating a foreign key in MySQL Workbench 5.2 OSS when I got the following error:

ERROR 1061: Duplicate key name 'tbl1_tbl2_fk'

I checked the "Foreign Keys" tab for the table to which I was adding the FK and there was no key with that name. In fact, there were no foreign keys with that name anywhere.

However, when I checked the indexes on that table I found the problem: an index with the same name as the foreign key I was creating. I dropped that index and was able to create the foreign key without any issues.

What happened was I had previously created a foreign key with that name and then dropped it on the 'Foreign Keys' tab. The index backing this key was not dropped when I did that, so when I attempted to recreate the foreign key with that name it tried to duplicate the index.