Hi
This looks like a bug in ActiveRecord.
I am messing with eager loading (on 12.1) and all of a sudden records
started disappearing from a table (and I am not doing anything delete
related)!!
The following line works OK:-
@todos = Todo.find(:all, :include => [ :whens],:order => "position
ASC")
The following line is mysteriously deleteing :where rows from the DB
after retrieving them so that the 2nd time I execute the action I get an
error cos there is no expected has_one data!!
@todos = Todo.find(:all, :include => [ :whens, :where],:order =>
"position ASC")
Here is my model:-
class Todo < ActiveRecord::Base
acts_as_list
has_one :where, :dependent => true
has_many :whens, :class_name => "Whens", :dependent => true
end
Below is the SQL from the log file showing first the OK line and then
the not working line.
***********************************************************
This is OK
Processing TodosController#list_edit (for 10.70.2.70 at Wed Apr 27
14:40:43 W. Europe Daylight Time 2005)
Parameters: {"action"=>"list_edit",
"controller"=>"todos",
"position"=>"0"}
[4;35mTodo Columns (0.010000) [0;37mSHOW FIELDS FROM todos
[4;33mWhens Columns (0.000000) [1;37mSHOW FIELDS FROM whens
[4;35mTodo Load Including Associations (0.000000) [0;37mSELECT
todos.position AS t0_r6, todos.due AS t0_r7, whens.todo_id AS t1_r0,
whens.id AS t1_r1, todos.repeat_id AS t0_r0, whens.category_id AS t1_r2,
todos.hour AS t0_r1, todos.priority AS t0_r2, todos.id AS t0_r3,
todos.description AS t0_r4, todos.kind_id AS t0_r5 FROM todos LEFT OUTER
JOIN whens ON whens.todo_id = todos.id ORDER BY position ASC
[4;33mCategory Load (0.000000) [1;37mSELECT * FROM categories
WHERE ckey = ''where''
[4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories
WHERE ckey = ''when''
[4;33mTodo Columns (0.000000) [1;37mSHOW FIELDS FROM todos
Rendering todos/list_edit within layouts/standard
[4;35mWhere Load (0.000000) [0;37mSELECT * FROM wheres WHERE
wheres.todo_id = 416 LIMIT 1
[4;33mWhere Columns (0.000000) [1;37mSHOW FIELDS FROM wheres
[4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories
WHERE categories.id = 5 LIMIT 1
[4;33mCategory Columns (0.010000) [1;37mSHOW FIELDS FROM
categories
[4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories
WHERE categories.id = 11 LIMIT 1
Rendering layouts/standard (200 OK)
Completed in 0.14900 (6 reqs/sec) | Rendering: 0.05900 (39%) | DB:
0.02000 (13%)
***************************************************
This shows Rails deleting a :where record and then trying to update it.
Fishy.
Processing TodosController#list_edit (for 10.70.2.70 at Wed Apr 27
14:41:41 W. Europe Daylight Time 2005)
Parameters: {"action"=>"list_edit",
"controller"=>"todos",
"position"=>"0"}
[4;33mTodo Columns (0.000000) [1;37mSHOW FIELDS FROM todos
[4;35mWhens Columns (0.000000) [0;37mSHOW FIELDS FROM whens
[4;33mWhere Columns (0.010000) [1;37mSHOW FIELDS FROM wheres
[4;35mTodo Load Including Associations (0.010000) [0;37mSELECT
wheres.id AS t2_r1, todos.position AS t0_r6, wheres.category_id AS
t2_r2, todos.due AS t0_r7, whens.todo_id AS t1_r0, whens.id AS t1_r1,
todos.repeat_id AS t0_r0, whens.category_id AS t1_r2, todos.hour AS
t0_r1, todos.priority AS t0_r2, todos.id AS t0_r3, wheres.todo_id AS
t2_r0, todos.description AS t0_r4, todos.kind_id AS t0_r5 FROM todos
LEFT OUTER JOIN whens ON whens.todo_id = todos.id LEFT OUTER JOIN wheres
ON wheres.todo_id = todos.id ORDER BY position ASC
[4;33mWhere Load (0.010000) [1;37mSELECT * FROM wheres WHERE
wheres.todo_id = 416 LIMIT 1
[4;35mSQL (0.000000) [0;37mBEGIN
[4;33mWhere Destroy (0.000000) [1;37mDELETE FROM wheres WHERE
id = 241
[4;35mSQL (0.060000) [0;37mCOMMIT
[4;33mSQL (0.000000) [1;37mBEGIN
[4;35mWhere Update (0.000000) [0;37mUPDATE wheres SET `todo_id`
= 416, `category_id` = 5 WHERE id = 241
[4;33mSQL (0.000000) [1;37mCOMMIT
[4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories
WHERE ckey = ''where''
[4;33mCategory Load (0.000000) [1;37mSELECT * FROM categories
WHERE ckey = ''when''
[4;35mTodo Columns (0.000000) [0;37mSHOW FIELDS FROM todos
Rendering todos/list_edit within layouts/standard
[4;33mCategory Load (0.000000) [1;37mSELECT * FROM categories
WHERE categories.id = 5 LIMIT 1
[4;35mCategory Columns (0.000000) [0;37mSHOW FIELDS FROM
categories
[4;33mWhens Load (0.000000) [1;37mSELECT * FROM whens WHERE
whens.todo_id = 416
[4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories
WHERE categories.id = 11 LIMIT 1
Rendering layouts/standard (200 OK)
Completed in 0.64000 (1 reqs/sec) | Rendering: 0.07000 (10%) | DB:
0.09000 (14%)
I wonder if it''s because your table is called "where"--that sounds very dangerous, since it''s a SQL keyword. Dave On 4/27/05 7:10 AM, "Cutting Peter" <Peter.Cutting-CwpgxV1jTAxWk0Htik3J/w@public.gmane.org> wrote:> Hi > This looks like a bug in ActiveRecord. > > I am messing with eager loading (on 12.1) and all of a sudden records > started disappearing from a table (and I am not doing anything delete > related)!! > > The following line works OK:- > > @todos = Todo.find(:all, :include => [ :whens],:order => "position ASC") > > The following line is mysteriously deleteing :where rows from the DB > after retrieving them so that the 2nd time I execute the action I get an > error cos there is no expected has_one data!! > > @todos = Todo.find(:all, :include => [ :whens, :where],:order => > "position ASC") > > Here is my model:- > > class Todo < ActiveRecord::Base > acts_as_list > has_one :where, :dependent => true > has_many :whens, :class_name => "Whens", :dependent => true > end > > Below is the SQL from the log file showing first the OK line and then > the not working line. > > *********************************************************** > This is OK > > Processing TodosController#list_edit (for 10.70.2.70 at Wed Apr 27 > 14:40:43 W. Europe Daylight Time 2005) > Parameters: {"action"=>"list_edit", "controller"=>"todos", > "position"=>"0"} > [4;35mTodo Columns (0.010000) [0;37mSHOW FIELDS FROM todos > [4;33mWhens Columns (0.000000) [1;37mSHOW FIELDS FROM whens > [4;35mTodo Load Including Associations (0.000000) [0;37mSELECT > todos.position AS t0_r6, todos.due AS t0_r7, whens.todo_id AS t1_r0, > whens.id AS t1_r1, todos.repeat_id AS t0_r0, whens.category_id AS t1_r2, > todos.hour AS t0_r1, todos.priority AS t0_r2, todos.id AS t0_r3, > todos.description AS t0_r4, todos.kind_id AS t0_r5 FROM todos LEFT OUTER > JOIN whens ON whens.todo_id = todos.id ORDER BY position ASC > [4;33mCategory Load (0.000000) [1;37mSELECT * FROM categories > WHERE ckey = ''where'' > [4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories > WHERE ckey = ''when'' > [4;33mTodo Columns (0.000000) [1;37mSHOW FIELDS FROM todos > Rendering todos/list_edit within layouts/standard > [4;35mWhere Load (0.000000) [0;37mSELECT * FROM wheres WHERE > wheres.todo_id = 416 LIMIT 1 > [4;33mWhere Columns (0.000000) [1;37mSHOW FIELDS FROM wheres > [4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories > WHERE categories.id = 5 LIMIT 1 > [4;33mCategory Columns (0.010000) [1;37mSHOW FIELDS FROM > categories > [4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories > WHERE categories.id = 11 LIMIT 1 > Rendering layouts/standard (200 OK) > Completed in 0.14900 (6 reqs/sec) | Rendering: 0.05900 (39%) | DB: > 0.02000 (13%) > > *************************************************** > This shows Rails deleting a :where record and then trying to update it. > Fishy. > > > Processing TodosController#list_edit (for 10.70.2.70 at Wed Apr 27 > 14:41:41 W. Europe Daylight Time 2005) > Parameters: {"action"=>"list_edit", "controller"=>"todos", > "position"=>"0"} > [4;33mTodo Columns (0.000000) [1;37mSHOW FIELDS FROM todos > [4;35mWhens Columns (0.000000) [0;37mSHOW FIELDS FROM whens > [4;33mWhere Columns (0.010000) [1;37mSHOW FIELDS FROM wheres > [4;35mTodo Load Including Associations (0.010000) [0;37mSELECT > wheres.id AS t2_r1, todos.position AS t0_r6, wheres.category_id AS > t2_r2, todos.due AS t0_r7, whens.todo_id AS t1_r0, whens.id AS t1_r1, > todos.repeat_id AS t0_r0, whens.category_id AS t1_r2, todos.hour AS > t0_r1, todos.priority AS t0_r2, todos.id AS t0_r3, wheres.todo_id AS > t2_r0, todos.description AS t0_r4, todos.kind_id AS t0_r5 FROM todos > LEFT OUTER JOIN whens ON whens.todo_id = todos.id LEFT OUTER JOIN wheres > ON wheres.todo_id = todos.id ORDER BY position ASC > [4;33mWhere Load (0.010000) [1;37mSELECT * FROM wheres WHERE > wheres.todo_id = 416 LIMIT 1 > [4;35mSQL (0.000000) [0;37mBEGIN > [4;33mWhere Destroy (0.000000) [1;37mDELETE FROM wheres WHERE > id = 241 > [4;35mSQL (0.060000) [0;37mCOMMIT > [4;33mSQL (0.000000) [1;37mBEGIN > [4;35mWhere Update (0.000000) [0;37mUPDATE wheres SET `todo_id` > = 416, `category_id` = 5 WHERE id = 241 > [4;33mSQL (0.000000) [1;37mCOMMIT > [4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories > WHERE ckey = ''where'' > [4;33mCategory Load (0.000000) [1;37mSELECT * FROM categories > WHERE ckey = ''when'' > [4;35mTodo Columns (0.000000) [0;37mSHOW FIELDS FROM todos > Rendering todos/list_edit within layouts/standard > [4;33mCategory Load (0.000000) [1;37mSELECT * FROM categories > WHERE categories.id = 5 LIMIT 1 > [4;35mCategory Columns (0.000000) [0;37mSHOW FIELDS FROM > categories > [4;33mWhens Load (0.000000) [1;37mSELECT * FROM whens WHERE > whens.todo_id = 416 > [4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories > WHERE categories.id = 11 LIMIT 1 > Rendering layouts/standard (200 OK) > Completed in 0.64000 (1 reqs/sec) | Rendering: 0.07000 (10%) | DB: > 0.09000 (14%) > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
No, I have already seen this problem in 12.0 but I thought it would be corrected in 12.1. When using the has_one relationship, rails first loads the association, the reissues a SELECT query (it should not). This results in the first loaded object to be considered as detached from the association. Rails issues a DELETE order (since the association is :dependant => true). The second loaded object (which is in fact the same database row as the first object) is then considered as attached to the association and an UPDATE order is issued. Problem: it has already been deleted. Note 1: the second SELECT statement should not be issued, else what is the point of eager loading ? Note 2: if the association is :dependant => false and the foreign key is nullifiable, this problem can pass unseen because the foreign key is first set to NULL (instead of the whole row deleted) then updated with the parent object id, returning it to its original state. It seems to be a real bug but I thought it was corrected because the release notes of v12.1 listed "Fixed frivilous database queries being triggered with eager loading on empty associations and other things". Regards Julien Faissolle -----Original Message----- From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Dave Ringoen Sent: Wednesday, April 27, 2005 3:23 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: Re: [Rails] Eager load mysteriously deletes records I wonder if it''s because your table is called "where"--that sounds very dangerous, since it''s a SQL keyword. Dave On 4/27/05 7:10 AM, "Cutting Peter" <Peter.Cutting-CwpgxV1jTAxWk0Htik3J/w@public.gmane.org> wrote:> Hi > This looks like a bug in ActiveRecord. > > I am messing with eager loading (on 12.1) and all of a sudden records > started disappearing from a table (and I am not doing anything delete > related)!! > > The following line works OK:- > > @todos = Todo.find(:all, :include => [ :whens],:order => "position > ASC") > > The following line is mysteriously deleteing :where rows from the DB > after retrieving them so that the 2nd time I execute the action I get > an error cos there is no expected has_one data!! > > @todos = Todo.find(:all, :include => [ :whens, :where],:order => > "position ASC") > > Here is my model:- > > class Todo < ActiveRecord::Base > acts_as_list > has_one :where, :dependent => true > has_many :whens, :class_name => "Whens", :dependent => true end > > Below is the SQL from the log file showing first the OK line and then > the not working line. > > *********************************************************** > This is OK > > Processing TodosController#list_edit (for 10.70.2.70 at Wed Apr 27 > 14:40:43 W. Europe Daylight Time 2005) > Parameters: {"action"=>"list_edit", "controller"=>"todos", > "position"=>"0"} > [4;35mTodo Columns (0.010000) [0;37mSHOW FIELDS FROM todos > [4;33mWhens Columns (0.000000) [1;37mSHOW FIELDS FROMwhens> [4;35mTodo Load Including Associations (0.000000)[0;37mSELECT> todos.position AS t0_r6, todos.due AS t0_r7, whens.todo_id AS t1_r0, > whens.id AS t1_r1, todos.repeat_id AS t0_r0, whens.category_id AS > t1_r2, todos.hour AS t0_r1, todos.priority AS t0_r2, todos.id AS > t0_r3, todos.description AS t0_r4, todos.kind_id AS t0_r5 FROM todos > LEFT OUTER JOIN whens ON whens.todo_id = todos.id ORDER BY positionASC> [4;33mCategory Load (0.000000) [1;37mSELECT * FROM categories > WHERE ckey = ''where'' > [4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories > WHERE ckey = ''when'' > [4;33mTodo Columns (0.000000) [1;37mSHOW FIELDS FROM todos > Rendering todos/list_edit within layouts/standard > [4;35mWhere Load (0.000000) [0;37mSELECT * FROM wheres WHERE > wheres.todo_id = 416 LIMIT 1 > [4;33mWhere Columns (0.000000) [1;37mSHOW FIELDS FROMwheres> [4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories > WHERE categories.id = 5 LIMIT 1 > [4;33mCategory Columns (0.010000) [1;37mSHOW FIELDS FROM > categories > [4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories > WHERE categories.id = 11 LIMIT 1 > Rendering layouts/standard (200 OK) > Completed in 0.14900 (6 reqs/sec) | Rendering: 0.05900 (39%) | DB: > 0.02000 (13%) > > *************************************************** > This shows Rails deleting a :where record and then trying to update > it. Fishy. > > > Processing TodosController#list_edit (for 10.70.2.70 at Wed Apr 27 > 14:41:41 W. Europe Daylight Time 2005) > Parameters: {"action"=>"list_edit", "controller"=>"todos", > "position"=>"0"} > [4;33mTodo Columns (0.000000) [1;37mSHOW FIELDS FROM todos > [4;35mWhens Columns (0.000000) [0;37mSHOW FIELDS FROMwhens> [4;33mWhere Columns (0.010000) [1;37mSHOW FIELDS FROMwheres> [4;35mTodo Load Including Associations (0.010000)[0;37mSELECT> wheres.id AS t2_r1, todos.position AS t0_r6, wheres.category_id AS > t2_r2, todos.due AS t0_r7, whens.todo_id AS t1_r0, whens.id AS t1_r1, > todos.repeat_id AS t0_r0, whens.category_id AS t1_r2, todos.hour AS > t0_r1, todos.priority AS t0_r2, todos.id AS t0_r3, wheres.todo_id AS > t2_r0, todos.description AS t0_r4, todos.kind_id AS t0_r5 FROM todos > LEFT OUTER JOIN whens ON whens.todo_id = todos.id LEFT OUTER JOIN > wheres ON wheres.todo_id = todos.id ORDER BY position ASC > [4;33mWhere Load (0.010000) [1;37mSELECT * FROM wheres WHERE > wheres.todo_id = 416 LIMIT 1 > [4;35mSQL (0.000000) [0;37mBEGIN > [4;33mWhere Destroy (0.000000) [1;37mDELETE FROM wheres WHERE > id = 241 > [4;35mSQL (0.060000) [0;37mCOMMIT > [4;33mSQL (0.000000) [1;37mBEGIN > [4;35mWhere Update (0.000000) [0;37mUPDATE wheres SET`todo_id`> = 416, `category_id` = 5 WHERE id = 241 > [4;33mSQL (0.000000) [1;37mCOMMIT > [4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories > WHERE ckey = ''where'' > [4;33mCategory Load (0.000000) [1;37mSELECT * FROM categories > WHERE ckey = ''when'' > [4;35mTodo Columns (0.000000) [0;37mSHOW FIELDS FROM todos > Rendering todos/list_edit within layouts/standard > [4;33mCategory Load (0.000000) [1;37mSELECT * FROM categories > WHERE categories.id = 5 LIMIT 1 > [4;35mCategory Columns (0.000000) [0;37mSHOW FIELDS FROM > categories > [4;33mWhens Load (0.000000) [1;37mSELECT * FROM whens WHERE > whens.todo_id = 416 > [4;35mCategory Load (0.000000) [0;37mSELECT * FROM categories > WHERE categories.id = 11 LIMIT 1 > Rendering layouts/standard (200 OK) > Completed in 0.64000 (1 reqs/sec) | Rendering: 0.07000 (10%) | DB: > 0.09000 (14%) > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
I changed where to location and it did not help Peter
On 4/28/05, Cutting Peter <Peter.Cutting-CwpgxV1jTAxWk0Htik3J/w@public.gmane.org> wrote:> I changed where to location and it did not helpPlease file a ticket about this at dev.rubyonrails.org, this will ensure it gets looked at by the right people.> Peter > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz