<p dir="auto"><strong>Migrated issue, originally created by moon cake (<a href="https://github.com/mooncake4132">@mooncake4132</a>)</strong></p>
<p dir="auto">It seems like <code class="notranslate">_sort_states</code> in <code class="notranslate">persistence.py</code> is trying to sort the primary key(s), irregardless whether it is sortable. In this case, I'm using an <code class="notranslate">enum.Enum</code>. Full repro code follows.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="(sqlalchemy) $ python test.py
Traceback (most recent call last):
  File &quot;test.py&quot;, line 35, in &lt;module&gt;
    create_trees(session)
  File &quot;test.py&quot;, line 29, in create_trees
    session.query(Tree).merge_result([Tree(color=Color.red), Tree(color=Color.black)])
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py&quot;, line 572, in __exit__
    self.rollback()
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py&quot;, line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/util/compat.py&quot;, line 187, in reraise
    raise value
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py&quot;, line 569, in __exit__
    self.commit()
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py&quot;, line 467, in commit
    self._prepare_impl()
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py&quot;, line 447, in _prepare_impl
    self.session.flush()
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py&quot;, line 2254, in flush
    self._flush(objects)
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py&quot;, line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py&quot;, line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/util/compat.py&quot;, line 187, in reraise
    raise value
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/session.py&quot;, line 2344, in _flush
    flush_context.execute()
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py&quot;, line 391, in execute
    rec.execute(self)
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py&quot;, line 556, in execute
    uow
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py&quot;, line 156, in save_obj
    base_mapper, states, uowtransaction
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py&quot;, line 286, in _organize_states_for_save
    states):
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py&quot;, line 1255, in _connections_for_states
    for state in _sort_states(states):
  File &quot;/tmp/sqlalchemy/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py&quot;, line 1277, in _sort_states
    sorted(persistent, key=lambda q: q.key[1])
TypeError: '&lt;' not supported between instances of 'Color' and 'Color'"><pre class="notranslate"><code class="notranslate">(sqlalchemy) $ python test.py
Traceback (most recent call last):
  File "test.py", line 35, in &lt;module&gt;
    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: '&lt;' not supported between instances of 'Color' and 'Color'
</code></pre></div>
<p dir="auto">=======</p>
<p dir="auto">Version</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Python 3.6.5 (default, Mar 29 2018, 03:28:50)
[GCC 5.4.0 20160609] on linux
Type &quot;help&quot;, &quot;copyright&quot;, &quot;credits&quot; or &quot;license&quot; for more information.
&gt;&gt;&gt; import sqlalchemy
&gt;&gt;&gt; sqlalchemy.__version__
'1.2.8'"><pre class="notranslate"><code class="notranslate">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.
&gt;&gt;&gt; import sqlalchemy
&gt;&gt;&gt; sqlalchemy.__version__
'1.2.8'
</code></pre></div>
<p dir="auto">=======</p>
<p dir="auto">Repro code</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="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)"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">enum</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>, <span class="pl-v">Column</span>, <span class="pl-v">Enum</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-s1">sessionmaker</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</span>


<span class="pl-k">class</span> <span class="pl-v">Color</span>(<span class="pl-s1">enum</span>.<span class="pl-v">Enum</span>):
    <span class="pl-s1">red</span> <span class="pl-c1">=</span> <span class="pl-s">'Red'</span>
    <span class="pl-s1">black</span> <span class="pl-c1">=</span> <span class="pl-s">'Black'</span>


<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()


<span class="pl-k">class</span> <span class="pl-v">Tree</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'tree'</span>

    <span class="pl-s1">color</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Enum</span>(<span class="pl-v">Color</span>), <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)


<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">'postgresql://tree:tree@127.0.0.1/trees'</span>)
<span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">engine</span>)

<span class="pl-v">Session</span> <span class="pl-c1">=</span> <span class="pl-en">sessionmaker</span>(<span class="pl-s1">bind</span><span class="pl-c1">=</span><span class="pl-s1">engine</span>)


<span class="pl-k">def</span> <span class="pl-en">create_trees</span>(<span class="pl-s1">session</span>):
    <span class="pl-k">with</span> <span class="pl-s1">session</span>.<span class="pl-en">begin_nested</span>():
        <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">Tree</span>).<span class="pl-en">merge_result</span>([<span class="pl-v">Tree</span>(<span class="pl-s1">color</span><span class="pl-c1">=</span><span class="pl-v">Color</span>.<span class="pl-s1">red</span>), <span class="pl-v">Tree</span>(<span class="pl-s1">color</span><span class="pl-c1">=</span><span class="pl-v">Color</span>.<span class="pl-s1">black</span>)])

    <span class="pl-s1">session</span>.<span class="pl-en">commit</span>()


<span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-v">Session</span>()
<span class="pl-en">create_trees</span>(<span class="pl-s1">session</span>)
<span class="pl-en">create_trees</span>(<span class="pl-s1">session</span>)</pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">original description:</p>
<p dir="auto">I'm using SA to talk to a MySQL database, but the database has a<br>
tendency to drop my connection while the queries are running.</p>
<p dir="auto">SQLError: (OperationalError) (2013, 'Lost connection to MySQL server<br>
during query') 'SELECT sum(bgp_churn.count) \nFROM bgp_churn \nWHERE<br>
bgp_churn.tstamp &gt; %s AND bgp_churn.type = %s \n LIMIT 1'<br>
<a href="1158084083.0,">'U'</a></p>
<p dir="auto">This isn't a particularly big deal for my application, I'm caching the<br>
results and can just retry a minute or two later. Unfortunately, this<br>
IS a big deal to SQLAlchemy. Frequently, but not always, it gets into<br>
a state where every query after this results in a 'Commands out of<br>
sync' message.</p>
<p dir="auto">SQLError: (ProgrammingError) (2014, "Commands out of sync; you can't<br>
run this command now") 'SELECT bgp_churn.asn, sum(bgp_churn.count) AS<br>
count \nFROM bgp_churn \nWHERE bgp_churn.tstamp &gt; %s AND<br>
bgp_churn.type = %s GROUP BY bgp_churn.asn ORDER BY count DESC \n<br>
LIMIT 4' [1158089885.0, 'U'</p>
<p dir="auto">Here's the MySQL manual page on this error:</p>
<p dir="auto"><a href="http://dev.mysql.com/doc/refman/4.1/en/commands-out-of-sync.html" rel="nofollow">http://dev.mysql.com/doc/refman/4.1/en/commands-out-of-sync.html</a></p>
<p dir="auto">The strange thing is that this does not occur consistently. It seems<br>
to occur less frequently when echo is on but that might be a placebo<br>
on my part.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/306/cursors_check.diff">cursors_check.diff</a></p>