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
-~----------~----~----~----~------~----~------~--~---
