Re: [sqlalchemy] Re: oursql two phase commit syntax error
ah, so little bit of a bug on our end then. Seems to be hardcoded in the
oursql driver. this is #2186
On Jun 6, 2011, at 3:56 PM, Yuen Ho Wong wrote:
> Replace the double quotes around the "%s" with single quotes seems to
> have solved the problem with either default SQL MODE or ANSI_QUOTES
> set.
>
> Thanks for the helping!
>
>
> On Jun 7, 3:25 am, Yuen Ho Wong wrote:
>> Ah it seems that this bug only happens with sql-mode = ANSI set in
>> my.cnf. This doesn't seem to be an issue with the mysql-python driver
>> tho.
>>
>> On Jun 7, 2:57 am, Michael Bayer wrote:
>>
>>
>>
>>> On Jun 6, 2011, at 2:23 PM, Yuen Ho Wong wrote:
>>
I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
Mac OS X 10.6.7
>>
Here's my test script:
>>
from sqlalchemy import create_engine, Column, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
>>
Base = declarative_base()
>>
class User(Base):
__tablename__ = 'user'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(128), nullable=False, unique=True)
>>
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, autoincrement=True, primary_key=True)
address = Column(Unicode(128), nullable=False, unique=True)
>>
engine = create_engine("mysql+oursql://tester:tester@localhost/
test_hometasty?charset=utf8")
>>
engine_bindings = {User: engine, Address: engine}
>>
User.metadata.create_all(engine)
Address.metadata.create_all(engine)
>>
Session = scoped_session(sessionmaker(twophase=True))
Session.configure(binds=session_bindings)
Session.configure(binds=engine_bindings)
session = Session()
alice = User(name=u"alice")
session.add(alice)
hk = Address(address=u"Hong Kong")
session.add(hk)
session.commit()
>>
>>> you might want to check that you're on MySQL 5.5 on all systems - the
>>> script works for me, provided I comment out the non-existent
>>> "session_bindings" variable.
>>
>>> my output is below:
>>
>>> 2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine
>>> CREATE TABLE address (
>>> id INTEGER NOT NULL AUTO_INCREMENT,
>>> address VARCHAR(128) NOT NULL,
>>> PRIMARY KEY (id),
>>> UNIQUE (address)
>>> )
>>
>>> 2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine ()
>>> 2011-06-06 14:56:37,279 INFO sqlalchemy.engine.base.Engine COMMIT
>>> 2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine
>>> CREATE TABLE user (
>>> id INTEGER NOT NULL AUTO_INCREMENT,
>>> name VARCHAR(128) NOT NULL,
>>> PRIMARY KEY (id),
>>> UNIQUE (name)
>>> )
>>
>>> 2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine ()
>>> 2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine COMMIT
>>> 2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine DESCRIBE
>>> `address`
>>> 2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine ()
>>> 2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
>>> 2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine ()
>>> 2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine XA BEGIN
>>> "_sa_9121998e519e1b3edb13e0aa440ca7c7"
>>> 2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine ()
>>> 2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine INSERT INTO
>>> address (address) VALUES (?)
>>> 2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine (u'Hong Kong',)
>>> 2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine INSERT INTO user
>>> (name) VALUES (?)
>>> 2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine (u'alice',)
>>> 2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine XA END
>>> "_sa_9121998e519e1b3edb13e0aa440ca7c7"
>>> 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
>>> 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA PREPARE
>>> "_sa_9121998e519e1b3edb13e0aa440ca7c7"
>>> 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
>>> 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA COMMIT
>>> "_sa_9121998e519e1b3edb13e0aa440ca7c7"
>>> 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
>>
Here's the error I get:
>>
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
\'"_sa_7fd8e09924568e2e2a653185227c2929"\' at line 1', None) 'XA BEGIN
"_sa_7fd8e09924568e2e2a653185227c2929"' ()
>>
Am I doing something wrong or is this a bug?
>>
--
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] Re: oursql two phase commit syntax error
Replace the double quotes around the "%s" with single quotes seems to
have solved the problem with either default SQL MODE or ANSI_QUOTES
set.
Thanks for the helping!
On Jun 7, 3:25 am, Yuen Ho Wong wrote:
> Ah it seems that this bug only happens with sql-mode = ANSI set in
> my.cnf. This doesn't seem to be an issue with the mysql-python driver
> tho.
>
> On Jun 7, 2:57 am, Michael Bayer wrote:
>
>
>
> > On Jun 6, 2011, at 2:23 PM, Yuen Ho Wong wrote:
>
> > > I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
> > > Mac OS X 10.6.7
>
> > > Here's my test script:
>
> > > from sqlalchemy import create_engine, Column, Integer, Unicode
> > > from sqlalchemy.ext.declarative import declarative_base
> > > from sqlalchemy.orm import scoped_session, sessionmaker
>
> > > Base = declarative_base()
>
> > > class User(Base):
> > > __tablename__ = 'user'
> > > id = Column(Integer, autoincrement=True, primary_key=True)
> > > name = Column(Unicode(128), nullable=False, unique=True)
>
> > > class Address(Base):
> > > __tablename__ = 'address'
> > > id = Column(Integer, autoincrement=True, primary_key=True)
> > > address = Column(Unicode(128), nullable=False, unique=True)
>
> > > engine = create_engine("mysql+oursql://tester:tester@localhost/
> > > test_hometasty?charset=utf8")
>
> > > engine_bindings = {User: engine, Address: engine}
>
> > > User.metadata.create_all(engine)
> > > Address.metadata.create_all(engine)
>
> > > Session = scoped_session(sessionmaker(twophase=True))
> > > Session.configure(binds=session_bindings)
> > > Session.configure(binds=engine_bindings)
> > > session = Session()
> > > alice = User(name=u"alice")
> > > session.add(alice)
> > > hk = Address(address=u"Hong Kong")
> > > session.add(hk)
> > > session.commit()
>
> > you might want to check that you're on MySQL 5.5 on all systems - the
> > script works for me, provided I comment out the non-existent
> > "session_bindings" variable.
>
> > my output is below:
>
> > 2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine
> > CREATE TABLE address (
> > id INTEGER NOT NULL AUTO_INCREMENT,
> > address VARCHAR(128) NOT NULL,
> > PRIMARY KEY (id),
> > UNIQUE (address)
> > )
>
> > 2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine ()
> > 2011-06-06 14:56:37,279 INFO sqlalchemy.engine.base.Engine COMMIT
> > 2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine
> > CREATE TABLE user (
> > id INTEGER NOT NULL AUTO_INCREMENT,
> > name VARCHAR(128) NOT NULL,
> > PRIMARY KEY (id),
> > UNIQUE (name)
> > )
>
> > 2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine ()
> > 2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine COMMIT
> > 2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine DESCRIBE
> > `address`
> > 2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine ()
> > 2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
> > 2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine ()
> > 2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine XA BEGIN
> > "_sa_9121998e519e1b3edb13e0aa440ca7c7"
> > 2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine ()
> > 2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine INSERT INTO
> > address (address) VALUES (?)
> > 2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine (u'Hong Kong',)
> > 2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine INSERT INTO user
> > (name) VALUES (?)
> > 2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine (u'alice',)
> > 2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine XA END
> > "_sa_9121998e519e1b3edb13e0aa440ca7c7"
> > 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
> > 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA PREPARE
> > "_sa_9121998e519e1b3edb13e0aa440ca7c7"
> > 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
> > 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA COMMIT
> > "_sa_9121998e519e1b3edb13e0aa440ca7c7"
> > 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
>
> > > Here's the error I get:
>
> > > sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
> > > an error in your SQL syntax; check the manual that corresponds to your
> > > MySQL server version for the right syntax to use near
> > > \'"_sa_7fd8e09924568e2e2a653185227c2929"\' at line 1', None) 'XA BEGIN
> > > "_sa_7fd8e09924568e2e2a653185227c2929"' ()
>
> > > Am I doing something wrong or is this a bug?
>
> > > --
> > > 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
> > > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because y
[sqlalchemy] Re: oursql two phase commit syntax error
Ah it seems that this bug only happens with sql-mode = ANSI set in
my.cnf. This doesn't seem to be an issue with the mysql-python driver
tho.
On Jun 7, 2:57 am, Michael Bayer wrote:
> On Jun 6, 2011, at 2:23 PM, Yuen Ho Wong wrote:
>
>
>
>
>
> > I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
> > Mac OS X 10.6.7
>
> > Here's my test script:
>
> > from sqlalchemy import create_engine, Column, Integer, Unicode
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import scoped_session, sessionmaker
>
> > Base = declarative_base()
>
> > class User(Base):
> > __tablename__ = 'user'
> > id = Column(Integer, autoincrement=True, primary_key=True)
> > name = Column(Unicode(128), nullable=False, unique=True)
>
> > class Address(Base):
> > __tablename__ = 'address'
> > id = Column(Integer, autoincrement=True, primary_key=True)
> > address = Column(Unicode(128), nullable=False, unique=True)
>
> > engine = create_engine("mysql+oursql://tester:tester@localhost/
> > test_hometasty?charset=utf8")
>
> > engine_bindings = {User: engine, Address: engine}
>
> > User.metadata.create_all(engine)
> > Address.metadata.create_all(engine)
>
> > Session = scoped_session(sessionmaker(twophase=True))
> > Session.configure(binds=session_bindings)
> > Session.configure(binds=engine_bindings)
> > session = Session()
> > alice = User(name=u"alice")
> > session.add(alice)
> > hk = Address(address=u"Hong Kong")
> > session.add(hk)
> > session.commit()
>
> you might want to check that you're on MySQL 5.5 on all systems - the script
> works for me, provided I comment out the non-existent "session_bindings"
> variable.
>
> my output is below:
>
> 2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine
> CREATE TABLE address (
> id INTEGER NOT NULL AUTO_INCREMENT,
> address VARCHAR(128) NOT NULL,
> PRIMARY KEY (id),
> UNIQUE (address)
> )
>
> 2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine ()
> 2011-06-06 14:56:37,279 INFO sqlalchemy.engine.base.Engine COMMIT
> 2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine
> CREATE TABLE user (
> id INTEGER NOT NULL AUTO_INCREMENT,
> name VARCHAR(128) NOT NULL,
> PRIMARY KEY (id),
> UNIQUE (name)
> )
>
> 2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine ()
> 2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine COMMIT
> 2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine DESCRIBE `address`
> 2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine ()
> 2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
> 2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine ()
> 2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine XA BEGIN
> "_sa_9121998e519e1b3edb13e0aa440ca7c7"
> 2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine ()
> 2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine INSERT INTO
> address (address) VALUES (?)
> 2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine (u'Hong Kong',)
> 2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine INSERT INTO user
> (name) VALUES (?)
> 2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine (u'alice',)
> 2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine XA END
> "_sa_9121998e519e1b3edb13e0aa440ca7c7"
> 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
> 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA PREPARE
> "_sa_9121998e519e1b3edb13e0aa440ca7c7"
> 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
> 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA COMMIT
> "_sa_9121998e519e1b3edb13e0aa440ca7c7"
> 2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
>
>
>
>
>
> > Here's the error I get:
>
> > sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
> > an error in your SQL syntax; check the manual that corresponds to your
> > MySQL server version for the right syntax to use near
> > \'"_sa_7fd8e09924568e2e2a653185227c2929"\' at line 1', None) 'XA BEGIN
> > "_sa_7fd8e09924568e2e2a653185227c2929"' ()
>
> > Am I doing something wrong or is this a bug?
>
> > --
> > 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
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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.
