Hi again :)
that sounds promising! I will definitively look into that!!
Karen Tracey schrieb:
On Thu, Apr 3, 2008 at 9:47 AM, Constantin Christmann <[EMAIL PROTECTED]>
wrote:
Hello,
I am working on the following problem now since yesterday and have no
idea what could be the reason for this behavoir:
Inside my view (I have TransactionMiddleware enabled) I execute some
raw SQL (UPDATE) and afterwards I save form data to the database.
With postgres this works fine but with MySQL the UPDATE statements of
the raw SQL aren't commited.
This is the query log of the MySQL-DB:
080403 15:24:53 16 Connect [EMAIL PROTECTED] on tucent
16 Query SET NAMES utf8
16 Query set autocommit=0
16 Query SELECT
`django_session`.`session_key`,`django_session`.`session_data`,`django_session`.`expire_date`
FROM `django_session` WHERE (`django_session`.`session_key` =
'c9bffcf99f9d65489ede472f4b490f73' AND `django_session`.`expire_date`
> '2008-04-03 15:24:52.988000')
16 Query SELECT
`auth_user`.`id`,`auth_user`.`username`,`auth_user`.`first_name`,`auth_user`.`last_name`,`auth_user`.`email`,`auth_user`.`password`,`auth_user`.`is_staff`,`auth_user`.`is_active`,`auth_user`.`is_superuser`,`auth_user`.`last_login`,`auth_user`.`date_joined`
FROM `auth_user` WHERE (`auth_user`.`id` = 1)
16 Query SELECT
`forum_post`.`id`,`forum_post`.`thread_id`,`forum_post`.`created_at`,`forum_post`.`lft`,`forum_post`.`rgt`,`forum_post`.`subject`,`forum_post`.`author_id`,`forum_post`.`text`,`forum_post`.`is_published`
FROM `forum_post` WHERE (`forum_post`.`id` = '3')
***the raw SQL***
16 Query UPDATE forum_post SET rgt=rgt+2
WHERE thread_id=1 AND rgt >= 3;
UPDATE forum_post SET lft=lft+2 WHERE
thread_id=1 AND lft > 3
***saving the form data***
17 Connect [EMAIL PROTECTED] on tucent
17 Query SET NAMES utf8
17 Query set autocommit=0
17 Query SELECT
`forum_thread`.`id`,`forum_thread`.`forum_id`,`forum_thread`.`root_post_id`,`forum_thread`.`created_at`
FROM `forum_thread` WHERE (`forum_thread`.`id` = 1)
17 Query INSERT INTO `forum_post`
(`thread_id`,`created_at`,`lft`,`rgt`,`subject`,`author_id`,`text`,`is_published`)
VALUES (1,'2008-04-03 15:24:53',3,4,'Re: Re: Test',1,'',1)
17 Query commit
17 Quit
Although the raw SQL and the save_instance() are inside the same view,
a new connection becomes established.
Because of that the UPDATE statements don't become commited and are
ignored.
But what could have triggered this new connection???
The associated django code lookes like this:
cursor = connection.cursor()
cursor.execute("""
UPDATE forum_post SET rgt=rgt+2 WHERE
thread_id=%s AND rgt >= %s;
UPDATE forum_post SET lft=lft+2 WHERE
thread_id=%s AND lft > %s;
""", [parent_post.thread_id, rgt,
parent_post.thread_id, rgt ])
transaction.set_dirty()
post = save_instance(form=post_form,
instance=Post(thread=parent_post.thread, lft=rgt, rgt=rgt+1,
author=request.user))
Try splitting your single cursor.execute() call with two UPDATEs into
two cursor.execute() calls each with a single update. I think what is
happening is your multi-UPDATE execute() generates two results to be
retrieved from the connection, but MySQLdb only reads one. Then the
next time Django goes to use the connection, the ping to test
connection validity raises an exception because there is an
outstanding result that hasn't been fetched. This causes the existing
connection to be closed and a new one to be opened. See:
http://code.djangoproject.com/browser/django/trunk/django/db/backends/mysql/base.py#L152
I have not read the specs involved closely enough to say whether
MySQLdb ought to be able to handle single executes that return multiple
results, or if you are just not supposed to do that.
Karen
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
|
