I just picked up Rails, am kicking the tires, and not being able to get it
do some basic stuff.
Can somebody help an utter newbie? I can''t get MySQL to commit
transactions where I''m trying to put in references to other db tables.
I''m getting this error message:
''Packets out of order'' error was received from the database.
The MySQL server is version 4.0.25, and Rails is able to make the
connection on other tables with an identical configuration.
Here''s what was coming from the template:
--
<p><label for="txatom_trnsaction_id">Transaction
ID</label>
<%= text_field ''txatom'', ''trnsaction_id'',
''size'' => 9 %>
<label for="txatom_account_id">Acct. ID</label>
<%= text_field ''txatom'', ''account_id'',
''size'' => 9 %>
<label for="txatom_cr">Dr/Cr</label>
<%= text_field ''txatom'', ''cr'',
''size'' => 1 %>
<label for="txatom_amount">Amount</label>
<%= text_field ''txatom'', ''amount''
%></p>
--
Here''s where it crapped it in the controller:
---
@txatom = Txatom.new(@params[:txatom])
---
Here''s what the error message is telling me:
----
Request
Parameters: {"commit"=>"Create",
"txatom"=>{"trnsaction_id"=>"1",
"account_id"=>"100", "cr"=>"0",
"amount"=>"200.00"}}
----
Here''s the table in question:
---
CREATE TABLE txatoms (
id int(11) unsigned NOT NULL auto_increment,
trnsaction_id int(11) unsigned default NULL,
account_id int(11) unsigned default NULL,
cr tinyint(1) unsigned default ''0'',
amount decimal(11,2) unsigned default NULL,
created_on timestamp(14) NOT NULL,
updated_on timestamp(14) NOT NULL default ''00000000000000'',
PRIMARY KEY (id)
) TYPE=MyISAM;
---
and here''s the model:
---
class Txatom < ActiveRecord::Base
belongs_to :trnsaction
validates_associated :trnsaction
belongs_to :account
validates_associated :account
validates_numericality_of :trnsaction_id
validates_numericality_of :account_id
validates_numericality_of :cr
validates_numericality_of :amount
end
---
I''d appreciate it if someone could tell me what I''m doing
wrong.
Christopher Singley wrote:> I''m getting this error message: > ''Packets out of order'' error was received from the database. > > The MySQL server is version 4.0.25, and Rails is able to make the > connection on other tables with an identical configuration.A Web search turned up this: http://daryl.learnhouston.com/?p=198 -- We develop, watch us RoR, in numbers too big to ignore.
Thank you for helping, but as I said, I''m not using MySQL 4.1, so I don''t think the passwords are the source of the problem. Rails is able to make db connection to MySQL, just not on this table. On Thu, 25 Aug 2005 00:08:25 -0500, Mark Reginald James <mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> wrote:> Christopher Singley wrote: > >> I''m getting this error message: >> ''Packets out of order'' error was received from the database. >> The MySQL server is version 4.0.25, and Rails is able to make the >> connection on other tables with an identical configuration. > > A Web search turned up this: http://daryl.learnhouston.com/?p=198 >
Christopher Singley wrote:> Thank you for helping, but as I said, I''m not using MySQL 4.1, so I > don''t think the passwords are the source of the problem. Rails is able > to make db connection to MySQL, just not on this table.OK. You say you''re trying to commit transactions, but they''re only supported for InnoDB tables, not MyISAM tables. -- We develop, watch us RoR, in numbers too big to ignore.
Thanks again for helping. In config/database.yml, I set ''adapter: mysql''. I have no idea why Rails is spitting out error messages about committing my data, since the table in question is a MyISAM table. What can I do to troubleshoot this problem? I am baffled. I''m sure there''s something obvious that I''m overlooking... TIA On Thu, 25 Aug 2005 01:08:33 -0500, Mark Reginald James <mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> wrote:> You say you''re trying to commit transactions, but they''re only > supported for InnoDB tables, not MyISAM tables.
Are you putting foreign key constraints? There''re a few restrictions (like it has to be InnoDB) that you must adhere to: http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html On 8/25/05, Christopher Singley <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote:> Thanks again for helping. > > In config/database.yml, I set ''adapter: mysql''. I have no idea why Rails > is spitting out > error messages about committing my data, since the table in question is a > MyISAM table. > > What can I do to troubleshoot this problem? I am baffled. > I''m sure there''s something obvious that I''m overlooking... > > TIA > > On Thu, 25 Aug 2005 01:08:33 -0500, Mark Reginald James > <mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> wrote: > > > You say you''re trying to commit transactions, but they''re only > > supported for InnoDB tables, not MyISAM tables. > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
MySQL ''Packets out of order'' can result from a race condition between two or more connections. One possibility is that two connections are allocated from a pool to update the same table and the responses are jumbled. This hypothesis can be tested by setting the connection pool size to 1. I don''t know how to do this in Ruby; perhaps somebody else can enlighten us. If a race condition is indeed the proximate problem, then the root cause is a Ruby multithreading conflict. On Thu, 2005-08-25 at 09:43 -0500, Christopher Singley wrote:> Thanks again for helping. > > In config/database.yml, I set ''adapter: mysql''. I have no idea why Rails > is spitting out > error messages about committing my data, since the table in question is a > MyISAM table. > > What can I do to troubleshoot this problem? I am baffled. > I''m sure there''s something obvious that I''m overlooking...
I am not putting in foreign key constraints. On Thu, 25 Aug 2005 11:09:06 -0500, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Are you putting foreign key constraints? There''re a few restrictions > (like it has to be InnoDB) that you must adhere to: > http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html > > On 8/25/05, Christopher Singley <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote: >> Thanks again for helping. >> >> In config/database.yml, I set ''adapter: mysql''. I have no idea why >> Rails >> is spitting out >> error messages about committing my data, since the table in question is >> a >> MyISAM table. >> >> What can I do to troubleshoot this problem? I am baffled. >> I''m sure there''s something obvious that I''m overlooking... >> >> TIA >> >> On Thu, 25 Aug 2005 01:08:33 -0500, Mark Reginald James >> <mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> wrote: >> >> > You say you''re trying to commit transactions, but they''re only >> > supported for InnoDB tables, not MyISAM tables. >> >> >> >> _______________________________________________ >> 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''m quite wet behind the ears with databases, so I can''t give a meaningful answer. However, the controller is dying on this line: --- @txatom = Txatom.new(@params[:txatom]) --- which seems like it ought to involve only one condition, no? On Thu, 25 Aug 2005 11:48:21 -0500, Fred Loney <loney-snu3QbYPQTrUEDaH6ef/NA@public.gmane.org> wrote:> MySQL ''Packets out of order'' can result from a race condition between > two or more connections. One possibility is that two connections are > allocated from a pool to update the same table and the responses are > jumbled. > > This hypothesis can be tested by setting the connection pool size to 1. > I don''t know how to do this in Ruby; perhaps somebody else can enlighten > us. If a race condition is indeed the proximate problem, then the root > cause is a Ruby multithreading conflict. > > On Thu, 2005-08-25 at 09:43 -0500, Christopher Singley wrote: >> Thanks again for helping. >> >> In config/database.yml, I set ''adapter: mysql''. I have no idea why >> Rails >> is spitting out >> error messages about committing my data, since the table in question is >> a >> MyISAM table. >> >> What can I do to troubleshoot this problem? I am baffled. >> I''m sure there''s something obvious that I''m overlooking... > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
I am not putting in foreign key constraints. On Thu, 25 Aug 2005 11:09:06 -0500, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Are you putting foreign key constraints? There''re a few restrictions > (like it has to be InnoDB) that you must adhere to: > http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html > > On 8/25/05, Christopher Singley <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote: >> Thanks again for helping. >> >> In config/database.yml, I set ''adapter: mysql''. I have no idea why >> Rails >> is spitting out >> error messages about committing my data, since the table in question is >> a >> MyISAM table. >> >> What can I do to troubleshoot this problem? I am baffled. >> I''m sure there''s something obvious that I''m overlooking... >> >> TIA >> >> On Thu, 25 Aug 2005 01:08:33 -0500, Mark Reginald James >> <mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> wrote: >> >> > You say you''re trying to commit transactions, but they''re only >> > supported for InnoDB tables, not MyISAM tables. >> >> >> >> _______________________________________________ >> 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
Thanks to those who''ve responded, but I''ve hit a blank wall. I use identical instructions & db table, but remove the ''trnsaction_id'' and ''account_id'' fields & references thereto in code, and MySQL CRUDs happily. As soon as I try writing some ''column_id'' in the fashion I''m attempting, I get these "Packets out of order" complaints. Is there another way to do it? What do other people put in to write a row with references to other column IDs? On Wed, 24 Aug 2005 23:59:06 -0500, Christopher Singley <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote:> I just picked up Rails, am kicking the tires, and not being able to get > it do some basic stuff. > Can somebody help an utter newbie? I can''t get MySQL to commit > transactions where I''m trying to put in references to other db tables. > > I''m getting this error message: > ''Packets out of order'' error was received from the database. > > The MySQL server is version 4.0.25, and Rails is able to make the > connection on other tables with an identical configuration. > > Here''s what was coming from the template: > -- > <p><label for="txatom_trnsaction_id">Transaction ID</label> > <%= text_field ''txatom'', ''trnsaction_id'', ''size'' => 9 %> > > <label for="txatom_account_id">Acct. ID</label> > <%= text_field ''txatom'', ''account_id'', ''size'' => 9 %> > > <label for="txatom_cr">Dr/Cr</label> > <%= text_field ''txatom'', ''cr'', ''size'' => 1 %> > > <label for="txatom_amount">Amount</label> > <%= text_field ''txatom'', ''amount'' %></p> > -- > > Here''s where it crapped it in the controller: > --- > @txatom = Txatom.new(@params[:txatom]) > --- > > Here''s what the error message is telling me: > ---- > Request > > Parameters: {"commit"=>"Create", "txatom"=>{"trnsaction_id"=>"1", > "account_id"=>"100", "cr"=>"0", "amount"=>"200.00"}} > ---- > > Here''s the table in question: > --- > CREATE TABLE txatoms ( > id int(11) unsigned NOT NULL auto_increment, > trnsaction_id int(11) unsigned default NULL, > account_id int(11) unsigned default NULL, > cr tinyint(1) unsigned default ''0'', > amount decimal(11,2) unsigned default NULL, > created_on timestamp(14) NOT NULL, > updated_on timestamp(14) NOT NULL default ''00000000000000'', > PRIMARY KEY (id) > ) TYPE=MyISAM; > --- > > and here''s the model: > --- > class Txatom < ActiveRecord::Base > belongs_to :trnsaction > validates_associated :trnsaction > belongs_to :account > validates_associated :account > > validates_numericality_of :trnsaction_id > validates_numericality_of :account_id > validates_numericality_of :cr > validates_numericality_of :amount > > end > --- > > I''d appreciate it if someone could tell me what I''m doing wrong. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
I remember getting the same error at one time... unfortunately I can''t remember what I did to fix the problem. Have you tried renaming your table fields to see if perhaps the names are conflicting with Rails in some weird way. I''m sure you''ve probably tried this already, but another thing you could do is start with the simplest setup, and gradually add more functionality until it breaks. Remove all the "validates..." from your model to make sure those aren''t causing the problem. Also tail -f your Rails log file and make sure the queries are correct. Perhaps test them outside of Rails. Make sure the account table has an id field. Sorry I can''t offer anything concrete. Best of luck. James On 8/26/05, Christopher Singley <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote:> Thanks to those who''ve responded, but I''ve hit a blank wall. > > I use identical instructions & db table, but remove the ''trnsaction_id'' > and ''account_id'' fields & references thereto in code, and MySQL CRUDs > happily. As soon as I try writing some ''column_id'' in the fashion I''m > attempting, I get these "Packets out of order" complaints. > > Is there another way to do it? What do other people put in to write a row > with references to other column IDs? > > On Wed, 24 Aug 2005 23:59:06 -0500, Christopher Singley > <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote: > > > I just picked up Rails, am kicking the tires, and not being able to get > > it do some basic stuff. > > Can somebody help an utter newbie? I can''t get MySQL to commit > > transactions where I''m trying to put in references to other db tables. > > > > I''m getting this error message: > > ''Packets out of order'' error was received from the database. > > > > The MySQL server is version 4.0.25, and Rails is able to make the > > connection on other tables with an identical configuration. > > > > Here''s what was coming from the template: > > -- > > <p><label for="txatom_trnsaction_id">Transaction ID</label> > > <%= text_field ''txatom'', ''trnsaction_id'', ''size'' => 9 %> > > > > <label for="txatom_account_id">Acct. ID</label> > > <%= text_field ''txatom'', ''account_id'', ''size'' => 9 %> > > > > <label for="txatom_cr">Dr/Cr</label> > > <%= text_field ''txatom'', ''cr'', ''size'' => 1 %> > > > > <label for="txatom_amount">Amount</label> > > <%= text_field ''txatom'', ''amount'' %></p> > > -- > > > > Here''s where it crapped it in the controller: > > --- > > @txatom = Txatom.new(@params[:txatom]) > > --- > > > > Here''s what the error message is telling me: > > ---- > > Request > > > > Parameters: {"commit"=>"Create", "txatom"=>{"trnsaction_id"=>"1", > > "account_id"=>"100", "cr"=>"0", "amount"=>"200.00"}} > > ---- > > > > Here''s the table in question: > > --- > > CREATE TABLE txatoms ( > > id int(11) unsigned NOT NULL auto_increment, > > trnsaction_id int(11) unsigned default NULL, > > account_id int(11) unsigned default NULL, > > cr tinyint(1) unsigned default ''0'', > > amount decimal(11,2) unsigned default NULL, > > created_on timestamp(14) NOT NULL, > > updated_on timestamp(14) NOT NULL default ''00000000000000'', > > PRIMARY KEY (id) > > ) TYPE=MyISAM; > > --- > > > > and here''s the model: > > --- > > class Txatom < ActiveRecord::Base > > belongs_to :trnsaction > > validates_associated :trnsaction > > belongs_to :account > > validates_associated :account > > > > validates_numericality_of :trnsaction_id > > validates_numericality_of :account_id > > validates_numericality_of :cr > > validates_numericality_of :amount > > > > end > > --- > > > > I''d appreciate it if someone could tell me what I''m doing wrong. > > _______________________________________________ > > 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 >
Do you have accounts and trnsactions associated to txatoms? I.e., class Account < ActiveRecord::Base has_one :txatom end I haven''t done validations yet (bad RoR developer, no cookie), but I imagine they would need everything associated properly for everything to jive. Also, what SQL shows up in your log file? That might help point the way a bit. -- Mando On 8/26/05, Christopher Singley <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote:> > Thanks to those who''ve responded, but I''ve hit a blank wall. > > I use identical instructions & db table, but remove the ''trnsaction_id'' > and ''account_id'' fields & references thereto in code, and MySQL CRUDs > happily. As soon as I try writing some ''column_id'' in the fashion I''m > attempting, I get these "Packets out of order" complaints. > > Is there another way to do it? What do other people put in to write a row > with references to other column IDs? > >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Thanks for your help.
In the models, I''ve done this:
---
class Txatom < ActiveRecord::Base
belongs_to :trnsaction
validates_associated :trnsaction
belongs_to :account
validates_associated :account
end
class Account < ActiveRecord::Base
has_many :txatoms
validates_associated :txatoms
end
class Trnsaction < ActiveRecord::Base
has_many :txatoms
validates_associated :txatoms
belongs_to :portfolio
validates_associated :portfolio
end
---
As you can see, there''s a Portfolio class in there too which (I hope)
doesn''t concern this problem.
When I get that "Packets out of order" error from ActiveRecord, the
log
shows this:
--
Processing TxatomController#create (for 127.0.0.1 at Fri Aug 26 22:13:35
Central Daylight Time 2005)
Parameters: {"commit"=>"Create",
"txatom"=>{"trnsaction_id"=>"1",
"account_id"=>"100", "cr"=>"0",
"amount"=>"200.00"},
"action"=>"create",
"controller"=>"txatom"}
[4;35mTxatom Columns (0.000000) [0;37mSHOW FIELDS FROM txatoms
[4;33mTxatom Columns (0.000000) [1;37mSHOW FIELDS FROM txatoms
[4;35mSQL (0.000000) [0;37mBEGIN
[4;33mAccount Load (0.000000) [1;37mSELECT * FROM accounts WHERE
accounts.id = 100 LIMIT 1
[4;35mTrnsaction Load (0.010000) [0;37mSELECT * FROM trnsactions
WHERE trnsactions.id = 1 LIMIT 1
[4;33mTrnsaction Columns (0.010000) [1;37mSHOW FIELDS FROM
trnsactions
[4;35mTxatom Load (0.000000) [0;37mSELECT * FROM txatoms WHERE
txatoms.trnsaction_id = 1
[4;33mAccount Load (0.000000) [1;37mSELECT * FROM accounts WHERE
accounts.id = 5 LIMIT 1
[4;35mAccount Columns (0.000000) [0;37mSHOW FIELDS FROM
accounts
[followed by many iterations of the following two lines]
[4;33mTxatom Load (0.010000) [1;37mSELECT * FROM txatoms WHERE
txatoms.account_id = 5
[4;35mAccount Load (0.000000) [0;37mSELECT * FROM accounts WHERE
accounts.id = 5 LIMIT 1
[followed by MySQL disgustedly throwing in the towel]
[4;33mTxatom Load (0.000000) [1;37mstack level too deep: SELECT *
FROM txatoms WHERE txatoms.account_id = 5
[4;35mSQL (0.000000) [0;37mPackets out of order: 1<>4: ROLLBACK
[followed by ActiveRecord puking up a whole pile of validation errors &c.]
---
All I want to is to debit account#100 for $200.00, and have that debit
bound to transaction#1.
What sort of SQL hell have I blundered into ignorantly?
On Fri, 26 Aug 2005 14:53:26 -0500, Mando Escamilla
<mando.escamilla-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> Do you have accounts and trnsactions associated to txatoms? I.e.,
>
> class Account < ActiveRecord::Base
> has_one :txatom
> end
>
> I haven''t done validations yet (bad RoR developer, no cookie), but
I
> imagine
> they would need everything associated properly for everything to jive.
>
> Also, what SQL shows up in your log file? That might help point the way a
> bit.
>
> --
> Mando
>
>
> On 8/26/05, Christopher Singley
<csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote:
>>
>> Thanks to those who''ve responded, but I''ve hit a
blank wall.
>>
>> I use identical instructions & db table, but remove the
''trnsaction_id''
>> and ''account_id'' fields & references thereto in
code, and MySQL CRUDs
>> happily. As soon as I try writing some ''column_id'' in
the fashion I''m
>> attempting, I get these "Packets out of order" complaints.
>>
>> Is there another way to do it? What do other people put in to write a
>> row
>> with references to other column IDs?
>>
>>
Is it necessary for txatoms to belong to both transactions AND accounts? It seems to me that things might be simpler if Txatoms belong_to trnsactions and trnsactions belong_to acounts. I''m no Rails expert, but I''m thinking that the txatom relationship to both trnsactions and accounts is your problem. Can you send your table sql for trnsactions and accounts? I might be able to get a little farther if I can hack on it myself. -- Mando On 8/26/05, Christopher Singley <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote:> > Thanks for your help. > > In the models, I''ve done this: > --- > class Txatom < ActiveRecord::Base > belongs_to :trnsaction > validates_associated :trnsaction > belongs_to :account > validates_associated :account > end > > class Account < ActiveRecord::Base > has_many :txatoms > validates_associated :txatoms > end > > class Trnsaction < ActiveRecord::Base > has_many :txatoms > validates_associated :txatoms > belongs_to :portfolio > validates_associated :portfolio > end > --- > As you can see, there''s a Portfolio class in there too which (I hope) > doesn''t concern this problem. > > When I get that "Packets out of order" error from ActiveRecord, the log > shows this: > -- > Processing TxatomController#create (for 127.0.0.1 <http://127.0.0.1> at > Fri Aug 26 22:13:35 > Central Daylight Time 2005) > Parameters: {"commit"=>"Create", "txatom"=>{"trnsaction_id"=>"1", > "account_id"=>"100", "cr"=>"0", "amount"=>"200.00"}, "action"=>"create", > "controller"=>"txatom"} > [4;35mTxatom Columns (0.000000) [0;37mSHOW FIELDS FROM txatoms > [4;33mTxatom Columns (0.000000) [1;37mSHOW FIELDS FROM txatoms > [4;35mSQL (0.000000) [0;37mBEGIN > [4;33mAccount Load (0.000000) [1;37mSELECT * FROM accounts WHERE > accounts.id <http://accounts.id> = 100 LIMIT 1 > [4;35mTrnsaction Load (0.010000) [0;37mSELECT * FROM trnsactions > WHERE trnsactions.id <http://trnsactions.id> = 1 LIMIT 1 > [4;33mTrnsaction Columns (0.010000) [1;37mSHOW FIELDS FROM > trnsactions > [4;35mTxatom Load (0.000000) [0;37mSELECT * FROM txatoms WHERE > txatoms.trnsaction_id = 1 > [4;33mAccount Load (0.000000) [1;37mSELECT * FROM accounts WHERE > accounts.id <http://accounts.id> = 5 LIMIT 1 > [4;35mAccount Columns (0.000000) [0;37mSHOW FIELDS FROM > accounts > > [followed by many iterations of the following two lines] > > [4;33mTxatom Load (0.010000) [1;37mSELECT * FROM txatoms WHERE > txatoms.account_id = 5 > [4;35mAccount Load (0.000000) [0;37mSELECT * FROM accounts WHERE > accounts.id <http://accounts.id> = 5 LIMIT 1 > > [followed by MySQL disgustedly throwing in the towel] > > [4;33mTxatom Load (0.000000) [1;37mstack level too deep: SELECT * > FROM txatoms WHERE txatoms.account_id = 5 > [4;35mSQL (0.000000) [0;37mPackets out of order: 1<>4: ROLLBACK > > [followed by ActiveRecord puking up a whole pile of validation errors &c.] > --- > > All I want to is to debit account#100 for $200.00, and have that debit > bound to transaction#1. > What sort of SQL hell have I blundered into ignorantly? > > > On Fri, 26 Aug 2005 14:53:26 -0500, Mando Escamilla > <mando.escamilla-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > Do you have accounts and trnsactions associated to txatoms? I.e., > > > > class Account < ActiveRecord::Base > > has_one :txatom > > end > > > > I haven''t done validations yet (bad RoR developer, no cookie), but I > > imagine > > they would need everything associated properly for everything to jive. > > > > Also, what SQL shows up in your log file? That might help point the way > a > > bit. > > > > -- > > Mando > > > > > > On 8/26/05, Christopher Singley <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote: > >> > >> Thanks to those who''ve responded, but I''ve hit a blank wall. > >> > >> I use identical instructions & db table, but remove the ''trnsaction_id'' > >> and ''account_id'' fields & references thereto in code, and MySQL CRUDs > >> happily. As soon as I try writing some ''column_id'' in the fashion I''m > >> attempting, I get these "Packets out of order" complaints. > >> > >> Is there another way to do it? What do other people put in to write a > >> row > >> with references to other column IDs? > >> > >> > > > _______________________________________________ > 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
On Sat, 27 Aug 2005 09:58:02 -0500, Mando Escamilla <mando.escamilla-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Is it necessary for txatoms to belong to both transactions AND accounts? > It > seems to me that things might be simpler if Txatoms belong_to trnsactions > and trnsactions belong_to acounts.Surely the problem is something along these lines. However, it''s not apparent to me that things can be simplified so cleanly. Let me explain the model. I''m trying to figure out how to do some financial accounting. A transaction (class Trnsaction) takes place in a portfolio (class Portfolio) on a given date. A transaction consists of an arbitrary number of debits and credits (class Txatom). Each debit or credit increases one specific ledger account (class Account) by a given amount. There exists a constraint that the sum of the amounts of all credits in a transaction must equal the sum of the amounts of all debits in the same transaction... i.e. standard double-entry accounting. To clarify my nomenclature, I''m using the word "account" in the technical sense - i.e. book-entry accounts in a journal or ledger. Examples of financial accounts are the cash account, the paid-in capital account, revenues, expenses, and so forth. With that in mind, each trnsaction is going to modify a minimum of 2 accounts; in fact, a single trnsaction could modify an arbitrary number of counts (e.g. doing payroll. That is why I''m separating Txatoms out into a different table, rather than embodying them as columns in the transactions table. Another point which may not be immediately obvious is that I intend the "Cr" attribute of each instance of Txatom to be a binary variable - 0 for debit, 1 for credit. The SQL for these tables is appended below. I''ve left in place a little data for a sample transaction. Thanks for taking a look. I''m very appreciative of any suggestions. I really would like to explore doing my application in Rails. CREATE TABLE portfolios ( id int(11) NOT NULL auto_increment, number int(11) default NULL, title varchar(255) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; CREATE TABLE accounts ( id int(11) NOT NULL auto_increment, number int(11) default NULL, name varchar(255) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; -- -- Dumping data for table `accounts` -- INSERT INTO accounts VALUES (5,100,''Assets''); INSERT INTO accounts VALUES (6,200,''Liabilities''); INSERT INTO accounts VALUES (7,300,''Equity''); CREATE TABLE trnsactions ( id int(11) unsigned NOT NULL auto_increment, number int(11) unsigned default ''0'', date date default NULL, portfolio_id int(11) unsigned default NULL, note varchar(255) default NULL, created_on timestamp(14) NOT NULL, updated_on timestamp(14) NOT NULL default ''00000000000000'', PRIMARY KEY (id) ) TYPE=MyISAM; -- -- Dumping data for table `trnsactions` -- INSERT INTO trnsactions VALUES (1,1,''2005-08-20'',NULL,''Sell 100sh stock @ $10'',20050824175017,00000000000000); CREATE TABLE txatoms ( id int(11) unsigned NOT NULL auto_increment, trnsaction_id int(11) unsigned default NULL, account_id int(11) unsigned default NULL, cr tinyint(1) unsigned default ''0'', amount decimal(11,2) unsigned default NULL, created_on timestamp(14) NOT NULL, updated_on timestamp(14) NOT NULL default ''00000000000000'', PRIMARY KEY (id) ) TYPE=MyISAM;
If you want to do transactions, don''t you have to use InnoDB tables in MySql? Switching them may not solve your problem, but if you truly need to do double-entry accounting (write 2 records or no records), you need to convert them to InnoDB anyway. On 8/27/05, Christopher Singley <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote:> On Sat, 27 Aug 2005 09:58:02 -0500, Mando Escamilla > <mando.escamilla-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > Is it necessary for txatoms to belong to both transactions AND accounts? > > It > > seems to me that things might be simpler if Txatoms belong_to trnsactions > > and trnsactions belong_to acounts. > > Surely the problem is something along these lines. However, it''s not > apparent to me that things can be simplified so cleanly. > > Let me explain the model. I''m trying to figure out how to do some > financial accounting. A transaction (class Trnsaction) takes place in a > portfolio (class Portfolio) on a given date. > > A transaction consists of an arbitrary number of debits and credits (class > Txatom). Each debit or credit increases one specific ledger account > (class Account) by a given amount. There exists a constraint that the sum > of the amounts of all credits in a transaction must equal the sum of the > amounts of all debits in the same transaction... i.e. standard > double-entry accounting. > > To clarify my nomenclature, I''m using the word "account" in the technical > sense - i.e. book-entry accounts in a journal or ledger. Examples of > financial accounts are the cash account, the paid-in capital account, > revenues, expenses, and so forth. > > With that in mind, each trnsaction is going to modify a minimum of 2 > accounts; in fact, a single trnsaction could modify an arbitrary number of > counts (e.g. doing payroll. That is why I''m separating Txatoms out into a > different table, rather than embodying them as columns in the transactions > table. > > Another point which may not be immediately obvious is that I intend the > "Cr" attribute of each instance of Txatom to be a binary variable - 0 for > debit, 1 for credit. > > The SQL for these tables is appended below. I''ve left in place a little > data for a sample transaction. Thanks for taking a look. I''m very > appreciative of any suggestions. I really would like to explore doing my > application in Rails. > > CREATE TABLE portfolios ( > id int(11) NOT NULL auto_increment, > number int(11) default NULL, > title varchar(255) default NULL, > PRIMARY KEY (id) > ) TYPE=MyISAM; > > CREATE TABLE accounts ( > id int(11) NOT NULL auto_increment, > number int(11) default NULL, > name varchar(255) default NULL, > PRIMARY KEY (id) > ) TYPE=MyISAM; > > -- > -- Dumping data for table `accounts` > -- > > INSERT INTO accounts VALUES (5,100,''Assets''); > INSERT INTO accounts VALUES (6,200,''Liabilities''); > INSERT INTO accounts VALUES (7,300,''Equity''); > > CREATE TABLE trnsactions ( > id int(11) unsigned NOT NULL auto_increment, > number int(11) unsigned default ''0'', > date date default NULL, > portfolio_id int(11) unsigned default NULL, > note varchar(255) default NULL, > created_on timestamp(14) NOT NULL, > updated_on timestamp(14) NOT NULL default ''00000000000000'', > PRIMARY KEY (id) > ) TYPE=MyISAM; > > -- > -- Dumping data for table `trnsactions` > -- > > INSERT INTO trnsactions VALUES (1,1,''2005-08-20'',NULL,''Sell 100sh stock @ > $10'',20050824175017,00000000000000); > > CREATE TABLE txatoms ( > id int(11) unsigned NOT NULL auto_increment, > trnsaction_id int(11) unsigned default NULL, > account_id int(11) unsigned default NULL, > cr tinyint(1) unsigned default ''0'', > amount decimal(11,2) unsigned default NULL, > created_on timestamp(14) NOT NULL, > updated_on timestamp(14) NOT NULL default ''00000000000000'', > PRIMARY KEY (id) > ) TYPE=MyISAM; > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Brock Weaver [OBC]Technique
Thanks for your suggestion. I think using InnoDB would help to guarantee transactional integrity by pushing all-or-none atomic commits down into the database. However, this doesn''t let me out of some fairly extensive validation paranoia at the application level. Anyway, at this point I''m pretty much stuck at the proof-of-concept level. Per the subject header, I am a bit afraid of mucking around with InnoDB before I get a basic setup working. I''m pretty dumb about databases. I''m a finance geek, not really a programmer. I''m hoping to use this little project as an excuse to check out Ruby & Rails, but if I can''t even get a "hello world; debit cash/credit equity" type setup working I''m afraid I may have to drop back to Excel & VBA. If you kind folks have any suggestions that would save an utter newbie from that fate, it would be very much appreciated. On Mon, 29 Aug 2005 08:46:49 -0500, Brock Weaver <brockweaver-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> If you want to do transactions, don''t you have to use InnoDB tables in > MySql? Switching them may not solve your problem, but if you truly > need to do double-entry accounting (write 2 records or no records), > you need to convert them to InnoDB anyway. > > On 8/27/05, Christopher Singley <csingley-3I6+fppYqIPENTYZowK78tBPR1lH4CV8@public.gmane.org> wrote: >> On Sat, 27 Aug 2005 09:58:02 -0500, Mando Escamilla >> <mando.escamilla-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >> > Is it necessary for txatoms to belong to both transactions AND >> accounts? >> > It >> > seems to me that things might be simpler if Txatoms belong_to >> trnsactions >> > and trnsactions belong_to acounts. >> >> Surely the problem is something along these lines. However, it''s not >> apparent to me that things can be simplified so cleanly. >> >> Let me explain the model. I''m trying to figure out how to do some >> financial accounting. A transaction (class Trnsaction) takes place in a >> portfolio (class Portfolio) on a given date. >> >> A transaction consists of an arbitrary number of debits and credits >> (class >> Txatom). Each debit or credit increases one specific ledger account >> (class Account) by a given amount. There exists a constraint that the >> sum >> of the amounts of all credits in a transaction must equal the sum of the >> amounts of all debits in the same transaction... i.e. standard >> double-entry accounting. >> >> To clarify my nomenclature, I''m using the word "account" in the >> technical >> sense - i.e. book-entry accounts in a journal or ledger. Examples of >> financial accounts are the cash account, the paid-in capital account, >> revenues, expenses, and so forth. >> >> With that in mind, each trnsaction is going to modify a minimum of 2 >> accounts; in fact, a single trnsaction could modify an arbitrary number >> of >> counts (e.g. doing payroll. That is why I''m separating Txatoms out >> into a >> different table, rather than embodying them as columns in the >> transactions >> table. >> >> Another point which may not be immediately obvious is that I intend the >> "Cr" attribute of each instance of Txatom to be a binary variable - 0 >> for >> debit, 1 for credit. >> >> The SQL for these tables is appended below. I''ve left in place a little >> data for a sample transaction. Thanks for taking a look. I''m very >> appreciative of any suggestions. I really would like to explore doing >> my >> application in Rails. >> >> CREATE TABLE portfolios ( >> id int(11) NOT NULL auto_increment, >> number int(11) default NULL, >> title varchar(255) default NULL, >> PRIMARY KEY (id) >> ) TYPE=MyISAM; >> >> CREATE TABLE accounts ( >> id int(11) NOT NULL auto_increment, >> number int(11) default NULL, >> name varchar(255) default NULL, >> PRIMARY KEY (id) >> ) TYPE=MyISAM; >> >> -- >> -- Dumping data for table `accounts` >> -- >> >> INSERT INTO accounts VALUES (5,100,''Assets''); >> INSERT INTO accounts VALUES (6,200,''Liabilities''); >> INSERT INTO accounts VALUES (7,300,''Equity''); >> >> CREATE TABLE trnsactions ( >> id int(11) unsigned NOT NULL auto_increment, >> number int(11) unsigned default ''0'', >> date date default NULL, >> portfolio_id int(11) unsigned default NULL, >> note varchar(255) default NULL, >> created_on timestamp(14) NOT NULL, >> updated_on timestamp(14) NOT NULL default ''00000000000000'', >> PRIMARY KEY (id) >> ) TYPE=MyISAM; >> >> -- >> -- Dumping data for table `trnsactions` >> -- >> >> INSERT INTO trnsactions VALUES (1,1,''2005-08-20'',NULL,''Sell 100sh stock >> @ >> $10'',20050824175017,00000000000000); >> >> CREATE TABLE txatoms ( >> id int(11) unsigned NOT NULL auto_increment, >> trnsaction_id int(11) unsigned default NULL, >> account_id int(11) unsigned default NULL, >> cr tinyint(1) unsigned default ''0'', >> amount decimal(11,2) unsigned default NULL, >> created_on timestamp(14) NOT NULL, >> updated_on timestamp(14) NOT NULL default ''00000000000000'', >> PRIMARY KEY (id) >> ) TYPE=MyISAM; >> >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> > >