<p dir="auto">The bug can be demonstrated on the following example:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import Table, MetaData, Integer
from sqlalchemy.dialects import mysql

metadata = MetaData()

table = Table('table', metadata,
   Column('id', Integer, primary_key=True),
   Column('int_or_null', Integer, nullable=True),
)

def dump_sql(query):
    return str(query.compile(dialect=mysql.dialect()))

query = mysql.insert(table) \
    .on_duplicate_key_update(int_or_null=None)

print(dump_sql(query))"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Table</span>, <span class="pl-v">MetaData</span>, <span class="pl-v">Integer</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">dialects</span> <span class="pl-k">import</span> <span class="pl-s1">mysql</span>

<span class="pl-s1">metadata</span> <span class="pl-c1">=</span> <span class="pl-v">MetaData</span>()

<span class="pl-s1">table</span> <span class="pl-c1">=</span> <span class="pl-v">Table</span>(<span class="pl-s">'table'</span>, <span class="pl-s1">metadata</span>,
   <span class="pl-v">Column</span>(<span class="pl-s">'id'</span>, <span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>),
   <span class="pl-v">Column</span>(<span class="pl-s">'int_or_null'</span>, <span class="pl-v">Integer</span>, <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">True</span>),
)

<span class="pl-k">def</span> <span class="pl-en">dump_sql</span>(<span class="pl-s1">query</span>):
    <span class="pl-k">return</span> <span class="pl-en">str</span>(<span class="pl-s1">query</span>.<span class="pl-en">compile</span>(<span class="pl-s1">dialect</span><span class="pl-c1">=</span><span class="pl-s1">mysql</span>.<span class="pl-en">dialect</span>()))

<span class="pl-s1">query</span> <span class="pl-c1">=</span> <span class="pl-s1">mysql</span>.<span class="pl-en">insert</span>(<span class="pl-s1">table</span>) \
    .<span class="pl-en">on_duplicate_key_update</span>(<span class="pl-s1">int_or_null</span><span class="pl-c1">=</span><span class="pl-c1">None</span>)

<span class="pl-en">print</span>(<span class="pl-en">dump_sql</span>(<span class="pl-s1">query</span>))</pre></div>
<p dir="auto">which generates an invalid SQL that subsequently fails to execute:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="INSERT INTO `table` (id, int_or_null) 
VALUES (%s, %s) 
ON DUPLICATE KEY UPDATE "><pre class="notranslate"><span class="pl-k">INSERT INTO</span> <span class="pl-s"><span class="pl-pds">`</span>table<span class="pl-pds">`</span></span> (id, int_or_null) 
<span class="pl-k">VALUES</span> (%s, %s) 
<span class="pl-k">ON</span> DUPLICATE KEY <span class="pl-k">UPDATE</span> </pre></div>
<p dir="auto">The missing <code class="notranslate">int_or_null = %s</code> or <code class="notranslate">int_or_null = NULL</code> part is apparently being cut in the <code class="notranslate">visit_on_duplicate_key_update(...)</code> function:</p>
<p dir="auto"></p><div class="Box Box--condensed my-2">
  <div class="Box-header f6">
    <p class="mb-0 text-bold">
      <a href="https://github.com/sqlalchemy/sqlalchemy/blob/e2521b595ae8b5b4ca8147c5ee13e2fc0f597e63/lib/sqlalchemy/dialects/mysql/base.py#L1241-L1242">sqlalchemy/lib/sqlalchemy/dialects/mysql/base.py</a>
    </p>
    <p class="mb-0 color-fg-muted">
        Lines 1241 to 1242
      in
      <a data-pjax="true" class="commit-tease-sha" href="/sqlalchemy/sqlalchemy/commit/e2521b595ae8b5b4ca8147c5ee13e2fc0f597e63">e2521b5</a>
    </p>
  </div>
  <div itemprop="text" class="Box-body p-0 blob-wrapper blob-wrapper-embedded data">
    <table class="highlight tab-size mb-0 js-file-line-container" data-tab-size="8" data-paste-markdown-skip="">

        <tbody><tr class="border-0">
          <td id="L1241" class="blob-num border-0 px-3 py-0 color-bg-default" data-line-number="1241"></td>
          <td id="LC1241" class="blob-code border-0 px-3 py-0 color-bg-default blob-code-inner js-file-line"> <span class="pl-k">if</span> <span class="pl-s1">val</span> <span class="pl-c1">is</span> <span class="pl-c1">None</span>: </td>
        </tr>

        <tr class="border-0">
          <td id="L1242" class="blob-num border-0 px-3 py-0 color-bg-default" data-line-number="1242"></td>
          <td id="LC1242" class="blob-code border-0 px-3 py-0 color-bg-default blob-code-inner js-file-line">     <span class="pl-k">continue</span> </td>
        </tr>
    </tbody></table>
  </div>
</div>
<p></p>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">the ZBlog demo can illustrate this.  this relationship in mapper.py:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Topic.mapper = mapper(Topic, tables.topics)
        
TopicAssociation.mapper = mapper(TopicAssociation,tables.topic_xref, 
                primary_key=[tables.topic_xref.c.topic_id](tables.topic_xref.c.post_id,), 
                properties={
                    'topic':relation(Topic.mapper, lazy=False),
                })
Post.mapper = mapper(Post, posts_with_ccount, properties={
    'id':posts_with_ccount.c.post_id,
    'topics':relation(TopicAssociation, lazy=False, private=True, association=Topic)
}, is_primary=True, order_by=[desc(posts_with_ccount.c.datetime)](desc(posts_with_ccount.c.datetime)))"><pre class="notranslate"><code class="notranslate">Topic.mapper = mapper(Topic, tables.topics)
        
TopicAssociation.mapper = mapper(TopicAssociation,tables.topic_xref, 
                primary_key=[tables.topic_xref.c.topic_id](tables.topic_xref.c.post_id,), 
                properties={
                    'topic':relation(Topic.mapper, lazy=False),
                })
Post.mapper = mapper(Post, posts_with_ccount, properties={
    'id':posts_with_ccount.c.post_id,
    'topics':relation(TopicAssociation, lazy=False, private=True, association=Topic)
}, is_primary=True, order_by=[desc(posts_with_ccount.c.datetime)](desc(posts_with_ccount.c.datetime)))
</code></pre></div>
<p dir="auto">this creates TopicAssociation as a many-to-many association object.  If you go to the interface and enter a post, and put the same topic name twice, it saves.  This is the bug.  the list of TopicAssociations should somehow be unique based on the Topic object its attached to, just like any other collection.  then if you go to update that post, the update on the topicassocaitions returns 2 rows instead of 1 and the concurrency detector goes off.</p>