Updating to W2P 2.20+ Now All Edit Forms Return IS_NOT_IN_DB(Validator) Error String

[ghost]

Originally submitted this issue to the web2py repo:

Describe the bug
Updated from 18.x to 20.2.x now causes ALL applications edit form submissions (a record UPDATE) to return error string "value already in database or empty" from class IS_NOT_IN_DB(Validator) of validator.py for custom PK's

To Reproduce
Steps to reproduce the behavior:
Use W2P 2.18
Have unique PK in db.py. Example:
Field('request_uuid', 'string', length=36, default=uuid.uuid4(), notnull=True, unique=True, readable=False), primarykey=['request_uuid']
Create record.
Attempt to edit record.
Make, or do not make, changes to record via UI, hit submit, error string will be returned either way.

Interim Fix
Removing unique=True from field definition in db.py fixes the error

Expected behavior
Edit form submission should successfully UPDATE database as it did prior to W2P update to 2.20.
DAL field definition should use unique=True, where necessary, and not throw the error.

Screenshots

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Smartphone (please complete the following information):

• Device: [e.g. iPhone6]
• OS: [e.g. iOS8.1]
• Browser [e.g. stock browser, safari]
• Version [e.g. 22]

Additional context
There's been recent work on the class in question, based on search:
https://github.com/web2py/web2py/search?q=IS_NOT_IN_DB%28Validator&unscoped_q=IS_NOT_IN_DB%28Validator

[davereinhart]

Not 100% sure, but it appears that some recent changes caused validators to no longer support keyed tables
https://github.com/web2py/pydal/commits/252e004be84d2b5c0642033e9024921ebf75c802/pydal/validators.py

[davereinhart]

I narrowed it down to this commit:
74c4a1c#diff-f61cb7600f7f1e4b0549d4e83462b1dc

I'm not familiar with why these changes were made or how to update them to support keyed tables. The only fix i've found is to roll back to the version of validators.py prior to this commit.

[jparga]

Hello,

This is an old issue not solved yet.

I think it is an issue that comes from previous version. It has to do with IS_NOT_IN_DB validator and auth. It worked in web2py 2.17.2 version with no problem

Traceback (most recent call last): 
 File "/base/data/home/apps/e~xxxxxxxxxxxxx-/218.431301544681755891/gluon/restricted.py", line 219, in restricted exec(ccode, environment) 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/applications/myapp/controllers/mycontroller.py", line 199, in <module> 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/gluon/globals.py", line 430, in <lambda> self._caller = lambda f: f() 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/gluon/tools.py", line 3875, in f return action(*a, **b) 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/applications/myapp/controllers/mycrontroller.py", line 110, in crear_usuario if form.accepts(request.vars, session): 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/gluon/sqlhtml.py", line 1790, in accepts **kwargs 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/gluon/html.py", line 2162, in accepts status = self._traverse(status, hideerror) 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/gluon/html.py", line 921, in _traverse newstatus = c._traverse(status, hideerror) and newstatus 
  File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/gluon/html.py", line 921, in _traverse newstatus = c._traverse(status, hideerror) and newstatus 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/gluon/html.py", line 921, in _traverse newstatus = c._traverse(status, hideerror) and newstatus 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/gluon/html.py", line 921, in _traverse newstatus = c._traverse(status, hideerror) and newstatus 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/gluon/html.py", line 928, in _traverse newstatus = self._validate() 
 File "/base/data/home/apps/e-myapp-dir/218.431301544681755891/gluon/html.py", line 1908, in _validate raise Exception(msg) 
Exception: Validation error, field:email <pydal.validators.IS_NOT_IN_DB object at 0x3ebef34de910>

This is the code of the controller where it breaks

form = SQLFORM(db.auth_user)
 if form.accepts(request.vars, session):

It also fails in appadmin db.auth_user new record

[mmoye]

Any movement on this one? When updating a form that uses a radio widget we are seeing the same "Value not in database" error message whether the value has been changed or not.

In db.py definition:

Field('my_field', 'string', requires=IS_NULL_OR(IS_IN_SET({'y':'Yes', 'n':'No', 'na':'N/A', 'unk':'Unknown'},sort=True )), widget=SQLFORM.widgets.radio.widget)),

After submit if you print my_field in the controller it returns a list which the validator cannot handle:

'my_field': ['y', '']

instead of just 'y'.
This does not happen in v2.18.x of Web2Py but is happening in v2.20.x

Any ideas why this is happening and how to fix it?

If I remove the widget= statement from the field definition the problem goes away, but I'd really like to use the widget on my form (I have several).