rails Column cannot be null:

Question

I noticed that my application was automatically setting all my processes to having been completed as soon as they were created. I looked through it, couldn't find why the date was being updated without being told to, but I found that one of my views was looking for a field that didn't exist. I created the 'complete' field, set it as a not null boolean and tried again.

I was getting it being set automatically to true again so I tried setting it as false explicitly in the create method, this still didn't work :S so I tried putting a hidden field into the form. I am now getting a "column cannot be null" error even though the value is being provided.

As you can see there is clearly a parameter for the complete value. What am I missing?

Error:

Mysql::Error: Column 'complete' cannot be null: INSERT INTO `decommissions` (`completed_at`, `keep_backups`, `services_stopped`, `updated_at`, `operating_system_id`, `comments`, `username`, `disposition`, `stakeholder_email`, `complete`, `alias`, `storage`, `model_id`, `contract_maintenance`, `created_at`) VALUES(NULL, 1, 1, '2010-10-18 00:32:37', 1, NULL, NULL, '', '[email protected]', NULL, 'test1', '', 1, '', '2010-10-18 00:32:37')

Parameters:

{"decommission"=>{"dns_items_attributes"=>{"0"=>{"ip"=>"131.181.185.111",
 "alias"=>"test",
 "retain"=>"1",
 "_destroy"=>""}},
 "keep_backups"=>"1",
 "services_stopped"=>"1",
 "operating_system_id"=>"1",
 "stakeholder_email"=>"[email protected]",
 "alias"=>"test1",
 "model_id"=>"1"},
 "commit"=>"Submit",
 "authenticity_token"=>"cMMf0zS/5jPExlXqVPaYVXndqPeVkm+OQ/WEPIYd2+g=",
 "disposition"=>"Dispose",
 "complete"=>"false",
 "storage"=>"Local",
 "contract_maintenance"=>"0"}

When I put the following in my create controller it renders 'true':

@decommission = Decommission.new(params[:decommission])
@decommission.complete = false
render :text => @decommission.complete

Answer 1

You've probably enforced a database constraint that the column cannot contain a NULL value via something in a migration like:

add_column :decommissions, :complete, :boolean, :null => false

You'll probably also want the default value of the column to be false rather than NULL

Personally I make a habit of whenever I add a boolean column to always set :default => false (or true), which helps to avoid logic bugs where you are checking for true or false in scopes etc, forgetting that the value can sometimes be null, if you don't set it right.

You can confirm is this is the issue from a mysql prompt:

mysql> show indexes from decommissions;

there should be an index with Non_unique set to 0 and Column_name of complete.

To fix this you can add a migration performing:

change_column_default(:decommissions, :complete, false)

Answer 2

@zetetic

I did have a method called complete, I renamed this but the problem was still occurring.

The problem occurred for all the following values: "disposition"=>"Dispose", "complete"=>"false", "storage"=>"Local", "contract_maintenance"=>"0"

These values were received using normal form selectors rather than rails ones. That was the only thing I could find that they had in common. I ended up running some experimental tests and found that the values were not being stored withing the decommission object, but rather in their own.

So in the controller I ran a few extra lines to set the values within the decommission object to equal the parameters associated with it:

@decommission.storage = params[:storage]
@decommission.contract_maintenance = params[:contract_maintenance]
@decommission.disposition = params[:disposition]
@decommission.complete = false