<div dir="ltr"><div>The problem is not for that single entry</div>Looks like the auto_increment for that table was reset (I don't know when-how)<div><br></div><div>Cheers, Massimo </div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, May 8, 2019 at 1:50 PM Surya Seetharaman <<a href="mailto:surya.seetharaman9@gmail.com">surya.seetharaman9@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div class="gmail_default" style="font-size:small">Hi,</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, May 8, 2019 at 12:41 PM Massimo Sgaravatto <<a href="mailto:massimo.sgaravatto@gmail.com" target="_blank">massimo.sgaravatto@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div>Hi</div><div><br></div><div>Fron time to time I use to move entries related to deleted instances to shadow tables, using the command:</div><div><br></div><div>nova-manage db archive_deleted_rows<br></div><div><br></div><div>This is now failing [*] for the instance_metadata table because of a 'duplicate entry for the primary key' problem:</div><div><br></div><div><div>DBDuplicateEntry: (pymysql.err.IntegrityError) (1062, u"Duplicate entry '6' for key 'PRIMARY'") [SQL: u'INSERT INTO shadow_instance_metadata (created_at, updated_at, deleted_at, deleted, id, `key`, value, instance_uuid) SELECT instance_metadata.created_at, instance_metadata.updated_at, instance_metadata.deleted_at, instance_metadata.deleted, <a href="http://instance_metadata.id" target="_blank">instance_metadata.id</a>, instance_metadata.`key`, instance_metadata.value, instance_metadata.instance_uuid \nFROM instance_metadata \nWHERE instance_metadata.deleted != %(deleted_1)s ORDER BY <a href="http://instance_metadata.id" target="_blank">instance_metadata.id</a> \n LIMIT %(param_1)s'] [parameters: {u'param_1': 1, u'deleted_1': 0}]</div></div><div><br></div><div><br></div><div>Indeed:</div><div><br></div><div><div>mysql> SELECT instance_metadata.created_at, instance_metadata.updated_at, instance_metadata.deleted_at, instance_metadata.deleted, <a href="http://instance_metadata.id" target="_blank">instance_metadata.id</a>, instance_metadata.`key`, instance_metadata.value, instance_metadata.instance_uuid FROM instance_metadata WHERE instance_metadata.deleted != 0 ORDER BY <a href="http://instance_metadata.id" target="_blank">instance_metadata.id</a> limit 1;</div><div>+---------------------+------------+---------------------+---------+----+-------+-------+--------------------------------------+</div><div>| created_at | updated_at | deleted_at | deleted | id | key | value | instance_uuid |</div><div>+---------------------+------------+---------------------+---------+----+-------+-------+--------------------------------------+</div><div>| 2018-09-20 07:40:56 | NULL | 2018-09-20 07:54:26 | 6 | 6 | group | node | a9000ff7-2298-454c-bf71-9e3c62ec0f3c |</div><div>+---------------------+------------+---------------------+---------+----+-------+-------+--------------------------------------+</div><div>1 row in set (0.00 sec)</div><div><br></div><div><br></div><div>But there is a 5-years old entry (if I am not wrong we were running Havana at that time) in the shadow table with that id:</div><div><br></div><div>mysql> select * from shadow_instance_metadata where id='6';</div><div>+---------------------+------------+---------------------+----+----------+---------------------+--------------------------------------+---------+</div><div>| created_at | updated_at | deleted_at | id | key | value | instance_uuid | deleted |</div><div>+---------------------+------------+---------------------+----+----------+---------------------+--------------------------------------+---------+</div><div>| 2014-11-04 12:57:10 | NULL | 2014-11-04 13:06:45 | 6 | director | microbosh-openstack | 5db5b17b-69f2-4f0a-bdd2-efe710268021 | 6 |</div><div>+---------------------+------------+---------------------+----+----------+---------------------+--------------------------------------+---------+</div><div>1 row in set (0.00 sec)</div><div><br></div><div>mysql> </div></div><div><br></div><div><br></div><div>I wonder how could that happen.</div><div><br></div><div>Can I simply remove that entry from the shadow table (I am not really interested to keep it) or are there better (cleaner) way to fix the problem ?</div><div><br></div><div><br></div><div>This Cloud is now running Ocata</div><div><br></div><div>Thanks, Massimo</div><div><br></div></div></div></div></div></div></div></div></blockquote><div><br></div><div class="gmail_default" style="font-size:small">From what I can understand, it looks like a record with id 6 was archived long back (havana-ish) and then there was a new record with id 6 again ready to be archived ? (not sure how there could have been two records with same id since ids are incremental even over releases, I am not sure of the history though since I wasn't involved with OS then). I think the only way out is to manually delete that entry from the shadow table if you don't want it. There should be no harm in removing it.</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default"><font face="arial, helvetica, sans-serif" style="font-size:small">We have a </font><span style="font-family:arial,helvetica,sans-serif">"</span><span class="gmail-m_6540274962041583097gmail-pre" style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0);box-sizing:border-box">nova-manage</span><span style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0)"> </span><span class="gmail-m_6540274962041583097gmail-pre" style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0);box-sizing:border-box">db</span><span style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0)"> </span><span class="gmail-m_6540274962041583097gmail-pre" style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0);box-sizing:border-box">purge</span><span style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0)"> </span><span class="gmail-m_6540274962041583097gmail-pre" style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0);box-sizing:border-box">[--all]</span><span style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0)"> </span><span class="gmail-m_6540274962041583097gmail-pre" style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0);box-sizing:border-box">[--before</span><span style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0)"> </span><span class="gmail-m_6540274962041583097gmail-pre" style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0);box-sizing:border-box"><date>]</span><span style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0)"> </span><span class="gmail-m_6540274962041583097gmail-pre" style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0);box-sizing:border-box">[--verbose]</span><span style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0)"> </span><span class="gmail-m_6540274962041583097gmail-pre" style="font-size:14px;font-family:arial,helvetica,sans-serif;color:rgb(0,0,0);box-sizing:border-box">[--all-cells]"</span><font face="arial, helvetica, sans-serif"> command that removes records from shadow_tables (</font><font face="arial, helvetica, sans-serif"><a href="https://docs.openstack.org/nova/rocky/cli/nova-manage.html" target="_blank">https://docs.openstack.org/nova/rocky/cli/nova-manage.html</a></font><span style="font-family:arial,helvetica,sans-serif">) but it was introduced in rocky. So it won't be available in Ocata unfortunately.</span></div><div class="gmail_default" style="font-size:small"> </div><div class="gmail_default" style="font-size:small">Cheers,</div><div class="gmail_default" style="font-size:small">Surya.</div></div></div></div>
</blockquote></div>