ValueError: Field <blah> is already bound to a table <-- need a fix for this

[Joe Barnhart]

I just switched to Version 2.15.1-stable+timestamp.2017.07.10.16.17.24 and I'm getting a new and very unwelcome error.

ValueError:  Field <blah> is already bound to a table

Yeah.  I knew that.  In fact, the field is already bound to exactly the SAME table in other queries.  So what?  Why is that now an error?  I double-checked and sure enough the table its bound to is exactly the table that it would be bound to if the "bind" function completes.  (Although the table._db of the already bound table has a "real" URI and the not-yet-bound table has a table._db lacking a real URI.)

Why have I never seen this error before?  I've been developing this app on and off for a couple of years and this is the first time I've seen this problem.  Is it by chance new with 2.15.1?

What is the purpose of this error?  What binding is occurring?  (I'm guessing this is the binding in the driver immediately before a query.)  Why can this binding only happen once?  Is it happening because I do the heavy lifting in modules instead of controllers?  I often use the same tables more than once in a function, so this could be problematic (read: fatal) in my adoption of 2.15.1.

-- Joe

[Anthony]

As usual, I think we need to see some code and a traceback.

[Joe Barnhart]

Hmm... That's gonna be a problem.

This is occurring in an SQLFORM... which is in a modal pop-up box... over a tabbed structure with about 5 more SQLFORMS...  plus 5 or 6 jQuery DataTables... all powered by the same basic set of 4 or 5 tables.  It will take a solid day to boil it down to a simpler example which could be posted successfully.

I'm hoping to get a sense of the philosophy of the new code, so I can debug it myself and figure out if it could be fixed for this case.  It seems that each Field is "bound" to a Table when a query is performed.  I presume this binding has always occurred, yet now it is causing this obscure error.  I'm not sure why it would ever be an error to bind the same field to the same table, and it seems that this "case" should be the most frequently encountered.  Now I'll admit it DOES bother me A LOT to place a database instance INSIDE the Field and the Table objects.  I'd think that would be a very poor idea if you wanted your design to be a clean and free from hidden side effects.

My biggest fear is that DAL has taken a direction which will make it incompatible with my use, and that I'll never be able to use 2.15.1 or later versions.  I'm weeks away from deploying the biggest site I've ever done. As of this second, my best short-term option is to revert to the previous version of web2py and finish.  But that has severe long-term repercussions if I can't update my web platform in the future.

-- Joe

[Anthony]

On Saturday, July 15, 2017 at 12:47:04 PM UTC-4, Joe Barnhart wrote:
> Hmm... That's gonna be a problem.
>
>
> This is occurring in an SQLFORM... which is in a modal pop-up box... over a tabbed structure with about 5 more SQLFORMS...  plus 5 or 6 jQuery DataTables... all powered by the same basic set of 4 or 5 tables.  It will take a solid day to boil it down to a simpler example which could be posted successfully.

Why can't you show the traceback?

Also, what do you mean the field is bound to the same table in other queries? Is that in the same request? Give us some details.

> I'm hoping to get a sense of the philosophy of the new code, so I can debug it myself and figure out if it could be fixed for this case.  It seems that each Field is "bound" to a Table when a query is performed.  I presume this binding has always occurred, yet now it is causing this obscure error.  I'm not sure why it would ever be an error to bind the same field to the same table, and it seems that this "case" should be the most frequently encountered.

I'm not sure why the code now checks for an existing table, but perhaps it would at least make sense to allow re-binding to the same table (though would be good to know why that is even happening to begin with).

> Now I'll admit it DOES bother me A LOT to place a database instance INSIDE the Field and the Table objects.  I'd think that would be a very poor idea if you wanted your design to be a clean and free from hidden side effects.

What is the problem, and what alternative do you purpose?

> My biggest fear is that DAL has taken a direction which will make it incompatible with my use, and that I'll never be able to use 2.15.1 or later versions.

Not sure where this is coming from - this is one bug from one new line of code. Either you're doing something wrong and will need to make an adjustment, or there is a bug in the DAL that needs to be fixed.

Antony

[Leonel Câmara]

I believe this DAL change is actually showing bugs in web2py we need to change SQLFORM.factory to clone the fields. Then we need to fix common_fields in the DAL so it makes clones of the fields. 

  

For now if you need a quickfix for your application you can clone the fields you send to the SQLFORM. Example:

db.define_table('t1', Field('name'))

SQLFORM.factory(db.t1.name.clone())

[Leonel Câmara]

Joe can you show us some code? I've already fixed two bugs related to this, one on the DAL and another in web2py, I want to make sure yours isn't a new one.

[Joe Barnhart]

Yes... Massimo helped me come up with a workaround for my case.

I am using SQLFORM.factory to create a form to edit two tables joined together.  One table is used to index entries in a bunch of other tables by a 6-tuple.  Since these are all swimming related, the tuple is (sex, upper_age, lower_age, stroke, distance, course).  This is turned into an index and used in a bunch of other tables which all need this same information.

Here is the outline of the problematic method, with the fix in RED:

def edit_event():
    . . .
    (dbe, dbei) = (db.event, db.event_index)
    . . .
    fields = [dbei[f].clone() for f in dbei.fields if f not in ['id','code']]
    fields += [dbe[f].clone() for f in dbe.fields if f not in ['id','id_meet','id_session','id_event','ord']]
    rec = db(dbe.id==event_id).select(dbe.ALL, dbei.ALL, cacheable=True, limitby=(0,1),
                                      join=dbei.on(dbei.id==dbe.id_event)).first()
    d = dict(rec.event_index)
    d.update(rec.event)
    . . .
    form = modal_form(SQLFORM.factory, *fields, record=d, table_name="event_join",showid=False,
                      title=H4("Editing Event Settings", _class="center”))
    . . .

Cloning the fields before using them in SQLFORM.factory avoids the error.

-- Joe

[Joe Barnhart]

Found the issue and a workaround.... see below.

[Leonel Câmara]

Oh, good to know, it was the bug i've fixed in SQLFORM.factory then.  It will be fixed in the next version and you will be able to rollback your workaround.

[Joe Barnhart]

I think I tried your fix but it didn't work. None of my fields were "common". Massimo had pointed out the fix on Github.

[Anthony]

You mentioned the field was being re-bound to the same table, which does not appear to be the case below, as SQLFORM. factory creates its own dummy table. Was this causing another problem in some other context?

Anthony

[Leonel Câmara]

Joe the fix on the DAL wasn't for your problem, Your problem was fixed in web2py here:

https://github.com/web2py/web2py/pull/1686/commits/ce0b2557470ecb7c92719645293cb00785fe48bc

[Joe Barnhart]

You are correct.  By using the SQLFORM.factory function the table was, indeed, different.  I blame the lapse on the inability of neurons to function in the 106 degree heat we're having in the Sacramento area today.

-- Joe

[Joe Barnhart]

Yes that fix looks exactly on point.

-- Joe

[Massimo Di Pierro]

Can you confirm all is good now with 2.15.2?

Thanks to you and Leonel for looking into this so quickly.

[Joe Barnhart]

Yes.  I just tested with 2.15.2 and all works perfectly as expected.

-- Joe