I'm having trouble getting updates from InnoDB table. I have a test
case with two programs. One simply creates a session and does a
periodic query. The other writes two values, one into a MyISAM table
and one into an InnoDB table. The next query from the first program
doesn't get the updated contents inserted from the second program.
Here is the test case code:
----------------------------------
First the reader program:
#!/usr/bin/python2.5
import time
import sqlalchemy
print sqlalchemy.__version__ # 0.4.8
from sqlalchemy import create_engine
engine = create_engine('mysql://localhost/test_db', echo=True)
from sqlalchemy import Table, Column, String, MetaData
metadata = MetaData()
myisam_table = Table('myisam_table', metadata,
Column('my_string', String(40), primary_key=True),
mysql_engine='MyISAM'
)
innodb_table = Table('innodb_table', metadata,
Column('my_string', String(40), primary_key=True),
mysql_engine='InnoDB'
)
metadata.create_all(engine)
class MyisamTableRow(object):
def __init__(self, my_string):
self.my_string = my_string
def __repr__(self):
return "<MyisamTableRow('%s')>" % self.my_string
class InnodbTableRow(object):
def __init__(self, my_string):
self.my_string = my_string
def __repr__(self):
return "<InnodbTableRow('%s')>" % self.my_string
from sqlalchemy.orm import mapper
mapper(MyisamTableRow, myisam_table)
mapper(InnodbTableRow, innodb_table)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
session = Session(bind=engine, autoflush=False, transactional=True)
while (True):
time.sleep(10)
print session.query(MyisamTableRow).all()
print session.query(InnodbTableRow).all()
----------------------------------
The writer is essentially the same, with the while loop replaced with:
m = MyisamTableRow('a test string')
session.save(m)
session.commit()
i = InnodbTableRow('a test string')
session.save(i)
session.commit()
------------------------------------
While the reader is running, I execute the writer:
2009-06-01 06:24:51,295 INFO sqlalchemy.engine.base.Engine.0x..50
BEGIN
2009-06-01 06:24:51,296 INFO sqlalchemy.engine.base.Engine.0x..50
INSERT INTO myisam_table (my_string) VALUES (%s)
2009-06-01 06:24:51,297 INFO sqlalchemy.engine.base.Engine.0x..50 ['a
test string']
2009-06-01 06:24:51,298 INFO sqlalchemy.engine.base.Engine.0x..50
COMMIT
2009-06-01 06:24:51,300 INFO sqlalchemy.engine.base.Engine.0x..50
BEGIN
2009-06-01 06:24:51,301 INFO sqlalchemy.engine.base.Engine.0x..50
INSERT INTO innodb_table (my_string) VALUES (%s)
2009-06-01 06:24:51,301 INFO sqlalchemy.engine.base.Engine.0x..50 ['a
test string']
2009-06-01 06:24:51,302 INFO sqlalchemy.engine.base.Engine.0x..50
COMMIT
------------------------------------
The next iteration of the reader querying, shows the MyISAM table
contents, but the InnoDB contents haven't updated:
2009-06-01 06:24:56,515 INFO sqlalchemy.engine.base.Engine.0x..50
SELECT myisam_table.my_string AS myisam_table_my_string
FROM myisam_table ORDER BY myisam_table.my_string
2009-06-01 06:24:56,516 INFO sqlalchemy.engine.base.Engine.0x..50 []
[<MyisamTableRow('a test string')>]
2009-06-01 06:24:56,523 INFO sqlalchemy.engine.base.Engine.0x..50
SELECT innodb_table.my_string AS innodb_table_my_string
FROM innodb_table ORDER BY innodb_table.my_string
2009-06-01 06:24:56,523 INFO sqlalchemy.engine.base.Engine.0x..50 []
[]
I am not sure what difference I have that is making the InnoDB table
not update properly between processes. Any help/suggestions would be
appreciated. I have not had luck finding previous issues like this on
Google.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
