Hi,
I have received the same error as Tom on MySQL (5.5.22) when adding a user
to the auth_user table via the Database Administration screen.
(1452, u'Cannot add or update a child row: a foreign key constraint fails)
Reverting back to "signature=False" resulted in an error (Trace below) :
InternalError: (1025, u"Error on rename of './web2py_dev/#sql-415a_13' to
'./web2py_dev/auth_user' (errno: 150)")
When using SQLite, you can use the database administrator to add a user
without being logged in to your application.
With the MySQL connection, you must be logged in so that the 'Createed By'
and 'Modified By' fields are correctly populated with a valid entry.
Once you register and login, the database administration tool can be used
as normal.
Thanks
Rakesh
Traceback (most recent call last):
File "/data/source/web2py_src/web2py/gluon/restricted.py", line 205, in
restricted
exec ccode in environment
File "/data/source/web2py_src/web2py/applications/web2py_test/models/db.py"
<http://localhost:8000/admin/default/edit/web2py_test/models/db.py>, line 23,
in <module>
auth.define_tables(username=True, signature=False)
File "/data/source/web2py_src/web2py/gluon/tools.py", line 1356, in
define_tables
format='%(username)s'))
File "/data/source/web2py_src/web2py/gluon/dal.py", line 6621, in define_table
polymodel=polymodel)
File "/data/source/web2py_src/web2py/gluon/dal.py", line 784, in create_table
fake_migrate=fake_migrate)
File "/data/source/web2py_src/web2py/gluon/dal.py", line 883, in migrate_table
self.execute(sub_query)
File "/data/source/web2py_src/web2py/gluon/dal.py", line 1446, in execute
return self.log_execute(*a, **b)
File "/data/source/web2py_src/web2py/gluon/dal.py", line 1440, in log_execute
ret = self.cursor.execute(*a, **b)
File "/data/source/web2py_src/web2py/gluon/contrib/pymysql/cursors.py", line
108, in execute
self.errorhandler(self, exc, value)
File "/data/source/web2py_src/web2py/gluon/contrib/pymysql/connections.py",
line 184, in defaulterrorhandler
raise errorclass, errorvalue
InternalError: (1025, u"Error on rename of './web2py_dev/#sql-415a_13' to
'./web2py_dev/auth_user' (errno: 150)")
On Sunday, 8 April 2012 17:04:14 UTC+2, 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?
>>>
>>>
>>>
>>>
>>>
>>>
>>>
