Issue 1:
Migrated issue, originally created by moon cake (@mooncake4132)

It seems like _sort_states in persistence.py is trying to sort the primary key(s), irregardless whether it is sortable. In this case, I'm using an enum.Enum. Full repro code follows.

(sqlalchemy) $ python test.py
Traceback (most recent call last):
File "test.py", line 35, in <module>
create_trees(session)
File "test.py", line 29, in create_trees
session.query(Tree).merge_result([Tree(color=Color.red), Tree(color=Color.black)])
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 572, in __exit__
self.rollback()
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
raise value
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 569, in __exit__
self.commit()
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 467, in commit
self._prepare_impl()
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
self.session.flush()
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
self._flush(objects)
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
transaction.rollback(_capture_exception=True)
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
raise value
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
flush_context.execute()
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
rec.execute(self)
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
uow
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 156, in save_obj
base_mapper, states, uowtransaction
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 286, in _organize_states_for_save
states):
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 1255, in _connections_for_states
for state in _sort_states(states):
File "/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 1277, in _sort_states
sorted(persistent, key=lambda q: q.key[1])
TypeError: '<' not supported between instances of 'Color' and 'Color'

=======

Version

Python 3.6.5 (default, Mar 29 2018, 03:28:50)
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.2.8'

=======

Repro code

import enum
from sqlalchemy import create_engine, Column, Enum
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

class Color(enum.Enum):
red = 'Red'
black = 'Black'

Base = declarative_base()

class Tree(Base):
__tablename__ = 'tree'

color = Column(Enum(Color), primary_key=True)

engine = create_engine('postgresql://tree:tree@127.0.0.1/trees')
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

def create_trees(session):
with session.begin_nested():
session.query(Tree).merge_result([Tree(color=Color.red), Tree(color=Color.black)])

session.commit()

session = Session()
create_trees(session)
create_trees(session)

Issue 2:
Migrated issue, originally created by Michael Bayer (@zzzeek)

original description:

I'm using SA to talk to a MySQL database, but the database has a

tendency to drop my connection while the queries are running.

SQLError: (OperationalError) (2013, 'Lost connection to MySQL server

during query') 'SELECT sum(bgp_churn.count) \nFROM bgp_churn \nWHERE

bgp_churn.tstamp > %s AND bgp_churn.type = %s \n LIMIT 1'

'U'

This isn't a particularly big deal for my application, I'm caching the

results and can just retry a minute or two later. Unfortunately, this

IS a big deal to SQLAlchemy. Frequently, but not always, it gets into

a state where every query after this results in a 'Commands out of

sync' message.

SQLError: (ProgrammingError) (2014, "Commands out of sync; you can't

run this command now") 'SELECT bgp_churn.asn, sum(bgp_churn.count) AS

count \nFROM bgp_churn \nWHERE bgp_churn.tstamp > %s AND

bgp_churn.type = %s GROUP BY bgp_churn.asn ORDER BY count DESC \n

LIMIT 4' [1158089885.0, 'U'

Here's the MySQL manual page on this error:

http://dev.mysql.com/doc/refman/4.1/en/commands-out-of-sync.html

The strange thing is that this does not occur consistently. It seems

to occur less frequently when echo is on but that might be a placebo

on my part.

Attachments: cursors_check.diff