weird problem db with autocommit

[meister]

Hello,

Here is problematic code (standard django setup, mysql backend):

import time

import os, sys, re
sys.path.append(os.path.abspath(os.path.dirname(__file__))+'/..')
os.environ['DJANGO_SETTINGS_MODULE'] = 'settings'

from django.conf import settings

from django.contrib.auth.models import User

#from django.db import connection
#cursor = connection.cursor()
#cursor.execute('SET autocommit = 1')

while True:
    u = User.objects.get(pk=1)
    print u.first_name
    time.sleep(1)

It displays user first_name each second. On the other hand with mysql client :

$ update auth_user set first_name = "foo" where id=1;

Value does not update in my loop (wireshark shows the old value too in the mysql packets dumped). If I restart the process, it fetch the correct new value.
I can fix the problem by adding the 3 autocommit lines commented out.

Problem do not occur on my ubuntu 32b desktop (32bits django 1.3.1 / MySQL-python 1.2.3, mysql 5.1.58) nor a debian squeeze server (32bits mysql 5.1.49).

Problem occurs on a 64 bits debian squeeze server (64bits mysql 5.1.49) and a ubuntu 64 server (64 bits mysql 5.1.41).

Thanks.

[meister <admin@…>]

A easier way to reproduce it :

$ ./manage.py shell
>>> from django.contrib.auth.models import User
>>> print User.objects.get(pk=1).first_name
aaa
# on the mysql server :
update auth_user set first_name = "ooo" where id=1;
# back in the django shell :
>>> print User.objects.get(pk=1).first_name

aaa

[Anssi Kääriäinen]

The problem seems to be that you are running in a transaction with repeatable read semantics. I don't think Django supports autocommit for MySQL.

[meister <admin@…>]

Replying to akaariai:

The problem seems to be that you are running in a transaction with repeatable read semantics. I don't think Django supports autocommit for MySQL.

It doesn't explain why my code works on some environments and do not work on others...

[meister <admin@…>]

Replying to meister <admin@…>:

Replying to akaariai:

The problem seems to be that you are running in a transaction with repeatable read semantics. I don't think Django supports autocommit for MySQL.

It doesn't explain why my code works on some environments and do not work on others...

It works when using MyISAM and creates some problem with InnoDB. My problem is explained in ​http://groups.google.com/group/django-users/msg/55fa3724d2754013?pli=1.