Actually I was wrong. this is not the problem. Web2py does the right thing.
Is there any way you can look into the mysql logs what is the sql string
that causes the problem?
On Tuesday, 10 April 2012 08:23:03 UTC-5, Massimo Di Pierro wrote:
>
> I figured this out. The table has a self reference and web2py inserts a
> zero in it instead of NULL. Works for sqlite but not MySQL.
> Changing zero with NULL may be treated as a bug fix it will constitute a
> minor change of backward compatibility in case you incorrectly do
>
> db(db.table.reference_field==0).select()
>
> while the correct thing to do would be
>
> db(~(db.table.reference_field>0)).select()
>
> I will try fix it and then will ask for comments.
>
> On Monday, 9 April 2012 18:29:10 UTC-5, tomt wrote:
>>
>> I have declared the table in db.py with auth.signature, and uncommented
>> auth.enable_record_versioning(db), but the _archive table isn't created.
>> - Tom
>>
>> On Monday, April 9, 2012 8:33:18 AM UTC-6, Massimo Di Pierro wrote:
>>>
>>> the signature=True only adds a signature to the auth_* tables so that if
>>> a user creates an account for another user or creates a group, you can keep
>>> track of who did it.
>>>
>>> The mything_archive table should be created by:
>>>
>>> auth.enable_record_versioning(db)
>>>
>>> This should be called after the mything table is defined. Does it work?
>>>
>>> On Sunday, 8 April 2012 22:08:47 UTC-5, tomt wrote:
>>>>
>>>> Hi,
>>>> Thanks for your response. I deleted the database as you suggested and
>>>> changed signature=False. The problem did go away and I was able to add
>>>> users without the error.
>>>> I then reverted to signature=True. While subsequent modifications did
>>>> show the signature, the 'mything_archive' was never created.
>>>>
>>>> - Tom
>>>>
>>>> On Sunday, April 8, 2012 9:04:14 AM UTC-6, Massimo Di Pierro wrote:
>>>>>
>>>>> Can you try again with mysql, delete the database and replace:
>>>>>
>>>>> auth.define_tables(signature=True)
>>>>> with
>>>>> auth.define_tables(signature=False)
>>>>>
>>>>> Does the problem does away? It looks like it does not like the self
>>>>> reference in auth_user.
>>>>>
>>>>> On Saturday, 7 April 2012 22:09:31 UTC-5, tomt wrote:
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I tried using your new versioning feature in trunk.
>>>>>> I created an app using a mysql database:
>>>>>> db = DAL('mysql://version:version@localhost/version')
>>>>>> When I used the admin function to define a new user
>>>>>> I received the following error:
>>>>>> ........................................
>>>>>> <class 'gluon.contrib.pymysql.err.IntegrityError'>
>>>>>> (1452, u'Cannot add or update a child row: a foreign key constraint
>>>>>> fails
>>>>>> (`version/auth_user`, CONSTRAINT `auth_user_ibfk_1`
>>>>>> FOREIGN KEY (`created_by`) REFERENCES `auth_user` (`id`) ON DELETE
>>>>>> CASCADE)')
>>>>>> ........................................
>>>>>>
>>>>>> I rebuilt the app to use sqlite instead of mysql:
>>>>>> db = DAL('sqlite://storage.sqlite')
>>>>>>
>>>>>> I was then able to add a user without the error
>>>>>>
>>>>>> I was using MySQL client version: 5.0.84
>>>>>>
>>>>>> - any suggestions? - Tom
>>>>>>
>>>>>> On Thursday, April 5, 2012 4:16:04 PM UTC-6, Massimo Di Pierro wrote:
>>>>>>>
>>>>>>> This is how it works:
>>>>>>>
>>>>>>> # define auth
>>>>>>> auth = Auth(db, hmac_key=Auth.get_or_create_key())
>>>>>>> auth.define_tables(username=True,signature=True)
>>>>>>>
>>>>>>> # define your own tables like
>>>>>>> db.define_table('mything',Field('name'),auth.signature)
>>>>>>>
>>>>>>> # than do:
>>>>>>> auth.enable_record_versioning(db)
>>>>>>>
>>>>>>> how does it work? every table, including auth_user will have an
>>>>>>> auth.signature including created_by, created_on, modified_by,
>>>>>>> modified_on,
>>>>>>> is_active fields. When a record of table mything (or any other table)
>>>>>>> is
>>>>>>> modified, a copy of the previous record is copied into mything_archive
>>>>>>> which references the current record. When a record is deleted, it is
>>>>>>> not
>>>>>>> actually deleted but is_active is set to False, all records with
>>>>>>> is_active==False are filtered out in searches except in appadmin.
>>>>>>>
>>>>>>> Pros:
>>>>>>> - your app will get full record archival for auditing purposes
>>>>>>> - could not be simpler. nothing else to do. Try with
>>>>>>> SQLFORM.grid(db.mything) for example.
>>>>>>> - does not break references and there is no need for uuids
>>>>>>> - does not slow down searches because archive is done in separate
>>>>>>> archive tables
>>>>>>>
>>>>>>> Cons:
>>>>>>> - uses lots of extra memory because every version of a record is
>>>>>>> stored (it would be more efficient to store changes only but that would
>>>>>>> make more difficult to do auditing).
>>>>>>> - slows down db(...).update(...) for multi record because it needs
>>>>>>> to copy all records needing update from the original table to the
>>>>>>> archive
>>>>>>> table. This requires selecting all the records.
>>>>>>>
>>>>>>> Comments? Suggestions?
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
